SQL诊断与优化

必备技能 阅读量

Explain 诊断

Explain各参数的含义如下：

列名 说明
id 执行编号，标识select所属的行。如果在语句中没有子查询或关联查询，只有唯一的select，每行都将显示1.否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type 显示本行是简单或复杂select，如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUIT
table 访问引用哪个表（引用某个查询，如“derived3”
type 数据访问/读取操作类型（All、index、range、ref、eq_ref、const/system、NULL）
possible_key 揭示哪一些索引可能有利于高效的查找
key 显示mysql实际决定采用哪个索引来优化查询
key_len 显示mysql在索引里使用的字节数
ref 显示了之前的表在key列记录的索引中查找值所用的列或常量
rows 为了找到所需要的行而需要读取的行数，估算值
Extra 额外信息，如using index、filesort等

select_type 常见类型及其含义

  • SIMPLE: 不包含子查询或者 UNION 操作的查询
  • PRIMARY: 查询中如果包含任何子查询，那么最外层的查询则被标记为 PRIMARY
  • SUBQUERY: 子查询中第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT，取决于外部查询
  • UNION: UNION 操作的第二个或者之后的查询
  • DEPENDENT UNION: UNION 操作的第二个或者之后的查询,取决于外部查询
  • UNION RESULT: UNION 产生的结果集
  • DERIVED: 出现在 FROM 字句中的子查询

type常见类型及其含义

  • system: 这是 const 类型的一个特例，只会出现在待查询的表只有一行数据的情况下
  • consts: 常出现在主键或唯一索引与常量值进行比较的场景下，此时查询性能是最优的
  • eq_ref: 当连接使用的是完整的索引并且是 PRIMARY KEY 或 UNIQUE NOT NULL INDEX 时使用它
  • ref：当连接使用的是前缀索引或连接条件不是 PRIMARY KEY 或 UNIQUE INDEX 时则使用它
  • ref_or_null: 类似于 ref 类型的查询，但是附加了对 NULL 值列的查询
  • index_merge: 该联接类型表示使用了索引进行合并优化
  • range: 使用索引进行范围扫描，常见于 between、> 、< 这样的查询条件
  • index: 索引连接类型与 ALL 相同，只是扫描的是索引树，通常出现在索引是该查询的覆盖索引的情况
  • ALL: 全表扫描，效率最差的查找方式

阿里编码规范要求：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

key列

实际在查询中是否使用到索引的标志字段

Extra列

Extra 列主要用于显示额外的信息，常见信息及其含义如下:

  • Using where: MySQL 服务器会在存储引擎检索行后再进行过滤
  • Using filesort: 通常出现在 GROUP BY 或 ORDER BY
    语句中，且排序或分组没有基于索引，此时需要使用文件在内存中进行排序，因为使用索引排序的性能好于使用文件排序，所以出现这种情况可以考虑通过添加索引进行优化
  • Using index: 使用了覆盖索引进行查询，此时不需要访问表，从索引中就可以获取到所需的全部数据
  • Using index condition: 查找使用了索引，但是需要回表查询数据
  • Using temporary: 表示需要使用临时表来处理查询，常出现在 GROUP BY 或 ORDER BY 语句中

SQL 优化

超大分页场景解决方案

如表中数据需要进行深度分页，如何提高效率？在阿里出品的Java编程规范中写道：

利用延迟关联或者子查询优化超多分页场景

说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写

1
2
3
4
5
# 反例（耗时129.570s）
select * from task_result LIMIT 20000000, 10;

# 正例（耗时5.114s）
SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id;

说明
task_result表为生产环境的一个表，总数据量为3400万，id为主键，偏移量达到2000万

批量插入

1
2
3
4
5
6
7
# 反例
INSERT into person(name,age) values('A',24)
INSERT into person(name,age) values('B',24)
INSERT into person(name,age) values('C',24)

