通过一个 create sort index 案例彻底弄懂 mysql inndb 索引的优化方法

栏目: 数据库 · Mysql · 发布时间: 6年前

内容简介:有一个业务是查询最新审核的5条数据查看当时的监控情况 cpu 使用率是超过了100%,查看该表的结构

有一个业务是查询最新审核的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_ft_pt_let` (`audit_time`,`last_edit_time`)
) ENGINE=InnoDB AUTO_INCREMENT=19956 DEFAULT CHARSET=utf8mb4;

索引有一个 audit_time 在左边的联合索引。

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

status='ONLINE'

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

所以是非常低效的。

改进思路 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。

该索引的弊端

如果 idx_audit_status 里扫描5行都是 statusONLINE ,那么只需扫描5行;

如果 idx_audit_status 里扫描前100万行中,只有4行 statusONLINE ,则需要扫描100万零1行,才能得到需要的5行记录。

索引需要扫描的行数不确定。

该索引的优势

在索引里面有 status 的值,就不用回表去查询。

疑惑

我猜到的两个处理方式

  1. 根据 status , audit_time , id 把索引数据进行排序,一次性排序,然后找出前5行;
  2. 遍历一遍通过 status 过滤出符合要求的行,然后再排序,找出前5行。

不管怎样,这里在回表的时候只有5行数据的查询了,在 iops 上会大大减少。

改进思路 2

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

实际统计

select count(*) from `th_content` where `audit_time` > 1541984478 and `status` = 'ONLINE';

只有7行。

因为 业务属性 是取最新的5条,往往都是头部数据。所以我们在使用 idx_status_audit 索引的时候,只需要扫描12行就能取到了所有的数据。

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


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

标签: mysql 索引

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

计算机组成(第 6 版)

计算机组成(第 6 版)

Andrew S. Tanenbaum、Todd Austin / 刘卫东、宋佳兴 / 机械工业出版社 / 2014-8-19 / CNY 99.00

本书采用结构化方法来介绍计算机系统,书的内容完全建立在“计算机是由层次结构组成的,每层完成规定的功能”这一概念之上。作者对本版进行了彻底的更新,以反映当今最重要的计算机技术以及计算机组成和体系结构方面的最新进展。书中详细讨论了数字逻辑层、微体系结构层、指令系统层、操作系统层和汇编语言层,并涵盖了并行体系结构的内容,而且每一章结尾都配有丰富的习题。本书适合作为计算机专业本科生计算机组成与结构课程的教......一起来看看 《计算机组成(第 6 版)》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具