Mysql面试必备

2025-09-08 09:47:15   0  举报





AI智能生成

"掌握MySQL基础知识是任何希望从事数据库管理或开发工作的专业人士的必备条件。首先，了解关系型数据库的基本概念以及SQL（结构化查询语言）是不可或缺的。其次，深入理解数据库设计原则，包括数据规范化和反规范化过程，以及范式（如第一范式、第二范式等）的理论知识。核心内容包括但不限于：事务管理、索引的使用与优化、存储引擎的特点（如InnoDB、MyISAM），以及数据库的备份与恢复策略。熟练掌握数据类型、函数和复杂的查询编写也是面试中经常遇到的考点。此外，对常用数据库操作命令和约束（如主键、外键、唯一性约束、检查约束等）的了解程度同样重要。修饰语如'精通'、'熟悉'和'理解'应与这些核心内容相结合，以清晰展示面试者的能力水平。这份面试核心内容概述为 '.doc' 文件类型，以确保格式稳定且便于编辑和分享。"

面试

Mysql

作者其他创作

大纲/内容

数据库类型

Innodb

聚集索引

索引的子节点就是数据本身

MYISAM

非聚集索引

索引和数据分在两个文件中存储,子节点存储的是地址

Innodb比MYISAM最大的不同

Innodb支持事务

Innodb支持行锁

索引（Innodb）

索引类型

B+Tree

最左原则

联合索引的顺序问题

就是数据

非主键索引子节点是id，需要回表才能获取数据

子主题

Hash

根据哈希值存放数据，查询效率在一定情况下比B+Tree更快

缺点是不能范围查询

索引优化

自增的整型ID优点

新增数据可以直接添加在队尾，效率更高

id比较更快，索引检索效率更高

整型4字节，索引文件更小

叶子节点是有序的，范围查询很快

Explain

type

system

是const的一个特例，整个表就一条数据

const

唯一值，比如：id=1

eq_ref

join的表用的主键或唯一索引做的关联

ref

查询条件没有用主键或唯一索引，用的是普通索引或唯一索引的前缀

rang

范围扫描，常出现在in()、between、>、<等操作

index

如果结果集在二级索引里也能查询到，通常是直接从二级索引查询，mysql的成本计算后的结果，这种查询一般使用覆盖索引，二级索引文件通常比较小，会被全表扫描快一些

all

全表扫描，直接从叶子节点扫描，mysql的成本预算会选择最优的查询方式，有时候用索引不一定比全表扫描快

possible key

mysql预测可能会使用的索引

key

mysql最终使用的索引，可能为空

key_len

使用的索引长度，根据索引字段的类型决定

char(n)--->n

varchar(n)--->3n+2,后面加的2是用来存储字符串长度

如果字段允许null，需要1个字节记录是否为null

其他类型就是所占用的字节长度

extra

using index

用二级索引查询一次就可以得到结果，没有回表的情况

覆盖索引

key有值，select的结果再这个索引中都可以查到，就叫覆盖索引，也就是说没有回表的操作

using where

使用where语句筛选结果，并且没有覆盖到索引

using index condition

用了所有，但是没全用

using temporary

用来临时表

using filesort

在内存或磁盘中做排序

单路排序

一次性将需要排序的行加载到内存中进行排序

双路排序

去出需要排序的字段和数据ID，排序完成以后再回表取其他数据

通过判断是单路还是双路排序max_length_for_sort_data（默认1024字节）

show warnings

显示mysql优化有的sql

like查询

不要使用前百分号的查询，'%ss'

字符串不加单引号会导致所有失效

本质上就是字段的类型和查询的数据类型要相同，比如 name= '1000',如果使用name=1000索引会失效

不要在索引字段上加计算操作

尽量使用覆盖索引

索引下推

在Mysql5.6以前，联合索引会根据有效的索引条件查询后回表再筛选其他条件

在Mysql5.6以后，引入了索引下推，在索引遍历的过程中，对所有索引先进行判断，筛选掉不需要的数据以后再回表，可以减少回表的次数

trace工具

1. set session optimizer_trace="enabled=on",end_markers_in_json=on;‐‐开启trace

2.select * from employees where name >'a' order by position;

