性能分析

MySQL Query Optimizer

  • MySQL中有专门负责优化SELECT语句的优化器模块,主要功能: 通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式,但不见得是DBA认为是最优的,这部分最耗费时间)
  • 当客户端项MySQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给MySQL Query Optimizer时,MySQL Query Optimizer首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值.并对Query中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件,结构调整等,然后分析Query中的Hint信息(如果有),看显示Hint信息是否可以完全确定该Query的执行计划.如果没有Hint或Hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据Query进行相应的计算分析再得出最后的执行计划.

MySQL常见瓶颈

  • CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  • IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈: top,free,iostat和vmstat来查看系统的性能状态

Explain

是什么

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析你的查询语句或者是表结构的性能瓶颈

能干嘛

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

怎么玩

  • Explain+SQL语句
  • 执行计划包含的信息有
    • id
    • select_type
    • table
    • type
    • possible_keys
    • key
    • key_len
    • ref
    • rows
    • Extra

id:

select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

  • id 相同 执行顺序由上而下
  • id 不同 如果是子查询 id的序号会递增 id值越大优先级越高 越先被执行
  • id相同不同同时存在

select_type:

  1. SIMPLE -简单的select查询 查询中不包含子查询或者UNION
  2. PRIMARY -查询中若包含任何复杂的子部分,最外层查询则被标记为
  3. SUBQUERY -在select或where列表中包含子查询
  4. DERIVED -在FROM列表中包含的子查询被标记为 DERIVED(衍生) mysql会递归执行这些子查询把结果放在临时表
  5. UNION -若第二个select出现union之后,则被标记为union 若union包含在from子句的子查询中,外层select将被标记为;DERIVED
  6. UNION RESULT -从union表获取结果的select

table:

显示这一行是关于哪张表的

type:

type显示的是访问类型,是较为重要的指标 常用的结果值从好到坏如下:

image-20200909112331920

  • system

    表只有一行记录(等于系统表) 这是const的特例 平时不会出现

  • const

    表示通过索引一次就找到,const用于比较primary key或者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遍历全表

  • 一般来说要至少达到range级别,最好到ref

possible_keys:

显示可能应用在这张表中的索引,一个或者多个.查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被实际查询使用

key:

  • 实际使用的索引 如果为null则没有使用索引
  • 查询中如果使用了覆盖索引 则该索引和查询的select字段重叠

key_len:

  • 表示索引中使用的字节数 可通过计算查询使用索引的长度 在不损失精确性的情况下 长度越短越好
  • key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

ref:

显示索引的哪一列被使用了,如果可能的话 是一个常数,哪些列或常量被用于查找索引列上的值

rows:

根据表统计信息以及索引选用情况,大致估算出找到所需记录所需读取的行数

Extra:

包含不适合在其它列中显示但是十分重要的额外信息

  1. Using filesort: 说明msql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序读取.mysql中无法利用索引完成的排序操作称为”文件排序”

  2. Using temporary: 使用了临时表保存中间结果 常见于排序order by和group by

  3. Using index:

    表示相应的select操作中使用了覆盖索引,避免了访问表的数据行 效率不错

    如果同时出现using where,表明索引被用来执行索引键值的查找

    如果没有同时出现using where 表明索引用来读取数据而非执行查找动作

    覆盖索引:

    覆盖索引(Covering Index) 一说为索引覆盖

    就是select字段只用从索引中就能取得 不必读取数据行 MySQL可以利用索引返回select列表中的字段 而不必根据索引再次读取数据文件 换句话说 查询列表被所建的索引覆盖

    如果要使用覆盖索引 一定要注意select列表中只取出需要的列 不可以使用**select * **

    如果将所有字段一起做索引将导致索引文件过大查询性能下降

  4. Using where: 使用了where

  5. Using join buffer: 使用了连接缓存

  6. impossible where: where子句的值总是false,不能用来获取任何元组

  7. select tables optimized away :

    在没有groupby子句的情况下 基于索引优化min/max操作或者对于MySIAM存储引擎优化COUNT(*)操作 不必等到执行阶段再进行计算 查询执行计划生成的阶段即完成优化

  8. distinct: 优化distinct操作 在找到第一匹配的元组后即停止找同样值的动作

索引优化

索引分析

单表

image-20200717093752691

image-20200717094034739

范围 索引无法用到

解决办法 只建 categoryId和views 两个索引

两表

1
2
3
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card
# 结论 type 有ALL
# 建立右表索引 all-->ref

左连接特性:左边一定都有 所以建立索引的关键在右表

三表

建两个表的索引

结论

尽可能减少join语句中NestedLoop的循环总次数 永远用小结果集驱动大的结果集

优先优化NestedLoop的内层循环

保证join语句中被驱动表上的join条件字段已经被索引

当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下 不要太吝啬joinBuffer的设置

索引失效的情况(应该避免)

  • 全值匹配

  • 最佳左前缀法则：

    指的是查询从索引的最左前列开始并且不跳过索引中的列

  • 不在索引列上进行任何操作(计算 函数 自动手动类型转换),如果做了 会导致索引失效

  • 存储引擎不能使用索引中范围条件的右边的列

  • 尽量使用覆盖索引,减少 **select ***

  • mysql在使用 != 或 <>无法使用索引 会导致全表扫描

  • is null,is not null也无法使用索引

  • like以通配符开头(‘%abc...‘) 索引失效

    like 百分号最好加在右边 其余位置导致索引失效

  • 字符串（varchar类型 ）不加单引号 索引失效

  • 少用or 用or连接索引失效

一般性建议:

  • 对于单键索引,尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
  • 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

优化步骤总结

