MySQL面试常见问题
2022-07-09 11:17:43 0 举报AI智能生成
MySQL面试题汇总，Java后端面试题
作者其他创作
大纲/内容
1. 能说下myIsam 和 Innodb的区别吗？
MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，支持全⽂索引、压缩、空间函数等，
但MyISAM不支持事务、外键和行级锁，并且索引和数据是分开存储的，⽽且最⼤的缺陷就是崩溃后⽆法
安全恢复。⼀般⽤于有⼤量查询少量插⼊的场景来使⽤。
但MyISAM不支持事务、外键和行级锁，并且索引和数据是分开存储的，⽽且最⼤的缺陷就是崩溃后⽆法
安全恢复。⼀般⽤于有⼤量查询少量插⼊的场景来使⽤。
InnoDB是基于聚簇索引建⽴的，和MyISAM相反它⽀持事务、外键和行级锁，并且通过MVCC来支持高
并发，索引和数据存储在⼀起。
并发，索引和数据存储在⼀起。
参考
2. 说下mysql的索引有哪些吧，聚簇和非聚簇索引
又是什么？
又是什么？
按照数据结构来说
B+树索引
Hash索引
按照物理存储来分
聚簇索引（主键索引）
二级索引（辅助索引）
按照字段特性来分
主键索引
唯一索引
普通索引
前缀索引
按照字段个数来分
单列索引
联合索引
聚簇索引
叶子节点包含索引列和数据，这种数据和索
引在⼀起存储的索引方式叫做聚簇索引
引在⼀起存储的索引方式叫做聚簇索引
⼀张表只能有⼀个聚簇索引，一般会选择主键作为
聚簇索引，没有定义主键，InnoDB会选择⼀个唯⼀
的非空字段代替，如果都没有的话则会隐式定义一
个主键作为聚簇索引
聚簇索引，没有定义主键，InnoDB会选择⼀个唯⼀
的非空字段代替，如果都没有的话则会隐式定义一
个主键作为聚簇索引
非聚簇索引（二级索引）
叶子结点包含索引列和主键Id值，这⼀点
和MyISAM保存的是数据地址是不同的
和MyISAM保存的是数据地址是不同的
全文索引
对内容进行分词搜索，仅可用于Myisam， 更多用ElasticSearch做
搜索 ALTER TABLE table_name ADD FULLTEXT ( filed_name )
搜索 ALTER TABLE table_name ADD FULLTEXT ( filed_name )
3. 那你知道什么是覆盖索引和回表吗？
覆盖索引
指的是在一次查询中，如果⼀个索引（二级索引）包含或者
说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，
⽽不再需要回表查询
说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，
⽽不再需要回表查询
回表查询
指的是一次查询，走的是二级索引，没有覆盖需要查询的
字段，需要拿到主键Id去聚簇索引中二次查询，多扫描一
次索引树
字段，需要拿到主键Id去聚簇索引中二次查询，多扫描一
次索引树
4. MySQL 中锁的类型有哪些呢？（TODO）
TODO
mysql锁分为共享锁和排他锁，也叫做读锁和写锁。
读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和⾏锁两种。
表锁会锁定整张表并且阻塞其他⽤户对该表的所有读写操作，⽐如alter修改表结构的时候会锁表。
⾏锁⼜可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。
读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和⾏锁两种。
表锁会锁定整张表并且阻塞其他⽤户对该表的所有读写操作，⽐如alter修改表结构的时候会锁表。
⾏锁⼜可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。
5. 你能说下事务的基本特性和隔离级别吗？
事务的基本特性
原子性
指的是⼀个事务中的操作要么全部成功，要么全部失败。
一致性
指的是数据库执行事务的前后，数据是一致的，不会出现
数据丢失。
数据丢失。
隔离性
指的是一个事务的修改在最终提交前，对其他事务是不可
见的，相互隔离。
见的，相互隔离。
持久性
指的是一旦事务提交，所做的修改就会永久保存到数据
库中。
库中。
事务的隔离级别
read uncommit（读未提交）
可能会读到其他事务未提交的数据，
也叫做脏读。
也叫做脏读。
read commit（读已提交）
两次读取结果不一致，叫做不可重复读。不可重复读
解决了脏读的问题，他只会读取已经提交的事务。
解决了脏读的问题，他只会读取已经提交的事务。
repeatable read（可重复读）
mysql的默认级别，就是每次读取结果都⼀样，
但是有可能产生幻读。
但是有可能产生幻读。
serializable（串行化）
给每一行读取的数据加锁，会导致大量超时和锁竞争的问
题。不管多少事务，都是「依次按序一个一个执行」
题。不管多少事务，都是「依次按序一个一个执行」
事务造成的三种问题
脏读
脏读指的是「读到了其他事务未提交的数据」，未提交意味着这些数据
可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。
读到了并一定最终存在的数据，这就是脏读
可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。
读到了并一定最终存在的数据，这就是脏读
不可重复读
对比可重复读，不可重复读指的是在同一事务内，「不同的时刻读到
的同一批数据可能是不一样的」。
的同一批数据可能是不一样的」。
幻读
幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些
行的内容作了更改，但是还未提交，此时事务B插入了与事务A更
改前的记录相同的记录行，并且在事务A提交之前先提交了，而这
时，在事务A中查询，会发现「好像刚刚的更改对于某些数据未起
作用」，但其实是事务B刚插入进来的这就叫幻读
行的内容作了更改，但是还未提交，此时事务B插入了与事务A更
改前的记录相同的记录行，并且在事务A提交之前先提交了，而这
时，在事务A中查询，会发现「好像刚刚的更改对于某些数据未起
作用」，但其实是事务B刚插入进来的这就叫幻读
6. 那ACID靠什么保证的呢？
A 原子性由undo log⽇志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执⾏成功的sql
C ⼀致性⼀般由代码层⾯来保证（TODO）
I 隔离性由MVCC来保证
D 持久性由内存+redo log日志来保证，mysql 修改数据同时在内存和 redo log 记录
这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复
这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复
7. 那什么是幻读，什么是 MVCC？
扩展
要说幻读，首先要了解MVCC，MVCC叫做多版本并发控制，实际上
就是保存了数据在某个时间节点的快照。
就是保存了数据在某个时间节点的快照。
我们每行数据实际上隐藏了两列，创建时间版本号，删除时间版本号，
每开始一个新的事务，版本号都会自动递增。
每开始一个新的事务，版本号都会自动递增。
案例：
由于MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为
空或者大于当前事务版本，小明的真实的查询应该是这样
空或者大于当前事务版本，小明的真实的查询应该是这样
子主题
参考链接：
含义
MVCC: 多版本并发控制，是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)
引擎实现中常用的处理读写冲突的手段，目的在于「提高数据库高并发场景下的吞
吐性能」。
引擎实现中常用的处理读写冲突的手段，目的在于「提高数据库高并发场景下的吞
吐性能」。
作用
在 MVCC 协议下，每个读操作会看到一个一致性的快照，「这个快照是基于
整个库的」，并且可以实现非阻塞的读，用于「支持读提交和可重复读隔离级
别的实现」
整个库的」，并且可以实现非阻塞的读，用于「支持读提交和可重复读隔离级
别的实现」
MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增
的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修
改的数据是「记录在 undolog 中」的。
的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修
改的数据是「记录在 undolog 中」的。
失效场景
两个连续的快照读中间有个当前读
8. 那你知道什么是间隙锁吗？
间隙锁是可重复读级别下才会有的锁，结合MVCC和间隙锁可以解决幻读的问题。
需要注意的是唯一索引是不会有间隙锁的。
需要注意的是唯一索引是不会有间隙锁的。
案例：
子主题
9. 你们的数据量有多大？分库分表怎么做的？
分表分为垂直和水平两个方式，一般来说
我们拆分的顺序是先垂直后水平。
我们拆分的顺序是先垂直后水平。
垂直
垂直分库
基于现在微服务拆分来说，都是已经做到了垂直分库
垂直分表
如果表字段比较多，将不常⽤的、数据较⼤的等等做拆分
子主题
水平
水平分表
⾸先根据业务场景来决定使⽤什么字段作为分表字段(sharding_key)，⽐如我们现在⽇订单1000万，我
们⼤部分的场景来源于C端，我们可以用user_id作为sharding_key，数据查询⽀持到最近3个⽉的订
单，超过3个⽉的做归档处理，那么3个⽉的数据量就是9亿，可以分1024张表，那么每张表的数据⼤概
就在100万左右。⽐如⽤户id为100，那我们都经过hash(100)，然后对1024取模，就可以落到对应的
表上了
们⼤部分的场景来源于C端，我们可以用user_id作为sharding_key，数据查询⽀持到最近3个⽉的订
单，超过3个⽉的做归档处理，那么3个⽉的数据量就是9亿，可以分1024张表，那么每张表的数据⼤概
就在100万左右。⽐如⽤户id为100，那我们都经过hash(100)，然后对1024取模，就可以落到对应的
表上了
10. 那分表后的ID怎么保证唯⼀性的呢？
1. 设定步长，比如1-1024张表我们设定1024的基础步长，这样主键落到不同的表就不会冲突了
2. 分布式ID，自己实现⼀套分布式ID生成算法或者使⽤开源的⽐如雪花算法这种
3. 分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订
单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。
单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。
11. 分表后非sharding_key的查询怎么处理呢？
1. 可以做⼀个mapping表，比如这时候商家要查询订单列表怎么办呢？不带user_id（分库分表字
段）查询的话你总不能扫全表吧？所以我们可以做⼀个映射关系表，保存商家和用户的关系，查询
的时候先通过商家查询到用户列表，再通过user_id去查询。
段）查询的话你总不能扫全表吧？所以我们可以做⼀个映射关系表，保存商家和用户的关系，查询
的时候先通过商家查询到用户列表，再通过user_id去查询。
2. 打宽表，⼀般而言，商户端对数据实时性要求并不是很高，比如查询订单列表，可以把订单表同步
到离线（实时）数仓，再基于数仓去做成⼀张宽表，再基于其他如es提供查询服务。
到离线（实时）数仓，再基于数仓去做成⼀张宽表，再基于其他如es提供查询服务。
3. 数据量不是很大的话，比如后台的一些查询之类的，也可以通过多线程扫表，然后再聚合结果的
方式来做。或者异步的形式也是可以的。
方式来做。或者异步的形式也是可以的。
12. 说说 MySQL 主从同步怎么做的吧？
1. master 主库提交完事务后，将此次更新的
事件类型写入 binlog 文件中
事件类型写入 binlog 文件中
2. master 主库创建 log dump 线程通知
slave 从库，需要更新数据
slave 从库，需要更新数据
3. slave 从库向主库发送请求，并启动一个IO线程读取
主库的 binlog，记录到本地的 relay log 中继日志中。
主库的 binlog，记录到本地的 relay log 中继日志中。
4. slave 从库再开启一个sql线程读取relay log 中的
内容，并将其中的内容在本地重新执行一遍，完成主
从数据同步
内容，并将其中的内容在本地重新执行一遍，完成主
从数据同步
5. slave 从库记录自己的binlog日志
由于mysql默认的复制方式是异步的，主库把日志发送给从库
后不关心从库是否已经处理，这样会产生一个问题就是假设主
库挂了，从库处理失败了，这时候从库升为主库后，日志就丢
失了。由此产生两个概念。
后不关心从库是否已经处理，这样会产生一个问题就是假设主
库挂了，从库处理失败了，这时候从库升为主库后，日志就丢
失了。由此产生两个概念。
全同步复制
主库写⼊binlog后强制同步日志到从库，所有的从库都执行完成后
才返回给客户端，性能差。
才返回给客户端，性能差。
半同步复制
半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认
给主库，主库收到至少一个从库的确认就认为写操作完成
给主库，主库收到至少一个从库的确认就认为写操作完成
13. 那主从延迟怎么解决呢？
1. 针对特定的业务场景（对延迟很敏感的业务），读写请求都强制走主库
2. MySQL 5.6 版本以后，提供了一种「并行复制」的方式，通过
将 SQL 线程转换为多个 work 线程来进行重放。
将 SQL 线程转换为多个 work 线程来进行重放。
3. 「提高机器配置」(王道)
4. 在业务初期就选择合适的分库、分表策略，「避免单表
单库过大」带来额外的复制压力
单库过大」带来额外的复制压力
5. 「避免长事务」
6. 「避免让数据库进行各种大量运算」
14. 一条 SQL 语句在数据库框架中的执行流程？
15. 数据库的三范式是什么？
「第一范式」：数据库中的字段具有「原子性」，不可再分，并且是单一职责
「第二范式」：「建立在第一范式的基础上」，第二范式要求数据库表中的每个实例或行
必须「可以被惟一地区分」。为实现区分通常需要为表加上一个列，以存储各个实例的惟
一标识。这个惟一属性列被称为主键
必须「可以被惟一地区分」。为实现区分通常需要为表加上一个列，以存储各个实例的惟
一标识。这个惟一属性列被称为主键
「第三范式」：「建立在第一，第二范式的基础上」，确保每列都和主键列直接
相关，而不是间接相关不存在其他表的非主键信息
相关，而不是间接相关不存在其他表的非主键信息
注意：在我们的日常开发当中，「并不是所有的表一定要满足三大范式」，有时候
冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的
冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的
16. char 和 varchar 的区别？
17. 谈谈你对最左前缀原则的理解？
18. 什么情况下索引会失效？即查询不走索引？
联合索引非最左匹配
对索引使用函数/表达式计算
索引字段上使用（！= 或者 < >）判断时，
会导致索引失效而转向全表扫描
会导致索引失效而转向全表扫描
索引字段上使用 is null / is not null 判断
时，会导致索引失效而转向全表扫描。
时，会导致索引失效而转向全表扫描。
子主题
联合索引的前面索引列使用范围查询(<,>,like),会导致后续的索引失效
索引字段使用 like 以通配符开头（‘%字符串’）时，
会导致索引失效而转向全表扫描，也是最左前缀原则。
会导致索引失效而转向全表扫描，也是最左前缀原则。
索引字段是字符串，但查询时不加单引号，会导致
索引失效而转向全表扫描
索引失效而转向全表扫描
索引字段使用 or 时，会导致索引失效而转向全表
扫描（取决于or链接的字段是否都使用索引）
扫描（取决于or链接的字段是否都使用索引）
19. MySQL 问题排查都有哪些手段？
20. MySQL 数据库 CPU 飙升到 500% 的话他怎么处理？
21. 为什么推荐使用自增 id （整数）作为主键？
1. 普通索引的 B+ 树叶子结点上存放的是主键索引的值，如果该值
较大（多个字节），会「导致普通索引的存储空间较大」
较大（多个字节），会「导致普通索引的存储空间较大」
2. 使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，
直接「按照顺序插入」，不用刻意维护
直接「按照顺序插入」，不用刻意维护
3. 页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，
如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据
会频繁的变动，「导致页分裂维护成本较高」
如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据
会频繁的变动，「导致页分裂维护成本较高」
22. 执行一条 select 语句，期间发生了什么？
图解
总结
连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
1）预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
2）优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
3）执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
1）预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
2）优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
3）执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
23. 使用 Innodb 的情况下，一条更新语句是怎么执行的?
用以下语句来举例，c 字段无索引，id 为主键索引
update T set c=c+1 where id=2;
update T set c=c+1 where id=2;
找数据
1. 执行器先找引擎取 id=2 这一行。id 是主键，引擎直接用树搜索找到这一行
如果 id=2 这一行所在的数据页本来就「在内存中」，就「直接返回」给执行器
「不在内存」中，需要先从磁盘「读入内存」，然后再「返回」
如果 id=2 这一行所在的数据页本来就「在内存中」，就「直接返回」给执行器
「不在内存」中，需要先从磁盘「读入内存」，然后再「返回」
准备数据
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，
得到新的一行数据，再调用引擎接口「写入这行新数据」
得到新的一行数据，再调用引擎接口「写入这行新数据」
数据双写
3. 引擎将这行新数据更新到内存中，同时将这个更新操作「记录到 redo log 里面」，
此时 redo log 处于 「prepare」 状态。然后告知执行器执行完成了，随时可以提交
事务
此时 redo log 处于 「prepare」 状态。然后告知执行器执行完成了，随时可以提交
事务
持久化binlog
4. 执行器「生成这个操作的 binlog」，并把 binlog 「写入磁盘」
提交事务
5.执行器调用引擎的「提交事务」接口，引擎把刚刚写入的 redo log 改成提
交（commit）状态，「更新完成」
交（commit）状态，「更新完成」
24. Innodb 事务为什么要两阶段提交?
先写 redolog 后写binlog。假设在 redolog 写完，binlog 还没有写完的时候，
MySQL 进程异常重启，这时候 binlog 里面就没有记录这个语句。然后你会发
现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 「binlog 丢
失」，这个临时库就会少了这一次插入，恢复出来的这一行 c 的值就是 0，与
原库的值不同。
MySQL 进程异常重启，这时候 binlog 里面就没有记录这个语句。然后你会发
现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 「binlog 丢
失」，这个临时库就会少了这一次插入，恢复出来的这一行 c 的值就是 0，与
原库的值不同。
先写 binlog 后写 redolog。如果在 binlog 写完之后 crash，由于 redolog 还
没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是 binlog 里面已
经记录了“把c从0改成1”这个日志。所以，在之后用 binlog 来恢复的时候就
「多了一个事务出来」，恢复出来的这一行 c 的值就是 1，与原库的值不同。
没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是 binlog 里面已
经记录了“把c从0改成1”这个日志。所以，在之后用 binlog 来恢复的时候就
「多了一个事务出来」，恢复出来的这一行 c 的值就是 1，与原库的值不同。
可以看到，「如果不使用“两阶段提交”，那么数据库的状态
就有可能和用它的日志恢复出来的库的状态不一致」。
就有可能和用它的日志恢复出来的库的状态不一致」。
含义（什么是两阶段提交？）
由于 redo log 的提交分为 prepare 和
commit 两个阶段，所以称之为两阶段
提交。
由于 redo log 的提交分为 prepare 和
commit 两个阶段，所以称之为两阶段
提交。
在 MySQL 中，两阶段提交的主角就是 binlog
和 redolog 从上图中可以看出，在最后提交事
务的时候，有 3 个步骤：
1）写入 redo log，处于 prepare 状态
2）写 binlog
3）修改 redo log 状态变为 commit
和 redolog 从上图中可以看出，在最后提交事
务的时候，有 3 个步骤：
1）写入 redo log，处于 prepare 状态
2）写 binlog
3）修改 redo log 状态变为 commit
25. 什么是索引？以及好处和坏处
索引是一种帮助快速查找数据的数据结构，可以把它理解为书
的目录，通过索引能够快速找到数据所在位置。
的目录，通过索引能够快速找到数据所在位置。
索引数据结构有：
Hash表（通过hash算法快速定位数据，但不适合范围查询，因为需要每个key都进行一次hash）、
二叉树（查找和修改效率都比较高），但是在InnoDB引擎中使用的索引是B+Tree，相较于二叉
树，B+Tree这种多叉树，更加矮宽，更适合存储在磁盘中。使用索引增加了数据查找的效率，但
是相对的由于索引也需要存储到磁盘，所以增加了存储的压力，并且新增数据时需要同步维护索
引。但是合理的使用索引能够极大提高我们的效率！
Hash表（通过hash算法快速定位数据，但不适合范围查询，因为需要每个key都进行一次hash）、
二叉树（查找和修改效率都比较高），但是在InnoDB引擎中使用的索引是B+Tree，相较于二叉
树，B+Tree这种多叉树，更加矮宽，更适合存储在磁盘中。使用索引增加了数据查找的效率，但
是相对的由于索引也需要存储到磁盘，所以增加了存储的压力，并且新增数据时需要同步维护索
引。但是合理的使用索引能够极大提高我们的效率！
26. 什么时候适合用索引？什么时候不适合？
适合
字段有唯一性
where 条件经常用到的字段
group by 和 order by 经常用到的字段
where 条件经常用到的字段
group by 和 order by 经常用到的字段
不适合
字段频繁变化
字段数据大量重复
不经常用的字段
数据太少
字段数据大量重复
不经常用的字段
数据太少
27. 执行计划的参数有哪些？
possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型
type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见
扫描类型的执行效率从低到高的顺序为：
ALL（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。
扫描类型的执行效率从低到高的顺序为：
ALL（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。
28. count(*)和count(1)有什么区别？
count(*)=count(1)>count(主键)>count(字段)
29. 为什么采用 B+ 树，而不是 B-树作为索引的数据结构？
B+ 树只在叶子结点储存数据，非叶子结点不存具体数据，只存 key，查询更稳定，
增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定，那么相比 B 树，
B- 树这些「可以存更多的索引结点」，宽度更大，树高矮，节点小，拉取一次数
据的磁盘 IO 次数少，并且 B+ 树在叶子节点上加了双向指针，因此只需要去遍历
叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，
效率更高。
增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定，那么相比 B 树，
B- 树这些「可以存更多的索引结点」，宽度更大，树高矮，节点小，拉取一次数
据的磁盘 IO 次数少，并且 B+ 树在叶子节点上加了双向指针，因此只需要去遍历
叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，
效率更高。
30. WAl 是什么？有什么好处？
WAL 就是 Write-Ahead Logging，其实就是「所有的修改都先被写入
到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性。
到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性。
好处:
1.「读和写可以完全地并发执行」，不会互相阻塞
2. 先写入 log 中，磁盘写入从「随机写变为顺序写」，降低了 client 端的延迟。并且，
由于顺序写入大概率是在一个磁盘块内，这样产生的 io 次数也大大降低
3. 写入日志当数据库崩溃的时候「可以使用日志来恢复磁盘数据」
1.「读和写可以完全地并发执行」，不会互相阻塞
2. 先写入 log 中，磁盘写入从「随机写变为顺序写」，降低了 client 端的延迟。并且，
由于顺序写入大概率是在一个磁盘块内，这样产生的 io 次数也大大降低
3. 写入日志当数据库崩溃的时候「可以使用日志来恢复磁盘数据」
31. 什么是索引下推？
如果存在某些被索引的列的判断条件时，MySQL 将这一部分判断条件
传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务
器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给
MySQL 服务器」 。
传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务
器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给
MySQL 服务器」 。
32. 什么是最左前缀原则？
最左前缀其实说的是，在 where 条件中出现的字段，「如果只有组合索引中的
部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后
触发，最左面一个列触发不了，之后的所有列索引都无法触发。
部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后
触发，最左面一个列触发不了，之后的所有列索引都无法触发。
33. 普通索引和唯一索引该怎么选择？
查询
当普通索引为条件时查询到数据会一直扫描，直到扫完整张表
当唯一索引为查询条件时，查到该数据会直接返回，不会继续扫表
当普通索引为条件时查询到数据会一直扫描，直到扫完整张表
当唯一索引为查询条件时，查到该数据会直接返回，不会继续扫表
更新
普通索引会直接将操作更新到 change buffer 中，然后结束。
唯一索引需要判断数据是否冲突
普通索引会直接将操作更新到 change buffer 中，然后结束。
唯一索引需要判断数据是否冲突
所以「唯一索引更加适合查询的场景，普通索引更适合插入的场景」
34. MySQL 中有哪些日志？分别有什么作用？
binlog
定义：binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的
文件，用于「记录用户对数据库更新的SQL语句信息」。
文件，用于「记录用户对数据库更新的SQL语句信息」。
主要作用
主从复制
数据恢复
主从复制
数据恢复
undolog
定义：undolog 是 InnoDB 存储引擎的日志，是回滚日志，用于保证数据的原子性，
「保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修
改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读（MVCC）。
「保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修
改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读（MVCC）。
主要作用
事务回滚
实现多版本控制(MVCC)
事务回滚
实现多版本控制(MVCC)
redolog
定义：redolog 是 「InnoDB 存储引擎所特有的一种日志」，是重做日志，用于记录事务操作的
变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
主要作用
可以做「数据恢复并且提供 crash-safe 能力」。
当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的
数据，「等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中（刷盘）」。
可以做「数据恢复并且提供 crash-safe 能力」。
当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的
数据，「等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中（刷盘）」。
relaylog
含义：relaylog 是中继日志，「在主从同步的时候使用到」，它是一个中介临时的日志文件，用于
存储从master节点同步过来的binlog日志内容。
存储从master节点同步过来的binlog日志内容。
master 主节点的 binlog 传到 slave 从节点后，从结点开启 io 线程写入 relay log 里。然后从节
点的 sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从而「使从服务器和
主服务器的数据保持一致」。
点的 sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从而「使从服务器和
主服务器的数据保持一致」。
35. redolog 是怎么记录日志的？
InnoDB 的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小
是1GB，那么总共就可以记录4GB的操作。「从头开始写，写到末尾就又回到开头循
环写」。
是1GB，那么总共就可以记录4GB的操作。「从头开始写，写到末尾就又回到开头循
环写」。
所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「
内存抖动」现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷
盘了。
内存抖动」现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷
盘了。
36. redolog 和 binlog 的区别
所属层次
1.「redolog」 是 「Innodb」 独有的日志，而 「binlog」 是 「server」 层的，所有的存储引擎都有使用到
记录内容
2.「redolog」 记录了「具体的数值」，对某个页做了什么修改，「binlog」 记录的「操作内容」
文件大小
3.「binlog」 大小达到上限或者 flush log 「会生成一个新的文件」，而 「redolog」 有固定大小「只能循环利用」
子主题
4.「binlog 日志没有 crash-safe 的能力」，只能用于归档。而 redo log 有 crash-safe 能力。
37. 当前读和快照读？
快照读就是最普通的Select查询语句
当前读指执行insert，update，delete，select ...... for update，
select ...... lock in share mode语句时进行读取数据的方式
select ...... lock in share mode语句时进行读取数据的方式
38. 一条 Sql 语句查询一直慢会是什么原因？
「1. 没有用到索引」
比如函数导致的索引失效，或者本身就没有加索引
比如函数导致的索引失效，或者本身就没有加索引
「2. 表数据量太大」
考虑分库分表吧
考虑分库分表吧
「3. 优化器选错了索引」
「考虑使用」 force index 强制走索引
「考虑使用」 force index 强制走索引
39. 一条 Sql 语句查询偶尔慢会是什么原因？
「1. 数据库在刷新脏页」
比如 「redolog 写满了」，「内存不够用了」释放内存如果是脏页
也需要刷，mysql 「正常空闲状态刷脏页」
比如 「redolog 写满了」，「内存不够用了」释放内存如果是脏页
也需要刷，mysql 「正常空闲状态刷脏页」
「2. 没有拿到锁」
40. 删除表数据后表的大小却没有变动，这是为什么？
在使用 delete 删除数据时，其实对应的数据行并不是真正的删除，是「逻辑删除」，
InnoDB 仅仅是将其「标记成可复用的状态」，所以表空间不会变小
InnoDB 仅仅是将其「标记成可复用的状态」，所以表空间不会变小
41. 为什么 VarChar 建议不要超过255？
当定义varchar长度小于等于255时，长度标识位需要一个字节(utf-8编码)
当大于255时，长度标识位需要两个字节，并且建立的「索引也会失效」
42. 分布式式事务怎么实现?
1.「本地消息表」
2.「消息事务」
3.「二阶段提交」
4.「三阶段提交」
5.「TCC」
6.「最大努力通知」
7.「Seata 框架」
2.「消息事务」
3.「二阶段提交」
4.「三阶段提交」
5.「TCC」
6.「最大努力通知」
7.「Seata 框架」
43. 为什么不使用长事务？
1.并发情况下，数据库「连接池容易被撑爆」
2.「容易造成大量的阻塞和锁超时」
长事务还占用锁资源，也可能拖垮整个库，
3.执行时间长，容易造成「主从延迟」
4.「回滚所需要的时间比较长」
事务越长整个时间段内的事务也就越多
5.「undolog 日志越来越大」
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的
任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就
会导致大量占用存储空间
2.「容易造成大量的阻塞和锁超时」
长事务还占用锁资源，也可能拖垮整个库，
3.执行时间长，容易造成「主从延迟」
4.「回滚所需要的时间比较长」
事务越长整个时间段内的事务也就越多
5.「undolog 日志越来越大」
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的
任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就
会导致大量占用存储空间
44. buffer pool 是做什么的?
buffer pool 是一块内存区域，为了「提高数据库的性能」，当数据库操作数据的时候，
把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里
面的数据，数据库的增删改查都是在 buffer pool 上进行
把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里
面的数据，数据库的增删改查都是在 buffer pool 上进行
buffer pool 里面缓存的数据内容也是一个个数据页（16k）
其中「有三大双向链表」:
「free 链表」
用于帮助我们找到空闲的缓存页
「flush 链表」
用于找到脏缓存页，也就是需要刷盘的缓存页
「lru 链表」
用来淘汰不常被访问的缓存页，分为热数据区和冷数据区，冷数据区主要存放那些不常被用到的数据
「free 链表」
用于帮助我们找到空闲的缓存页
「flush 链表」
用于找到脏缓存页，也就是需要刷盘的缓存页
「lru 链表」
用来淘汰不常被访问的缓存页，分为热数据区和冷数据区，冷数据区主要存放那些不常被用到的数据
预读机制:
Buffer Pool 有一项特技叫预读，存储引擎的接口在被 Server 层调用时，会在响应的同时进行
预判，将下次可能用到的数据和索引加载到 Buffer Pool
Buffer Pool 有一项特技叫预读，存储引擎的接口在被 Server 层调用时，会在响应的同时进行
预判，将下次可能用到的数据和索引加载到 Buffer Pool
45. 说说你的 Sql 调优思路？
预防
1.「表结构优化」
1.1 拆分字段
1.2 字段类型的选择
1.3 字段类型大小的限制
1.4 合理的增加冗余字段
1.5 新建字段一定要有默认值
1.1 拆分字段
1.2 字段类型的选择
1.3 字段类型大小的限制
1.4 合理的增加冗余字段
1.5 新建字段一定要有默认值
4.「分库分表」
执行
2.「索引方面」
2.1 索引字段的选择
2.2 利用好mysql支持的索引下推，覆盖索引等功能
2.3 唯一索引和普通索引的选择
2.1 索引字段的选择
2.2 利用好mysql支持的索引下推，覆盖索引等功能
2.3 唯一索引和普通索引的选择
3.「查询语句方面」
3.1 避免索引失效
3.2 合理的书写where条件字段顺序
3.3 小表驱动大表
3.4 可以使用force index()防止优化器选错索引
3.1 避免索引失效
3.2 合理的书写where条件字段顺序
3.3 小表驱动大表
3.4 可以使用force index()防止优化器选错索引
46. 针对线上的数据库，你会做哪些监控？业务性能 + 数据安全 角度分析
业务性能
1、应用上线前会审查业务新增的 sql，和分析 sql 执行计划 比如是否存在 select * ，索引建立是否合理
2、开启慢查询日志，定期分析慢查询日志
3、监控CPU/内存利用率，读写、网关IO、流量带宽 随着时间的变化系统统计图
4、吞吐量 QPS/TPS，一天内读写随着时间的变化业务统计图
1、应用上线前会审查业务新增的 sql，和分析 sql 执行计划 比如是否存在 select * ，索引建立是否合理
2、开启慢查询日志，定期分析慢查询日志
3、监控CPU/内存利用率，读写、网关IO、流量带宽 随着时间的变化系统统计图
4、吞吐量 QPS/TPS，一天内读写随着时间的变化业务统计图
数据安全
1、短期增量备份，比如一周一次。 定期全量备份，比如一月一次
2、检查是否有非授权用户，是否存在弱口令，网络防火墙检查
3、导出数据是否进行脱敏，防止数据泄露或者黑产利用
4、数据库 全量操作日志审计，防止数据泄露
5、数据库账号密码 业务独立，权限独立控制，防止多库共用同个账号密码
6、高可用 主从架构，多机房部署
1、短期增量备份，比如一周一次。 定期全量备份，比如一月一次
2、检查是否有非授权用户，是否存在弱口令，网络防火墙检查
3、导出数据是否进行脱敏，防止数据泄露或者黑产利用
4、数据库 全量操作日志审计，防止数据泄露
5、数据库账号密码 业务独立，权限独立控制，防止多库共用同个账号密码
6、高可用 主从架构，多机房部署
收藏
0 条评论
下一页
