MySQL 索引的匹配类型

/*

创建测试表

drop table  t_index ;

create table t_index(

tid  int  not null PRIMARY key  auto_increment ,

tname varchar(100) not null ,

tage tinyint default 0 ,

tadd varchar(100) default  '' ,

tel int default  0,

tmob varchar(20) DEFAULT '' ,

tsfz varchar(100) default  '' 

) 

ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('张三风',120,'武当山' ,18099001122,'012-46319976','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('朱元璋',56,'北京' ,18112401122,'012-40119976','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('杨过',25,'武汉' ,18099112122,'012-46340116','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('郭靖',45,'长沙' ,13149001122,'012-46900176','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('黄老邪',100,'河北' ,13129001122,'012-49001976','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('周伯通',102,'河南' ,15679001122,'012-46319001','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('洪七公',78,'合肥' ,11243001122,'012-46319976','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('欧阳峰',67,'广西' ,13214001122,'012-14009976','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('欧阳可',27,'深圳' ,15123001122,'012-46314006','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('尼玛',10,'上海' ,13125001122,'012-41400976','') ;

insert into  t_index(tname,tage,tadd,tel,tmob,tsfz)

VALUES('杨康',30,'西藏' ,15798001122,'012-46311400','') ;

创建一个多列索引

alter table t_index 

add  key (tname,tage,tadd)

可以使用B-tree的查询类型：

1.全值匹配:查询出来的结果集全部来自索引查找

查询中包含索引的所有列,并且索引的所有列必须为等值运算，或者索引中的最后一列为非等值运算。

sql1

mysql> explain  select * from t_index where tname='张三风' and  tage=120 and  tadd='武当山' ;

+----+-------------+---------+------+---------------+-------+---------+-------------------+------+-------------+

| id | select_type | table   | type | possible_keys | key   | key_len | ref               | rows | Extra       |

+----+-------------+---------+------+---------------+-------+---------+-------------------+------+-------------+

|  1 | SIMPLE      | t_index | ref  | tname         | tname | 607     | const,const,const |    1 | Using where |

+----+-------------+---------+------+---------------+-------+---------+-------------------+------+-------------+

mysql> select * from t_index where tname='张三风' and  tage=120 and  tadd='武当山' ;

+-----+-----------+------+-----------+------------+--------------+------+

| tid | tname     | tage | tadd      | tel        | tmob         | tsfz |

+-----+-----------+------+-----------+------------+--------------+------+

|   1 | 张三风    |  120 | 武当山    | 2147483647 | 012-46319976 |      |

+-----+-----------+------+-----------+------------+--------------+------+

sql2

mysql> explain   select * from t_index where tname='张三风' and  tage = 120  and  tadd like  '武当%' ;

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra       |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

|  1 | SIMPLE      | t_index | range | tname         | tname | 607     | NULL |    1 | Using where |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

mysql>   select * from t_index where tname='张三风' and  tage = 120  and  tadd like  '武当%' ;

+-----+-----------+------+-----------+------------+--------------+------+

| tid | tname     | tage | tadd      | tel        | tmob         | tsfz |

+-----+-----------+------+-----------+------------+--------------+------+

|   1 | 张三风    |  120 | 武当山    | 2147483647 | 012-46319976 |      |

+-----+-----------+------+-----------+------------+--------------+------+

 查询中包含索引的部分列。部分列中前面的所有列必须包含在查询中，并且为等值运算或包含在查询中的索引的最后一列为非等值运算。

如果查询中包含索引的第一列并且不包含索引中的其他列,那么索引可以使用到索引的第一列查找数据。

如果查询包含了索引中的第一列和第二列不包含索引中的其他列并且第一列为等值运算那么查询可以利用索引的第一列和第二列

 sql3

mysql> explain   select * from  t_index where  tname ='张三风' and  tage  =110  ;

+----+-------------+---------+------+---------------+-------+---------+-------------+------+-------------+

| id | select_type | table   | type | possible_keys | key   | key_len | ref         | rows | Extra       |

+----+-------------+---------+------+---------------+-------+---------+-------------+------+-------------+

|  1 | SIMPLE      | t_index | ref  | tname,tage    | tname | 304     | const,const |    1 | Using where |

+----+-------------+---------+------+---------------+-------+---------+-------------+------+-------------+

mysql>   select * from  t_index where  tname ='张三风' and  tage  =110  ;

+-----+-----------+------+--------+------------+--------------+------+

| tid | tname     | tage | tadd   | tel        | tmob         | tsfz |

+-----+-----------+------+--------+------------+--------------+------+

|  13 | 张三风    |  110 | 恒山   | 2147483647 | 012-46319976 |      |

+-----+-----------+------+--------+------------+--------------+------+

sql4

mysql> explain   select * from  t_index where  tname ='张三风' and  tage > 110  ;

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra       |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

|  1 | SIMPLE      | t_index | range | tname,tage    | tname | 304     | NULL |    2 | Using where |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

mysql>    select * from  t_index where  tname ='张三风' and  tage > 110  ;

+-----+-----------+------+-----------+------------+--------------+------+

| tid | tname     | tage | tadd      | tel        | tmob         | tsfz |

+-----+-----------+------+-----------+------------+--------------+------+

|   1 | 张三风    |  120 | 武当山    | 2147483647 | 012-46319976 |      |

|  12 | 张三风    |  120 | 泰山      | 2147483647 | 012-46319976 |      |

+-----+-----------+------+-----------+------------+--------------+------+

sql1的执行计划中预估值为1，查询结果记录数也为1,

sql1的执行计划中预估值为1，查询结果记录数也为1,

sql3的执行计划中预估值为1，查询结果记录数也为1,

sql4的执行计划中预估值为2，查询结果记录数也为2,

可以看出sql1，sql2，sql3，sql4的结果是通过索引查找得到的，没有另外表扫描。

以上查询属于全值匹配,查询出来的数据全部来自索引查找，这样的sql语句充分的发挥了索引功能

2.部分值匹配：查询出来的结果部分来自索引查找，部分来自表扫描。

 查询包含索引的所有列但是非最后一列为范围查找

以下这个就使用了索引的第一列和第二列，没有使用到第三列

sql5

mysql> explain   select * from t_index where tname='张三风' and  tage  like  '12%' and  tadd='武当山' ;

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra       |

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

|  1 | SIMPLE      | t_index | ref  | tname         | tname | 302     | const |    3 | Using where |

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

mysql>   select * from t_index where tname='张三风' and  tage  like  '12%' and  tadd='武当山' ;

+-----+-----------+------+-----------+------------+--------------+------+

| tid | tname     | tage | tadd      | tel        | tmob         | tsfz |

+-----+-----------+------+-----------+------------+--------------+------+

|   1 | 张三风    |  120 | 武当山    | 2147483647 | 012-46319976 |      |

+-----+-----------+------+-----------+------------+--------------+------+

 查询中包含索引中的某些列,

例如查询中包含了第一列和第三列，那么查询只能利用到索引的第一列

如果包含了第二列和第三列，那么索引就不能使用索引查找数据。

sql6

mysql> explain     select * from  t_index where  tname='张三风' and   tadd ='武当山'  ;

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra       |

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

|  1 | SIMPLE      | t_index | ref  | tname         | tname | 302     | const |    3 | Using where |

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

mysql>    select * from  t_index where  tname='张三风' and   tadd ='武当山'  ;

+-----+-----------+------+-----------+------------+--------------+------+

| tid | tname     | tage | tadd      | tel        | tmob         | tsfz |

+-----+-----------+------+-----------+------------+--------------+------+

|   1 | 张三风    |  120 | 武当山    | 2147483647 | 012-46319976 |      |

+-----+-----------+------+-----------+------------+--------------+------+

sql7

mysql> explain  select * from  t_index where  tage =120  and   tadd ='武当山'  ;

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | t_index | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using where |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

mysql>  select * from  t_index where  tage =120  and   tadd ='武当山'  ;

+-----+-----------+------+-----------+------------+--------------+------+

| tid | tname     | tage | tadd      | tel        | tmob         | tsfz |

+-----+-----------+------+-----------+------------+--------------+------+

|   1 | 张三风    |  120 | 武当山    | 2147483647 | 012-46319976 |      |

+-----+-----------+------+-----------+------------+--------------+------+

sql5的执行计划中预估值为3，查询结果记录数也为1,

sql6的执行计划中预估值为3，查询结果记录数也为1,

sql7的执行计划中预估值为17，查询结果记录数也为1,

sql5和sql6使用索引的部分 匹配，

sql7使用的表扫描