从原理上理解MySQL的优化建议
概述
自从学习 MySQL 以来,我们一直听到或者看到很多优化建议,比如说不要用 select * 查询,用什么字段就查什么字段;建议用自增主键来作为表的主键,等等。这些建议听得很多感觉都成了 MySQL 开发的常识了,但是对于这些优化建议,我们有没有想过为什么要这么做呢?这篇博文我们从 MySQL 的原理出发,来解释下为什么有这些优化建议?
本文实验环境 MySQL 5.7.25
预备知识
B+ 树索引
MySQL 的默认存储引擎 InnoDB 使用 B+ 树来存储数据的,所以在分析优化建议之前,我们有必要了解 B+ 树索引的基本原理。
上图是一个 B + 树索引示意图(B+ 树的定义可以看这里),每个节点表示一个磁盘块,也可以理解为数据库中的页。我们来分析下 B+ 树索引的查找过程,如果我要查询主键为 35 的数据,索引会怎么走呢?首先会判断 35 小于根节点 37 ,继续查询左子树,判断 35 大于 22 和 33 那么进入其右子树,找到了叶子节点 33 ,继续遍历找到了 35 ,最后取出其 data 即可。在索引的情况下,查询 35 只用了3次 IO 操作,这是非常高效的。在真实的场景下,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。上图中也是体现了只要维持树的高度足够低,IO 操作就会足够少,IO次数少,查询性能就会高。
Explain 执行计划
上图就是一个 explian 执行计划,先看看上面各个字段的含义是什么?
-
id: Query Optimizer 所选定的执行计划中的查询编号。
-
select_type: 所使用的查询类型,主要有几种查询类型:
类型名称 说明 SIMPLE 除子查询或者UNION查询之外的其他查询。 PRIMARY 子查询中的最外层查询,注意并不是主键查询。 UNION UNION语句中第二个 SELECT 开始的后面所有 SELECT,第一个 SELECT 为 PRIMARY。 DEPENDENT UNION 子查询中的 UNION,且为 UNION 中从第二个 SELECT 开始的后面所有 SELECT ,同样依赖于外部查询的结果集。 UNION RESULT UNION 中的合并结果。 SUBQUERY 子查询内层查询的第一个 SELECT,结果不依赖于外部查询结果集 。 DEPENDENT SUBQUERY 子查询内层查询的第一个 SELECT,结果依赖于外部查询结果集。 DERIVED 驱动表,就是主表 MATERIALIZED 子查询的结果被保存为虚拟临时表 UNCACHEABLE SUBQUERY 结果集无法缓存的子查询 UNCACHEABLE UNION 结果集无法缓存的 UNION 查询 -
table: 显示执行这一步所访问的数据库中的表的名称。
-
partitions: 查询分区表匹配的分区,非分区表显示 NULL 。
-
type: 查询表所使用的方式,类型如下:
类型名称 说明 all 全表扫描。 const 读常量,最多只有一条记录匹配,由于是常量,所以实际上只要读一次。 system 系统表,表中只有一条数据,它是特殊的 const 类型。 eq_ref 最多只会匹配一条结果,一般是通过主键或者唯一索引来访问。 ref Join 语句中被驱动表的索引查询 full_text 使用 full_text 索引 ref_or_null 与ref唯一的区别就是在使用索引查询之外再增加一个空值查询。 index_merge 查询中同时使用两个(或者多个)索引,然后对索引结果进行merge之后再读表数据。 unique_subquery 子查询中的返回结果字段组合是主键或者唯一索引 index_subquery 子查询中的返回结果字段组合是索引,但是不是主键或者唯一索引 range 索引范围扫描,经常出现在比较条件中 ,如:<, > ,BETWEEN 等 Index 全索引扫描 他们的性能由好到差依次是:system > const > eq_ref > ref > full_text > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > all 。
-
possible_keys: 查询可能用到的索引。
-
key_len: 用到的索引长度。
-
ref: 展示将那些列或者常量与命中的索引比较。
-
rows: 执行这次查询扫描的行数。
-
filtered: 过滤行数百分比,最大值是100,当显示100时候,表示没有过滤行, rows显示了检查的估计行数,乘以过滤百分比将显示与下表连接的行数。例如,如果行数为1000,过滤条件为50.00(50%),则与下表联接的行数为1000×50%= 500。
-
extra: 执行查询额外的条件,详细的条件可以查看这里。
掌握了前面这些前置知识,我们来用这些知识分析下经常建议我们的那些MySQL优化建议。
为什么建议使用自增主键
当我们每次建立表的时候都在考虑是用自增主键呢?还是用 UUID 呢?但是从性能考虑我们还是建议使用自增 Id,为什么呢?主要是由于 MySQL B+ 树索引性质决定的,数据的新增是要更新索引的,也就是要更新 B+ 树。换句话说,使用自增Id 和 非自增 Id 哪种更新 B+ 树更快,成本更低,谁就是更优的选择。我们来模拟下自增 Id 插入和非自增 Id 插入情况。
自增Id 插入情况: 我们在一个已经有10条数据的 B + 数上插入2条数据,分别是10和11,我们看看树是如何变化的。
我们这里可以发现两个特点:
1、自增的数据插入影响的范围永远只有最右的子树,要么直接在子树插入节点,要么就是子树分裂,影响其父节点。
2、除了最右子树,其他子树的节点都是满的。
上面两个特点有什么影响呢?我们根据前面 B+ 树索引示意图可以知道,每个点都是一个磁盘块,操作每个节点相当于进行一次 IO,由于每次插入影响的节点只有最右子树,那么磁盘 IO 的范围就可控;最重要一点是除最右子树,其他子树的节点都是满的,这种情况,叶子节点数据的物理连续性会更好, 根据局部性原理,查询性能也会更高。
非自增 Id 插入情况:
非自增 Id 插入特点对比自增 Id 插入我们很容易就能知道:
1、插入影响节点不可控,无法预知。
2、每个子树都存在叶子节点不满的情况。
按照之前的分析思路,我们也就知道了非自增 Id 插入有什么性能劣势了。由于插入数据影响节点不可控,导致节点分裂的情况就会更频繁,节点分裂也是 IO 操作,性能自然受到影响。子树的叶子节点不满,会导致叶子节点物理连续性不好。最后如果我们是UUID的话,Id 过长,会占用节点空间,每个页能存储的节点变少,页分裂变多,性能也会受到影响。这也是为什么建议使用自增主键的原因。
为什么不要使用 select * 查询
我们经常听到查询表,只要查询自己想要的字段,不需要的字段就不要查询,严禁使用 select *
,我们能想到很直观的理由就是,数据库要帮你翻译成每个字段名去查询,接着查询多余的字段会占用内存,带宽等资源。这确实是一个理由,而且这个理由很重要,但是我这里想说的是另外一个原因,覆盖索引。我之前的一篇索引文章也介绍了覆盖索引,感兴趣的同学可以点击这里。覆盖索引的意思是指查询使用联合索引覆盖了要查询的字段,这样数据库不用去进行回表,从而减少IO,提高性能。
这里我用MySQL官方给的示列数据进行一个实验,数据地址下载可以点击这里。
我选择 employees
表数据进行演示,默认数据是没有联合索引的,我们加上一个联合索引:
---employee表结构----------- +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | emp_no | int(11) | NO | PRI | NULL | | | birth_date | date | NO | | NULL | | | first_name | varchar(14) | NO | MUL | NULL | | | last_name | varchar(16) | NO | | NULL | | | gender | enum('M','F') | NO | | NULL | | | hire_date | date | NO | | NULL | | +------------+---------------+------+-----+---------+-------+
ALTER TABLE employees.employees add Index `idx_first_name_last_name` (first_name,last_name);
查看该表索引情况:show index from employees.employees;
表已经成功创建了first_name
和 last_name
的符合索引,我们开启 profiles 监控 SQL 执行的情况。
SET SESSION profiling = 1;
然后分别执行以下SQL
SELECT first_name,last_name FROM employees.employees WHERE first_name='Eric'; SELECT * FROM employees.employees WHERE first_name='Eric';
查看Profiles;
这里我们看到使用 select * 比 select 字段慢了4倍左右,为什么会这样呢?我们看看执行计划。
我们看到两者的执行计划几乎一样,只有 Extra 有区别,使用字段的 Extra 显示 using index,这就告诉你只使用索引就找到了想要的数据,因为你的索引就是用 first_name
和 last_name
建立的, 而 select * 它还需要查询 gender
和hire_date
字段(主键字段不用额外查,辅助索引指向的就是主键),所以它不能不进行回表查询其他字段,性能差异也是这里。
总结
本文从原理上分析了我们日常的两点建议,为什么建议使用自增主键?为什么不建议使用 select * 查询?其实主要最终的原因还是和索引相关,既然我们用索引来提高我们的效率就要充分利用它,下面是知识点总结:
1、B+ 树查询的效率高低是受其树高影响,树的高度越低,查询IO次数越少,性能相对也就越高。
2、执行计划的类型由好到差依次是:system > const > eq_ref > ref > full_text > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > all 。
3、自增主键的好处就是连续,插入维护的成本相对较低,同时子树的叶子节点大部分是满节点,物理连续性好,查询性能更优。
4、UUID 主键长度过长,导致单个子节点存储的主键变少,更平凡的出发页分裂,影响性能,这也是为什么建议索引不要太长的原因。
5、覆盖索引是很好的优化技巧,可以让查询直接通过索引返回数据,而不用回表,减少IO,提升性能。
参考
1、https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
2、https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
3、http://blog.codinglabs.org/articles/theory-of-mysql-index.html
关注公众号,享受第一时间更新。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
云是否被“边缘”化了?剖析边缘云计算对云战略的长期影响
云栖号资讯:【点击查看更多行业资讯】在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来! 新技术名词的不断出现是不可避免的,时下比较流行的名词当属边缘计算。专家们用了不同的方式描述边缘计算,还用了诸多用例佐证边缘计算是如何如何地厉害,边缘计算听起来是很棒的东西。甚至还有一种观念认为边缘计算可以替代所有传统的、更中心化的云计算模型。笔者在这赶紧插一句:这是不会发生的。 未来科技环境大有可能是一种同时具传统云计算和边缘计算功能的混合结构。 边缘计算其实是对云计算很自然的互补,不是替代,反之亦然。边缘计算解决了网络延迟限制的问题,而云计算则可以为大量工作负载提供了更快、更便宜的中心式计算和存储。这些工作负载包括人工智能、数据分析和机器学习等等,在当前的COVID-19环境里这些工作负载更是显得至关重要,笔者曾在另一篇文章里“在大流行期间如何利用人工智能和机器学习探讨过这个话题。 未来科技环境大有可能是一种同时具传统云计算和边缘计算功能的混合结构。可以说是真正的阴与阳的关系,两个方面互补调和。 每个人都想争先 纵观业界,各种科技正在逐渐融合边缘云的强大威力,云计算本身也在朝多云混合支...
- 下一篇
防微杜渐的稳定性法宝:播控系统面向失败的设计
作者| 阿里文娱高级开发工程师 云琰 一、浅谈面向失败的设计 1. 什么是面向失败的设计? 面向失败的设计,就是以“失败”为对象,天然为了失败而存在的设计思想,在一开始的系 统设计阶段就考虑到各种失败场景,把面向失败当成是系统设计的一部分,准备好从失败中恢 复的策略。 2. 为什么要面向失败设计? 因为无所不在的失败场景,比如硬件问题,软件 bug,配置变更错误,系统恶化,超预期流量,外部攻击,依赖库问题,依赖服务问题。并且,这些一旦失败,轻则出现业务不可用,影响用户体验和企业声誉;重则导致数据永 久丢失、业务再无恢复可能。例如,911 事故发生前,约有 350 家企业在世贸大厦工作。事故 后,有 200 家企业由于重要系统被破坏,关键数据丢失而倒闭。 3. 怎么面向失败设计? 在软件的整个生命周期中,不同的阶段面对失败场景有不同的应对规则。
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- SpringBoot2整合Redis,开启缓存,提高访问速度
- CentOS6,CentOS7官方镜像安装Oracle11G
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- Red5直播服务器,属于Java语言的直播服务器
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- 2048小游戏-低调大师作品