MySql优化
explain分析
| id | SELECT查询语句的编号.如果SELECT语句有字查询,则ID递增. | |
| select_type | SELECT的类型，可以为以下任何一种 | |
| SIMPLE | 简单SELECT(不使用UNION或子查询) | |
| PRIMARY | 最外面的SELECT,说明有子查询或者UNION | |
| DERIVED | 当子查询是from子句时，其select_type为 DERIVED | |
| UNION | union中的第二个或后面的SELECT语句 | |
| SUBQUERY | 子查询中的第一个SELECT,注意必须是非FROM型子查询 | |
| DEPENDENT SUBQUERY | 在IN型子查询中第一个子查询的select_type则是DEPENDENT SUBQUERY | |
| DEPENDENT UNION |
在IN型子查询中用union时, 第二个union的select_type就是DEPENDENT UNION |
|
| UNION RESULT | UNION的结果 | |
| table |
查询针对的表名 实际表名 表的别名 derived null |
|
| type | 联接类型,下面给出各种联接类型,按照从最佳类型到最坏类型进行排序. | |
| system | 表仅有一行。这是const联接类型的一个特例 | |
| const |
表最多有一个匹配行，它将在查询开始时被读取。 因为仅有一行，在这行的列值可被优化器剩余部分认为是常数 |
|
| eq_ref | 在联表中,通过UNIQUE索引或PRIMARY KEY索引可以直接找到某一行 | |
| ref | 在联表中,通过索引可以直接找到某些行 | |
| ref_of_null | 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行 | |
| index_merge |
该联接类型表示使用了索引合并优化方法 经常出现在使用一张表中的多个索引时,mysql会将多个索引合并在一起 |
|
| unique_subquery | unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高. 该类型替换了下面形式的IN子查询的ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) |
|
| index_subquery | 该联接类型类似于unique_subquery。 可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr) |
|
| range | 只检索给定范围的行，使用一个索引来选择行 | |
| index | 扫描所有索引节点 | |
| all | 全表扫描 | |
| possible_key | 可能用到的索引 | |
| key | 最终用的索引 | |
| key_len | 索引长度 | |
| ref | 连接查询时,前表和后表的引用关系 | |
| rows | 预计扫描的行数 | |
| extra | 查询的附加信息 | |
| Distinct | MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行 | |
| Not exists |
MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后, 不再为前面的的行组合在该表内检查更多的行 |
|
| range checked for each record |
MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用 | |
| Using filesort | MySQL需要额外的一次传递,以找出如何按排序顺序检索行 | |
| Using index | 索引覆盖，效率非常高 |
|
| Using temporary | 为了解决查询,MySQL需要创建一个临时表来容纳结果 | |
| Using where | 光靠索引定位不了，还得where条件判断一下 |
|
|
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查询的所有列,而不要额外搜索硬盘访问实际的表 |
|
一.select_type的说明
SIMPLE: 简单SELECT(不使用UNION或子查询)
mysql> EXPLAIN SELECT goods_id,goods_name,cat_id FROM goods; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | goods | ALL | NULL | NULL | NULL | NULL | 31 | | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.01 sec)
PRIMARY: 最外层的SELECT,说明是有子查询或者UNION
mysql> EXPLAIN SELECT goods_id,goods_name,cat_id FROM (SELECT goods_id,goods_name,cat_id FROM goods) AS t; //子查询 +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 31 | | | 2 | DERIVED | goods | ALL | NULL | NULL | NULL | NULL | 31 | | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ 2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT cat_id FROM category WHERE cat_id = 1 UNION ALL SELECT goods_id FROM goods WHERE goods_id =3; // UNION +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | category | const | PRIMARY | PRIMARY | 2 | const | 1 | Using index | | 2 | UNION | goods | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.05 sec)
DERIVED: 当子查询是from子句时，其select_type为 DERIVED
mysql> EXPLAIN SELECT goods_id,goods_name,cat_id FROM (SELECT goods_id,goods_name,cat_id FROM goods) AS t; //子查询 +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 31 | | | 2 | DERIVED | goods | ALL | NULL | NULL | NULL | NULL | 31 | | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ 2 rows in set (0.00 sec)
UNION: union中的第二个或后面的SELECT语句
mysql> EXPLAIN SELECT cat_id FROM category WHERE cat_id = 1 UNION ALL SELECT goods_id FROM goods WHERE goods_id =3; // UNION +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | category | const | PRIMARY | PRIMARY | 2 | const | 1 | Using index | | 2 | UNION | goods | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.05 sec)
SUBQUERY: 子查询中的第一个SELECT,注意必须是非FROM型子查询
mysql> EXPLAIN SELECT goods_id FROM goods WHERE goods_id = (SELECT cat_id FROM category WHERE cat_id = 4); +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | goods | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index | | 2 | SUBQUERY | category | const | PRIMARY | PRIMARY | 2 | | 1 | Using index | +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+ 2 rows in set (0.00 sec)
DEPENDENT SUBQUERY: 在IN型子查询中第一个子查询的select_type则是DEPENDENT SUBQUERY
DEPENDENT UNION: 在IN型子查询中用union时,第二个union的select_type就是DEPENDENT UNION
UNION RESULT: UNION的结果
mysql> EXPLAIN SELECT goods_id FROM goods WHERE goods_id IN(SELECT cat_id FROM category WHERE cat_id = 4); +----+--------------------+----------+-------+---------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +----+--------------------+----------+-------+---------------+---------+---------+-------+------+--------------------------+ | 1 | PRIMARY | goods | index | NULL | PRIMARY | 3 | NULL | 31 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | category | const | PRIMARY | PRIMARY | 2 | const | 1 | Using index | +----+--------------------+----------+-------+---------------+---------+---------+-------+------+--------------------------+ 2 rows in set (0.01 sec)
EXPLAIN SELECT goods_id FROM goods WHERE goods_id IN(SELECT goods_id FROM admin_log WHERE log_id=3 UNION ALL SELECT cat_id FROM category WHERE cat_id=4); +----+--------------------+------------+-------+---------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+-------+---------------+---------+---------+-------+------+--------------------------+ | 1 | PRIMARY | goods | index | NULL | PRIMARY | 3 | NULL | 31 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | admin_log | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 3 | DEPENDENT UNION | category | const | PRIMARY | PRIMARY | 2 | const | 1 | Using index | |NULL| UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------------+------------+-------+---------------+---------+---------+-------+------+--------------------------+
二.type的说明
system: 表仅有一行
const: 表最多有一个匹配行
mysql> EXPLAIN SELECT goods_id,goods_name FROM (SELECT goods_id,goods_name FROM goods WHERE goods_id = 1) AS t; +----+-------------+------------+--------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | goods | const | PRIMARY | PRIMARY | 3 | | 1 | | +----+-------------+------------+--------+---------------+---------+---------+------+------+-------+ 2 rows in set (0.00 sec)
eq_ref: 在联表中,通过UNIQUE索引或PRIMARY KEY索引可以直接找到某一行
mysql> EXPLAIN SELECT goods_id,a.cat_id,cat_name FROM goods AS a LEFT JOIN category AS b ON a.cat_id = b.cat_id; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 31 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 2 | shop.a.cat_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ 2 rows in set (0.00 sec)
ref: 在联表中,通过索引可以直接找到某些行,a表变成ref,原因是b表中所以的cat_id<4,同样会去a表去找一遍,a的cat_id是会重复出现的.
mysql> EXPLAIN SELECT goods_id,a.cat_id,cat_name FROM goods AS a INNER JOIN category AS b ON a.cat_id = b.cat_id AND b.cat_id < 4; +----+-------------+-------+-------+--------------------------+---------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+--------------------------+---------+---------+---------------+------+-------------+ | 1 | SIMPLE | b | range | PRIMARY | PRIMARY | 2 | NULL | 4 | Using where | | 1 | SIMPLE | a | ref | cat_id,cat_id_2,cat_id_3 | cat_id | 2 | shop.b.cat_id | 6 | | +----+-------------+-------+-------+--------------------------+---------+---------+---------------+------+-------------+ 2 rows in set (0.00 sec)
index_merge: 经常出现在使用一张表中的多个索引时。mysql会将多个索引合并在一起
mysql> EXPLAIN SELECT * FROM goods WHERE cat_id>4 or goods_id<2 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type: index_merge possible_keys: PRIMARY,cat_id,cat_id_2,cat_id_3 key: cat_id,PRIMARY key_len: 2,3 ref: NULL rows: 15 Extra: Using sort_union(cat_id,PRIMARY); Using where 1 row in set (0.00 sec)
unique_subquery: 该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。
注意ref列，其中第二行显示的是func，表明unique_subquery是一个函数，而不是一个普通 的ref.
mysql> EXPLAIN SELECT goods_id,goods_name FROM goods WHERE goods_id IN(SELECT cat_id FROM category WHERE cat_id>4); //注意: 子查询中查的是主键cat_id +----+--------------------+----------+-----------------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------+-----------------+---------------+---------+---------+------+------+--------------------------+ | 1 | PRIMARY | goods | ALL | NULL | NULL | NULL | NULL | 31 | Using where | | 2 | DEPENDENT SUBQUERY | category | unique_subquery | PRIMARY | PRIMARY | 2 | func | 1 | Using index;Using where | +----+--------------------+----------+-----------------+---------------+---------+---------+------+------+--------------------------+ 2 rows in set (0.00 sec)
index_subquery: 该联接类型与上面的太像了，唯一的差别就是子查询查的不是主键而是非唯一索引
mysql> EXPLAIN SELECT goods_id FROM goods WHERE goods_id IN(SELECT cat_id FROM goods WHERE cat_id>4); //注意: 子查询中goods表中查的不是主键 +----+--------------------+-------+----------------+--------------------------+---------+---------+------+------+---------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +----+--------------------+-------+----------------+--------------------------+---------+---------+------+------+---------+ | 1 | PRIMARY | goods | index | NULL | PRIMARY | 3 | NULL | 31 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | goods | index_subquery | cat_id,cat_id_2,cat_id_3 | cat_id | 2 | func | 6 | Using index;| +----+--------------------+-------+----------------+--------------------------+---------+---------+------+------+----------+ 2 rows in set (0.00 sec)
range: 只检索给定范围的行，使用一个索引来选择行
mysql> EXPLAIN SELECT goods_id FROM goods WHERE goods_id < 10; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | goods | range | PRIMARY | PRIMARY | 3 | NULL | 9 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
index: 扫描所有索引节点.
mysql> EXPLAIN SELECT goods_id FROM goods WHERE goods_id > 0; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | goods | index | PRIMARY | PRIMARY | 3 | NULL | 31 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
ALL: 扫描全表,因为没沿着索引去找,而是很傻的一条条去扫描直到找到对应的行.
mysql> EXPLAIN SELECT goods_name FROM goods WHERE goods_name = 'abc'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | goods | ALL | NULL | NULL | NULL | NULL | 31 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
三.extra的说明
Distinct: 发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists: 如果在b表发现一个匹配的行就不再继续扫描b了，因为b表中的order_id字段不可能为NULL。这样避免了对b表的多次扫描。
mysql> EXPLAIN SELECT goods_id,cat_name FROM goods AS a LEFT JOIN category AS b ON a.cat_id = b.cat_id WHERE b.cat_id is null; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------------------+ | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 31 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 2 | shop.a.cat_id | 1 | Using where; Not exists | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------------------+ 2 rows in set (0.00 sec)
range checked for each record (index map: #): 这种情况是mysql没有发现好的索引可用，速度比没有索引要快得多。
Using filesort : 无法根据索引去排序.
mysql> EXPLAIN SELECT goods_id FROM goods ORDER BY goods_name; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | goods | ALL | NULL | NULL | NULL | NULL | 31 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec)
Using index : 索引覆盖
mysql> EXPLAIN SELECT goods_id FROM goods; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | goods | index | NULL | PRIMARY | 3 | NULL | 31 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Using temporary: 生成临时表 [何时产生临时表?]
mysql> EXPLAIN SELECT goods_name,goods_id FROM goods GROUP BY goods_name; +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | 1 | SIMPLE | goods | ALL | NULL | NULL | NULL | NULL | 31 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ 1 row in set (0.00 sec)
Using where: 当有where子句时，extra都会有说明。
mysql> EXPLAIN SELECT goods_name,goods_id FROM goods WHERE goods_name = 'abc'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | goods | ALL | NULL | NULL | NULL | NULL | 31 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Using sort_union(...), Using union(...), Using intersect(...): 使用一张表中的多个索引时。mysql会将多个索引合并在一起,这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by: 索引中找到分组所需的所有数据，不需要查询实际的表。
mysql> EXPLAIN SELECT goods_id FROM goods GROUP BY goods_id; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | goods | range | NULL | PRIMARY | 3 | NULL | 32 | Using index for group-by | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
