MySQL数据库的SQL调优,你会了吗?

作者阿里云代理 文章分类 分类:新闻快递 阅读次数 已被围观 88
  • 前言
  • 初步了解索引
  • 要调优 SQL,怎么能不认识 explain
  • 重点!SQL 优化

因为笔者现在工作中用的存储引擎大多是 InnoDB,所以本文基于 InnoDB,数据库版本MySQL 5.7为前提写的。我们平常说的 SQL 优化,基本上就是对索引的优化。这里既然重点是 SQL 优化,所以我们得先了解索引,然后了解下我们分析 SQL 的工具 explain,最后才能到优化。这也是本文的大纲顺序。

了解 SQL 优化之前,有几个概念需要先知道:

  • MySQL 索引的数据结构

B+Tree,是 M 阶搜索树。现在以主键索引为例,非叶子节点会冗余我们的主键排序并构成树结构(非叶子节点不会存储数据);叶子节点会存储数据,并且叶子节点会形成一个双向链表,值得注意的是首尾节点也有指针互相指向。(具体可以看:https://segmentfault.com/a/1190000008545713?utm_source=sf-related)

  • 聚簇索引

叶子节点存储索引对应的 record信息。

  • 非聚簇索引

叶子节点只存储主键数据,所以要查询索引以外的数据需要回表。

  • 回表

走非聚簇索引得到主键数据后,根据主键再走一次聚簇索引那里查询列需要的数据。

  • 优化器

优化器是MySQL 众多组件中的一个,它会对我们的 SQL 进行分析,看预计使用哪些索引,SQL 的执行顺序如何,实际会使用哪些索引(没有真的执行 SQL,执行 SQL 是存储引擎去进行读写的),使用索引的情况等等。

需要知道使用 InnoDB 的表肯定有一个聚簇索引(有且仅有一个),使用的数据结构是 B+Tree。

*.frm:数据表结构相关信息存储的文件

*.idb:索引和数据存储的文件

注意:*.idb 这个文件本身就是 B+Tree 的文件,叶子节点包含完整的数据记录。

下面以主键索引为例(我的user表就只有三个字段)

  • 主键(不会重复)

如果我们没有主键,MySQL会使用我们表从第一列开始选择一列所有元素都不相等的列构建B+Tree,假设我们不存在符合这个要求的列,MySQL会自己为我们创建一个符合这个条件的隐藏列构建索引。像这种开销没必要花费,我们自己建表时,直接处理可以。

  • 自增

维护B+Tree时,更容易,性能更好。

  • 整型

查询范围时,整型比较大小更简单;整型占用空间更小,节约空间,事实上公司一般都会要求明确字段大小,过大字段,DBA一般都会要求开发解释为什么要这么大,当然从存储数据量角度来看,索引也是越小越好。

二级索引是非聚集的,主要是为了节约空间。二级索引是先找到主键,通过主键回表找到真正的数据行。

假如现在我有个用户表有4个字段:username、telephone、age、sex。

我们可以建两种类型的联合索引:联合主键,普通的联合索引。

现在我用 username、sex 构建成联合主键,维护索引如下:

这个和上面的差不多,只是 data 存的是主键,需要回表查找。

以上图为例子,先根据名字转成的ascii码进行排序,如果 ascii 码一样,那么再根据性别的 ascii 码大小比较排序。只有 username 的索引生效了,sex 的索引才有可能生效。要证明也很容易:如果没有匹配 username,直接匹配 sex,单看 sex 的话,我们索引的排序是无序的,就没法使用二分法了,所以不走索引。

讲了索引的数据结构,以及生效的情况,那么接下来就要看看如何 SQL 优化了。但是在此之前,我们要先了解下 explain 和 trace。

使用 explain 可以模拟优化器执行 SQL,分析 SQL,看看能否优化。

explain 标识的 SQL 不会真的执行,只是返回执行计划。如果 from 中包含子查询,仍会执行该子查询,子查询的结果将会放在临时表中。

explain 分析的 SQL 中,每查询一个表就会有一行记录。

更多内容请参考官方文档:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

了解每一列的意义,掌握最常用那几列。

3.1.1 id

id 列的编号是 select 的序列号,查几个表就有几个 id,并且 id 值越大执行优先级越高。如果 id 值相同,就从上往下执行,最后执行 id 为 null 的。

3.1.2 select_type

查询类型。

  • primary

简单查询。查询不包含子查询和union。

  • subquery

复杂查询中最外层的 select。

  • derived

包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)。如下:

