MySQL中USE INDEX 和 FORCE INDEX
Contents
问题
在一次生产环境排查性能问题时, 发现有个请求在一些用户的数据量比较大的情况下, 最高耗时差不多要3s. 而且还是一个轮询的请求.
原因
在排查问题时, 定位到是执行某条SQL时在用户的数据比较大的情况下, SQL执行耗时要1.5s.
mysql> SELECT count(1)
-> FROM
-> cc_session cs
-> LEFT JOIN users_platform cp ON cs.user_id = cp.user_id
-> AND cs.to_openid = cp.open_id
-> WHERE
-> cs.`status` = 0
-> AND cs.user_id = 219
-> AND cs.agent_user_id = 219
-> AND cs.create_time < DATE_SUB(now(), INTERVAL 10 MINUTE)
-> AND cp.cc_open = 1;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (1.50 sec)
它的执行计划如下:
mysql> explain SELECT count(1) FROM cc_session cs LEFT JOIN users_platform cp ON cs.user_id = cp.user_id AND cs.to_openid = cp.open_id WHERE cs.`status` = 0 AND cs.user_id = 219 AND cs.agent_user_id = 219 AND cs.create_time < DATE_SUB(now(), INTERVAL 10 MINUTE) AND cp.cc_open = 1;
+----+-------------+-------+------+---------------------------------------+---------------------+---------+------------------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------------+---------------------+---------+------------------------+------+------------------------------------+
| 1 | SIMPLE | cp | ref | uid_opid | uid_opid | 4 | const | 50 | Using index condition; Using where |
| 1 | SIMPLE | cs | ref | id_from_to_close_uq,idx_user_agent_id | id_from_to_close_uq | 194 | uniweibo_v2.cp.open_id | 127 | Using index condition; Using where |
+----+-------------+-------+------+---------------------------------------+---------------------+---------+------------------------+------+------------------------------------+
2 rows in set (0.00 sec)
两张表的索引如下:
mysql> show index from cc_session;
+------------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cc_session | 0 | PRIMARY | 1 | id | A | 3279492 | NULL | NULL | | BTREE | | |
| cc_session | 0 | id_from_to_close_uq | 1 | to_openid | A | 25822 | NULL | NULL | | BTREE | | |
| cc_session | 0 | id_from_to_close_uq | 2 | from_openid | A | 3279492 | NULL | NULL | | BTREE | | |
| cc_session | 0 | id_from_to_close_uq | 3 | closed_time | A | 3279492 | NULL | NULL | | BTREE | | |
| cc_session | 1 | idx_user_agent_id | 1 | user_id | A | 513 | NULL | NULL | | BTREE | | |
| cc_session | 1 | idx_user_agent_id | 2 | agent_user_id | A | 1886 | NULL | NULL | | BTREE | | |
+------------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
mysql> show index from users_platform;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_platform | 0 | PRIMARY | 1 | id | A | 373 | NULL | NULL | | BTREE | | |
| users_platform | 1 | uid_opid | 1 | user_id | A | 373 | NULL | NULL | | BTREE | | |
| users_platform | 1 | uid_opid | 2 | open_id | A | 373 | NULL | NULL | | BTREE | | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql>
由执行计划可知,它分别使用了cc_session表的id_from_to_close_uq索引, 和users_platform表中的uid_opid索引.
使用 use index 建议MySQL使用其他索引
修改之后的SQL如下:
mysql> SELECT count(1)
-> FROM
-> cc_session cs use index (idx_user_agent_id)
-> LEFT JOIN users_platform cp use INDEX (uid_opid)
-> ON cs.user_id = cp.user_id
-> AND cs.to_openid = cp.open_id
-> WHERE
-> cs.`status` = 0
-> AND cs.user_id = 219
-> AND cs.agent_user_id = 219
-> AND cs.create_time < DATE_SUB(now(), INTERVAL 10 MINUTE)
-> AND cp.cc_open = 1;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql>
耗时从1.5秒,降低到0.01秒
执行计划如下:
mysql> explain SELECT count(1)
-> FROM
-> cc_session cs use index (idx_user_agent_id)
-> LEFT JOIN users_platform cp use INDEX (uid_opid)
-> ON cs.user_id = cp.user_id
-> AND cs.to_openid = cp.open_id
-> WHERE
-> cs.`status` = 0
-> AND cs.user_id = 219
-> AND cs.agent_user_id = 219
-> AND cs.create_time < DATE_SUB(now(), INTERVAL 10 MINUTE)
-> AND cp.cc_open = 1;
+----+-------------+-------+------+-------------------+-------------------+---------+--------------------------------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+--------------------------------+-------+------------------------------------+
| 1 | SIMPLE | cs | ref | idx_user_agent_id | idx_user_agent_id | 8 | const,const | 22966 | Using where |
| 1 | SIMPLE | cp | ref | uid_opid | uid_opid | 180 | const,uniweibo_v2.cs.to_openid | 1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+--------------------------------+-------+------------------------------------+
2 rows in set (0.00 sec)
mysql>
use index 和 force index
use index : 是建议MySQL去使用这个索引.最后到底是用不用, 还是由MySQL来决定. 如果MySQL还是觉得全表扫描来得快, 那即使是有索引, 它还是会使用全表扫描. force index : 是强制MySQL去使用这个索引. 如果用不上, 就全表. 如果能用上, 就一定会使用该索引.