如何获取有性能问题的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)
慢查询使用日志中记录的信息
使用慢查询获取有性能问题的SQL
常使用的慢查询日志分析工具(mysqldumpslow)
介绍：汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出
慢查询日志实例
慢查询的相关配置设置
命令行执行参数查看分析的结果
常使用的慢查询日志分析工具(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/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm
3、安装工具包
]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm
执行命令分析慢查询日志
]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep
分析的结果如下
MySQL服务器处理查询请求的整个过程
查询缓存对SQL性能的影响
查询缓存的优化参数
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生成错误的执行计划的原因
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;
执行查询：
show profiles;
show profile for query N;
查询的每个阶段所消耗的时间
使用profile查看语句所消耗的时间
特定的SQL查询优化
1、利用主从切换的原理进行大表的表结构修改，例如，现在从服务器上修改，修改完毕以后，进行主从切换，再在原来老的主上进行大表的修改，存在一定的风险。
2、在主服务器上创建于一个新的表，表结构就是将要修改大表后表结构，再把老表的数据重新导入到新表中，并在老表中建立一系列的触发器，把老表的数据同步更新到新表中，当老表中的数据全部同步到新表以后，再对老表加排它锁，把新表改成老表的名称，删除重命名的老表，如下图所示
使用pt-online-schema-change命令来修改大表，具体操作如下图所示
上图的参数解释
原创作品，转载请注明出处