MySQL面试题

大约 17 分钟约 5095 字全民制作人ikun

MySQL面试题

基础

MySQL执行流程

MYSQL执行流程如下：

建立连接：客户端与MYSQL服务器建立连接，涉及TCP/IP握手以及身份验证
查询缓存：查询语句可能会被查询缓存命中，如果之前执行过相同的查询，并且结果没有发生变化，则直接从缓存中返回结果
解析SQL：数据库系统通过解析器对SQL查询语句进行词法分析和语法分析，构建语法树
执行SQL：
- 预处理阶段（Preprocessing）：检查表和字段是否存在，将查询中的符号扩展为实际的列。
- 优化阶段（Query Optimization）：数据库系统根据查询的成本考虑选择最优的执行计划。
- 执行阶段（Query Execution）：数据库系统根据优化器选择的执行计划执行SQL查询，将符合条件的结果返回给客户端。

索引

了解过索引吗

索引(index)是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构(B+树)，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。

MYSQL底层采用的数据结构是B+树

B-Tree树：

B+ Tree

了解过索引吗？

索引(index)是帮助小ySQL高效获取数据的数据结构（有序）
提高数据检索的效率，降低数据库的O成本（不需要全表扫描）
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
索引的底层数据结构了解过嘛？
MySQL的InnoDB引擎采用的B+树的数据结构来存储索引
阶数更多，路径更短
磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
B+树便于扫库和区间查询，叶子节点是一个双向链表

聚簇索引

聚簇索引（Clustered Index）是一种特殊类型的索引，它改变了数据存储的物理结构以优化性能。在聚簇索引中，索引的键值决定了数据行在磁盘上的物理存储顺序，因此数据行的存储顺序与索引的顺序一致。

聚集泰l(Clustered Index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有，而且只有一个
二级索引(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个
聚簇索引的选取规则：
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB:会自动生成一个rowid作为隐藏的聚集索引。

回表查询
回表查询是指在使用覆盖索引（Covering Index）时，虽然索引已经包含了查询需要的所有列，但为了获取完整的结果集，数据库仍然需要通过主键或者聚簇索引回到数据表中进行进一步的查询。这种额外的查询操作被称为回表查询。

面试题回答：
什么是聚簇索引什么是非聚簇索引？

聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
非聚簇索引（二级索）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个
知道什么是回表查询嘛？
通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

覆盖索引

覆盖索引（Covering Index）是一种特殊类型的索引，它包含了查询所需的所有列，可以完全满足查询需求，而不需要额外地回到数据表中去获取数据。因此，覆盖索引可以避免回表查询，提高查询性能。

回表查询：
比如查询id，name，gender的时候，gender没有索引，查不到，回进行回表查询

回答面试：
知道什么叫覆盖索引嘛？

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到
使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*

MYSQL超大分页怎么处理？

可以使用覆盖索引解决

回答问题：
MYSQL超大分页怎么处理？
问题：在数据量比较大时，imt分页查询，需要对数据进行排序，效率低
解决方案：覆盖索引+子查询

select *
from tb_sku t,
(select id from tb_sku order by id limit 9000000,10) a
where t.id =a.id

索引创建的原则

1).针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据（增加用户体验）
2).针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3).尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4).如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5).尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6).要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7).如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效

1.查询条件不满足索引最左前缀原则：

在使用联合索引时，如果查询条件不满足索引的最左前缀原则，索引可能会失效。
这意味着索引只能有效利用查询条件的最左边部分，而不能跳过中间的列。

如图，索引有三个

当跳过某一列的时候，索引会失效：

范围查询右边的列，不能使用索引
不要在索引列上进行运算操作，索引将失效。
字符串不加单引号，造成索引失效
%开头模糊查询导致索引失效

回答面试：
什么情况下索引会失效？

违反最左前缀法则
范围查询右边的列，不能使用索
不要在索引列上进行运算操作，索引将失效
字符串不加单引号，造成索引失效。（类型转换）
以%开头的Like模糊查询，索引失效

事务

ACID

ACID是什么？可以详细说一下吗？

