一 序
慢SQL是常见的问题,可能建表没有,慢慢随着业务增长就有了。所以是一个长期的过程。需要不断去监控,维护。
二 无索引
当数据库中出现访问表的SQL无索引导致全表扫描,如果表的数据量很大,扫描大量的数据,应用请求变慢占用数据库连接,连接很快被耗用完,新请求无连接可用。
case:
CREATE TABLE `user` (
……
mo bigint NOT NULL DEFAULT '' ,
KEY ind_mo (mo)
……
) ENGINE=InnoDB;
SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1
执行计划
explain SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1;
id: 1
select_type: SIMPLE
table: user
type: ALL
possible_keys: NULL
key: NULL
rows: 707250
Extra: Using where
从上面的SQL看到执行计划中ALL,代表了这条SQL执行计划是全表扫描,数据量大了非常慢的。
添加索引
mysql> alter table user add index ind_mo(mo);
mysql>SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1;
Empty set (0.05 sec)
执行计划
explain SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: index
possible_keys: ind_mo
key: ind_mo
rows: 1
Extra: Using where; Using index
三 隐式转换
SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。
CREATE TABLE `user` (
……
mo char(11) NOT NULL DEFAULT '' ,
KEY ind_mo (mo)
……
) ENGINE=InnoDB;
mysql> explain extended select uid from`user` where mo=13772556391 limit 0,1;
mysql> show warnings;
Warning1:Cannot use index 'ind_mo' due to type or collation conversion on field 'mo'
Note:select `user`.`uid` AS `uid` from `user` where (`user`.`mo` = 13772556391) limit 0,1
MySQL的策略是将字符串转换为数字之后再比较。函数作用于表字段,索引失效。
如何解决:
explain SELECT uid FROM `user` WHERE mo='13772556391' LIMIT 0,1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: ref
possible_keys: ind_mo
key: ind_mo
rows: 1
Extra: Using where; Using index
其他case:
在定义时间字段的时候没采用时间类型的数据类型。
表索引的COLLATE排序集不一样,跨表join 也会发生扫描all.
参考官网介绍:https://dev.mysql.com/doc/refman/5.7/en/charset-binary-collations.html
tips:
- 在使用索引时,我们可以通过explain+extended查看SQL的执行计划,判断是否使用了索引以及发生了隐式转换。
- 由于常见的隐式转换是由字段数据类型以及collation定义不当导致,因此我们在设计开发阶段,要避免数据库字段定义,避免出现隐式转换。
- 由于MySQL不支持函数索引,在开发时要避免在查询条件加入函数,例如date(gmt_create)。
四 关联更新、删除
虽然MySQL5.6引入了物化特性,但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。
比如下面UPDATE语句,MySQL实际执行的是循环/嵌套子查询(DEPENDENT SUBQUERY)
UPDATE operation o
SET status = 'applying'
WHERE o.id IN (SELECT id
FROM (SELECT o.id,
o.status
FROM operation o
WHERE o.group = 123
AND o.status NOT IN ( 'done' )
ORDER BY o.parent,
o.id
LIMIT 1) t);
执行计划:
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | o | index | | PRIMARY | 8 | | 24 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | | | | | | | | Impossible WHERE noticed after reading const tables |
| 3 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
重写为JOIN之后,子查询的选择模式从DEPENDENT SUBQUERY变成DERIVED,执行速度大大加快
UPDATE operation o
JOIN (SELECT o.id,
o.status
FROM operation o
WHERE o.group = 123
AND o.status NOT IN ( 'done' )
ORDER BY o.parent,
o.id
LIMIT 1) t
ON o.id = t.id
SET status = 'applying'
执行计划简化为:
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | | | | | | | | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
MySQL对待EXISTS子句时,仍然采用嵌套子查询的执行方式。同样可以采用left join 优化。
五 条件下推
MySQL引入了Materialization(物化)这一关键特性用于子查询(比如在IN/NOT IN子查询以及 FROM 子查询)优化。 具体实现方式是:在SQL执行过程中,第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ,后续对子查询结果集的访问将直接通过临时表获得。 与此同时,优化器还具有延迟物化子查询的能力,先通过其它条件判断子查询是否真的需要执行。物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。 与之相对的执行方式是对外表的每一行都对子查询进行调用,其执行计划中的查询类型为“DEPENDENT SUBQUERY”。
在使用Materialization(物化)能提高SQL性能的同时,也有必要留意相关SQL是否存在进一步优化空间的可能性。
外部查询条件不能够下推到复杂的视图或子查询的情况有:
- 聚合子查询;
- 含有LIMIT的子查询;
- UNION 或UNION ALL子查询;
- 输出字段中的子查询;
SELECT *
FROM (SELECT target,
Count(*)
FROM operation
GROUP BY target) t
WHERE target = 'rm-xxxx'
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 514 | const | 2 | Using where |
| 2 | DERIVED | operation | index | idx_4 | idx_4 | 519 | NULL | 20 | Using index |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
确定从语义上查询条件可以直接下推后,重写如下:
SELECT target,
Count(*)
FROM operation
WHERE target = 'rm-xxxx'
GROUP BY target
执行计划变为:
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
六 提前缩小范围
SELECT *
FROM my_order o
LEFT JOIN my_userinfo u
ON o.uid = u.uid
LEFT JOIN my_productinfo p
ON o.pid = p.pid
WHERE ( o.display = 0 )
AND ( o.ostaus = 1 )
ORDER BY o.selltime DESC
LIMIT 0, 15
该SQL语句原意是:先做一系列的左连接,然后排序取前15条记录。从执行计划也可以看出,最后一步估算排序记录数为90万,时间消耗为12秒。
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
由于最后WHERE条件以及排序均针对最左主表,因此可以先对my_order排序提前缩小数据量再做左连接。SQL重写后如下,执行时间缩小为1毫秒左右。
SELECT *
FROM (
SELECT *
FROM my_order o
WHERE ( o.display = 0 )
AND ( o.ostaus = 1 )
ORDER BY o.selltime DESC
LIMIT 0, 15
) o
LEFT JOIN my_userinfo u
ON o.uid = u.uid
LEFT JOIN my_productinfo p
ON o.pid = p.pid
ORDER BY o.selltime DESC
limit 0, 15
再检查执行计划:子查询物化后(select_type=DERIVED)参与JOIN。虽然估算行扫描仍然为90万,但是利用了索引以及LIMIT 子句后,实际执行时间变得很小。
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
| 1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
七 LIMIT 分页查询
分页查询是最常用的场景之一。
SELECT *
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
ORDER BY create_time
LIMIT 1000, 10;
数据量大时从1000000开始分页还是慢。
要知道数据库也并不知道第1000000条记录从什么地方开始,即使有索引也需要从头计算一次。出现这种性能问题,多数情形下是程序员偷懒了。在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值当成参数作为查询条件的。SQL重新设计如下:
SELECT *
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time limit 10;
在新设计下查询时间基本固定,不会随着数据量的增长而发生变化。
TIPS:
数据库编译器产生执行计划,决定着SQL的实际执行方式。但是编译器只是尽力服务,所有数据库的编译器都不是尽善尽美的。
写SQL的时候,尽量简洁清晰短小(对数据库压力小),对于业务复杂的SQL尽量找dba先评估下。
参考:
http://mysql.taobao.org/monthly/2017/03/03/