MySQLSQL语句分析查询优化如何获取有性能问题的SQL
1、通过⽤户反馈获取存在性能问题的SQL
2、通过慢查询⽇志获取性能问题的SQL
3、实时获取存在性能问题的SQL
使⽤慢查询⽇志获取有性能问题的SQL
⾸先介绍下慢查询相关的参数
1、slow_query_log      启动定制记录慢查询⽇志
设置的⽅法，可以通过MySQL命令⾏设置set global slow_query_log=on
或者修改/etc/myf⽂件，添加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、第⼀⾏记录的信息为使⽤sbtest做的测试
2、第⼆⾏记录的信息为慢查询⽇志的时间
3、第三⾏记录的信息为所使⽤锁的时间
4、第四⾏记录的信息为返回的数据⾏数
5、第五⾏记录的信息为扫描数据的⾏数
6、第六⾏记录的信息为时间戳
7、第七⾏记录的信息为查询的SQL语句
使⽤慢查询获取有性能问题的SQL
常使⽤的慢查询⽇志分析⼯具(mysqldumpslow)
介绍：汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出
慢查询⽇志实例
慢查询的相关配置设置
viewership是什么意思中文
命令⾏执⾏参数查看分析的结果
]# cd /var/lib/mysql/log
]# mysqldumpslow -s r -t 10 slow-mysql
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 www.percona/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 > p
分析的结果如下
MySQL服务器处理查询请求的整个过程
1、客户端发送SQL请求给服务器
2、服务器检查是否存在在缓存服务器中命中该SQL
intercept统计学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()条件进⾏优化
如何确定查询处理各个阶段所消耗的时间
php工程师教程使⽤profile程序连接
set profiling = 1;
执⾏查询：
show profiles;
show profile for query N;
查询的每个阶段所消耗的时间
使⽤profile查看语句所消耗的时间
特定的SQL查询优化
1、利⽤主从切换的原理进⾏⼤表的表结构修改，例如，现在从服务器上修改，修改完毕以后，进⾏主从切换，再在原来⽼的主上进⾏⼤表的修改，存在⼀定的风险。
2、在主服务器上创建于⼀个新的表，表结构就是将要修改⼤表后表结构，再把⽼表的数据重新导⼊到新表中，并在⽼表中建⽴⼀系列的触发器，把⽼表的数据同步更新到新表中，当⽼表中的数据全部同步到新表以后，再对⽼表加排它锁，把新表改成
使⽤pt-online-schema-change命令来修改⼤表，具体操作如下图所⽰
上图的参数解释
--alter      所使⽤的sql语句
--user        数据库的登录⽤户
--password    登录⽤户的密码
D            指定所有修改表的数据库名称
t            表的名称
--charset    指定数据库的字符串
--excute      执⾏
原创作品，转载请注明出处：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。