Mysql索引优化学习记录

联合索引结构

联合索引跟普通索引一样，底层都是一颗B+树。

MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。

最左前缀法则

联合索引的结构，也决定了查询的时候，必须遵守最左前缀法则，才能使用到索引。这指的是：查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM employees WHERE age = 26 AND position ='java开发工程师'; 
EXPLAIN SELECT * FROM employees WHERE position = 'java开发工程师'; 
EXPLAIN SELECT * FROM employees WHERE name = 'lili';

这三条SQL中只有第三条SQL能用到索引，因为索引中第一列是 `name`，根据最左前缀法则，查询条件中必须要有 `name` 这一列，否则是用不到这条索引的。这个联合索引的结构所决定的，查询的时候按建索引的字段顺序逐个比较，所以如果没有第一列，那么无法使用索引。同理，如果只有第一列跟第三列，那么只能根据第一列来查找，第三列无法生效。

初始化数据

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (1, 'xujingyi', 28, 'java开发工程师', '2021-09-01 20:00:00');
INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (2, 'lili', 27, 'java开发工程师', '2021-09-12 20:00:00');
INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (3, 'lucy', 25, '财务', '2021-10-08 20:00:00');
INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (4, 'luna', 20, '软件测试', '2021-11-24 20:00:00');

表中有一条索引 idx_name_age_position，包含 `name`,`age`,`position`三个字段。

SQL基本优化

全值匹配

索引长度越多，过滤数据起来越高效。

假设索引中有三个字段：(`name`,`age`,`position`)，查询的时候，使用 name + age + position 三个条件查询，会比只使用 name + age，或者单独使用 name 进行过滤，要高效。

不在索引列上做任何函数操作

如果对查询的列，做了任何函数操作，那么mysql不会走索引，会转为全表扫描。

EXPLAIN SELECT * FROM employees WHERE name = 'lucy'; 
EXPLAIN SELECT * FROM employees WHERE left(name, 3) = 'lucy';

例如第二条 SQL 中对 name 列加了一个 left 函数，那就不会走索引。

尽量使用覆盖索引，减少 select * 语句

也就是说查询的所有列数据均在一棵索引树上，这样不需要访问实际的行记录。explain 时，Extra 列结果就是 Using index。可以一定程度上提升效率

尽量避免使用不等于（!= 或者 <>）

mysql在使用不等于（!= 或者 <>）时无法使用索引，会导致全表扫描。

尽量避免使用 is null, is not null

mysql在使用 is null,is not null 时也无法使用索引。

like关键字查询时，记得通配符%不要加在左边

还是由联合索引的结构决定，如果连最左边都确定不了，mysql是无法使用索引的。

根据字符串查询时，记得要加单引号

EXPLAIN SELECT * FROM employees WHERE name = '1000'; 
EXPLAIN SELECT * FROM employees WHERE name = 1000;

这两条SQL，第一条会走索引，第二条不会走，因为 mysql 发现搜索的列是字符串类型，然而传的值确是数字类型时，会自动调用一个函数。而一旦对查询的列使用了函数，那么mysql就不会使用索引。

少用or或in，用它查询时，mysql不一定使用索引

mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

范围查找优化

mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。有时候如果单次数据量查询过大，优化器可能最终选择不走索引。

优化方法：可以讲大的范围拆分成多个小范围。

Mysql如何选择合适的索引

EXPLAIN select * from employees where name > 'a';

对于这条SQL，正常来讲是可以使用索引的，因为 name 列是索引的第一列，符合最左前缀法则。

但是这也不是绝对的，这会受到你查询的字段，表中数据条数，表中那一列某个值的数量等因素的影响。如果我表里的数据量，或者其他因素不一样，explain 出来的结果可能就是没有使用索引，反而使用的是全表扫描。

Mysql 会在内部进行一些估算。比如全表扫描，主键索引，辅助索引，估算它们各自需要扫描的行数，查询成本(cost)。通过分析全表扫描以及各个索引的查询成本，来决定是否使用索引或者选择合适的索引。如果发现全表扫描成本反而更低，那就不用走索引了。

如果想查看 mysql 对 sql 的内部分析，查看全表扫描以及各个索引的查询成本等信息，可以使用 mysql 的 trace 工具。

SQL深入优化

order by 与 group by 优化

1）场景1

EXPLAIN SELECT * FROM employees WHERE name = 'lili' and position = 'java开发工程师' order by age;

利用最左前缀法则：中间字段不能断，从 key_len=74 也能看出，查询的时候索引只用到了 name 列。而 age 索引列用在排序过程中，因为 Extra 字段里没有 using filesort 。

2）场景2

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

从 explain 的执行结果来看：key_len=74，查询只使用了 name 索引。由于通过 position 进行排序，但跳过了 age，出现了 Using filesort。

3）场景3

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

查询只用到了 name ，排序用到了 age 和 position。无 Using filesort。

4）场景4

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

跟场景3的区别是出现了 Using filesort，因为 position 和 age 顺序颠倒了，跟索引顺序不匹配。

5）场景5

EXPLAIN SELECT * FROM employees WHERE name = 'lili' and age = 26 order by position,age;

根据 key_len = 78 可知查询时用到了 name 和 age 索引。

在 Extra 中并未出现 Using filesort，因为 age 为常量，在排序中被优化，所以索引未颠倒， 不会出现 Using filesort。 

6）场景6

EXPLAIN SELECT * FROM employees WHERE name = 'lili' order by age asc, position desc;

虽然排序的字段列与索引顺序一样，且 order by 默认升序，这里 position desc 变成了降序，导致与索引的排序方式不同，从而产生 Using filesort。

多列排序时，只能全部升序或者全部降序，才能用到索引。这也是由组合索引结果决定的。

