oracle 5种索引方式,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.