MySQL索引提示-强制索引和忽略索引

背景

昨天在迭代新功能时,遇到了一个问题,分享一下。
我们有一个大表,千万级数据,大概结构如下:

CREATE TABLE `msg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL COMMENT '用户id',
  `nickname` varchar(128) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '用户昵称',
  `room_id` int(11) NOT NULL DEFAULT '0' COMMENT '房间id',
  `msg` varchar(256) CHARACTER SET utf8mb4 DEFAULT NULL,
  `add_time` int(11) NOT NULL DEFAULT '0' COMMENT '添加时间',
  `del_flag` tinyint(2) NOT NULL DEFAULT '0' COMMENT '删除标记',
  `check_status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '审核状态',
  PRIMARY KEY (`id`),
  KEY `idx` (`room_id`,`add_time`,`check_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句如下:

SELECT * FROM `msg`  
WHERE room_id = 225928  
AND (add_time >= 1653926400 and add_time < 1654012800) 
AND uid not in (1111111,2222222) 
ORDER BY id desc 
LIMIT 10;

Explain 执行之后发现 key 是 PRIMARY, possible_keys 是 idx。
当然,Explain 的显示信息并不一定正确,实际查询一下试试看(建议在mysql负载不高时试验,很慢的哦)

slow query
慢出天际了!!!

解释与解决

说明可能如 Explain 显示,没走idx这个索引,而扫描了全表。MySQL 的查询优化并不完全可靠,它认为全表扫描代价更小时,会按全表扫描走逐渐索引。

那么怎么办呢?

Index Hints 索引提示

MySQL 有三种索引提示:

  • USE 用指定的某个索引去做查询,不再考虑其他可用的索引(可以指定多个索引,但是MySQL也可能不会用指定的这些索引)
  • FORCE 强制MySQL使用一个特定的索引查询
  • IGNORE 不要使用某些索引查询

上面遇到的情况我们可以使用 Force

SELECT * FROM `msg`  
FORCE INDEX(index_team_id_add_time)
WHERE room_id = 225928  AND (add_time >= 1653926400 and add_time < 1654012800) 
AND uid not in (1111111,2222222) 
ORDER BY id desc LIMIT 10;

我们执行一下看看:
force index
多测试几次,发现确实快了很多,达成预期效果。

more

  • 索引提示建议配合 explain 使用
  • 测试的时候务必在流量谷底,以免影响生产

参考:


MySQL索引提示-强制索引和忽略索引
https://github.13sai.com/2022/06/02/401/
作者
13sai
许可协议