mysql优化

以下总结来源于：https://www.itzhai.com/articles/how-sql-works-understand-the-essence-of-tuning-by-the-execution-principle.html

 

1、count优化总结

count(*)不会取记录的值，与count(1)类似。
执行效率对比：count(字段) < count(主键) < count(1)

2、order by优化总结

order by字段尽量使用固定长度的字段类型，因为排序字段不支持压缩；
order by字段如果需要用可变长度，应尽量控制长度，道理同上；
查询中尽量不用用select *，避免查询过多，导致order by的时候sort buffer内存不够导致外部排序，或者行大小超过了max_length_for_sort_data导致走了sort_key, rowid排序模式，使得产生了更多的磁盘读，影响性能；
尝试给排序字段和相关条件加上联合索引，能够用到覆盖索引最佳。

3、join使用总结

join优化的目标是尽可能减少join中Nested-Loop的循环次数，所以请让小表做驱动表；
关联字段尽量走索引，这样就可以用到Index Nested-Loop Join了；
如果有order by，请使用驱动表的字段作为order by，否则会使用 using temporary；
如果不可避免要用到BNL算法，为了减少被驱动表多次扫描导致的对Buffer Pool利用率的影响，那么可以尝试把 join_buffer_size调大；
为了进一步加快BNL算法的执行效率，我们可以给关联条件加上索引，转换为BKA算法；如果加索引成本较高，那么可以通过临时表添加索引来实现；
如果您使用的是MySQL 8.0.18，可以尝试使用hash join，如果是较低版本，也可以自己在程序中实现一个hash join。

4、group by 优化建议

尽量让group by走索引，能最大程度的提高效率；
如果group by结果不需要排序，那么可以加上order by null，避免进行排序；
如果group by的数据量很大，可以使用SQL_BIG_RESULT修饰符，提醒优化器应该使用排序算法得到group的结果。

5、DISTINCT 优化建议
同group by

6、子查询 优化建议

Semijoin，半连接转换，把子查询sql自动转换为semijion；
Materialization，子查询物化；
EXISTS策略，in转exists；
其中Semijoin只能用于IN，= ANY，或者EXISTS的子查询中，不能用于NOT IN，<> ALL，或者NOT EXISTS的子查询中。

7、Materialization(物化)

优化器使用Materialization(物化)来实现更加有效的子查询处理。物化针对非关联子查询进行优化。

物化通过把子查询结果存储为临时表（通常在内存中）来加快查询的执行速度。MySQL在第一次获取子查询结果时，会将结果物化为临时表。随后如果再次需要子查询的结果，则直接从临时表中读取。

优化器可以使用哈希索引为临时表建立索引，以使查找更加高效，并且通过索引来消除重复项，让表保持更小。

子查询物化的临时表在可能的情况下存储在内存中，如果表太大，则会退回到磁盘上进行存储。

8、子查询的优化

首先优先使用Semijoin来进行优化，消除子查询，通常选用FirstMatch策略来做表连接；
如果不可以使用Semijoin进行优化，并且当前子查询是非关联子查询，则会物化子查询，避免多次查询，同时这一步的优化会遵循选用小表作为驱动表的原则，尽量走索引字段关联，分为两种执行方式：Materialize-lookup，Materialization-scan。通常会选用哈希索引为物化临时表提高检索效率；
如果子查询不能物化，那就只能考虑Exists优化策略了，通过condition push down把条件下推到exists子查询中，减少子查询的结果集，从而达到优化的目的。

9、limit优化

如果查询的offset很大，避免直接使用offset，而是通过id到聚集索引中检索查找。

利用自增索引，如：
select * from t30 where id > 10000 limit 10;
当然，这也是会有问题的，如果id中间产生了非连续的记录，这样定位就不准确了