mysql 分析查询语句,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;

履行查询： showprofiles; show profile forqueryN; 查询的每个阶段所耗费的时刻

运用profile检查句子所耗费的时刻

特定的SQL查询优化

1、运用主从切换的原理进行大表的表结构修正，例如，现在从服务器上修正，修正结束今后，进行主从切换，再在本来老的主上进行大表的修正，存在必定的危险。2、在主服务器上创建于一个新的表，表结构便是即将修正大表后表结构，再把老表的数据从头导入到新表中，并在老表中树立一系列的触发器，把老表的数据同步更新到新表中，当老表中的数据悉数同步到新表今后，再对老表加排它锁，把新表改成老表的称号，删去重命名的老表，如下图所示

运用pt-online-schema-change指令来修正大表，具体操作如下图所示

上图的参数解说

--alter 所运用的sql句子

--user 数据库的登录用户

--password 登录用户的暗码

D 指定一切修正表的数据库称号

t 表的称号

--charset 指定数据库的字符串

--excute 履行

作者：demon_gdy