MySQL常见的面试题
一、 MyISAM和InnoDB的区别
MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了⼤量的特性，包括全⽂索引、压缩、空间函数等，但MyISAM不⽀持事务和⾏级锁，⽽且最⼤的缺陷就是崩溃后⽆法安全恢复。不过，5.5版本之后，MySQL引⼊了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。
- 是否支持行级锁
MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(rowlevel locking)和表级锁，默认为⾏级锁。 - 是否支持事务和崩溃后的安全恢复
MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。 - 是否⽀持外键
MyISAM不⽀持，InnoDB⽀持。 - 是否⽀持MVCC
仅 InnoDB ⽀持。应对⾼并发事务，MVCC⽐单纯的加锁更⾼效;MVCC只在READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作，MVCC可以使⽤乐观(optimistic)锁和悲观(pessimistic)锁来实现，各数据库中MVCC实现并不统⼀。
二、 什么是MVCC
MVCC全称Mutli Version Concurreny Control,多版本并发控制，也可称之为一致性非锁定读；它通过行的多版本控制方式来读取当前执行时间数据库中的行数据。实质上使用的是快照数据，这样就可以实现不加锁读。MVCC 主要应用于 Read Commited 和 Repeatable read 两个事务隔离级别。
1. 一般我们认为MVCC有下面几个特点：
- 每行数据都存在一个版本，每次数据更新时都更新该版本
- 修改时Copy出当前版本, 然后随意修改，各个事务之间无干扰
- 保存时比较版本号，如果成功(commit)，则覆盖原记录, 失败则放弃copy(rollback)
- 就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道, 因为这看起来正是，在提交的时候才能知道到底能否提交成功
2. 而InnoDB实现MVCC的方式是:
- 事务以排他锁的形式修改原始数据
- 把修改前的数据存放于undo log，通过回滚指针与主数据关联
- 改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）
三、 数据库的事务隔离机制
1. SQL 标准定义了四个隔离级别
- READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更， 可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据， 可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
- REPEATABLE-READ(可重复读)：对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改， 可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
- SERIALIZABLE(可串⾏化)：最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说， 该级别可以防⽌脏读、不可重复读以及幻读。
Mysql InnoDB默认的是REPEATABLE-READ（可重复读），在该事务隔离级别下使用的是next-key lock 锁算法，因此可以避免幻读的产生。
2. 并发事务带来哪些问题
- 脏读
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
丢失修改（Lost to modify）
指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。 - 不可重复读
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两 次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不 可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果 只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。 - 幻读
是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象 发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。 如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。
四、 索引
1. 什么是索引
索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。
2. 索引具体采用的哪种数据结构
常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树
3. B+树和Hash索引比起来有什么优缺点吗
- 因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描，减少I/O的次数。
- 哈希索引适合等值查询，但是无法进行范围查询；哈希索引没办法利用索引完成排序；哈希索引不支持多列联合索引的最左匹配规则；如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
4. 聚簇索引和非聚簇索引
- 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体的细节依赖于实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B+Tree索引和数据行。
- 当表中有聚簇索引时，它的数据实际上存储在索引的叶子页中（叶子页中包含了行的全部数据）。而没有聚簇索引时B+Tree叶子页存放的是指向数据的指针。（页是mysql存储引擎最小的存储单元，InnoDB每个页默认大小为16k）可以理解为 有聚簇索引时，数据和对应的叶子页在同一页中，没有聚簇索引时，叶子页和对应的数据不在同一页中。
- 聚簇索引查询会更快，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询。
5. 非主键索引一定会查询多次吗
通过覆盖索引也可以只查询一次，覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。
如表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = 'keytest';的时候，就可以通过覆盖索引查询，无需回表。
6. 创建索引
（1）创建索引时需要考虑的因素
我们一般对于查询概率比较高，经常作为where条件的字段设置索引，为了提高查询的效率，我们还可以创建联合索引。
（2）联合索引字段的排序
我们把识别度最高的字段放到最前面，也就是常说的左前缀匹配。
在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则
7. 索引下推，查询优化
Index Condition Pushdown（索引下推）
MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';可以将其关闭。官方文档中给的例子和解释如下：
people表中（zipcode，lastname，firstname）构成一个索引
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。
如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。
8. 怎么判断索引有没有生效
可以通过explain查看sql语句的执行计划，通过执行计划来分析索引使用情况。
可以看这篇文章，写的很清楚：https://blog.csdn.net/qq_42819407/article/details/98603953?utm_medium=distribute.pc_relevant.none-task-blog-searchFromBaidu-2.control
9. 什么情况下创建了索引却没有用到索引
查询优化器，一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。
这个成本最低的方案就是所谓的执行计划。优化过程大致如下：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
五、 Mysql的锁机制
1. MyISAM和InnoDB存储引擎使⽤的锁
MyISAM采⽤表级锁(table-level locking)。
InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁
- 表级锁： MySQL中锁定 粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也
⽐较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低，
MyISAM和 InnoDB引擎都⽀持表级锁。 - ⾏级锁： MySQL中锁定 粒度最⼩ 的⼀种锁，只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减
少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死
锁。 - PS:行级锁是怎么实现的
2. InnoDB存储引擎的锁的算法有三种
- Record lock：单个⾏记录上的锁
- Gap lock：间隙锁，锁定⼀个范围，不包括记录本身
- Next-key lock： record+gap 锁定⼀个范围，包含记录本身
（1）innodb对于⾏的查询使⽤next-key lock
（2）Next-locking keying为了解决Phantom Problem幻读问题
（3）当查询的索引含有唯⼀属性时，将next-key lock降级为record key
（4）Gap锁设计的⽬的是为了阻⽌多个事务将记录插⼊到同⼀范围内，⽽这会导致幻读问题的产⽣
（5）有两种⽅式显式关闭gap锁：（除了外键约束和唯⼀性检查外，其余情况仅使⽤record lock）A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1 - PS：什么是间歇锁
六、 分表
1. 分表有哪些方式
垂直根表和水平分表
2. 分表后怎么进行多字段查询
加冗余表，一条订单信息多次落盘
