MySQL优化分析

在任何一个数据库上执行SQL，尤其是数据量大的时候，必须要考虑SQL语句的优化、表的优化。本文就MySQL的优化进行学习。

1 EXPLAIN语法

语法结构：

1）Explain tbl_name是DESCRIBE tbl_name或show columns from tbl_name的一个同义词
2）如果在SELECT语句之前放上关键字Explain，MySQL将解释它如何处理SELECT，提供有关表如何连接以及连接的次序。
Explain为用于SELECT语句中的每个表返回一行信息，表以他们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次连接（single-sweep multi-join）的方式解决所有连接，这意味着MySQL从第一个表中读取一行，然后找到第二个表中的一个匹配行，然后在第三个表中再匹配等等。当所有表处理完后，它输出选中的列并返回表清单知道找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。

1.1 示例

例1：单表简单查询

mysql> explain select * from sys_tables where table_name = 'hello';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sys_tables | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

例2：表关联查询

mysql> explain select * from sys_tables t1 , sys_table_cols t2 where t1.table_name = t2.table_name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.04 sec)

例3：表左连接查询

mysql> explain select * from sys_tables t1 left join sys_table_cols t2 on t1.table_name = t2.table_name where t1.table_name = 'hello';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where                                        |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

例4：表右连接查询

mysql> explain select * from sys_tables t1 right join sys_table_cols t2 on t1.table_name = t2.table_name where t1.table_name = 'hello';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

例5：表内连接查询

mysql> explain select * from sys_tables t1 inner join sys_table_cols t2 on t1.table_name = t2.table_name where t1.table_name = 'hello';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

例6：表子查询

mysql> explain select * from sys_tables t1 where exists (select 1 from sys_table_cols t2 where t2.table_name = t1.table_name);
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | t1    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | index | NULL          | PRIMARY | 276     | NULL |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

例7：联合查询

mysql> explain select table_name from sys_tables union select table_name from sys_table_cols ;
+----+--------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | sys_tables     | NULL       | index | NULL          | PRIMARY | 184     | NULL |    2 |   100.00 | Using index     |
|  2 | UNION        | sys_table_cols | NULL       | index | NULL          | PRIMARY | 276     | NULL |    1 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2>   | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+----------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)

mysql>

例8：JSON格式输出

mysql> explain format=json select t.* from (select table_name ,column_name from sys_table_cols) t,
sys_tables t1 where t.table_name = 'hello' and t.table_name = t1.table_name;
+------------------------------------------------------------------------------------------
| EXPLAIN
+------------------------------------------------------------------------------------------
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.60"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "sys_table_cols",
          "access_type": "index",
          "key": "PRIMARY",
          "used_key_parts": [
            "owner",
            "table_name",
            "column_name"
          ],
          "key_length": "276",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "760"
          },
          "used_columns": [
            "table_name",
            "column_name"
          ],
          "attached_condition": "(`sys_manager`.`sys_table_cols`.`table_name` = 'hello')"
        }
      },
      {
        "table": {
          "table_name": "t1",
          "access_type": "index",
          "key": "PRIMARY",
          "used_key_parts": [
            "owner",
            "table_name"
          ],
          "key_length": "184",
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 1,
          "filtered": "50.00",
          "using_index": true,
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "2.60",
            "data_read_per_join": "384"
          },
          "used_columns": [
            "table_name"
          ],
          "attached_condition": "(`sys_manager`.`t1`.`table_name` = `sys_manager`.`sys_table_cols`.`table_name`)"
        }
      }
    ]
  }
} |
+---------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)

mysql>

例9：DERIVED

mysql> explain select t.*,t1.cnt from sys_tables t, (select table_name,count(1) cnt from sys_table_cols group

by table_name) t1 where t.table_name = t1.table_name;
+----+-------------+----------------+------------+-------+---------------+-------------+---------+--------------------------+------+----------+----------------------------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key         | key_len | ref                      | rows | filtered | Extra                                        |
+----+-------------+----------------+------------+-------+---------------+-------------+---------+--------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | t              | NULL       | ALL   | NULL          | NULL        | NULL    | NULL                     |    2 |   100.00 | NULL                                         |
|  1 | PRIMARY     | <derived2>     | NULL       | ref   | <auto_key0>   | <auto_key0> | 92      | sys_manager.t.table_name |    2 |   100.00 | NULL                                         |
|  2 | DERIVED     | sys_table_cols | NULL       | index | PRIMARY       | PRIMARY     | 276     | NULL                     |    1 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+----------------+------------+-------+---------------+-------------+---------+--------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

