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 语句中
如何查看 Mysql 优化器优化之后的 SQL
# 仅在服务器环境下或通过Navicat进入命令列界面explain extended SELECT * FROM `student` where `name` = 1 and `age` = 1;
# 再执行show warnings;
# 结果如下：/* select#1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1))
复制代码
为什么要做这个事呢？我们知道 Mysql 有一个最左匹配原则，那么如果我的索引建的是 age，name，那我以 name，age 这样的顺序去查询能否使用到索引呢？实际上是可以的，就是因为 Mysql 查询优化器可以帮助我们自动对 SQL 的执行顺序等进行优化，以选取代价最低的方式进行查询（注意是代价最低，不是时间最短）
SQL 优化
超大分页场景解决方案
如表中数据需要进行深度分页，如何提高效率？在阿里出品的 Java 编程规范中写道：
``利用延迟关联或者子查询优化超多分页场景``
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写
# 反例（耗时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万
复制代码
获取一条数据时的 Limit 1
如果数据表的情况已知，某个业务需要获取符合某个 Where 条件下的一条数据，注意使用 Limit
说明：在很多情况下我们已知数据仅存在一条，此时我们应该告知数据库只用查一条，否则将会转化为全表扫描
# 反例（耗时2424.612s）select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48';
# 正例（耗时1.036s）select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48' LIMIT 1;
# 说明task_result表为生产环境的一个表，总数据量为3400万，where条件非索引字段，数据所在行为第19486条记录
复制代码
批量插入
# 反例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 语句一般业务要求都是 ``'%关键字%'``这种形式，但是依然要思考能否考虑使用右模糊的方式去替代产品的要求，其中阿里的编码规范提到：
``页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决``
# 反例（耗时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
########################################################################### 对正例的Explain1 SIMPLE task_result range adapt_id adapt_id 98 99 100.00 Using index condition
# 对反例的Explain1 SIMPLE task_result ALL 33628554 11.11 Using where
# 说明task_result表为生产环境的一个表，总数据量为3400万，taskid是一个普通索引列，可见%%这种匹配方式完全无法使用索引，从而进行全表扫描导致效率极低，而正例通过索引查找数据只需要扫描99条数据即可
复制代码
避免 SQL 中对 where 字段进行函数转换或表达式计算
# 反例select * from task_result where id + 1 = 15551;
# 正例select * from task_result where id = 15550;
########################################################################### 对正例的Explain1 SIMPLE task_result const PRIMARY PRIMARY 8 const 1 100.00
# 对反例的Explain1 SIMPLE task_result ALL 33631512 100.00 Using where
# 说明其实在知道了有SQL优化器之后，我个人感觉这种普通的表达式转换应该可以提前进行处理再进行查询，这样一来就可以用到索引了，但是问题又来了，如果mysql优化器可以提前计算出结果，那么写sql语句的人也一定可以提前计算出结果，所以矛盾点在这个地方，导致5.7版本以前的此种情况都无法使用索引吧，未来可能会对其进行优化
复制代码
使用 ISNULL()来判断是否为 NULL 值
说明：NULL 与任何值的直接比较都为 NULL
# 1） NULL<>NULL 的返回结果是 NULL，而不是 false。 # 2） NULL=NULL 的返回结果是 NULL，而不是 true。 # 3） NULL<>1 的返回结果是 NULL，而不是 true。
复制代码
多表查询
我所在的公司基本禁止了多表查询，那如果必须使用到的话，我们可以一起参考一下阿里的编码规范
Eg：超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引
明明有索引为什么还走全表扫描
之前回答一些面试问题的时候，对某一个点的理解出现了偏差，即我认为只要查询的列有索引则一定会使用索引去 Push 数据
然而实际上不仅仅是这样，真正应该是：``针对查询的数据行占总数据量过多时会转化成全表查询``
那么这个过多指代的是多少呢？
我的测试结果是 50%，但个人认为 MySQL 优化器不会完全纠结于行数区分是否全表，而是有很多其他因素综合考虑发现全表扫描的效率更高等等，所以充分认识到该问题即可
count(*) 还是 count(id)
阿里的 Java 编码规范中有以下内容：
``【强制】不要使用 count(列名) 或 count(常量) 来替代 count(*)``
count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行
字段类型不同导致索引失效
阿里的 Java 编码规范中有以下内容：
``【推荐】防止因字段类型不同造成的隐式转换，导致索引失效``
实际上数据库在查询的时候会作一层隐式的转换，比如 varchar 类型字段通过 数字去查询
# 正例EXPLAIN SELECT * FROM `user_coll` where pid = '1';type：refref：const rows:1 Extra:Using index condition
# 反例EXPLAIN SELECT * FROM `user_coll` where pid = 1;type：indexref：NULL rows:3(总记录数)Extra:Using where; Using index
# 说明pid字段有相应索引，且格式为varchar
复制代码
关于
感谢以下博文及其作者：
干货！SQL性能优化，书写高质量SQL语句
干货！SQL性能优化，书写高质量SQL语句（二）
MySQL官方文档
Tips
自建数据表进行测试
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
复制代码
插入数据
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();
复制代码
评论