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_8614140308007
27_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
>>>>>>>>>>>>>>####
# 对正例的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字段进⾏函数转换或表达式计算
# 反例
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 3363151
2 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编码规范中有以下内容：
【推荐】防⽌因字段类型不同造成的隐式转换，导致索引失效查看mysql索引
实际上数据库在查询的时候会作⼀层隐式的转换，⽐如 varchar 类型字段通过数字去查询
# 正例
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。

四、MySQL键值
« 上一篇
mysql查看库结构---查看表结构
下一篇 »

发表评论