Mysql之force index

Mysql的查询优化一般场景下好用，但在一些特殊场景下还是需要直接指定查询索引，这里复盘一个慢查询问题的修复。

表结构：

show create table wK_qy_messageG
*************************** 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 1G
*************************** 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 1G
*************************** 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 1G
*************************** 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 1G
*************************** 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 1G
*************************** 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 1G
*************************** 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 1G
*************************** 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 1G
*************************** 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 1G
*************************** 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;