如何获取有性能问题的sql
- 通过用户反馈存在性能问题的sql //用户反馈，测试人员测试
- 通过慢查询日志获取存在性能问题的sql //主要手段
- 实时捕获存在性能问题的sql
慢查询日志
# 查看my.cnf参数配置
mysqld --help --verbose | more
主要的开销是磁盘io和磁盘空间
启动慢查询日志
slow_query_log 启动停止慢查询日志
slow_query_log_file 指定慢查询日志存储日志及文件
long_query_time 指定慢查询日志sql执行时间的阀值
log_queries_not_using_indexes 是否记录未使用索引的sql
long_query_time=1
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow-query.log
log_queries_not_using_indexes=1
慢查询分析工具
- mysqldumpslow mysql安装之后就自带的
查看详细命令 mysqldumpslow --help
实时发现有性能问题的sql
利用information_schema
//可以通过脚本去执行，实时去查找执行时间超过30秒的
select id,`user`,DB,`host`,command,`time`,state,info from information_schema.processlist where time >30
查询为什么会慢
mysql查询执行的过程
- 客户端发送sql请求给服务器
- 服务器检查是否可以在查询缓存中命中该sql
- 服务器进行sql解析，预处理，再由优化器生成对应的执行计划
- 根据执行计划，调用引擎API来查询数据
- 将结果返回给客户端
影响的因素
查询缓存
如果查询缓存是打开的，优先检查查询缓存是否命中，使用hash查找来匹配缓存结果。如果命中查询缓存，在返回之前，会检查用户权限，如果权限符合，则返回结果。
检查缓存是否命中时，需要对缓存进行加锁，并且在数据被更新之后，缓存也就失效了。如果系统比较繁忙，则不建议开启缓存。
查询缓存的影响参数
query_cache_type 设置查询缓存是否可用 off/on
query_cache_size 设置查询缓存的内存大小 0
query_cache_limit 设置查询缓存可用存储的最大值
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit 设置查询缓存分配的内存块最小单位
依照执行计划对存储引擎进行交互
在过程中出错则返回
解析sql，预处理，优化sql执行计划
确定查询处理各个阶段所消耗的时间
使用profile
开启 set profiling = 1 这是一个session级的配置
执行查询
show profiles 查看每一个查询所消耗的总时间信息
查看 show profile for query n;
+----------+------------+-------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------+
| 1 | 0.01540625 | show databases |
| 2 | 0.00012825 | SELECT DATABASE() |
| 3 | 0.00027775 | show databases |
| 4 | 0.00042550 | show tables |
| 5 | 0.00030150 | show tables |
+----------+------------+-------------------+
//查看
show profile for query 1;
//结果
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.001794 |
| checking permissions | 0.000015 |
| Opening tables | 0.000693 |
| init | 0.000022 |
| System lock | 0.000011 |
| optimizing | 0.000005 |
| statistics | 0.000027 |
| preparing | 0.000017 |
| executing | 0.012708 |
| Sending data | 0.000046 |
| end | 0.000007 |
| query end | 0.000005 |
| closing tables | 0.000004 |
| removing tmp table | 0.000010 |
| closing tables | 0.000007 |
| freeing items | 0.000019 |
| cleaning up | 0.000018 |
+----------------------+----------+
Performance Schema
从mysql5.5 引入的
传送门
慢查询基础
简单的查询衡量指标：1响应时间，2扫描行数，3返回的行数
- 响应时间主要是等待时间和服务时间，服务时间是服务器处理这个查询所消耗的时间。等待时间是等待资源的时间，如io、锁等待
- 扫描行数在一定程度上能说明该查询的效率， 较短的行访问较快，内存中的行比磁盘上快。
优化数据访问
- 确认是否检索了大量超过需要的数据。通常是访问了太多行，有时候也可能是太多列
- 确认服务层是否分析了大量超过需要的数据行
- 是否向数据库请求了不需要的数据
- 是否扫描了额外的记录
扫描的行数和返回的行数
扫描的行数和访问类型
重构查询方式
- 将复杂查询分解成多个简单查询 //少连表 或单表查询
- 切分查询 //如删除多行数据，该为多次删除
- 分解关联查询
子查询优化为join查询，需注意一对多情况时，是否有数据重复
mysql 查询执行路径
- 客户端发送一条查询给服务器
- 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
- 服务端进行SQL解析，预处理，再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
- 将结果返回给客户端。
查询优化
count
count是一种特殊函数，可以统计某个列值的数量，也可以统计行数。在统计列值时，要求列值是非空的。
count可以通过索引覆盖来实现优化，或者使用汇总表
select count(id2),count(id) from t
//结果 id2:2 id:3 count的列为null时，count并不会将其统计
利用null 优化count
select count(release_year='2008' or null) from film
优化关联查询
- 确保on或者using子句中的列上有索引。一般来说只需要在关联顺序中的第二个表的相应列上建立索引。 如：表A、B用c列进行关联，关联顺序为B、A，则只需要在A表上建立c列的索引。
- 确保任何的group by 和order by 中的表达式只涉及表中的一个列，这样mysql才有可能使用索引来优化这个过程
优化子查询
子查询尽量换成关联查询。若使用5.6以上版本，则不需要进行替换。
优化group by 和distinct
mysql优化器会在内部处理时的时候相互转化这两类查询，都可以使用索引来优化，也是最有效的方法。
当无法使用索引优化时，group by 由临时表或文件排序来做分组
limit优化
可以参考mysql翻页优化
通常在where条件上加索引会由不错的性能，但是当数据量大，且翻页多时，如，limit 10010，10。此时mysql需要查询10010条数据，并且10000都被抛弃，只取最后10条。要对此类语句优化，要么限制页面中分页的数量，或者优化最大偏移量的性能。
优化最大偏移量，利用索引覆盖来加快查询。利用的是主键
limit和offset的问题，都是offset的问题。它会导致mysql扫描大量不需要的行然后在抛弃。
//第一种写法
select * from actor where actor_id >=(select actor_id from actor order by actor_id limit 100,1) limit 10;
//第二种写法
select * from actor a join (select actor_id from actor order by actor_id limit 100,10) b on a.actor_id=b.actor_id
