简介优化SQL是DBA的常见工作之一。如何高效快速的优化一条语句是每个DBA经常面临的问题。在日常的优化工作中，我发现很多操作都是优化过程中必不可少的步骤。但是，这些步骤的重复执行会消耗DBA大量的精力。于是萌生了自己写一个小工具来提高优化效率的想法。那么选择哪种语言来开发工具呢？对于一个DBA来说，掌握一门与其工作相匹配的语言是非常有必要的。与shell的简洁和perl的优雅相比，Python是一门严谨的高级语言。它具有上手快、语法简单、扩展丰富、跨平台等诸多优点。许多人称它为“胶水”语言。通过大量丰富的类库和模块，您可以快速构建您需要的工具。于是乎，这个小工具就成了我学习Python的第一份作业，我称之为“MySQL语句优化辅助工具”。并且从那时起，我就深深地爱上了Python，开发了很多数据库相关的小工具，有机会再介绍给大家。一、优化方法和步骤下面，在介绍工具的使用之前，先说明一下MySQL中语句优化的常用手段、方法和需要注意的问题。这是大家在日常手动优化中需要了解和掌握的。1.执行计划——EXPLAIN命令的执行计划是语句优化的主要切入点。通过对执行计划的解读，可以了解语句的执行过程。在执行计划生成方面，MySQL与Oracle有明显的不同。它不缓存执行计划，每次都进行“硬解析”。查看执行计划的方法是使用EXPLAIN命令。1）基本用法EXPLAINQUERY在Select语句前使用关键字EXPLAIN时，MySQL会解释Select语句是如何运行的，显示表是如何连接的，连接的顺序等信息。EXPLAINEXTENDEDQUERY当使用EXTENDED关键字时，EXPLAIN会生成额外的信息，这些信息可以通过SHOWWARNINGS浏览。此信息显示优化器在SELECT语句中限定的表名和列名，重写和执行优化规则后SELECT语句的样子，还可能包括有关优化过程的其他注释。可用于MySQL5.0及更新版本。在MySQL5.1中，它多了一个过滤列（filtered）。EXPLAINPARTITIONSQUERY显示查询要访问的数据分区-如果有分区。只能用于MySQL5.1及以上版本。EXPLAINFORMAT=JSON(Newin5.6)另一种显示执行计划的格式。您可以查看表之间的关联方式等信息。2）输出字段下面解释EXPLAIN输出的字段含义，从中学习如何判断一个执行计划。idMySQL查询在选定执行计划中的序号。如果语句中没有子查询等，那么整个输出就只有一个SELECT，这样每一行都会在这一列显示一个1。如果语句中使用了子查询、集合操作、临时表等，都会给ID列带来很大的复杂度。如上例，WHERE部分使用了子查询，id=2的行表示关联子查询。select_type语句使用的查询类型。它是一个简单的SELECT还是一个复杂的SELECT（如果是后者，它是哪种复杂类型）。通常使用以下类型的标记。DEPENDENTSUBQUERY子查询内层的第一个SELECT依赖于外层查询的结果集。DEPENDENTUNION子查询中的UNION，以及UNION中从第二个SELECT开始的所有后续SELECT，也依赖于外部查询的结果集。PRIMARY子查询中的最外层查询，注意不是主键查询。子查询或UNION以外的SIMPLE查询。SUBQUERY子查询的内层查询的第一个SELECT，结果不依赖于外层查询的结果集。UNCACHEABLESUBQUERY其结果集无法缓存的子查询。UNIONUNION语句中的第二个SELECT启动所有后续SELECT，第一个SELECT是PRIMARY。合并结果为UNIONRESULTUNION。从UNION临时表中获取结果的SELECT。DERIVED派生表查询（FROM子句中的子查询）。MySQL将递归地执行这些子查询并将结果放在临时表中。在内部，服务器将其称为“派生表”，因为临时表是从子查询派生的。table此步骤访问的数据库中的表名或SQL语句指定的别名表。该值可以是表名、表别名或为查询生成的临时表的标识符，例如派生表、子查询或集合。类型表的访问方法。下面按从好到坏的顺序列出了各种类型的表连接。system系统表，该表只有一行记录。这是const表连接类型的一个特例。const读取常量，最多只有一条匹配记录。由于只有一行，所以这一行的字段值在优化器中可以看作是一个常量值。const用于与PRIMARYKEY或UNIQUE索引进行比较的固定值的情况。eq_ref最多只会有一个匹配结果，通常通过主键或唯一键索引访问。从该表中读取一行以与从前一个表中读取的记录连接。与const类型不同，这是唯一的连接类型。当索引的所有部分都用于连接并且索引是PRIMARYKEY或UNIQUE类型时使用它。eq_ref可用于在进行“=”比较时检索字段。比较的值可以是固定值，也可以是表达式，表达式中可以使用表中的字段，在读取表之前已经准备好了。在refJOIN语句中驱动表索引引用的查询。此表中与检索值匹配的所有记录都将被提取并与从前一个表中提取的记录合并。当连接程序使用键的最左前缀或键不是PRIMARYKEY或UNIQUE索引时使用ref（换句话说，连接程序不能根据键值只获得一条记录）。当仅根据键值查询少数匹配记录时，这是一种很好的连接类型。当使用“=”运算符比较搜索字段??时，也可以使用ref。ref_or_null和ref的唯一区别是在使用索引引用的查询之外增加了空值查询。这种连接类型类似于ref，不同的是MySQL在检索时会额外查找包含NULL值的记录。这种连接类型优化从MySQL4.1.1开始可用，并且通常用于子查询。index_merge查询同时使用两个（或多个）索引，然后合并索引结果，然后读取表数据。这种连接类型意味着使用索引合并优化方法。unique_subquery子查询中返回结果字段的组合是主键或唯一约束。index_subquery子查询返回结果字段的组合是一个索引（或索引组合），但不是主键或唯一索引。这种连接类型类似于unique_subquery。它使用子查询而不是IN，但是当子查询中没有唯一索引时使用它。range索引范围扫描。只会获取给定范围内的记录，使用索引获取记录。索引全索引扫描。连接类型与ALL相同，只是它只扫描索引树。它通常比ALL快，因为索引文件通常比数据文件小。在被查询的字段是单独索引的一部分的情况下，MySQL使用此连接类型。fulltext全文索引扫描。all全表扫描。possible_keys字段是指MySQL在查找表记录时可能使用的索引。如果没有索引可用，则显示null。键查询优化器从possible_keys中选择要使用的索引。key字段显示了MySQL将实际使用的索引。当没有使用索引时，该字段的值为NULL。选择key_len以使用索引的索引键长度。key_len字段显示了MySQL使用的索引的长度。当key字段的值为NULL时，索引的长度为NULL。引用列表由常量或表的字段过滤。ref字段显示哪些字段或常量用于结合键从表中查询和记录。rows该字段显示查询优化器根据系统收集的统计信息估计的结果集记录数。Extra该字段显示查询中有关MySQL的附加信息。filteredcolumntype是MySQL5.1新加入的，只有在使用EXPLAINEXTENDED时才会出现。它显示了对表中满足特定条件（WHERE子句或连接条件）的记录百分比的悲观估计。3）SQL重写EXPLAIN除了可以显示执行计划外，还可以显示SQL重写。所谓SQL重写，就是MySQL在对SQL语句进行优化之前，会根据一些原则对语句进行重写，以便后续的优化器进行优化，生成更好的执行计划。该函数与EXPLAINEXTENDED+SHOWWARNINGS结合使用。让我们用一个例子来说明。从上面的例子可以看出，原来语句中的IN子查询被改写成了一种表间关联的方式。2.统计信息查看统计信息也是优化报表必不可少的一步。统计信息提供了对象存储方式的快速概览。下面介绍主要的两类统计——表、索引。1）表统计—SHOWTABLESTATUSName：表名Engine：表存储引擎类型（ISAM、MyISAM或InnoDB）Row_format：行存储格式（Fixed-fixed、Dynamic-dynamic或Compressed-compressed）Rows：行数。在某些存储引擎中，例如MyISAM和ISAM，它们存储确切数量的记录。然而，在其他存储引擎中，它可能只是一个近似值。Avg_row_length：平均行长度。data_length：数据文件的长度。Max_data_length：数据文件的最大长度。Index_length：索引文件的长度。Data_free：已分配但未使用的字节数。auto_increment：下一个自增（自动加1）的值。Create_time：创建表的时间。update_time：数据文件的最新更新时间。Check_time：***对表进行检查的时间。执行mysqlcheck命令后更新，只对MyISAM有效。Create_options：CREATETABLE的附加选项。Comment：创建表时使用的注释（或者一些关于为什么MySQL不能访问表信息的信息）。版本：数据表的'.frm'文件版本号。Collat??ion：表的字符集和校正字符集。校验和：实时校验和值（如果有）。3.索引统计——SHOWINDEX表：表名。Non_unique：0，如果索引不能包含重复项。Key_name：索引名Seq_in_index：列在索引中的序号，从1开始。Column_name：列名。排序规则：列在索引中的排序方式。在MySQL中，这可以具有值A（升序）或NULL（无排序）。Cardinality：索引中唯一值的个数。sub_part：如果列只是部分索引，则索引字符数。当整个字段被索引时，它的值为NULL。Packed：表示键值如何压缩，NULL表示不压缩。Null：当字段包含NULL记录为YES时，其值为，否则为''。Index_type：使用哪种索引算法（BTREE、FULLTEXT、HASH、RTREE）。评论：备注。系统参数：系统参数也会影响语句的执行效率。要查看系统参数，请使用SHOWVARIABLES命令。1)参数说明系统参数较多，下面介绍其中的几个。sort_buffer_size排序区大小。它的大小直接影响用于排序的算法。如果系统中排序比较大，内存足够，并发量不是很大，可以适当增加这个参数。此参数适用于单个Thead。join_buffer_sizeJoin操作使用内存区域大小。JoinBuffer仅在Join为ALL、index、range或index_merge时使用。如果join语句较多，可以适当增加join_buffer_size。需要注意的是，这个值是针对单个Thread的。每个Thread都会创建自己独立的Buffer，而不是整个系统共享的Buffer。不要设置太大导致系统内存不足。tmp_table_size如果内存中的临时表超过这个值，MySQL会自动将其转换为硬盘上的MyISAM表。如果执行许多高级GROUPBY查询并且内存很大，则可以增加tmp_table_size的值。read_buffer_size读取查询操作可以使用的缓冲区大小。此参数适用于单个Thead。4.优化器开关在MySQL中，有一些参数可以用来控制优化器的行为。1）参数说明optimizer_search_depth该参数控制优化器在穷举执行计划时的限制。如果查询长时间处于“统计”状态，可以考虑调低这个参数。optimizer_prune_level默认是开启的，它允许优化器根据需要扫描的行数来决定是否跳过一些执行计划。optimizer_switch变量包含启用/禁用优化器功能的标志。示例—干预优化器行为（ICP功能）默认情况下，ICP功能处于打开状态。看看优化器的行为。基于二级索引的过滤查询使用了ICP特性，从Extra中的“使用索引条件”可以看出。如果优化器行为受到优化器开关的干预怎么办？从Extra可以看出，ICP特性已经被禁用。5、系统状态（SHOWSTATUS）MySQL中也有一些内置的状态。这些状态变量还可以反映语句执行的一些情况，便于定位问题。如果手动执行，可以在语句执行前后执行SHOWSTATUS命令查看状态变化。当然，因为状态变量比较多，不方便比较。我后面介绍的这个小工具可以解决这个问题。1）状态变量状态变量有很多，这里列举几个。Sort_merge_passes排序算法执行的合并次数。如果这个变量的值很大，可以考虑增加sort_buffer_size系统变量的值。Sort_range对范围执行的排序次数。Sort_rows已排序的行数。Sort_scan扫描表完成的排序数。读取Handler_read_first索引中第一个条目的次数。读取索引头的次数。如果这个值很高，说明全索引扫描次数多。Handler_read_key按键读取一行的请求数。如果它很高，则查询和表的索引正确。handler_read_next按key顺序读取下一行的请求数。如果您查询具有范围约束的索引列或执行索引扫描，则此值会增加。handler_read_prev按key顺序读取上一行的请求数。handler_read_rnd根据固定位置读取一行的请求数。如果您执行大量查询并需要对结果进行排序，则此值会更高。那么您可能正在使用大量需要MySQL扫描整个表的查询，或者连接没有正确使用键。Handler_read_rnd_next读取数据文件中下一行的请求数。如果正在进行大量表扫描，则此值会更高。通常表示表索引不正确或者查询是在没有使用索引的情况下编写的。6.SQLPerformanceAnalyzer(QueryProfiler)MySQL的QueryProfiler是一个非常方便的Query诊断分析工具，通过它可以得到一个Query在整个执行过程中各种资源的消耗情况，如CPU、IO、IPC、SWAP、等，以及发生的PAGEFAULTS、CONTEXTSWITCHE等，同时在源文件中可以得到MySQL在Query执行过程中调用的各个函数的位置。1）使用方法启用mysql>select@@profiling；mysql>setprofiling=1;默认情况下profiling值为0，表示MySQLSQLProfiler关闭，启用SQL性能分析器后profiling值为1。执行SQL语句mysql>selectcount(*)fromt1;获取概要信息使用“showprofile”命令获取当前系统中保存的多个Queryprofile的概要信息。mysql>showprofiles;+------------+------------+----------------------+|Query_ID|Duration|Query|+----------+------------+--------------------+|1|0.00039300|selectcount(*)fromt1|+--------+------------+---------------------+获取单个查询的详细配置文件信息。获取摘要信息后，可以根据摘要信息的Query_ID获取查询执行过程中的详细profile信息。档案信息。mysql>showprofileforquery1;mysql>showprofilecpu,blockioforquery1;2.工具说明上面提到的各种方法对SQL语句的调优很有帮助。通过下面这个小工具，可以自动调用命令将以上内容一次性推送给DBA，大大加快优化进程。1、准备条件模块-MySQLDB模块-sqlparsePython版本=2.7.3（2.6.x版本应该没问题，3.x版本没测试过）2、调用方法pythonmysql_tuning.py-ptuning_sql.ini-s'selectxxx'1)参数说明-p指定配置文件名-s指定SQL语句3.配置文件分为两段信息，分别是描述数据库连接信息的[database]和[option]运行配置信息。1)[database]server_ip=127.0.0.1db_user=testuserdb_pwd=testpwddb_name=test2)[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）执行计划是调用explainextended的输出结果。如果结果太长，可能是串口显示有问题（暂未解决）。6）优化器重写的SQL可以判断优化器是否对SQL进行了优化（比如子查询的处理）。7）统计信息这里会显示SQL语句涉及到的所有表及其索引的统计信息。8）运行状态信息在session级别比较执行前后的状态（SHOWSTATUS），显示变化的部分。需要注意的是，由于状态数据是使用SELECT方式采集的，所以会导致个别指标（如Com_select）出错。9)PROFILE详细信息调用SHOWPROFILE得到的详细信息。10）PROFILE汇总信息根据PROFILE的资源消耗，展示不同阶段（TOPN）的消耗对比，直观展示“瓶颈”。
