覆盖索引定义 http://zhangzhibinshuai.diandian.com/post/2012-03-31/14761710
测试：
表结构 表中数据只有5行
mysql> desc aa;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| other | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
+------+--------+--------+
| id | name | other |
+------+--------+--------+
| 1 | asdf | dfdfd |
| 2 | sdf | fdfd |
| 3 | dfasdf | dfdasd |
| 4 | fasdf | fdasd |
| 5 | asdf | dasd |
+------+--------+--------+
在id 和 name 上建立联合索引后 第一种查询用到了覆盖索引(Extra using index)(select name ... where id)
mysql> create index aa_id_name on aa(id,name);
Query OK, 5 rows affected (0.14 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> explain select name from aa where id = 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: aa
type: ref
possible_keys: aa_id_name
key: aa_id_name
key_len: 5
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select other from aa where id = 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: aa
type: ref
possible_keys: aa_id_name
key: aa_id_name
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
创建id 和 name联合索引后的表结构：
mysql> desc aa;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(16) | YES | | NULL | |
| other | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
索引顺序是id->name
①查询时将name 放到where条件中（select id ... where name），根据下边信息知道
http://www.chenyajun.com/2009/01/02/1581
mysql> explain select id from aa where name = 'asdf'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: aa
type: index
possible_keys: NULL //根据sql语句mysql根据最左前缀规则是用不到索引的
key: aa_id_name //mysql优化时会找一个覆盖索引来进行查询
key_len: 24
ref: NULL
rows: 5
Extra: Using where; Using index
② 当然下边两条sql执行计划信息表明这条第一条SQL语句肯定没有用到索引（select other ... where name 和select other ... where id）
mysql> explain select other from aa where name = 'asdf'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: aa
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.20 sec)
mysql> explain select other from aa where id = 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: aa
type: ref
possible_keys: aa_id_name
key: aa_id_name
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
