首页 > 代码库 > 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 索引的匹配类型