? union

其实就是使用了 union 关键字后面的查询,如下:

3.1.3 table

表示这一列使用的是哪一张表。

当 from 子句中有子查询时,table列是格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查询。如下图:

当有 union 时,UNION RESULT 的 table 列的值为,1和2表示参与 union 的 select 行id。

3.1.4 partitions

使用的哪个分区,需要结合表分区才可以看到。因为我的例子都是没有分区的,所以是 null。

3.1.5 type

关联类型或者访问类型。一般要保证查询达到 range 级别,最好达到 ref。

从最优到最差:system > const > eq_ref > ref > range > index > ALL。

  • system, const

const 是 MySQL 能对查询的某部分转成一个常量,如下:

而 system 是 conts 的一个特例,当表里只有一条记录时,匹配时为 system。

  • eq_ref

使用了主键字段或者唯一索引字段进行关联,最多只会返回一条符合条件的记录时,等级为 eq_ref。

  • ref

相较于 eq_ref,它使用的是普通索引或者唯一索引的部分前缀,可能会找到多条符合条件的记录。

  • range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

  • index

这种一般是通过扫描某个二级索引的所有叶子节点(其实就是应该做全表扫描,但是这里利用了B+Tree的叶子节点是链表的特性遍历)。这种方式,虽然比较慢,但是用覆盖索引优化,性能上还是要比全表扫描(ALL)要好的,因为它占用空间小,一次IO可以读更多数据。

  • ALL

这个级别没啥好说的,就是我们常说的全表扫描。

3.1.6 possible_keys

显示可能会使用的索引。

3.1.7 key

实际会使用的索引。

3.1.8 key_len

通过这个值,可以推算出使用到索引的哪些列(一般针对联合索引使用多些),举个例子:

film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成,并且每个 int 是4字节。通过结果中的 key_len=4 可推断出查询使用了第一个列:film_id列来执行索引查找。

key_len计算规则如下:

  • 字符串:char(n) 和 varchar(n),5.0.3以后版本中,n均代表字符数,而不是字节数,如果是utf-8,一个数字或字母占1个字节,一个汉字占3个字节

– char(n):如果存汉字长度就是 3n 字节

– varchar(n):如果存汉字则长度是 3n + 2 字节,加的2字节用来存储字符串长度,因为varchar是变长字符串

  • 数值类型

– tinyint:1字节

– smallint:2字节

– int:4字节

– bigint:8字节

  • 时间类型

– date:3字节

– timestamp:4字节

– datetime:8字节

  • 如果字段允许为 NULL,需要1字节记录是否为 NULL。索引最大长度是768字节,当字符串过长时,MySQL会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

3.1.9 ref

这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)。

3.1.10 rows

这一列是MySQL估计要读取并检测的行数,注意这个不是结果集里的行数。

3.1.11 filtered

通过过滤条件之后对比总数的百分比。

3.1.12 Extra

这一列展示的是额外信息。常见的重要值如下:

  • Using index

使用覆盖索引。覆盖索引其实就是查询列是索引字段,这样就能避免回表,提高性能。因此,我们覆盖索引针对的是辅助索引。

  • Using where

使用 where 语句处理结果,并且查询列未被索引覆盖。如下:

  • Using index condition

查询的列没被索引完全覆盖, where 条件中是一个前导列的范围。

  • Using temporary

创建临时表来处理查询

(1)actor.name没有索引,此时创建了张临时表来distinct。

(2)film.name建立了idx_name索引,此时查询时extra是using index,没有用临时表。

  • Using filesort

使用外部排序而不是索引排序,数据量较小时使用内存,否则会使用磁盘。

(1)actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录。

(2)film.name建立了idx_name索引,此时查询时extra是using index。

Using filesort 原理详解:

– 单路排序

一次性取出满足条件的所有字段,然后在 sort buffer 中排序。用 trace 工具可以看到 sort_mode 信息里显示 或者 < sort_key, packed_additional_fields>

– 双路排序(回表排序)

