MySQL优化分析操作过程

发表于2019-03-28更新于2022-04-30阅读次数

使用explains（执行计划）优化慢查询

MySQL的Explain命令用于查看执行效果，显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。
explain的语法如下，在select语句前加上explain就可以：

1
EXPLAIN select id from USER_2 where  num is  null

字段含义

  • table：显示这一行的数据是关于哪张表的
  • type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_ref、ref、range、index和all
  • possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
  • key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
  • key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
  • rows：MYSQL认为必须检查的用来返回请求数据的行数
  • Extra：关于MYSQL如何解析查询的额外信息。

通过explain的参数介绍，我们可以得知：

  1. 表的读取顺序(id)
  2. 数据读取操作的操作类型(type)
  3. 哪些索引被实际使用(key)
  4. 表之间的引用(ref)
  5. 每张表有多少行被优化器查询(rows)

id字段

select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。一般有三种情况：
第一种：id全部相同，sql的执行顺序是由上至下；
第二种：id全部不同，sql的执行顺序是根据id大的优先执行；
第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。

select_type 字段

select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询

  • simple：简单的select 查询，查询中不包含子查询或者union
  • primary：查询中若包含任何复杂的子查询，最外层查询则被标记为primary
  • subquery：在select或where 列表中包含了子查询
  • derived：在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
  • union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
  • union result：从union表获取结果的select

Extra 字段

  • using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
  • using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
  • using index ：使用覆盖索引的时候就会出现
  • using where：在查找使用索引的情况下，需要回表去查询所需的数据
  • using index condition：查找使用了索引，但是需要回表查询数据
  • using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
  • distinct：优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的操作

using filesort、using temporary 这两项出现时需要注意下，这两项是十分耗费性能的。

在使用 group by 的时候，虽然没有使用 order by，如果没有索引，是可能同时出现 using filesort，using temporary 的。

因为 group by 就是先排序再分组，如果没有排序的需要，可以加上一个 order by NULL 来避免排序，这样 using filesort 就会去除，能提升一点性能。

type 字段

性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all，保证查询至少达到range级别或者最好能达到ref

  • system：表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现。

  • const：如果通过索引一次就找到了，const 用于主键索引或者 unique 索引。因为只能匹配一行数据，所以很快。如果将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。

  • eq_ref：主键或唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

  • range：索引范围查询，只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现between、<、>、in 等的查询。这种范围扫描索引比全表扫描要好，因为只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  • index：Full Index Scan ，index 与 all 的区别为 index 类型只遍历索引树，这通常比 all 快，因为索引文件通常比数据文件小。也就是说虽然 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘读取的。

  • all：Full Table Scan，遍历全表获得匹配的行。

唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。
普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描，直到将索引文件扫描完为止。

纠正

在 where 子句中对字段进行 null 值判断、in 和 not in、使用!=或<>操作符并不会出现索引失效情况(MySQL 5.6)。

如何查看执行SQL的耗时

使用show profiles分析sql性能。
profile默认是不打开的

1
show variables like "%pro%";

可以看到profiling 默认是OFF的。

开启profile，然后测试

1
set profiling=1;

先执行一遍SQL语句，然后执行show profiles就可以看到语句执行的耗时

duration：单位秒

接着执行SHOW profile FOR QUERY 3; 查看时间具体花在哪些地方，3为查询语句的ID。
PROFILE的结果我们主要关注两列：Status、Duration，前者表示的是PROFILE里的状态，它和PROCESSLIST的状态基本是一致的，后者是该状态的耗时。因此，我们最主要的是关注处于哪个状态耗时最久，这些状态中，哪些可以进一步优化。

慢查询日志

有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用 mysqldumpslow 去分析。

查询慢查询日志信息可以直接通过执行 SQL 命令查看相关变量，常用的 SQL 如下：

mysqldumpslow 的工具十分简单，我主要用到的参数如下：

-t：限制输出的行数，我一般取前十条就够了。

-s：根据什么来排序默认是平均查询时间 at，我还经常用到 c 查询次数，因为查询次数很频繁但是时间不高也是有必要优化的，还有 t 查询时间，查看那个语句特别卡。

-v：输出详细信息。

例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500。

查看 SQL 进程和杀死进程

如果你执行了一个 SQL 的操作，但是迟迟没有返回，你可以通过查询进程列表看看它的实际执行状况。

如果该 SQL 十分耗时，为了避免影响线上可以用 kill 命令杀死进程，通过查看进程列表也能直观的看下当前 SQL 的执行状态；如果当前数据库负载很高，在进程列表可能会出现，大量的进程夯住，执行时间很长。

命令如下：

1
2
3
4
--查看进程列表
SHOW PROCESSLIST;
--杀死某个进程
kill 183665

MySQL 排序规则

一般使用 _bin 和 _genera_ci：

  • utf8_genera_ci：不区分大小写，ci 为 case insensitive 的缩写，即大小写不敏感。

  • utf8_general_cs：区分大小写，cs 为 case sensitive 的缩写，即大小写敏感，但是目前 MySQL 版本中已经不支持类似于_genera_cs 的排序规则，直接使用 utf8_bin 替代。

  • utf8_bin：将字符串中的每一个字符用二进制数据存储，区分大小写。

那么，同样是区分大小写，utf8_general_cs 和 utf8_bin 有什么区别？

  • cs 为 case sensitive 的缩写，即大小写敏感；bin 的意思是二进制，也就是二进制编码比较。

utf8_general_cs 排序规则下，即便是区分了大小写，但是某些西欧的字符和拉丁字符是不区分的，比如 ä=a，但是有时并不需要 ä=a，所以才有 utf8_bin。

  • utf8_bin 的特点在于使用字符的二进制的编码进行运算，任何不同的二进制编码都是不同的，因此在 utf8_bin 排序规则下：ä<>a。

优化点

1、尽可能把所有列定义为NOT NULL

原因：

  • 索引NULL列需要额外的空间来保存，所以要占用更多的空间；

  • 进行比较和计算时要对NULL值做特别的处理

2、使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
TIMESTAMP 占用4字节和INT相同，但比INT可读性高，超出TIMESTAMP取值范围的使用DATETIME类型存储。
经常会有人用字符串存储日期型的数据（不正确的做法）：

  • 缺点1：无法用日期函数进行计算和比较

  • 缺点2：用字符串存储日期要占用更多的空间

3、同财务相关的金额类数据必须使用decimal类型

  • 非精准浮点：float,double

  • 精准浮点：decimal

Decimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

4、禁止使用SELECT * ，必须使用SELECT <字段列表> 查询

原因：

  • 消耗更多的CPU和IO网络带宽资源

  • 无法使用覆盖索引

  • 可减少表结构变更带来的影响

5、避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

  • 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响，特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
  • 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

参考资料