MySql索引

一、什么是索引（what）

索引：对数据库中一列或多列的值进行排序的一种结构

二、为什么用索引（why）

当表中由大量记录时，若要对表进行查询

第一种方式，可进行全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作。

第二种方式，在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

优点：

• 索引大大加快数据的检索速度
• 创建唯一性索引，保证数据库表中每一行数据的唯一性。
• 加速表和表之间的连接
• 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

• 额外的占用物理空间
• 对表中数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

三、如何使用索引（how）

B+树索引就是传统意义上的索引，目前关系型数据库系统中查找最为常用和最为有效的索引。

四、B+树

​ B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。

1、插入操作

Leaf Page满	Index Page满	操作
No	No	直接将记录插入到叶子节点
Yes	No	1）拆分Leaf Page 2）将中间的节点放入到Index Page中 3）小于中间节点的记录放左边 4）大于或等于中间节点的记录放右边
Yes	Yes	1）拆分Leaf Page 2）将中间的节点放入到Index Page中 3）小于中间节点的记录放左边 4）大于或等于中间节点的记录放右边 5）小于中间节点的记录放左边 6）大于中间节点的记录放右边 7）中间节点放入上一层Index Page

示例：

• 直接插入28

• 插入70

• 插入95

旋转功能

why

​ 为了保持平衡，对于新插入的键值可能需要做大量的拆分页（split）操作。因为B+树结构主要用于磁盘，所有应该再可能的情况下尽量减少页的拆分操作。

因此，B+树提供了类似与平衡二叉树的旋转功能。

how

​ 旋转发生在Leaf Page已经满，但是其左右兄弟节点没有满的情况下。会先将记录移到所在页的兄弟节点上。

通常情况，做兄弟会被首先检查用来做旋转操作。

示例：

• 准备插入70

• 旋转后

2、删除操作

​ B+树使用填充因子（fill factor）来控制树的删除变化，50%是填充因子可设的最小值。

叶子节点小于填充因子	中间节点小于填充因子	操作
No	No	直接将记录从叶子节点删除，如果该节点还是Index Page的节点，用该节点的右节点代替
Yes	No	合并叶子节点和它的兄弟节点，同时更新Index Page
Yes	Yes	1）合并叶子节点和它的兄弟节点 2）更新Index Page 3）合并Index Page和它的兄弟节点

• 删除70

• 删除25

• 删除60

五、B+树索引

数据库中的B+树索引可以分为聚集索引和辅助索引。

聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

1、聚集索引

​ 聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。

​ 同B+树数据结构一样，每个数据页都通过一个双向链表进行链接。

聚集索引的存储

​ 聚集索引的存储并不是物理上连续的，而是逻辑上连续的。其中有两点：

• 1、页通过双向链表链接，页按照主键顺序排序
• 2、页中的记录通过双向链表进行维护的。物理存储上可以同样不按照主键存储。

​ 聚集索引的另一个好处是，对于主键的排序查找和范围查找速度非常快。

2、辅助索引

概念

​ 也称非聚集索引，叶子节点并不包含记录的全部数据。叶子节点除了包含键值外，索引行中还包含了一个书签（bookmark）。用来告诉InnoDB存储引擎那里可以找到与索引相对应的行数据。

​ 由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

​ 当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引找到一个完整的行记录。

六、B+树索引的使用

1、联合索引

对表上对各列进行索引

假定两个键值的名称分别为a、b

对于条件where a= XX and b= XX
以及 where a= XX 都是可用的
但是 where b= XX 不可用

优势，已经对第二个键值进行了排序

where a=xx order by b可用

2、覆盖索引

覆盖索引（overing index）

从辅助索引中就可以得到查询的记录，不需要查询聚集索引中的记录。

优势：

• 由于覆盖索引是辅助索引不包含整行记录的索引信息，大小远小于聚集索引，减少IO操作。
• 对统计问题而言，由于bug_log表上有辅助索引，辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，优化器自动选择辅助索引。（表现为possible_keys列为null，但实际使用了索引）

SELECT COUNT(*) FROM bug_log

联合索引

SQL查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择联合索引。

3、优化器选择不使用索引的情况

对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的

select count(*) from orderdetails
WHERE orderid>10000 and orderid<102000;

可使用FORCXE INDEX强制使用某个索引

select * FROM orderdetails FORCE INDEX(OrderID)
WHERE orderid>10000 and orderid<102000;