面经整理-数据库

本文是作者整理的个人笔记，文中可能引用到其他人的成果但是未指明出处，如有不妥，请指正，谢谢！

转载注明：http://blog.youkuaiyun.com/u012294820/article/details/78732771

1.分类

1.sql

1）mysql

2）sqlserver

2.nosql

1）键值对数据库：redis、memcache

2）列存储数据库：hbase

3）文档型数据库：mongdb

4）图形数据库：graph

=============================================================================

2.查询优化

=============================================================================

3.性能优化

调整硬件性能

影响因素：

• 磁盘IO
• 网络IO吞吐量

解决方案：

• 扩大虚拟内存，并保证有足够的可以扩充的空间
• 关闭数据库服务器上不必要的服务
• 数据库服务器和主域服务器分开
• 数据库服务器的吞吐量调为最大

调整数据库

• 只建立一个聚簇索引
• 为经常查询的列都建立索引
• 不要建立太多的索引
• 避免为大型数据类型的列建立索引

使用存储过程

• 尽量使用存储过程

应用程序结构和算法

=============================================================================

4.索引

索引概念

索引 是对数据库表中一个或多个列的值进行排序的结构

优点：

1.大大加快检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

1）大大减少服务器需要扫描的数据量

2）帮助服务器避免排序和临时表

3）将所及I/O变为顺序I/O

缺点：

　　1.索引需要占用数据表以外的物理存储空间

　　2.创建索引和维护索引要花费一定的时间

3.当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。

总结索引使用原则：

1:不要索引数据量不大的表，对于小表来讲，表扫描的成本并不高。

2:不要设置过多的索引，在没有聚集索引的表中，最大可以设置249个非聚集索引，过多的索引首先会带来更大的磁盘空间，而且在数据发生修改时，对索引的维护是特别消耗性能的。

3:合理应用复合索引，有某些情况下可以考虑创建包含所有输出列的复合索引。

4:对经常使用范围查询的字段，可能考虑聚集索引。

5:避免对不常用的列，逻辑性列，大字段列创建索引。

索引类型

普通索引：create index stusno on student（sno），alter table

唯一索引： UNIQUE 例如：create unique index stusno on student（sno）；

表明此索引的每一个索引值只对应唯一的数据记录，对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

主键索引： primary key

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚簇索引：cluster

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。 如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

全文索引：fulltext

CREATE INDEX indextest5 ON textbook (tag5)

ALTER TABLE textbook ADD PRIMARY KEY (timeKey)

ALTER TABLE textbook ADD UNIQUE indextest0 (timeKey)

ALTER TABLE textbook ADD INDEX indextest3 (tag3)

ALTER TABLE textbook ADD FULLTEXT indextest4 (tag4)

ALTER TABLE textbook ADD CLUSTERED indextest6 (tag6)

索引的实现方式

1 B+树 我们经常听到B+树就是这个概念，用这个树的目的和红黑树差不多，也是为了尽量保持树的平衡，当然红黑树是二叉树，但B+树就不是二叉树了，节点下面可以有多个子节点，数据库开发商会设置子节点数的一个最大值，这个值不会太小，所以B+树一般来说比较矮胖，而红黑树就比较瘦高了。

关于B+树的插入，删除，会涉及到一些算法以保持树的平衡，这里就不详述了。ORACLE的默认索引就是这种结构的。

如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

2 散列索引

第二种索引叫做散列索引，就是通过散列函数来定位的一种索引，不过很少有单独使用散列索引的，反而是散列文件组织用的比较多。

散列文件组织就是根据一个键通过散列计算把对应的记录都放到同一个槽中，这样的话相同的键值对应的记录就一定是放在同一个文件里了，也就减少了文件读取的次数，提高了效率。

散列索引呢就是根据对应键的散列码来找到最终的索引项的技术，其实和B树就差不多了，也就是一种索引之上的二级辅助索引，我理解散列索引都是二级或更高级的稀疏索引，否则桶就太多了，效率也不会很高。

3 位图索引

位图索引是一种针对多个字段的简单查询设计的一种特殊的索引，适用范围比较小，只适用于字段值固定并且值的种类很少的情况，比如性别，只能有男和女，或者级别，状态等等，并且只有在同时对多个这样的字段查询时才能体现出位图的优势。

位图的基本思想就是对每一个条件都用0或者1来表示，如有5条记录，性别分别是男，女，男，男，女，那么如果使用位图索引就会建立两个位图，对应男的10110和对应女的01001,这样做有什么好处呢，就是如果同时对多个这种类型的字段进行and或or查询时，可以使用按位与和按位或来直接得到结果了。 MySQL不支持

三种索引实现方式的比较

B+树最常用，性能也不差，用于范围查询和单值查询都可以。特别是 范围查询，非得用B+树这种顺序的才可以了。

HASH的如果只是对 单值查询的话速度会比B+树快一点，但是ORACLE好像不支持HASH索引，只支持HASH表空间。

位图的使用情况很局限，只有很少的情况才能用，一定要确定真正适合使用这种索引才用（ 值的类型很少并且需要复合查询，比如性别），否则建立一大堆位图就一点意义都没有了。

B+树的基本原理

MySQL是怎么用B+树

索引的数据结构

使用索引查询一定能提高查询的性能吗？为什么

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索

对于建立索引的列，数据是均匀分布好还是不均匀分布好

数据均匀分布比较好。最好每条记录的索引列的值各不相同，比如ID，这样查找效率最高。比如年龄，对于千万级的大