MySQL 深入浅出索引

索引的常见模型

索引的出现其实是为了提高数据查询的效率，但实现索引的方式却有很多种。

• 哈希表

• 有序数组

• 搜索树

哈希表

哈希表是一种以键值对存储数据的结构，只需要输入待查找的key，就可以找到对应的value。哈希的思路是，把值放在一个数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组换算出来的位置。多个key经过哈希函数换算，不可避免会出现同一个值的情况，这种时候，就用到了链表。

例如一个有着身份证信息和姓名的表，就可能出现如下情况：User2和User4经过哈希运行，都在N位置上了，这是就以链表的方式。

 

哈希表这种结构适用于只有等值查询的场景,因为通过key可以很方便的查询到value，但是如果是范围查询，他需要一个个遍历判断是否符合这个区间，所以向Memcached和Redis这种NoSQL就比较适合。

有序数组

有序数组在等值查询和范围查询场景中的性能就比较优秀了，如果仅仅看查询效率，那么有序数组就是最好的数据结构了，但是，在需要更新数据的时候就麻烦了，往中间插入一个记录，就必须挪动后面所有的记录，成本太高了。

二叉搜索树

二叉搜索树的特点是：父节点左子树所有节点的值小于父节点的值，右字数所有节点的值大于父节点的值。

 

如果要差ID_card_n2的话，按照图中的搜索顺序就是UserA->UserC->UserF->User2这个路径，时间复杂度是O(log(N))。

搜索树

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。为了让一个查询尽量少的读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是使用N叉树，这里的N取决于数据块的大小。

以InnoDB的一个整数字段索引为例，这个N差不多是1200（MySQL默认一个节点的长度为16K，一个整数（bigint）字段索引的长度是8B，另外每个索引还要跟着6B指向其子树的指针，所以是16K/14B≈1170）。这颗树高是4的时候，就可以存1200的3次方，这已经是17亿了。考虑到树根的数据库总是在内存中，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了（第二层大概是1200页，所以1200*16K≈20M，在内存还是有可能放得下的。）。

在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

关于InnoDB的表结构：在InnoDB中，每一张表其实就是多个B+树，即一个主键索引树和多个非主键索引树；执行查询的效率时，使用主键索引 > 使用非主键索引 > 不使用索引，如果不使用索引进行查询，则从主索引B+树的叶子节点进行遍历。

create TABLE T (
    id INT PRIMARY KEY,
    k  INT NOT NULL,
    name VARCHAR(16),
    INDEX(k)
) ENGINE=INNODB;

INSERT INTO t(id,k,name) values(100,1,'R1'),(200,2,'R2'),(300,3,'R3'),(500,5,'R4'),(600,6,'R5')

在MySQL中，索引类型分为主键索引和非主键索引。

 

主键索引是叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引(clustered index)。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引(secondary index)。

基于主键索引和普通索引查询的区别

• 如果语句是 select * from T where ID = 500 即主键查询方式，则只需要搜索ID这颗B+树；

• 如果语句是 select * from T where k = 5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一颗搜索树，因此应该在应用中尽量使用主键查询。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。如果插入新的行ID值为700，那么只需要在R5的记录后面插入一个新纪录。如果新插入的ID为400，那么就相对麻烦了，需要在逻辑上挪动后面的数据，空出位置给ID为400的记录。

由于数据存储是以页为单位的，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作也会影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体控件利用率降低大约50%。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。

自增主键可以避免页分裂，逻辑删除可以避免页合并。

哪些场景应该使用自增主键

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的：

ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT

在插入新纪录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。自增主键的插入数据模式，每次插入一条新纪录都是追加操作，不会涉及到挪动其他记录，也不会触发叶子节点的分裂。

而使用业务逻辑的字段做主键，往往不容易保证有序插入，这样写数据成本相对较高。

从性能和存储空间方面考量，自增主键往往是更合理的选择。

索引查询

create table T1 (
    ID int primary key,
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '',
    index k(k)
)
engine=InnoDB;

insert into T1 values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

在 T1表中，如果执行 select * from T where k between 3 and 5; 需要执行几次树的搜索操作，会扫描多少行？

 

执行流程如下：

1. 在k索引树上找到k = 3的记录，取得ID = 300；

2. 再到ID索引树查到ID = 300 对应行（R3）；

3. 在k索引树取下一个值k = 5,取得ID = 500；

4. 再回到ID索引树查到ID = 500对应行（R4）；

5. 在k索引树取下一个值k = 6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，称为回表。这个查询过程读了k索引树的3条记录（步骤1、3、5），回表了2次（步骤2和4）。由于查询过程所需要的数据只在主键索引上有，所以不得不回表。

覆盖索引

如果执行的语句是select ID from Twhere k between 3 and 5; 这时只需要查ID的值，且ID的值已经在k索引树上了，可以直接提供查询结果，不需要回表。在这个查询里面，索引k已经覆盖了我们的需求，称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

基于上面的覆盖索引，讨论一个问题：在一个用户表上，是否有必要将身份证号和名字建立联合索引？ 

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

身份证号是用户的唯一标识，如果有这么一个高频请求，需要用到身份证号查询他的姓名，那么这个联合索引就很有必要，在这个高频请求上用到覆盖索引，不再需要回表查询整行记录，减少语句的执行时间。

最左前缀原则

如果为每一种查询都设计一个索引，索引是不是太多了。B+数这种索引结构，可以利用索引的“最左前缀”，来定位记录。

例如以姓名和年龄(name,age)建立联合索引

 

索引项是按照索引定义里面出现的字段顺序排序的。当你的需求是查找名字为张三的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。

最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

在建立联合索引的时候，如何安排索引内的字段顺序。因为支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑才用的。

索引下推

在满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这是，又有那些不符合最左前缀的部分呢

以用户表的联合索引(name, age)为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。

select * from tuser where name like '张%' and age = 10 and ismale = 1;

这个语句在搜索索引树的时候，只能用“张”，找到第一个满足条件的记录ID3，然后判断其他条件是否满足，这总比全表扫描要好。在MySQL5.6之前，只能从ID3开始一个个回表，到主键索引上找出数据行，再对比字段值。在MySQL5.6引入的索引下推优化，可以在遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

  

小结

讨论了常见的索引模型，分别有哈希表、有序数组、搜索树等，索引的作用主要是为了提高数据查询效率，而B+Tree这种结构更适合存储。索引类型分为主键索引和非主键索引，主键索引的叶子节点存的是整行的数据（聚簇索引），非主键索引的叶子节点内容是主键的值（二级索引），主键索引只要搜索ID这颗树即可得到整行数据，而普通索引先搜索索引拿到主键值，再到主键索引树搜索一次，这个过程称为回表。同样的，还讨论了数据库索引的概念，包括了覆盖索引、前缀索引、索引下推，当在满足语句需求的情况下，尽量少地访问资源是数据库设计的重要原则之一，在使用数据库的时候，尤其是设计表结构时，也要以减少资源消耗作为目标。