总结三道MySQL联合索引面试题

目录

前言：

众所周知MySQL联合索引遵循最左前缀匹配原则，在少数情况下也会不遵循（有兴趣，可以翻一下上篇文章）

创建联合索引的时候，建议优先把区分度高的字段放在第一列。

至于怎么统计区分度，可以按照下面这种方式。

创建一张测试表，用来测试：

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='测试表';

统计每个字段的区分度：

select
    count(distinct a)/count(*),
    count(distinct b)/count(*),
    count(distinct c)/count(*)
from test;

值越大，区分度越高，优先放在第一列。

很多人不知道联合索引在B+树中是怎么存储的？我简单画一下。

比如在（a,b）字段上面创建联合索引，存储结构类似下面这样：

叶子节点存储全部数据，用双链表指针相连，数据都是先按a字段排序，a字段的值相等时再按b字段排序。

a字段的值是全局有序的，分别有1,1,1,2,2,2。

b字段的值是全局无序的，分别有1,3,5,1,3,5，只有在a字段的值相等时才呈现出局部有序。

所以在进行SQL查询的时候，如果where条件中没有a字段，只有b字段，是无法用到索引的，像下面这样：

select * from test where b=1;

像有些文章上面说的，在（a,b）两个字段上创建联合索引，就会创建两个索引，分别是（a）和（a,b），这其实是一种不恰当的表述，虽然结果是对的。

下面做几道联合索引的经典面试题，试一下大家掌握的怎么样？

第一题：

下面这条SQL，该怎么创建联合索引？

SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;

你以为的答案是（a,b,c），其实答案是6个，abc三个的排列组合，（a,b,c）、（a,c,b）、（b,a,c）、（b,c,a）、（c,a,b）、（c,b,a）。

MySQL优化器为了适应索引，会调整条件的顺序。

再给面试官补充一句，区分度高的字段放在最前面，大大加分。

第二题：

下面这条SQL，该怎么创建联合索引？

SELECT * FROM test WHERE a = 1 and b > 1 and c = 1;

考察的知识点是： 联合索引遇到范围匹配会停止，不会再匹配后面的索引字段。

所以答案应该是：（a,c,b）和 （c,a,b）。

当创建（a,c,b）和 （c,a,b）索引的时候，查询会用到3个字段的索引，效率更高。

怎么判断是用到了3个字段的索引，而不是只用到前两个字段的索引呢？

有个非常简单的方法，看执行计划的索引长度。

由于int类型的字段占4个字节，3个字段长度刚好是12个字节。

第三题：

下面这条SQL，该怎么创建联合索引？

SELECT * FROM test WHERE a in (1,2,3) and b > 1;

答案是（a,b）。in条件查询会被转换成等值查询，可以验证一下：

可以看到用到了两个字段的索引。

所以我们在平时做开发，尽量想办法把范围查询转换成in条件查询，效率更高。

到此这篇关于总结三道MySQL联合索引面试题的文章就介绍到这了,更多相关MySQL联合索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 总结三道MySQL联合索引面试题

    总结三道MySQL联合索引面试题

    目录 前言: 众所周知MySQL联合索引遵循最左前缀匹配原则,在少数情况下也会不遵循(有兴趣,可以翻一下上篇文章) 创建联合索引的时候,建议优先把区分度高的字段放在第一列. 至于怎么统计区分度,可以按照下面这种方式. 创建一张测试表,用来测试: CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `a` int NOT NULL, `b` int NOT NULL, `c` int NOT NULL, PRIM

  • MySQL联合索引用法示例

    MySQL联合索引用法示例

    本文实例讲述了MySQL联合索引.分享给大家供大家参考,具体如下: 员工表 userid 部门表 deptid 员工部门表 条件:一个员工可以对应多个部门 问题:怎么样设置数据库,让其不能重复添加 userid 和deptid? uuid userid deptid 111 212 311(这个就不能让其添加) DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dep

  • MySQL联合索引功能与用法实例分析

    MySQL联合索引功能与用法实例分析

    本文实例讲述了MySQL联合索引功能与用法.分享给大家供大家参考,具体如下: 联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用

  • 深入浅析Mysql联合索引最左匹配原则

    前言 之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,最近面试时和面试官交流,发现遗漏了些东西,这里自己整理一下这方面的内容. 最左前缀匹配原则 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例: 对列col1.列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1,col2,col3); 联合索引 test_col1_col2_col3 实际建

  • mysql联合索引的使用规则

    mysql联合索引的使用规则

    联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 从一道有趣的题目开始分析: 假设某个表有一个联合索引(c1,c2,c3,c4)以下选项哪些字段使用了该索引: A where c1=x and c2=x and c4>x and c3=x B w

  • mysql 联合索引生效的条件及索引失效的条件

    mysql 联合索引生效的条件及索引失效的条件

    目录 1.联合索引失效的条件 2.索引失效的条件 1.联合索引失效的条件 联合索引又叫复合索引.两个或更多个列上的索引被称作复合索引. 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单

  • MySQL联合索引遵循最左前缀匹配原则

    MySQL联合索引遵循最左前缀匹配原则

    面试官: 我看你的简历上写着精通MySQL,问你个简单的问题,MySQL联合索引有什么特性? 心想,这还不简单,这不是问到我手心里了吗? 听我给你背一遍八股文! 我: MySQL联合索引遵循最左前缀匹配原则,即最左优先,查询的时候会优先匹配最左边的索引. 例如当我们在 (a,b,c) 三个字段上创建联合索引时,实际上是创建了三个索引,分别是(a).(a,b).(a,b,c). 查询条件中包含这些索引的时候,查询就会用到索引.例如下面的查询条件,就可以用到索引: select * from tab

  • MySQL 联合索引与Where子句的优化 提高数据库运行效率

    网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了.涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火~~然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我确定了不是机器配置的问题,"深圳-枪手"热心人他的机器512内存过百W的数据里也跑得飞快,甚至跟那些几W块的机器一样牛(吹过头了),呵呵~~~ 在群友的分析指点下,尝试把排序.条件等一个一个去除来做测试,结果发现问题就出在排序部分,去除排序的时候,执行时间由原来的48秒变成0.3x秒,这是

  • Mysql数据库索引面试题(程序员基础技能)

    Mysql数据库索引面试题(程序员基础技能)

    引言 索引是Mysql的一块硬骨头,但是对于程序猿来说又是十分重要的基础技能.在平常的项目开发中,它是重要的SQL优化手段.在求职面试中,它是面试官常常用来考察求职者数据库性能优化方面的重要考量.因此透彻的掌握索引原理,并能够将其运用到数据库查询实战是每个程序猿必备的能力.本文将从索引原理.索引设计原则方面阐述Mysql索引.相信阅读完本文之后,在Mysql索引查询数据理解这块完全可以征服阿里面试官.准备好了吗?我们发车了. 索引原理 在进行索引设计以及优化之前,我们先深入理解下索引的原理.因为

  • 三道MySQL新手入门面试题,通往自由的道路

    三道MySQL新手入门面试题,通往自由的道路

    目录 1. 讲讲你认识MySQL锁吧 2. 你知道什么是事务.四大特性.隔离级别吗? 3. MyISAM 和 InnoDB 存储引擎的区别 总结 1. 讲讲你认识MySQL锁吧 对于数据库来讲,读写都是非常频繁的吧,在并发量来的时候,在进行读写操作时,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,所以锁就可以在一定限度保护它的一致性. 首先我们可以按锁的粒度分: 1.表级锁:它上锁是锁住的整张表,当下一个事务来访问的时候,必须等到当前事务把锁释放了,才能对表进行操作访问. 特点:表

随机推荐