MySQL面试题

前言：本文大量学习参考自：https://joonwhee.blog.csdn.net/article/details/106893197

MySQL 的事务隔离级别有哪些？分别用于解决什么问题？

　　Read-Uncommited，读未提交。存在脏读问题。

　　Read-Commited，读已提交。解决脏读问题，存在不可重复读问题。

　　Repeatable-Read，可重复度。解决不可重复读问题，存在幻读问题。

　　Serialization，串行化。解决幻读问题。

MySQL 的可重复读怎么实现的？

　　通过MVCC的快照读（当前事务版本ID介于创建版本ID和删除版本ID之间），当前事务第二次读取同一条数据时，不再访问数据库，而是去快照缓存中读取。

　　DB_TRX_ID，creator_id：生成快照读时事务id；trx_ids：生成快照读时正在活跃的事务；up_limit_id，low_limit_id：trx_ids最小和最大的两个id。

　　可以读到快到读的条件：

　　1）当前事务id=creator_id

　　2）当前事务id<up_limit_id

　　3）当前事务介于up_limit_id和low_limit_id之间，但不在trx_ids中

那 MVCC 解决了幻读了没有？

　　用间隙锁解决了。

什么是索引？常见的索引类型有哪些？

　　官方定义：MySQL帮助高效查找数据行的数据结构。

　　常见索引类型：B树，B+树，hash索引。

为什么MySQL数据库要用B+树存储索引？而不用红黑树、Hash、B树？

　　B树：B+树非叶子结点不存储数据行，相比于B树可容纳更多结点，更加“矮胖”，树的深度即IO次数，矮树效率更高。范围查询B树可能需要跨层，中序遍历，更多IO。

　　Hash：不支持范围，不支持排序，不支持联合索引最左匹配原则。

　　红黑树：红黑树在内存中效率比较高，但是在磁盘上效率主要看树的高度，红黑树是二叉树，树的高度更高，需要更多IO操作。

B树和B+树结构参考文章：https://blog.csdn.net/login_sonata/article/details/75268075

MySQL 中的索引叶子节点存放的是什么？

　　InnoDB：主键索引：Key+数据行。非主键索引：Key+主键。

　　MyiSAM：存放Key+数据行所在地址。

什么是聚簇索引（聚集索引）？

　　存放Key+数据行的索引。Innodb的主键索引就是，没有主键会用非空唯一索引，没有的话隐藏列row_id。

　　MyiSAM是非聚簇索引。查到数据行所在地址后再访问数据。

什么是回表查询？

　　通过普通索引查到主键后，再用主键回表查询数据。

走普通索引，一定会出现回表查询吗？

　　不一定，可能出现覆盖索引，普通索引存放的数据（Key+PrimaryKey）已经满足查询的要求时，不需要再回表。

　　explain时，Extra显示using index时为覆盖索引。

联合索引（复合索引）的底层实现？最佳左前缀原则？

　　B+树索引，排序时按照从左往右，最左列相同才会比较第二列。

union 和 union all 的区别

　　union和union all都是对结果集进行合并，union会删除重复行并且按默认排序。union all不会。

B+树中一个节点到底多大合适？

　　一页。16K。

为什么一个节点为1页就够了？

　　非叶子结点：Key+指针。

　　叶子结点：数据行。

　　假设一条数据1K，也够存储16条数据。

　　Key为BigInt8个字节，指针6个字节。那么16*1024/(8+6)=1170，可以存放1000+个元素。

　　B+树一般高度为三层，那么可存放数据就是1170*1170*16=21902400（千万级）

什么是 Buffer Pool？

　　InnoDB维护的一个内存区域，内存越大，MySQL越像内存数据库，默认128M。

　　InnoDB会将一些热点数据（Hash索引）和即将访问的数据放入BufferPool。

　　修改数据数，如果数据在BufferPool，InnoDB会直接在BufferPool进行修改，并按一定频率写入磁盘，这时我们称这片区域为脏页。

InnoDB 四大特性知道吗？

　　InsertBuffer：由于磁盘顺序IO和随机IO效率差别巨大（4000-5000倍），所以插入主键索引和插入普通索引的效率差别巨大。因此InnoDB设计了InsertBuffer，当插入普通索引（非聚簇索引）时，先检查数据是否在BufferPool中，在则直接修改BufferPool，否则更新到一片名为InsertBuffer的内存空间，然后按一定频率进行merge合并，再按一定频率写入磁盘，提高效率。要求：索引非聚簇索引，非唯一索引。

　　DoubleWriteBuffer：MySQL一页默认大小为16K，但是操作系统写文件是以4K为单位，一般而言MySQL一页需要分为四块执行。当一页还未完全写完时，发生系统断电或者系统崩溃，就会导致页数据丢失。此时RedoLog也无能为力，因为页本身已经损坏。DoubleWrite就是用来解决这个问题，在脏数据写入数据库前，先复制到DoubleWrite区域，再写入磁盘。

