MySQL常见面试题

devtools/2024/11/18 0:15:17/

MySQL

优化

  • 定位慢查询

  • SQL执行计划 ：SQL分析能力

  • SQL优化经验：SQL优化

  • 索引

    • 存储引擎
    • 索引底层数据结构
    • 聚簇和非聚簇索引
    • 索引创建原则
    • 索引失效场景

其他

  • 事务相关：事务特性 隔离级别 MVVC
  • 主从同步原理：高并发
  • 分库分表：海量数据存储

一.索引

1.什么是索引?

索引是MySQL 高效获取数据的数据结构(有序)

在数据之外,数据库系统还维护着满足特定查找算法的数据结构(B+树),这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法.

2.索引创建的原则

  • 先陈述自己在工作中是如何运用的
  • 主键索引
  • 唯一索引
  • 根据业务创建索引(复合索引)

1.针对数据量比较大,且查询比较频繁的表建立索引

单表如果超过10w数据 (增加用户体验)

目的就是查询表的时候速度更快

2.针对常作为 查询条件的 where order by 分组group by 操作的字段建立索引

3.尽量去选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高

image-20241109175557394

这里的区分度就不高,所以尽量不使用这种字段作为索引

4.如果是字符串,字段的长度较长,可以针对于字段的特点,建立前缀索引

image-20241109175730647

5.尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引(避免回表),节省存储空间,提高查询效率

image-20241109175938028

6.需要控制索引的数量,索引并不是多多益善,索引越多,维护成本就越大,从而会影响增删改的效率

7.如果**索引列不能存储null值,请在创建表时使用NOT NULL 去约束它,**当优化器知道每列是否包含NULL值时,可以更好的确定哪个索引最有效地用于查询.

image-20241109180601754

总结:
  1. 数据量大且查询比较频繁的表
  2. 常作为查询条件的 字段
  3. 字段内容区分度比较高
  4. 内容比较长 使用前缀索引
  5. 尽量联合索引
  6. 要控制索引的数量
  7. 如果索引不能存储null值,在创建表时使用NOT NULL去约束它

3索引的底层数据结构是什么?

B+树

MySQL的InnoDB引擎采用的是B+树的数据结构来存储索引

  • 阶树更多,路径更短
  • 磁盘读写代价B+树更低, 非叶子结点只存储指针,叶子结点存储数据
  • B+树 便于扫库 和区间查询,叶子结点是一个 双向链表

image-20241109171615817

数据结构对比

B树 是一种多叉路衡查找树,相当于二叉树,B树每个节点可以有多个分支,多叉.

image-20241109171934730

4.B+树:

在B树的基础上的一种优化,更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构

image-20241109172328787

  • 区别在于

非叶子结点只存储指针不存储数据

叶子结点才会真正的去存储数据

  1. 磁盘读写代价B+树更低
  2. 查询效率B+树更加稳定
  3. B+树便于扫库和区间查询

image-20241109172656885

结点直接存在双向指针,相当于查询6 他可以一次性把所有的数据都拿到

总结:
  • 索引是帮助MySQL高效获取数据的数据结构
  • 是为了提高数据检索的效率,降低数据库的IO成本(不需要全表扫描)
  • 通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗

5.什么是聚簇索引什么是非聚簇索引? 什么是回表?

什么是聚集索引,什么是二级索引(非聚簇索引)

什么是回表?

  • 聚餐索引

将 数据存储与 索引放到一块,索引结构的叶子节点保存了行数据,必须有,而且只有一个

  • 二级索引

将 数据与 索引分开存储,索引结构的叶子节点关联的对应的主键, 可以存在多个

  • 聚簇索引选取规则:
  1. 如果存在主键,主键索引就是聚集索引
  2. 如果不存在主键,将使用唯一 UNIQUE 索引 作为聚集索引
  3. 如果表没有主键,或者没有合适的唯一索引,InnoDB会自动生成一个rowid作为隐藏的聚集索引

image-20241109174447865

  • 回表查询

先通过二级索引找到对应的主键值,拿到主键再到聚集索引中查询出整行的数据,这个过程叫做回表查询

image-20241109174816080

总结:
  • 聚集索引(聚簇索引):数据与索引放到一块,B+树的叶子节点保存的整行的数据,有且只有一个
  • 二级索引:数据与索引分开存储,B＋树的叶子结点上存储的是对应的 主键,可以有多个
  • 什么是回表查询?
    • 通过二级索引找到对应的主键值,然后再到聚簇索引中查询出整行的数据,这个过程就是回表查询

image-20241109175137427

6.什么情况下索引会失效?

image-20241109180725514

7.如何去快速判断索引是否失效呢

执行计划explain

name status address

  • 违法了最左前缀法则

image-20241109181033191

失效的情况:

image-20241109181123603