先根据条件获取相应的排序字段和可以直接定位行数据的行ID,然后在 sort buffer 中排序,最后回表获取完整记录。用 trace 工具可以看到 sort_mode 信息里显示 。

– MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

  1. 如果字段的总长度小于 max_length_for_sort_data,那么使用单路排序。
  2. 如果字段的总长度大于 max_length_for_sort_data,那么使用双路排序。
  • Select tables optimized away

使用某些聚合函数(比如 max、min)来访问存在索引的某个字段时为 Select tables optimized away。

极端点说,SQL 优化就是对索引的优化。因此,我们要看下各种情况下,如何优化索引。

在我看来,SQL优化分以下几种情况:

1.可以走索引

  • 应该走索引,但是没走
  • 走索引了,但是没到最优(explain 分析,type 一般我们要求至少到达 range 这个级别)
  • order by 和 group by 优化

2.没法走索引(客观现实上的)或者 type 是 index,而且数据量大

  • 了解适用索引的情况,请不要只有面试时会说,工作就不知道了(数据量不大,直接查没事;大的话,考虑引进其他技术解决,如 :Redis, MongoDB, elasticsearch等)

3.小表驱动大表

4.count 查询优化

5.如何建索引

  • 该在哪个字段建索引
  • 哪些字段要使用联合索引
  • 表字段的设计(数据类型,大小)

Note : 单个索引生不生效,怎么处理还是比较简单的,所以下面只针对联合索引做分析。

下面先建表和造数据:

其中员工表插入了10W+数据。

  • 联合索引第一个字段不能过滤大部分数据,导致回表效率低,走全表扫描的 cost 更小。

当然我们也可以选择强制走索引,如下:

不过,走索引一定性能就更好吗?我们试验下。

别看我这差距不大,我这只是表列不多,字段不大,数据量也不算太多,所以差距不大,如果表更大的话,差距就会比较明显了。实际工作中,我们很难确定走索引的 cost 就一定小于全表扫描的。因此,我们一般不强制走索引。

优化方案:

我想让 MySQL自己去走索引,而不是我强制走索引。怎么办呢?其实上面已经提到了,这里是因为第一个字段过滤不多,导致回表效率低。既然如此,我们让它不回表不就好了吗?使用覆盖索引优化,就是我们查询列的字段都是使用的这个索引树上建了索引的字段,这样就不需要回表了。如下:

扩展:

  1. 我们使用 in 和 or 时,有时走索引,有时不走,其实是因为 MySQL 判断走索引的cost不如全表扫描的。
  2. 我们这里用了 like 'keyword%',这里涉及到一个概念叫索引下推。其实就是,MySQL 5.6 之前,对于以下的SQL,如果是走索引的话,它会先根据 name 过滤得到主键,进行回表拿到数据后,再去对比 age 和 position。MySQL 5.6 对此进行了优化——索引下推,根据 name 过滤后,不先回表,而是直接去对比 age 和 position,最后得到的主键才回表查数据。注意:1、索引下推只用于二级索引;2、不是 like 'keyword%' 就一定使用索引下推。
  • 分页不走索引

分页查询,系统十分常见的查询,建议大家学习完后,赶紧看下自己负责的分页功能是否走索引了,或者是否走了索引但是还能优化。以下,看例子来说一些优化手段。

这 SQL 其实是去了10010条记录出来,然后再舍弃前面的一万条。因此数据量大的话,其实效率是十分低的。

一些优化方案:

1.和产品同事商量,给一些一定有的查询条件或者隐藏的查询条件,给这些条件使用上索引。

这个方案是最简单并且直接的。

2.像我这里记录的id是连续且自增的情况下:

属于取巧,通过主键索引使用 where 直接筛选掉前面10000条记录。

缺点:

(1) 如果 id 不是连续且自增,那么这种方式就不行。

(2)不是使用主键排序,这种情况也不行。

3.非主键排序,不用ID连续自增也能生效。

  • 首先想到覆盖索引优化,看看能否这样干