优化总结口诀.. (这完全不押韵………)

全值匹配我最爱,最左前缀要遵守

带头大哥不能死,中间兄弟不能断

索引列上少计算,范围之后全失效

like百分写最右,覆盖索引不写星

不等空值还有or,索引失效要少用

查询截取分析

查询优化

永远小表驱动大表(类似 nested loop)

image-20200720083359021

image-20200720083437424

image-20200720083540695

order by优化:

  • 尽量使用index方式排序 避免使用filesort

  • order by满足两情况 使用index:

    1. order by 使用索引最左前列
    2. 使用where和order by组合满足索引最左前列

  • 尽量在索引列上完成排序操作,遵照索引最佳左前列

  • 如果不在索引列上 filesort有两种算法 mysql就要启动 双路排序和单路排序

    • 双路排序:mysql4.1 以前使用 双路排序 字面意思就是两次扫描磁盘 最终获取数据

      读取行指针和orderby列 对他们进行排序 然后扫描已经排序好的列表 按照列表的值重新从列表中读取对应数据输出

      从磁盘读取排序字段,在buffer进行排序,再从磁盘取其它字段

    • 单路排序:从磁盘重新读取需要的列,按照order by列在buffer排序,然后扫描排序后的列表进行输出,它的效率会更快一点,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是他会使用更多的空间,因为把每一行都保存在内存中了

    • 由于单路是后出的 总体而言好过双路 但是单路有存在问题…

  • 优化策略:

    • 增大sort_buffer_size参数的设置
    • 增大max_length_for_sort_data参数的设置
    • image-20200720091217561
    • image-20200720091645124

Group by优化:

  • group by实质是先排序后进行分组 遵照索引建立的最佳左前缀
  • 当无法使用索引列时 ,增大sort_buffer_size参数的设置+增大max_length_for_sort_data参数的设置
  • where高于having 能写在where就不去having限定

慢查询日志

是什么

  • MySQL的慢查询日志 是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过long_query_time值的SQL,就会被记录到慢查询日志中
  • long_query_time的值默认是10秒

怎么玩

  • 默认情况下 MySQL数据库没有开启慢查询日志 我们需要手动来设置这个参数

    默认情况下 slow_query_log的值为OFF,表示禁用慢查询

    1
    2
    3
    show variables like '%slow_query_log%';
    set global slow_query_log=1;
    #只对当前数据库生效 MySQL重启失效

    如果要永久生效,必须修改my.cnf文件

    1
    2
    3
    4
    5
    在[mysqld]下增加或修改
    slow_query_log=1;
    #系统默认指定 一个 host_name=slow.log
    slow_query_log_file=/var/lib/mysql/XXX-slow.log
    #重启mysql服务

  • 当然 如果不是调优需要 一般不开启 会带来或多或少的性能影响

  • 慢查询日志支持将日志写入文件

  • 日志分析工具mysqldumpslow

    • 查看mysqldumpslow的帮助信息

      s:何种方式排序

      c:访问次数

      l:锁定时间

      r:返回记录

      t:查询时间

      al:平均锁定时间

      ar:平均返回记录数

      at:平均查询时间

      t:返回前面多少条数据

      g:后边搭配一个正则匹配模式 大小写不敏感

    • 常用命令

      得到返回记录集最多的10个sql

      1
      mysqldumpslow -s r -t 10 /var/lib/mysql/XXX-slow.log

      得到访问次数最多的10个sql

      1
      mysqldumpslow -s c -t 10 /var/lib/mysql/XXX-slow.log

      得到按照时间排序前十条里含有左连接的查询语句

      1
      mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/XXX-slow.log

      建议使用的时候结合|和more一起使用 否则可能出现爆屏情况

      1
      mysqldumpslow -s r -t 10 /var/lib/mysql/XXX-slow.log |more

批量创建数据用于测试

image-20200909143247391

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i+1;
END WHILE;
RETURN return_str;
END $$


DELIMITER $$
CREATE FUNCTION ranf_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$

批量传入
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i+1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000.400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i+1;
INSERT INTO dept(deptno,dname,loc)VALUES((START+i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

show profile

是什么:

mysql提供的可以用来分析当前会话中语句执行的资源消耗情况,可以用于sql的调优测量

默认情况下 参数处于关闭情况 并保存最近15次的运行结果

分析步骤：

  1. 是否支持:

    1
    2
    3
    show variables like 'profiling'
    #或者
    show variables like 'profiling%'

  2. 使用前先开启

  3. 运行 sql之后 查看结果

    1
    show profiles;

  4. 诊断 showprofile cpu,block io for query [id]

    id是上一步问题的数字号码

    image-20200720122505144

  5. 日常主要关注四个

    1. converting HEAP to MySIAM查询结果太大 ,内存不够用了往磁盘上搬了
    2. creating tmp table 创建临时表:拷贝数据到临时表 用完删除
    3. copying to tmp table on disk 把内存中临时表复制到磁盘,危险!
    4. locked

全局查询日志

配置启用

在mysql的my.cnf中

1
2
3
general_log=1;
general_log_file=/path/logfile
log_output=file;

编码启用

1
2
set global general_log=1;
set global log_output='table';

此后所编写的sql都会记录到mysql库里面的general_log表

1
2
# 查看
select * from mysql.general_log;

永远不要在生产环境开启

一般的的优化流程

  • 分析
    1. 观察 至少跑一天 看看生产的慢SQL的情况
    2. 开启慢查询日志 设置阈值 抓取出sql
    3. explain+慢sql分析
    4. show profiles;
    5. 运维经理 DBA 进行数据库服务器的参数调优

资料参考: 尚硅谷