MySQL 高频面试题

MySQL 索引

聊聊 MySQL 的索引结构，为什么使用 B+ 树而不是 B 树？

B+ 树的非叶子节点不存放实际的数据，只存放索引，因此数据量相同的条件下，相比既存数据又存索引的 B 树，B+ 树可以存放更多的索引，B+ 树可以比 B 树更矮胖，查询底层节点的磁盘 IO 次数会变少
B+ 树有大量冗余节点，所有非叶子节点都是冗余索引，这些冗余索引让 B+ 树在插入、删除时的效率更高，相比 B 树有更少的结构变换
B+ 树叶子节点之间用链表连接，有利于范围查询，而 B 树要实现范围查询，只能遍历树，效率更低。

你是怎么建立索引的？一般给哪些字段建立索引？

字段有唯一性限制的，比如商品编码
经常使用 WHERE 查询条件的字段，如果查询字段不是一个，可以建立联合索引
建立联合索引时，按照最左匹配原则，且稀疏度越大的越放在左边
经常用 GROUP BY 的字段，这样查询的时候数据就是排序好的，因为建立索引后数据在 B+ 树中的记录是排序好的

如何确定语句是否走了索引？

在 SQL 前添加 explain 查看执行计划：

possible_keys：可能用到的索引
key：实际用到的索引，如果为 null，表示没有走索引
key_len：索引的长度
rows：扫描的数据行数
type：扫描的数据类型，如果为 all，表示没有走索引，进行了全表扫描

什么是联合索引？

联合索引由多个字段组合而成。在 B+ 树中的非叶子节点中保存了联合索引的多个字段。联合查询时，先按照第一个索引字段查询，相同的话按第二个字段查询，以此类推。存在最左匹配原则。

如果要建立联合索引，字段的顺序有什么需要注意的吗？

最左匹配原则
稀疏度高的放在左边：稀疏度高就是区分度高的字段，比如唯一 id 要放在类型左边

MySQL 索引失效的几种情况？

模糊匹配：like %xxxx 或 like %xxx%
where 中对索引列使用函数
where 中对索引列使用表达式计算
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转化，由于隐式类型转化是通过 cast 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效
联合索引不遵循最左匹配原则

索引底层的数据结构有哪些实现方式？了解 Hash 索引吗？

MySQL 常见索引：

B+ Tree 索引：MySQL 默认存储引擎 InnoDB 采用的索引数据结构，所有数据都存储在叶子节点中，非叶子节点只存储索引，提高范围查询的性能和减少磁盘 IO次数，千万级数据量的 B+ 树只需要 3 层
Full-Text 索引：全文索引用于对文本内容进行搜索，采用倒排索引等数据结构来实现全文搜索功能，支持关键字搜索和模糊查询
哈希索引：哈希索引通过哈希函数计算键的存储位置，适用于等值查找，速度快，但不适用于范围查找

索引有什么优缺点？

优点：

提高数据检索效率：索引可以加快数据的检索速度，减少数据查询的时间
支持快速排序和分组：索引可以帮助数据库快速排序和分组数据

缺点：

占用存储空间：索引会占用额外的存储空间，增加数据库的存储成本
维护成本高：随着数据的增删改查，索引需要不断更新维护，增加数据库的维护成本
降低写入性能：对于频繁进行写操作的表，索引可能会降低写入性能，因为每次写入都需要更新索引

索引和数据库的源结构是耦合的吗？

InnoDB 存储引擎中，索引可以为分聚簇索引和非聚簇索引（二级索引），他们的区别在于叶子节点存放的数据不同；

聚簇索引叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点
非聚簇索引的叶子节点存放的是主键值，而不是实际数据

MySQL 事务

事务的四个特性是什么？

原子性（Automicity）：一个事务中的全部操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，如果事务在执行过程中发生错误，会会滚到事务开始前的状态，就像这个事务从来没有执行过一样。比如用户购买一件商品，购买成功，则给商家了付了钱，商品也到手；反之购买失败，商品没有拿到，钱也还在消费者手里
一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性的状态。比如用户 A 和用户 B 分别有800 元和 600 元，共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元，从 B 的账户增加 200 元，一致性就是按上述要求操作结束后，A 剩余 600 元，B 剩余 800 元，合计还是 1400 元，不会出现 A 少了 200 元，B 的账户未增加的情况
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个并发事务执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会互相干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失

