Mysql面试题整理

Q1. MVCC是什么？如何实现？

A1. MVCC：多版本并发控制。MVCC维持一个数据的多个版本，使得读写没有冲突。
MVCC的实现依赖于：3个隐式字段+undo日志+Read View
3个隐式字段：

• DB_TRX_ID：最近修改（包括插入的）事务ID，记录创建这条记录或者最后一次修改该记录的事务ID；
• DB_ROLL_PTR：回滚指针。指向这条记录的上一个版本。
• DB_ROW_ID：隐含的自增ID（隐藏主键）。如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引。
• 还有一个删除flag隐藏字段：记录被更新或者删除并不是真的删除，而是删除flag值改变。

undo日志

• insert undo log：代表事务在insert新记录时产生的undo log，只在事务回滚时需要，事务提交后就会被立刻清除。
• update undo log: 事务在进行update或者delete时产生undo log；不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除。只有在快速读或者事务回滚不涉及该日志时，对应的日志才会被purge线程同一清除。
  undo日志执行流程：
  
  

Read View(读视图)

ReadView就是事务进行快照读时产生的。在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID。
相关属性：
creator_trx_id: 当前事务id
m_ids: 所有事务的事务id
min_trx_id: m_ids里最小的事务id值
max_trx_id: 最大的事务id，即已出现的最大事务ID+1；

1. DB_TRX_ID < min_trx_id ：则表示当前事务能看到DB_TRX_ID 所在的记录。
2. DB_TRX_ID >= max_trx_id: 则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见
3. min_trx_id <= DB_TRX_ID <max_trx_id: 分两种情况：
   如果这个DB_TRX_ID 在m_ids中，表示这个版本是由还未提交的事务生成的，所以是不可见的。如果不在，表示这个版本是由已经提交的事务生成的，所以是可见的。
   

高性能MYSQL解答：
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现：一个保存创建时间；一个保存过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号，每开启一个事务，系统版本号就会自增。事务开始时刻的系统版本号会作为当前事务的版本号，用来和查询到的每行记录的版本号作对比。MVCC具体操作如下：
SELECT：
InnoDB会根据两个条件查询每行记录
1. InnoDB只查找版本遭遇当前事务版本的数据号（也即是行的系统版本号小于或等于事务的版本号），这样可以确保事务读取的行，要么是在事务开启前就已经存在，要么是事务自身插入或者修改过。
2. 行的删除版本未定义，要么大于当前事务版本号，确保事务读取到的行，在事务开启前未被删除。
INSERT： InnoDB为新插入的每一行保存当前系统版本号作为行版本号；
DELETE：InnoDB为每删除的每一行保存当前系统版本号为行删除标识；
UPDATE：InnoDB为插入一行新纪录，保存当前系统系统版本号作为行版本号，同时保存当前系统版本号为undo log中最新旧数据的行删除标识。
MVCC是的大多数的读操作可以不加锁，但只在REPEATABLE READ 和 READ COMMIT两个隔离级别下工作。

Q2. 慢查询优化，using filesort什么意思

1. 避免回表查询
2. 如果需要对两个字段进行and查询，使用符合索引代替两个单独的索引
3. 避免索引失效
4. 优化数据结构
5. 使用inner jion代替 左连接或者右连接
   using filesort是指：如果查询没有使用索引，就是filesort。

Q3. 索引失效的情况

1. 索引列参与计算
2. like关键字：在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用
3. 使用not ， <>, != , is not null
4. or 语句前后没有同时使用索引
5. 数据类型出现隐式转化。

Q4.数据库索引的分类

索引可以分为单列索引和联合索引
其中 单列索引又可以分为：主键索引、唯一索引、普通索引和全文索引。

Q5.什么是最左前缀匹配/联合索引什么时候会失效

在MYSQL建立联合索引时会遵循最左前缀匹配原则，即检索数据是从联合索引的最左边开始匹配的。

联合索引失效：
查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询，如果出现了第一列则会利用联合索引；即不满足左前缀匹配原则的联合索引会失效。

Q6. 索引的优势

1. 通过创建唯一索引，可以保证每一行数据的唯一性；
2. 可以大大提高查询速度；
3. 可以加速表与表的连接；
4. 可以显著减少查询中分组和排序时间；
5. 通过使用索引，可以在查询过程中使用优化隐藏器，提高系统性能；

所以重复率小的列建议生成索引。因为重复数据少，索引树查询更有效；
数据具有唯一性，建议生成唯一索引，保证数据正确性；
频繁group by、order by的列建议生成索引，可以提高分组排序效率；
经常用于查询条件的字段建议生成索引，通过索引查询，速度更快。

Q7. 索引是越多越好吗？

