背景

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

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 使用
  • 测试的时候务必在流量谷底，以免影响生产

参考：

许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

分享文章