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

如何获取有性能问题的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、第一行记录的信息为使用sbtest做的测试
2、第二行记录的信息为慢查询日志的时间
3、第三行记录的信息为所使用锁的时间
4、第四行记录的信息为返回的数据行数
5、第五行记录的信息为扫描数据的行数
6、第六行记录的信息为时间戳
7、第七行记录的信息为查询的SQL语句

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

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


慢查询日志实例

慢查询的相关配置设置

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

]# cd /var/lib/mysql/log
]# mysqldumpslow -s r -t 10 slow-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/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服务器处理查询请求的整个过程

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查看语句所消耗的时间

特定的SQL查询优化

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


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

上图的参数解释

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

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

(0)

相关推荐

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

    如何获取有性能问题的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 指定慢查询日志

  • MySQL慢查询分析工具pt-query-digest详解

    目录 一.简介 二.安装pt-query-digest 三.pt-query-digest语法及重要选项 四.分析pt-query-digest输出结果 五.用法示例 一.简介 pt-query-digest是用于分析mysql慢查询的一个工具,它可以分析binlog.General log.slowlog,也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析.可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进

  • mysql update语句的执行过程详解

    以前有过一篇关于MySQL查询语句的执行过程,这里总结一下update语句的执行过程.由于update涉及到数据的修改,所以,很容易推断,update语句比select语句会更复杂一些. 1,准备 创建一张test表 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) NOT NULL DEFAULT '0' COMMENT '数值', PRIMARY KEY (`id`) ) ENGINE=InnoDB

  • IDEA连接MySQL数据库并执行SQL语句使用数据图文详解

    目录 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 二.使用数据库的数据 (一)新建Java类 Test (二)下载MySQL驱动Jar包 (三)返回IDEA,新建文件夹lib (四)非常重要(添加到库) (五)编写Test类 (六)运行主函数,得到数据 三.总结 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 1.点击右侧DataBase 2.点击加号,找到MySQL,添加数据库 3.输入用户名和密码,点击Test Co

  • MySQL的索引原理以及查询优化详解

    目录 一.介绍 1.什么是索引? 2.为什么要有索引呢? 二.索引的原理 一 索引原理 二 磁盘IO与预读 三.索引的数据结构 四.Mysql索引管理 一.功能 二.MySQL的索引分类 三. 索引的两大类型hash与btree 四.创建/删除索引的语法 五.测试索引 1.准备 2 .在没有索引的前提下测试查询速度 3. 加上索引 六.正确使用索引 一.覆盖索引 二.联合索引 三.索引合并 七.慢查询优化的基本步骤 总结 一.介绍 1.什么是索引? 一般的应用系统,读写比例在10:1左右,而且插

  • Mysql的SELECT语句与显示表结构详解

    SELECT... SELECT 1+1, 2+2;# 直接这样写相当于下面这句 SELECT 1+1, 2+2 FROM DUAL; # 这里DUAL:伪表 SELECT ... FROM 语法: SELECT 标识选择哪些字段(列)FROM 标识从哪个表中选择 例如选择全部列: SELECT * FROM departments; 一般情况下,除非需要使用表中所有的字段数据,最好不要使用通配符‘*’.使用通配符虽然可以节省输入查询语句的时间,但是获取不需要的列数据通常会降低查询和所使用的应用

  • Mysql Limit 分页查询优化详解

    select * from table LIMIT 5,10; #返回第6-15行数据 select * from table LIMIT 5; #返回前5行 select * from table LIMIT 0,5; #返回前5行 我们来写分页 物理分页 select * from table LIMIT (当前页-1)*每页显示条数,每页显示条数; MySQL之Limit简单优化.md 同样是取90000条后100条记录,传统方式还是改造方式? 传统方式是先取了前90001条记录,取其中最

  • Web网络安全漏洞分析XSS常用语句及编码绕过详解

    目录 XSS进阶 XSS常用语句及编码绕过 XSS常用的测试语句 JS编码 HTML实体编码 URL编码 XSS进阶 XSS常用语句及编码绕过 XSS常用的测试语句 <img src=1 alert(1)> (显示1) <script>alert("xss")</script> (显示xss) <script>alert(document.cookie)</script> (显示cookie) <script>wi

  • MySQL索引最左匹配原则实例详解

    目录 简介 准备 理论详解 聚集索引和非聚集索引 回表查询 索引覆盖 最左匹配原则 详细规则 补充:为什么要使用联合索引 总结 简介 这篇文章的初衷是很多文章都告诉你最左匹配原则,却没有告诉你,实际场景下它到底是如何工作的,本文就是为了阐述清这个问题. 准备 为了方面后续的说明,我们首先建立一个如下的表(MySQL5.7),表中共有5个字段(a.b.c.d.e),其中a为主键,有一个由b,c,d组成的联合索引,存储引擎为InnoDB,插入三条测试数据.强烈建议自己在MySQL中尝试本文的所有语句

  • MySql执行流程与生命周期详解

    目录 一.MySQL 客户端 二.MySQL 服务端 1. 查询缓存 2. SQL 解析器 3. 查询优化器 4. SQL 执行器 总结 我们经常会在一起讨论怎样优化 SQL.怎样优化数据库,那么请问你知道执行一条 SQL 的过程中都经过了哪些动作吗?如果不知道的话,又怎么去优化 SQL.优化数据库呢?为了更好地优化 MySQL 数据库,接下来我们就来讨论一下执行一条 SQL 都经过哪些步骤. 为了方便理解,在介绍 SQL 执行流程之前,我们先通过一张图,来详细阐述一下 SQL 执行流程. 由上

随机推荐