不是；
索引需要维护，不仅耗费时间，同时也占用磁盘资源；
频繁更新的数据，不宜建索引。数据频繁更新，触发索引频频维护，降低写速度；
数据量小的表没必要建立索引。

Q8 数据库存储引擎

数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。
数据库常见的存储引擎有：
InnoDB 、 MyISAM、MEMORY等

InnoDB:是数据库的默认事务型存储引擎。它被设计用来处理大量短期事务。用文件.frm存储表结构，而数据存储在表空间中。采用MVCC来支持高并发，并且实现了四个标准的隔离级别。默认级别的可重复读（REPEATABLE READ）,并且使用间隙锁防止幻读。InnoDB的表是基于聚簇索引建立的，支持表锁行锁等；具有自动奔溃恢复等特性，使其具有很高的优越性，一般都选择InnoDB作为存储引擎。
MyISAM: 在5.1前是默认存储引擎，提供大量特性：包括全文索引，压缩等，但不支持事务和行锁；还有就是崩溃后无法完全恢复。MyISAM会将表存储在两个文件中，索引文件（.MYI）和数据文件（.MYD）。
Archive:只支持INSERT 和 SELECT操作。它会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘IO更少。但每次SELECT都需要进行全表扫描，所以更适合日志和数据采集类应用。支持行级锁和专用缓冲区，使得可以实现高并发插入。总而言之，是一个针对高速插入和压缩做了优化的简单引擎。
Memory:所有的数据都保存在内存中，不需要磁盘IO。其表结构在重启后仍会保留，但数据会丢失。支持哈希索引，所以查找特别快。只支持表级锁，所以写入性能较低。不支持BLOB和TEXT，且每行长度固定，会造成一定的内存浪费。

Q9 InnoDB与MyISAM的区别

	InnoDB	MyISAM
支持外键	支持	不支持
锁	支持行锁，锁粒度更小	只支持表锁
索引	使用聚簇索引，索引和数据存在一个文件	使用非聚簇索引，索引和数据分开，是缓存索引
事务	支持事务	不支持
全文索引	支持全文索引	不支持全文索引
唯一索引	必须要有唯一索引	可以没有
存储文件	frm 、ibd	frm、MYD、MYI
其他	InnoDB不保存具有的行数，执行select count(*) from table需要全表扫描	用一个变量保存了整个表的行数

Q10 数据库的底层数据结构

数据库的底层是B+树结构

B+树特点：

1. 非叶子节点只存储索引而不存储数据；数据存储在叶子节点上；
2. 父节点包含其右孩子节点的第一个索引；
3. 每个叶子节点都有一个指向相邻叶子节点的指针；
4. 叶子结点按关键字升序存储；

为什么选择B+树而不是B树？

1. 因为B+树的内部节点相对于B树更小，一个磁块能存储更多的关键字，一次性查找的关键字也就越多，即B+树的磁盘读写代价更小；
2. B+树每个关键字查询路径都是一样长的，所以查询更稳定；
3. 更适用于范围查询，只需要遍历叶子结点就能实现全部数据的遍历。

为什么选择B+树而不是红黑树？

因为如果数据比较多时，红黑树的深度会过深，从而导致磁盘IO次数频繁。
而B树是为磁盘等辅助设备设计的多路平衡查找树，其每个非叶子结点可以存储多个数据，所以它的高度往往小于AVL树和红黑树，磁盘IO次数大大减小。

为什么选择B+树而不是哈希索引

哈希索引就是将键值换算成哈希值存储。查询速度快
如果只有等值查询，那么哈希索引的查询有绝对优势；
但哈希索引并不支持范围查询，因为原先有序的键值经过哈希换算后就可能不连续了；
其次哈希索引没办法利用索引完成排序；
在键值数量大的情况下，因为存在哈希碰撞，也同样会导致哈希索引的效率变低。

Q11 聚簇索引 VS 非聚簇索引

聚簇索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是表的行数据。
优点：
数据访问更快
对于主键的排序查找和范围查找速度快
缺点：
插入严重依赖顺序插入，否则会出现页分裂，严重影响性能。所以一般都会定义一个自增ID为列的主键。
更新主键代价高
二级索引需要两次索引查找，第一次找到主键，第二次根据主键找到行数据。
会导致全表扫描速度变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续。

非聚簇索引（辅助索引）：辅助索引的叶子节点存储的不是物理位置而是主键值，需要通过辅助索引找到主键值，但通过主键值找到数据行的数据页。

为什么要用int型自增id，用string不行么

因为聚簇索引依赖于顺序插入，自增ID可以减少页分裂，是的数据存储比较连续。
同时自增ID也能保证唯一性。
还有就是INT型往往更快，是作为标识列的最佳选择。

Q12 怎么设计高效的索引

