未加星标

MySQL SQL语句分析与查询优化详解

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 | 作者 红领巾 ] 0人收藏点击收藏

如何获取有性能问题的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)

慢查询使用日志中记录的信息

MySQL SQL语句分析与查询优化详解
1、第一行记录的信息为使用sbtest做的测试
2、第二行记录的信息为慢查询日志的时间
3、第三行记录的信息为所使用锁的时间
4、第四行记录的信息为返回的数据行数
5、第五行记录的信息为扫描数据的行数
6、第六行记录的信息为时间戳
7、第七行记录的信息为查询的SQL语句

使用慢查询获取有性能问题的SQL

常使用的慢查询日志分析工具(mysqldumpslow)
介绍:汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中所指定的顺序输出
MySQL SQL语句分析与查询优化详解
MySQL SQL语句分析与查询优化详解

慢查询日志实例

慢查询的相关配置设置

MySQL SQL语句分析与查询优化详解

命令行执行参数查看分析的结果

]# cd /var/lib/mysql/log
]# mysqldumpslow -s r -t 10 slow-mysql

MySQL 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语句分析与查询优化详解

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;
执行查询:
show profiles;

show profile for query N;

查询的每个阶段所消耗的时间

使用profile查看语句所消耗的时间
MySQL SQL语句分析与查询优化详解

特定的SQL查询优化

1、利用主从切换的原理进行大表的表结构修改,例如,现在从服务器上修改,修改完毕以后,进行主从切换,再在原来老的主上进行大表的修改,存在一定的风险。
2、在主服务器上创建于一个新的表,表结构就是将要修改大表后表结构,再把老表的数据重新导入到新表中,并在老表中建立一系列的触发器,把老表的数据同步更新到新表中,当老表中的数据全部同步到新表以后,再对老表加排它锁,把新表改成老表的名称,删除重命名的老表,如下图所示
MySQL SQL语句分析与查询优化详解
MySQL SQL语句分析与查询优化详解

使用pt-online-schema-change命令来修改大表,具体操作如下图所示

MySQL SQL语句分析与查询优化详解

上图的参数解释

--alter 所使用的sql语句
--user 数据库的登录用户
--password 登录用户的密码
D 指定所有修改表的数据库名称
t 表的名称
--charset 指定数据库的字符串
--excute 执行

原创作品,转载请注明出处


本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

分页:12
转载请注明
本文标题:MySQL SQL语句分析与查询优化详解
本站链接:https://www.codesec.net/view/573711.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(259)