原子性(Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency）:事务完成时，必须使所有的数据都保持一致状态。
隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
结合转账的案例来说

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
未提交读	1	1	1
读已提交	0	1	1
默认可重复读	0	0	1
串行化	0	0	0
事务隔离级别越高，数据越安全，但是性能越低。

undo log和redo log

redo log 如下：
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中，后者在磁盘中。当事
务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样，它是逻辑日志。
可以认为当delete-一条记录时，undo log中会记录一条对应的insert记录，反之亦然，
当update-一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log 和redo log区别

redo log:记录的是数据页的物理变化，服务宕机可用来同步数据
undo log:记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

事务中的隔离性是如何保证的呢？

锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）
mvcc:多版本并发控制
你解释一下MVCC?
全称Multi-Version Concurrency Control,.多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView.。
MVCC的实现原理如下：

版本号管理：
- 每个数据行都有一个版本号，用于标识数据的修改版本。
- 当一个事务对数据行进行更新时，系统会为新的数据行创建一个新版本，并将旧版本标记为无效。
读取操作：
- 当一个事务需要读取数据时，系统会根据事务的隔离级别选择合适的数据版本。
- 在读取时，系统会忽略被其他事务修改的数据行，而只读取有效版本的数据行。
写入操作：
- 当一个事务对数据行进行更新时，系统会为新的数据行创建一个新版本，并将旧版本标记为无效。
- 事务对数据行的修改不会影响到正在读取旧版本数据行的其他事务。
快照读取：
- 在一些隔离级别下，系统会为每个事务创建一个快照（Snapshot），用于读取数据时使用。
- 快照包含了事务开始时数据库中所有数据行的有效版本，事务可以根据快照读取数据，而不受其他事务的影响。
回滚段：
- 当一个事务对数据进行更新时，系统会在回滚段（Undo Segment）中保存原始数据的副本。
- 如果事务需要回滚，系统可以使用回滚段中的数据副本来恢复数据。

回答问题：
事务中的隔离性是如何保证的呢？（你解释一下MVCC)
MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

隐藏字段：
①trx id(事务id),记录每一次操作的事务id,是自增的
②roll pointer(回滚指针)，指向上一个版本的事务版本记录地址
undo log:
①回滚日志，存储老版本数据
②版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll pointer指针形成一个链表
readView解决的是一个事务查询选择版本的问题
- 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据
- 不同的隔离级别快照读是不一样的，最终的访问的结果不一样
- RC：每一次执行快照读时生成ReadView
- RR：仅在事务中第一次执行快照读时生成ReadView,后续复用

主从同步

MySQL主从复制的核心就是二进制日志
二进制日志(BINLOG)记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句。

MySQL主从复制的核心就是二进制日志binlog(DDL(数据定义语言)语句和DML(数据操纵语言)语句)
①主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
②从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log。
③从库重做中继日志中的事件，将改变反映它自己的数据

分库分表

分库分表是一种数据库水平拆分（Sharding）的策略，用于解决单一数据库服务器无法满足大规模数据存储和处理需求的问题。在分库分表中，数据库中的数据被拆分成多个数据库实例和表集合，每个数据库实例和表集合负责存储和处理部分数据，从而提高系统的扩展性、性能和可用性。

水平分库

水平分表：

问题：

面试题：

业务介绍

根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）
达到了什么样的量级（单表1000万或超过20G)

具体拆分策略

水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
水平分表，解决单表存储和性能的问题
垂直分库，根据业务进行拆分，高并发下提高磁盘O和网络连接数
垂直分表，冷热数据分离，多表互不影响

锁

日志

内存

触发器

视图

优化

在MySQL中，如何定位慢查询？

解决办法：

使用开源工具
- 调试工具：Arthas
- 运维工具：Prometheus,Skywalking，
使用MYSQL自带的慢日志
- 在MySQL配置文件中启用慢查询日志。可以通过设置slow_query_log参数为1来启用。
- 设置long_query_time参数，指定查询运行时间超过多少秒才会被记录到慢查询日志中，默认值为10秒。
- 使用log_queries_not_using_indexes参数来记录未使用索引的查询。
- 重启MySQL服务使配置生效。
- 查看慢查询日志文件，通常位于MySQL的数据目录下，默认名称为hostname-slow.log。例如/var/lib/mysql/localhost-sow.log
在查询前添加EXPLAIN关键字，可以让MySQL解释执行查询的计划。通过查看执行计划，可以确定查询是否使用了索引，以及是否存在性能问题。

