mysql use index用法_MySQL中USE INDEX 和 FORCE INDEX

问题

在一次生产环境排查性能问题时, 发现有个请求在一些用户的数据量比较大的情况下, 最高耗时差不多要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去使用这个索引. 如果用不上, 就全表. 如果能用上, 就一定会使用该索引.