MySQL使用索引的几种方式

                                  MySQL使用索引的几种方式

      之前的blog有介绍令人迷糊的extra中信息，本文我们重点来看看当执行计划使用索引的时候
extra中几种显示的场景

 版本:Mysql 5.6.14

测试表结构如下:

 CREATE TABLE `snapshot` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `snap_id` int(11) DEFAULT NULL,
  `name` varchar(500) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `snap_id_ix` (`snap_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

在snap_id上创建了索引，然后插入一些测试数据。

1，snap_id为等于的操作

mysql> explain extended select * from snapshot where snap_id=10;
+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | snapshot | ref  | snap_id_ix    | snap_id_ix | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这个虽然使用了索引，但是extra中没有任何信息，显示为空

2,使用索引条件，先扫描索引，然后再通过索引扫描表

mysql>  explain extended select *  from snapshot where snap_id<100;
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | snapshot | range | snap_id_ix    | snap_id_ix | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

通过索引条件，然后扫描表数据，extra是Using index condition

 

3,使用cover index，只查索引中的列，直接从索引中返回数据就可以了

mysql>  explain extended select snap_id from snapshot;
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | snapshot | index | NULL          | snap_id_ix | 5       | NULL | 6415 |   100.00 | Using index |
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>  explain extended select snap_id from snapshot where snap_id=10;
+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | snapshot | ref  | snap_id_ix    | snap_id_ix | 5       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql>  explain extended select snap_id from snapshot where snap_id>10;
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | snapshot | range | snap_id_ix    | snap_id_ix | 5       | NULL | 6367 |   100.00 | Using where; Using index |
+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

在extra出现的是Using index提示，表明只使用索引即可，不需要对表进行扫描操作.

 

4,   接下来我们看连接的操作，假设为snapshot作为连接的内表

mysql> explain
    -> select *
    -> from t1 join snapshot
    -> on (t1.a=snapshot.snap_id)
    -> ;
+----+-------------+----------+------+---------------+------------+---------+----------+------+-------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref      | rows | Extra |
+----+-------------+----------+------+---------------+------------+---------+----------+------+-------+
|  1 | SIMPLE      | t1       | ALL  | PRIMARY       | NULL       | NULL    | NULL     |    3 | NULL  |
|  1 | SIMPLE      | snapshot | ref  | snap_id_ix    | snap_id_ix | 5       | db1.t1.a |  200 | NULL  |
+----+-------------+----------+------+---------------+------------+---------+----------+------+-------+
2 rows in set (0.00 sec)

通过执行计划可以看出对snapshot也是索引来访问的，外表传过来的值，在内表很类似于snap_id=XX,所以和第1中的执行
计划很类似.除ref之外，一个是来自于常量(const),而本例中是来自表t1的索引db.t1.a.