2.SELECT * FROM information_schema.OPTIMIZER_TRACE;

trace展示的sql执行流程

第一阶段：SQL准备阶段，格式化sql

第二阶段：SQL优化阶段

条件处理

表依赖详情

预估表的访问成本

表扫描情况

扫描行数

这才是mysql最终选择查询方式的判断依据

第三阶段：SQL执行阶段

sort_mode，判断单路排序还是双路排序的字段

单路排序值为< sort_key, additional_fields >或者< sort_key,packed_additional_fields >

双路排序值为< sort_key, rowid >

分页查询优化

当分页offset非常大的时候，查询效率会很慢

只查询分页的数据，减少结果集

如果无法通过id查询出范围，可以利用覆盖索引达到效果

利用覆盖索引精确到具体的行，再通过id join查询其他信息

join优化

嵌套循环连接 Nested-Loop Join(NLJ) 算法

一行一行取出满足条件的数据到内存中

有索引的情况更快

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

分批加载到内存中进行对比

没有索引的情况更快

join的优化建议

关联的字段尽量加索引，使mysql用NLJ算法

小表驱动大表

事务

事务的ACID属性

原子性(Atomicity):当前事务的操作要么同时成功，要么同时失败。原子性由undo log日志来实现

一致性(Consistent):使用事务的最终目的，由其它3个特性以及业务代码正确逻辑来实现。

隔离性(Isolation) ：在事务并发执行时，他们内部的操作不能互相干扰。隔离性由MySQL的各种锁以及MVCC机制来实现

事务隔离级别

读未提交(Readuncommitted)

读已提交(Readcommitted)

可重复读(Repeatableread)

可串行化(Serializable)

并发事务引起的问题

更新丢失或脏写

脏读-RU级别

不可重复读

幻读

持久性(Durable) ：一旦提交了事务，它对数据库的改变就应该是永久性的。持久性由redo log日志来实现。

事务问题定位

SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(timediff(now(), trx_started ))>1;--查询超过1秒的事务

开启事务不会产生真正的事务，只有执行了更新操作或是排它锁的时候才会产生事务id，但是第一次执行的查询语句也会产生一个不是真正意义上的事务，这个事务的id非常大，可以理解为临时事务id，在执行了更新语句或排他锁之后，事务id会变的很小，

锁

从性能上分

乐观锁(用版本对比或CAS机制)

悲观锁，读锁和写锁都属于悲观锁

比如：udpate account set num= num+1where id =1;所有更新都会排队

对数据的操作粒度上分

表锁

开销小，加锁快，不会出现死锁

一般用于整张表迁移的场景

页锁

只有BDB的存储引擎才有页锁

行锁

开销大，加锁慢，会出现死锁，并发更高

如果where条件用的索引字段，是在这个字段的索引上加行锁

如果where条件用的非索引字段，

RR隔离级别会升级为表锁

因为RR需要解决不可重复读或幻读问题，会把所有扫描过的索引和间隙都加锁

RC隔离级别不会升级为表锁

对数据操作的类型

读锁，共享锁、S锁(Shared)

会阻塞写

在查询语句后面加上：lock in share mode

写锁，排它锁，X锁(eXclusive)

读写都会阻塞

意向锁

当有行锁的时候会在表的级别标记上意向锁，避免扫描所有行判断是否有锁

其他锁

间隙锁

只有可重复度有间隙锁

临键锁

就是行锁和间隙锁同时存在

死锁

查看死锁日志：show engine innodb status;

锁等待分析

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits:当前正在等待锁定的数量

Innodb_row_lock_time:从系统启动到现在锁定总时间长度

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_waits:系统启动后到现在总共等待的次数

查询事务：select * from INFORMATION_SCHEMA.INNODB_TRX;

查询锁：select * from INFORMATION_SCHEMA.INNODB_LOCKS;8.0之后需要换成这张表data_locks

查询锁等待：select * from INFORMATION_SCHEMA.INNODB_LOCKS;8.0之后需要换成这张表data_lock_waits

死锁

锁等待的一种特殊情况

当一个事务正在持有一个资源（如一行记录或一个表）的锁并请求另一个资源的锁，而另一个事务正在持有该请求的锁并请求第一个事务持有的锁，这时就会发生死锁。

