MySQL的索引（四）

索引的出现是为了提高数据查询的效率，在MySQL中，索引是在存储引擎层实现的。

如果一本书没有目录，你如果想要快速找到某个知识点，那可能耗费的时间就比较长。对于数据库的表来说，索引其实就是它的目录。

1.1 常见的索引实现方式

• 哈希表
• 有序数组
• 搜索树

1.1.1 哈希表

一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。

原理：定义一个哈希函数计算key对应数组的位置，然后吧value放在数组的这个位置，当出现hash冲突（多个key值经过哈希函数的换算，会出现同一个值的情况），就在该位置采用头插法拉出一个链表。

下面给出一个具体的实例哈希表的存储和查找过程。

如果有一张用户信息表，里面有两个字段用户身份证和用户姓名，现在需要根据身份证号查找对应的用户姓名，这时哈希索引存储如下所示：

如果需要查找ID_card_n2对应的名字是什么？

1. 将ID_card_n2通过哈希函数算出N；
2. 按顺序遍历，找到User2。

如果需要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户？

只能是扫描全表的数据。

所以说，哈希表这种结构适用于只有等值查询的场景。

1.1.2 有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀

还是上面的例子，下图是有序数组的存储方式：

如果你要查ID_card_n2对应的名字？

在身份证没有重复的前提下，因为是按身份证递增的方式存储的，通过二分法方式能快速得到，时间复杂度O(log(N))。

如果查找身份证号在[ID_card_X, ID_card_Y]区间的User呢？

1. 先用二分法找到ID_card_X（没有就找第一个大于它的user）；
2. 向右遍历，直到找到第一个大于ID_card_Y的身份证号再退出循环。

是不是感觉有序数组在查找上还是很不错的数据结构。但更新就麻烦了，你如果新增一个用户信息，ID_card比原记录中最小的还要小（最坏的情况），那么相当于你原数组所有的值都得往后移，来给新值"腾位置"，成本太高了。

所以有序数组只适合静态存储引擎（比如按年维度存储某个城市的人口信息，这类不会修改的数据）。

1.1.3 二叉搜索树

N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

同样的例子，二叉搜索树对于用户的信息是怎样存储的，如下图所示：

特点：每个节点的左子节点小于父节点，右子节点大于父节点。

如果查找ID_card_n2，只需要按UserA -> UserC -> UserF -> User2这个路径得到，时间复杂度是多少呢？我们来推算一下，

如果有N个节点的二叉搜索树，那么它的高度是[log2N] + 1，[]表示取整；

最好情况下根节点就是目标节点，只需要查找一次，最坏的情况是查找到叶子节点，查找次数就是[log2N] + 1，时间复杂度就是log2N，

运用换底公式：
$${\log }_{2}N=\frac{{\log }_{c}N}{{\log }_{c}2},(c为常数)=\frac{1}{{\log }_{c}2}\ast {\log }_{c}N,(c为常数)$$
所以时间复杂度为O(log(N))，前提得保持这棵树是平衡二叉树，这时更新的时间复杂度也是O(log(N))。

看到这，你可能以为数据库的存储使用二叉树就很好了，实际上大多数的数据库存储却并不使用二叉树。原因是索引不止存在内存中，还要写到磁盘上。

如果有100万节点的平衡二叉树，树的高度就是20，可能最坏的情况一次查询就得访问20个数据块，机械硬盘随机读取寻址的时间是10ms左右，单独访问一行可能就得花费200ms，这是不能忍受的。

为了减少读磁盘的次数，就得想办法增加每个节点子节点的数量来让树高"降低"，所以一般存储是使用的"N叉树"，N取决于每个数据块的大小。如果按每个数据块4kb的大小，以InnoDB的一个整数字段索引为例，这个N差不多是1200，如果树高为4的话，就可以存1200的3次方个值，这已经17亿了，。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。

1.2 InnoDB 的索引

每一个索引在InnoDB里面对应一棵B+树。

例如如下一张表，主键id和字段k，k上有索引：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

	id	k
R1	100	1
R2	200	2
R3	300	3
R4	500	5
R5	600	6

两个索引对应的两棵树如下图所示。

根据叶子节点的内容，索引可分为主键索引和非主键索引。

• 主键索引：叶子节点存的是整行数据，InnoDB中又被称为聚簇索引（clustered index）。
• 非主键索引：叶子节点内容是主键的值，InnoDB中又被称为二级索引（secondary index）。

根据上面两种索引类型，我们看看二者在查询上有什么区别？