SQL语句执行很慢，如何分析？

对于聚合查询：可以新增一个临时表
对于多表查询：可以优化SQL结构
对于表数据量过大的查询：分析SQL 语句

可以使用explain或者desc明亮来获取mysql执行的select语句信息，直接加在select前面即可

id：
- 这是查询中每个操作的标识符。
- 对于复杂查询，可以通过该字段查看操作之间的父子关系。
select_type：
- 表示每个查询中的查询类型。
- 可能的值包括：SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、DERIVED（派生表查询）、UNION（联合查询）等。
table：
- 显示查询涉及的表名。
- 如果查询涉及多个表，会显示使用的连接方式。
partitions：
- 表示查询使用的分区（如果适用）。
type：
- 表示MySQL在表中找到所需行的方式。
- 常见的值有：ALL（全表扫描）、index（使用索引扫描）、range（范围扫描）、ref（基于索引的等值查找）、const（常量表）等。
possible_keys：
- 显示可能用于此查询的索引。
- 如果MySQL选择了其中一个索引来执行查询，则该索引会在key字段中显示。
key：
- 表示MySQL实际选择用于查询的索引。
- 如果该字段显示为NULL，则表示未使用索引，可能需要考虑创建适当的索引。
- type这条sql的连接的类型，性能由好到差为NULL、system、const、.eq_ref、ref range、index、all。system:查询系统中的表，const:根据主键查询，eq ref:主键索引查询或唯一索引查询，ref:索引查询，range:范围查询，index:索引树扫描，all:全盘扫描
key_len：
- 表示使用的索引的长度。
- 可以帮助你了解索引的效率和覆盖范围。
ref：
- 显示索引之间的列之间的比较。
- 对于使用索引的查询，此字段显示与索引列进行比较的列名。
rows：
- 表示MySQL预计扫描的行数。
- 这是MySQL优化器根据统计信息估算的值，实际执行时可能会有所不同。
filtered：
- 表示从表中检索出的行的百分比。
- 这是根据WHERE条件和表的统计信息估算的。
Extra：
- 提供其他信息，如使用临时表、文件排序、使用索引提示等。
- 这里可能包含一些提示，帮助你理解查询执行的情况。

对于 extra的额外建议：

using where;using index,查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
using index condition:查找使用了索引，但是需要回表查询数据

SQL优化经验

表的设计优化
表的设计优化（参考阿里开发手册《嵩山版》）
①比如设置合适的数值(tinyint int bigint),要根据实际情况选择
②比如设置合适的字符串类型(char和varchar)char定长效率高，varchari可变长度，效率稍低
索引优化
SQL语句优化
①SELECT语句务必指明字段名称（避免直接使用select*)
②SQL语句要避免造成索引失效的写法
③尽量用union all代替union union会多一次过滤，效率低
④避免在where子句中对字段进行表达式操作
⑤Join优化能用innerjoin就不用left join right join,如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或right join,不会重新调整顺序
主从复制、读写分离
如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。
分库分表

MySQL面试题

# MySQL面试题

# 基础

# MySQL执行流程

# 索引

# 了解过索引吗

# 聚簇索引

# 覆盖索引

# MYSQL超大分页怎么处理？

# 索引创建 的原则

# 索引失效

# 事务

# ACID

# 并发事务问题

# 事务隔离级别

# undo log和redo log

# 事务中的隔离性是如何保证的呢？

# 主从同步

# 分库分表

# 锁

# 日志

# 内存

# 触发器

# 视图

# 优化

# 在MySQL中，如何定位慢查询？

# SQL语句执行很慢，如何分析？

# SQL优化经验

MySQL面试题

基础

MySQL执行流程

索引

了解过索引吗

聚簇索引

覆盖索引

MYSQL超大分页怎么处理？

索引创建的原则

索引失效

事务

ACID

并发事务问题

事务隔离级别

undo log和redo log

事务中的隔离性是如何保证的呢？

主从同步

分库分表

锁

日志

内存

触发器

视图

优化

在MySQL中，如何定位慢查询？

SQL语句执行很慢，如何分析？

SQL优化经验