MySQL SQL 优化之覆盖索引

本贴最后更新于 1941 天前,其中的信息可能已经时移世改

利用索引提升 SQL 的查询效率是我们经常使用的一个技巧,但是有些时候 MySQL 给出的执行计划却完全出乎我们的意料,我们预想 MySQL 会通过索引扫描完成查询,但是 MySQL 给出的执行计划却是通过全表扫描完成查询的,其中的某些场景我们可以利用覆盖索引进行优化。


前些天,有个同事跟我说:“我写了个 SQL,SQL 很简单,但是查询速度很慢,并且针对查询条件创建了索引,然而索引却不起作用,你帮我看看有没有办法优化?”。

我对他提供的 case 进行了优化,并将优化过程整理了下来。


我们先来看看优化前的表结构、数据量、SQL、执行计划、执行时间等。

1. 表结构:

CREATE TABLE `t_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_code` char(12) NOT NULL,
  `order_amount` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

隐藏了部分不相关字段后,可以看到表足够简单, 并且在 order_code 上创建了唯一性索引 uni_order_code。

2. 数据量:316977

这个数据量还是比较小的,不过如果 SQL 足够差,一样会查询很慢。

3. SQL:

select order_code, order_amount from t_order order by order_code limit 1000;

哇,SQL 足够简单,不过有时候越简单也越难优化。

4. 执行计划:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order ALL NULL NULL NULL NULL 316350 Using filesort

全表扫描、文件排序,注定查询慢!

那为什么 MySQL 没有利用索引(uni_order_code)扫描完成查询呢?因为 MySQL 认为这个场景利用索引扫描并非最优的结果。我们先来看下执行时间,然后再来分析为什么没有利用索引扫描。

5. 执行时间:260ms

的确,执行时间太长了,如果表数据量继续增长下去,性能会越来越差。


我们来分析下 MySQL 为什么使用全表扫描、文件排序,而没有使用索引扫描、利用索引顺序:

1. 全表扫描、文件排序:

虽然是全表扫描,但是扫描是顺序的(不管机械硬盘还是 SSD 顺序读写性能都是高的),并且数据量不是特别大,所以这部分消耗的时间应该不是特别大,主要的消耗应该是在排序上。

2. 利用索引扫描、利用索引顺序:

uni_order_code 是二级索引,索引上保存了(order_code,id),每扫描一条索引需要根据索引上的 id 定位(随机 IO)到数据行上读取 order_amount,需要 1000 次随机 IO 才能完成查询,而机械硬盘随机 IO 的效率是极低的(机械硬盘每秒寻址几百次)。

根据我们自己的分析选择全表扫描相对更优。如果把 limit 1000 改成 limit 10,则执行计划会完全不一样。


既然我们已经知道是因为随机 IO 导致无法利用索引,那么有没有办法消除随机 IO 呢?

有,覆盖索引。


我们来看看利用覆盖索引优化后的索引、执行计划、执行时间。

1. 创建索引:

ALTER TABLE `t_order` 
ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);

创建了复合索引 idx_ordercode_orderamount(order_code,order_amount),将 select 的列 order_amount 也放到索引中。

2. 执行计划:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order index NULL idx_ordercode_orderamount 42 NULL 1000 Using index

执行计划显示查询会利用覆盖索引,并且只扫描了 1000 行数据,查询的性能应该是非常好的。

3. 执行时间:13ms

从执行时间来看,SQL 的执行时间提升到原来的 1/20,已经达到我们的预期。


总结:

覆盖索引是 select 的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列,还包含查询条件、排序等。

要写出性能很好的 SQL 不仅需要学习 SQL,还要能看懂数据库执行计划,了解数据库执行过程、索引的数据结构等。

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    673 引用 • 535 回帖
  • 覆盖索引
    1 引用
  • 索引排序
    1 引用

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...
loujinhe
我就是船长,船长就是我 苏州