扩展:

  • 不能使用覆盖索引,用了非主键排序,全表扫描的原因:MySQL 5.6~5.7 版本的优化器认为走二级索引再回表的效率不如全表扫描,这时是不会走索引的(但是也有例外,select * from employees order by name desc limit 10 就会走索引,因为只需要拿10条记录,这数量足够小,具体可以看这个博客,写得很好:https://www.cnblogs.com/25lH/p/11010095.html)。

解决方案如下:

(1)

这里其实就是利用了二级索引,拿到了10010条数据,并且按照 name 排好序,由于这里的子查询只要 id,所以不需要回表,然后再通过 join 就能利用主键索引快速拿到记录。

(2)当然除了这种方式,我们也可以强制走索引,因为我们知道这里二级索引只有一个,并且 name 是前导列,所以我这个案例走索引性能肯定比全表扫描好。因此,我们也可以选择强制走索引。

  • 不符合最左原则

我们索引之所以可以帮我们快速找到目标数据,是因为它的数据结构的特点。其中有序这一特征十分重要,如果不满足,那么肯定是不会走索引的(具体原因要回到平衡二叉查找树,再到二分法。因为不是这里的重点,所以不展开讲)。

  • 在索引列上做了以下操作:

– 对索引列是用了函数

– 对索引列做了类型转换

  • 根据查询条件过滤的数据不多,导致优化器认为走索引不如全表扫描。

其实第一个案例已经涉及到了,但是这里针对的是不等于, not in, not exists, <, >, is null, is not null 等等,这些能匹配到多条记录的写法。

排序和分组的优化其实是十分像的,本质是先排序后分组,遵循索引创建顺序的最左匹配原则。因此,这里以排序为例。

??https://www.cnblogs.com/25-lH/p/11010095.html??这个博客有讲到无查询条件的排序的案例,我这里就直接上图了,如下:

接下来写的都是有查询条件的情况。

MySQL 支持两种排序方式 filesort 和 index, Using index 是扫描索引完成的排序,而 Using filesort 是利用内存甚至磁盘完成排序的。因此,index 效率高,filesort 效率低。

当我们做多表关联查询时,常常会听到小表驱动大表。这里要了解什么是小表,什么是大表,为什么是小表驱动大表,MySQL 用了什么算法。

下面以两张表关联为例,介绍概念

什么是小表,什么是大表?不是表数据量较多那张表就是大表!!!而是经过我们的条件筛选后,匹配数据相对较小的那张表就是小表,另外一张就是大表。

所谓的小表驱动大表就是:先查小表,然后通过关联字段去匹配大表数据。

MySQL 的表关联常见有两种算法:

  • Nested-Loop Join 算法(NLJ)
  • Block Nested-Loop Join 算法(BNL)

4.3.1 NLJ,嵌套循环连接算法

这个算法就是一次一行地从驱动表中读取,通过关联字段在被驱动表中取出满足条件的行,然后取出两张表的结果合集。

从执行计划可以看出:

  • uuc_user_role 是驱动表,并且扫描了9条记录(表里只有9条记录),然后通过 user_id 去关联了 uuc_user(被驱动表)。

注意:优化器一般会优先选择小表驱动大表,我们 SQL 写的表的先后顺序有可能会被优化。

上面 SQL 的大致流程如下:

  1. 先从 uuc_user_role 中读取一行记录(如果有查询条件,会根据查询条件过滤结果中取一条)
  2. 获取关联字段,通过关联字段到 uuc_user 找到匹配记录
  3. 对第二步得到的记录,根据查询条件得到的记录跟第一步得到的记录进行合并,返回客户端
  4. 重复上面三步

查询结果如下(由于数据太多,手工拼接图了)

NLJ这个过程会读取 ur 所有数据(9行记录),每次读一行并拿到 user_id 的值,然后得到对应的 uuc_user 里的记录(这就是又扫了一次索引得到一行数据)。也就是说,整个过程扫描了18行记录。注意:如果被驱动表的关联字段没有索引,使用NLJ算法性能较低,MySQL会选择使用 BNL 算法。

扩展:如果我这里使用的是 left join,这时,左边的是驱动表,右边的是被驱动表;right join 则刚好相反。

4.3.2 BNL,基于块的嵌套循环连接算法

把驱动表的数据读入 join_buffer 中,然后扫描被驱动表,把被驱动表每一行取出来和 join_buffer 中的数据做匹配。

上面扩展已经出现了 BNL 算法的例子了,我就直接使用了。

这条 SQL 的流程大致如下:

  1. 把 uuc_user 所有记录放入 join_buffer
  2. 查 uuc_user_role 的记录和 join_buffer 中的数据匹配
  3. 返回满足条件的数据

整个过程扫描了 uuc_user 表225条记录和 uuc_user_role 表9条记录,总扫描行数为234行。内存比较最大次数 = 225 * 9 = 2025(次),想想 for 循环的代码就知道了。

两个问题:

  • 如果内存不够大,即 join_buffer 放不下 uuc_user 的数据怎么办?
  • 为什么被驱动表的关联字段没有索引会选择 BNL 算法呢?

答案:

  • 内存不够,那就分段放。打个比方我内存只能放下200条记录,我这里225,那么我一次放200,分两次放完就好了。join_buffer 默认值是256k。
  • 如果关联字段没有索引,使用 NLJ 算法的话,那么我们的比较都需要走磁盘扫描(等于是查询没有用到索引)。这时,都没用到索引的话,我内存比较的性能要比磁盘的好。因此,使用 BNL。但是有索引的话,我们可以通过索引大大提升查询性能(其实就是减少IO),所以会使用 NLJ。

4.3.3 多表关联的优化

互联网公司其实一般不允许做多表关联,如果做了关联,最多不超过3张表。多表关联时,关联字段一定要有索引,并且数据类型保持一致。为什么这么要求?直接原因,阿里规范(老大都这样规范,小弟跟着做,没毛病)。根本原因?看《高性能MySQL》,这本书推荐阅读。

  • 关联字段加索引,让 MySQL 做 join 时尽量选择 NLJ 算法。
  • 小表驱动大表,如果自己能知道哪张表肯定是小表,我们可以使用 straight_join,省去优化器的判断时间。

4.3.4 in 和 exsits

原则还是小表驱动大表

假设 A 表是左表,B 表是子查询的表。当 A 表是大表, B 表是小表时,使用 in。

当 A 表是小表, B 表是大表时,使用 exsits。

网上挺多资料说,要count(id)或者count(1),不要count(*),到底是不是这样呢?我们今天就来实践一下。

具体耗时如下(其实,随着电脑的状态不同,会有出入,但是多次测试会发现,这截图的排序结果是多数)。

因此,我们可以看出 count(*) 少用,性能较差是谣言,可以放心使用。这是因为 MySQL 5.6+ 会对 count(*) 进行优化,所以执行效率还是很高的。

hire_time 慢的原因是因为没有索引。

老生常谈的东西了,面试也经常问,这里就做个总结。

对于如何建索引这个问题,我个人觉得应该从以下几个角度思考:

  • 什么场景要建索引
  • 应该挑选哪些字段建索引,字段的大小,字段的类型
  • 索引的数量

4.5.1 什么场景要建索引

  • 高频查询,且数据较多,能够通过索引筛选较多数据
  • 表关联
  • 统计,排序,分组聚合

4.5.2 应该挑选哪些字段建索引,字段的大小,字段的类型

  • 高频查询,更新低频,并且可以过滤较多数据的字段
  • 用于表关联的关联字段
  • 用于排序,分组,统计等等的字段
  • 作为建索引的字段尽量小,可以降低树的高度,具体规则看下面的阿里规范

4.5.3 索引的数量

索引的数量要尽量的少。

  1. 因为索引是会占空间的;
  2. 记录更新数据库记录时,是有维护索引的成本的,数量越多,维护成本越高;
  3. 一张表索引过多,当一个条件发现多个索引都生效时,优化器一般会挑选性能最好的那个索引来用,数量多,优化器的挑选的成本也会上升。

1.代码先行,索引后上

只有对系统有了一定全局观,才知道哪些地方需要用索引,大多 SQL 是怎样的,我应该如何建索引。这样,我们就能有效减少不必要的索引,做到联合索引尽量覆盖条件。

2.尽量不要在过滤数据不多的字段建立索引,如:性别。

3.where 与 order by 冲突时,优先处理 where。

蔡柱梁,51CTO社区编辑,从事Java后端开发8年,做过传统项目广电BOSS系统,后投身互联网电商,负责过订单,TMS,中间件等。

本公司销售:阿里云、腾讯云、百度云、天翼云、金山大米云、金山企业云盘!可签订合同,开具发票。