ANALYZE TABLE

用来分析和存储表的关键字的分布，使得系统获得准确的统计信息，影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表，是不需要经常进行表分析的。但是如果表的数据量变化很明显，用户感觉实际的执行计划和预期的执行计划不同的时候，执行一次表分析可能有助于产生预期的执行计划。

表分析的语法是：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

这个命令对 MyISAM，BDB，InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表，这个命令的执行效果与 myisamchk –a 的执行效果相当。

CHECK TABLE

的作用是检查表或者视图是否存在错误，对 MyISAM 和 InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表进行表检查，也会同时更新关键字统计数据。

表检查的语法是：

CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

CHECKSUM TABLE

数据在传输时可能会发生变化，也有可能因为其他原因损坏，为了保证数据的一致，我们可以计算校验值

用法：CHECKSUM TABLE tbl_name [, tbl_name] ...[QUICK|EXTENDED ]

QUICK表示返回存储的checksum值，而checksum会重新计算checksum值，如果没有指定选项，默认是extended

OPTIMIZE TABLE

语法：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果你的MySQL是有备库的，如果你只希望在主库上执行的话，那么可以加上关键字NO_WRITE_TO_BINLOG(或者LOCAL)。

作用：删除表的一大部分数据后，或者对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改，使用Optimize后能帮我们回收更多的磁盘空间、减少“碎片” (defragment)。

原理：当你删除数据时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺点，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频烦的表，要定期进行optimize，帮我们回收更多的空间、减少“碎片” (defragment)。一个月一次，看实际情况而定了。

适用的的存储引擎：MyISAM, InnoDB, BDB

对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。

对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间，回收主键索引空间。

适用场景：磁盘耗尽、或者InnoDB Tablespaces用完的情况。这时候，在考虑扩容等方案之前，最好先使用Optimize Table试试。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

EXPLAIN TABLE

id

本次 select 的标识符。在查询中每个 select都有一个顺序的数值。

select_type

select 的类型，可能会有以下几种：

simple: 简单的 select (没有使用 union或子查询)

primary: 最外层的 select。

union: 第二层，在select 之后使用了 union。

dependent union: union 语句中的第二个select，依赖于外部子查询

subquery: 子查询中的第一个 select

dependent subquery: 子查询中的第一个 subquery依赖于外部的子查询

derived: 派生表 select(from子句中的子查询)

table

记录查询引用的表。

type

表连接类型。以下列出了各种不同类型的表连接，依次是从最好的到最差的：

system:表只有一行记录(等于系统表)。这是 const表连接类型的一个特例。

const:表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个 恒定值。const表查询起来非常快，因为只要读取一次！const 用于在和 primary key 或unique 索引中有固定值比较的情形。下面的几个查询中，tbl_name 就是 c表了：

select * from tbl_name where primary_key=1; select * from tbl_namewhere primary_key_part1=1 and primary_key_part2=2;

eq_ref:从该表中会有一行记录被读取出来和从上一个表中读取出来的记录做联合。与const类型不同的是，这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个primary key 或 unique 类型。eq_ref可以用于在进行"="做比较时检索字段。比较的值可以是固定值或者是表达式，表达示中可以使用表里的字段，它们在读表之前已经准备好 了。以下的几个例子中，mysql使用了eq_ref 连接来处理 ref_table：

select * from ref_table,other_table where ref_table.key_column=other_table.column;

select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;

ref: 该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引(换句话说，就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。 ref还可以用于检索字段使用 =操作符来比较的时候。以下的几个例子中，mysql将使用 ref 来处理ref_table：

select * from ref_table where key_column=expr; select * fromref_table,other_table whereref_table.key_column=other_table.column; select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;

ref_or_null: 这种连接类型类似 ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录。这种连接类型的优化是从mysql4.1.1开始的，它经常用于子查询。在以下的例子中，mysql使用ref_or_null 类型来处理 ref_table：

select * from ref_table where key_column=expr or key_column is null;

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。range用于将某个字段和一个定值用以下任何操作符比较时 =, <>, >,>=, , between, 或 in：

select * from tbl_name where key_column = 10;

select * fromtbl_name where key_column between 10 and 20;

select * from tbl_namewhere key_column in (10,20,30);

select * from tbl_name wherekey_part1= 10 and key_part2 in (10,20,30);

index: 连接类型跟 all 一样，不同的是它只扫描索引树。它通常会比 all快点，因为索引文件通常比数据文件小。mysql在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。

all: 将对该表做全部扫描以和从前一个表中取得的记录作联合。这时候如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免all。

possible_keys

表示查询时可以使用的索引列

key

表示使用的索引。当没有任何索引被用到的时候，这个字段的值就是null。想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index,或 ignore index。如果是 myisam 和 bdb 类型表，可以使用 analyze table 来帮助分析使用使用哪个索引更好。如果是 myisam类型表，运行命令 myisamchk --analyze也是一样的效果。

key_len

索引长度。当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。

ref

ref 字段显示了哪些字段或者常量被用来和 key配合从表中查询记录出来。

rows

查询中检索的记录数。

extra

本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释：

distinct:mysql当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。

not exists:mysql在查询时做一个 left join优化时，当它在当前表中找到了和前一条记录符合 left join条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：

select * from t1 left join t2 on t1.id=t2.id where t2.id isnull;

假使 t2.id 定义为 not null。这种情况下，mysql将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在 t2中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是null，就不会再在 t2 中查找相同 id值的其他记录了。也可以这么说，对于 t1 中的每个记录，mysql只需要在t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。

range checked for each record (index map: #)

mysql没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引(如果有的话)，并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。

using filesort: mysql需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合 where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。

using index：

字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。

using temporary: mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了groupby 和 order by 子句，它以不同的方式列出了各个字段。

using where：

where子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录，否则的话当查询的extra 字段值不是 using where 并且表连接类型是 all 或 index时可能表示有问题。

如果你想要让查询尽可能的快，那么就应该注意 extra 字段的值为usingfilesort 和 using temporary 的情况。

你可以通过 explain 的结果中 rows字段的值的乘积大概地知道本次连接表现如何。它可以粗略地告诉我们mysql在查询过程中会查询多少条记录。如果是使用系统变量 max_join_size 来取得查询结果，这个乘积还可以用来确定会执行哪些多表select 语句。

阅读(475) | 评论(0) | 转发(0) |