# 正例
INSERT into person(name,age) values('A',24),('B',24),('C',24);

说明
比较常规，就不多做说明了

like语句的优化

like语句一般业务要求都是 '%关键字%' 这种形式，但是依然要思考能否考虑使用右模糊的方式去替代产品的要求，其中阿里的编码规范提到:

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

1
2
3
4
5
6
7
8
9
10
11
12
# 反例（耗时78.843s）
EXPLAIN select * from task_result where taskid LIKE '%tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1;

# 正例（耗时0.986s）
select * from task_result where taskid LIKE 'tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1

##########################################################################
# 对正例的Explain
1	SIMPLE	task_result		range	adapt_id	adapt_id	98		99	100.00	Using index condition

# 对反例的Explain
1	SIMPLE	task_result		ALL					                    33628554	11.11	Using where

说明
task_result表为生产环境的一个表，总数据量为3400万，taskid是一个普通索引列，可见%%这种匹配方式完全无法使用索引，从而进行全表扫描导致效率极低，而正例通过索引查找数据只需要扫描99条数据即可

避免SQL中对where字段进行函数转换或表达式计算

1
2
3
4
5
6
7
8
9
10
11
12
# 反例
select * from task_result where id + 1 = 15551;

# 正例
select * from task_result where id = 15550;

##########################################################################
# 对正例的Explain
1	SIMPLE	task_result		const	PRIMARY	PRIMARY	8	const	1	100.00	

# 对反例的Explain
1	SIMPLE	task_result		ALL					                33631512  100.00	Using where

说明
其实在知道了有SQL优化器之后，我个人感觉这种普通的表达式转换应该可以提前进行处理再进行查询，这样一来就可以用到索引了，但是问题又来了，如果mysql优化器可以提前计算出结果，那么写sql语句的人也一定可以提前计算出结果，所以矛盾点在这个地方，导致5.7版本以前的此种情况都无法使用索引吧，未来可能会对其进行优化

使用 ISNULL()来判断是否为 NULL 值

说明：NULL 与任何值的直接比较都为 NULL

1
2
3
# 1） NULL <> NULL 的返回结果是 NULL，而不是 false。 
# 2） NULL = NULL 的返回结果是 NULL，而不是 true。 
# 3） NULL <> 1 的返回结果是 NULL，而不是 true。

明明有索引为什么还走全表扫描

MYSQL查询优化器针对查询的数据行占总数据量过多时会转化成全表查询

count(*) 还是 count(id)

【强制】不要使用 count(列名) 或 count(常量) 来替代 count(*)

count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

字段类型不同导致索引失效

【推荐】防止因字段类型不同造成的隐式转换，导致索引失效

1
2
3
4
5
6
7
8
9
10
11
12
13
# 正例
EXPLAIN SELECT * FROM `user_coll` where pid = '1';
type：ref
ref：const	
rows:1	
Extra:Using index condition

# 反例
EXPLAIN SELECT * FROM `user_coll` where pid = 1;
type：index
ref：NULL	
rows:3(总记录数)
Extra:Using where; Using index

说明
pid字段有相应索引，且格式为varchar

Tips

  • 自建数据表进行测试
1
2
3
4
5
6
7
8
CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  `page` bigint(20) DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL COMMENT '状态：0 正常，1 冻结，2 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4
  • 插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER ;;
    CREATE PROCEDURE insertData()
    BEGIN
        declare i int;
        set i = 1 ;
        WHILE (i < 1000000) DO
            INSERT INTO student(`name`,class,`page`,`status`)
                VALUES(CONCAT('class_', i),
                    CONCAT('class_', i),
                i, (SELECT FLOOR(RAND() * 2)));
            set i = i + 1;
        END WHILE;
        commit;
		END;;
CALL insertData();

Powered by Hexo and Hexo-theme-hiker

Copyright © 2017 - 2023 Keep It Simple And Stupid All Rights Reserved.

访客数 : | 访问量 :