MySQL索引

MySQL的索引介绍

B+Tree

• 树中的节点并不存储数据本身，而是只是作为索引，数据是存储在叶子节点。
• 我们把每个叶子节点串在一条链表上，链表中的数据是从小到大有序的。（支持按照区间来查找数据）

InnoDB

• InnoDB使用B+Tree数据结构来存储数据。树的高度通常可以达到2-4层，而且每层的节点数量也可以从有限的值中取决（例如**满二叉树（Full Binary Tree）**可以存储2^(h-1)个key，其中h是树的高度）。（如取数据每一层相当于一次IO操作）
• InnoDB 数据读写以页（Device Page）为单位，即一条记录不是从磁盘读出来，而是将整个页读入内存，页的大小为 16K。

主键

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

如何选择主键

每个表必须有一个主键，它也被称为id，并保持一种增长趋势。当为小型系统的时候，可以依赖于 MySQL中的自增主键来实现这个功能；而对于大型系统或分库分表，应该使用内置的 ID 生成器来解决该问题。

• 性能方面：使用自增ID作为主键有以下两个优势

  1. 不需要挪动其他的记录，可以更有效地管理索引。
  2. 无须引起叶子节点的分裂（split）或者合并（merge），这样也大大减少了磁盘I/O操作。

• 空间方面：

  • 若使用较小的主键，那么普通索引叶子节点将会变得更小，占据的空间也会随之减少。

普通索引

普通索引树只保存主键索引的ID。

回表

1

SELECT * FROM T WHERE k BETWEEN 3 AND 5;

如果要获取整条数据需要，在k上的普通索引树查找获取主键ID后需要回到主键索引树。回到主键索引树搜索的过程我们称为回表。

覆盖索引

1
2
3
4
5

-- 在普通索引上找到主键索引的引用后，再去主键索引获取整行数据
SELECT * FROM T WHERE k BETWEEN 3 AND 5;

-- 在普通索引上已经保存了主键的ID，所以不需要再去主键索引查找
SELECT ID FROM T WHERE k BETWEEN 3 AND 5;

索引下推

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

1
2

-- InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。
SELECT * FROM tuser WHERE name LIKE '张 %' AND age = 10 AND ismale = 1;

唯一索引

• 查询过程：唯一索引和普通索引性能差距很小。
• 更新过程：由于普通索引使用的Change Buffer（减少随机读），性能比唯一索引好。
• 所以在”业务代码保证不会重复”的条件可以作为一个性能优化点。

普通索引改成了唯一索引：由于数据页不在内存，需要从磁盘读取到内存，来判断有没有冲突。业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。

联合索引

最左前缀原则

• 当发生联合索引，诸如 (a,b) 和 (a,c) 时，MySQL只能使用第一列字段(a) 来搜索，不能使用第二列(b) 或者第三列(c)单独搜索。可以通过调整顺序避免冗余索引，如果现有 (a,b) 的联合索引，是不需要单独在 a 字段上建立索引的。

• 此外，在指定字段类型时，也要尽量将较大的字段放在前头，以便更有效地利用索引空间。比如：name 字段比 age 字段大，那么就建议你创建一个 (name,age) 的联合索引以及一个 (age) 的单字段索引。

为什么选错索引

由于优化器需要找到一个最优的执行方案以最小的代价去执行语句。需要判断条件有描行数，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

索引选择异常和处理

• 采用 force index 强行选择一个索引。SELECT * FROM t FORCE INDEX (a) WHERE a BETWEEN 10000 AND 20000;
• 修改语句，引导 MySQL 使用我们期望的索引。SELECT * FROM ( SELECT * FROM t WHERE a BETWEEN 1 AND 1000 AND b BETWEEN 50000 AND 100000 ORDER BY b LIMIT 100 ) alias LIMIT 1; （由于b需要排序，降低权重）
• 可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

给字符串加索引

前缀索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 算出这个列上不同email有多少
SELECT count(DISTINCT email) AS L FROM SUser;

-- 依次算出前缀为4，5，6，7不同email有多少
SELECT
  count(DISTINCT left(email, 4) ） AS L4,
  count(DISTINCT left(email, 5) ） AS L5,
  count(DISTINCT left(email, 6) ） AS L6,
  count(DISTINCT left(email, 7) ） AS L7,
FROM SUser;

前缀索引对覆盖索引的影响

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

前缀索引区分度不够如何处理

1. 倒序

1
2

-- 储身份证号的时候把它倒过来存。
SELECT field_list FROM t WHERE id_card = reverse('input_id_card_string');

2. 使用 hash 字段

1
2
3
4

-- 在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
ALTER TABLE t ADD COLUMN id_card_crc int UNSIGNED, ADD INDEX (id_card_crc);
-- 查询: 由于CRC会可能有冲突
SELECT field_list FROM t WHERE id_card_crc = crc32('input_id_card_string') AND id_card = 'input_id_card_string'

倒序和使用hash字段虽然节省了空间，但也不支持范围查询，只能等值查询。

总结

1. 直接创建完整索引：该方法会占用较大的空间；
2. 创建前缀索引：节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引：可以解决字符串本身前缀不够区分度的问题，但也不支持范围扫描；
4. 增加 hash 字段索引：查询性能稳定，但有额外的存储和计算消耗，也不支持范围扫描。

如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号 @gmail.com",而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。

• 因为维护的只是一个学校的，因此前面 6 位（其中，前三位是所在城市编号、第四到第六位是学校编号）其实是固定的，邮箱后缀都是 @gamil.com，因此可以只存入学年份加顺序编号，它们的长度是 9 位。

哪些情况不走索引

条件字段函数操作

常见的不可被索引的where条件有各种的函数类型，例如字符串函数（LPAD()，RPAD()，SUBSTRING() … )， 日期函数，数学函数等。另外，对列的运算也不允许用索引，例如要条件查询一个列为 A+B>20, 是无法使用索引的。

1
2
3
4

-- 不走索引
SELECT count(*) FROM tradelog WHERE month(t_modified) = 7;
-- 修改
SELECT count(*) FROM tradelog WHERE t_modified='2018-7-1’;

隐式类型转换

1
2
3
4
5
6

select '10' > 9
+----------+
| '10' > 9 |
+----------+
| 1        |
+----------+

1. 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
2. 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

1
2
3

SELECT * FROM tradelog WHERE tradeid = 110717;
-- 上面的语句相当于下面做了类型转换
SELECT * FROM tradelog WHERE CAST(tradid AS signed int) = 110717;

隐式字符编码转换

如果两个表的字符串编码不同，会转通过CONVERT(traideid USING utf8mb4)换编码。

慢查询性能问题

• 索引没有设计好
• SQL 语句没写好
• MySQL 选错了索引

数据库设计规范

MySQL 实战 45 讲