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

引言

优化 SQL，是 DBA 常见的工作之一。如何高效、快速地优化一条语句，是每个 DBA 经常要面对的一个问题。在日常的优化工作中，我发现有很多操作是在优化过程中必不可少的步骤。然而这些步骤重复性的执行，又会耗费 DBA 很多精力。于是萌发了自己编写小工具，提高优化效率的想法。

那选择何种语言来开发工具呢？

对于一名 DBA 来说，掌握一门语言配合自己的工作是非常必要的。相对于 shell 的简单、perl 的飘逸，Python 是一种严谨的高级语言。其具备上手快、语法简单、扩展丰富、跨平台等多种优点。很多人把它称为一种“胶水”语言，通过大量丰富的类库、模块，可以快速搭建出自己需要的工具。

于是乎，这个小工具就成了我学习 Python 的第一个作业，我把它称之为“MySQL 语句优化辅助工具”。而且从此以后，我深深爱上了 Python，并开发了很多数据库相关的小工具，以后有机会介绍给大家。

一、优化手段、步骤

下面在介绍工具使用之前，首先说明下 MySQL 中语句优化常用的手段、方法及需要注意的问题。这也是大家在日常手工优化中，需要了解掌握的。

1、执行计划 — EXPLAIN 命令

执行计划是语句优化的主要切入点，通过执行计划的判读了解语句的执行过程。在执行计划生成方面，MySQL 与 Oracle 明显不同，它不会缓存执行计划，每次都执行“硬解析”。查看执行计划的方法，就是使用 EXPLAIN 命令。

1）基本用法

EXPLAIN QUERY

当在一个 Select 语句前使用关键字 EXPLAIN 时，MySQL 会解释了即将如何运行该 Select 语句，它显示了表如何连接、连接的顺序等信息。

EXPLAIN EXTENDED QUERY

当使用 EXTENDED 关键字时，EXPLAIN 产生附加信息，可以用 SHOW WARNINGS 浏览。该信息显示优化器限定 SELECT 语句中的表和列名，重写并且执行优化规则后 SELECT 语句是什么样子，并且还可能包括优化过程的其它注解。在 MySQL5.0 及更新的版本里都可以使用，在 MySQL5.1 里它有额外增加了一个过滤列(filtered)。

EXPLAIN PARTITIONS QUERY

显示的是查询要访问的数据分片——如果有分片的话。它只能在 MySQL5.1 及更新的版本里使用。

EXPLAIN FORMAT=JSON (5.6 新特性)

另一个格式显示执行计划。可以看到诸如表间关联方式等信息。

2）输出字段

下面说明一下 EXPLAIN 输出的字段含义，并由此学习如何判断一个执行计划。

id

MySQL 选定的执行计划中查询的序列号。如果语句里没有子查询等情况，那么整个输出里就只有一个 SELECT，这样一来每一行在这个列上都会显示一个 1。如果语句中使用了子查询、集合操作、临时表等情况，会给 ID 列带来很大的复杂性。如上例中，WHERE 部分使用了子查询，其 id= 2 的行表示一个关联子查询。

select_type

语句所使用的查询类型。是简单 SELECT 还是复杂 SELECT(如果是后者，显示它属于哪一种复杂类型)。常用有以下几种标记类型。

• DEPENDENT SUBQUERY

子查询内层的第一个 SELECT，依赖于外部查询的结果集。

• DEPENDENT UNION

子查询中的 UNION，且为 UNION 中从第二个 SELECT 开始的后面所有 SELECT，同样依赖于外部查询的结果集。

• PRIMARY

子查询中的最外层查询，注意并不是主键查询。

• SIMPLE

除子查询或 UNION 之外的其他查询。

• SUBQUERY

子查询内层查询的第一个 SELECT，结果不依赖于外部查询结果集。

• UNCACHEABLE SUBQUERY

结果集无法缓存的子查询。

• UNION

UNION 语句中的第二个 SELECT 开始后面的所有 SELECT，第一个 SELECT 为 PRIMARY。

• UNION RESULT

UNION 中的合并结果。从 UNION 临时表获取结果的 SELECT。

• DERIVED

衍生表查询(FROM 子句中的子查询)。MySQL 会递归执行这些子查询，把结果放在临时表里。在内部，服务器就把当做一个 ” 衍生表 ” 那样来引用，因为临时表就是源自子查询。

table

这一步所访问的数据库中表的名称或者 SQL 语句指定的一个别名表。这个值可能是表名、表的别名或者一个为查询产生的临时表的标识符，如派生表、子查询或集合。

type

表的访问方式。以下列出了各种不同类型的表连接，依次是从最好的到最差的。

• system

系统表，表只有一行记录。这是 const 表连接类型的一个特例。

• const