隔离性是怎么实现的？

MVCC，多版本并发控制或锁机制来实现的。

一致性是怎么实习的？

持久性通过 redo log（重做日志）来保证的
原子性通过 undo log（回滚日志）来保证的
隔离性通过 MVCC 或锁机制保证的
一致性通过持久性+原子性+隔离性保证的

MySQL 有哪些隔离级别？可能产生什么问题？

读未提交：当一个事务还未提交时，它的变更就能被其他事务看到
读已提交：当一个事务被提交后，它的变更就能被其他事务看到
可重复读：InnoDB 默认隔离级别，事务执行时看到的数据和启动时一样
串行化：会对记录加上读写锁，多个事务对一条记录进行读写操作时，后一个事务需要等待前一个事务执行完成释放锁后才能继续执行

不同的隔离级别可能产生不同的问题：

读未提交：脏读、不可重复读、幻读
读已提交：不可重复读、幻读
可重复读：幻读
串行化：无

InnoDB 如何避免不可重复读？

InnoDB 默认隔离级别是可重复读，可重复读隔离级别在开启事务后，执行第一个 select 语句时，会生成一个 Read View，后面整个事务 select 期间都在用这个 Read View，所以这期间读取的数据都是一致的，不会出现前后读取的数据不一致的问题，避免了不可重复读。

滥用事务，或者一个事务里有特别多 SQL 的弊端？

一个事务里的 SQL 太多，容易造成大量的死锁和锁超时
回滚记录会占用大量的存储空间，事务回滚的时间长。在 MySQL 中，实际上每条记录在更新时都会同时记录一条回滚操作，记录上的最新值，通过回滚操作可以得到前一个状态的值，sql 越多，需要保存的回滚数据就越多
执行时间长，容易造成主从延迟，主库上必须等待事务执行完成后才能写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很有可能造成从库延迟 10 分钟

MySQL 锁

MySQL 死锁是怎么产生的？如何解决？

【需要使用实验验证】

示例 1：

假设有两个事务 A 和 B[1]：

A: select * from table where id = 1;，事务 A 对 id = 1 的记录上了 X 行锁
B: select * from table where id = 3;，事务 B 对 id = 3 的记录上了 X 行锁
A: select * from table where id = 3;，事务 A 等待 B 释放 id = 3 的锁后再加锁
B: select * from table where id = 1;，事务 B 等待 A 释放 id = 1 的锁后再加锁

这时事务 A 和事务 B 都在等待对方释放锁后才能加锁，陷入无限等待，这时进入死锁。

解决办法是加锁时添加顺序，比如必须按照这个顺序：先获取 id = 1 的锁，再获取 id = 3 的锁。那么以上步骤就会变为 1，2，4，3，这时就不会发生死锁了。

示例 2：

假设有 100 条数据，id 从 1～100
事务 A 查询 id=101 的数据：select * from table where id = 101;，对 (100,+♾️] 加 X 型 next-key 锁
事务 B 查询 id=102 的数据：select * from table where id = 102;，对 (100,+♾️] 加 X 型 next-key 锁
事务 A 未查到 101 的数据，所以在事务内接着尝试插入数据：insert into table (id, time) values (101, now())，会添加一个插入意向锁，插入意向锁与 next-key 锁冲突，所以需要等待 B 释放 next-key 锁
事务 B 未查到 102 的数据，所以在事务内接着尝试插入数据：insert into table (id, time) values (102, now())，会添加一个插入意向锁，插入意向锁与 next-key 锁冲突，所以需要等待 A 释放 next-key 锁，这时造成死锁

MySQL 有哪些锁？

全局锁
表级锁
表锁
元数据锁
意向锁
行级锁
记录锁：分为 S 锁和 X 锁，满足读写互斥、写写互斥
间隙锁
Next-Key Lock 临键锁

