【MySQL】InnoDB中的索引

本篇以MySQL中的InnoDB引擎为主

• 建立索引的出发点，该怎么建？
• 索引储存的数据结构？

索引底层的数据结构：B+树

B树与B+树的区别

• 在叶子节点的存储上，B+树存储的是Key和data。而B树任务节点都可以存放Key和Data。
• B+树的叶子节点的引用链指向相邻的叶子结点（节点也就是双向链表哦，页内的记录是单链表哦），而B树的叶子节点都是孤单节点。
• 在范围查询时，B+树只需要对于叶子结点进行遍历，而 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限

InnoDB与MyISAM在B+树使用索引结构的不同？

在MyISAM中，B+树的data域中存储的是 主键索引 + 数据记录的地址（行号）。在索引检索的时候，首先检查 索引文件中的 叶子结点（存储 主键值 + 行号），如果指定的 Key 存在，则根据行号取出 数据文件 相应的记录（按照插入顺序）。（即：在MyISAM中，索引文件跟数据文件是分离的）
在InnoDB中，索引即数据，也就是聚簇索引的那棵B+树的叶子结点存储了所有完整的用户记录。而在其他二级索引时候，跟MyISAM实现的类似，B+树的叶子节点存储的是二级索引列 + 主键。

聚簇索引

在InnoDB 中，聚簇索引就是主键索引。
聚簇索引的特性：

• 使用 记录（数据列的）主键值的大小进行记录排序和页排序
• B+ 树的叶子节点存储的是完整的用户记录
• 目录项是 主键+页号 的搭配。

InnoDB 存储引擎会自动的为我们创建聚簇索引。
在 InnoDB 存储引擎中， 聚簇索引 就是数据的存储方式（所有的用户记录都存储在了 叶子节点 ），也就是所谓的索引即数据，数据即索引。

非聚簇索引

非聚簇索引又称为二级索引，辅助索引。
非聚簇索引的特性：

• 使用记录的二级索引列进行记录排序和页排序
• B+ 树的叶子节点存储的是 记录的二级索引列 + 主键
• 目录项变成了记录的二级索引列 + 页号 的搭配。

也就是说，我们还需要进行一次回表操作。

联合索引

同时为多个列建立索引，也就是以同时以多个列的大小作为排序规则
根据非聚簇索引的特性，我们很容易的得出：
假定，为 c2 和 c3 列建立的索引

• 每条 目录项记录 都由 c2 、 c3 、 页号 这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录 的 c2 列相同，则按照 c3 列的值进行排序。
• B+ 树叶子节点处的用户记录由 c2 、 c3 和主键 c1 列组成。

B+树索引适用的条件

CREATE TABLE person_info(
 id INT NOT NULL auto_increment,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
 PRIMARY KEY (id),
 KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);
1
2
3
4
5
6
7
8
9

• 创建了两颗B+树：聚簇索引 id以及 非聚簇索引 idx_name_birthday_phone_number
• 其中，idx_name_birthday_phone_number 索引对应的 B+ 树中页面和记录的排序方式就是
  • 先按照 name 列的值进行排序。
  • 如果 name 列的值相同，则按照 birthday 列的值进行排序。
  • 如果 birthday 列的值也相同，则按照 phone_number 的值进行排序。

查询

全值匹配

搜索列与索引列完全一致。

SELECT *
FROM person_info 
WHERE name = 'Ashburn' 
	AND birthday = '1990-09-27' 
  AND phone_number = '15123983239';
--  搜索条件的顺序对查询的执行过程没有影响
SELECT * 
FROM person_info 
WHERE birthday = '1990-09-27' 
	AND phone_number = '15123983239' 
  AND name = 'Ashburn';
1
2
3
4
5
6
7
8
9
10
11

匹配左边的列

搜索列只匹配左边的索引列，或包含多个左边的列。
如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中 从最左边连续的列

SELECT * FROM person_info 
WHERE name = 'Ashburn';

SELECT * FROM person_info 
WHERE name = 'Ashburn' 
	AND birthday = '1990-09-27';
1
2
3
4
5
6

下面是个不走索引的例子：是因为 根据B+树组合索引的建立规则：name 列的值不同的记录中 birthday 的值可能是无序的。

SELECT * 
FROM person_info 
WHERE birthday = '1990-09-27';
1
2
3

匹配列前缀

对于字符串类型的索引列来说，我们只匹配 它的前缀也是可以快速定位记录的

SELECT * 
FROM person_info 
WHERE name LIKE 'As%';
1
2
3

下面是个不走索引的例子：如果只给出后缀或者中间的某个字符串

