如何获取有性能问题的SQL
通过用户反馈获取存在性能问题的SQL
通过慢查询日志获取存在性能问题的SQL
实时获取存在性能问题的SQL
使用慢查询日志获取有性能问题的SQL
slow_query_log 启动停止记录慢查询日志
slow_query_log_file指定慢查询日志存储路径及文件
long_query_time指定记录慢查询日志SQL执行时间的伐值
- 默认10秒
记录所有符合条件的SQL
- 包括查询语句
- 数据修改语句
- 已经回滚的SQL
log_queries_not_using_indexes是否记录未使用索引的SQL
常用的慢查询日志分析工具
mysqldumpslow
pt-query-digest
实时获取性能问题SQL
information_schema -> PROCESSLIST表
SQL的解析预处理及生成执行计划
搞清楚这些查询为什么会慢
- 客户端发送sQL请求到服务器
- 检查是否可以在查询换存储命中
- 服务器端执行SQL解析，预处理，再由优化器声称对应的执行计划
- 根据执行计划，调用存储引擎API来查询数据
- 将结果返回给客户端
查询缓存对SQL性能的影响
query_cache_type
- 设置查询缓存是否可用
query_cache_size
- 设置查询缓存的内存大小
query_cache_limit
- 设置查询缓存可用存储的最大值
query_cache_wlock_invalidate
- 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit
- 设置查询缓存非配的内存块的最小单位
读写比较频繁的系统，建议关闭缓存
MySQL依照执行计划和存储引擎进行交互
解析SQL
语法解析阶段通过关键字对MySQL语句进行解析，并生成一颗对应的解析树
MySQL解析器将使用MySQL语法规则验证和解析查询
- 检查语法是否使用了正确的关键字
- 关键字的顺序是否正确
预处理
根据MySQL规则进一步检查解析树是否合法
- 查询中所涉及的表和数据列是否存在，名字或者别名是否存在歧义等
查询优化器生成查询计划
优化SQL执行计划
SQL的解析预处理以及生成执行计划
会造成MySQL生成错误的执行计划的原因
统计信息不准确
执行计划中的成本估算不等同于实际的执行计划的成本
- MySQL服务器层不知道哪些页面在内存中
- 哪些页面在磁盘上
- 哪些页面要顺序读取
- 哪些页面需要随机读取
MySQL优化器所认为的最优可能与你所认为的最优不一样
- 基于成本模型选择最优的执行计划
MySQL从不考虑其他并发的查询，这可能会影响当前查询的速度
MySQL有时候也会基于一些固定的规则来生成执行计划
MySQL不会考虑不收其控制的成本
- 存储过程
- 用户自定义的函数
MySQL优化器可优化的SQL类型
重新定义表的关联顺序
将外链接转化为内连接
使用等价变换规则
优化count，min，max
- select tables optimized away
建一个表达式转化为常数表达式
子查询优化
- 子查询->连接查询
提前终止查询
对in条件进行优化
如何确定查询处理各个阶段所消耗的时间
使用profile
set profiling=1
- session级别
执行查询
show profiles
show profile for query x
show profile cpu for query x
使用performance_schema
特定SQL的查询优化
大批量删除
- 分批次删除
修改大表的表结构
主从切换，先从后主
新老表同步，比较复杂
- pt-online-schema-change
优化not in 和<>查询
- 优化为LEFT JOIN方法
使用汇总表优化查询
- 截止到前一天的数据汇总count
- 今天的全部数据进行count
- 把两部分数据汇总
附Xmind
