前言
同事去面试被问到了单个索引 和 组合索引谁更快,往往不经意的知识就有可能会被问到,这里以 MySQL 为例,相信被问到这个问题的时候,很多人也不清楚谁比较快,什么情况下会快,下面就让我为大家分析两者的主要区别,供大家参考学习。
首先我们创建第一张 user1 表为组合索引,如下所示:
第二种表为单列索引,如下所示:
创建完表之后我们开始测试
测试单个索引
条件 user_name and sex and student_job_no
EXPLAIN select user_name from user2 where user_name = "张三" and sex = "1" and student_job_no = 'HSBZR01032'
我们发现 3 个单列索引只用上了一个,位置在第三个的,而其他两个索引都没有用到,这是为什么呢?
因为这里涉及到了 MySQL 优化器的优化策略,当多条件组合查询时,优化器会评估用哪个条件的索引效率最高!它会选择最佳的索引去使用,也就是说,使用 user_name 、sex 、student_job_no 这个三个索引查询 ,优化器会判断使用 student_job_no 这一个索引能最高效完成本次的查询,所以 explain 显示的的 key 为 student_job_no 。
条件 sex and student_job_no
EXPLAIN select user_name from user2 where sex = "1" and student_job_no = 'HSBZR01032'
此处条件只有 student_job_no 生效,和前面的一样。
条件 user_name and sex
EXPLAIN select user_name,sex from user2 where user_name = "张三" and sex = "1"
此处条件只有 user_name 生效。
测试组合索引
条件 user_name
EXPLAIN select * from user1 where user_name = "张三"
通过 user_name 查询索引生效。
条件 sex
EXPLAIN select * from user1 where sex = "1"
通过 sex 查询索引无效。
条件 student_job_no
EXPLAIN select * from user1 where student_job_no = "HSBZR01032"
通过 student_job_no 查询索引无效。
条件 user_name and student_job_no
EXPLAIN select * from user1 where user_name ="张三" and student_job_no = "HSBZR01032"
通过 user_name and student_job_no 查询索引有效。
条件 sex and student_job_no
EXPLAIN select * from user1 where sex ="1" and student_job_no = "HSBZR01032"
通过 sex and student_job_no 查询索引无效。
user_name and sex and student_job_no
EXPLAIN select * from user1 where user_name ="张三" and sex ="1" and student_job_no = "HSBZR01032"
通过 user_name and sex and student_job_no 查询索引依然有效。
组合索引的本质
我这里创建了组合索引,为什么有的组合索引可以,有的不可以呢。
这是因为创建(user_name , sex , student_job_no )组合索引时,其实是相当于分别建立了下面三组组合索引:
[ user_name ,sex ,student_job_no ] 、[ user_name ,sex ]、[ user_name ]
这三个组合索引当中,为什么没有 sex ,student_job_no 等这样的组合索引呢?这是因为 MySQL 组合索引“最左前缀”的结果。
最左前缀
简单的理解就是只从最左边的开始组合。组合索引的第一个字段必须出现在查询组句中,并且不能跳跃,这个索引才会被用到,因此并不是只要包含这三列的查询都会用到该组合索引。
下面的几个 SQL 就会用到组合索引:
select * from user1 where user_name="张三" AND sex="1"
select * from user1 where user_name="张三"
而下面几个则不会用到:
select * from user1 where sex ="1" and student_job_no = "HSBZR01032"
select * from user1 where student_job_no = "HSBZR01032"
这里需要注意一点就是:索引的字段可以是任意顺序的
比如以下的 SQL 都会使用到索引:
EXPLAIN select * from user1 where user_name ="张三" and sex ="1"
EXPLAIN select * from user1 where sex ="1" and user_name ="张三"
MySQL创建联合索引的规则是首先会对联合合索引的最左边的,也就是第一个字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个字段进行排序。
查询时间
单列索引时间:
select * from user2 where user_name = "张三" and sex = "1" and student_job_no = 'HSBZR01032'
受影响的行: 0
时间: 0.004s
组合索引时间:
select * from user1 where user_name ="张三" and sex ="1" and student_job_no = "HSBZR01032"
受影响的行: 0
时间: 0.005s
给三个列加上索引,不管是单列索引还是组合索引,查询时间都是相差不大。
但是如果组合索引没有按照 “最左前缀” 规则实现,比如以下 SQL :
select * from user1 where sex ="1" and student_job_no = "HSBZR01032"
受影响的行: 0
时间: 0.046s
查询时间会比单列索引时间要长。
组合索引优势
组合索引 比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。