如何高效快速地优化MySQL、SQL语句(附源码)

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

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

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

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

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

执行计划是语句优化的主要切入点,通过执行计划的判读了解语句的执行过程.在执行计划生成方面,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(如果是后者,显示它属于哪一种复杂类型).常用有以下几种标记类型.

1. DEPENDENT SUBQUERY

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

2. DEPENDENT UNION

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

3. PRIMARY

   子查询中的最外层查询,注意并不是主键查询.

4. SIMPLE

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

5. SUBQUERY

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

6. UNCACHEABLE SUBQUERY

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

7. UNION

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

8. UNION RESULT

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

9. DERIVED

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

• table

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

• type

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

1. ?system

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

2. ?const

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

3. ?eq_ref

   （编辑：ASP站长网）