optimization

1.explain结果 id : select识别符 select_type : select类型 simple 简单select PRIMARY 最外面的SELECT UNION UNION中的第二个或后面的SELECT语句 DEPENDENT UNION UNION中的第二个或后面的SELECT语句，取决于外面的查询 UNION RESULT UNION的结果 SUBQUERY 子查询中的第一个SELECT DEPENDENT SUBQUERY 子查询中的第一个SELECT，取决于外面的查询 DERIVED 导出表的SELECT(FROM子句的子查询) table 输出的行所引用的表 type 联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序： system 表仅有一行(=系统表)。这是const联接类型的一个特例。 const 表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。 eq_ref 对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。 ref_or_null 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。 unique_subquery 该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。 index_subquery 该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr) range 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range index 该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。 ALL 对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。 possible_keys 指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。如果该列是NULL，则没有相关的索引。 key key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX key_len key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。 ref ref列显示使用哪个列或常数与key一起从表中选择行 rows rows列显示MySQL认为它执行查询时必须检查的行数。 Extra 该列包含MySQL解决查询的详细信息: Distinct MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。 Not exists MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。 range checked for each record MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用.对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。 Using filesort MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。 Using index 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。 Using temporary 为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。 Using where WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。 Using sort_union(...), Using union(...), Using intersect(...) 这些函数说明如何为index_merge联接类型合并索引扫描 Using index for group-by 类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。 MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引,在join时where两边的键类型及长度时查询更高效 2.估计查询性能 在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表，可以使用B－树索引进行估计，将需要log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1次搜索才能找到行。 在MySQL中，索引块通常是1024个字节，数据指针通常是4个字节，这对于有一个长度为3(中等整数)的索引的500,000行的表，通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。 当表格变得更大时，所有内容缓存到OS或SQL服务器后，将仅仅或多或少地更慢。在数据变得太大不能缓存后，将逐渐变得更慢，直到应用程序只能进行磁盘搜索(以logN增加)。为了避免这个问题，随数据增加而增加 键高速缓冲区大小 3.mysql的优化 去除不必要的括号，常量重叠，去除常量条件，索引使用的常数表达式仅计算一次 无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。 如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。 对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。 所有常数的表在查询中比其它表先读出。常数表为： 空表或只有1行的表。 与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。 尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。 如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。 如果使用SQL_SMALL_RESULT，MySQL使用内存中的一个临时表。 每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。 在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。 输出每个记录前，跳过不匹配HAVING子句的行。 1）单元素索引的范围访问方法 对于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应一个范围条件。 对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条件。 对于所有类型的索引，多个范围条件结合OR或AND则产生一个范围条件。 2）多元素索引的范围访问方法 多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序，通过一系列关键元组来定义关键元组区间。 3）索引合并优化 索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。 索引合并交集访问算法：该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件(where key1 = '' and key2 = '') 索引合并并集访问算法：该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候 (where key1 = '' or key2 = '') 索引合并排序并集访问算法：该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件，但索引合并方法联合算法并不适用的时候。SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20; 排序联合算法和联合算法的区别是排序联合算法必须先索取所有记录的行ID，然后在返回记录前对它们进行排序。 4）mysql优化is null MySQL可以对可以结合col_name = constant_value使用的col_name IS NULL进行相同的优化. 如果WHERE子句包括声明为NOT NULL的列的col_name IS NULL条件，表达式则优化。当列会产生NULL时，不会进行优化； 5) mysql优化distinct 结合LIMIT row_count和DISTINCT后，MySQL发现唯一的row_count行后立即停止 6) mysql优化left join , right join 7) mysql优化order by 在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。 即使ORDER BY不确切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数，就可以使用索引 在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行 对不同的关键字使用ORDER BY ：SELECT * FROM t1 ORDER BY key1, key2； 对关键字的非连续元素使用ORDER BY ：SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2； 混合ASC和DESC： SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； 用于查询行的关键字与ORDER BY中所使用的不相同：SELECT * FROM t1 WHERE key2=constant ORDER BY key1； 你正联接许多表，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表。(这是EXPLAIN输出中的没有const联接类型的第1个表） 有不同的ORDER BY和GROUP BY表达式 使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索引情况即如此 文件排序优化不仅用于记录排序关键字和行的位置，并且还记录查询需要的列。这样可以避免两次读取行 为了避免速度变慢，该优化只用于排序元组中的extra列的总大小不超过max_length_for_sort_data系统变量值的时候 如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试下面的策略： 增加sort_buffer_size变量的大小 增加read_rnd_buffer_size变量的大小 更改tmpdir指向具有大量空闲空间的专用文件系统 默认情况下，MySQL排序所有GROUP BY col1，col2，...查询的方法如同在查询中指定ORDER BY col1，col2，...。 如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。 如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序 8) mysql优化group by 满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如果有)。 在某些情况中，MySQL能够做得更好，通过索引访问而不用创建临时表。 为GROUP BY使用索引的最重要的前提条件是 所有GROUP BY列引用同一索引的属性，并且索引按顺序保存其关键字(例如，这是B-树索引，而不是HASH索引) 是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件，以及选择的累积函数 松散索引扫描 使用索引时最有效的途径是直接搜索组域。通过该访问方法，MySQL使用某些关键字排序的索引类型(例如，B-树)的属性。该属性允许使用 索引中的查找组而不需要考虑满足所有WHERE条件的索引中的所有关键字。既然该访问方法只考虑索引中的关键字的一小部分，它被称为松散索引扫描 紧凑索引扫描 紧凑式索引扫描可以为索引扫描或一个范围索引扫描，取决于查询条件。 9) mysql优化limit 如果你用LIMIT只选择一些行，当MySQL选择做完整的表扫描时，它将在一些情况下使用索引 如果你使用LIMIT row_count与ORDER BY，MySQL一旦找到了排序结果的第一个row_count行，将结束排序而不是排序整个表.一旦找到了行，则不需要再排序结果的其它部分，并且MySQL不再进行排序。 当结合LIMIT row_count和DISTINCT时，MySQL一旦找到row_count个唯一的行，它将停止。 在一些情况下，GROUP BY能通过顺序读取键(或在键上做排序)来解决，然后计算摘要直到关键字的值改变。在这种情况下，LIMIT row_count将不计算任何不必要的GROUP BY值 只要MySQL已经发送了需要的行数到客户，它将放弃查询，除非你正使用SQL_CALC_FOUND_ROWS 4. INSERT语句的速度 插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例： 连接：(3),发送查询给服务器：(2),分析查询：(2),插入记录：（1x记录大小）,插入索引：（1x索引）,关闭：(1) 表的大小以logN (B树)的速度减慢索引的插入。 加快插入的一些方法： 如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，可以调节bulk_insert_buffer_size变量，使数据插入更快。 如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度 用MyISAM，如果在表中没有删除的行，能在SELECT语句正在运行的同时插入行。 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍 5. UPDATE语句的速度 更新查询的优化同SELECT查询一样，需要额外的写开销。写速度依赖于更新的数据大小和更新的索引的数量。没有更改的索引不被更新。 使更改更快的另一个方法是推迟更改然后在一行内进行多次更新。如果锁定表，同时做多个更新比一次做一个快得多。 6.DELETE语句的速度 删除一个记录的时间与索引数量确切成正比。为了更快速地删除记录，可以增加键高速缓冲的大小。 如果想要删除一个表的所有行，使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name 7.其它优化技巧 使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接，可能要更改thread_cache_size变量的值 总是检查所有查询确实使用已经在表中创建了的索引。 尝试避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题 对于没有删除的行的MyISAM表，可以在另一个查询正从表中读取的同时在末尾插入行。如果这很重要，应考虑按照避免删除行的方式使用表。另一个可能性是在删除大量行后运行OPTIMIZE TABLE 对于频繁更改的MyISAM表，应试图避免所有变长列(VARCHAR、BLOB和TEXT)。如果表包括单一的变长列则使用动态记录格式 只是因为行太大，将一张表分割为不同的表一般没有什么用处。为了访问行，最大的性能冲击是磁盘搜索以找到行的第一个字节。在找到数据后，大多数新型磁盘对大多数应用程序来说足够快，能读入整个行。 如果你需要很经常地计算结果，例如基于来自很多行的信息的计数，引入一个新表并实时更新计数器可能更好一些。 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。 在一般情况下，应该尝试以非冗余方式(查看数据库理论中的第三正则形式)保存数据，但是为了获得更快的速度，可以复制信息或创建总结表。 存储过程或UDF(用户定义函数)可能是获得更好性能的一个好方法 总是能通过在应用程序中缓存查询/答案并尝试同时执行很多插入/更新来获得一些好处。如果数据库支持锁定表(象MySQL和Oracle)，这应该有助于确保索引缓存只在所有更新后刷新一次。还可以利用MySQL的查询缓存来获得类似的结果 当不需要知道何时写入数据时，使用INSERT DELAYED。这样可以加快处理，因为很多记录可以通过一次磁盘写入被写入。 使用多行INSERT语句通过一个SQL命令来存储很多行 使用LOAD DATA INFILE装载较大数量的数据。这比使用INSERT要快得多 使用AUTO_INCREMENT列构成唯一值 当MyISAM使用动态表格式时，偶尔使用OPTIMIZE TABLE可以避免碎片。 在Web服务器中，图象和其它二进制资产应该作为文件存储。也就是仅在数据库中存储的本文件的引用而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多，因此使用文件一般要快得多。 如果正使用数字数据，在许多情况下，从一个数据库访问信息(使用实时连接)比访问一个文本文件快些。这是因为数据库中的信息比文本文件更紧凑，因此这将涉及更少的磁盘访问。还可以在应用程序中节省代码，因为不须分析文本文件来找出行和列的边界。 通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份时主服务器变慢，可以使用一个从服务器来备份。