数据库索引

数据库索引的实现原理（B+树）

使用B树和B+树的比较

InnoDB的索引使用的是B+树实现，B+树对比B树的好处：

IO次数少：B+树的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存更多的数据，让索引树更加矮胖；

范围查询效率更高：B树需要中序遍历整个树，只B+树需要遍历叶结点中的链表；

查询效率更加稳定：每次查询都需要从根结点到叶结点，路径长度相同，所以每次查询的效率都差不多

使用B树索引和哈希索引的比较

哈希索引能以 O(1) 时间进行查找，但是只支持精确查找，无法用于部分查找和范围查找，无法用于排序与分组；B树索引支持大于小于等于查找，范围查找。哈希索引遇到大量哈希值相等的情况后查找效率会降低。哈希索引不支持数据的排序。

使用索引的优点

大大加快了数据的检索速度；

可以显著减少查询中分组和排序的时间；

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）

缺点：建立和维护索引耗费时间空间，更新索引很慢。

哪些情况下索引会失效？

以“%(表示任意0个或多个字符)”开头的LIKE语句；

OR语句前后没有同时使用索引；

数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；

对于多列索引，必须满足 最左匹配原则/最左前缀原则 (最左优先，eg：多列索引col1、col2和col3，则索引生效的情形包括 col1或col1，col2或col1，col2，col3)；

如果MySQL估计全表扫描比索引快，则不使用索引（比如非常小的表）

创建索引时的注意事项

只应建立在小字段上，而不要对大文本或图片建立索引（一页存储的数据越多一次IO操作获取的数据越大效率越高）；

建立索引的字段应该非空，在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替NULL；

选择数据密度大（唯一值占总数的百分比很大）的字段作索引

索引的分类？

普通索引

唯一索引 UNIQUE：索引列的值必须唯一，但允许有空值；

主键索引 PRIMARY KEY：必须唯一，不允许空值（是一种特殊的唯一索引；MySQL创建主键时默认为聚集索引，但主键也可以是非聚集索引）；

单列索引和多列索引/复合索引（Composite）：索引的列数；

覆盖（Covering）索引：索引包含了所有满足查询所需要的数据，查询的时候只需要读取索引而不需要回表读取数据；

聚集（Clustered）索引/非聚集索引：对磁盘上存放数据的物理地址重新组织以使这些数据按照指定规则排序的一种索引（数据的物理排列顺序和索引排列顺序一致）。因此每张表只能创建一个聚集索引（因为要改变物理存储顺序）。优点是查询速度快，因为可以直接按照顺序得到需要数据的物理地址。缺点是进行修改的速度较慢。对于需要经常搜索范围的值很有效。非聚集索引只记录逻辑顺序，并不改变物理顺序；

分区索引（？）

虚拟索引（Virtual）：模拟索引的存在而不用真正创建一个索引，用于快速测试创建索引对执行计划的影响。没有相关的索引段，不增加存储空间的使用