写点什么

MySQL 索引性能分析概要

  • 2019-12-05
  • 本文字数:2379 字

    阅读完需:约 8 分钟

MySQL 索引性能分析概要

上一篇文章 MySQL 索引设计概要 介绍了影响索引设计的几大因素,包括过滤因子、索引片的宽窄与大小以及匹配列和过滤列。在文章的后半部分介绍了 数据库索引设计与优化 一书中,理想的三星索引的设计流程和套路,到目前为止虽然我们掌握了单表索引的设计方法,但是却没有分析预估索引耗时的能力。



在本文中,我们将介绍书中提到的两种分析索引性能的方法:基本问题法(BQ)和快速估算上限法(QUBE),这两种方法能够帮助我们快速分析、估算索引的性能,及时发现问题。

基本问题法

当我们需要考虑对现有的 SELECT 查询进行分析时,哪怕没有足够的时间,也应该使用基本问题法对查询进行评估,评估的内容非常简单:现有的索引或者即将添加的索引是否包含了 WHERE 中使用的全部列,也就是对于当前查询来说,是否有一个索引是半宽索引。



在上一篇文章中,我们介绍过宽索引和窄索引,窄索引 (username) 其实就叫做半宽索引,其中包含了 WHERE 中的全部的列 username,当前索引的对于该查询只有一颗星,它虽然避免了无效的回表查询造成的随机 IO,但是如果当前的索引的性能仍然无法满足需要,就可以添加 age 将该索引变成宽索引 (username, age) 以此来避免回表访问造成的性能影响;对于上图中的简单查询,索引 (username, age) 其实已经是一个三星索引了,但是对于包含 ORDER BY 或者更加复杂的查询,(username, age) 可能就只是二星索引:



在这时如果该索引仍然不能满足性能的需要,就可以考虑按照上一篇文章 MySQL 索引设计概要 中提供的索引设计方法重新设计了。


虽然基本问题法能够快速解决一些由于索引造成的问题,但是它并不能保证足够的性能,当表中有 (city, username, age) 索引,谓词为 WHERE username="draveness" AND age="21" 时,使用基本问题法并不能得出正确的结果。

快速估算上限法

基本问题法非常简单,它能够最短的时间内帮助我们评估一个查询的性能,但是它并不能准确地反映一个索引相关的性能问题,而快速估算上限法就是一种更加准确、复杂的方法了;其目的在于在程序开发期间就能将访问路径缓慢的问题暴露出来,这个估算方法的输出就是本地响应时间(Local Response Time):



本地响应时间就是查询在数据库服务器中的耗时,不包括任何的网络延迟和多层环境的通信时间,仅包括执行查询任务的耗时。

响应时间

本地响应时间等于服务时间和排队时间的总和,一次查询请求需要在数据库中等待 CPU 以及磁盘的响应,也可能会因为其他事务正在对同样的数据进行读写,导致当前查询需要等待锁的获取,不过组成响应时间中的主要部分还是磁盘的服务时间:



QUBE 在计算的过程中会忽略除了磁盘排队时间的其他排队时间,这样能够简化整个评估流程,而磁盘的服务时间主要还是包括同步读写以及异步读几个部分:



在排除了上述多个部分的内容,我们得到了一个非常简单的估算过程,整个估算时间的输入仅为随机读和顺序读以及数据获取的三个输入,而它们也是影响查询的主要因素:



其中数据获取的过程在比较不同的索引对同一查询的影响是不需要考虑的,因为同一查询使用不同的索引也会得到相同的结果集,获取的数据也是完全相同的。

访问

当 MySQL 读取一个索引行或者一个表行时,就会发生一次访问,当使用全表扫描或者扫描索引片时,读取的第一个行就是随机访问,随机访问需要磁盘进行寻道和旋转,所以其代价巨大,而接下来顺序读取的所有行都是通过顺序访问读取的,代价只有随机访问的千分之一。


如果大量的顺序读取索引行和表行,在原理上可能会造成一些额外的零星的随机访问,不过这对于整个查询的估算来说其实并不重要;在计算本地响应时间时,仍然会把它们当做顺序访问进行估算。

示例

在这里,我们简单地举一个例子来展示如何计算查询在使用某个索引时所需要的本地响应时间,假设我们有一张 users 表,其中有一千万条数据:



在该 users 表中除了主键索引之外,还具有以下 (username, city)、(username, age) 和 (username) 几个辅助索引,当我们使用如下所示的查询时:



两个查询条件分别有着 0.05% 和 12% 的过滤因子,该查询可以直接使用已有的辅助索引 (username, city),接下来我们根据表中的总行数和过滤因子开始估算这一步骤 SQL 的执行时间:



该查询在开始时会命中 (username, city) 索引,扫描符合条件的索引片,该索引总共会访问 10,000,000 * 0.05% * 12% = 600 条数据,其中包括 1 次的随机访问和 599 次的顺序访问,因为该索引中的列并不能满足查询的需要,所以对于每一个索引行都会产生一次表的随机访问,以获取剩余列 age 的信息:



在这个过程中总共产生了 600 次随机访问,最后取回结果集的过程中也会有 600 次 FETCH 操作,从总体上来看这一次 SQL 查询共进行了 601 次随机访问、599 次顺序访问和 600 次 FETCH,根据上一节中的公式我们可以得到这个查询的用时约为 6075.99ms 也就是 6s 左右,这个时间对于绝大多数应用都是无法接受的。



在整个查询的过程中,回表查询的 600 次随机访问成为了这个超级慢的查询的主要贡献,为了解决这个问题,我们只需要添加一个 (username, city, age) 索引或者在已有的 (username, city) 后添加新的 age 列就可以避免 600 次的随机访问:



(username, city, age) 索引对于该查询其实就是一个三星索引了,有关索引设计的内容可以阅读上一篇文章 MySQL 索引设计概要 如果读者有充足的时间依然强烈推荐 数据库索引设计与优化 这本书。

总结

这篇文章是这一年来写的最短的一篇文章了,本来想详细介绍一下 数据库索引设计与优化 书中对于索引性能分析的预估方法,仔细想了一下这部分的内容实在太多,例子也非常丰富,只通过一篇文章很难完整地介绍其中的全部内容,所以只选择了其中的一部分知识点简单介绍,这也是这篇文章叫概要的原因。


如果对文章的内容有疑问,可以在评论中留言。

Reference


本文转载自 Draveness 技术博客。


原文链接:https://draveness.me/sql-index-performance


2019-12-05 18:12992

评论

发布
暂无评论
发现更多内容

MindIE PD分离部署Q&A

AI布道Mr.Jin

“你好BOE”2025首站启幕 助力“横琴-澳门国际数字艺术博览会”打造沉浸式科技艺术新高地

爱极客侠

为什么选择React-native

溪抱鱼

面试 前端 React

Compressor for Mac(视频转码编辑工具)v4.10.1中文版

Rose

自定义linux脚本用于快速jar包启动、停止、重启

刘大猫

jar Linux 脚本 启动 停止

华为游戏中心深化全生命周期扶持,鸿蒙为独立游戏注入新活力

最新动态

运维想转SRE?先了解这7个原则

巴辉特

运维 SRE 转岗 转行

Final Cut Pro (fcpx专业视频后期制作)v11.1.1中文版

Rose

Fork for Mac(Git客户端)v2.53.1激活版

Rose

普通提示词和工程提示词的区别

阿星AI工作室

AI 工程 rag 提示词 rag实战

不会代码?1小时用AI克隆高颜值知识卡片网站!附保姆级部署教程

阿星AI工作室

AI 工具 AI教程 AI编程 AI工具推荐

vLLM 核心技术 PagedAttention 原理详解

Se7en

鸿蒙仓颉语言开发教程:页面和组件的生命周期

幽蓝计划

DaVinci Resolve Studio中文破解版 达芬奇影视后期调色剪辑

Rose

基于YOLOv8的7种交通场景识别项目|完整源码数据集+PyQt5界面+完整训练流程+开箱即用!

申公豹

yolov8

人工智能三人行-热评:如何看待MCP领域的竞争?您看好哪家?

雅菲奥朗

Premiere Pro 2025 (adobe pr 2025)中文破解安装教程

Rose

从零开始:使用 Amazon Q CLI 开发一款软件

亚马逊云科技 (Amazon Web Services)

Autodesk AutoCAD 2026 (CAD2026)中文激活版详细安装教程

Rose

人工智能三人行-热评:美国通过新法案,10年内禁止监管AI

雅菲奥朗

UML软件建模器 StarUML for Mac v6.3.2激活版

Rose

【拥抱鸿蒙】基于 Cocos Creator 的 HarmonyOS 自动构建

郑知鱼

华为 鸿蒙 自动化 CocosCreator HarmonyOS NEXT

Sketch for mac(专业矢量绘图设计软件)v101.9 中文激活版

Rose

鸿蒙运动开发实战:打造 Keep 式轨迹播放效果

王二蛋和他的张大花

鸿蒙

attention计算过程的一些细节

AI布道Mr.Jin

前端使用正则表达式检查是否为十六进制字符串

刘大猫

Java 正则表达式 人工智能 数据分析 十六进制字符串

Disruptor—核心源码实现分析(三)

不在线第一只蜗牛

Java

成就年薪百万程序员必修三门课:技术精进、架构修炼、管理探秘!

程序员高级码农

程序员 架构师

TechSmith Snagit mac中文版最强大的屏幕截图

Rose

菜单栏图标管理 Bartender 5 for Mac 隐藏、组织和自定义Mac菜单栏

Rose

HarmonyOS 应用中使用 AI 在医疗影像分析中的应用探索

网罗开发

鸿蒙 HarmonyOS HarmonyOS NEXT

MySQL 索引性能分析概要_文化 & 方法_Draveness_InfoQ精选文章