例10：雾化查询

mysql> EXPLAIN
    -> SELECT *
    ->   FROM EMP
    ->  WHERE EMPNO IN (SELECT OBJECT_ID
    ->                    FROM DAO_OBJECTS1 T1
    ->                   WHERE EXISTS (SELECT  1
    ->                                   FROM DEPT  D
    ->                                  WHERE  T1.OBJECT_ID = D.DEPTNO) );
+----+--------------------+-------------+--------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type        | table       | type   | possible_keys | key     | key_len | ref                   | rows | Extra       |
+----+--------------------+-------------+--------+---------------+---------+---------+-----------------------+------+-------------+
|  1 | PRIMARY            | <subquery2> | ALL    | NULL          | NULL    | NULL    | NULL                  | NULL | Using where |
|  1 | PRIMARY            | EMP         | eq_ref | PRIMARY       | PRIMARY | 4       | <subquery2>.OBJECT_ID |    1 | NULL        |
|  2 | MATERIALIZED       | T1          | ALL    | NULL          | NULL    | NULL    | NULL                  |    1 | Using where |
|  3 | DEPENDENT SUBQUERY | D           | eq_ref | PRIMARY       | PRIMARY | 4       | dao.T1.OBJECT_ID      |    1 | Using index |
+----+--------------------+-------------+--------+---------------+---------+---------+-----------------------+------+-------------+
4 rows in set (0.01 sec)


mysql> EXPLAIN EXTENDED
    -> SELECT *
    ->   FROM EMP
    ->  WHERE EMPNO IN (SELECT T1.OBJECT_ID
    ->                    FROM DAO_OBJECTS1 T1 ,DAO_OBJECTS1 T2
    ->                   WHERE T1.OBJECT_ID =T2.OBJECT_ID
    ->                     AND EXISTS (SELECT  1
    ->                                  FROM DEPT D
    ->                                 WHERE D.DEPTNO= T1.OBJECT_ID )
    ->                      );
+----+--------------------+-------------+--------+---------------+---------+---------+-----------------------+------+----------+----------------------------------------------------+
| id | select_type        | table       | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra                                              |
+----+--------------------+-------------+--------+---------------+---------+---------+-----------------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY            | <subquery2> | ALL    | NULL          | NULL    | NULL    | NULL                  | NULL |     0.00 | Using where                                        |
|  1 | PRIMARY            | EMP         | eq_ref | PRIMARY       | PRIMARY | 4       | <subquery2>.OBJECT_ID |    1 |   100.00 | NULL                                               |
|  2 | MATERIALIZED       | T1          | ALL    | NULL          | NULL    | NULL    | NULL                  |    1 |   100.00 | Using where                                        |
|  2 | MATERIALIZED       | T2          | ALL    | NULL          | NULL    | NULL    | NULL                  |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  3 | DEPENDENT SUBQUERY | D           | eq_ref | PRIMARY       | PRIMARY | 4       | dao.T1.OBJECT_ID      |    1 |   100.00 | Using index                                        |
+----+--------------------+-------------+--------+---------------+---------+---------+-----------------------+------+----------+----------------------------------------------------+
5 rows in set, 2 warnings (0.00 sec)

例11：不可缓存子查询

mysql> explain select (select @rownum:= @rownum + status from sys_tables limit 1) ;
+----+----------------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type          | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+----------------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY              | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
|  2 | UNCACHEABLE SUBQUERY | sys_tables | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL           |
+----+----------------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

例12：不可缓存联合查询

mysql> explain  SELECT      (select xx.c1 from ( SELECT @rownum c1 from EMP  union  SELECT @rownum from EMP  )  xx limit 1 )
    ->     FROM DEPT D  ;
