索引

概念

B+树

B+树最上层的是根节点，还包含存放目录记录的数据页（目录页）非叶子节点和存放用户记录的数据页（用户记录页）叶子节点。

目录页和用户记录页的区别在于前者中的目录数据的record_type = 1，后者的用户记录数据的record_type = 0。目录页中映射的索引列的值都是对应用户记录页中索引列的最小值。

聚簇索引

根据主键值的大小(从小到大)进行目录页和记录页的排序（页之间是双向链表，页中的数据是单向链表），B+树的叶子节点存储的是完整的用户数据（包括隐藏列）。InnoDB存储引擎会自动的为我们创建聚簇索引。

二级索引

根据指定列的大小（从小到大）进行目录页和记录页的排序， B+树的叶子节点存储的是指定列的值 + 主键值，相比聚簇索引，二级索引第一次查询得到主键值后，会进行第二次回表查询操作。

二级索引在目录页中存放的记录是指定列 + 主键值 + 目录页码（保证唯一性），这样能够在指定列出现相同值时定位到目录页（叶子节点）。

回表还是全表扫描，这个是由回表的代价决定的，如果第一次查询二级索引（顺序IO）有90%的数据需要回表查询(随机IO)，那么不如直接进行全表扫描（这个是由查询优化器决定的）。

所以更推荐覆盖索引，即查询的列表中只包含索引列。

# 这样就不需要回表查询了，因为查询的字段在二级索引的叶子节点中都存在
SELECT name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_number;

联合索引

本质上也是一个二级索引，现根据A列的大小进行排序，在A列的值相等的情况下根据B列的值进行排序。非叶子节点（目录页）由A列 + B列 + 主键 + 页码组成，同时叶子节点的用户记录由A列 + B列 + 主键列组成。

注意事项

• 每当表创建一个B+树时，都会为这个索引创建一个根节点页面，随着表中插入数据，会先把数据插入根节点中，随着数据量增多，会复制数据到新的页中，并升级为目录页。此过程中根节点地址是不会变的，变的只是角色。
• 一个页面至少存储两条数据。

索引的查询

# 创建表时添加索引
CREATE TABLE demo(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(10),
  PRIMARY KEY(id),
  UNIQUE INDEX idx_name(name) # 创建唯一索引
)
# 修改表添加索引
ALTER TABLE demo DROP INDEX idx_name;
ALTER TABLE demo ADD FULLTEXT INDEX f_name_idx(name);

---

索引适用条件

全值匹配

搜索条件中的列和索引列一致的话，这种情况就称为全值匹配。即使我们不按照联合索引创建的列的顺序查询，也是会走联合索引的（查询优化器）。

最左匹配

在全值匹配的基础上，查询可以不用包含全部的列，只需要包含一个或多个最左边的列就可以（最左匹配原则）。我们按照a-b-c的顺序创建了联合索引，那么a、a-b、a-b-c、a-c（a生效）查询方式都是可以走联合索引的。但b-c是不生效的。

最左匹配原则遇到范围查询就会停止匹配。

前缀匹配

查询的字符串列的前缀都是排好序的，那么只匹配它的前缀也是可以快速定位记录。

SELECT * FROM person_info WHERE name LIKE 'As%'; √
SELECT * FROM person_info WHERE name LIKE '%As%'; ×

针对一些无法使用前缀匹配的字段，比如xxx.com的搜索，我们可以反转字符串然后基于com%进行前缀匹配。

范围匹配

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

基于联合索引（此处只用到了name）找到第一个大于Asa的数据返回主键，回表查询返回给客户端。然后沿着上一步记录所在的链表继续查找，下一条二级索引记录，并判断是否符合小于Barlow，符合就回表查询并返回数据，重复步骤直到条件不符合。

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

基于上步的范围匹配流程得到的结果集，进行birthday > '1980-01-01'再匹配（但不会走联合索引）。

精确匹配某一列与范围匹配另一列

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000'; ×
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND phone_number > '15100000000'; √

name等值匹配可以走联合索引，当name相同时都是按照birthday从小到大的进行排序，所以可以进行birthday的范围匹配，但birthday的范围匹配就无法保证phone_number从小到大排序，所以phone_number只能遍历结果进行筛选，无法走联合索引。

排序

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10; √
SELECT * FROM person_info ORDER BY name asc, birthday desc, phone_number asc; ×
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10; √
SELECT * FROM person_info ORDER BY name, country LIMIT 10; ×
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10; ×

按照联合索引的顺序进行排序（默认联合索引的B+树就按照这个顺序创建）。

1. 如果联合索引中的每个列的查询顺序不一致，那么不能使用索引进行排序。
2. 如果排序中列包含非同一个索引的列，那么不能使用索引进行排序。
3. 排序列使用了复杂的表达式，如UPPER()、REPLACE()等。

分组

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number; √

group by按照联合索引的顺序，索引也是生效的。

如何使用索引

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

• 为基数大（这一列中不重复的数据，基数越大不重复的越多）的列创建索引。

• 索引列的类型尽量的小（减少磁盘占用和提升读写IO）。

• 索引字符串值的前缀，针对列的值特别长的情况（但是基于此列的排序会走文件排序：在内存或磁盘中排序）。

  # 添加字符串前缀索引，只索引前10个字符的编码
  ALTER TABLE person_info ADD INDEX idx_name(name(10));

  通过前缀索引然后定位到相应前缀所在的位置，然后回表匹配完成的字符串。

• 索引列在比较表达式中单独出现（age > 2 √ age * 2 > 10 ×）。

• 主键最好自增，避免因为主键值忽大忽小带来的页分裂问题（性能损失）。

• 避免创建冗余和重复索引。

• 尽量使用索引覆盖（查询索引中的字段）进行查询，避免由回表查询变为全文搜索。

---