1. 选择唯一性索引
2. 为经常需要排序、分组和联合操作的字段建立索引
3. 为经常作为查询条件的字段建立索引
4. 限制索引数目
5. 尽量使用数据比较短的字段作为索引
6. 数据量小的表最好不要索引
7. 最好使用前缀索引
8. 删除不再使用或者甚少使用的索引

Q13 行级锁 表级锁 页级锁（锁粒度角度）

表级锁：分为表独占锁和表共享锁。开销小、加锁快，锁粒度大，发生索冲突最高，并发度低。
行级锁：分为共享锁和排他锁。锁粒度最细。开销大，发生锁粒度冲突概率最低，并发度高。会出现死锁。
页级锁：锁粒度介于表级锁和行级锁中，一次锁定相邻的一组记录，也会出现死锁，并发度一般。

Q14 共享锁 排他锁 意向锁（锁级别角度）

共享锁：又叫读锁。是读取操作创建的锁。其他用户可以并发的读取数据，但不能修改，直到释放共享锁。
用法：SELECT … LOCK IN SHARE MODE
排他锁：又称写锁、独占锁。当数据加上排他锁之后，其他任何事务都不能对该数据进行任何的封锁。而获得排他锁的事务既能取读数据，又能修改数据。
用法：SELECT … FOR UPDATE;

意向锁：
意向锁的原因：如果事务A用行锁锁住一行，事务B申请表的互斥锁，想要修改整个表。这是行锁和表锁就会产生冲突。所以事务B必须等到事务A释放行锁，但事务B并不知道事务A锁住的哪一行，所以必须对整个表的每一行进行遍历判断是否有行锁，这个过程就比较耗时。
意向锁就是为了解决这个问题：事务A在加行锁前向给表加上一个意向锁，此时事务B来获取表锁时可以先判断是否有意向锁，如果有意向锁，则阻塞，等待事务A的锁释放。

意向锁属于表锁，意向锁之间并不互斥，即使是两个意向互斥锁也是可以共存的。

Q15 自动锁 显示锁

自动锁：当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。
DML锁：用于控制并发事务中的数据操纵，保证数据的一致性和完整性；
DDL锁：用于保护数据库对象的结构，如表、索引等的结构定义。

显示锁：用户手动请求的锁

Q16 乐观锁 悲观锁

乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁：对数据被外界修改保持保守态度，在整个数据处理过程中，将数据处于锁定状态。
悲观锁流程：
对任意记录进行修改前，想尝试为该记录加上排他锁，如果加锁失败，说明该记录正在被修改，那么当前查询等待或直接抛出异常，如果加锁成功，那么就可以对数据进行操作，事务完成之后就会释放锁。在这个过程期间如果有其他事务对该数据进行修改或加排他锁操作，那么同样会等待或直接抛出异常。
不足：悲观并发控制实际上就是“先加锁，再访问”的保守策略，为数据处理的安全提供了保障。但是在效率方面，加锁操作会产生额外的开销，还会增加死锁的机会。而且对于只读事务，没必要使用锁，加锁只会增加系统的负载。另外还降低了并行性。

乐观锁：乐观锁假设认为一般情况下数据是不会有冲突的，所以在数据进行提交更新的时候，才会正式对数据是否有冲突继续检测。如果发现重读，则返回错误信息，让用户决定接下来的操作。
乐观锁实现流程：

1. 版本号（version）：给数据增加一个版本标识，也就是增加一个version字段，每次更新，这个字段就+1。读取数据时，会把version读取出来，在更新的时候比较version，如果还是一开始读取的版本，就可以对数据进行更新。如果version不一致，说明有其他事务对这个数据进行了修改，这时返回一个无法更新的通知，由用户决定怎么处理。
2. 时间戳，同上。
   优点与不足：乐观并发控制认为事物之间的数据冲突很小，因此尽可能直接做下去，知道提交的时候才会进行判断，所以避免了任何锁和死锁，效率高。但一旦锁粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。

Q18 间隙锁（Gap Lock）vs 记录锁（Record Lock）vs Next-key Lock

间隙锁是InnoDB在可重复读提交下为了解决幻读问题而引入的锁机制。是一个在索引记录之间的间隙上的锁，会用在非唯一索引或不走索引的当前读中。会阻塞insert操作。
记录锁是单个行记录上的锁，锁定的是记录上的索引而不是记录本身。
Next-key Lock：是Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。

在InnoDB存储引擎中，SELECT 的操作时不可重复读问题是通过MVCC得到解决的。而UPDATE、DELETE得不可重复读问题是通过Record Lock解决的，INSERT的不可重读问题是通过Next-key Lock解决的。

Q19 死锁

事务A与事务B同时锁住对方需要的数据资源但又企图去获取对方锁定的数据资源。从而双方都在等待对方释放资。

