MySQL中Explain初识
2011 年 3 月 4 日
All
Demo
以OrderItems表为例：
| OrderItems | CREATE TABLE `OrderItems` (
`order_num` int(11) NOT NULL,
`order_item` int(11) NOT NULL,
`prod_id` char(10) NOT NULL,
`quantity` int(11) NOT NULL,
`item_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`order_num`,`order_item`),
KEY `FK_OrderItems_Products` (`prod_id`),
KEY `idx_price` (`item_price`),
KEY `idx_order_num_price` (`order_num`,`item_price`),
CONSTRAINT `FK_OrderItems_Orders` FOREIGN KEY (`order_num`) REFERENCES `Orders` (`order_num`),
CONSTRAINT `FK_OrderItems_Products` FOREIGN KEY (`prod_id`) REFERENCES `Products` (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> select * from OrderItems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
| 20006 | 1 | BR01 | 20 | 5.99 |
| 20006 | 2 | BR02 | 10 | 8.99 |
| 20006 | 3 | BR03 | 10 | 11.99 |
| 20007 | 1 | BR03 | 50 | 11.49 |
| 20007 | 2 | BNBG01 | 100 | 2.99 |
| 20007 | 3 | BNBG02 | 100 | 2.99 |
| 20007 | 4 | BNBG03 | 100 | 2.99 |
| 20007 | 5 | RGAN01 | 50 | 4.49 |
| 20008 | 1 | RGAN01 | 5 | 4.99 |
| 20008 | 2 | BR03 | 5 | 11.99 |
| 20008 | 3 | BNBG01 | 10 | 3.49 |
| 20008 | 4 | BNBG02 | 10 | 3.49 |
| 20008 | 5 | BNBG03 | 10 | 3.49 |
| 20009 | 1 | BNBG01 | 250 | 2.49 |
| 20009 | 2 | BNBG02 | 250 | 2.49 |
| 20009 | 3 | BNBG03 | 250 | 2.49 |
+-----------+------------+---------+----------+------------+
18 rows in set (0.03 sec)
举例说明几个Explain type的查询：
all，直接全表查询
mysql> explain select item_price from OrderItems;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | OrderItems | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
index，利用索引
mysql> explain select order_num from OrderItems;
+----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | FK_OrderItems_Products | 30 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+
range，对索引进行范围查询，多见于between/in/<>等关键字
mysql> explain select order_num from OrderItems where order_num between 20003 and 20005;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | OrderItems | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
ref，使用普通索引
mysql> explain select order_num from OrderItems where order_num = 20005;
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY | PRIMARY | 4 | const | 2 | 100.00 | Using index |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数。
Optimization
通过分析Explain语句结果可以优化查询性能。一般关注点有：一是type往好的方向优化，二是有order by时，尽量不要在extra中出现Using filesort。
-
type优化，如 阿里巴巴Java编程规范
中定义的那样，一般不允许all和index的查询，会极大影响性能。尽量优化至range以上。方法一般就是建索引，不要为了节省插入性能而去缩减必要的索引。
mysql> CREATE INDEX idx_price on OrderItems(item_price);
然后再执行关于item_price的查询：
mysql> explain select order_num from OrderItems where item_price between 5 and 8;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | OrderItems | NULL | range | idx_price | idx_price | 4 | NULL | 2 | 100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
看到type位置变为range，possible_keys和key的位置出现了我们新建的索引
- 去除Using filesort
执行下列查询
mysql> explain select * from OrderItems where order_num = 20003 order by item_price;
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
使用了fileSort。我们建立索引并再次执行查询如下：
mysql> CREATE INDEX idx_order_num_price on OrderItems(order_num, item_price);
mysql> explain select * from OrderItems where order_num = 20003 order by item_price;
+----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY,idx_order_num_price | idx_order_num_price | 4 | const | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
如果建立了多重索引A-B(A和B是column name)，那么查询语句的where clause中仅使用了A也是可以利用该A-B索引的。事实上，只要查询条件从左至右依次匹配某索引，都是可以利用的。
Problems
另外遇到两个关于索引有序性使用的小坑。
-
查询语句中的字段匹配索引的前半部分，但如果它们是用于in/between，索引失效
比如在建立了order_num/item_price索引的情况下，还是会fileSort：
mysql> explain select order_num from OrderItems where order_num between 20003 and 20005 order by item_price;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using filesort |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
- 单一Order时使用索引不分升降序，但如果对多字段排序，则要求索引顺序和查询语句’一致’
mysql> explain select item_price from OrderItems ORDER BY order_num, item_price;
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_order_num_price | 8 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select item_price from OrderItems ORDER BY order_num, item_price desc;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_price | 4 | NULL | 18 | 100.00 | Using index; Using filesort |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select item_price from OrderItems ORDER BY order_num desc, item_price desc;
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_order_num_price | 8 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
如果理解了MySQL索引的物理实现（B+ Tree)，这些应该就比较容易理解了（TODO）。
Reference
About The Author
bjmayor
程序员，码农，php,python,ios,android,go，产品经理，创业。