MySQL SQL点查，范围查，排序，分组的Explain分析和SQL优化（8.0版本）

时间：2024-01-25 22:18:41

MySQL SQL常用优化主要有where，range，order，group by，or等查询。下图是优化的原则，后面会有一个例子来看看：

image.png

  1. 对于where和range来说，按照优先最左匹配原则，其次跨列的索引条件下推，这会导致部分索引字段失效，最后是全部扫描，通过MySQL服务器来过滤存储引擎返回的数据。
  2. 对于order排序来说，优先使用索引扫描来排序，其次是文件排序，文件排序可能在内存也能在磁盘。
  3. 对于group分组来说，优先使用索引来分组，但是分为稀疏索引分组和紧密索引分组，通常情况下，稀疏索引效果更好，因为可以跳过索引顺序来扫描，也可以理解为跨桶扫描，其次是紧密索引分组，最后用临时表分组。
  4. 对于or来说，给每一个字段加独立索引，然后优化器会进行索引合并。

案例

建表如下：

CREATE TABLE t1 ( c1 int DEFAULT NULL, c2 int DEFAULT NULL, c3 int NOT NULL, c4 int DEFAULT NULL, KEY idx (c1,c2,c3) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

比如我们为t1建立了联合索引(c1,c2,c3)，c4美元加索引。c1，c2，c3的索引长度分别为5，5，4。因为c3字段是int，占4个字节，而c1是int，但是可以为NULL，所以额外使用一个字节存储NULL的索引字段。数据有50条：

点查

SELECT * FROM training.t1 where c3 =1 and c2=1 and c1=1 ;

使用了索引，只要全部包含索引列，那么点查顺序无所谓，索引长度为14： image.png

SELECT * FROM training.t1 where c2=1 and c1=1 ;

使用了索引，只要包含了首列c1，c1和c2顺序无所谓，索引长度为10： image.png

SELECT * FROM training.t1 where c3=1 and c1=1 ;

使用了索引，但是索引条件下推了，只要包含了首列，跨列顺序无所谓，会使用索引下推，但是c3无法走索引，索引长度是5，只用了c1字段索引： image.png

SELECT * FROM training.t1 where c2=1 and c3=1;

不包含首列c1，那么会索引失效，走全部扫描： image.png

范围查询

SELECT * FROM t1 where c3>1 and c1>1 and c2>1;

只有c1用到了索引，c2，c3没用到索引，索引长度是5，可以看出范围查询很简单，只要一列用了范围查询，后面的列都不会走索引，但是会走索引下推：

image.png

SELECT * FROM t1 where c3>1 and c2>1;

没有c1，会直接全部扫描： image.png

SELECT * FROM t1 where c1=1 and c2>1 and c3=1;

用到了索引字段c1，c2。字段c3无法使用索引，索引长度是10，c3列走了索引下推优化： image.png

排序

排序优先索引排序，这就要求查询和排序字段都必须要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;

以上都利用了索引排序。 image.png

SELECT c1,c2,c3,c4 FROM t1 order by c1,c2,c3;

由于查询列不包括索引字段，所以使用了文件排序： image.png

SELECT c1,c2,c3 FROM t1 order by c1 asc,c2 desc

排序的方向不一样，使用了文件排序： image.png

SELECT c1,c2,c3 FROM t1 where c1=2 order by c1 asc,c2 desc

排序方向不一样，出现了backward index scan（反向索引扫描） image.png

SELECT c1,c2,c3 FROM t1 order by c1 asc,c4 asc

排序不在索引的列，会导致filesort： image.png

SELECT c1,c2,c3 FROM t1 where c1>2 order by c2,c3

索引列第一个是范围查询，排序使用后面索引会导致filesort： image.png

参考

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

相关文章