读常量，最多只有一行匹配的记录。由于只有一行记录，优化程序里该行记录的字段值可以被当作是一个恒定值。const 用于在和 PRIMARY KEY 或 UNIQUE 索引中有固定值比较的情形。

• eq_ref

最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与 const 类型不同的是，这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个 PRIMARY KEY 或 UNIQUE 类型。eq_ref 可以用于在进行 ”=” 做比较时检索字段。比较的值可以是固定值或者是表达式，表达示中可以使用表里的字段，它们在读表之前已经准备好了。

• ref

JOIN 语句中驱动表索引引用的查询。该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref 用于连接程序使用键的最左前缀或者是该键不是 PRIMARY KEY 或 UNIQUE 索引 (换句话说，就是连接程序无法根据键值只取得一条记录) 的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。ref 还可以用于检索字段使用 ”=” 操作符来比较的时候。

• ref_or_null

与 ref 的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。这种连接类型类似 ref，不同的是 MySQL 会在检索的时候额外的搜索包含 NULL 值的记录。这种连接类型的优化是从 MySQL 4.1.1 开始的，它经常用于子查询。

• index_merge

查询中同时使用两个 (或更多) 索引，然后对索引结果进行合并(merge)，再读取表数据。这种连接类型意味着使用了 Index Merge 优化方法。

• unique_subquery

子查询中的返回结果字段组合是主键或唯一约束。

• index_subquery

子查询中的返回结果字段组合是一个索引(或索引组合)，但不是一个主键或唯一索引。这种连接类型类似 unique_subquery。它用子查询来代替 IN，不过它用于在子查询中没有唯一索引的情况下。

• range

索引范围扫描。只有在给定范围的记录才会被取出来，利用索引来取得一条记录。

• index

全索引扫描。连接类型跟 ALL 一样，不同的是它只扫描索引树。它通常会比 ALL 快点，因为索引文件通常比数据文件小。MySQL 在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。

• fulltext

全文索引扫描。

• all

全表扫描。

possible_keys

该字段是指 MySQL 在搜索表记录时可能使用哪个索引。如果没有任何索引可以使用，就会显示为 null。

key

查询优化器从 possible_keys 中所选择使用的索引。key 字段显示了 MySQL 实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是 NULL。

key_len

被选中使用索引的索引键长度。key_len 字段显示了 MySQL 使用索引的长度。当 key 字段的值为 NULL 时，索引的长度就是 NULL。

ref

列出是通过常量，还是某个表的某个字段来过滤的。ref 字段显示了哪些字段或者常量被用来和 key 配合从表中查询记录出来。

rows

该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数。

Extra

该字段显示了查询中 MySQL 的附加信息。

filtered

这个列式在 MySQL5.1 里新加进去的，当使用 EXPLAIN EXTENDED 时才会出现。它显示的是针对表里符合某个条件 (WHERE 子句或联接条件) 的记录数的百分比所作的一个悲观估算。

3) SQL 改写

EXPLAIN 除了可以显示执行计划外，还可以显示 SQL 改写。所谓 SQL 改写，是指 MySQL 在对 SQL 语句进行优化前，会基于一些原则进行语句的改写，以方便后面的优化器进行优化生成更优的执行计划。该功能是通过 EXPLAIN EXTENDED+SHOW WARNINGS 配合使用。下面通过示例说明一下。

从上面示例中，可看到原有语句中的 IN 子查询被改写成为表间关联的方式。

2、统计信息

查看统计信息也是优化语句中必不可少的一步。通过统计信息可以快速了解对象的存储特征如何。下面说明主要的两类统计信息——表、索引。

1) 表统计信息 — SHOW TABLE STATUS

• Name：表名
• Engine：表的存储引擎类型(ISAM、MyISAM 或 InnoDB)
• Row_format：行存储格式(Fixed- 固定的、Dynamic- 动态的或 Compressed- 压缩的)
• Rows：行数量。在某些存储引擎中，例如 MyISAM 和 ISAM 他们存储了精确的记录数。不过其他存储引擎中，它可能只是近似值。
• Avg_row_length：平均行长度。
• Data_length：数据文件的长度。
• Max_data_length：数据文件的最大长度。
• Index_length：索引文件的长度。
• Data_free：已分配但未使用了字节数。
• Auto_increment：下一个 autoincrement(自动加 1)值。
• Create_time：表被创造的时间。
• Update_time：数据文件最后更新的时间。
• Check_time：最后对表运行一个检查的时间。执行 mysqlcheck 命令后更新，仅对 MyISAM 有效。
• Create_options：额外留给 CREATE TABLE 的选项。
• Comment：当创造表时，使用的注释(或为什么 MySQL 不能存取表信息的一些信息)。
• Version：数据表的 ’.frm’ 文件版本号。
• Collation：表的字符集和校正字符集。
• Checksum：实时的校验和值(如果有的话)。