　　AdaptiveHashIndex：哈希是一种非常快的查找方式，增删改查时间复杂度都是O(1)，于是InnoDB利用这种特性把一些热点数据放到BufferPool，建立哈希索引。

　　ReadAhead：由一个概念引申而来——局部空间性：当一个数据被访问时，相邻的数据也可能很快被访问。InnoDB有两种预读方式，一种是线性预读，一种随机预读。线性预读：当访问这个extent（64个Page）里的数据到一定程度（InnoDB_Read_Ahead_Threshold）时，会预读下一个extent区域到BufferPool。随机预读：当访问这个extent的一些数据时，会把整个extent预读到BufferPool中。

说说共享锁和排他锁？

　　共享锁：读锁，多个事务读同一数据可以共享同一把锁。

　　排他锁：写锁，当一个事务修改数据时，这些数据不能被其他事务访问和修改，直到当前事务释放这把锁。

　　select：无锁。

　　update/insert/delete：排他锁。

　　select in share lock：共享锁。

　　select for update：排他锁。

说说数据库的行锁和表锁？

　　行锁：操作时只给当前行（部分几行）上锁，开销大，加锁慢，会出现死锁（待消化），锁的粒度小，发生锁冲突概率小，并发度高。

　　表锁：操作时给整个表上锁，开销小，加锁快，不会出现死锁，锁的粒度大，发生锁冲突概率大，并发度低。

InnoDB 的行锁是怎么实现的？

　　通过索引的索引项实现。所以只有通过索引查找，才会发生行锁，不走索引时会锁住整张表。

　　普通索引：先锁住普通索引，再锁住主键索引。

　　主键索引：直接锁住主键。

InnoDB 锁的算法有哪几种？

　　RecordLock，记录锁，锁住当前记录的索引。

　　GapLock，间隙锁，锁住一定范围内记录的索引（不包含当前记录的索引）。

　　Next-KeyLock，上面两者结合，锁住包含当前记录的一定范围内的记录的索引。

MySQL 如何实现悲观锁和乐观锁？

　　乐观锁：MVCC（CAS）。

　　悲观锁：共享锁，排他锁。

InnoDB 和 MyISAM 的区别？

对比项　　　　　　　　　　InnoDB　　　　　　　　　　　　 MyIsam

事务　　　　　　　　　　　支持　　　 　　　　　　　　　　 不支持

锁类型　　　　　　　　　　行锁、表锁　　　　　　　　　　  表锁

缓存　　　　　　　　　　　缓存索引和数据  　　　　　　　   只缓存索引

主键　　　　　　　　　　　必须有，用于实现聚簇索引　　　  可以没有

索引　　　　    　　　　　　B+树，主键是聚簇索引　　　　　 B+树，非聚簇索引

select count(*) from table　　较慢，扫描全表　　　　　　　　  贼快，用一个变量保存了表的行数，只需读出该变量即可

hash索引　　　　　　　　   支持　　　　　　　　　　　　　  不支持

记录存储顺序　　　　　　    按主键大小有序插入　　                按记录插入顺序保存

外键　　　　　　　　　　　支持　　　　　　　　　　　　　  不支持

全文索引　　　　　　　　   5.7 支持　　　　　　　　　　　　 支持

关注点　　　　　　　　　   事务　　　　　　　　　　　　　　性能

存储引擎的选择？

　　大部分情况InnoDB，绝大部分是只读操作，可以考虑MyIsam

explain 用过吗，有哪些字段分别是啥意思？

　　id，标识符

　　selectType，查询类型

　　table，结果输出集的表

　　partition，匹配的分区

　　type，表的连接类型

　　possible_keys，可能用到索引

　　key，实际用到的索引

　　key_len，用到的索引字段长度

　　ref，列与索引的比较

　　rows，要检查的行数

　　filtered，按表条件过滤过滤的百分比

　　Extra，额外信息

type 中有哪些常见的值？

　　const>eq_ref>ref>range>index>all

　　const：通过索引命中唯一的一条记录

　　eq_ref：通常出现在两表关联查询，使用主键链接，通过非唯一索引的等号查询

　　ref：通过普通索引的等号查询

　　range：通过索引的范围查找

　　index：全索引扫描

　　all：全表扫描

explain 主要关注哪些字段？

　　type、key、rows、Extra，主要看是否使用索引，扫描了多少行，是否出现Using fileSort、Using temporary等影响性能的指标。

如何做慢 SQL 优化？

　　通过explain分析，是否使用索引，是否加载不需要的数据列，还是数据量太大了……

说说 MySQL 的主从复制？

　　主从复制相关三个线程，LogDumpThread（主节点），I/OThread、SQLThread（从节点）

　　1）从I/OThread线程中向主线程请求读取binLogFile的binLogPosition数据

　　2）数据传输到从节点的relayLog后，然后把当前读到的位置记录在mater-info

　　3）SQLThread解析relayLog开始同步数据。

异步复制，主库宕机后，数据可能丢失？

　　半同步复制：上一题步骤1）2）两步用ACK信号同步执行，2）完成后才会返回客户端

　　全同步复制：1）2）3）全部执行完才返回客户端