MySQL 索引基本常识

本文摘自微信公众号。

• 什么是索引？
• 索引为什么查询快，索引的数据结构是什么？
• 聚簇索引/非聚簇索引区别？
• 什么是覆盖索引？
• 唯一索引/普通索引？
• 单列索引/联合索引区别？Full-index全文索引？
• 什么是下推索引？
• 什么是最左匹配，查询回表？哪些字段适合建索引？
• 为什么一般主键索引最好是自增长的, 尽量短的数值类型？
• 为什么有些SQL不走索引？索引的最佳实践？

1. 索引为什么快

索引的本质是空间换时间。

• +bonus: 加快检索速度，加快多表连接
• -price: 额外空间开销，维护索引的额外时间开销
  所以我们通过索引这个缓存来提高数据查询的效率。

假如我们自己设计数据库索引的话，我们会选取什么样的数据结构呢？下面我们来分析下各种查询常见的数据结构的性格，看看选谁是最合适的人选。

2. 数据结构比较

• 有序数组：等值查询和范围查询场景中的性能就都非常优秀。特定值查询用二分法就可以快速得到，这个时间复杂度是 O(log(N))。类似between[x, y]的 范围查询 也比较快，先用值查询二分法找到x, 然后向后遍历，知道找到y。但是他最大的问题是插入或者删除一个新数据，这个新数据后面的整个数组都需要挪动，复杂度是O(N)。
• HashMap：虽然可以快速定位，值查询的时间复杂度是O(1), 但是Hashmap没有顺序，进行范围查询的话复杂度高是O(N)。
• 二叉树查找树BST：二叉树的高度不均匀，不能自平衡，查找效率跟数据量有关（树的高度），在极端情况下（插入数据本身就是有序的）这棵树就退化成链表了，查询实际复杂度是O(N)
• 红黑树：是平衡的BST，性能稳定在O(logN), 但因为是二叉树，树的高度随着数据量增加而增加，并且需要再平衡。适合数据都在内存的情况，比如Java里的HashMap。但是在硬盘寻址的场景下IO成本会比较高。
• B-Tree：相比二叉树来说是一种多路平衡查询树，但是B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
• B+Tree: 从物理存储结构上说是N叉树，B-Tree和B+Tree都以页(4K)来划分节点的大小，但是由于B+Tree的中间节点（非叶子节点）不存储数据，存的是索引信息，索引包含Key和Point指针。因此B+Tree能够在同样大小的节点中，存储更多的key，提高查找效率。

每一个索引在 InnoDB 里面对应一棵 B+ 树。以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 ^(4-1) 个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。

3. 聚簇索引/非聚簇索引

区别主要看叶子节点存了什么数据：

• 在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。

• 而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

聚簇索引查询相对会更快一些，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询（这个过程叫做回表, 也就是查了2个索引树）。

4. 覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。覆盖索引不是索引树，是一个结果。当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

例如表T中有一个普通索引 idx_key(key)，那么：

-- 索引覆盖了
select id from T where key = 'test';

-- 索引没覆盖，需要回表
select * from T where key = 'test';

问题，为什么第一个SQL索引覆盖了? 非聚簇索引的叶子节点存的是id。

5. 唯一索引/普通索引

唯一索引和普通索引在查询和更新的时候区别：

• 唯一索引找到满足的第一条记录会立马返回，通知检索（因为唯一性的保证）。但是这个区别并没有很大的性能区别，因为Innodb是按照页（默认16KB）读写的，读数据的时候是从B+树的根节点开始搜索，搜索的时候将整个页从硬盘加载到内存。
• 唯一索引在插入的时候会多做些判断，想要做这个判断就必须先把数据页读入内存。但是普通索引不需要做这个判断，就可以把需要更新的数据做判断：如果数据在内存则直接更新；如果不在也不加载内存，而是先写入change buffer，等下次查询的时候再执行change buffer。这样普通索引会相对性能好一些。但是注意：如果业务场景是写入后立马有查询，其实还是会立马需要把数据页加载到内存，这样的情况下其实并不能带来优化IO的操作。