image-20241109181227346

  • 范围查询右边的列不能使用索引

image-20241109181452261

  • 不要在索引列上进行运算操作,索引将失效

image-20241109181550212

这里使用的substring进行了运算操作,且在索引列上

  • 字符串不加单引号,造成索引失效

image-20241109181739159

查询的时候,没有对字符串加单引号,MySQL 查询优化器,会自动的进行类型转换, 造成索引失效

  • 模糊查询有可能会导致索引失效

image-20241109182040387

总结:
  1. 违反了最左前缀法则: 使用复合索引,不能跳过某一列去查询
  2. 范围查询右边的列,不能使用索引
  3. 不要在索引上进行运算操作,索引会失效
  4. 字符串不加单引号,造成索引失效(类型转换)
  5. 以百分号% 开头的Like模糊查询,索引失效

image-20241109182432918

二.MySQL中，如何定位慢查询

  • 聚合查询
  • 多表查询
  • 表数据量过大查询
  • 深度分页查询

表象：页面加载过慢、接口压测响应时间过长

方案一：开源工具

  • 调试工具：Arthas
  • 运维工具：Skywalking Prometheus

image-20241109164438036

方案二：MySQL自带慢日志查询

执行慢的SQL执行在日志文件中

默认MySQL没有开启我们只需要在配置文件中开启就行

/etc/my.cnf

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢查询的时间为2s SQL语句执行时间超过2s
long_query_time=2
# 配置完毕之后，通过指令重启MySQL服务器进行测试，查看慢日志文件中的记录
/var/lib/mysql/localhost-slow.log

image-20241109165108311

总结：
  1. 介绍当时问题问题产生的场景
  2. 我们系统中采用了skywalking 可以检测出哪个接口，最终原因是sql的问题
  3. 在mysql中开启慢查询日志 ，一旦sql查询超过2s就会记录到日志中

image-20241109165318072

三.sql语句执行的很慢，如何分析

SQL语句执行的很慢如何去分析呢？

  • 聚合查询
  • 多表查询
  • 表数据量过大查询： 添加索引
  • 深度分页查询

前三个可以通过 SQL执行计划找到慢的原因

EXPLAIN DESC命令获取到MySQL如何执行SELECT 语句信息

直接在select语句之前加上 explain/desc

image-20241109165633567

这些字段不需要全部掌握

  • possible_key :当前sql可能会使用到的索引
  • key 当前sql 实际命中的索引
  • key_len 索引占有的大小
  • Extra 额外的优化建议

通过查看是否命中索引就是看 key 和key_len

image-20241109165857538

出现了Using index condition 说明索引的使用是有优化的空间的

  • type sql连接的类型 null system const eq_ref ref range index all

越往左边性能越好

system :查询系统中的表

const:根据主键查询

eq_ref:主键索引查询或者唯一索引查询

ref:索引查询

range :范围查询

index:索引树扫描

all:全盘扫描

总结:

如果 SQL执行很慢,如何分析

  1. 我们可以采用MySQL自带的分析工具Explain
  2. 通过查看key 和key_len检查是否命中了索引 (索引本身存在是否失效的问题)
  3. 通过查看 type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或者全盘扫描
  4. 通过extra建议来判断,是否出现了回表的情况,如果出现了可以添加索引或者修改返回字段来修复

四.谈谈SQL优化经验

  • 表的设计优化
  • 索引优化 ： 参考优化创建原则和索引失效的原则
  • SQL语句优化
  • 主从复制、读写分离
  • 分库分表 后面专门章节介绍

1.表设计的优化

参考阿里开发手册 嵩山版本

2.SQL语句优化

image-20241109195133293

  1. 避免使用select* 有可能会造成回表查询

  2. SQL语句要避免索引失效的写法

  3. 尽量 使用 union all 代替 union， union会多一次过滤，效率低

    image-20241109195417199

  4. 避免在where子句中对字段进行表达式操作，例如substring 索引失效的时候讲到过 在索引字段上进行表达式操作

  5. Join 优化 能用 inner join 就不用 left join right join，如果必须使用一定要以小表驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里面。 left join 或 right join ，不会重新调整顺序

比如说：

image-20241109195730801

外面的是小循环是小表，里面大循环是大表 3次连接1000次操作 和1000次连接3次操作

image-20241109195905932

3.主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作造成的性能影响，可以采用读写分离的架构，为了解决数据库的写入，影响了查询的效率

image-20241109200230634

在生成环境下 经常主从复制 读写分离

总结：
  1. 表设计优化，数据类型选择 char varchar
  2. 索引优化
  3. sql语句优化，避免索引失效，避免使用select *
  4. 主从复制，读写分离，不让数据的写入，影响读操作
  5. 分库分表

五.主从同步原理

image-20241109200542391

