最新消息:欢迎加入小松的QQ群一起讨论一起学习,搜索页面修改成lua+ElasticSearch,使用php这个页面速度相对慢一点,优化之后速度杠杠的,如有问题请加群联系我

MySQL单列索引和组合索引(联合索引)的区别详解

MYSQL 7742浏览 0评论

发现index merge局限性,优化器会自动判断是否使用 index merge 优化技术,查询还是需要组合索引【推荐阅读:对mysql使用索引的误解

MySQL单列索引和组合索引(联合索引)的区别详解
初始我写这篇文章的原因在于面试到一家大的游戏公司的时候,一个面试题
大致的内容是怎么加速这个where a=’1′ 和 where a=’1′ and b=’2′,当时我答题的时候回答的是a加索引,a和b加联合索引。
面试官跟我聊题目的时候说 where a=’1′ and b=’2′ 在加一个b的索引就好了,因为之前加过a的单列索引了,他很确定的告诉我,因为我没有测试过,所以没有反驳。在回去的路上我一直想不通,多个单列的索引和联合索引都一样的速度,联合索引还有什么用?
今天有空来做个试验,当然之前我已经查过资料了,为了确定一下
创建一个users表

CREATE TABLE `users` (
`userID` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
PRIMARY KEY (`userID`)
)

创建一个genUsers存储过程,用来模拟数据

delimiter $
create procedure genUsers()
begin
declare i int default 0;
while i < 100000 do
set i = i + 1;
insert into users(userID,userName,`password`) values(i,concat('username',i),concat('password',i));
end while;
end $
delimiter ;

执行存储过程

call genUsers();

复制表结构和数据

CREATE TABLE users2 SELECT * FROM users;

添加userName,password单列索引

alter table users add index userName(userName);
alter table users add index password(password);
select * from users where userName like 'username65%' and password like 'password65%'; 执行时间0.06 秒
alter table users2 add index userName_password(userName,password);
select * from users2 where userName like 'username65%' and password like 'password65%’;执行时间0.00秒

上面的结果每台机子测试的结果可能有所不同
原因是在mysql执行查询的时候,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。最左前缀:顾名思义,就是最左优先,打一比方
alter table users add index lname_fname_age(lname,fname,age);
创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)联合索引以及(lname,fname,age)联合索引。

QQ交流群:136351212(满) 455721967

如无特别说明,本站文章皆为原创,若要转载,务必请注明以下原文信息:
转载保留版权:小松博客» MySQL单列索引和组合索引(联合索引)的区别详解
本文链接地址:https://www.phpsong.com/586.html

发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
木有头像就木有JJ!点这里按步骤申请Gravatar头像吧!

网友最新评论 (2)

加载中,请稍候...
  1. 按照你的步骤 不过我数据行为10000000, 但是查询的速率跟你有很大的出入啊, 使用单列索引的时候耗时为0.18, 使用联合索引时耗时0.22.
    abc2016-09-26 12:03:37回复
    • myisam还是innodb得数据
      小松2016-09-26 20:22:22回复