6. Full-index全文索引

Mysql 5.6 引入了全文索引Full text index，但是只能适用于分词的情况，如果是匹配字符串的一部分就不适用了。

7. 单列索引/联合索引

对于一个表里的多个列，比如是有些列高频查询，有些列低频查询。如果为每一个低频的列单独建立索引感觉有些浪费，如果不建立索引又只能走全表扫描。

所以我们经常用联合索引来解决这个问题，联合索引如idx_key1_key2_key3(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，那么在建立联合索引的时候，如何安排索引内的字段顺序？

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用
按照字段在查询条件中出现的频度建立索引
我们考虑key1 是最常用的列放最前面，key2和key3不常用。

上面这种建立一个联合索引就实际上包含了3个索引的特性就是最左匹配原则。这个最左匹配可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

总结起来

• 索引的匹配规则是左匹配的
• 只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用
• 有了(A,B,C)，就等于同时拥有了(A)，（A,B）和 (A,B,C) 三个索引
• 只要索引内，开始用范围查询，后面的索引就失效了。**这里注意：**IN 在 where 中，也属于准确查询，不会使后面索引失效。

8 什么是下推索引？

在MySQL 5.6中，引入了Index Condition Pushdown Optimization 优化。本质是针对那些需要回表查找的部分如果索引里已经包含了该列，那么先在索引里做过滤判断。

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

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

我们已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好。然后呢？当然是判断其他条件是否满足。在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

9. 哪些字段适合建索引?

• 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
• 并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
  多表 join 的关联列

10. 为什么有些SQL不走索引？

• 使用了通配符开头，NOT IN 语句或者
• 联合索引的第一个字段查询条件中
• 数据引擎的优化器选错了索引（可以适当使用 force index 语句来优化）

11. 为什么一般主键索引最好是自增的, 尽量短的数值类型？

• 自增

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。
由于InnoDB索引的特性，因此如果主索引不是自增的(id作主键)，那么每次插入新的数据，都很可能对B+Tree的主索引进行重整，影响性能。因此，尽量以自增id作为InnoDB的主索引。
这就是为什么主键的Id需求一般是整体趋势递增的原因。

• 短数

每个非主键索引的叶子节点上都是主键的值。如果用UUID，比如 b8a52179-7d54-46de-b1de-d88911a42790 做主键，那么每个二级索引的叶子节点占用约 36字节，而如果用整型做主键，则只要 4字节，如果是长整型（bigint）则是 8字节。所以，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
利用了twitter的雪花算法来尽量做到生成短数字且趋势自增的的ID。

12. 索引的最佳实践？

• 要建索引

1. 定义主键的数据列一定要建立索引。
2. 定义有外键的数据列一定要建立索引。
3. 对于经常查询的数据列最好建立索引。
4. 对于需要在指定范围内的快速或频繁查询的数据列;
5. 经常用在WHERE子句中的数据列。
6. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

• 不要建索引

1. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
2. 对于定义为text、image和bit的数据类型的列不要建立索引。
3. 对于经常存取的列避免建立索引

• 索引的坑

1. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
2. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

• 索引不会包含有NULL值的列

1. 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL
2. 使用短索引（列内容越短越好）
   对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

• 索引列排序

1. MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

• like语句操作

1. 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。即：左匹配规则。可以使用reverse函数来支持逆序匹配，从而增强like走索引的可能。

ALTER TABLE `T` ADD `reverse_identifier` VARCHAR(255)  CHARACTER SET utf8  COLLATE utf8_general_ci;

select * from T where reverse_identifier like reverse('%SDTE');

• 不要在列上进行运算

select * from users where YEAR(adddate)<2007;

1. 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成 select * from users where adddate<‘2007-01-01’;

• 不使用NOT IN和<>操作

1. 因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。因为在以通配符 % 和 _ 开头作查询时，MySQL不会使用索引。