Mysql8以上版本有降序索引可以支持该种查询方式。 

7）场景7

EXPLAIN SELECT * FROM employees WHERE name in ('lili', 'lucy', 'luna') order by age,position;

只在查询的时候用到了 name 索引。排序的时候没有用到索引，即使 age 和 position 顺序正确。

对于排序来说，name in ('lili', 'lucy') 也是范围查询。

根据 name = 'lili' ，name = 'lucy' 以及 name = 'luna' 查询出来的结果，他们的 age 可能是无序的，没法走索引。

优化总结： 
1、MySQL支持两种方式的排序 Using filesort 和 Using index。Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。 
2、order by 满足两种情况会使用 Using index。 
  -- 1) order by 语句使用索引最左前列。 
  -- 2) 使用 where 子句与 order by 子句条件列组合满足索引最左前列。 
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。 
4、如果 order by 的条件不在索引列上，就会产生 Using filesort。 
5、能用覆盖索引尽量用覆盖索引 
6、group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 ·order by null· 禁止排序。注意，where 高于 having，能写在 where 中的限定条件就不要去 having 限定了。

分页查询优化

limit的弊端

先看一条 SQL：

select * from employees limit 10000,10; 

这条 SQL 看似只查询了10条数据，而实际上这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率 是非常低的。

如何优化？

看一条查询SQL：

EXPLAIN select * from employees ORDER BY name limit 9000,5;

查询耗时 0.059s

可以发现，并没有使用 name 字段的索引！原因是：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。我这里一共10000条记录，只查找5条数据，却要先在辅助索引上查询出9005条数据，经过筛选后，再回到主键索引上取出其他字段的值，要遍历多个索引树。mysql 经过计算成本，发现还不如全表扫描呢，就会走全表扫描。

如果这里换成 limit 0,5 ，那么就会走索引了，因为只需要扫描前5条记录了，而不是 9005条。

其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

SQL改写如下：

explain select * from employees e inner join (select id from employees order by name limit 9000,5) ed on e.id = ed.id;

查询耗时 0.019s，显然，比原 SQL 快了不少，而且原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。 

join关联查询优化

mysql的表关联常见有两种算法

• Nested-Loop Join 算法 
• Block Nested-Loop Join 算法

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

# 字段a有索引
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

根据执行计划可以看出： 

• 驱动表是 t2，被驱动表是 t1。先执行的是驱动表（执行计划中，id 一样的话，按从上到下顺序执行 sql）。这里跟 t1，t2 哪个写在前面是没有关系的。优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
• 表关联时使用了 NLJ 算法。一般 join 语句中，如果执行计划 Extra 中未出现 'Using join buffer'，则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下： 

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

整个过程会读取 t2 表的所有数据（100行），然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行。它会扫描 100 次  t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100 行。因此整个过程扫描了 200 行。 

但是如果被驱动表的关联字段没索引，就没法走索引了，t1表就不是扫描100次那么简单了，使用NLJ 算法性能会比较低，mysql 会选择 Block Nested-Loop Join 算法。

2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL) 算法 

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

# 字段b没有索引
EXPLAIN select*from t1 inner join t2 on t1.b= t2.b;

Extra 中 的 Using join buffer (Block Nested Loop) 说明该关联查询使用的是 BNL 算法。

上面sql的大致流程如下： 

1. 把 t2 的所有数据放入到 join_buffer 中（内存中） 
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比 
3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。

由于 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100万次。

如果上面第二条 sql 使用 NLJ，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。而 BNL 的磁盘扫描次数是 10100，比 NLJ 小很多。BNL 的 100万次计算是在内存中进行的，会比磁盘扫描快得多！ 

因此，MySQL 对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法。

关联sql优化建议

• 关联字段加索引，让 mysql 做 join 操作时尽量选择 NLJ 算法 
• 小标驱动大表，写多表连接 sql 时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去 mysql 优化器自己判断的时间

straight_join解释：

// 指定 mysql 选中 t2 表作为驱动表
select * from t2 straight_join t1 on t2.a = t1.a;

straight_join 只适用于 inner join，并不适用于 left join，right join。（因为left join，right join已经代表指定了表的执行顺序）

使用 straight_join 一定要慎重，人为指定的执行顺序并不一定会比优化引擎要靠谱，所以除非是自己非常确定，不然还是让 mysql 优化器自己去选择比较靠谱。

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集

in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)
# 等价于： 
for (select id from B) { 
  select * from A where A.id = B.id 
}

exists：当A表的数据集小于B表的数据集时，exists优于in 
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留。

select * from A where exists (select 1 from B where B.id = A.id) 
# 等价于: 
for(select * from A){ 
  select * from B where B.id = A.id 
}

1、EXISTS(subquery)只返回 TRUE 或 FALSE ,因此子查询中的 SELECT * 也可以用 SELECT 1 替换，官方说法是实际执行时会忽略SELECT清单，因此没有区别；
2、EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比 
3、EXISTS 子查询往往也可以用 JOIN 来代替，何种最优需要具体问题具体分析

count(*)查询优化

现在有4条SQL，其中，id是主键索引，name是辅助索引

EXPLAIN select count(1) from employees; 
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees; // 不会统计null的数据
EXPLAIN select count(*) from employees;

这四条SQL执行的结果可以看到，使用的key都是idx_name_age_position，也就是辅助索引。即便是select(id)，使用的也是辅助索引。

为什么Mysql选择的是辅助索引，而不是主键索引呢？因为二级索引相对于主键索引来说，存储的数据更少，检索性能也就更高。

其实对于select(*)而言，没什么好优化的，mysql 底层已经尽量优化过了，这四种方式速度都差不多，当然，select(name) 这种由于会忽略null的数据，所以不会使用它。一般推荐使用 select(*) 就行了。