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去使用這個索引. 如果用不上, 就全表. 如果能用上, 就一定會使用該索引.
總結
以上是生活随笔為你收集整理的mysql use index用法_MySQL中USE INDEX 和 FORCE INDEX的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 苹果5G芯片研发失败：网友议论纷纷
- 下一篇： php postgresql多条,PHP