记一次SQL优化经历

一条慢SQL导致线上服务器CPU占用报警.具体如下: 

SELECT * FROM video v RIGHT OUTER JOIN ( SELECT id FROM video USE INDEX(primary,idx_userid_status_createtime,channelId,createtime) WHERE 1 AND userid=223179 AND status IN (0,2,7,11) AND forumid=249943 AND id >=15552941 AND id <= 69684349 ORDER BY id DESC LIMIT 0, 50 ) t using (id) 

SQL优化不就是三板斧嘛.看索引,explain, show porfiles 

1.这是一条简单的SQL 使用 right outer join 也是为了能提高查询效率,采用延迟关联的方式，这样在偏移量大的时候性能提升很大

2.经测试 这条SQL 在id >=15552941 AND id <= 69684349时运行很慢, 但是把id增加两万 69704349时,就很快基本秒出的,

3.怀疑是use index 的问题,于是做了一下测试

本次测试均id>=min(id) and id<=max(id) 并 去掉 forumid
用户	视频个数	不带primary主键	带primary主键	去掉use index
a	20610	50 rows in set (0.12 sec)	50 rows in set (0.14 sec)	50 rows in set (0.49 sec)
b	38889	50 rows in set (0.10 sec)	50 rows in set (0.20 sec)	50 rows in set (0.29 sec)
c	77656	50 rows in set (0.24 sec)	50 rows in set (0.15 sec)	50 rows in set (0.49 sec)
问题客户	137114	50 rows in set (0.18 sec)	50 rows in set (0.31 sec)	50 rows in set (0.18 sec)
e	121738	50 rows in set (0.15 sec)	50 rows in set (0.18 sec)	50 rows in set (0.17 sec)
f	344498	50 rows in set (0.28 sec)	50 rows in set (0.30 sec)	50 rows in set (0.29 sec)
g	10969094	50 rows in set (13.00 sec)	50 rows in set (0.25 sec)	50 rows in set (0.36 sec)
问题sql	前置条件:id >=15552941    AND id <= 69684349
		条件 forumid	不带primary主键	带primary主键	去掉use index
	问题客户	249943	1 row in set (1.31 sec)	1 row in set (44.66 sec)	1 row in set (0.57 sec)
		239534	1 row in set (0.47 sec)	1 row in set (5.17 sec)	1 row in set (0.23 sec)
		230047	1 row in set (1.27 sec)	1 row in set (37.36 sec)	1 row in set (0.51 sec)
	前置条件:id >=15699738 AND id <= 69878053
		条件 forumid	不带primary主键	带primary主键	去掉use index
	问题客户	249943	50 rows in set (0.30 sec)	50 rows in set (0.15 sec)	50 rows in set (0.08 sec)
		239534	50 rows in set (0.33 sec)	50 rows in set (5.96 sec)	50 rows in set (0.20 sec)
		230047	50 rows in set (1.07 sec)	50 rows in set (37.67 sec)	50 rows in set (0.51 sec)

可以发现 得出结论:

1.在去掉 use index 的时候 SQL的执行效率都很快, 所以有时候不要轻易加use index 强制使用索引, MySQL InnoDB自己底层的优化可能会更好. 

2.优化SQL一定从业务本身出发, 这次 优化 忽略了 forumid这个字段, 本身没有建索引也不适合建索引, 如果去掉这个条件,即使是带着use index 依然很快

mysql> explain SELECT * FROM video v RIGHT OUTER JOIN ( SELECT id FROM video USE INDEX(primary,idx_userid_status_createtime,channelId,createtime) WHERE 1 AND userid=223179 AND status IN (0,2,7,11) AND forumid=249943 AND id >=15552941 AND id <= 69684349 ORDER BY id DESC LIMIT 0, 50 ) t using (id);
+----+-------------+------------+--------+--------------------------------------+---------+---------+------+----------+-------------+
| id | select_type | table      | type   | possible_keys                        | key     | key_len | ref  | rows     | Extra       |
+----+-------------+------------+--------+--------------------------------------+---------+---------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                 | NULL    | NULL    | NULL |       50 | NULL        | 
|  1 | PRIMARY     | v          | eq_ref | PRIMARY                              | PRIMARY | 4       | t.id |        1 | NULL        | 
|  2 | DERIVED     | video      | range  | PRIMARY,idx_userid_status_createtime | PRIMARY | 4       | NULL | 27086238 | Using where | 
+----+-------------+------------+--------+--------------------------------------+---------+---------+------+----------+-------------+
3 rows in set (0.00 sec)

mysql> mysql> explain SELECT * FROM video v RIGHT OUTER JOIN ( SELECT id FROM video  WHERE 1 AND userid=223179 AND status IN (0,2,7,11) AND forumid=249943 AND id >=15552941 AND id <= 69684349 ORDER BY id DESC LIMIT 0, 50 ) t using (id);
+----+-------------+------------+--------+---------------------------------------------------------+---------+---------+------+--------+------------------------------------+
| id | select_type | table      | type   | possible_keys                                           | key     | key_len | ref  | rows   | Extra                              |
+----+-------------+------------+--------+---------------------------------------------------------+---------+---------+------+--------+------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                    | NULL    | NULL    | NULL |     50 | NULL                               | 
|  1 | PRIMARY     | v          | eq_ref | PRIMARY                                                 | PRIMARY | 4       | t.id |      1 | NULL                               | 
|  2 | DERIVED     | video      | range  | PRIMARY,userid,valid_video,idx_userid_status_createtime | userid  | 8       | NULL | 210560 | Using index condition; Using where | 
+----+-------------+------------+--------+---------------------------------------------------------+---------+---------+------+--------+------------------------------------+
3 rows in set (0.01 sec)

mysql> explain SELECT * FROM video v RIGHT OUTER JOIN ( SELECT id FROM video USE INDEX(primary,idx_userid_status_createtime,channelId,createtime) WHERE 1 AND userid=223179 AND status IN (0,2,7,11)  AND id >=15552941 AND id <= 69684349 ORDER BY id DESC LIMIT 0, 50 ) t using (id);
+----+-------------+------------+--------+--------------------------------------+------------------------------+---------+------+--------+------------------------------------------+
| id | select_type | table      | type   | possible_keys                        | key                          | key_len | ref  | rows   | Extra                                    |
+----+-------------+------------+--------+--------------------------------------+------------------------------+---------+------+--------+------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                 | NULL                         | NULL    | NULL |     50 | NULL                                     | 
|  1 | PRIMARY     | v          | eq_ref | PRIMARY                              | PRIMARY                      | 4       | t.id |      1 | NULL                                     | 
|  2 | DERIVED     | video      | range  | PRIMARY,idx_userid_status_createtime | idx_userid_status_createtime | 9       | NULL | 302116 | Using where; Using index; Using filesort | 
+----+-------------+------------+--------+--------------------------------------+------------------------------+---------+------+--------+------------------------------------------+
3 rows in set (0.00 sec)

在 使用 use index 和不使用 use index 以及去掉 forumid 时 可以看到使用的索引并不一样,