单个索引和组合索引(联合索引)谁效率高 | 您所在的位置:网站首页 › libev和libevent哪个效率高 › 单个索引和组合索引(联合索引)谁效率高 |
前言
同事去面试被问到了单个索引 和 组合索引谁更快,往往不经意的知识就有可能会被问到,这里以 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'
我这里创建了组合索引,为什么有的组合索引可以,有的不可以呢。 这是因为创建(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查询时间会比单列索引时间要长。 组合索引优势组合索引 比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。 |
CopyRight 2018-2019 实验室设备网 版权所有 |