MySQL 索引的匹配類型

標籤：mysql 索引   匹配類型   

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.全值匹配:查詢中包含多列索引中德所有列並且在查詢中充分利用到了索引讀取資料。

這個情況存在於多列索引中的，對於單列索引都是全值匹配的。

在多列索引中可能由於某些原因不能做到全值匹配，只能做到部分列匹配。

例如：

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 |

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

該查詢使用的就是全值匹配,在查詢中使用到了多列索引中的全部列

注意：要是做到全值匹配,那麼多列索引中的最後一列的前面的所有列必須給出準確的值，不能是範圍值，

否則就是變成了了部分值匹配,

2.部分值匹配：查詢中沒有完全利用到多列索引中的列。

查詢中使用的列都包含在多列索引中，但是有部分列使用了範圍值(索引中最後一列除外)

sql11

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 |

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

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 |

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

sql3

mysql>  explain   select * from t_index where tname=‘張三風‘ and  tage  > 115 and  tadd=‘武當山‘ ;

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

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

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

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

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

sql4

mysql> explain  select * from t_index where tname=‘張三風‘ and  tage  < 110 and  tadd=‘武當山‘ ;

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

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

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

|  1 | SIMPLE      | t_index | ALL  | tname         | NULL | NULL    | NULL |   13 | Using where |

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

sql1中tage使用模糊查詢查詢導致該查詢只使用到了索引的第一列(以前長期認為此種查詢會使用到第一列和第二列)

sql2中tadd使用了範圍值,但是tadd是索引中的最後一列，查詢還是使用了全值匹配。

sql3中tage使用了範圍值，從執行計畫可以看到查詢使用了索引的第一列和第二列

sql4使用了全表掃描，沒有使用任何的索引掃描，根據索引使用規則，該查詢完全能走索引掃描的，但是為什麼沒有走呢。

我查詢結果公布下，估計會出乎意料之外。

mysql>   select * from t_index where tname=‘張三風‘ and  tage  < 110 and  tadd=‘武當山‘ ;

Empty set (0.00 sec)

查詢出來是空值。根據我們的常規思維,sql4的可以使用到多列索引的第一列和第二列。這一點有點和SQLServer不一樣，

這裡提一下，謹防上當受騙。

本文出自 “SQLServer MySQL” 部落格，謝絕轉載！

MySQL 索引的匹配類型