AI实践哪家强?来 AICon, 解锁技术前沿,探寻产业新机! 了解详情
写点什么

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:12996

评论

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

一次代码审计实战案例【思路流程】

H

网络安全

网络安全kali渗透学习 web渗透入门 Metasploit---基于SMB协议收集信息

学神来啦

网络安全 kali kali Linux

7招!实现安全高效的流水线管理

阿里云云效

云计算 阿里云 运维 云原生 持续交付

网络安全:小记一次代码审计

网络安全学海

网络安全 信息安全 渗透测试 安全漏洞 代码审计

2021年信创产业融资分析报告

统小信uos

飞步科技 x 焱融 YRCloudFile:大幅提升训练效率,开启智驾新纪元

焱融科技

自动驾驶 云计算 分布式 高性能 文件存储

通过蜜罐技术获取攻击者手机号、微信号【网络安全】

H

黑客 网络安全

未来的直播技术将会有哪些新的进化形式?

字节跳动视频云技术团队

互联网 科技革命 直播技术 低延时 音视频技术

构建测试的体系化思维(进阶篇)

BY林子

测试体系 质量内建

软件工程能力漫谈:比编码更重要的,是项目管理能力

百度开发者中心

面由心生,由脸观心:基于AI的面部微表情分析技术解读

百度大脑

浙江省人民医院:用宜搭助力党建改革工作,重构医院重大事项议事决策机制

一只大光圈

钉钉 低代码 钉钉宜搭 宜搭 宜搭数字化

投稿开奖丨云服务器ECS征文活动(1月)奖励公布

阿里云弹性计算

阿里云 云服务器 征文投稿开奖 玩转ECS

【重磅发布】百度参编信通院《联邦学习场景应用研究报告(2022年)》

百度开发者中心

2021 盘点 | 券商 TOP 5 出炉,谁才是最“卷”的券商王者?

博睿数据

技术创想 | Cypress UI 自动化测试框架

领创集团Advance Intelligence Group

开发提效小技巧分享(二)

编程三昧

工具 gitee GitHub、 3月月更

评测有礼 | 飞桨黑客松第二期热身活动上线啦!

百度大脑

一撕得:全员参与低代码开发,全面实现企业数字化管理

一只大光圈

钉钉 低代码 数字化 宜搭 一撕得

昇思MindSpore再突破:蛋白质结构预测训练推理全流程开源,助力生物医药发展

Geek_32c4d0

mindspore 昇思 生物医药

2023总结

明明

基础

关于 ZEGO 支撑 100 亿场高质量直播的秘笈

ZEGO即构

后台开发 CDN 音视频技术 音视频引擎

无需嵌码的主动式监测:一种预先感知用户体验的最佳实践

博睿数据

2022年软件开发趋势:远程工作已成主流

码语者

NFT音乐盲盒游戏系统开发方案

薇電13242772558

NFT

半导体材料的国产替代,机遇与挑战并存!

IC男奋斗史

芯片行业思考 芯片技术 芯片上游

技术实践 | 如何给NVMe做RAID

沃趣科技

raid 硬盘

阿里 BladeDISC 深度学习编译器正式开源

阿里云大数据AI技术

深度学习 开源 编译器

2022年炙手可热的边缘计算与端智能

战场小包

边缘计算 端智能 3月月更

自动化测试指南

FunTester

敏捷 性能测试 自动化测试 测试框架 FunTester

快速入门!全国大学生智能汽车竞赛百度创意组首期直播宣讲来啦

百度大脑

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