加入收藏 | 设为首页 | 会员中心 | 我要投稿 温州站长网 (https://www.0577zz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

自制小工具大大加速MySQL SQL语句优化(附源码)

发布时间:2019-06-25 04:11:03 所属栏目:MySql教程 来源:宜信技术学院
导读:副标题#e# 引言 优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的步骤。然而这些步骤重复性的执行,又会耗费DBA很多精力。于是萌发了自己编写

Join操作使用内存区域大小。只有当Join是ALL、index、range或index_merge时使用到Join Buffer。如果join语句较多,可以适当增大join_buffer_size。需要注意到是,这个值针对单个Thread。每个Thread都会自己创建独立的Buffer,而不是整个系统共享的Buffer,不要设置过大而造成系统内存不足。

tmp_table_size

如果内存内的临时表超过该值,MySQL自动将它转换为硬盘上的MyISAM表。如果执行许多高级GROUP BY查询并且有大量内存,则可以增加tmp_table_size的值。

read_buffer_size

读查询操作所能使用的缓冲区大小。这个参数是针对单个Thead的。

4、优化器开关

在MySQL中,还有一些参数是可以用来控制优化器行为的。

1) 参数说明

optimizer_search_depth

这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于"statistics"状态,可以考虑调低此参数。

optimizer_prune_level

默认是打开的,这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

optimizer_switch

这个变量包含了一些开启/关闭优化器特性的标志位。

示例 — 干预优化器行为(ICP特性)

自制小工具大大加速MySQL SQL语句优化(附源码)

默认情况下,ICP特性是开启的。查看一下优化器行为。

自制小工具大大加速MySQL SQL语句优化(附源码)

基于二级索引的过滤查询,使用了ICP特性,从Extra中的”Using index condition”可见。如果通过优化器开关,干预优化器行为,又会如何呢?

自制小工具大大加速MySQL SQL语句优化(附源码)

从Extra可见,ICP特性已经禁用。

5、系统状态(SHOW STATUS)

MySQL中也内置了一些状态,通过这些状态变量也可反映出语句执行的一些情况,方便定位问题。手工执行的话,可以在执行语句的前后分别执行SHOW STATUS命令,查看状态的变化。当然,因状态变量很多,对比起来不太方便,后面我介绍的小工具,可以解决这个问题。

1) 状态变量

状态变量很多,这里介绍几个。

Sort_merge_passes

排序算法已经执行的合并的数量。如果这个变量值较大,应考虑增加sort_buffer_size系统变量的值。

Sort_range

在范围内执行的排序的数量。

Sort_rows

已经排序的行数。

Sort_scan

通过扫描表完成的排序的数量。

Handler_read_first

索引中第一条被读的次数。读取索引头的次数,如果这个值很高,说明全索引扫描很多。

Handler_read_key

根据键读一行的请求数。如果较高,说明查询和表的索引正确。

Handler_read_next

按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。

Handler_read_prev

按照键顺序读前一行的请求数。

Handler_read_rnd

根据固定位置读一行的请求数。如果执行大量查询并需要对结果进行排序该值较高。则可能使用了大量需要MySQL扫描整个表的查询或连接没有正确使用键。

Handler_read_rnd_next

在数据文件中读下一行的请求数。如果正进行大量的表扫描,该值较高。通常说明表索引不正确或写入的查询没有利用索引。

6、SQL性能分析器(Query Profiler)

MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具,通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况,如CPU、IO、IPC、SWAP等,以及发生的PAGE FAULTS、CONTEXT SWITCHE等,同时还能得到该Query执行过程中的MySQL所调用的各个函数在源文件中的位置。

1) 使用方法

开启

  1. mysql> select @@profiling;  
  2. mysql> set profiling=1; 

默认情况下profiling的值为0表示MySQL SQL Profiler处于OFF状态,开启SQL性能分析器后profiling的值为1。

执行SQL语句

  1. mysql> select count(*) from t1; 

获取概要信息

使用"show profile"命令获取当前系统中保存的多个Query的profile的概要信息。

  1. mysql> show profiles;  
  2. +----------+------------+-----------------------+  
  3. | Query_ID | Duration   | Query                  |  
  4. +----------+------------+-----------------------+  
  5. |        1 | 0.00039300 | select count(*) from t1 |  
  6. +----------+------------+-----------------------+ 

针对单个Query获取详细的profile信息

(编辑:温州站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

热点阅读