查询死锁的三种方式

查询mysql的错误日志：SHOW VARIABLES LIKE 'log_error'

查询Innodb的状态：SHOW ENGINE INNODB STATUS，通过查询关键词"LATEST DETECTED DEADLOCK"或"TRANSACTIONS"

结合INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS表进行判断

锁的优化实践

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少检索条件范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能低级别事务隔离

MVCC可见性算法

基于undo log实现

mysql的内部管理机制，常用的并发控制机制

原理：在数据库中保存多个版本的数据副本，解决多用户读写的冲突问题

每一行都有一个指针，指向修改前的数据

insert的指针指向的是delete语句

Read View可见性判断

拥有四个属性

未提交事务id集合

已提最大交事务id/应该分配给下一个事务的id

未提交事务最小id

当前事务id

三种情况判断数据是否可见

第一：数据的事务id小于未提交事务最小id或者等于当前事务id，该数据是可见的

第二：数据事务id大于最大事务id，数据不可见

第三：数据id大于未提交最小事物id，并且小于最大事物id，分两种情况

如果事务id在未提交事务id集合中，数据不可见

如果事务id不在未提交事务id集合中，数据可见

日志

redo log

部分参数详解

red log buffer默认大小为16M,可通过innodb_log_buffer_size设置

文件大小默认48M，课通过innodb_log_file_size修改

文件个数默认2个，可通过innodb_log_files_in_group修改

redo log是确保数据持久化的一种机制

执行器会先修改buffer pool中的值，完成后返回给客户端成功消息

然后执行器会记录redo log buffer

然后将redo log buffer的数据异步写入磁盘

写入磁盘的过程叫checkpoint

原理：redo log写完一个文件以后，写另外一个文件，当最后一个文件写完后，又回头写第一个文件，这个文件的内容会顺序覆盖

write pos:当前写的指针

checkpoint：当前要擦除的位置指针

擦除前，要把记录更新到数据文件中

write pos 和checkpoint 之间的空隙就是可以写的磁盘空间

记录bin log，完成后记录commit标记到redo log

这个标记是为了保证事物提交后redo log和bin log的数据一致

最后由一个线程，在系统空闲的时候将数据持久化到磁盘中

以buffer pool里的page为单位，随机写入磁盘

bin log

记录所有对数据库更改的操作日志

用二进制进行存储

bin log的三个作用

恢复数据，如果系统故障或删库跑路，可以用于恢复数据

在主从部署中，用于从库复制

可以识别用户操作，识别未授权的操作

bin log 的三种日志格式

通过binlog_format设置日志格式

STATEMENT：基于更新操作的sql记录，缺点是uuid这类函数在从库的值会不一样，日志量小，IO开销小，性能高

ROW：基于row的值做的记录，可以解决上面的问题，但是开销大，比如update更新了10 行，它会记录10行数据

MIXED，前两种的混合模式，他会判断是否带有函数或执行才知道结果的情况，就选ROW，其他情况选STATEMENT

bin log 写入磁盘的时机是通过sync_binlog控制，默认是0

0的时候，事物提交后会记录到page cache,由系统判断什么时候写入磁盘，性能高，但是宕机会丢失数据

1，表示事物提交就写入磁盘，最安全

N(N > 1),表示先写入page cache，当事物提交数量达到N的时候，写入磁盘

查看或操作bin log

查看：mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐bi
nlog.000007

数据恢复：mysqlbinlog ‐‐no‐defaults ‐‐start‐position=219 ‐‐stop‐position=701 ‐‐database=test D:/dev/mysql‐
5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v test

undo log

undo log 采用段的方式管理文件

每段可以支持1024个事物回滚

mysql 5.5只有一个回滚段，mysql 5.6之后最多支持128个回滚段

undo log 删除的时机

新增类型，在事物提交以后删除

update 类型，事务提交以后不会马上删除，因为要用于MVCC，在没有事务用到的时候才会删除

错误日志

记录数据库在启动、停止、运行中产生的严重错误

show variables like '%log_error%';

relay log

当从库同步数据的时候，会将主库的redo log同步到从库的relay log，也叫中继日志