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负载不高时试验,很慢的哦)

16s,慢出天际了!!!

解释与解决

说明可能如 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;

我们执行一下看看:

多测试几次,发现确实快了很多,达成预期效果。

more

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

参考:

文章首发于blog: MySQL索引提示-强制索引和忽略索引

本作品采用《CC 协议》,转载必须注明作者和本文链接
收藏前不妨点个赞试试!!! github.13sai.com/ 分享开发知识,欢迎交流。公众号:codesai
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!