推荐标签 标签

  • 以太坊

    以太坊(Ethereum)并不是一个机构,而是一款能够在区块链上实现智能合约、开源的底层系统。以太坊是一个平台和一种编程语言 Solidity,使开发人员能够建立和发布下一代去中心化应用。 以太坊可以用来编程、分散、担保和交易任何事物:投票、域名、金融交易所、众筹、公司管理、合同和知识产权等等。

    34 引用 • 367 回帖 • 2 关注
  • H2

    H2 是一个开源的嵌入式数据库引擎,采用 Java 语言编写,不受平台的限制,同时 H2 提供了一个十分方便的 web 控制台用于操作和管理数据库内容。H2 还提供兼容模式,可以兼容一些主流的数据库,因此采用 H2 作为开发期的数据库非常方便。

    11 引用 • 54 回帖 • 638 关注
  • Kotlin

    Kotlin 是一种在 Java 虚拟机上运行的静态类型编程语言,由 JetBrains 设计开发并开源。Kotlin 可以编译成 Java 字节码,也可以编译成 JavaScript,方便在没有 JVM 的设备上运行。在 Google I/O 2017 中,Google 宣布 Kotlin 成为 Android 官方开发语言。

    19 引用 • 33 回帖 • 21 关注
  • Python

    Python 是一种面向对象、直译式电脑编程语言,具有近二十年的发展历史,成熟且稳定。它包含了一组完善而且容易理解的标准库,能够轻松完成很多常见的任务。它的语法简捷和清晰,尽量使用无异义的英语单词,与其它大多数程序设计语言使用大括号不一样,它使用缩进来定义语句块。

    534 引用 • 671 回帖
  • Webswing

    Webswing 是一个能将任何 Swing 应用通过纯 HTML5 运行在浏览器中的 Web 服务器,详细介绍请看 将 Java Swing 应用变成 Web 应用

    1 引用 • 15 回帖 • 635 关注
  • NGINX

    NGINX 是一个高性能的 HTTP 和反向代理服务器,也是一个 IMAP/POP3/SMTP 代理服务器。 NGINX 是由 Igor Sysoev 为俄罗斯访问量第二的 Rambler.ru 站点开发的,第一个公开版本 0.1.0 发布于 2004 年 10 月 4 日。

    311 引用 • 546 回帖 • 56 关注
  • HBase

    HBase 是一个分布式的、面向列的开源数据库,该技术来源于 Fay Chang 所撰写的 Google 论文 “Bigtable:一个结构化数据的分布式存储系统”。就像 Bigtable 利用了 Google 文件系统所提供的分布式数据存储一样,HBase 在 Hadoop 之上提供了类似于 Bigtable 的能力。

    17 引用 • 6 回帖 • 32 关注
  • WordPress

    WordPress 是一个使用 PHP 语言开发的博客平台,用户可以在支持 PHP 和 MySQL 数据库的服务器上架设自己的博客。也可以把 WordPress 当作一个内容管理系统(CMS)来使用。WordPress 是一个免费的开源项目,在 GNU 通用公共许可证(GPLv2)下授权发布。

    45 引用 • 113 回帖 • 321 关注
  • JVM

    JVM(Java Virtual Machine)Java 虚拟机是一个微型操作系统,有自己的硬件构架体系,还有相应的指令系统。能够识别 Java 独特的 .class 文件(字节码),能够将这些文件中的信息读取出来,使得 Java 程序只需要生成 Java 虚拟机上的字节码后就能在不同操作系统平台上进行运行。

    180 引用 • 120 回帖 • 2 关注
  • 心情

    心是产生任何想法的源泉,心本体会陷入到对自己本体不能理解的状态中,因为心能产生任何想法,不能分出对错,不能分出自己。

    59 引用 • 369 回帖
  • Quicker

    Quicker 您的指尖工具箱!操作更少,收获更多!

    16 引用 • 68 回帖
  • 架构

    我们平时所说的“架构”主要是指软件架构,这是有关软件整体结构与组件的抽象描述,用于指导软件系统各个方面的设计。另外还有“业务架构”、“网络架构”、“硬件架构”等细分领域。

    139 引用 • 441 回帖
  • Hprose

    Hprose 是一款先进的轻量级、跨语言、跨平台、无侵入式、高性能动态远程对象调用引擎库。它不仅简单易用,而且功能强大。你无需专门学习,只需看上几眼,就能用它轻松构建分布式应用系统。

    9 引用 • 17 回帖 • 591 关注
  • RIP

    愿逝者安息!

    8 引用 • 92 回帖 • 285 关注
  • 招聘

    哪里都缺人,哪里都不缺人。

    189 引用 • 1056 回帖
  • FlowUs

    FlowUs.息流 个人及团队的新一代生产力工具。

    让复杂的信息管理更轻松、自由、充满创意。

    1 引用 • 3 关注
  • Rust

    Rust 是一门赋予每个人构建可靠且高效软件能力的语言。Rust 由 Mozilla 开发,最早发布于 2014 年 9 月。

    57 引用 • 22 回帖
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    330 引用 • 614 回帖
  • Love2D

    Love2D 是一个开源的, 跨平台的 2D 游戏引擎。使用纯 Lua 脚本来进行游戏开发。目前支持的平台有 Windows, Mac OS X, Linux, Android 和 iOS。

    14 引用 • 53 回帖 • 506 关注
  • Electron

    Electron 基于 Chromium 和 Node.js,让你可以使用 HTML、CSS 和 JavaScript 构建应用。它是一个由 GitHub 及众多贡献者组成的活跃社区共同维护的开源项目,兼容 Mac、Windows 和 Linux,它构建的应用可在这三个操作系统上面运行。

    15 引用 • 136 回帖
  • CSDN

    CSDN (Chinese Software Developer Network) 创立于 1999 年,是中国的 IT 社区和服务平台,为中国的软件开发者和 IT 从业者提供知识传播、职业发展、软件开发等全生命周期服务,满足他们在职业发展中学习及共享知识和信息、建立职业发展社交圈、通过软件开发实现技术商业化等刚性需求。

    14 引用 • 155 回帖
  • 运维

    互联网运维工作,以服务为中心,以稳定、安全、高效为三个基本点,确保公司的互联网业务能够 7×24 小时为用户提供高质量的服务。

    148 引用 • 257 回帖 • 1 关注
  • JetBrains

    JetBrains 是一家捷克的软件开发公司,该公司位于捷克的布拉格,并在俄国的圣彼得堡及美国麻州波士顿都设有办公室,该公司最为人所熟知的产品是 Java 编程语言开发撰写时所用的集成开发环境:IntelliJ IDEA

    18 引用 • 54 回帖
  • IDEA

    IDEA 全称 IntelliJ IDEA,是一款 Java 语言开发的集成环境,在业界被公认为最好的 Java 开发工具之一。IDEA 是 JetBrains 公司的产品,这家公司总部位于捷克共和国的首都布拉格,开发人员以严谨著称的东欧程序员为主。

    180 引用 • 400 回帖
  • GitBook

    GitBook 使您的团队可以轻松编写和维护高质量的文档。 分享知识,提高团队的工作效率,让用户满意。

    3 引用 • 8 回帖 • 1 关注
  • SendCloud

    SendCloud 由搜狐武汉研发中心孵化的项目,是致力于为开发者提供高质量的触发邮件服务的云端邮件发送平台,为开发者提供便利的 API 接口来调用服务,让邮件准确迅速到达用户收件箱并获得强大的追踪数据。

    2 引用 • 8 回帖 • 429 关注
  • CodeMirror
    1 引用 • 2 回帖 • 109 关注