MySQL SQL常用优化主要有where,range,order,group by,or等查询。下图是优化的原则,后面会有一个例子来看看:
比如建立了联合索引(c1,c2,c3),索引长度分别为5,5,4。数据有50条:
点查
SELECT * FROM training.t1 where c3 =1 and c2=1 and c1=1 ;
使用了索引,只要全部包含索引列,那么点查顺序无所谓,索引长度为14:
SELECT * FROM training.t1 where c2=1 and c1=1 ;
使用了索引,只要包含了首列c1,c1和c2顺序无所谓,索引长度为10:
SELECT * FROM training.t1 where c3=1 and c1=1 ;
使用了索引,但是索引条件下推了,只要包含了首列,跨列顺序无所谓,会使用索引下推,但是c3无法走索引,索引长度是5,只用了c1字断索引:
SELECT * FROM training.t1 where c2=1 and c3=1;
不包含首列c1,那么会索引失效,走全部扫描:
范围查询
SELECT * FROM t1 where c3>1 and c1>1 and c2>1;
只有c1用到了索引,c2,c3没用到索引,索引长度是5,可以看出范围查询很简单,只要一列用了范围查询,后面的列都不会走索引,但是会走索引下推:
SELECT * FROM t1 where c3>1 and c2>1;
没有c1,会直接全部扫描:
SELECT * FROM t1 where c1=1 and c2>1 and c3=1;
用到了索引字段c1,c2。c3字段无法使用索引,索引长度是10,c3列走了索引下推优化:
排序
排序优先索引排序,这就要求查询和排序字段都必须要order by子句中:
SELECT c1,c2,c3 FROM t1 order by c1; SELECT c1,c2,c3 FROM t1 order by c1,c2; SELECT c1,c2,c3 FROM t1 order by c1,c2,c3; SELECT c1,c2,c3 FROM t1 where c1=1 order by c2,c3; SELECT c1,c2,c3 FROM t1 where c1>1 order by c1,c2,c3;
以上都利用了索引排序。
SELECT c1,c2,c3,c4 FROM t1 order by c1,c2,c3;
由于查询列不包括索引字段,所以使用了文件排序:
SELECT c1,c2,c3 FROM t1 order by c1 asc,c2 desc
排序的方向不一样,使用了文件排序:
SELECT c1,c2,c3 FROM t1 where c1=2 order by c1 asc,c2 desc
排序方向不一样,出现了backward index scan(反向索引扫描)
SELECT c1,c2,c3 FROM t1 order by c1 asc,c4 asc
排序不在索引的列,会导致filesort:
SELECT c1,c2,c3 FROM t1 where c1>2 order by c2,c3
索引列第一个是范围查询,排序使用后面索引会导致filesort:
参考
where优化:https://dev.mysql.com/doc/refman/8.0/en/where-optimization.html range优化:https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html 排序优化:https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html 分组优化:https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html
标签:8.0,Explain,t1,索引,SQL,c3,c2,c1,SELECT From: https://blog.51cto.com/thinklili/9417980