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

更新日期: 2018-12-15阅读: 3.2k标签: 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


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

node如何和MySQL进行通信

Node.js与MySQL交互操作有很多库,常用最多的是mysql模块,mysql数注意:安装前先把目录cd到node.exe所在目录下,这样执行安装命令时,这篇文章主要介绍:链接mysql的流程、数据库连接参数说明、MYSQL CURD操作、连接池Pooling connections、断线重连、防止SQL注入

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

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

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

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

MySQL技术内幕:InnoDB存储引擎

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

MYSQL中视图的使用

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

Mysql常用函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。

mysql innodb索引原理

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

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

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

mysql统计行数时到底应该怎么count

每个人在写代码时都有遇到过要获取MYSQL表里数据行数的情况,多数人获取数据表行数时都用COUNT(*),但同时也流传了不少其他方式,比如说COUNT(1)、COUNT(主键)、COUNT(字段)。

MySQL有哪些索引类型?

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

点击更多...

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