+----+----------------------+------------+-------+---------------+-----------+---------+------+------+-----------------+
| id | select_type          | table      | type  | possible_keys | key       | key_len | ref  | rows | Extra           |
+----+----------------------+------------+-------+---------------+-----------+---------+------+------+-----------------+
|  1 | PRIMARY              | D          | index | NULL          | PRIMARY   | 4       | NULL |    4 | Using index     |
|  2 | UNCACHEABLE SUBQUERY | <derived3> | ALL   | NULL          | NULL      | NULL    | NULL |   28 | NULL            |
|  3 | DERIVED              | EMP        | index | NULL          | FK_DEPTNO | 5       | NULL |   14 | Using index     |
|  4 | UNCACHEABLE UNION    | EMP        | index | NULL          | FK_DEPTNO | 5       | NULL |   14 | Using index     |
| NULL | UNION RESULT         | <union3,4> | ALL   | NULL          | NULL      | NULL    | NULL | NULL | Using temporary |
+----+----------------------+------------+-------+---------------+-----------+---------+------+------+-----------------+
5 rows in set (0.00 sec)

例13：const示例

mysql> create table TYPE_SYSTEM (c1 int primary key ) ;
Query OK, 0 rows affected (0.41 sec)

mysql> insert into TYPE_SYSTEM values(1) ;
Query OK, 1 row affected (0.12 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from type_system;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | type_system | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from type_system where c1 = '1';
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | type_system | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from type_system where c1 = '2';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

1.2 结果分析

1.2.1 id

SELECT识别符，这是SELECT的查询识别号，对应的JSON名称：select_id。在使用UNION语句的时候，这个字段为空。

1.2.2 select_type

类别	描述
SIMPLE	简单的SELECT查询（不适用UNION或子查询）
PRIMARY	最外面的SELECT
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询
DERIVED	导出表的SELECT（FROM语句的子查询），使用内联视图的时候会出现，参考：例9
MATERILIZED	雾化子查询，在出现非相关子查询的时候，会出现这种情况。参考：例10
UNCACHEABLE SUBQUERY	不可缓存子查询，对应JSON中的节点名为：cacheable (false)。参考：例11
UNCACHEABLE UNION	不可缓存关联查询

1.2.3 table

输出的行所引用的表。

类别	描述
<unionM,N>	此数据依赖于ID为M和N的联合结果
<derivedN>	数据引用ID为N的查询结果，衍生表可以是结果，例如，FROM子句中的子查询的结果
<subqueryN>	数据引用ID为N的子查询结果。

1.2.4 partitions

partitions表示查询出的记录所在的分区。只有当使用PARTITIONS关键字的时候，这列才会显示值。非分区索引的显示为空。

英文原文：The partitions from which records would be matched by the query. This column is displayed only if the PARTITIONS keyword is used. The value is NULL for nonpartitioned tables.

详情参考[2]深入解析MySQL分区（Partition）功能中的分区功能介绍。

1.2.5 type

表示数据访问路径，表述表是怎么进行关联的。

type从最优到最糟糕的排序为：null->system->const->eq-ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL

类别	描述
NULL	不查询任何表（eg:select 1 ）或者根据主键查询某张表，但是未查询到记过时会显示为NULL（eg: select * from table_name where key=1）
SYSTEM	要查询的表中只包含一条数据，是CONST的一个特例。（官方是这么写的，但是没见过）
CONST	此种类型，最多只会返回一条记录，所以优化器会把它当做常量。可以认为，表进行了一次索引扫描，并且唯一索引只返回一条记录。当你按照主键或者唯一索引查询这张表的时候会出现此情况。 eg: SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
EQ-REF	表与表之间按照主键或者唯一索引进行关联，它是最可能用到的一种关联方式，性能方面也不错。并且rq-ref是用=来关联两个表的字段
REF	索引非唯一扫描，即一个表的主键或索引是联合主键、索引，只根据其中的一个字段（且为第一个字段）进行查找，就显示为REF。 eg: SELECT * FROM ref_table WHERE key_column=expr; 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;
FULLTEXT	使用全文检索的时候出现 eg: mysql> ALTER TABLE DAO_OBJECTS2 ADD FULLTEXT( OBJECT_NAME ); Query OK, 0 rows affected, 1 warning (2.15 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> EXPLAIN -> SELECT * -> FROM DAO_OBJECTS2 -> WHERE match(OBJECT_NAME) AGAINST('TABLES')
REF_OR_NULL	该链接类型类似于REF，但是添加了MYSQL中专门搜索NULL的语句，在子查询中经常使用此优化： eg: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS 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： SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);
INDEX	该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。
ALL	对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

1.2.6 possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

为了看清一张表有什么索引，使用SHOW INDEX FROM tbl_name。

1.2.7 key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

对于MyISAM和BDB表，运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表，可以使用myisamchk --analyze。

1.2.8 key-len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

key_len 计算公式为
可变长度*字符集位数+(如果可为null+1 否则+0)+2 声明变长字段
固定字段 int 4 +(not null +1 else 0 )

eg:

mysql> EXPLAIN SELECT * FROM DAO_OBJECTS1 WHERE OBJECT_NAME = 'DBA_TABLES' ;
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-----------------------+
| id | select_type | table        | type | possible_keys | key           | key_len | ref   | rows | Extra                 |
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | DAO_OBJECTS1 | ref  | IDX2_OBJECTS2 | IDX2_OBJECTS2 | 131     | const |    2 | Using index condition |
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

 CREATE TABLE `DAO_OBJECTS1` (
  `OWNER` varchar(30) NOT NULL,
  `OBJECT_NAME` varchar(128) DEFAULT NULL,
  `SUBOBJECT_NAME` varchar(30) DEFAULT NULL,
  `OBJECT_ID` int(11) NOT NULL DEFAULT '0',
  `DATA_OBJECT_ID` int(11) DEFAULT NULL,
  `OBJECT_TYPE` varchar(19) DEFAULT NULL,
  `CREATED` datetime DEFAULT NULL,
  `LAST_DDL_TIME` datetime DEFAULT NULL,
  `TIMESTAMP` varchar(19) DEFAULT NULL,
  `STATUS` varchar(7) DEFAULT NULL,
  `TEMPORARY` varchar(1) DEFAULT NULL,
  `GENERATED` varchar(1) DEFAULT NULL,
  `SECONDARY` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`OBJECT_ID`),
  KEY `IDX2_OBJECTS2` (`OBJECT_NAME`),
  KEY `IDX3_OBJECTS1` (`OWNER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

key_len=128+1(null)+2(var变长)


mysql> EXPLAIN SELECT * FROM DAO_OBJECTS1 WHERE OBJECT_ID=9527 ;
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | DAO_OBJECTS1 | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

key_len=4(int)+0(primary key not null 0 ) +0 (固定长度)

1.2.9 ref

ref列展示使用key列中的索引或主键中的那些检索字段或方式从表中搜索数据。

官方描述：

The ref column shows which columns or constants are compared to the index named in the key
column to select rows from the table.
If the value is func, the value used is the result of some function. To see which function, use EXPLAIN
EXTENDED followed by SHOW WARNINGS. The function might actually be an operator such as an
arithmetic operator.

1.2.10 rows

rows列显示MySQL认为它执行查询时必须检查的行数。

如果是驱动行源则为该行源大小如果是被驱动行源,则是单次操作的行源大小。

mysql> EXPLAIN SELECT COUNT(*) FROM DAO_OBJECTS1 T1 ,DAO_OBJECTS2 T2 WHERE T1.OBJECT_ID=T2.OBJECT_ID
    -> ;
+----+-------------+-------+--------+---------------+---------+---------+------------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+-------+-------------+
|  1 | SIMPLE      | T2    | index  | PRIMARY       | PRIMARY | 4       | NULL             | 50887 | Using index |
|  1 | SIMPLE      | T1    | eq_ref | PRIMARY       | PRIMARY | 4       | dao.T2.OBJECT_ID |     1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------------+-------+-------------+
2 rows in set (0.00 sec)

第一行 row = 50887 为T2表总大小
第二行 row = 1     代表从T2表取一行 到T1表中遍历可获取的行数的大小

1.2.11 filtered

filtered表示根据table的where条件过滤的记录数所占百分比。换而言之，row描述的是

1.2.12 extra

该列包含MySQL解决查询的详细信息。

在优化过程中，要特别留意此列是否有“ Using filesort “ 或 ” Using temporary”，这两个属性涉及到排序、使用临时空间。所以在优化时尽可能避免这种情况出现。详情参看下面的描述：

类别	描述
Distinct	MySQL发现第一个匹配行之后，停止为当前组合搜索更多的行。
Not Exists	MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。下面是一个可以这样优化的查询类型的例子： SELECT * from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL；假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。
range checked for each record (index map: #)	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查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。

2 SQL性能评估

2.1 估计查询性能

在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在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次搜索。

上面的索引需要大约500,000 * 7 * 3/2 = 5.2MB，(假设典型情况下索引缓存区填充率为2/3)，可以将大部分索引保存在内存中，仅需要1-2调用从OS读数据来找出行。

然而对于写，将需要4次搜索请求(如上)来找到在哪儿存放新索引，并且通常需要2次搜索来更新这个索引并且写入行。

注意，上述讨论并不意味着应用程序的性能将缓慢地以logN 退化！当表格变得更大时，所有内容缓存到OS或SQL服务器后，将仅仅或多或少地更慢。在数据变得太大不能缓存后，将逐渐变得更慢，直到应用程序只能进行磁盘搜索(以logN增加)。为了避免这个问题，随数据增加而增加键高速缓冲区大小。对于MyISAM表, 由key_buffer_size系统变量控制键高速缓冲区大小。

注：摘自官方文档。

2.2 SELECT查询的速度

总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。

下面是一些加速对MyISAM表的查询的一般建议：

· 为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZE TABLE或myisamchk --analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk --description --verbose可以显示索引分布信息。

· 要想根据一个索引排序一个索引和数据，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！

2 优化方法示例分析

2.1 优化方法

注：该章节摘自官方文档，做记录使用。

2.1.1 Where优化示例

该节讨论为处理WHERE子句而进行的优化。例子中使用了SELECT语句，但相同的优化也适用DELETE和UPDATE语句中的WHERE子句。

请注意对MySQL优化器的工作在不断进行中，因此该节并不完善。MySQL执行了大量的优化，本文中所列的并不详尽。

下面列出了MySQL执行的部分优化：

· 去除不必要的括号：

·                        ((a AND b) AND c OR (((a AND b) AND (c AND d))))

·                -> (a AND b AND c) OR (a AND b AND c AND d)

· 常量重叠：

·                   (a<b AND b=c) AND a=5

·                -> b>5 AND b=c AND a=5

· 去除常量条件(由于常量重叠需要)：

·                   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)

·                -> B=5 OR B=6

· 索引使用的常数表达式仅计算一次。

对于MyISAM和HEAP表，在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。
无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。
对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。
所有常数的表在查询中比其它表先读出。常数表为：
- 空表或只有1行的表。
- 与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。

下列的所有表用作常数表：

mysql> SELECT * FROM t WHERE primary_key=1;

mysql> SELECT * FROM t1,t2

           WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。
如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。
如果使用SQL_SMALL_RESULT，MySQL使用内存中的一个临时表。
每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。
在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。
输出每个记录前，跳过不匹配HAVING子句的行。

下面是一些快速查询的例子：

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name

    WHERE key_part1=constant;

SELECT ... FROM tbl_name

    ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name

    ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

下列查询仅使用索引树就可以解决(假设索引的列为数值型)：

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name

    WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

下列查询使用索引按排序顺序检索行，不用另外的排序：

SELECT ... FROM tbl_name

    ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name

    ORDER BY key_part1 DESC, key_part2 DESC, ... ;

2.1.2 范围优化

range访问方法使用单一索引来搜索包含在一个或几个索引值距离内的表记录的子集。可以用于单部分或多元素索引。后面的章节将详细描述如何从WHERE子句提取区间。

2.1.2.1 单元素索引的范围访问方法

对于单元素索引，可以用WHERE子句中的相应条件很方便地表示索引值区间，因此我们称为范围条件而不是“区间”。

单元素索引范围条件的定义如下：

· 对于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应一个范围条件。

· 对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条件。

· 对于所有类型的索引，多个范围条件结合OR或AND则产生一个范围条件。

前面描述的“常量值”系指：

· 查询字符串中的常量

· 同一联接中的const或system表中的列

· 无关联子查询的结果

· 完全从前面类型的子表达式组成的表达式

下面是一些WHERE子句中有范围条件的查询的例子：

SELECT * FROM t1

    WHERE key_col > 1

    AND key_col < 10;

SELECT * FROM t1

    WHERE key_col = 1

    OR key_col IN (15,18,20);

SELECT * FROM t1

    WHERE key_col LIKE 'ab%'

    OR key_col BETWEEN 'bar' AND 'foo';

请注意在常量传播阶段部分非常量值可以转换为常数。

MySQL尝试为每个可能的索引从WHERE子句提取范围条件。在提取过程中，不能用于构成范围条件的条件被放弃，产生重叠范围的条件组合到一起，并且产生空范围的条件被删除。

例如，考虑下面的语句，其中key1是有索引的列，nonkey没有索引：

SELECT * FROM t1 WHERE

   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR

   (key1 < 'bar' AND nonkey = 4) OR

   (key1 < 'uux' AND key1 > 'z');

key1的提取过程如下：

1. 用原始WHERE子句开始：

2. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR

3. (key1 < 'bar' AND nonkey = 4) OR

4. (key1 < 'uux' AND key1 > 'z')

5. 删除nonkey = 4和key1 LIKE '%b'，因为它们不能用于范围扫描。删除它们的正确途径是用TRUE替换它们，以便进行范围扫描时不会丢失匹配的记录。用TRUE替换它们后，可以得到：

6.            (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR

7.            (key1 < 'bar' AND TRUE) OR

8.            (key1 < 'uux' AND key1 > 'z')

9. 取消总是为true或false的条件：

· (key1 LIKE 'abcde%' OR TRUE)总是true

· (key1 < 'uux' AND key1 > 'z')总是false

用常量替换这些条件，我们得到：

(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

删除不必要的TRUE和FALSE常量，我们得到

(key1 < 'abc') OR (key1 < 'bar')

10.将重叠区间组合成一个产生用于范围扫描的最终条件：

11.        (key1 < 'bar')

总的来说(如前面的例子所述)，用于范围扫描的条件比WHERE子句限制少。MySQL再执行检查以过滤掉满足范围条件但不完全满足WHERE子句的行。

范围条件提取算法可以处理嵌套的任意深度的AND/OR结构，并且其输出不依赖条件在WHERE子句中出现的顺序。

2.1.2.2 多元素索引的范围访问方法

多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序，通过一系列关键元组来定义关键元组区间。

例如，考虑定义为key1(key_part1, key_part2, key_part3)的多元素索引，以及下面的按关键字顺序所列的关键元组：

key_part1  key_part2  key_part3

  NULL       1          'abc'

  NULL       1          'xyz'

  NULL       2          'foo'

   1         1          'abc'

   1         1          'xyz'

   1         2          'abc'

   2         1          'aaa'

条件key_part1 = 1定义了下面的范围：

(1，-inf，-inf) <= (key_part1，key_part2，key_part3) < (1，+inf，+inf)

范围包括前面数据集中的第4、5和6个元组，可以用于范围访问方法。

通过对比，条件key_part3 = 'abc'不定义单一的区间，不能用于范围访问方法。

下面更加详细地描述了范围条件如何用于多元素索引中。

· 对于HASH索引，可以使用包含相同值的每个区间。这说明区间只能由下面形式的条件产生：

·                     key_part1 cmp const1

·                 AND key_part2 cmp const2

·                 AND ...

·                AND key_partN cmp constN;

这里，const1，const2，...为常量，cmp是=、<=>或者IS NULL比较操作符之一，条件包括所有索引部分。(也就是说，有N 个条件，每一个对应N-元素索引的每个部分）。

例如，下面为三元素HASH索引的范围条件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

· 对于BTREE索引，区间可以对结合AND的条件有用，其中每个条件用一个常量值通过=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE 'pattern' (其中'pattern'不以通配符开头)比较一个关键元素。区间可以足够长以确定一个包含所有匹配条件(或如果使用<>或!=，为两个区间)的记录的单一的关键元组。例如，对于条件：

·                  key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

单一区间为：

('foo'，10，10)

   < (key_part1，key_part2，key_part3)

      < ('foo'，+inf，+inf)

创建的区间可以比原条件包含更多的记录。例如，前面的区间包括值('foo'，11，0)，不满足原条件。

· 如果包含区间内的一系列记录的条件结合使用OR，则形成包括一系列包含在区间并集的记录的一个条件。如果条件结合使用了AND，则形成包括一系列包含在区间交集内的记录的一个条件。例如，对于两部分索引的条件：

·                (key_part1 = 1 AND key_part2 < 2)

·                OR (key_part1 > 5)

区间为：

(1, -inf) < (key_part1, key_part2) < (1, 2)

(5, -inf) < (key_part1, key_part2)

在该例子中，第1行的区间左侧的约束使用了一个关键元素，右侧约束使用了两个关键元素。第2行的区间只使用了一个关键元素。EXPLAIN输出的key_len列表示所使用关键字前缀的最大长度。

在某些情况中，key_len可以表示使用的关键元素，但可能不是你所期望的。假定key_part1和key_part2可以为NULL。则key_len列显示下面条件的两个关键元素的长度：

key_part1 >= 1 AND key_part2 < 2

但实际上，该条件可以变换为：

key_part1 >= 1 AND key_part2 IS NOT NULL

2.1.3 索引合并优化

索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。

在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素。

例如：

SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;

SELECT * FROM tbl_name

WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;

SELECT * FROM t1, t2

WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')

AND t2.key1=t1.some_col;

SELECT * FROM t1, t2

WHERE t1.key1=1

AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

索引合并方法有几种访问算法 (参见EXPLAIN输出的Extra字段)：

· 交集

· 联合

· 排序并集

后面几节更加详细地描述了这些方法。

注释：索引合并优化算法具有以下几个已知缺陷：

· 如果可以对某些关键字进行范围扫描，则不考虑索引合并。例如，下面的查询：

·                SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

对于该查询，可以有两个方案：

1. 使用(goodkey1 < 10 OR goodkey2 < 20)条件进行索引合并扫描。

2. 使用badkey < 30条件进行范围扫描。

然而，优化器只考虑第2个方案。如果这不是你想要的，你可以通过使用IGNORE INDEX或FORCE INDEX让优化器考虑index_merge。下面的查询使用索引合并执行：

SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)

WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

SELECT * FROM t1 IGNORE INDEX(badkey)

WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

· 如果查询有一个复杂的WHERE子句，有较深的AND/OR嵌套关系，MySQL不选择该优选方案，通过下面的识别法则尝试分布各条件：

·                (x AND y) OR z = (x OR z) AND (y OR z)

·                (x OR y) AND z = (x AND z) OR (y AND z)

index_merge访问方法的不同变量之间的选择和其它访问方法基于各适用选项的成本估计。

2.1.3.1 索引合并交集访问算法

该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件，每个条件为下面之一：

· 以这种形式，即索引有确切的N部分(即包括了所有索引部分)：

·                key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

· 任何InnoDB或BDB表的主键的范围条件。

下面是一些例子：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name

WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

索引合并交集算法同时对所有使用的索引进行扫描，并产生从合并的索引扫描接收的行序列的交集。

如果使用的索引包括查询中使用的所有列，所有表记录均不搜索，并且在这种情况下EXPLAIN的输出包含Extra字段中的Using index。下面是一个此类查询的例子：

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

如果使用的索引未包括查询中使用的所有列，只有满足所有使用的关键字的范围条件才搜索所有记录。

如果某个合并条件是InnoDB或BDB表的主键的一个条件，不用于记录查询，但用于过滤使用其它条件搜索的记录。

2.1.3.2 索引合并并集访问算法

该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候，每个条件为下面之一：

· 以这种形式，即索引有确切的N部分(即包括了所有索引部分)：

·                key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

· 任何InnoDB或BDB表的主键的范围条件。

· 索引合并方法交集算法适用的一个条件。

下面是一些例子：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR

  (key3='foo' AND key4='bar') AND key5=5;

2.1.3.3 索引合并排序并集访问算法

该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件，但索引合并方法联合算法并不适用的时候。

下面是一些例子：

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name

     WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

排序联合算法和联合算法的区别是排序联合算法必须先索取所有记录的行ID，然后在返回记录前对它们进行排序。

2.2 官方优化示例

假定你有下面所示的SELECT语句，计划使用EXPLAIN来检查它：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,

               tt.ProjectReference, tt.EstimatedShipDate,

               tt.ActualShipDate, tt.ClientID,

               tt.ServiceCodes, tt.RepetitiveID,

               tt.CurrentProcess, tt.CurrentDPPerson,

               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,

               et_1.COUNTRY, do.CUSTNAME

        FROM tt, et, et AS et_1, do

        WHERE tt.SubmitTime IS NULL

          AND tt.ActualPC = et.EMPLOYID

          AND tt.AssignedPC = et_1.EMPLOYID

          AND tt.ClientID = do.CUSTNMBR;

对于这个例子，假定：

被比较的列声明如下：

表有下面的索引：

tt.ActualPC值不是均匀分布的。

开始，在进行优化前，EXPLAIN语句产生下列信息：

table type possible_keys key  key_len ref  rows  Extra

et    ALL  PRIMARY       NULL NULL    NULL 74

do    ALL  PRIMARY       NULL NULL    NULL 2135

et_1  ALL  PRIMARY       NULL NULL    NULL 74

tt    ALL  AssignedPC,   NULL NULL    NULL 3872

           ClientID,

           ActualPC

      range checked for each record (key map: 35)

因为type对每张表是ALL，这个输出显示MySQL正在对所有表产生一个笛卡尔乘积；即每一个行的组合！这将花相当长的时间，因为必须检查每张表的行数的乘积！对于一个实例，这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大，你只能想象它将花多长时间……

这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15)，长度不匹配。

为了修正在列长度上的不同，使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)，再执行EXPLAIN语句产生这个结果：

table type   possible_keys key     key_len ref         rows    Extra

tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using

             ClientID,                                         where

             ActualPC

do    ALL    PRIMARY       NULL    NULL    NULL        2135

      range checked for each record (key map: 1)

et_1  ALL    PRIMARY       NULL    NULL    NULL        74

      range checked for each record (key map: 1)

et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

这不是完美的，但是好一些了：rows值的乘积少了一个因子74。这个版本在几秒内执行完。

第2种方法能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列的长度失配问题：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),

    ->                MODIFY ClientID   VARCHAR(15);

EXPLAIN产生的输出显示在下面：

table type   possible_keys key      key_len ref           rows Extra

et    ALL    PRIMARY       NULL     NULL    NULL          74

tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using

             ClientID,                                         where

             ActualPC

et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1

do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

这几乎很好了。

剩下的问题是，默认情况，MySQL假设在tt.ActualPC列的值是均匀分布的，并且对tt表不是这样。幸好，很容易告诉MySQL来分析关键字分布：

mysql> ANALYZE TABLE tt；

现在联接是“完美”的了，而且EXPLAIN产生这个结果：

table type   possible_keys key     key_len ref           rows Extra

tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using

             ClientID,                                        where

             ActualPC

et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1

et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1

do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

注意在从EXPLAIN输出的rows列是一个来自MySQL联接优化器的“教育猜测”。你应该检查数字是否接近事实。如果不是，可以通过在SELECT语句里面使用STRAIGHT_JOIN并且试着在FROM子句以不同的次序列出表，可能得到更好的性能。

3 总结

SQL优化是一个循序渐进的过程，首先要了解所使用数据库的数据检索机制及相关的算法。然后在实际项目中，不断学习、测试，方可提高优化水平。

后续需要了解：

1. MySQL执行SELECT语句、Update语句的顺序；

2. MySQL表连接（left join、相关/非相关子查询）的检索机制；

3. MySQL中Exists、Distinct、Not Exists、Group By的执行机制。

注：本文大部分摘自官方文档及相关博客。

参考：

[1] MySQL执行计划初探

[2]深入解析MySQL分区（Partition）功能

[3]MySQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

posted @ 2017-07-04 01:21 wlzjdm 阅读(246) 评论(0) 编辑收藏举报

刷新页面返回顶部

wlzjdm

MySQL优化分析

1 EXPLAIN语法

1.1 示例

1.2 结果分析

1.2.1 id

1.2.2 select_type

1.2.3 table

1.2.4 partitions

1.2.5 type

1.2.6 possible_keys

1.2.7 key

1.2.8 key-len

1.2.9 ref

1.2.10 rows

1.2.11 filtered

1.2.12 extra

2 SQL性能评估

2.1 估计查询性能

2.2 SELECT查询的速度

2 优化方法示例分析

2.1 优化方法

2.1.1 Where优化示例

2.1.2 范围优化

2.1.2.1 单元素索引的范围访问方法

2.1.2.2 多元素索引的范围访问方法

2.1.3 索引合并优化

2.1.3.1 索引合并交集访问算法

2.1.3.2 索引合并并集访问算法

2.1.3.3 索引合并排序并集访问算法

2.2 官方优化示例

3 总结

公告