MySQL 查询优化

置顶张志帅‍

已于 2023-02-13 01:23:30 修改

阅读量2.8k

点赞数 4

分类专栏：编程菜鸟养成记 # MySQL 基础与进阶文章标签： mysql 索引 sql

于 2020-07-28 09:05:56 首次发布

本文链接：https://blog.csdn.net/MrBaymax/article/details/107603173

版权

编程菜鸟养成记同时被 2 个专栏收录

180 篇文章 8 订阅

订阅专栏

MySQL 基础与进阶

10 篇文章 0 订阅

订阅专栏

使用索引

规则

全值匹配：查询的字段按照顺序在索引中都可以匹配到，
最佳左前缀法则：指的过滤条件要使用索引必须按照索引建立时的顺序依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。注意，MySQL 8.0.13 中添加了 Skip Scan Range。
不在索引列上做计算：不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换「尤其注意 varchar 和 int」），会导致索引失效而转向全表扫描。
索引列上不能有范围查询：将可能做范围查询的字段的索引顺序放在最后
尽量使用覆盖索引：查询列和索引列一致，不写 select *；
不使用不等于（!=以及 <>）
当字段允许为 NULL 时：where 后条件 is null 可以使用索引，is not null 不可以用到索引
Like 以通配符（%sadfj%）开头时，MySQL 索引失效。解决办法：①可以使用主键索引；②使用覆盖索引，查询字段必须是建立覆盖索引字段；③当覆盖索引指向的字段是 varchar(380) 及 380 以上的字段时，覆盖索引会失效！
字符串不加单引号索引失效（比如字符串为一串数字，不加单引号的话会被认为是数字，发生了函数转化）
少用 or，用了索引会失效
IN 的话，不一定失效（如果 in 后的数据比较多，可能会失效「MySQL 认为拿到索引再去回表还不如走全表扫描呢」）

示例

MySQL 索引优化

助记口诀

全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写*；
不等空值还有 OR，索引失效要注意；
VAR 引号不可丢，SQL 优化有诀窍。

具体方案

关联查询优化

LEFT JOIN：左侧为驱动表，右侧为被驱动表
INNER JOIN：MySQL 会自动将小结果的表作为驱动表
straight_join：效果和 inner join 一样，但是会强制将左侧作为驱动表！
子查询尽量不要放在被驱动表，有可能使用不到索引，会生成临时表。

在优化关联查询时，在被驱动表建立索引才有效。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

排序优化

在索引列上

ORDER BY 语句使用索引最左前列
使用 WHERE 子句与 ORDER BY 子句条件列组合满足索引最左前列
ORDER BY 后字段同时使用升序或降序
如果 WHERE 使用索引的最左前缀为常量，则 OEDER BY 可以使用索引

不在索引列上

双路排序：字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。效率高
单路容易出现的问题：如果取出的数据总大小超过了 sort_buffer 的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
优化策略
① 增大 sort_buffer_size参数的设置（单路排序的内存大小）
② 增大 max_length_for_sort_data参数的设置（单次排序字段大小）
③ 去掉select 后面不需要的字段

分组优化

同排序优化
实质是先排序后分组，遵循索引最左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
WHERE 效率高于 HAVING

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort;
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

子查询优化

有索引：inner join 是最好的，其次是 in，exists 最糟糕
无索引：反之（待验证）

分页优化

优化前：EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY deptno LIMIT 10000,40;会出现 using filesort
在这里插入图片描述
给 deptno 加索引后没效果：

优化后：EXPLAIN SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id
在这里插入图片描述
优化思路：先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联(查询的数据量小了后再进行查询)。

去重查询

尽量不要使用 distinct 关键字去重，可以使用 group by+需要去重的字段，这个时候会用到索引

对于 IN 和 EXISTS 的使用

直接说个结论吧：EXISTS 后接大表，IN 后接小表，两表大小无差别两者都行

用错索引

MySQL 统计的扫描行数出了问题，可以使用 show index from table_name 查看某个表的索引基数，可以看 cardinality 此参数。
在这里插入图片描述
虽然 MySQL 可以自动触发索引统计（可能不及时），可以使用 analyze table table_name 手动统计。