Mysql索引优化实战

只要平凡丶

已于 2022-08-16 11:25:26 修改

阅读量640

点赞数 1

文章标签： mysql 数据库 java

于 2022-07-08 15:38:51 首次发布

本文链接：https://blog.csdn.net/weixin_45161367/article/details/125677670

版权

sql案例

一、联合索引第一个字段用范围不会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'zhangsan' AND age = 22 AND position ='manager';

请添加图片描述
结论：mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描。

如果强制走索引：

-- 先关闭查询缓存
set global query_cache_size=0;  
set global query_cache_type=0;
-- 执行时间0.333s
SELECT * FROM employees WHERE name > 'zhangsan';
-- 执行时间0.444s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'zhangsan';

虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高。
覆盖索引优化： 只查询覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'zhangsan' AND age = 22 AND position ='manager';

二、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

EXPLAIN SELECT * FROM employees WHERE name in ('zhangsan','HanMeimei','Lucy') AND age = 22 AND position ='manager';

请添加图片描述

EXPLAIN SELECT * FROM employees WHERE (name = 'zhangsan' or name = 'HanMeimei') AND age = 22 AND position ='manager';

请添加图片描述
结论：优化器会判断数据量，如果表记录很多，会走索引，如果表记录很少，mysql认为全表更快，所以就不走索引。

三、like KK% 一般情况都会走索引，但范围不一定

EXPLAIN SELECT * FROM employees WHERE name like 'zhangsan%' AND age = 22 AND position ='manager';

请添加图片描述

EXPLAIN SELECT * FROM employees WHERE name > 'zhangsan' AND age = 22 AND position ='manager';

请添加图片描述
结论:like KK%可以看作是等值常量查询，而且联合索引前导列的值相等才能保证后续列值的有序性，范围查询后的结果集不一定是有序的，所以不使用索引。
MySQL5.6版开对于like KK%这种查询引入了索引下推

索引下推：

在MySQL5.6之前的版本，使用联合索引like KK%只能先匹配 ‘KK’ 开头的索引ID，然后拿这些ID到对应的主键逐个回表取到整行记录，再比对age和position这两个字段的值是否符合。
MySQL 5.6引入了索引下推优化，使用联合索引，在匹配到KK开头的索引时，先去判断age和position这两个字段的值是否符合，这样就可以直接拿到过滤后的索引ID，再拿ID回表查整行数据。

为什么范围查询没有用索引下推优化

Mysql可能认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

四、Order by优化

CASE1:
EXPLAIN SELECT * FROM employees WHERE name ='zhangsan' AND position ='dev' ORDER BY age;

请添加图片描述
利用最左前缀法则：中间字段不能断，因此查询用到了name索引，Extra字段里没有using filesort,所以说在排序过程中用到了age索引。

CASE2:
EXPLAIN SELECT * FROM employees WHERE name ='zhangsan' ORDER BY position;

请添加图片描述
查询使用了name索引，Extra字段里出现了Using filesort，是因为用了position进行排序，跳过了age。

CASE3:
EXPLAIN SELECT * FROM employees WHERE name ='zhangsan' ORDER BY age,position;

请添加图片描述
只用到name索引，age和position用于排序，无Using filesort，age和position顺序不能颠倒。

CASE4:
EXPLAIN SELECT * FROM employees WHERE name = 'zhangsan' and age = 18 ORDER BY position,age;

请添加图片描述
age和position顺序颠倒了，但Extra中并未出现Using filesort，因为age为常量，name和age过滤出的结果集中就name已经是常量了，所以实际上order by age实际上没用到，所以不会出现Using filesort。

CASE5:
EXPLAIN SELECT * FROM employees WHERE name = 'zhangsan' ORDER BY age asc,position desc;

请添加图片描述
虽然排序的字段列与索引顺序一样，order by默认升序，但position desc变成了降序，导致与索引的排序方式不同，所以产生Using filesort

