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

时间: 2018-12-15阅读: 1635标签: 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.云服务推荐: 国内主流云服务商,各类云产品的最新活动,优惠券领取。地址:阿里云腾讯云华为云

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

MYSQL中视图的使用

什么是视图?执行一条SQL,将结果集保存在一张虚拟表中。在视图创建后,可以用与表基本相同的方式使用(查询、过滤、排序数据、与其他视图或连结、(添加、更新))视图只是用来查看存储在别处的数据的设施,本身不包含数据,返回的数据也是从其他表检索出来的。

mysql innodb索引原理

innodb存储引擎表是索引组织表,表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B+树,其叶子结点中存放的就是整张表的行记录数据,这些叶子节点成为数据页。

MySQL技术内幕:InnoDB存储引擎

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

MySQL设置时区和默认编码

学习spring boot时要在Windows本地安装MySQL5.7,配置好之后项目,启动之后提示需要设置时区,并且在使用过程中发现出现乱码,中文无法显示,出现上述问题的主要是MySQL5.7中没有设置时区和编码。

MySQL索引分类,90%的开发都不知道

MySQL的索引分类问题一直让人头疼,几乎所有的资料都会给你列一个长长的清单,给你介绍什么主键索引、单值索引,覆盖索引,自适应哈希索引,全文索引,聚簇索引,非聚簇索引等

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

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

前端开发对MySql使用总结

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

MySQL中Explain初识

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

Mysql使用索引可能失效的场景

WHERE字句的查询条件里有不等于号(WHERE column!=…),MYSQL将无法使用索引;类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=…),MYSQL将无法使用索引

mysql图形化管理工_ mysql可视化工具哪个好?

MySQL是一个非常流行的小型关系型数据库管理系统。目前MySQL被广泛地应用在Internet上的 中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据 库

点击更多...

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