数据库索引

首先看一些基础知识

mysql-index

b tree

问题

带着问题学习。

  • 什么是联合索引？

  • 如何创建？

  • 使用时候的最佳实践

  • 联合索引数据结构是怎么样的？

  • 为什么有最左原则？

联合索引

联合索引又叫复合索引。

对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是 key index （a,b,c）。 可以支持 a 、a,b 、a,b,c 3种组合进行查找，但不支持 b,c 进行查找。

当最左侧字段是常量引用时，索引就十分有效。

两个或更多个列上的索引被称作复合索引。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

创建

  • 语法
create index indexName on  tableName(column1,column2,...,columnN)

如 create idx_un_userid_username on user(id,name)

联合索引可以建立多列(列数大于2)的索引,建议列数最多不要越过3列，超过3列，应重新设计表。

最佳实践

联合索引的优点

A:避免回表

在执行计划中，table access by index rowid代表是回表动作。

如在user的id列建有索引，select id from user这个不用回表，直接从索引中读取id的值，而select id,name from user中，不能返回除id列其他的值，所以必须要回表。

如果建有了id和name列的联合索引，则可以避免回表。

另外，建立了id和name的联合索引(id列在在前)，则select id from user可以避免回表，而不用单独建立id列的单列索引。

这个其实就是 mongo 覆盖查询，道理是一样的。

B:两个单列查询返回行较多，同时查返回行较少，联合索引更高效。

如果 select * from user where id=2 和 select * from user where name='tom' 各自返回的行数比较多，

而 select * from user where id=2 and name='tom' 返回的行数比较少，那么这个时候使用联合索引更加高效。

如何设计

A: 等值查询中，查询条件a返回的条目比较多，查询条件b返回的条目比较多，而同时查询a、b返回的条目比较少，那么适合建立联合索引；

B: 对于有等值查询的列和范围查询的列，等值查询的列建在前、范围查询的列建在后比较实用；

C: 如第3点A中的另外说到，如果联合索引列的前置列与索引单列一致，那么单列查询可以用到索引，这样就避免了再建单列索引，因此联合索引的前置列应尽量与单列一致；

DML 的性能分析

  • 插入

索引越多插入明显慢得多，这是因为记录必须与索引同时更新，而要维护索引那种有序排列的结构，就必须把新增的索引键值插入到特定的位置，而不是随机排放，这里就涉及到重组数据的动作，如果索引块存不下，如果索引块存不下，则还要涉及到扩展索引块的动作，这都需要很大的开销。

相对于有序插入，无序插入时索引的影响更加惊人，因为有序插入时，由于插入的数据有一定的顺序，可以在准备工作后快速扩展新块和批量重组，而对于无序操作，批量则不可能。

  • 删除

删除影响所有的索引，在海量数据库定位删除少量记录时，这个条件列是索引列显然是必要的，但过多的索引还是会影响明显，因为其他列的索引也要更新。在经常要删除大量记录的时候，危害加剧。

另外，delete删除索引后，索引块中的相关需要删除记录只是被打上了一个删除标志而已，并没有真正删除。

  • 更新

更新的影响最小，如果是更新整条记录则与delete类似，如果是修改某列时，则不会触及到其他索引列的维护。

注意点

  1. 超过3个列的联合索引不合适，否则虽然减少了回表动作，但索引块过多，查询时就要遍历更多的索引块了；

  2. 建索引动作应谨慎，因为建索引的过程会产生锁，不是行级锁，而是锁住整个表，任何该表的DML操作都将被阻止，在生产环境中的繁忙时段建索引是一件非常危险的事情；

  3. 对于某段时间内，海量数据表有频繁的更新，这时可以先删除索引，插入数据，再重新建立索引来达到高效的目的。

联合索引的数据结构

单列索引

我们知道单列索引，使用的 B+ Tree 存储。数据结构如下：

单列索引数据结构

多列索引

  • 疑惑

比方说联合索引 (col1, col2,col3)，我知道在逻辑上是先按照col1进行排序再按照col2进行排序最后再按照col3进行排序。

因此如果是select * from table where col1 = 1 and col3 = 3的话，只有col1的索引部分能生效。

但是其物理结构上这个联合索引是怎样存在的，我想不懂。

  • 解答

假设这是一个多列索引(col1, col2,col3)，对于叶子节点，是这样的：

多列索引数据结构

PS:该图改自《MySQL索引背后的数据结构及算法原理》一文的配图。

也就是说，联合索引(col1, col2,col3)也是一棵B+Tree，其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

配图可能不太让人满意，因为col1都是不同的，也就是说在col1就已经能确定结果了。

  • 更合适的图

自己又画了一个图（有点丑），col1表示的是年龄，col2表示的是姓氏，col3表示的是名字。

如下图：

更合适的图

PS:对应地址指的是数据记录的地址。

如图，联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。

且是按照年龄、姓氏、名字的顺序排列的。

执行过程

因此，如果执行的是：

select * from STUDENT where 姓氏='李' and 名字='安'

或者

select * from STUDENT where 名字='安'

那么当执行查询的时候，是无法使用这个联合索引的。

因为联合索引中是先根据年龄进行排序的。如果年龄没有先确定，直接对姓氏和名字进行查询的话，就相当于乱序查询一样，因此索引无法生效。

因此查询是全表查询。

如果执行的是：

select * from STUDENT where 年龄=1 and 姓氏='李'

那么当执行查询的时候，索引是能生效的，从图中很直观的看出，age=1的是第一个叶子节点的前6条记录，在age=1的前提下，姓氏=’李’ 的是前3条。因此最终查询出来的是这三条，从而能获取到对应记录的地址。

如果执行的是：

select * from STUDENT where 年龄=1 and 姓氏='黄' and 名字='安'

那么索引也是生效的。

而如果执行的是：

select * from STUDENT where 年龄=1 and 名字='安'

那么，索引年龄部分能生效，名字部分不能生效。也就是说索引部分生效。

因此我对联合索引结构的理解就是B+Tree是按照第一个关键字进行索引，然后在叶子节点上按照第一个关键字、第二个关键字、第三个关键字…进行排序。

最左原则

而之所以会有最左原则，是因为联合索引的B+Tree是按照第一个关键字进行索引排列的。

参考资料

联合索引数据结构

  • 联合索引

mysql联合索引

【索引】联合索引的基本知识

SQL联合索引 与 单一列的索引

  • 测试文章

本文通过实际测试，求知精神值得学习

联合索引使用规则