CASE6:
EXPLAIN SELECT * FROM employees WHERE name >'a' ORDER BY name;

请添加图片描述
这个排序也产生了Using filesort，可能是由于数据量大引起，因为SELECT *需要回表，数据量大的情况回表性能降低很多，所以优化器直接选择全表扫描。可以使用覆盖索引优化

五、分页查询优化

1、根据自增且连续的主键排序的分页查询：

select * from employees limit 10000,10;

从表 employees 中先读取 10010 条，然后扔掉前 10000 条，读到后面 10 条数据。如果查询一张大表比较靠后的数据，执行效率是非常低。

优化方法：
如果主键是自增并且连续的，没添加单独 order by，表示通过主键排序，所以可以改写成根据主键去查询：
select * from employees where id > 90000 limit 5;
这种方法前提是主键必须是连续的，否则会导致结果不一样， order by非主键的字段也会导致结果不一样。

2、根据非主键字段排序的分页查询：

select * from employees ORDER BY name limit 90000,5;

这条sql也不会走索引，因为可能要遍历多个索引树，成本比扫描全表的成本更高，所以优化器放弃使用索引。

优化方法：
关键是让排序时返回的字段尽可能少，可以先查出主键，然后根据主键查到对应的记录：
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
这种方法是查出主键，因为主键肯定是走索引树的，查询很快，并且查询出的结果集只有5条，再拿这5条数据组成的临时表，查出全部字段就非常快了。
原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

六、Join关联查询优化

首先要了解什么是驱动表什么是被驱动表：

在决定哪个表做驱动表的时候，两个表会先按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是驱动表，反之就是被驱动表。
使用inner join、join时，排在前面的表不一定就是驱动表。
使用left join时，左表是驱动表，右表是被驱动表。
使用right join时，右表时驱动表，左表是被驱动表。
驱动表会优先执行。

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

有t1、t2两张表，t1表一万行数据，t2表100行，都是a有索引，b没有索引。
嵌套循环连接 Nested-Loop Join(NLJ) 算法：
关联字段有索引，并且执行计划 Extra 中未出现 Using join buffer 就表示使用的算法是 NLJ。

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

请添加图片描述
从执行计划中可以看出：
驱动表是 t2，被驱动表是 t1,用where条件过滤完t2，然后再跟t1做关联查询，使用了 NLJ算法。首先拿t2的100条数据，逐条去t1表扫描，此时t2会扫描行数为100，因为t1表是走了索引的，只有在取数据的时候扫描磁盘，所以也是100次。共200次

NLJ算法的sql执行流程：

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

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法：
关联字段没有索引，并且，Extra中出现Using join buffer说明该关联查询使用的是 BNL 算法。

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

请添加图片描述
从执行计划中可以看出：
驱动表是 t2，被驱动表是 t1，两张表都走了全表扫描，使用了BNL算法。
1、首先会把t2表的所有数据扫瞄一遍，放到join_buffer内存中（放到join_buff中的数据是无序的），所以扫描行数是100。
2、然后会把t1的每条数据跟join_buffe中对比，扫描行数为10000次。
3、因为join_buffer中的数据是无序的，所以极端情况下，扫描行数可能是10000*100=100万次。而且join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，就会分段放。
比如join_buffer一次只能放80行，会先往 join_buffer 里放80行记录，然后t1表数据跟 join_buffer对比取到部分结果集。然后清空 join_buffer ，再放入t2剩余20行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次t1表。

BNL算法的sql执行流程：

1、把t2的所有数据放入到join_buffer中
2、把表t1中每一行取出来，跟join_buffer中的数据做对比
3、返回满足 join 条件的数据
4、如果join_buffer一次放不下t2表所有数据，会重复上述步骤

同样没有索引的情况下，BNL会比NLJ算法快，因为100万次扫描是在join_buffer内存中进行的，不是磁盘扫描。

