优化SQL，是DBA的常见工作之一。如何高效快速的优化一条语句是每个DBA经常面临的问题。在日常的优化工作中，我发现很多操作都是优化过程中必不可少的步骤。但是，这些步骤的重复执行会消耗DBA大量的精力。今天抽空总结了一些mysql数据库常用的优化步骤，仅供参考。执行计划——EXPLAIN命令的执行计划是语句优化的主要入口点。通过对执行计划的解释，可以了解语句的执行过程。在执行计划生成方面，MySQL与Oracle有明显的不同。它不缓存执行计划，每次都进行“硬解析”。1.基本用法EXPLAINQUERY在Select语句前使用关键字EXPLAIN时，MySQL会解释Select语句是如何运行的，显示表是如何连接的，连接的顺序等信息。EXPLAINEEXTENDEDQUERY当使用EXTENDED关键字时，EXPLAIN会生成额外的信息，这些信息可以通过SHOWWARNINGS浏览。此信息显示优化器在SELECT语句中限定的表名和列名，重写和执行优化规则后SELECT语句的样子，还可能包括有关优化过程的其他注释。EXPLAINPARTITIONSQUERY显示查询将访问哪些数据片段——如果有的话。只能用于MySQL5.1及以上版本。EXPLAINFORMAT=JSON(newin5.6)另一种显示执行计划的格式。您可以查看表之间的关联方式等信息。2.输出字段EXPLAIN输出的字段含义解释如下：(1)idMySQL选择的执行计划中查询的序号。如果语句中没有子查询等，那么整个输出就只有一个SELECT，这样每一行都会在这一列显示一个1。如果语句中使用了子查询、集合操作、临时表等，都会给ID列带来很大的复杂度。(2)select_type语句使用的查询类型。它是一个简单的SELECT还是一个复杂的SELECT（如果是后者，它是哪种复杂类型）。通常使用以下类型的标记。DEPENDENTSUBQUERY：子查询内层的第一个SELECT依赖于外层查询的结果集。DEPENDENTUNION：子查询中的UNION，以及从UNION中的第二个SELECT开始的所有后续SELECT也依赖于外层查询的结果集。PRIMARY：子查询中的最外层查询，注意不是主键查询。SIMPLE：不同于子查询或UNION的查询。SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外层查询的结果集。UNCACHEABLESUBQUERY：其结果集不能被缓存的子查询。UNION：UNION语句中的第二个SELECT开始所有后续的SELECT，第一个SELECT是PRIMARY。UNIONRESULT：UNION中的合并结果。从UNION临时表中获取结果的SELECT。DERIVED：派生表查询（FROM子句中的子查询）。MySQL将递归地执行这些子查询并将结果放在临时表中。在内部，服务器将其称为“派生表”，因为临时表是从子查询派生的。(3)table本步骤访问的数据库中的表名或SQL语句指定的别名表。该值可以是表名、表别名或为查询生成的临时表的标识符，例如派生表、子查询或集合。(4)类型表的访问方式。下面按从好到坏的顺序列出了各种类型的表连接。system：系统表，该表只有一行记录。这是const表连接类型的一个特例。const：读取常量，最多只有一条匹配记录。由于只有一行，所以这一行的字段值在优化器中可以看作是一个常量值。const用于与PRIMARYKEY或UNIQUE索引进行比较的固定值的情况。eq_ref：最多会有一个匹配结果，一般通过主键或唯一键索引访问。从该表中读取一行以与从前一个表中读取的记录连接。与const类型不同，这是最好的连接类型。当索引的所有部分都用于连接并且索引是PRIMARYKEY或UNIQUE类型时使用它。eq_ref可用于在进行“=”比较时检索字段。比较的值可以是固定值，也可以是表达式，表达式中可以使用表中的字段，在读取表之前已经准备好了。ref：在JOIN语句中驱动表索引引用的查询。此表中与检索值匹配的所有记录都将被提取并与从前一个表中提取的记录合并。当连接程序使用键的最左前缀或键不是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：全表扫描。(5)possible_keys该字段是指MySQL在查找表记录时可能使用的索引。如果没有索引可用，则显示null。(6)key查询优化器从possible_keys中选择的索引。key字段显示了MySQL将实际使用的索引。当没有使用索引时，该字段的值为NULL。(7)key_len选择使用索引的索引键长度。key_len字段显示了MySQL使用的索引的长度。当key字段的值为NULL时，索引的长度为NULL。(8)reflist由常量或表的字段过滤。ref字段显示哪些字段或常量用于结合键从表中查询和记录。(9)rows该字段显示查询优化器通过系统收集的统计信息估计的结果集记录数。(10)Extra该字段显示MySQL在查询中的附加信息。(11)过滤列类型是MySQL5.1新增的，只有在使用EXPLAINEXTENDED时才会出现。它显示了对表中满足特定条件（WHERE子句或连接条件）的记录百分比的悲观估计。3.SQL改写除了显示执行计划，EXPLAIN还可以显示SQL改写。所谓SQL重写，就是MySQL在对SQL语句进行优化之前，会根据一些原则对语句进行重写，以便后续的优化器进行优化，生成更好的执行计划。该函数与EXPLAINEXTENDED+SHOWWARNINGS结合使用。做一个简单的实验：从上面的例子可以看出，将原来语句中的IN子查询改写成了一种表间关联的方式。2.统计信息查看统计信息也是优化报表必不可少的一步。统计信息提供了对象存储方式的快速概览。下面介绍主要的两类统计——表、索引。1、表统计信息—SHOWTABLESTATUS说明如下：Name：表名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说明如下：Table：表名。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命令。四、优化器开关在MySQL中，有一些参数可以用来控制优化器的行为。1.参数说明optimizer_search_depth：该参数控制穷举执行计划时优化器的极限。如果查询长时间处于“统计”状态，可以考虑调低这个参数。optimizer_prune_level：默认开启，允许优化器根据扫描的行数决定是否跳过某些执行计划。optimizer_switch：此变量包含启用/禁用优化器功能的标志。2.实验——干预优化器行为（ICP特性）默认情况下，ICP特性是开启的。看看优化器的行为。基于二级索引的过滤查询使用了ICP特性，从Extra中的“使用索引条件”可以看出。如果通过优化器开关干预优化器的行为会怎样？setoptimizer_switch="index_condition_pushdown=off";从Extra可以看出ICP特性已经被禁用。5、系统状态（SHOWSTATUS）MySQL也有一些内置的状态，这些状态变量也可以反映一些语句执行的情况，便于定位问题。如果手动执行，可以在语句执行前后执行SHOWSTATUS命令查看状态变化。状态变量：状态变量有很多，这里只介绍几个常用的。Sort_merge_passes：排序算法执行的合并次数。如果这个变量的值很大，可以考虑增加sort_buffer_size系统变量的值。Sort_range：要对该范围执行的排序数。Sort_rows：已排序的行数。sort_scan：扫描表完成的排序数。Handler_read_first：索引中第一个条目被读取的次数。读取索引头的次数。如果这个值很高，说明全索引扫描次数多。handler_read_key：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。2、执行SQL语句mysql>selectcount(*)fromt1;3。获取配置文件信息使用“showprofile”命令获取当前系统中保存的多个Query配置文件的配置文件信息。mysql>显示配置文件；4.获取单次查询的详细剖析信息获取摘要信息后，可以根据摘要信息的Query_ID获取查询执行过程中的详细剖析信息。mysql>showprofileforquery1;mysql>showprofilecpu,blockioforquery1;
