总结三道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= 测试表
统计每个字段的区分度:
selectcount(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答案是(a,b)。in条件查询会被转换成等值查询,可以验证一下:
可以看到用到了两个字段的索引。
所以我们在平时做开发,尽量想办法把范围查询转换成in条件查询,效率更高。
到此这篇关于总结三道MySQL联合索引面试题的文章就介绍到这了,更多相关MySQL联合索引内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 总结三道MySQL联合索引面试题
相关文章
- mysql中字符转数字,MYSQL字符数字转换为数字「建议收藏」
- MySQL中处理大字段的方法(mysql大字段)
- MySQL列名书写技巧:如何使用中文(mysql列名中文)
- 比较:Visual Studio 与MySQL 链接(vs链接mysql)
- C如何连接MySQL数据库?(c怎么连接mysql)
- Mysql 建立复合索引:提升查询效率(mysql建复合索引)
- 什么MySQL添加索引的命令:详解(mysql添加索引命令是)
- MySQL如何利用索引加快查询速度(mysql使用索引查询)
- 证明自己挑战 MySQL 二级考试(mysql二级考试)
- MySQL如何利用前缀索引提升效率(mysql创建前缀索引)
- MySQL: 如何优化索引的删除速度(mysql删除索引很慢)
- MySQL语句如何分页?一步步教你实现!(Note: This article is written in Simplified Chinese. The title reads How to paginate MySQL statements? Let us guide you step by step! in English.)(分页mysql语句)
- MySQL实现纵向分表的技巧(mysql纵向分表)
- MySQL字段索引实现数组功能(mysql字段数组)
- MySQL索引:提升查询效率的秘诀(mysql什么索引)
- MySQL中添加索引的语句示例(mysql加索引的语句)
- 如何应对Mysql索引变慢的问题?——25个字的技巧总结(mysql索引变慢)
- MySQL添加新列到表中(mysql给表增加列)
- MySQL外部命令的使用方法(mysql中使用外部命令)
- MySQL中每行数据最多可以包含多少列(mysql中一行有多少列)
- MySQL中使用Key索引优化查询操作(mysql中key索引)
- MySQL中的B索引提高数据库查询效率的重要工具(mysql中b 索引)
- API 存储至MySQL数据库的极速汇总(api 存到 mysql)
- 在CMD下使用MySQL连接到数据库的步骤(cmd 下连接mysql)
- 如何在MySQL中删除索引文件(mysql中删除索引文件)
- 教你轻松创建MySQL聚合索引(mysql中创建聚合索引)
- 如何在MySQL中创建前缀索引(mysql中创建前缀索引)
- MySQL三级索引优化查询速度(mysql三级索引)
- MySQL的索引有哪些区别(mysql三种索引区别)
- MySQL常用索引解析(mysql一般索引)
- MySQL运行缓慢看看你的数据库中是否有未使用索引的情况(mysql不用索引情况)
- 无索引, MySQL效能急剧下降(mysql 不用索引)