宜信手艺实践｜克己小对象大大加快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输出的字段寄义，并由此进修若何判断一个执行规划。

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 |

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