MySQL高频面试题

1.数据库存储引擎有：MyIsam、InnoDB、Memory、Archive、Federated

2.InnoDB采用的是B+树结构存储，B树的每个节点对应innoDB的一个page，一个page的大小一般为16K，非叶子节点只有键值，叶子节点包含完整数据

适用场景：

1）经常更新的表，适合处理多重并发的更新请求。

2）支持事务。

3）可以从灾难中恢复（通过 bin-log 日志等）。

4）外键约束。只有他支持外键。

5）支持自动增加列属性 auto_increment。

经典题目

1.B树和B+树的区别？

B树的特点：

1. 一个节点里面有多个元素

2. 排了序

   

B+树的特点：

1. 一个节点里面有多个元素
2. 排了序
3. 叶子节点之间有指针
4. 非叶子节点上的元素都冗余了一份在叶子节点上（往B+树上面存的元素都会存在叶子节点上，且有序有指针连接）

2.Innodb中的B+树有什么特点？

在Mysql中，指针是双向的

3.什么是Innodb中的page？

B树的每个节点对应innoDB的一个page，一个page的大小一般为16KB，非叶子节点只有键值，叶子节点包含完整数据

（通过page去查询和插入，而不是一条一条查询，减少磁盘io）

在innodb中，在插入数据时，会按照索引字段升序排序插入，所以推荐主键索引字段id使用自增，以减少插入时耗费的性能。

页目录：存储每组数据的起始位置的元素序号值(索引值)以及指针指向每组的起始元素，减少查询时的遍历，是一种以空间换时间的思想。

在innodb中主键索引就是聚簇索引

在B+树中，从叶子节点开始从左往右找叫做全表扫描，从上往下找走索引

4.Innodb是如何支持范围查找走索引的？（比如查找为主键的a字段，a>6的记录）

a>6:先找a=6，再返回后面所有的数据

a<6:先找a=6，再返回前面所有的数据（双向指针,更好的支持范围查找）

5.为什么要遵循最左前缀原则才能利用到索引？

联合索引： 通过联合索引找到主键值后，再回主键索引中查找数据（回表）

最左前缀原则： 比如bcd联合索引，查询时必须要给出最左边的索引，即b必须要给。在从上往下扫描时，要满足最左前缀原则，如果是从叶子节点从左往右扫描（比如没有where条件），不需要满足。

索引下推（index condition Pushdown）： 是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。（索引优化）

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

demo：https://baijiahao.baidu.com/s?id=1716515482593299829&wfr=spider&for=pc

6.范围查找导致索引失效原因

范围查找不一定会导致索引失效，引擎会根据搜索效率判断是走索引还是全表扫描，当走索引（数据多时，回表次数增加）效率低下时，索引失效，走全表扫描

7.覆盖索引的底层原理

覆盖索引： 只通过索引，就取到了所需要的数据，即需要查询的字段就是索引字段（或者主键字段），则使用索引，不需要回表

8.索引扫描底层原理

当没有加where条件时，查询联合索引中的索引字段值（不需要满足最左前缀原则），还是会走索引，不会走全表扫描。因为叶子节点通过索引存储相比于主键索引的叶子节点存储所有数据，所占的内存小，在查询时，可能只需遍历3个page，而全表扫描需要遍历4个page

9.order by为什么会导致索引失效？

select * from t1 order by b,c,d; bcd字段为联合索引

• 走bcd索引 不需要排序+回表8次
• 全表扫描 额外排序（内存，速度非常快） + 不用回表 √

select b from t1 order by b,c,d; 走索引

10.MySQL中的数据类型转换有哪些需要注意的？

在MySQL中，当查询条件的字段为数值类型时，对于不是数字的字符会转化成0，对于是数字的字符转化成数字

对字段进行了类型转换就用不了索引

11.对字段进行操作导致索引失效原理

对字段进行加减法操作或者类型转换后会导致索引失效，因为如果还走索引可能会破坏B+树的整体结构

---

12.MySQL的锁有哪些，如何理解？

按锁粒度分类：

1. 行锁：锁某行数据，锁粒度最小，并发度高，会出现死锁。
2. 表锁：锁整张表，锁粒度最大，并发度低，不会出现死锁。
3. 间隙（页面）锁：锁的是一个区间，会出现死锁。

还可以分为：

1. 共享锁：也就是读锁，一个事务给某行数据加了读锁，其它事务也可以读，可以加读锁，但是不能写，也不能加写锁。
2. 排它锁：也就是写锁，一个事务给某行数据加了写锁，其他事务可以读，但是不能加读锁，不能加写锁，也不能写。

还可以分为：

1. 乐观锁：乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据，并不会真正的去锁每行数据，而是通过一个版本号实现。
2. 悲观锁：悲观锁就是在自己读或写数据的时候认为别人也刚好在写或者读数据，所以会对自己操作的数据加锁，只有等自己的整个事务提交了，才释放自己加上的锁。上面的行锁、表锁都是悲观锁。

13.Mysql慢查询该如何优化

1. 检查是否走了索引，如果没有则优化SQL利用索引
2. 检查所利用的索引是否是最优索引
3. 检查所查字段是否都是必须的，去掉查出的多余数据
4. 检查表中的数据是否过多了，是否应该进行分库分表
5. 检查数据实例所在机器的性能配置是否太低，是否可以适当增加资源

14.聚簇索引和非聚簇索引的区别

都是B+树的数据结构