InnoDB 有哪些锁？

InnoDB 有如下锁[2]：

Shared(S) and Exclusive(X) Locks
Intention Locks
Record Locks
Gap Locks
Next-Key Locks
Insert Intention Locks
AUTO-INC Locks
Predicate Locks for Spatial Indexes

具体参考InnoDB 锁介绍

可重复读隔离级别下，以下 SQL 会发生什么？

(id, no, name, age, score)

(15, S0001, Bob, 25, 34)
(18, S0002, Alice, 24, 77)
(20, S0003, Jim, 24, 5)
(30, S0004, Eric, 23, 91)
(37, S0005, Tom, 22, 22)
(49, S0006, Tom, 25, 83)
(50, S0007, Rose, 23, 89)

事务A: 
time1: update students set score=100 where id = 25
time3: insert into students(id,no,name,age,score) value (25,'S0025','sony',28,90)

事务B: 
time2: update students set score=100 where id = 26
time4: insert into students(id,no,name,age,score) value (26,'S0026','ace',28,90)

解答:

time1: 事务 A 获取 Gap Lock（间隙锁），范围 20~30
time2: 事务 B 获取 Gap Lock（间隙锁），范围 20~30，间隙锁可以共存[3]
time3: 事务 A 生成 Insert Intention Lock（插入意向锁），等待
time4: 事务 B 生成 Insert Intention Lock（插入意向锁），等待
由于双方都在等待对方释放间隙锁，进入死锁

MySQL 如何实现乐观锁？

乐观锁（Optimistic Concurrency Control，缩写“OCC”）是一种并发控制机制，最早是由孔祥重（H.T.Kung）教授提出[4]。乐观锁相信事务之间的竞争是比较小的，因此在读取数据时不会加锁，只在更新数据时根据版本号决定是否更新：

如果更新时，版本号与数据库中的版本号一致，则将版本号加 1，并更新到数据库
如果更新时，版本号于数据库中的版本号不一致，表示记录已经被其他事务更新，此次更新失败

SQL 如下：

update table set
xxx = '其他字段',
version = version + 1
where version = #{当前版本号}

MySQL 两个线程的 update 语句同时处理同一条数据，会不会有阻塞？

例如 update ... where id = 1，是会阻塞的，因为 InnoDB 实现了行级锁。

当事务 A 对 id=1 这条记录进行更新时，会对主键 id 为 1 的记录加 X 类型的记录锁，这样吧第二个事务 B 对 id=1 的记录更新时，发现已经有锁记录了，就会进入阻塞状态。

两条 update 语句处理一张表的不同主键范围的记录，一个 < 10, 一个 > 15，会不会遇到阻塞？底层是为什么？

不会，因为锁住的范围不一样，不会形成冲突

第一条 update xxxx where id < 10，锁住的范围是 (-♾️, 10)
第一条 update xxxx where id > 15，锁住的范围是 (-15, +♾️)

如果 2 个范围不是主键或索引，还会阻塞吗？

如果以上两个范围查询的字段不是主键或者索引时，就代表 update 没有用到索引，这时候触发了全表扫描，全部索引都会加行级锁，这时候第二条 update 语句执行时，就会阻塞了。如果 update 没有用到索引，在扫描过程中会对索引加锁，所以全表扫描的场景下，所有记录都会被加锁。

除了表锁、行锁之外，还有其他类型的锁吗？

还有全局锁。通过 flash table with read lock 会将整个数据库处于只读状态，这时其他线程执行增删改或者表结构修改都会阻塞。全局锁主要用于全库逻辑备份，这样在备份数据库期间，不会因为修改或表结构的更新，出现备份后的数据于语气不一致。

其他

设计 MySQL 的表结构要考虑什么问题？

