DROP TABLE IF EXISTS `test_explain_type`; CREATE TABLE `test_explain_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `index_type` (`a`, `b`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test_explain_type(id,a,b,c) values (3,4,2,4); insert into test_explain_type(id,a,b,c) values (2,7,6,5); insert into test_explain_type(id,a,b,c) values (4,4,1,6); insert into test_explain_type(id,a,b,c) values (1,4,4,9); insert into test_explain_type(id,a,b,c) values (5,2,7,2); insert into test_explain_type(id,a,b,c) values (7,4,0,0);
下边的过程是依据我本地的mysql版本来的
1 2 3 4 5 6 7
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.17 | +-----------+ 1 row in set (0.00 sec)
查看表结构
1 2 3 4 5 6 7 8 9 10
mysql> desc test_explain_type; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | a | int(11) | NO | MUL | NULL | | | b | int(11) | NO | | NULL | | | c | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
type： 连接类型(下面的从好到坏依次解释) system 触发条件：表只有一行，这是一个const type 的特殊情况。 const 触发条件：使用了主键或唯一索引索引的查询，最多只有一行匹配。 eq_ref 触发条件：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件 ref 触发条件：使用非唯一索引扫描，还可见于唯一索引最左原则匹配扫描 range 范围扫描通常出现在in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。 index 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据. 以下两种情况会触发： 1、如果索引是查询的覆盖索引，就是说索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。 在这种情况下，explain 的 Extra 列的结果是 Using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。 2、全表扫描会按索引的顺序来查找数据行。使用索引不会出现在Extra列中。 all 全表扫描
下边我们分别对几种索引连接类型进行说明：
1、index类型
1 2 3 4 5 6 7
mysql> explain select id from test_explain_type; +----+-------------+-------------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test_explain_type | NULL | index | NULL | index_type | 8 | NULL | 6 | 100.00 | Using index | +----+-------------+-------------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
这里全表扫描主键索引id，性能比all略好
2、range类型
1 2 3 4 5 6 7
mysql> explain select * from test_explain_type where id>1; +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test_explain_type | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
-- 子查询用到了覆盖索引（Using index），无需回表 mysql> explain select * from prop_action_reward where id > (select id from prop_action_reward order by create_time limit 100000,1) limit 10; +----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+ | 1 | PRIMARY | prop_action_reward | range | PRIMARY | PRIMARY | 8 | NULL | 47244120 | Using where | | 2 | SUBQUERY | prop_action_reward | index | NULL | idx_create_time | 5 | NULL | 94488240 | Using index | +----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+
-- 耗费了0.03S，提升很大 mysql> select * from prop_action_reward where id > (select id from prop_action_reward order by create_time limit 100000,1) limit 10; ... 10 rows in set (0.03 sec)