• 聚簇索引：将数据存储和索引放到了一起，并且是按照一定的顺序组织的，找到了索引也就找打了数据，数据的物理存储顺序与索引数据是一致的。
• 非聚簇索引：非聚簇索引中叶子节点不存储数据，存储的是数据行地址（辅助索引存放的是主键id），也就是说根据索引查找到数据行的位置，再去磁盘查找数据，也就是回表（非覆盖索引的情况下）。

15.如何实现分库分表？

水平分表通过保证id的唯一性拆分，分库可以使用hash方法，使用id对库的分区号取模，来保证负载均衡

垂直分表通过引入关联列类似于外键来拆分。

16.存储拆分后如何解决唯一主键？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

• UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

• 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

• 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

• Twitter的snowflake算法 ： SnowFlake算法是Twitter设计的一个可以在分布式系统中生成唯一的ID的算法，它可以满足Twitter每秒上万条消息ID分配的请求，这些消息ID是唯一的且有大致的递增顺序。

• 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。

17.ACID靠什么保证的

​ 1、原子性是由undolog日志来保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。

​ 2、一致性是由其他三大特征保证，程序代码要保证业务上的一致性。

​ 3、隔离性是由MVCC来保证

​ 4、持久性由redolog来保证，MySQL修改数据的时候会在redolog中记录一份日志数据，就算数据没有保存成 功，只要日志保存成功了，数据仍然不会丢失。

18.什么是MVCC

MVCC 全称是 Multi-Version Concurrency Control，即多版本并发控制，在读取数据时通过一种类似快照的方式将数据保存下来，它可以让读写操作不互斥，提高数据库的并发性能和一致性。

InnoDB行记录中出了记录业务数据外，还有两个隐藏的字段，分别是trx_id和roll_pointer。

trx_id表示最近修改该行记录的事务id，roll_ptr相当于一个指针，指向上一个版本的地址。

InnoDB中使用undo log来实现多版本和事务回滚的功能，undo log分为insert undo log和update undo log。

• insert undo log记录了插入操作产生的新行记录，在事务提交后可以删除。
• update undo log记录了个更新或删除操作之前的旧记录，在事务提交后不能立即删除，因为其它事务可能需要这个undo log来获取某个版本的数据。

InnoDB使用ReadView来判断一个数据版本是否对当前事务可见的一个数据结构，Mysql支持四种隔离级别：（READ COMMITTED）读已提交，（READ UNCOMMITTED）读未提交，（REPEATABLE READ）可重复读，（SERIALIZABLE）串行化。其中MVCC只在读已提交和可重复读两种级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行；而 SERIALIZABLE 则会对所有读取的行都加锁。

- 对于 READ COMMITTED 隔离级别，每次执行查询语句时都会生成一个新的 ReadView，这样可以保证每次查询都能看到最新提交的数据。

- 对于 REPEATABLE READ 隔离级别，第一次执行查询语句时会生成一个 ReadView，并在整个事务中复用这个 ReadView，这样可以保证在同一个事务中多次查询结果一致。

https://baijiahao.baidu.com/s?id=1764154588499784629&wfr=spider&for=pc

19.Mysql主从同步原理

Mysql主从复制中主要有三个线程，Master一条线程和Slave中的两条线程，master（binlog dump thread）、slave（I/O thread 、 SQL thread）。

• 主库将数据的操作记录到一个二进制日志中，即binlog。
• 当binlog日志有变动时，log dump线程读取其内容并发送给从库。
• 从库的I/O线程接受binlog内容，并将其写入到relay log中继日志中。
• 从库的SQL线程读取relay log文件内容对从库数据更新进行重放，最终保证数据的一致性。

主从复制的三种方式：

1. 异步复制： 主库只需要完成自己的数据库操作即可，至于从库是否收到二进制日志，是否完成操作，主库不用关心。这是Mysql默认的复制方式，但是会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库就升为主库，日志就丢失了。
2. 同步复制： 主库等所有从库执行完成后才返回给客户端，这个方式显然性能会受到很大影响。
3. 半同步复制： 主库只保证至少一个从库操作成功后就返回。

20.事务的基本特征和隔离级别

事务基本特性ACID分别是：

​ 1、原子性指的是一个事务中的操作要么全部成功要么全部失败。

​ 2、一致性是由其他三大特征保证，指的是数据库总是从一个一致性的状态转换到另一个一致性的状态，程序代码要保证业务上的一致性，比如转账案例余额不足。

​ 3、隔离性指的是一个事务在最终提交前，对其他事务不可见，隔离性是由MVCC来保证。

​ 4、持久性由redolog来保证，MySQL修改数据的时候会在redolog中记录一份日志数据，就算数据没有保存成 功，只要日志保存成功了，数据仍然不会丢失（一旦提交事务，所做的修改就会永久保存到数据库中）。

隔离性有4个隔离级别：

1. read uncommit 读未提交： 可能会读到其它事务未提交的数据，如果其它更新事务因为某种原因操作失败回滚了，就会出现脏读。
2. read commit 读已提交： 两次读取结果不一致，也叫不可重复读。
3. repeatable read 可重复读： 这是mysql的默认级别，可能会产生幻读，幻读是指在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几行数据，而另一个事务在此时插入了几行数据，先前的事务在查询中，就会发现有几行数据是之前没有的。
4. serializable 串行化： 一般不会使用，会给每一行读取的数据都加锁，会导致大量超时和锁竞争问题。