数据库通过中间件连接了两个数据库，主库从库，主库负责写数据 从库负责读数据，那么如何进行同步的呢 原理是什么？

主从复制的核心就是二进制日志

二进制日志 binlog记录了所有的DDL 数据库定义语言，和DML 数据库操作语言，但不包括数据的查询 SELECT SHOW 语句

IOthread线程专门负责去读取 主库的binlog日志，读取完成之后就写入到从库的中继日志中relay log ,然后再由从库的SQLthread`线程去读取 中继日志的文件，然后再去执行 这样主库和从库的数据就保持了一致

image-20241109201125674

  1. Master主库在事务提交的时候，会把数据变更记录在二进制日志文件Binlog中。
  2. 从库读取主库的 binlog，写入到从库的中继日志Relay log
  3. slave重做中继日志中的事件，将改变反映成自己的数据。

image-20241109201431565

六.分库分表

image-20241109201823614

  • 前提： 单表数据量达到 1000w 和 20G
  1. 项目业务数据逐渐增多，或业务发展比较迅速
  2. 优化已经解决不了性能问题（主从读写分离，查询索引）
  3. IO瓶颈（磁盘IO 网络IO），CPU瓶颈（聚合查询，连接数太多）

1.拆分策略

垂直拆分
  1. 垂直分库
  2. 垂直分表
  • 垂直分库

以表为依据，根据业务将不同表拆分到不同的库中。

image-20241109202213391

  • 垂直分表

以字段为依据，根据字段属性将不同字段拆分到不同表中

拆分规则

  1. 将不常用的字段单独放在一张表中
  2. 将text blog等大字段拆分出来放在附表中

比如感兴趣才把详情展示给用户

image-20241109202550024

可能是在同一数据库下拆分成两张表

核心就是

  1. 冷热数据分离
  2. 减少IO 过度争抢，两表互不影响
水平拆分
  1. 水平分库：将一个库中的数据拆分到多个库中
  2. 水平分表：将一个表的数据拆分到多个表中（可以在同一个数据库）

image-20241109203129859

路由规则

  • 按照id进行取模
  • 按id就是路由范围

image-20241109203216650

多个库共同去存储业务的数据，海量数据存储问题，多个节点也能解决高并发的问题

水平分表

image-20241109203450418

2.分库产生的问题

  • 分布式事务一致性问题
  • 跨界点关联的问题
  • 跨界点进行分页，排序函数
  • 主键避冲：比如水平分库 每个库中都存储相同的表 每个表中的数据都是自增的

为了解决增加分库分表的中间件

  1. MyCat
  2. sharding-sphereimage-20241109204115456

image-20241109204132724

七.事务(重要)

1.事务的特性是什么 ?

什么是事务?

事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交活撤销操作,这些操作要么同时成功,要么同时失败

ACID:

  • 原子性:Atomicity:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
  • 一致性:Consistencey:事务完成时,必须所有数据保持一致状态
  • 隔离性:Isolation:数据库系统提供的隔离机制,保证事务在不受外部并发影响的独立环境下运行
  • 持久性:Durability:事务一旦提交或者回滚,他对数据库的改变就是永久的

image-20241111210301600

2.并发事务带来了哪些问题?怎么解决?MySQL的默认隔离级别是?

  • 并发事务问题: 脏读 不可重复读 幻读
  • 隔离界别:读未提交 读已提交 可重复读 串行化

3.MySQL并发事务问题:

  • 脏读:一个事务读到了另外一个事务还没有提交的数据

image-20241111210648994

事务A还没有提交但是事务B可以读到最新事务修改之后的数据, 一个事务读到了另外一个事务还没有提交的数据

  • **不可重复读:**一个事务先后读取同一条记录,但是两次读取的数据不同,称之为不可重复读.

image-20241111210916823

  • 幻读:一个事务按照条件查询的时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 幻影

image-20241111211321982

前提是已经解决了不可重复读的问题

一个事务内,查询同一条数据都是相同的数据 (1 ,4 相同) 但是执行3插入的时候依然提示存在这个数据这就像出现了幻觉一样

4.MySQL事务隔离界别:

image-20241111211622911

  • 未提交度 (一般不用)
  • 读已提交: 可以解决脏读 不可重复读 幻读
  • 可重复读: MySQL默认隔离级别 可以解决不可重复读,脏读 不能解决幻读 Repeatable Read
  • 串行化: 可以解决所有问题,但是性能很低

事务隔离界别越往下,安全级别越高,性能越低

八.存储引擎

1.MySQL体系结构

存储引擎控制的就是MySQL数据的存储和提取的方式 ,服务器通过API和存储引擎来进行通信进行交互,Index索引是在存储引擎层实现的,不同引擎存储的结构是不一样的

InnoDB是MySQL 5.5版本之后默认的存储引擎

MySQL体系结构

存储引擎简介:

存储引擎特点:

存储引擎选择:

image-20241111213038651

image-20241111213100700

2.存储引擎简介:

什么是存储引擎?

默认存储引擎 : InnoDB

相当于发动机是一个机器的核心

存储引擎就是存储数据,建立索引,更新/查询数据等技术的实现方式,存储引擎是基于表而不是基于库的,所以存储引擎也可以被称为表类型

一个数据库中的多张表可以选择不同的存储引擎

查看当前数据库支持的存储引擎

show engines;

image-20241111214315405

InnoDB 默认存储引擎 支持事务 支持外键

image-20241111214513964

3.InnoDB

MySQL中InnoDB是一种兼顾了高可靠性和高性能的通用存储引擎, 5.5 之后作为 默认存储引擎

  • DML操作支持ACID,和支持事务
  • 行级锁,提高并发访问性能
  • 支持外键 FOREIGH KEY,保证数据的完整性和正确性

文件

image-20241111214930787

image-20241111215526309

col 值得是一个一个的字段

image-20241111215654647

4.MyISAM

MyISAM是MySQL早期的默认存储引擎

  • 不支持事务,不支持外键
  • 支持表锁,不支持行锁
  • 访问速度快

image-20241111215925124

5.Memery

Memory 引擎的表数据是存储在内存中的,由于受到硬件问题,或断电问题的影响,只能将这些表作为临时表或缓存使用

  • 内存存放
  • hash索引(默认)

image-20241111220232938

6.区别:

image-20241111220321568

如何选择:

image-20241111220736384

image-20241111221200415


http://www.ppmy.cn/devtools/134830.html

相关文章

核心期刊论文供参考（中科院三区、可转）

核心期刊论文供参考（中科院三区、可转）

SC20243269 多供应商电力网络，电力中心模式，排兵布阵优化算法，电力储备工具，负载请求程序 SC20243268 简化复杂的能源枢纽模型：大逃杀优化的多阶段方法 SC20243266 城市环境中提高效率的蓄能冷热电联产系统的…
阅读更多...
AR眼镜方案_AR智能眼镜阵列/衍射光波导显示方案

AR眼镜方案_AR智能眼镜阵列/衍射光波导显示方案

在当今AR智能眼镜的发展中，显示和光学组件成为了技术攻坚的主要领域。由于这些组件的高制造难度和成本，其光学显示模块在整个设备的成本中约占40%。 采用光波导技术的AR眼镜显示方案，核心结构通常由光机、波导和耦合器组成。光机内的微型显示…
阅读更多...
FPGA 第8讲 简单组合逻辑--半加器

FPGA 第8讲 简单组合逻辑--半加器

时间：2024.11.16 一、学习内容 1.半加器 数字电路中加法器是经常用到的一种基本器件，主要用于两个数或者多个数的加和，加法器又分为半加器（half adder）和全加器（full adder）。 半加器电路是指…
阅读更多...
go-bindata

go-bindata

go bindata 在项目中引用了静态资源时，项目打包后，需要保证包与静态资源的相对目录不变。bindata可以将静态资源生成.go文件，在打包时会嵌入到包中，非常好用。 安装 需要让bindata下载到GOPATH/bin目录下，在项目外执…
阅读更多...
【会话文本nlp】对话文本解析库pyconverse使用教程版本报错、模型下载等问题解决超参数调试

【会话文本nlp】对话文本解析库pyconverse使用教程版本报错、模型下载等问题解决超参数调试

前言： 此篇博客用于记录调用pyconverse库解析对话文本时遇到的问题与解决思路，以供大家参考。 文章目录 pycoverse介绍代码github链接问题解决1 [cannot import name ‘cached_download‘ from ‘huggingface_hub‘ 问题解决](https://blog.csdn.net/wei…
阅读更多...
羊城杯2020Easyphp

羊城杯2020Easyphp

审题 看到url，可以想到伪协议读取 尝试过后可以发现，题目绕过了read后面的编码 我们可以尝试双重urlencode进行绕过 ?filephp://filter/read%25%36%33%25%36%66%25%36%65%25%37%36%25%36%35%25%37%32%25%37%34%25%32%65%25%36%32%25%36%31%25%37%33%…
阅读更多...
Scala的Set集合

Scala的Set集合

//设置一个Book。有三个属性：书名，作者，价格 class Book(var bookNmame:String,var author:String,var price:Double){} object demo7 {def main(args: Array[String]): Unit { // val set1 Set(1,1,2,3,3) // println(set1)//创建可…
阅读更多...
1+X应急响应（网络）系统加固：

1+X应急响应（网络）系统加固：

系统加固： 数据库的重要性： 数据库面临的风险： 数据库加固： 业务系统加固： 安全设备加固： 网络设备加固：
阅读更多...
最新文章

Copyright @ 2022~2023