产生死锁的四个必要条件：
1 互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用
2 请求和保持条件：进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
3 不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
4 环路等待条件：发生死锁时，必然存在一个 进程——资源 的环形链。

解决死锁的方法：

1. 按同一顺序访问对象
2. 避免事务中的用户交互
3. 保持事务间断并在一个批处理中
4. 使用低隔离级别

Q20 事务的四大特性

1. 原子性：事务包含的所有操作要么全部成功，要么全部回滚。
2. 一致性：事务必须使数据库从一个一致性状态转换到另一个一致性状态。一个事务执行之前和执行之后都必须处于一致性状态。
3. 隔离性：一个事务的执行不能被其他事务干扰。
4. 持久性：事务一旦提交，它对数据的改变是永久性的。

Q21 脏读 不可重复读 幻读

脏读：其实就是读到了别的事务回滚前的脏数据。也就是说，事务B对某个数据x进行修改，在为提交前，事务A读取了数据x，而事务B却回滚了。
不可重复读：事务先读取了一次数据，再次读取时这个数据已经是别的事务修改成功后的数据，导致数据两次读取不一样。也就是说，事务A读取了数据x，然后执行逻辑是的时候，事务B对这个数据x进行修改，导致事务A再次读取这个数据时，发现数据不一致了。
幻读：当前数据第一次读取到的数据比后一次读取到的数据条目不一致。比如事务A根据条件索引获得N条数据，然后事务B增添了M条，事务A再次搜索发现有N+M条，这就是幻读。

Q22 事务的隔离级别

Read uncommitted （读未提交）：一个事务可以读取另一个事务未提交的数据。
Read committed（读已提交）：一个事务要等到另一个事务提交后才能读取数据。
Repeatable read（可重复读）：在开始读取数据时，不允许修改操作。
Serializable（可序列化）：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

	脏读	不可重复读	幻读
Read uncommitted （读未提交）	√	√	√
Read committed（读已提交）	×	√	√
Repeatable read（可重复读）	×	×	√
Serializable（可序列化）	×	×	×

× 表示不会出现；√表示可能出现。

MySQL的默认隔离级别是Repeatable read（可重复读）

Q23 三大范式

1. 第一范式：列不可再分
   每一列属性都是不可再分的属性值，确保每一列的原子性
   两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据
2. 第二范式：属性完全依赖于主键
   第二范式是在第一范式的基础上建立起来的。要求数据库表中的每一个实例或者行必须可以被唯一区分。（通常为每一行添加一个唯一标识）
3. 第三范式：属性不依赖于其它非主属性 [ 消除传递依赖 ]
   第三范式是在第二范式的基础上建立的，要求一个数据库表中不包含已在其他表中已包含的非主关键字信息
4. BC范式：所有非主属性对每一个码都是完全函数依赖；所有的主属性对于每一个不包含它的码，也是完全函数依赖；没有任何属性完全函数依赖于非码的任意一个组合

Q24 mysql中的日志

1. redo log(重做日志)：InnoDB存储引擎层的日志
   作用：确保事务的持久性。在发生故障是，尚有数据未写入磁盘，在重启mysql服务后，会根据redo log进行重写，从而保证了事务的持久性。
   redo log 是物理日志，记录的是事务在某个数据页上做了什么修改。
   存储： redo log 的空间大小是固定的
   
   如图一般分为四个空间，wirte pos是当前记录的位置，一边写一边后移；check point 是当前要擦除的位置，也是一边擦除一边后移，擦除前要把记录更新到数据文件。两者之间的空间可用来写日志。当write pos追上check point时，就不能进行更新，必须先擦除一些数据。

2. bin log (二进制日志)：数据库server层的日志，所有引擎又可以有binlog日志。又被成为归档日志，是以二进制的形式记录语句的原始逻辑。
   概念：bin log记录了数据库表结构和表数据的变更（不包括select）。是逻辑日志。使用追加的方式写入的，当一个文件大小到达给定值时，就会生成新的文件用于存储
   作用：binlog有两个作用：主从复制 和 恢复数据

3. undo log(回滚日志)
   undo log主要存储的也是逻辑日志。记录数据的修改的反向操作。比如要insert一条记录，undo log 会记录一条对应的delete日志。这样可以方便事务的回滚。
   此外 undo log 存储的修改之前的数据，用于MVCC实现。

快照读 和 当前读

快照读：MVCC的SELECT操作时快照中的数据，不需要进行加锁操作

当前读：MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

自增ID用完了会怎么样

1. 如果设置了主键，将会报错 主键冲突；
2. 如果没有设置主键，数据库会自动生成一个全局row_id，新的数据会覆盖旧的数据。

————————待更新