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

Wesley13
• 阅读 692

发现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表

  1. CREATE TABLE `users` (
  2. `userID` int(11) NOT NULL AUTO_INCREMENT,
  3. `userName` varchar(20) NOT NULL,
  4. `password` varchar(20) NOT NULL,
  5. PRIMARY KEY (`userID`)
  6. )

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

  1. delimiter $
  2. create procedure genUsers()
  3. begin
  4. declare i int default 0;
  5. while i < 100000 do
  6. set i = i + 1;
  7. insert into users(userID,userName,`password`) values(i,concat('username',i),concat('password',i));
  8. end while;
  9. end $
  10. delimiter ;

执行存储过程

  1. call genUsers();

复制表结构和数据

  1. CREATE TABLE users2 SELECT * FROM users;

添加userName,password单列索引

  1. alter table users add index userName(userName);

  2. alter table users add index password(password);

  3. select * from users where userName like 'username65%' and password like 'password65%'; 执行时间0.06 秒

  4. alter table users2 add index userName_password(userName,password);

  5. 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

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
添砖java的啾 添砖java的啾
3年前
distinct效率更高还是group by效率更高?
目录00结论01distinct的使用02groupby的使用03distinct和groupby原理04推荐groupby的原因00结论先说大致的结论(完整结论在文末):在语义相同,有索引的情况下groupby和distinct都能使用索引,效率相同。在语义相同,无索引的情况下:distinct效率高于groupby。原因是di
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Peter20 Peter20
3年前
什么是索引?Mysql目前主要的几种索引类型
一、索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。创
Wesley13 Wesley13
3年前
MySQL千万级别优化·中
MySQL千万级别的查询优化手段·中单列索引(假设在v\_record表中存在id列的索引)1、WHERE条件使用​EXPLAINSELECT\FROMv\_recordWHEREid2​结论:利用索引进行回表查询2、SELECT字段使用
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
3年前
MySQL单列索引和组合索引(联合索引)的区别详解 – 小松博客
发现indexmerge局限性,优化器会自动判断是否使用indexmerge优化技术,查询还是需要组合索引【推荐阅读:对mysql使用索引的误解(https://www.oschina.net/action/GoToLink?urlhttp%3A%2F%2Fwww.phpsong.com%2F2753.html)】MySQL单列索引
Stella981 Stella981
3年前
ELK学习笔记之ElasticSearch的索引详解
0x00ElasticSearch的索引和MySQL的索引方式对比Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。倒排索引很多地方都有介绍,但是其比关系型
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究