作为开发者必须掌握的mysql操作sql语句优化-showprocesslistandexplain(七)

10.ALL

完整的扫描全表，最慢的联接类型，尽可能的避免。

1. mysql> explain select * from t_order;
2. +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
5. | 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | |
6. +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
7. 1 row in set (0.00 sec)

三.extra的说明

1.Distinct

MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。对于此项没有找到合适的例子，求指点。

2.Not exists

因为b表中的order_id是主键，不可能为NULL，所以mysql在用a表的order_id扫描t_order表，并查找b表的行时，如果在b表发现一个匹配的行就不再继续扫描b了，因为b表中的order_id字段不可能为NULL。这样避免了对b表的多次扫描。

1. mysql> explain select count(1) from t_order a left join t_order_ext b on a.order_id=b.order_id where b.order_id is null;
2. +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
5. | 1 | SIMPLE | a | index | NULL | express_type | 1 | NULL | 100395 | Using index |
6. | 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | Using where; Using index; Not exists |
7. +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
8. 2 rows in set (0.01 sec)

3.Range checked for each record

这种情况是mysql没有发现好的索引可用，速度比没有索引要快得多。

1. mysql> explain select * from t_order t, t_order_ext s where s.order_id>=t.order_id and s.order_id<=t.order_id and t.express_type>5;
2. +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
5. | 1 | SIMPLE | t | range | PRIMARY,express_type | express_type | 1 | NULL | 1 | Using where |
6. | 1 | SIMPLE | s | ALL | order_id | NULL | NULL | NULL | 1 | Range checked for each record (index map: 0x1) |
7. +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
8. 2 rows in set (0.00 sec)

4.Using filesort

在有排序子句的情况下很常见的一种情况。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。

1. mysql> explain select * from t_order order by express_type;
2. +----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
3. | id | select_type | t