3、索引统计信息 — SHOW INDEX

• Table：表名。
• Non_unique：0，如果索引不能包含重复。
• Key_name：索引名
• Seq_in_index：索引中的列顺序号，从 1 开始。
• Column_name：列名。
• Collation：列怎样在索引中被排序。在 MySQL 中，这可以有值 A(升序)或 NULL(不排序)。
• Cardinality：索引中唯一值的数量。
• Sub_part：如果列只是部分被索引，索引字符的数量。当整个字段都做索引了，那么它的值是 NULL。
• Packed：表示键值是如何压缩的，NULL 表示没有压缩。
• Null：当字段包括 NULL 的记录是 YES，它的值为，反之则是 ”。
• Index_type：使用了哪种索引算法(有 BTREE、FULLTEXT、HASH、RTREE)。
• Comment：备注。
• 系统参数：系统参数也会影响语句的执行效率。查看系统参数，可使用 SHOW VARIABLES 命令。

1) 参数说明

系统参数很多，下面介绍几个。

sort_buffer_size

排序区大小。其大小直接影响排序使用的算法。如果系统中排序都比较大、内存充足且并发量不是很大的情况，可以适当增加此参数。这个参数是针对单个 Thead 的。

join_buffer_size

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 特性）

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

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

从 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) 使用方法

开启

mysql> select @@profiling;

mysql> set profiling=1;

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

执行 SQL 语句

mysql> select count(*) from t1;

获取概要信息

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

mysql> show profiles;

+----------+------------+-----------------------+

| Query_ID | Duration   | Query                   |

+----------+------------+-----------------------+

|        1 | 0.00039300 | select count(*) from t1 |

+----------+------------+-----------------------+

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

在获取概要信息之后，就可以根据概要信息的 Query_ID 来获取某个 Query 的执行过程中详细的 profile 信息。

mysql> show profile for query 1;

mysql> show profile cpu,block io for query 1;

二、工具说明

前面谈到了多种手段，对于 SQL 语句的调优都有所帮助。通过下面这个小工具，可以自动调用命令将上面这些内容一次性推给 DBA，大大加速优化的过程。

1、准备条件

模块 – MySQLDB

模块 – sqlparse

Python 版本 = 2.7.3 (2.6.x 版本应该也没问题，3.x 版本没测试)

2、调用方法

python mysql_tuning.py -p tuning_sql.ini -s 'select xxx'

1) 参数说明

-p 指定配置文件名称

-s 指定 SQL 语句

3、配置文件

共分两节信息，分别是 [database] 描述数据库连接信息，[option]运行配置信息。

1) [database]

server_ip   = 127.0.0.1

db_user     = testuser

db_pwd      = testpwd

db_name     = test

2) [option]

sys_parm    = ON     // 是否显示系统参数

sql_plan    = ON // 是否显示执行计划

obj_stat    = ON // 是否显示相关对象 (表、索引) 统计信息

ses_status  = ON // 是否显示运行前后状态信息(激活后会真实执行 SQL)

sql_profile = ON   // 是否显示 PROFILE 跟踪信息(激活后会真实执行 SQL)

4、输出说明

1) 标题部分

包含运行数据库的地址信息及数据版本信息。

2) 原始 SQL

用户执行输入的 SQL，这部分主要是为了后续对比 SQL 改写时使用。语句显示时使用了格式化。

3) 系统级参数

脚本选择显示了部分与 SQL 性能相关的参数。这部分是写死在代码中的，如需扩展需要修改脚本。

4) 优化器开关

下面是和优化器相关的一些参数，通过调整这些参数可以人为干预优化器行为。

5) 执行计划

就是调用 explain extended 的输出结果。如果结果过长，可能出现显示串行的问题(暂时未解决)。

6) 优化器改写后的 SQL

通过这里可判断优化器是否对 SQL 进行了某种优化(例如子查询的处理)。

7) 统计信息

在 SQL 语句中所有涉及到的表及其索引的统计信息都会在这里显示出来。

8) 运行状态信息

在会话级别对比了执行前后的状态(SHOW STATUS)，并将出现变化的部分显示出来。需要注意的是，因为收集状态数据是采用 SELECT 方式，会造成个别指标的误差(例如 Com_select)。

9) PROFILE 详细信息

调用 SHOW PROFILE 得到的详细信息。

10) PROFILE 汇总信息

根据 PROFILE 的资源消耗情况，显示不同阶段消耗对比情况(TOP N)，直观显示 ” 瓶颈 ” 所在。

源码文件下载地址：https://pan.baidu.com/s/1slF3…

作者：韩锋

内容来源：宜信技术学院