主键设计要合理：主键不要有业务含义，比如身份证号码虽然是唯一的，但是不宜做主键，常见的主键 ID 有自增 ID、uuid、雪花算法生成的 ID 等
不要有太多字段：一张表的字段尽量不要超过 20 个，如果字段太多，保存的数据可能太大，查询效率会降低。当表中字段非常多时，可以拆分为两张表，一张为查询表，另一张为详情表
优先考虑逻辑删除，而不是物理删除：会数据添加一个 is_delete 字段，表示数据是否已经逻辑删除，最好不要物理删除，因为数据恢复会很困难，且自增的主键不再连续
尽可能使用 not null 定义字段：首先，NULL 值可以防止出现控制真问题；其次，NULL 值存储也是需要额外空间的，它会导致比较运算符复杂，使优化器难以优化 SQL；最后 NULL 值可能会导致索引失效
评估哪些字段需要加索引：区分度不高的字段，不宜加索引。索引也不要建的太多，一搬单表索引数量不要超过 5 个，建立联合索引时，要结合最左匹配原则，和业务上频繁使用的 where 条件来综合考虑

MySQL 的 char 和 varchar 有什么区别？

char 是固定长度字符串类型，定义时需要指定字段长度，存储时会在末尾补足空格。char 适合存储长度固定的数据，如固定长度的代码，状态等，存储空间固定，对于短字符串效率较高
varchar 是可变长度的字符串类型，定义时需要指定最大长度，存储时根据实际长度占用存储空间。varchar 适合存储长度可变的字符，如用户输入的文本，备注等

当前读和快照读的区别是什么？

当前读可以读取其他事务最新已经提交的记录，执行的过程中会通过加行级锁的方式保持事务的隔离性，比如 select for update，update，delete，都属于当前读
快照读是无锁的，主要是基于 mvcc 机制实现的，可重复读和读已提交的 select 都属于快照读
可重复读的隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在使用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录
读提交隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，同一个事务期间多次读取同一条记录，两次读取到的结果可能不一样，因为可能这期间另外一个事务修改了记录，并提交了事务

MySQL 分表怎么设计？

按时间分表：根据数据的时间特征，按照时间范围（如年、月、日）将数据存到不同表中
按业务分表：按照业务需求将数据按照业务逻辑进行分类，可根据不同业务属性将数据打散到不同的表中，实现逻辑上的分离
按哈希分表：通过使用哈希计算，将数据均匀分布在多个表中，避免单表数据量过大
按范围分表：根据数据的某个范围属性，（用户 ID、地区 ID 等）将数据打散到不同的表中，便于查询和管理不同范围的数据
按数据量分表：当单表数据量过大时，按照一定规则将数据分散到不同表中

MVCC 是什么意思？

MVCC（Multiversion concurrency control）多版本并发控制。假设有两个事务同时更新一条数据，数据有两个隐藏列：

trx_id: 当一个聚簇索引对该记录进行改动时，trx_id 为该事务的 id
roll_pointer: 每次对某条聚簇索引改动时，都会把旧版本的日志写到 undo 日志中，roll_pointer 是一个指针，指向上一个版本的数据，形成一个链

当数据库引擎为不同隔离级别时，事务通过数据库快照 ReadView 判断应该读取版本链中的哪条数据：

隔离级别为 READ COMMITTED：每次查询开始是生成独立的 ReadView
REPEATABLE COMMITTED：第一次读取数据时生成 ReadView

ReadView 有四个字段，在如上不同隔离级别下通过这四个字段完成数据访问： - m_ids：表示在生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表。 - min_trx_id：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务 id，也就是 m_ids 中的最小值。 - max_trx_id：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。 - creator_trx_id：表示生成该 ReadView 的事务的事务 id。

数据库三大范式是什么？

第一范式：保证每一列不可再分
第二范式：前提是满足第一范式，每张表只描述一件事情，消除部分依赖的问题
第三范式：前提是满足第一范式和第二范式，第三范式需要确保数据表中每一列数据都和主键直接相关，而不能间接相关，消除传递依赖的问题

数据库怎么分类，描述一下你对这些数据库的理解？

按照数据模型，主要分为关系型数据库和非关系型数据库

关系型数据库：基于关系模型组织的数据库，如 MySQL，Oracle 等
非关系型数据库：不使用传统表格形式存储的数据库，如 MongoDB、Redis 等

