以下内容只针对innodb,mysql版本基于5.6
一.join优化
1、优化算法
优化算法了解:嵌套循环-NLJ(Nested-Loop Join)、块嵌套循环-BNLJ(Block Nested-Loop Join)、MRR(mUlti Range Read)、批量键值访问-BKA(Batched Key Access Join)、Hash Join(mysql 8.0后引入)- NLJ
![0](/i/l/?n=23&i=blog/146328/202302/146328-20230218211056185-406022226.png)
- BNLJ
![0](/i/l/?n=23&i=blog/146328/202302/146328-20230218211056499-1617745484.png)
- MRR
- BKA
![](/i/l/?n=23&i=blog/146328/202302/146328-20230218210803463-930965997.png)
- Hash Join
2、优化原则
- 小表驱动大表:一般优化器会自动处理,如果优化器自动处理不符合原则,可使用 STRAIGHT_JOIN
- where条件:应当使用索引,并尽可能的减少外层循环的数据量
- join字段应有索引,并且join字段的类型要一致
- 尽量减少扫描的行数(explain-rows控制在百万内性能较好)
- 尽可能参与join的表不要太多,阿里规约建议不超过3张,超过的建议进行SQL拆分为多个SQL,并在服务器内存中再组装数据
二、limit优化
示例:select * from t_order limit 300000,10; 方案1:覆盖索引 select order_no from t_order limit 300000,10; 如果一定要查询覆盖索引字段外的其他字段呢? 方案2:覆盖索引+join select * from t_order o inner join (select id from t_order limit 300000,10) t on o.id = t.id; 方案3:覆盖索引+子查询 select * from t_order where id >= (select id from t_order limit 300000,1) limit 10 方案4:范围查询+limit select * from t_order where id > 100000 limit 10 前提条件:能拿到上一页主键的最大值 5、如果能够获得起始主键值和结束主键值 select * from t_order where id between 100000 and 100010 6、禁止传入过大页码 比如超过设计目标页码时则提示禁止或者执行默认页码的逻辑三、count优化
1、优化结论
COUNT(*)和COUNT(1)对于innodb性能一样没有区别,MyISAM 不带where条件时count(*)会更快 详见https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html![](/i/l/?n=23&i=blog/146328/202302/146328-20230218210740982-321352012.png)
COUNT(*)会选择最小的非主键索引,不存在则使用主键 COUNT(字段)会排除为null的行,count(*)不会排除 尽量使用COUNT(*):mysql后续版本innodb也会对不带任何条件的COUNT(*)做优化,
2、count(*)自动优化分析
- 结论
- 当没有非主键索引,会使用主键索引
- 如果存在非主键索引,使用非主键索引
- 如果存在多个非主键索引,会食用最小的非主键索引
- 原因
- innodb非主键索引的叶子节点存储内容是:索引+主键;
- 主键索引叶子节点存储内容是:主键+表数据,
- 所以非主键索引的数据的更小,在1个page里,非主键索引可以存储更多的条目,从而扫描次数会更小,性能更快
- 多个非主键索引之前他会选择性能更快的,及非主键索引存储内容数据更小的
- 示例和优化方案
四、order by优化
1、利用索引避免排序
如果order by子句的条件正好是索引,就可以利用索引本身的有序性,让mysql跳过排序过程![](/i/l/?n=23&i=blog/146328/202302/146328-20230218210850594-693397979.png)
比如 t_order表有组合索引 index idx_create_time_pay_time(create_time,pay_time) 那么是否能使用索引避免排序 【能】 select * from t_order order by create_time, pay_time; 【不能】select * from t_order order by create_time desc, pay_time asc; 【能】 select * from t_order WHERE create_time = '2020-01-01' order by pay_time; 【能】 select * from t_order WHERE create_time < '2020-01-01' order by create_time; 【不能】select * from t_order WHERE create_time < '2020-01-01' order by pay_time; 【能】 select * from t_order WHERE create_time = '2020-01-01' and pay_time > '2020-01-01' order by pay_time; 【不能】select * from t_order order by create_time, id;
- 分析:
- 利用索引本身的有序性和用比较的思路来分析,排序类比Java和.net中的排序比较接口:Comparable和IComparable
- 测试说明
- mysq优化器发现全表扫描开销更低,会直接用全表扫描
- 无法利用索引避免排序的情况(都可归类为不符合最左前缀原则)
- 排序字段存在于多个索引
- 组合索引的排序升降序不一致
2、文件排序
rowid排序 1.从表中获取满足WHERE条件的记录 2、对于每条记录,将记录的主键及排序键(id,order. column)取出放入sort buffer (由sort. buffer. size控制)(第一次IO) 3、如果sort buffer能存放所有满足条件的(id,order_ column) ,则进行排序;否则sort buffer满后,排序并写到临时文件- 排序算法:快速排序算法
- 排序算法:归并排序算法
五、group by/distinct优化
处理groupby的三种方式- 松散索引扫描,性能最好, explain中会有Using index for group-by
- 紧凑索引扫描,性能第二, explain中无明显标识
- 临时表,性能最差, explain中会有Using temporary