优化方法：
1、关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引。
2、小表驱动大表因为如果不走索引，表数据加载到join_buffer中次数越少，扫描的次数也就越少，所以需要小表驱动大表。写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join功能同join类似，但能让左边的表来驱动右边的表，能改变优化器对于联表查询的执行顺序。
语法：select * from t2 straight_join t1 on t2.a = t1.a;

尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

7、count查询优化

字段有索引： count(*)≈count(1)>count(字段)>count(主键 id)
字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
字段无索引： count(*)≈count(1)>count(主键 id)>count(字段)
字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
count(字段)不会统计字段为null值的数据行
count(1)跟count(字段)区别：
count(1)不需要取出字段统计，就用常量1做统计，count(字段)需要取出字段，所以理论上count(1)比count(字段)会快。

count(*)为什么最快？
count(*)不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高

为什么对于count(id)，mysql选择二级索引而不是主键索引？
可能是因为二级索引相对主键索引存储数据更少，检索性能应该更高

优化方法：
1、查询mysql自己维护的总行数
因为myisam存储了表的记录行数，查询不需要计算，仅限于不带where条件，innodb没有存储。
2、show table status
可以查询到表的大概数据行数，性能很高
3、将总数维护到Redis里
性能高，但高并发场景下很难保证表操作和redis操作的事务一致性
4、增加数据库计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

优化总结：

1、尽量在索引列上完成排序。
2、order by满足以下两种情况会使用Using index。

order by语句使用索引最左前列。
where子句与order by子句条件列组合满足索引最左前列。

3、能用覆盖索引尽量用覆盖索引
4、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。
5、Using filesort文件排序方式有两种：
单路排序： 一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
双路排序： 首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

EXPLAIN SELECT * FROM employees WHERE name ='zhangsan' ORDER BY age;

开启/关闭trace工具
开启：set session optimizer_trace="enabled=on",end_markers_in_json=on;
关闭：set session optimizer_trace="enabled=off";

查询语句和查询trace同时执行：
mysql> SELECT * FROM employees WHERE name > 'a' ORDER BY position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE

开启trace工具会对MySQL的性能造成一定的影响，所以建议只在需要分析SQL语句时开启；

单路排序过程：
1、从索引name找到第一个满足 name = ‘zhangsan’ 条件的主键 id
2、根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
3、从索引name找到下一个满足 name = ‘zhangsan’ 条件的主键 id
4、重复上述步骤直到没有满足条件 name = ‘zhangsan’ 的数据
5、对 sort_buffer 中的数据按照age进行排序
双路排序过程：
1、从索引 name 找到第一个满足 name = ‘zhangsan’ 的主键id
2、根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer 中
3、从索引 name 取下一个满足 name = ‘zhangsan’ 记录的主键 id
4、重复上述步骤直到没有满足条件 name = ‘zhangsan’ 的数据
5、对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
6、遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端

对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小判断使用哪种排序模式。
如果字段的总长度小于max_length_for_sort_data ，使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，使用双路排序模式。

单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小。

索引设计原则：

1、代码先行，索引后上
等到主体业务功能开发完毕，把涉及到该表相关sql拿出来分析之后再建立索引。
2、联合索引尽量覆盖条件
可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足最左前缀原则。
3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
这种字段的索引树里只包含男和女两种，根本没法进行快速的二分查找，索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是不同值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
4、长字符串可以采用前缀索引
尽量对字段类型较小的列设计索引，因为字段类型较小的话，占用磁盘空间也会比较小，搜索的时候性能也会比较好一点。但还是要分情况，很多时候也要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以对这个字段的前20个字符建立索引 KEY index(name(20),age,position)。
此时在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到与前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以索引会失效， group by也是同理。
5、where与order by冲突时优先where
where条件会优先使用索引来快速筛选出来一部分数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选数据，然后再做排序的成本可能会小很多。
6、基于慢sql查询做优化
我们可以把一些慢查询sql通过配置导出到文件中，针对这些慢sql查询再做特定的索引优化。
关于慢sql查询不清楚的可以参考这篇文章