MySQL 索引的匹配类型_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1927 | 回复: 0   主题: MySQL 索引的匹配类型        下一篇 
伊恩dih
注册用户
等级:少校
经验:819
发帖:15
精华:0
注册:2015-5-28
状态:离线
发送短消息息给伊恩dih 加好友    发送短消息息给伊恩dih 发消息
发表于: IP:您无权察看 2015-6-1 10:37:55 | [全部帖] [楼主帖] 楼主

/*


创建测试表

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使用的表扫描




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论