MySQL索引优化

系列文章目录

一、MySQL数据结构选择
二、MySQL性能优化explain关键字详解
三、MySQL索引优化
四、MySQL事务
五、MySQL锁机制
六、MySQL多版本并发（MVCC）机制

一、索引下推

  索引下推是MySQL 5.6之后引入的优化，核心思想是将查询中的一部分条件，特别是非索引列上的条件， 提前应用到索引扫描的阶段，而不是等到读取数据行后再进行处理。
  例如对于name,age,position作为联合索引的表，例如SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'这条语句，实际只有name会走索引，因为name是右侧模糊匹配，得到的结果是不确定的，name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。在5.6之前，MySQL会拿着利用联合索引里匹配到名字是 ‘LiLei’ 开头的索引获得的主键ID，去回表查询，再去比对age和position。而5.6引入索引下推后，上面的sql在获取到联合索引里匹配到名字是 ‘LiLei’ 开头的索引后，还会对age和position进行过滤，用过滤后剩下的索引对应的主键id再回表查整行数据。
  再看另一个直观一些的例子：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  position VARCHAR(50)
);

CREATE INDEX idx_name_position ON employees(name, position);

  使用如下的语句进行查询：

EXPLAIN SELECT * FROM employees
WHERE name = 'John'
  AND position = 'Manager'
  AND age > 25;

  因为name和position是联合索引，age字段不会走索引，在没有索引下推的情况下，会获取到所有同时满足name和position条件的记录的主键ID，然后用该主键ID去回表查询出真正的记录行，再去记录行中筛选出age > 25的记录。而引入了索引下推时，则可以将age > 25的条件筛选下推到索引扫描阶段，即获取到所有同时满足name和position和age条件的记录的主键ID，然后用该主键ID去回表查询出真正的记录行。

二、文件排序

  当MySQL需要按照某个字段（或多个字段）进行排序时，如果该字段没有索引或索引不能完全满足查询要求，MySQL 就会使用文件排序。这种排序操作的名称源于 MySQL 在执行排序时，可能会将数据加载到内存中或临时存储在磁盘上进行排序，类似于对文件进行排序。也就是EXPLAIN 输出中的 “Using filesort”。文件排序通常会有单路排序和双路排序的情况。

• 单路排序通常是在数据量较小且可以完全加载到内存中的情况下使用。在单路排序中，所有数据会先被读取到内存中，然后在sort buffer中通过归并排序或堆排序等算法进行排序。
• 双路排序类似于回表，首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段。

  举例说明单路排序和双路排序的流程：

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position

  单路排序，每次都回表。

1. 根据name = 'xx’的条件，在索引树上找到该条记录的id。
2. 根据id去查询该行完整的数据，放入sort buffer。
3. 重复上面的过程，直到不满足name = 'xx’的条件。
4. 对sort buffer中的记录根据position进行排序，返回结果。

  双路排序，最后统一去回表。

1. 根据name = 'xx’的条件，在索引树上找到该条记录的id。
2. 根据id去查询该行完整的数据，把position和id放入sort buffer。
3. 重复上面的过程，直到不满足name = 'xx’的条件。
4. 对sort buffer中的position和id根据position进行排序。
5. 根据id的值回到原表中取出所有字段的值返回给客户端。

  即:单路排序每次都会去回表，把需要查询的数据都放到 sort buffer 中，最后统一进行排序。而双路排序只会把主键和需要排序的字段放到 sort buffer 中，最后根据排序字段对主键进行排序进行排序，然后再通过主键回到原表查询需要的字段。

二、Order by与Group by优化

  还是以上一篇的employees表为例，该表有一个idx_name_age_position联合索引，以及ID主键索引。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 22 order by position

  上面的sql语句走了name和age的索引。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position

  上面的sql语句只走了name的索引，并且根据最左前缀法则，中间的age缺失，还出现了文件排序，效率低。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position,age

  上面的sql依旧只走了name索引，并且出现了文件排序。因为age和position没有按照顺序。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by age,position

  上面的sql依旧只走了name索引，但是没有出现文件排序。因为顺序满足要求。但是，如果对于age asc,position desc的情况，会出现文件排序。
  综上所述，对于order by的优化，尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。并且尽量用覆盖索引。

