一个案例彻底弄懂如何正确使用 mysql inndb 联合索引

i_garfileo 发布于2019-08-01 10:03 / 3423人阅读

摘要：查看该表的结构内容标题正文内容审核时间最近编辑时间资讯状态索引有一个在左边的联合索引，没有关于的索引。画了一个示意图，说明第一步的查询过程，粉红色部分表示最后需要回表查询的数据行。索引需要扫描的行数不确定。

原文：我的个人博客 https://mengkang.net/1302.html
工作了两三年，技术停滞不前，迷茫没有方向，不如看下我的直播 PHP 进阶之路

有一个业务是查询最新审核的5条数据

SELECT `id`, `title`
FROM `th_content`
WHERE `audit_time` < 1541984478
    AND `status` = "ONLINE"
ORDER BY `audit_time` DESC, `id` DESC
LIMIT 5;

查看当时的监控情况 cpu 使用率是超过了100%，show processlist看到很多类似的查询都是处于create sort index的状态。

查看该表的结构

CREATE TABLE `th_content` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT "" COMMENT "内容标题",
  `content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT "正文内容",
  `audit_time` int(11) unsigned NOT NULL DEFAULT "0" COMMENT "审核时间",
  `last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "最近编辑时间",
  `status` enum("CREATED","CHECKING","IGNORED","ONLINE","OFFLINE") CHARACTER SET utf8 NOT NULL DEFAULT "CREATED" COMMENT "资讯状态",
  PRIMARY KEY (`id`),
  KEY `idx_at_let` (`audit_time`,`last_edit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

索引有一个audit_time在左边的联合索引，没有关于status的索引。

分析上面的sql执行的逻辑：

从联合索引里找到所有小于该审核时间的主键id（假如在该时间戳之前已经审核了100万条数据，则会在联合索引里取出对应的100万条数据的主键 id）

未来如果有一个优化就好了，目前还有：~~对100个主键 id 排序，然后在下面一步回表操作中挨得近的主键可能一次磁盘 I/O 就都取到了~~

逐个回表，查出100万行记录，筛选出status="ONLINE"的行记录

最后对查询的结果进行排序（假如有50万行都是ONLINE，则继续对这50万行进行排序）

最后因为数据量很大，虽然只取5行，但是按照我们刚刚举的极端例子，实际查询了100万行数据，而且最后还在内存中进行了50万行数据库的内存排序。

所以是非常低效的。

画了一个示意图，说明第一步的查询过程，粉红色部分表示最后需要回表查询的数据行。
图中我按照索引存储规律来YY伪造填充了一些数据，如有不对请留言指出。希望通过这张图大家能够看到联合索引存储的方式和索引查询的方式

改进思路 1

范围查找向来不太好使用好索引的，如果我们增加一个audit_time, status的联合索引，会有哪些改进呢？

ALTER TABLE `th_content` ADD INDEX `idx_audit_status` (`audit_time`, `status`);

mysql> explain select `id`, `title` from `th_content` where `audit_time` < 1541984478 and `status` = "ONLINE" order by `audit_time` desc, `id` desc limit 5;
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
| id | select_type | table      | type  | possible_keys                            | key              | key_len | ref  | rows   | Extra       |
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | th_content | range | idx_at_ft_pt_let,idx_audit_status        | idx_audit_status | 4       | NULL | 209754 | Using where |
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+

细节：因为audit_time是一个范围查找，所以第二列的索引用不上了，只能用到audit_time，所以key_len是4。而下面思路2中，还是这两个字段key_len则是5。

还是分析下在添加了该索引之后的执行过程：

从联合索引里找到小于该审核时间的audit_time最大的一行的联合索引

然后依次往下找，因为< audit_time是一个范围查找，而第二列索引的值是分散的。所以需要依次往前查找，匹配出满足条件（status="ONLINE"）的索引行，直到取到第5行为止。

回表查询需要的具体数据

在上面的示意图中，粉红色标识满足第一列索引要求的行，依次向前查询，本个叶子节点上筛选到了3条记录，然后需要继续向左，到前一个叶子节点继续查询。直到找到5条满足记录的行，最后回表。

改进之处

因为在索引里面有status的值，所以在筛选满足status="ONLINE"行的时候，就不用回表查询了。在回表的时候只有5行数据的查询了，在iops上会大大减少。

该索引的弊端

如果idx_audit_status里扫描5行都是status是ONLINE，那么只需扫描5行；
如果idx_audit_status里扫描前100万行中，只有4行status是ONLINE，则需要扫描100万零1行，才能得到需要的5行记录。索引需要扫描的行数不确定。

改进思路 2

ALTER TABLE `th_content` DROP INDEX `idx_audit_status`;
ALTER TABLE `th_content` ADD INDEX `idx_status_audit` (`status`, `audit_time`);

这样不管是排序还是回表都毫无压力啦。

云数据库MySQL 联合索引彻底卸载MySQL mysql彻底卸载干净 mysql如何使用

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/48192.html

mysql 优化

摘要：这就是非聚簇索引主索引和次级索引都指向行在磁盘上的位置索引的叶子节点比较大，上面有索引对应的整条记录，所以查找数据的时候找到了索引后立马能拿到对应的数据，不用再回行到数据文件去拿数据。 mysql 优化一、表的优化与列类型选择 1. 表的优化 1.1 定长与变长核心且常用的字段，宜建成定长放在一个表中而varchar、text、blob等变长类型的字段，适合放在另外的表中，用主...

lixiang 2019-07-31 18:06 评论0 收藏0
MySQL InnoDB索引介绍及优化

摘要：以前只知道向互联网索取和吐槽，看到好的文章羡慕并认同，看到差的文章吐槽并叹息，觉得写得一点都不负责任。现在自己写博客也是怀着一颗忐忑之心，同勉正文一索引概念索引翻译为一个目录，用于快速定位我们想要找的数据的位置。前言：由于BOOS要求每个月写一篇文章,因此也申请了自己的博客，说来惭愧，工作了几年还没有博客，脸略红。以前只知道向互联网索取和吐槽，看到好的文章羡慕并认同，看到差的文章...

megatron 2019-07-31 16:29 评论0 收藏0
MySQL索引原理及慢查询优化

摘要：比如当张三这样的数据来检索时，树可以用来指定搜索方向，但下一个字段的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是的数据了，这个是非常重要的性质，即索引的最左匹配特性。背景 MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓好马配好鞍，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述...

MobService 2019-08-01 10:05 评论0 收藏0
MySQL学习记录：约束以及修改数据表

摘要：约束约束保证数据的完整性和一致性。约束分为表级约束和列级约束。父表子表所参照的表。外键列不存在索引的话，自动创建索引外键约束的参照操作这四个选项是指在在进行了外键约束的创建以后，在更新表的时候，子表是否也进行相应的操作。 showImg(https://segmentfault.com/img/bVSOus?w=1072&h=468); 约束约束保证数据的完整性和一致性。约束分...

ygyooo 2019-07-31 17:11 评论0 收藏0
MySQL 索引的原理与应用：索引类型，存储结构与锁

摘要：索引的原理与应用索引类型，存储结构与锁在数据结构与算法索引一节中，我们讨论了这样的文件索引以及全文索引的基础算法，本文则会针对文件索引在关系型数据库中的实际应用进行探讨。这个索引的是数据表的主键，因此表数据文件本身就是主索引。 showImg(https://segmentfault.com/img/remote/1460000018453572?w=1280&h=554); 本文节选...

bingchen 2019-08-06 14:54 评论0 收藏0