MySQL高级(二)--性能分析和优化
性能分析
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:
- SIMPLE -简单的select查询 查询中不包含子查询或者UNION
- PRIMARY -查询中若包含任何复杂的子部分,最外层查询则被标记为
- SUBQUERY -在select或where列表中包含子查询
- DERIVED -在FROM列表中包含的子查询被标记为 DERIVED(衍生) mysql会递归执行这些子查询把结果放在临时表
- UNION -若第二个select出现union之后,则被标记为union 若union包含在from子句的子查询中,外层select将被标记为;DERIVED
- UNION RESULT -从union表获取结果的select
table:
显示这一行是关于哪张表的
type:
type显示的是访问类型,是较为重要的指标 常用的结果值从好到坏如下:
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:
包含不适合在其它列中显示但是十分重要的额外信息
Using filesort: 说明msql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序读取.mysql中无法利用索引完成的排序操作称为”文件排序”
Using temporary: 使用了临时表保存中间结果 常见于排序
order by
和group by
Using index:
表示相应的select操作中使用了覆盖索引,避免了访问表的数据行 效率不错
如果同时出现using where,表明索引被用来执行索引键值的查找
如果没有同时出现using where 表明索引用来读取数据而非执行查找动作
覆盖索引:
覆盖索引(Covering Index) 一说为索引覆盖
就是select字段只用从索引中就能取得 不必读取数据行 MySQL可以利用索引返回select列表中的字段 而不必根据索引再次读取数据文件 换句话说 查询列表被所建的索引覆盖
如果要使用覆盖索引 一定要注意select列表中只取出需要的列 不可以使用**select * **
如果将所有字段一起做索引将导致索引文件过大查询性能下降
Using where: 使用了where
Using join buffer: 使用了连接缓存
impossible where: where子句的值总是false,不能用来获取任何元组
select tables optimized away :
在没有groupby子句的情况下 基于索引优化min/max操作或者对于MySIAM存储引擎优化COUNT(*)操作 不必等到执行阶段再进行计算 查询执行计划生成的阶段即完成优化
distinct: 优化distinct操作 在找到第一匹配的元组后即停止找同样值的动作
索引优化
索引分析
单表
范围 索引无法用到
解决办法 只建 categoryId和views 两个索引
两表
1 | EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card |
左连接特性:左边一定都有 所以建立索引的关键在右表
三表
建两个表的索引
结论
尽可能减少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
)
order by优化:
尽量使用index方式排序 避免使用filesort
order by满足两情况 使用index:
- order by 使用索引最左前列
- 使用where和order by组合满足索引最左前列
尽量在索引列上完成排序操作,遵照索引最佳左前列
如果不在索引列上 filesort有两种算法 mysql就要启动
双路排序
和单路排序
双路排序:mysql4.1 以前使用 双路排序 字面意思就是两次扫描磁盘 最终获取数据
读取行指针和orderby列 对他们进行排序 然后扫描已经排序好的列表 按照列表的值重新从列表中读取对应数据输出
从磁盘读取排序字段,在buffer进行排序,再从磁盘取其它字段
单路排序:从磁盘重新读取需要的列,按照order by列在buffer排序,然后扫描排序后的列表进行输出,它的效率会更快一点,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是他会使用更多的空间,因为把每一行都保存在内存中了
由于单路是后出的 总体而言好过双路 但是单路有存在问题…
优化策略:
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
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
3show 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
批量创建数据用于测试
1 | DELIMITER $$ |
show profile
是什么:
mysql提供的可以用来分析当前会话中语句执行的资源消耗情况,可以用于sql的调优测量
默认情况下 参数处于关闭情况 并保存最近15次的运行结果
分析步骤:
是否支持:
1
2
3show variables like 'profiling'
或者
show variables like 'profiling%'使用前先开启
运行 sql之后 查看结果
1
show profiles;
诊断 showprofile cpu,block io for query [id]
id是上一步问题的数字号码
日常主要关注四个
- converting HEAP to MySIAM查询结果太大 ,内存不够用了往磁盘上搬了
- creating tmp table 创建临时表:拷贝数据到临时表 用完删除
- copying to tmp table on disk 把内存中临时表复制到磁盘,危险!
- locked
全局查询日志
配置启用
在mysql的my.cnf中
1 | general_log=1; |
编码启用
1 | set global general_log=1; |
此后所编写的sql都会记录到mysql库里面的general_log表
1 | 查看 |
永远不要在生产环境开启
一般的的优化流程
- 分析
- 观察 至少跑一天 看看生产的慢SQL的情况
- 开启慢查询日志 设置阈值 抓取出sql
- explain+慢sql分析
- show profiles;
- 运维经理 DBA 进行数据库服务器的参数调优
资料参考: 尚硅谷