SELECT * 
FROM person_info 
WHERE name LIKE '%As%';
1
2
3

匹配范围的值

如果对多个列同时进行范围查找的话，只有对索引最左边的那个 列进行范围查找的时候才能用到 B+ 树索引

SELECT * 
FROM person_info 
WHERE 
	name > 'Asa' AND name < 'Barlow';
1
2
3
4

对于联合索引 idx_name_birthday_phone_number 来说，只能用到 name 列的部分，而用不到 birthday 列 的部分，因为只有 name 值相同的情况下才能用 birthday 列的值进行排序，而这个查询中通过 name 进行范围查 找的记录中可能并不是按照 birthday 列进行排序的，所以在搜索条件中继续以 birthday 列进行查找时是用不到 这个 B+ 树索引的

SELECT * 
FROM person_info 
WHERE 
	name > 'Asa' AND name < 'Barlow' 
  AND birthday > '1980-01-01';
1
2
3
4
5

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精 确查找，则右边的列可以进行范围查找。

SELECT *
FROM person_info 
WHERE 
name = 'Ashburn'AND 
birthday > '1980-01-01' AND birthday< '2000-12-31' AND 
phone_number > '15100000000';
1
2
3
4
5
6

name = ‘Ashburn’ 和 birthday > ‘1980-01-01’ AND birthday < ‘2000-12-31’ 肯定能够用到索引。
对于 phone_number > ‘15100000000’ ，通过 birthday 的范围查找的记录的 birthday 的值可能不同，所以这个 条件无法再利用 B+ 树索引了，只能遍历上一步查询得到的记录。 （即索引下推哦）

SELECT * 
FROM person_info 
WHERE 
	name = 'Ashburn' 
  AND birthday = '1980-01-01' 
  AND AND phone_number > '15100000000';
1
2
3
4
5
6

避免使用隐式转换

MySQL性能优化：MySQL中的隐式转换造成的索引失效 - 夜月归途
通过上面的测试我们发现 MySQL 使用操作符的一些特性：

1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
2. 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
3. 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

排序

索引列用于排序语句。

必须按照索引列的顺序

可以只使用索引列中左边 的列

对于 联合索引 有个问题需要注意， ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出，或只使用索引列中左边 的列

SELECT * 
FROM person_info 
WHERE 
	name = 'A' 
ORDER BY 
	birthday, phone_number LIMIT 10;
1
2
3
4
5
6

这个索引使用联合索引进行排序是因为 name 列的值相同的记录是按照 birthday , phone_number 排序的

不能ASC、DESC混用

SELECT * 
FROM person_info 
ORDER BY 
	name, 
  birthday DESC 
LIMIT 10;
1
2
3
4
5
6

这个不会使用索引排序，因为：如果使用索引排序的话过程就是这样的：

• 先从索引的最左边确定 name 列最小的值，然后找到 name 列等于该值的所有记录，然后从 name 列等于该值 的最右边的那条记录开始往左找10条记录。
• 如果 name 列等于最小的值的记录不足10条，再继续往右找 name 值第二小的记录，重复上边那个过程，直 到找到10条记录为止。

WHERE 子句中不能出现非排序使用到的索引列

SELECT * 
FROM person_info 
WHERE 
	country = 'China' 
ORDER BY 
	name 
LIMIT 10;
1
2
3
4
5
6
7

这个查询只能先把符合搜索条件 country = ‘China’ 的记录提取出来后再进行排序，是使用不到索引

排序列不能包含非同一个索引的列

SELECT * 
FROM person_info 
ORDER BY 
	name, 
  country 
LIMIT 10;
1
2
3
4
5
6

name 和 country 并不属于一个联合索引中的列，所以无法使用索引进行排序

排序列不能使用了复杂的表达式

索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式

SELECT * 
FROM person_info 
ORDER BY UPPER(name) 
LIMIT 10;
1
2
3
4

使用了 UPPER 函数修饰过的列就不是单独的列啦，这样就无法使用索引进行排序啦。

分组

使用 B+ 树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边 的列进行分组

SELECT name, birthday, phone_number, COUNT(*) 
FROM person_info 
GROUP BY
	name, 
  birthday, phone_number
1
2
3
4
5

回表

用 idx_name_birthday_phone_number 索引为例，下面的这个查询就会用到回表

SELECT * 
FROM person_info 
WHERE 
	name > 'Asa' AND name < 'Barlow';
1
2
3
4