数据库是用于存储、管理和检索数据的系统，关系型数据库使用结构化语言 SQL 来管理数据，适用于需要保持数据一致性和完整性的场景；NoSQL 数据库则更加灵活，适用于需要处理大量非结构化数据或需要高可伸缩性的场景。

什么情况使用 MySQL，什么情况使用 Redis？

选择 MySQL：需要存储结构化数据，需要支持复杂的查询操作，和需要支持事务处理时，选择 MySQL
选择 Redis：需要快速数据读取和写入，实现分布式锁时，可以使用 Redis

一般 Redis 会作为 MySQL 的缓存，提高服务系统的查询性能，比如一些高吞吐量的场景，会做二级缓存，请求顺序为本地缓存-Redis-MySQL，Redis 直接读取内存，MySQL 读取硬盘，所以 Redis 速度更快。

了解 SQL 注入吗？怎么防止 SQL 注入？

SQL 注入是发生于应用程序和数据库之间的安全漏洞，是输入的字符串中输入 SQL 指令，数据库以为是正常的指令去执行，系统因此遭到破坏。比如有如下 SQL：

select * from table where user_name = 'name' and pass_word = 'passwd' and user_type = 'student';

假如在用户界面需要输入用户名和密码，用户名输入 name，密码输入 'or true --，SQL 变为：

select * from table where user_name = 'name' and pass_word = ''or true --' and user_type = 'student';

-- 是 SQL 的注释符号，因此会把 and user_type = 'student' 这段注释掉，而由于 or true 的存在，整个 SQL 的逻辑相当于：

select * from table;

由此实现了通过 SQL 注入不用密码即可登录成功。

预防措施：

严格限制数据库的操作权限，给连接数据库的用户提供满足需要的最低权限
校验参数是否合法，利用正则或特殊字符的判断
对进入数据库的特殊字符转义处理，或编码转换
预编译 SQL（Java 中使用 PreparedStatement），参数化查询方式，避免 SQL 拼接

MySQL 有哪些引擎？说一下他们的区别？

有 InnoDB 和 MyISAM，它们的区别：

事务：InnoDB 支持事务，MyISAM 不支持，这是 MySQL 的默认引擎从 MyISAM 变成 InnoDB 重要原因之一
索引结构：InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，再通过主键查询到数据。因此主键不应太大，主键太大会导致其他索引也很大。而 MyISAM 是非聚簇索引，数据文件和索引是分离的，索引保存的是数据文件的指针
锁粒度：InnoDB 最小粒度的锁是行锁，MyISAM 最小粒度的锁是表锁。一个更新语句会锁住整张表，因此并发访问受限
count 的效率：InnoDB 不保存行数，select count(1) from table 查询时需要全表扫描，而 MyISAM 用一个变量保存了整个表的行数，执行上述语句只需要读改变量的值即可，速度比较快

MySQL 查询数据怎么优化？

通过 explain 执行结果，查看 SQL 是否走索引，如果不走索引，考虑增加索引
建立联合索引，索引区分度最大的字段放在最左边，且要符合最左匹配原则
避免索引失效，比如不要使用左模糊匹配，函数计算、表达式计算等
连表查询最好要以小表驱动大表，被驱动表的字段要有索引，最好通过冗余字段的设计避免多表关联
针对 limit x, y 深分页的查询优化，可以把 limit 查询转换成某个位置的查询：select * from tb_sku where id > 20000 limit 10;，该方案适用于主键自增的表
将字段多的表拆解成多个表，有些字段使用频率高，有些低，可以考虑分开，因为数据量大时使用频率低的字段速度会慢

binlog 的类型有哪些？

基于语句的复制（Statement-based）
基于行的复制（Row-Based）
混合模式的复制（Mixed）

参考

《MySQL 是怎样运行的：从根儿上理解 MySQL》第 22 章第 6 节
MySQL 8.0 Reference Manual-17.7.1
小林 coding
孔祥重: https://zh.wikipedia.org/wiki/%E5%AD%94%E7%A5%A5%E9%87%8D