问题

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