Mysql之force index
Mysql的查询优化一般场景下好用，但在一些特殊场景下还是需要直接指定查询索引，这里复盘一个慢查询问题的修复。
表结构：
show create table wK_qy_message\G *************************** 1. row *************************** Table: wK_qy_message Create Table: CREATE TABLE `wK_qy_message` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `wxid` varchar(200) CHARACTER SET utf8 NOT NULL COMMENT 'x号', `qyid` varchar(100) NOT NULL COMMENT '企业号 id', `chatServerId` varchar(100) NOT NULL COMMENT '联系人服务端 id', `msgSvrId` bigint(20) NOT NULL COMMENT '消息服务端 id', `msgId` bigint(20) NOT NULL COMMENT '消息客户端 id', PRIMARY KEY (`id`), UNIQUE KEY `wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex` (`wxid`,`qyid`,`chatServerId`,`msgSvrId`), KEY `idx_wxid` (`wxid`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
表状态：
mysql> show table status like 'wk_qy_message'\G *************************** 1. row *************************** Name: wk_qy_message Engine: InnoDB Version: 10 Row_format: Compact Rows: 96841188 Avg_row_length: 1007Data_length: 97555316736 Max_data_length: 0 Index_length: 36063674368 Data_free: 4194304 Auto_increment: 651285142 Create_time: 2020-10-12 17:52:01 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: Block_format: Original 1 row in set (0.00 sec)
不使用强制索引查询：
desc select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message where (`wxid` = 'wxid_1') order by `id` desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: index possible_keys: wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex,idx_wxid key: PRIMARY key_len: 8 ref: NULL rows: 8782 Extra: Using where 1 row in set (0.00 sec) mysql> desc select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message where (`wxid` = 'wxid_2') order by `id` desc limit 1\ G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: ref possible_keys: wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex,idx_wxid key: idx_wxid key_len: 602 ref: const rows: 682248 Extra: Using where 1 row in set (0.00 sec)
强制使用索引 wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex：
DESC select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message force index(wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex) where (`wxid` = 'wxid_1') order by `id` desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: ref possible_keys: wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindexkey: wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex key_len: 602 ref: const rows: 11026 Extra: Using index condition; Using where; Using filesort 1 row in set (0.00 sec) desc select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message force index(wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex) where (`wxid` = 'wxid_2') order by `id` desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: ref possible_keys: wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex key: wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex key_len: 602 ref: const rows: 764316 Extra: Using index condition; Using where; Using filesort 1 row in set (0.00 sec)
这样看无论 where wxid 取值怎样,使用索引
wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex 检索数据,Ertxa 都是 Using
index condition; Using where; Using filesort
接下来换一个索引：
强制使用索引 wxid：
DESC select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message force index(idx_wxid) where (`wxid` = 'wxid_1') order by `id` desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: ref possible_keys: idx_wxid key: idx_wxid key_len: 602 ref: const rows: 11096 Extra: Using where 1 row in set (0.00 sec) DESC select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message force index(idx_wxid) where (`wxid` = 'wxid_2') order by `id` desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: ref possible_keys: idx_wxid key: idx_wxid key_len: 602 ref: const rows: 682248 Extra: Using where 1 row in set (0.00 sec)
这样看来
无论 where wxid 取值怎样,使用索引 wxid 检索数据,Ertxa 都是 Using where;
对比使用索引 wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex 执行时间为 1s 到 10s
不等
,而使用索引 wxid 后,该查询执行的时间为 10ms 到 30ms 不等;
如果不使用 force index,让查询自己选择索引,则如果 wxid 的数据量较小时,会选择主键索引检索数据,
此时查询时间非常大(1/8-21 点出现问题的时候就是这种情况)
如果不提示使用那个索引呢：
DESC select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message force index(idx_wxid) where (`wxid` = 'wxid_1') order by `id` desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: ref possible_keys: idx_wxid key: idx_wxid key_len: 602 ref: const rows: 11096 Extra: Using where 1 row in set (0.00 sec) DESC select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message where (`wxid` = 'wxid_2') order by `id` desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wk_qy_message type: index possible_keys: wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex,idx_wxid key: PRIMARY key_len: 8 ref: NULL rows: 9084 Extra: Using where 1 row in set (0.00 sec) select count(*) from wk_qy_message where (`wxid` = 'wxid_1') order by `id` desc limit 1\G *************************** 1. row *************************** count(*): 442888 1 row in set (0.56 sec) mysql> select count(*) from wk_qy_message where (`wxid` = 'wxid_2') order by `id` desc limitt 1\G *************************** 1. row *************************** count(*): 6077 1 row in set (0.01 sec)
结论
固定 idx_wxid 索引,不要让数据库自己选择索引,遇到很大的索引列的数目很小时优化器会选择主键,此时
导致执行时间巨大
结论:选择 idx_wxid 索引,减少查询时间
结论无论 where 条件中对应的 wxid 取值怎样,使用索引 wxid 检索数据,Ertxa 都是 Using where;
对比使用索引 wk_qy_message_wxid_qyid_chatServerId_msgSvrId_uindex 执行时间为 1s 到 10s
不等,而使用索引 wxid 后,该查询执行的时间为 10ms 到 30ms 不等;
如果不使用 force index,让查询自己选择索引,遇到 wxid 的数据量很大时,很选择 wx_id 索引检索数据,
此时很快 0.2s 返回查询结果;如果 wxid 的数据量较小时,会选择主键索引检索数据,此时查询时间非常大
(1/8-21 点出现问题的时候就是这种情况)
订正的语法如下
select `msgId`, `msgSvrId`, `createTime`, `insert_time` from wk_qy_message force index(idx_wxid) where (`wxid` = 'wxid_1') order by `id` desc limit 1;
