首页 > 数据库 >三道MySQL联合索引面试题,淘汰80%的面试者,你能答对几道

三道MySQL联合索引面试题,淘汰80%的面试者,你能答对几道

时间:2022-08-23 10:46:38浏览次数:70  
标签:count 面试题 80% 索引 字段 联合 MySQL test 区分度

众所周知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条件查询,效率更高。

> 文章持续更新,可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。

标签:count,面试题,80%,索引,字段,联合,MySQL,test,区分度
From: https://www.cnblogs.com/yidengjiagou/p/16615287.html

相关文章

  • maven工程(二)运行环境修改和获取mysql数据库数据
    运行环境修改可以加上这个进行定义,你tomcat运行的端口<build><plugins><plugin><!--https://mvnrepository.com/artifact......
  • [Oracle] LeetCode 1802 Maximum Value at a Given Index in a Bounded Array
    Youaregiventhreepositiveintegers:n,index,andmaxSum.Youwanttoconstructanarraynums(0-indexed)thatsatisfiesthefollowingconditions:nums.len......
  • MySQL和Navicat安装过程记录Win10
    安装MySQL双击下载好的文件点击Execute,等待绿勾后Finish验证配置环境变量以在命令行使用Win+R,输入cmd回车,输入mysql-uroot-p登录成功 ......
  • 基于StatefulSet控制器运行MySQL一主多从
    1、下载相关镜像并上传到harbor私有仓库拉取官网镜像nerdctlpullregistry.cn-hangzhou.aliyuncs.com/hxpdocker/xtrabackup:1.0nerdctlpullmysql:5.7.36对镜像tag......
  • 面试题:如何保证HTTP接口的安全性
    首先应该考虑使用https协议,因为http协议是不安全的,一般来说购买服务器的时候厂商都会送免费的https的ssl证书,只需要在nginx配置就可以了。接口应该开启加密,分为对称加密......
  • 480. 滑动窗口中位数
     难度困难383收藏分享切换为英文接收动态反馈中位数是有序序列最中间的那个数。如果序列的长度是偶数,则没有最中间的数;此时中位数是最中间的两个数的平均数。例如......
  • mysql部分--安装mysql 8.0以上版本
    安装mysqlmysql本质上是一个软件一、mysql安装1.下载链接:https://downloads.mysql.com/archives/community/2.先安装windows补丁[百度网盘下载](链接:https://pan.baid......
  • Docke 搭建 apache2 + php8 + MySQL8 环境
    Docker安装执行Docker安装命令curl-fsSLhttps://get.docker.com/|sh启动Docker服务sudoservicedockerstart查看Docker是否正常工作sudo......
  • SYSDATE() vs NOW() in MySQL
    SYSDATE() returnsthetimeatwhichitexecutes.返回实际的执行时间。NOW() returnsaconstanttimethatindicatesthetimeatwhichthe statement begant......
  • MySQL高可用-gtid
    课前回顾MySQL高可用MHA#!/bin/bashmysql_conn='mysql-uuser-ppwd-h127.0.0.1-P2345'mha_log_file="/etc/mha/app1/manager.log"down_master_ip=`sed-nr's#^mh......