关闭

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

时间: 2018-12-15阅读: 1201标签: mysql

有一个业务是查询最新审核的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`);


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

链接:https://mengkang.net/1302.html


站长推荐

1.云服务推荐: 国内主流云服务商,各类云产品的最新活动,优惠券领取。地址:阿里云腾讯云华为云

2.广告联盟: 整理了目前主流的广告联盟平台,如果你有流量,可以作为参考选择适合你的平台点击进入

链接: http://www.fly63.com/article/detial/1592

关闭

前端开发对MySql使用总结

数据库引擎使用,这里仅仅只介绍常用的两种引擎,而InnoDB是从MySQL 5.6.版本以后InnoDB就是作为默认启动使用的存储引擎。那么这里下面既是对上面几个概念进行基本的日常操作。

MySQL为字段添加默认时间

在数据表中,要记录每条数据是什么时候创建的,不需要应用程序去特意记录,而由数据数据库获取当前时间自动记录创建时间;实现方式:将字段类型设为 TIMESTAMP

MySQL中Explain初识

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了。

MySQL查漏补缺

唯一索引比普通索引快吗, 为什么?MySQL由哪些部分组成, 分别用来做什么.MySQL查询缓存有什么弊端, 应该什么情况下使用, 8.0版本对查询缓存有什么变更.

一条sql语句在mysql中是如何执行的

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的。

mysql中的CHAR和VARCHAR到底支持多长?

最近在研究MySQL的数据类型,我们知道,选择合适的数据类型和数据长度对MySQL的性能影响是不可忽视的,小字段意味着可以MySQL可以读取更多的记录,从而加快查询速度。

MySQL技术内幕:InnoDB存储引擎

本文绝大部分内容来源《MySQL技术内幕:InnoDB存储引擎》一书。InnoDB存储引擎是多线程模型,其后台有多个不同的后台线程,负责处理不同的任务。

MySQL 常用30种SQL查询语句优化方法

MySQL查询语句优化方法:应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

MySQL有哪些索引类型?

从数据结构角度B+树索引(O(log(n)));hash索引;FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了);R-Tree索引(用于对GIS数据类型创建SPATIAL索引)

mysql数据库使用insert语句插入中文数据报错

在mysql的命令行模式中,通过insert语句插入中文数据的时候报错,类似于下面这样,造成这个错误通常是由于创建数据表的时候使用了不正确的编码格式,可以使用如下命令查看操作的目标数据表的编码格式。

点击更多...

内容以共享、参考、研究为目的,不存在任何商业目的。其版权属原作者所有,如有侵权或违规,请与小编联系!情况属实本人将予以删除!