《打造扛得住的MySQL数据库架构》第7章 SQL查询优化

SQL查询优化

7-1 获取有性能问题SQL的三种方法

• 如何设计最优的数据库表结构
• 如何建立最好的索引
• 如何拓展数据库的查询

查询优化，索引优化，库表结构优化

如何获取有性能问题的SQL

1、通过测试反馈获取存在性能问题的SQL

2、通过慢查日志获取存在性能问题的SQL

3、实时获取存在性能问题的SQL

7-2 慢查询日志介绍 

存储日志所需要的大量的磁盘空间

• slow_query_log  启动停止记录慢查询日志，默认为关闭

如果希望在已经运行的MySQL上on此功能可通过set global启动功能。

通过脚本来定时的开关慢查日志。

• slow_query_log_file  指定慢查日志的存储路径及文件，默认保存在MySQL的数据目录中。

建议日志存储和数据存储分开存储

• long_query_time  指定记录慢查询日志SQL执行时间的阈值，参数单位是S

记录符合条件的查询语句和数据修改语句，包括已经回滚的SQL

通常改为0.001秒也就是1毫秒可能比较合适

• log_queries_not_using_indexes  是否记录未使用索引的SQL

即便在时间的阈值内没用索引的SQL依旧会被日志记录

慢查日志中记录的内容

使用慢查询日志分析工具获取有性能问题的SQL

mysqldumpslow 

汇总除查询条件外其它完全相同的SQL，

并将分析结果按照参数中所指定的顺序输出。

mysqldumpslow -s r -t 10 slow-mysql.log

7-3 慢查询日志实例

常用的慢查日志分析工具（pt-query-digest）

pt-query-digest

--explain h=127.0.0.1,u=root,p=p@ssW0rd

slow-mysql.log

7-4 实时获取性能问题SQL 

如何实时获取mysql有性能问题的SQL

写脚本周期性执行查询命令

7-5 SQL的解析预处理及生成执行计划 

mysql服务器处理查询请求的整个过程

1. 客户端发送SQL请求给服务器
2. 服务器检查是否可以在查询缓存中命中该SQL
3. 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
4. 根据执行计划，调用存储引擎API来查询数据
5. 将结果返回给客户端

查询缓存对SQL性能的影响

query_cache_type  设置查询缓存是否可用  off

DEMAND表示只有在查询语句中使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存

query_cache_size  设置查询缓存的内存大小  0

query_cache_limit  设置查询缓存可用存储的最大值

query_cache_wlock_invalidate  设置数据表被锁后是否返回缓存中的数据

query_cache_min_res_unit  设置查询缓存分配的内存块最小单位

Hash查找只能进行全值匹配，从查询缓存中直接返回结果，概率上并不容易。

对于一个读写频繁的系统使用查询缓存，可能会降低查询处理的效率。

MySQL依照这个执行计划和存储引擎进行交互，这个阶段包括了多个子过程：

语法解析阶段是通过关键字对MySQL语句进行解析，并生成一棵对应的“解析树”

　　包括检查语法是否使用了正确的关键字，关键字的顺序是否正确等。

预处理阶段检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等

语法检查全都通过了，查询优化器就可以生成查询计划了。

会造成MySQL生成错误的执行计划的原因

• 统计信息不准确
• 执行计划中的成本估算不等同于实际的执行计划的成本

• MySQL从不考虑其他并发的查询，这可能会影响当前查询的速度
• MySQL有时候也会基于一些固定的规则来生成执行计划
• MySQL不会考虑不受其控制的成本

MySQL优化器可优化的SQL类型

• 重新定义表的关联顺序
• 将外连接转化成内连接
• 使用等价变化规则

• 优化count()、min()和max()
• 将一个表达式转化为常数表达式
• 子查询转换为关联查询
• 提前终止查询 limit 10
•  对in()条件进行优化

7-6 如何确定查询处理各个阶段所消耗的时间 

如何确定查询处理各个阶段所消耗的时间

mysql5.7版本，使用performance_schema

use performance_schema;

update setup_instruments set enabled ='YES',timed='yes' where name like 'stage%';

update setup_consumers set enabled ='YES' where name like 'events%';

执行被全局监控的SQL查询语句；

7-7 特定SQL的查询优化

如何获取一个存在性能问题的SQL

如何度量一个SQL在执行的各个阶段所消耗的时间

大表的数据修改最好要分批处理

1000万行记录的表中删除/更新100万行记录，一次只删除/更新5000行记录

暂停几秒，缓解主从集群下复制同步的压力

如何修改大表的表结构

• 对表中的列的字段类型进行修改
• 改变字段的宽度时还是会锁表
• 无法解决主从数据库延迟的问题

pt-online-schema-change

--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT '' "

--user=root --password=PassWord D=imooc,t=sbtest4

--charset=utf8 --execute

如何优化not in 和<>查询

使用汇总表优化查询

汇总表就是提前把要统计的数据进行汇总并记录到表中，

以备后续的查询使用。