韩锋，宜信技能研发中央数据库架构师。精通多种关系型数据库，曾任职于当当网、TOM在线等公司，曾任多家公司首席DBA、数据库架构师等职，多年一线数据库架构、设计、开拓履历。著有《SQL优化最佳实践》一书。
弁言
优化SQL，是DBA常见的事情之一。如何高效、快速地优化一条语句，是每个DBA常常要面对的一个问题。在日常的优化事情中，我创造有很多操作是在优化过程中必不可少的步骤。然而这些步骤重复性的实行，又会耗费DBA很多精力。于是萌发了自己编写小工具，提高优化效率的想法。
那选择何种措辞来开拓工具呢？
对付一名DBA来说，节制一门措辞合营自己的事情是非常必要的。相对付shell的大略、perl的洒脱，Python是一种严谨的高等措辞。其具备上手快、语法大略、扩展丰富、跨平台等多种优点。很多人把它称为一种“胶水”措辞，通过大量丰富的类库、模块，可以快速搭建出自己须要的工具。
于是乎，这个小工具就成了我学习Python的第一个作业，我把它称之为“MySQL语句优化赞助工具”。而且从此往后，我深深爱上了Python，并开拓了很多数据库干系的小工具，往后有机会先容给大家。
一、优化手段、步骤
下面在先容工具利用之前，首先解释下MySQL中语句优化常用的手段、方法及须要把稳的问题。这也是大家在日常手工优化中，须要理解节制的。
1、实行操持 — EXPLAIN命令
实行操持是语句优化的紧张切入点，通过实行操持的判读理解语句的实行过程。在实行操持天生方面，MySQL与Oracle明显不同，它不会缓存实行操持，每次都实行“硬解析”。查看实行操持的方法，便是利用EXPLAIN命令。
基本用法
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新特性)
另一个格式显示实行操持。可以看到诸如表间关联办法等信息。
输出字段
下面解释一下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_
与ref的唯一差异便是在利用索引引用的查询之外再增加一个空值的查询。这种连接类型类似ref，不同的是MySQL会在检索的时候额外的搜索包含值的记录。这种连接类型的优化是从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在搜索表记录时可能利用哪个索引。如果没有任何索引可以利用，就会显示为。
key
查询优化器从possible_keys中所选择利用的索引。key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值便是。
key_len
当选中利用索引的索引键长度。key_len字段显示了MySQL利用索引的长度。当key字段的值为时，索引的长度便是。
ref
列出是通过常量，还是某个表的某个字段来过滤的。ref字段显示了哪些字段或者常量被用来和key合营从表中查询记录出来。
rows
该字段显示了查询优化器通过系统网络的统计信息估算出来的结果集记录条数。
Extra
该字段显示了查询中MySQL的附加信息。
filtered
这个列式在MySQL5.1里新加进去的，当利用EXPLAIN EXTENDED时才会涌现。它显示的是针对表里符合某个条件(WHERE子句或联接条件)的记录数的百分比所作的一个悲观估算。
SQL改写
EXPLAIN除了可以显示实行操持外，还可以显示SQL改写。所谓SQL改写，是指MySQL在对SQL语句进行优化前，会基于一些原则进行语句的改写，以方便后面的优化器进行优化天生更优的实行操持。该功能是通过EXPLAIN EXTENDED+SHOW WARNINGS合营利用。下面通过示例解释一下。
从上面示例中，可看到原有语句中的IN子查询被改写成为表间关联的办法。
2、统计信息
查看统计信息也是优化语句中必不可少的一步。通过统计信息可以快速理解工具的存储特色如何。下面解释紧张的两类统计信息——表、索引。
表统计信息 — 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(升序)或(不排序)。
Cardinality：索引中唯一值的数量。
Sub_part：如果列只是部分被索引，索引字符的数量。当全体字段都做索引了，那么它的值是。
Packed：表示键值是如何压缩的，表示没有压缩。
：当字段包括的记录是YES，它的值为，反之则是''。
Index_type：利用了哪种索引算法(有BTREE、FULLTEXT、HASH、RTREE)。
Comment：备注。
系统参数：系统参数也会影响语句的实行效率。查看系统参数，可利用SHOW VARIABLES命令。
参数解释
系统参数很多，下面先容几个。
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中，还有一些参数是可以用来掌握优化器行为的。
参数解释
optimizer_search_depth
这个参数掌握优化器在穷举实行操持时的限度。如果查询永劫光处于\公众statistics\"大众状态，可以考虑调低此参数。
optimizer_prune_level
默认是打开的，这让优化器会根据须要扫描的行数来决定是否跳过某些实行操持。
optimizer_switch
这个变量包含了一些开启/关闭优化器特性的标志位。
示例 — 干预优化器行为（ICP特性）
默认情形下，ICP特性是开启的。查看一下优化器行为。
基于二级索引的过滤查询，利用了ICP特性，从Extra中的”Using index condition”可见。如果通过优化器开关，干预优化器行为，又会如何呢？
从Extra可见，ICP特性已经禁用。
5、系统状态（SHOW STATUS）
MySQL中也内置了一些状态，通过这些状态变量也可反响出语句实行的一些情形，方便定位问题。手工实行的话，可以在实行语句的前后分别实行SHOW STATUS命令，查看状态的变革。当然，因状态变量很多，比拟起来不太方便，后面我先容的小工具，可以办理这个问题。
状态变量
状态变量很多，这里先容几个。
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所调用的各个函数在源文件中的位置。
利用方法
开启
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'
参数解释
-p 指定配置文件名称
-s 指定SQL语句
3、配置文件
共分两节信息，分别是[database]描述数据库连接信息，[option]运行配置信息。
[database]
server_ip = 127.0.0.1
db_user = testuser
db_pwd = testpwd
db_name = test
[option]
sys_parm = ON //是否显示系统参数
sql_plan = ON //是否显示实行操持
obj_stat = ON //是否显示干系工具(表、索引)统计信息
ses_status = ON //是否显示运行前后状态信息(激活后会真实实行SQL)
sql_profile = ON //是否显示PROFILE跟踪信息(激活后会真实实行SQL)
4、输出解释
标题部分
包含运行数据库的地址信息及数据版本信息。
原始SQL
用户实行输入的SQL，这部分紧张是为了后续比拟SQL改写时利用。语句显示时利用了格式化。
系统级参数
脚本选择显示了部分与SQL性能干系的参数。这部分是写去世在代码中的，如需扩展须要修正脚本。
优化器开关
下面是和优化器干系的一些参数，通过调度这些参数可以人为干预优化器行为。
实行操持
便是调用explain extended的输出结果。如果结果过长，可能涌现显示串行的问题(暂时未办理)。
优化器改写后的SQL
通过这里可判断优化器是否对SQL进行了某种优化(例如子查询的处理)。
统计信息
在SQL语句中所有涉及到的表及其索引的统计信息都会在这里显示出来。
运行状态信息
在会话级别比拟了实行前后的状态(SHOW STATUS)，并将涌现变革的部分显示出来。须要把稳的是，由于网络状态数据是采取SELECT办法，会造成个别指标的偏差(例如Com_select)。
PROFILE详细信息
调用SHOW PROFILE得到的详细信息。
PROFILE汇总信息
根据PROFILE的资源花费情形，显示不同阶段花费比拟情形(TOP N)，直不雅观显示\"大众瓶颈\"大众所在。
源码文件下载
点击文末【阅读原文】即可获取“MySQL 语句优化赞助工具”源码文件。
干系专题：
精选专题（官网：dbaplus.cn）
◆ 近期热文◆
◆ 专家专栏◆
◆ 近期活动◆
Gdevops环球敏捷运维峰会上海站
峰会官网：www.gdevops.com