三、分页查询优化

  假如有一条sql：select * from employees limit 10000,10;，它的执行过程是，首先查出10010条数据，然后将前10000条数据丢弃。而不是简单的在10000条数据后查出10条数据。这就是为什么数据量多的时候，页数越往后，查询的速度就越慢的原因。针对分页进行优化，如果主键是自增且连续的，可以通过这样的语句：

select * from employees where id > 10000 limit 10;

  但是，如果将表中的某些记录删除，会导致和select * from employees limit 10000,10查询结果不一致。
  最常规的优化方法，可以采用自连接的方式，即让查询出10010条记录的结果集尽量小（只查出了id，而非*），再用该结果集去查询出该行完整的记录：

select * from employees e inner join (select id from employees limit 10000,10) ed on e.id = ed.id;

四、Join优化

  假设有如下的表

-- 创建 t1 表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建 t2 表，结构与 t1 相同
CREATE TABLE `t2` LIKE `t1`;

-- 插入数据到 t1 表的存储过程
DROP PROCEDURE IF EXISTS insert_t1;
DELIMITER ;;
CREATE PROCEDURE insert_t1()
BEGIN
  DECLARE i INT;
  SET i = 1;
  WHILE i <= 10000 DO
    INSERT INTO t1(a, b) VALUES(i, i);
    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;

-- 调用存储过程插入数据
CALL insert_t1();

-- 插入数据到 t2 表的存储过程
DROP PROCEDURE IF EXISTS insert_t2;
DELIMITER ;;
CREATE PROCEDURE insert_t2()
BEGIN
  DECLARE i INT;
  SET i = 1;
  WHILE i <= 100 DO
    INSERT INTO t2(a, b) VALUES(i, i);
    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;

-- 调用存储过程插入数据
CALL insert_t2();

  Join通常会采用Nested-Loop Join (NLJ) 或 Block Nested-Loop Join (BNLJ) 算法：
  例如：

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a

  两张表都走了索引，t2表（小表）驱动了t1表（大表）：

1. 先从t2读取一条记录
2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端

  也就是整个过程扫描了100+100 = 200行
  而对于：

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

  b不是索引字段，没有走索引，采用了Block Nested-Loop Join (BNLJ) 算法

1. 把 t2 的所有数据放入到 join_buffer 中。
2. 将 t1 中的每一行数据拿出来跟 join_buffer 中的数据做对比。
3. 返回满足 join 条件的数据。

  整个过程扫描了10000 + 100 = 10100 次。并且在 join_buffer 中 ，t2的每1行，都要和t1全表的数据对比一次，也就是最终对比了100 * 10000 = 1000000 次。这也体现出了为什么要用小表驱动大表，因为 join_buffer 也是有大小限制的，虽然最终对比的结果都是1000000 次，但是如果将t1所有的数据都放到join_buffer 中，会触发多次分段的策略。
  综上所述，对于join的优化，最好的方式就是连接的字段走索引，并且小表驱动大表。

五、in 和 exist 优化

  先说结论，在子查询数据量较小的情况下，in的性能高于exist：

• IN 会首先计算子查询的结果集（即所有匹配的值），然后将外层查询的每个记录与子查询的结果进行比较。这意味着内层子查询会返回一个结果集，外层查询的每一行都要与这个结果集进行比较。
• EXISTS 检查子查询是否至少返回一行数据。它通常会停止执行子查询，一旦找到第一个匹配的记录，子查询就会返回 TRUE。因此，EXISTS 子查询通常不需要处理所有子查询结果，只要找到匹配就会短路退出。

  优化的原则依旧是小表驱动大表

六、count查询优化

  这四条语句有何区别？

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

  count(name)不会统计null值。但是count(1)无需取出具体的字段去统计，而是用常量统计，所以理论上会比count(*)和count(字段)性能要高一些。
  如果表中的行数较多，又需要进行计数，在对于精确度要求不高的场景下，可以使用show table status命令，查看rows字段的值。如果需要精确知道行数，可以将总数维护到redis，或者专门用另一张表维护计数。

七、其他优化

  下面再列举一下日常需要注意的点：

1. 对于没有负数的整型，推荐使用UNSIGNED无符号类型。
2. 对于小数，建议使用DECIMAL类型，保证精度。
3. 对于字符串，长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
4. 对于日期时间，推荐使用TIMESTAMP，比DATETIME更节约空间。但是相对应的，TIMESTAMP的时间上限是2038-01-19 03:14:07,而DATETIME无需考虑时间上限。