澳门新浦京app下载MySQL SQL语句分析与查询优化详解,mysqlsql

在收集好数据之后,需要做的就是对数据进行分析。如果不知道哪里出了问题,可能需要分析的数据会很多,新版的
SQL Server 和 Windows 都提供了很多工具来优化这些过程,比如第 11
章中介绍的 PAL
工具,可以把从性能监视器得到的数据通过一些表格展示,而不需要用户额外再手动去转换、制图。当然,工具不是万能的。

性能问题有很多种,包含资源配置或使用问题、设计问题、编码问题、管理问题等。每种问题的处理手段和侧重点都不同,所以需要进行分类,然后再做处理。本书将会介绍在获取到性能数据之后,如何对数据进行分类。

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

如何获取有性能问题的SQL

1、通过用户反馈获取存在性能问题的SQL
2、通过慢查询日志获取性能问题的SQL
3、实时获取存在性能问题的SQL

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

澳门新浦京app下载 ,首先介绍下慢查询相关的参数

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)

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

澳门新浦京app下载 1

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

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

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

澳门新浦京app下载 2
澳门新浦京app下载 3

慢查询日志实例

慢查询的相关配置设置

澳门新浦京app下载 4

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

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

澳门新浦京app下载 5

常使用的慢查询日志分析工具(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

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
分析的结果如下

澳门新浦京app下载 6

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

澳门新浦京app下载 7

特定的SQL查询优化

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

澳门新浦京app下载 8
澳门新浦京app下载 9

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

澳门新浦京app下载 10

上图的参数解释

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

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

SQL语句分析与查询优化详解,mysqlsql
如何获取有性能问题的SQL 1、通过用户反馈获取存在性能问题的SQL
2、通过慢查询日志获取性能问题…

大部分的性能问题集中在低效的编码、不合理的设计及配置上面,当你面对庞大的性能数据时,如果一时间不知道从何处开始,可以尝试先把精力集中在这些地方。比如可以使用如下语句先查找逻辑读取最高的查询。

这里需要特别提醒的是,如果处理完一个性能问题之后就以为万事大吉,不做好分类、总结甚至一些处理预案,下一次还是有可能再次出现问题的。所以强烈建议,在处理现有的性能问题时,要对问题进行深度分析,找到根源,尽可能避免重复出现,这样才能做到最终的性能优化。同时,在处理完问题后,把问题归档,即使面对的是一些无法避免的问题,有了解决方法的归档和分类,也可以减少侦测性能瓶颈并搜索解决方法的花费时间和降低难度。

SELECTTOP(25)P.nameAS[SPName],Deps.total_logical_readsAS[TotalLogicalReads],deps.total_logical_reads/deps.execution_countAS[AvgLogicalReads],deps.execution_count,ISNULL(deps.execution_count/DATEDIFF(Second,deps.cached_time,GETDATE()),0)AS[Calls/Second],deps.total_elapsed_time,deps.total_elapsed_time/deps.execution_countAS[avg_elapsed_time],deps.cached_timeFROMsys.proceduresASpINNERJOINsys.dm_exec_procedure_statsASdepsONp.[Object_id]=deps.[Object_id]WHEREdeps.Database_id=DB_ID()ORDERBYdeps.total_logical_readsDESC;

性能问题的主要优化步骤如下。

然后找出这些查询的执行计划,再进行分析优化,这部分在第 8 章详细介绍。

❏ 分析实例级别的等待

❏ 组合等待和队列

❏ 确定方向,然后确定优化方案

❏ 细化到数据库、文件级别

❏ 细化到进程级别

❏ 优化索引 /查询

1 .分析实例级别的等待

优化方法论的第一步通常是从实例级别找出是哪些等待类型占用了大部分的等待时间。从
SQL Server 2005 开始,可以使用 DMV 来实现,如果是 SQLServer 2000,要通过
DBCC SQLPERF(WAITSTATS)
命令来实现。等待信息是很好的问题切入点,而其他工具可能返回的信息过多,容易产生误导。这部分将在第
7章中详细介绍。

通过等待信息,可以发现比如锁、闩锁、
IO、事务日志、内存等对象相关的等待。但是需要提醒的是,大部分的资源问题都可能是因为设计不合理、编码低效引起的,并不一定是资源真的有问题。

2.组合等待和队列

一旦找到实例级别最高的几个等待类型,就可以把研究面缩小。接下来就是组合等待类型和队列找出有问题的资源了,这一步主要使用性能监视器,跟踪比如
I/O 队列、缓存命中率、内存等相关计数器,也可以使用 SQL Server 2005
开始提供的 sys.dm_os_performance_counters 这个 DMV
来查看常用的计数器。对于其他有用但是暂时未提供的计数器,还是需要使用性能监视器来监控。这一步在第
11 章介绍。

3.确定方向,然后确定方案

通过前面的步骤得到准确的信息之后,就要根据这些信息定好优化的方向,然后确定方案。但是如果你发现存在的是一些资源问题,或者阻塞、编译重编译等问题,那么采取的方案就不一样了。

4.细化到数据库、文件级别

到这一步就要细化到数据库、文件级别了,找到哪个库占了比较大的开销,通常是用户数据库引起的,但也可能是系统库的问题。除了库,还要找文件类型,比如是数据文件还是日志文件,文件类型的不同决定了方案的不同。
SQL Server 2005 开始提供的 sys.dm_io_virtual_file_stats
函数,可以返回文件相关的 I/O 信息。如果是 SQL Server 2000,可以使用
::fn_virtualfilestats 函数。

如果是日志文件问题,检查是否和数据文件有 I/O
争用,因为两者的读写行为是不一样的,日志文件是顺序读写,数据文件是随机读写。如果是
TempDB 问题,要考虑是否代码使用临时对象过多,配置有无失当等,这部分在第
10 章介绍。

5.细化到进程级别

把问题集中到一个库上时,可以进一步细化到进程级别,也就是找到需要优化的进程,比如存储过程、批处理等。这里可以使用一些
DMV 、
Profiler等工具来查找,比如查找一些查询是否返回了大量的数据、运行时间长的查询、CPU
、 IO 高的查询等。

6.优化索引 /查询

找到“元凶”之后,就进入优化查询的步骤。首先是改进代码,特别是改写一些明显有性能问题的写法,比如可以用
CTE 来替代的一些游标查询,适当添加高效的 where 条件,避免非 SARG
写法等,使其能从写法上尽可能符合高效利用索引的要求。但是事情总不会那么如意,比如一些二次开发的程序或者一些设计有问题的表结构,会导致你的代码已经没有改写的空间。如果你认为代码无法改进,可能需要通过修改索引甚至是修改设计来实现,比如一个添加一个覆盖索引,把表进行垂直拆分或者水平拆分等。这部分是本书的重点,将在第
6章介绍。

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图