如何获取存在性能问题的SQL
1、通过用户反馈获取有性能问题的SQL
2、从低速查询日志中获取性能问题的SQL
3、实时获取存在性能问题的SQL
使用低速查询日志获取有性能问题的SQL
首先，介绍与滚动查询相关的参数
1、slow_query_log启动自定义日志滚动查询日志
中选择所需的墙类型。 可以从MySQL命令行设置set global slow_query_log=on
修改或/etc/my.cnf文件，然后添加slow_query_log=on
2、指定slow_query_log_file滚动查询日志的存储路径和文件
建议分别存储日志存储和数据存储
3、long_query_time指定记录滚动查询日志SQL执行时间的阈值
记录所有符合条件的SQL
数据修改语句
包含查询语句
回滚的SQL
注意：
时间精确到微秒，保存单位为秒，默认值为10秒。 例如，如果要查询1微秒的值，请在此设定为0.001秒
4、log_queries_not_using_indexes是否记录未使用索引的SQL
5、设置log_output抛出日志查询的保存格式(如果需要保存为文件，请更改为FILE ) )。
低速查询使用日志中记录的信息
第1、1行记录的信息是使用sbtest的测试
第2、2行记录的信息是滚动查询日志的时间
第3、3行记录的信息是使用的锁定时间
第4、4行中记录的信息是返回的数据行数
第5、5行记录的信息是扫描数据的行数
第6、6行记录的信息是时间戳
第7、7行记录的信息是查询的SQL语句
使用低速查询获取有性能问题的SQL
常用的滚动查询日志分析工具(mysqldumpslow ) ) )。
摘要：汇总查询条件以外的完全相同的SQL，并按参数指定的顺序输出分析结果
低速查询登录实例
低速查询的配置设置
运行命令行参数以显示分析结果
] # CD/var/lib/MySQL/log ] # mysqldumpslow-Sr-t 10s low-MySQL
常用的笔划查询日志分析工具(pt-query-digest ) )。
在使用该工具之前，必须安装该工具。 如果已经有，请省略以下安装步骤
1、perl模块
] # yum install-y perl-CPAN perl-time-hires perl-io-socket-SSL perl-DBD-MySQL perl-digest-MD5
2、切换到src目录下载rpm软件包
]# cd /usr/local/src
] # wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/red hat/7/x86 _ 65374; 65374; 65374; percona/percona-tooool
3、安装工具包
] # rpm-ivhpercona-toolkit-3.0.7-1.el7.x86 _ 64.rpm
运行命令分析低速查询日志
] # pt -查询- digest-- user=root-- password=red hat-- host=127.0.0.1 slow-MySQL slow.rep
分析结果如下
MySQL服务器处理查询请求的整个过程
1、客户端向服务器发送SQL请求
2、服务器检查缓存服务器中是否存在命中的SQL
3、服务器端进行SQL分析，进行预处理，优化程序对应执行计划
4、根据执行计划，调用存储引擎API查询数据
5、将结果返回客户端
查询缓存对SQL性能的影响
1、优先检查整个查询是否命中查询缓存中的数据
2 .通过对大小写敏感的哈希搜索实现
查询缓存优化参数
query_cache_type设置查询缓存是否可用
ON，OFF，DEMAND
注： DEMAND表示仅在查询语句中使用SQL——CACHE和SQL_NO_CACHE来控制是否需要缓存
query_cache_size设置查询缓存的内存大小
query_cache_limit设置查询缓存中可用的最大存储
query_cache_wlock_invalidate设置数
据表被锁后是否返回缓存中的数据(默认是关闭的，建议也是关闭的此选项)query_cache_min_res_unit 设置查询缓存分配的内存块最小的值
会造成MySQL生成错误的执行计划的原因
1、统计信息不准确
2、执行计划中的成本估算不等同于实际的执行计划的成本
3、MySQL优化器所认为的最优可能与你所认为的最优不一样
4、MySQL从不考虑其他并发的查询，这可能会影响当前查询数据
5、MySQL有时候也会基于一些固定的规则来生成执行计划
6、MySQL不会考虑不受其控制的成本
MySQL优化器可优化的SQL类型
1、重新定义表的关联顺序
优化器会根据统计信息来决定表的关联顺序
2、将外链接转换成内连接
where条件和库表结构等
3、使用等价变换规则
(5=5 and a > 5)将会被改写成 a > 5
4、优化count(), min()和max()
select tables optimized away
优化器已经从执行计划中移除了该表，并以一个常数取而代之
5、将一个表达式转换为常数表达式
6、使用等价变换规则
7、子查询优化
8、对in()条件进行优化
如何确定查询处理各个阶段所消耗的时间
使用profile set profiling = 1;
执行查询： showprofiles; show profile forqueryN; 查询的每个阶段所消耗的时间
使用profile查看语句所消耗的时间
特定的SQL查询优化
1、利用主从切换的原理进行知性的帽子的表结构修改，例如，现在从服务器上修改，修改完毕以后，进行主从切换，再在原来老的主上进行知性的帽子的修改，存在一定的风险。2、在主服务器上创建于一个新的表，表结构就是将要修改知性的帽子后表结构，再把wsdg的数据重新导入到新表中，并在wsdg中建立一系列的触发器，把wsdg的数据同步更新到新表中，当wsdg中的数据全部同步到新表以后，再对wsdg加排它锁，把新表改成wsdg的名称，删除重命名的wsdg，如下图所示
使用pt-online-schema-change命令来修改知性的帽子，具体操作如下图所示
上图的参数解释
--alter 所使用的sql语句
--user 数据库的登录用户
--password 登录用户的密码
D 指定所有修改表的数据库名称
t 表的名称
--charset 指定数据库的字符串
--excute 执行
作者：demon_gdy