• select * from T where ID=500
  

  主键查询只需要搜索ID这棵B+树；

• select * from T where k=5
  

  普通索引查询需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这一过程又被称为回表。

• 显而易见，非主键索引查询需要多扫描一棵索引树，所以在应用中我们应该尽量使用主键查询。

1.3 索引的维护

在插入新值或者删除旧值，需要维护B+树索引的有序性。

还是上面那个例子，下面看两种新增的情况：

1. 插入ID=700的记录；在R5后面继续新增就可以了。

2. 插入ID=400的记录；需要逻辑移动R4和R5之后的数据了。

   如果在移动过程中，R5所在数据页满了，这时需要申请一个新的数据页，然后把R5挪过去，这种情况性能肯定会受影响，这个过程称为页分裂。

   当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并，合并的过程是分裂的逆过程。

上面简要说明了下索引的维护过程，接着我们来探讨一个问题，建表语句里面一定要有自增主键吗？或者说哪些场景应该使用自增主键，哪些场景不该用？

先说下自增主键与业务逻辑字段做主键有哪些优势？主要是在性能和存储两个方面。

1. 从性能角度来看，因为主键id是自增的，所以新增数据场景都是追加操作，不涉及叶子节点的页分裂，新增效率高；业务逻辑主键往往不保证有序插入。
2. 从存储空间角度看，比如在用户信息表中，身份证号是唯一的，当用身份证号做主键时，如果此时name加上了二级索引，那么其叶子节点约占20个字节，而用整型做主键只要4个字节，长整型则是8个字节。（主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。）

由此可以看出什么场景适合业务字段直接做主键？

1. 只有一个索引；
2. 该索引必须是唯一索引。

1.3.1 重建索引

为什么需要重建索引？

上面说过，所以可能因为删除或者页分裂导致数据页有空洞（数据页利用率较低），重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

如何重建索引呢？

alter table T engine=InnoDB

1.4 覆盖索引

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

有如下表初始化语句：

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

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

我们来看下下面这个sql语句的整个查找流程。

select * from T where k between 3 and 5

1. 在k索引树上找到k=3的记录，取得 ID = 300；（k树）
2. 到ID索引树查到ID=300对应的R3；（ID树）
3. 在k索引树取下一个值k=5，取得ID=500；（k树）
4. 回到ID索引树查到ID=500对应的R4；（ID树）
5. 在k索引树取下一个值k=6，不满足条件，循环结束。（k树）

整个过程读了k树3条记录，回表了2次。

如果执行下面这条sql呢。

select ID from T where k between 3 and 5

和上面区别是只查询了ID字段，ID的值已经在k索引树上了，不需要回表，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

比如有张用户信息表，现在有一个高频需求，需要根据用户身份证查他的姓名，这时可以建立一个（身份证号、姓名）的联合索引，不再需要回表查整行记录，减少语句的执行时间。

当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

1.5 最左前缀原则

只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

有了上面的覆盖索引，还是用户信息的表，如果现在要按照市民的身份证号去查他的家庭地址呢，总不能再创建一个（身份证号，地址）的索引吧！如果为每一种查询都设计一个索引，索引是不是太多了。

1.5.1 聚合索引

考虑去建**聚合索引。**索引是否生效需要满足最左前缀匹配原则–以最左边的为起点任何连续的索引都能匹配上

比如一个user表中有id，name，sex，age四个字段，建立 key idx_sex_age_name(sex,age,name)聚合索引，

SELECT * FROM user where age="4"; #未使用索引
SELECT * FROM user where name="2"; #未使用索引
SELECT * FROM user where sex="2" and age="3"; #使用索引
SELECT * FROM user where sex="2" and age="3" and name="4"; #使用索引
SELECT * FROM user where age="3" and name="4";  #未使用索引
SELECT * FROM user where sex="2" and name="4";  #使用索引

最后一个sql你可能会疑惑为什么会走索引，那是mysql优化器的作用，mysql优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

1.5.2 聚合索引如何安排索引内的字段顺序

1. 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。（首要原则）

2. 空间大小考虑

   如果一个表查询，既有a，b字段的联合查询，也有单独b字段的查询，这时候得建立(a,b)和b两个索引，如果a字段比b字段大，建议创建一个(a,b)联合索引和b字段单独索引

1.6 索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

现有一个用户表，有（name, age）联合索引，做如下查询：

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

MySQL 5.6之前执行过程如下图：

MySQL5.6引入索引下推：

两者的区别是引入索引下推后，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。