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使用的表扫描
转载于:https://blog.51cto.com/dwchaoyue/1552639
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