• 从索引 idx_name_birthday_phone_number 对应的 B+ 树中取出 name 值在 Asa ～ Barlow 之间的用户记录。
• 由于索引idx_name_birthday_phone_number 对应的 B+ 树用户记录中只包含 name 、 birthday 、 phone_number 、 id 这4个字段，而查询列表是 * ，意味着要查询表中所有字段，也就是还要包括 country 字段。
• 这时需要把从上一步中获取到的每一条记录的 id 字段都到聚簇索引对应的 B+ 树中找到完整的用户记 录，也就是我们通常所说的 回表 然后把完整的用户记录返回给查询用户。

因此，需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用 二级索引 。

回表较少的数据列

减少回表的次数，可以查询获取较少的记录数会让优化器更倾向于选择使用 二级索引 + 回表

SELECT * 
FROM person_info 
WHERE name > 'Asa' AND name < 'Barlow' 
LIMIT 
	10;
1
2
3
4
5

覆盖索引

为了彻底告别 回表 操作带来的性能损耗，最好在查询列表里只包含索引列，
比如这样：

SELECT 
	name, birthday, phone_number 
FROM person_info 
WHERE 
	name > 'Asa' AND name < 'Barlow'
1
2
3
4
5

索引下推

索引条件下推（Index Condition Pushdown，ICP），就是过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。
例如：对于联合索引 idx_name_birthday_phone_number 来说:

SELECT * 
FROM person_info 
WHERE 
	name > 'Asa' AND name < 'Barlow' 
  AND birthday > '1980-01-01';
1
2
3
4
5

对于上述的查询语句，

1. InnoDB使用联合索引查出所有 'Asa' < name < 'Barlow' 的二级索引数据，假设得到得到10w个主键值；
2. 拿到主键索引进行回表，到聚簇索引中拿到这10w个完整的用户记录；
3. InnoDB把这10w条完整的用户记录返回给MySQL的Server层，在Server层过滤出 birthday >'1980-01-01'的唯一的用户。

而使用了索引下推：
我们第一步已经通过联合索引查出所有 'Asa' < name < 'Barlow' 的10w个数据，而且birthday字段也恰好在联合索引的叶子节点的记录中。这个时候可以直接在联合索引的叶子节点中进行遍历，筛选出birthday >'1980-01-01'记录，找到唯一的那条记录，最后只需要进行1次回表操作即可找到符合全部条件的1条记录，返回给Server层。

如何挑选索引

只为用于搜索、排序或分组的列创建索引

SELECT birthday, country 
FROM person name
WHERE 
	name = 'Ashburn';
1
2
3
4

像查询列表中的 birthday 、 country 这两个列就不需要建立索引，我们只需要为出现在 WHERE 子句中的 name 列创建索引就可以了。

基数大的列建立索引

列的基数 指的是某一列中不重复数据的个数，比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 ，虽然有 9 条 记录，但该列的基数却是 3 。
最好为那些列的基数大的列建立索引，为基数 太小列的建立索引效果可能不好。

索引列的类型尽量小

• 数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带 来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

考虑使用字符列前缀

二级索引的记录中只保留字符串前几个字符。
这样在查找记录时虽然不能精确的定位 到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串 值，再对比就好了。
这样只在 B+ 树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时 间，还大概能解决排序的问题

CREATE TABLE person_info(
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
 KEY 
  idx_name_birthday_phone_number 
  (name(10), birthday, phone_number)
);
1
2
3
4
5
6
7
8
9

name(10) 就表示在建立的 B+ 树索引中只保留记录的前 10 个字符的编码，

索引列在比较表达式中单独出现

也就是索引列进行做了其他操作，例如数值计算、使用函数、（手动或自动）类型转换等操作，会导致索引失效。

1. WHERE my_col * 2 < 4       ×
2. WHERE my_col < 4/2         √
1
2

避免冗余和重复索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。

主键索引拥有 AUTO_INCREMENT 属性

尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况

最左匹配原则

从上面就可以得出来最左匹配原则的定义咯：
在使用组合索引时，

• 搜索列必须全部为索引列
• 搜索列可以为部分索引列，但是部分索引列必须满足顺序性
• 搜索列的范围查询，搜索列的最左边的列才会用到索引
• 排序条件、分组条件与搜索列一致，要么全部为索引列，要么部分索引列必须满足顺序性

相关阅读

联合索引的最左匹配原则_：_https://mp.weixin.qq.com/s/8qemhRg5MgXs1So5YCv0fQ
图解｜索引覆盖、索引下推以及如何避免索引失效：https://zhuanlan.zhihu.com/p/481750465
MySQL是怎样运行的：https://book.douban.com/subject/35231266/