mysql 面试题总结

以下所有面试题均由个人总结自网络或者书籍，不保证完全正确

• 聚簇索引
  通过B+树组织，使用记录主键值的大小进行记录和页的排序，页内的记录根据主键值大小排成一个单向链表并划分为若干组。每个组主键值最大的记录在页内的偏移量会被当做槽依次放入页目录中。通过页目录可以二分快速定位到某个记录。对于每个页，根据用户记录主键值的大小排成双向链表。存放目录项记录的页分为不同层级，每一层根据目录项记录的主键大小排成双向链表。这样构成的一棵B+树的叶子节点存储的就是完整的用户记录。

• 非聚簇索引
  同样是B+树，但是它以索引列的大小作为页和记录的排序，叶子节点存储的是索引列和对应的主键值。

• 索引优化
  一方面是mysql内部有索引条件下推的功能，用来把查询中与被使用索引有关的搜索条件下推到存储引擎中判断，而不是把记录都读取了然后返回server层再判断。索引条件下推可以减少读取完整的聚簇索引记录的次数，从而减少IO操作。所以它只适用于二级索引，不适用于聚簇索引。
  另一方面，1.在创建索引时，只为用于搜索、排序或分组的列创建索引，减少维护不必要索引的代价。2.在列中不重复记录的个数占比很大的时候才为该列创建索引以减少可能的频繁回表带来的性能损耗。3.索引列的类型尽量小，只为索引列前缀创建索引，以减少索引占用的存储空间（前缀索引因为不包含所有的索引信息，所以不能用来排序）。4.尽量使用覆盖索引进行查询，以避免回表带来的性能损耗。5.让索引列以列名的形式单独出现在搜索条件中，以免出现用不上索引的情况。6.让主键自增，减少让聚簇索引发生页面分裂的情况。7.定位删除表中重复和冗余的索引。

• 基于块的循环嵌套连接(join buffer)
  表连接的时候，驱动表中的每一条符合条件的记录都要对被驱动表执行全表扫描。这样就需要频繁地从磁盘读取这个表，IO代价很大。为了减少被驱动表的访问次数，mysql在内存中申请了一块固定的内存，叫做join buffer。这样在连接的时候先把驱动表里符合条件的若干条记录装在join buffer里面，然后扫描被驱动表，这样被驱动表的一条记录可以一次性与join buffer中的多条记录进行匹配，可以显著减少被驱动表的IO代价。最好的情况是join buffer足够大或者驱动表足够小，这样join buffer可以容纳所有的驱动表记录，只需访问一次被驱动表就可以完成连接操作。所以通常希望以小表驱动大表。

• Index Dive
  在真正执行查询前通过直接访问索引对应的B+树来计算某个扫描区间对应的索引记录条数。用于计算查询代价，生成执行计划。

• Buffer pool
  Buffer pool 里面维护了许多链表，例如free链表，它的每一个节点都代表一个空闲的缓冲页，将磁盘的页加载到buffer pool中时，会从free链表中寻找空闲的缓冲页。为了快速定位某个页是否被加载到了Buffer pool中，使用表空间号+页号作为key，缓冲页控制块的地址作为value来建立哈希表。Buffer pool里面的脏页不是立刻刷新的，而是加入flush链表中，待之后的某个时刻再刷新到磁盘里。
  另外就是mysql中的LRU链表，它分为young区域和old区域，首次从磁盘加载到Buffer pool中的页会放到old区域的头部，为了防止全表扫描是大量不常用的页把young区域的常用数据给挤掉，只有长于规定间隔时间的访问才会将访问的页移动到young区域的头部（因为短于规定时间间隔的很有可能是全表扫描，所以不移动到young区域）。当Buffer pool中没有可用的空闲缓冲页时，会首先淘汰old区域的一些页。

• ACID
  原子性：—个具备原子性的事务，它的操作不可分割，指要么完成操作，要么就不操作，不存在中间状态。
  一致性：指事务执行的结果必须要从一个一致性状态转移到另一个一致性状态。也就是要符合现实世界中的约束
  隔离性：由于多个事务可能同时操纵同一个资源，要保证多个执行中的事务之间不会互相影响，保持相互隔离。
  持久性：指一个事务提交了，数据库的状态就永远发生了改变，不能因为宕机等原因使这个改变失效

• redo日志
  重启恢复的时候使用redo日志更新数据页，保证数据的持久性。事务提交的时候不将所有修改的页面马上刷新到磁盘，而是先将执行过程中产生的redo日志刷新到磁盘。好处是redo日志占用的空间非常小，另外redo日志是顺序写入磁盘的，而直接把脏页写入磁盘可能因为页面不连续而产生大量随机IO。
  redo日志是分组的，因为有些操作会产生多条redo日志，比如向某个索引对应的B+树进行一次悲观插入（数据页剩余空间不足，要进行页分裂，需要对多个页面进行修改）。对于这种需要保证操作原子性的情况，必须以组的形式记录redo日志。
  把对底层页面进行一次原子访问的过程称为一个mini-transaction（mtr），一个mtr可以包含一组redo日志，进行崩溃恢复的时候需要把这一组redo日志作为一个不可分割的整体来处理。redo日志不直接写入磁盘，和buffer pool类似，redo日志有redo log buffer日志缓冲区用来解决磁盘速度过慢的问题。在某个脏页刷新到磁盘前会先将对应的redo日志先刷新到磁盘，事务提交的时候或者log buffer空间不足时也会将redo日志刷新到磁盘。
  当然，根据刷盘策略不同，写入磁盘的时机又有所不同。将innodb_flush_log_at_trx_commit设置为0时表示事务在提交时不立即向磁盘同步，设置为1时表示每次事务提交都要将redo日志同步刷新到磁盘，设置为2时表示将redo日志写到操作系统的缓冲区，并不保证真正刷新到磁盘。另外InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存（page cache），然后调用fsync刷盘。
  log sequence number（lsn）指已经被写入的redo日志量，因为redo日志是顺序的，lsn越小就代表对应的redo日志产生的越早。同时，如果脏页已经被刷新到磁盘了，对应的redo日志也就没用了，它的磁盘空间就可以被后续的redo日志重用。
  判断脏页是否被刷新到磁盘可以通过当前的checkpoint，每执行一次checkpoint就将可以被覆盖的redo日志对应的lsn最大值赋给全局变量checkpoint_lsn，lsn值小于checkpoint_lsn的redo日志就可以被覆盖。重启恢复时从checkpoint_lsn开始向后恢复。
  为了加快恢复的过程，采用哈希表将表空间号和页数作为key，然后把表空间号和页数相同的redo日志都作为value放到同一个槽里用链表连起来，之后遍历哈希表一次性将一个页面恢复好，避免读取很多页面的随机IO。

• undo日志
  假如一个事务执行了一半服务器崩溃了，但是这个事务的redo日志已经刷新到磁盘里，下次重启恢复就会造成一个事务执行了一半的情况，违背了原子性。或者有时候事务执行到一半，手动回滚，为了保证原子性，就引入了undo日志。
  每次进行增删改的操作就要记录对应的undo日志，undo日志里除了记录对应的数据外还需要记录对应的事务id，trx_id。roll_pointer指针指向上一条undo日志。因为向undo页面写入undo日志本身也是一个写页面的过程，所以需要记录相应的redo日志。重启时为了确定哪些事务需要回滚，通过从回滚段的undo slot里找到不为空闲的undo slot，每个undo slot对应一个一个undo页面链表。从undo页面链表中第一个页面的undo log segment header中的属性确定是否有活跃的事务向这个undo页面链表写入undo日志，如果有则找到本undo页面链表最后一个undo log header的位置，通过它得到对应的事务id，这个事务id对应的事务就是未提交事务，通过undo日志记录的信息把它对页面的更改回滚掉。

• 并发一致性问题
  脏写：一个事务修改了另一个未提交数据修改过的数据
  脏读：一个事务读取了另一个未提交事务修改过的数据
  不可重复读：一个事务修改了另一个未提交事务读取的数据。导致这个未提交事务再次读取数据时与初次读取不同
  幻读：一个事务先根据某些搜索条件查询出一些记录，在该事务未提交的时候，另一个事务写入了一些符合那些搜索条件的记录

• 隔离级别：
  读未提交：可能发生脏读，不可重复读和幻读
  读已提交：可能发生不可重复读和幻读
  可重复读：可能发生幻读，不会出现脏读和不可重复读
  可串行化：各种现象都不会发生

• MVCC（多版本并发控制）
  根据聚簇索引的隐藏列trx_id和undo日志的roll_pointer形成版本链，版本链的头结点就是当前记录的最新值，每个版本包含生成该版本对应的事务id。读未提交就读取记录最新版本，可串行化通过加锁保证。
  对于读已提交和可重复读就要保证读取到已经提交事务修改过的记录，所以需要判断版本链中哪个版本是当前事务可见的。所以就有了readview一致性视图。里面记录了当前活跃的事务id列表，如果被访问记录的事务id和当前事务id相同，就代表它在访问自己的记录，所以允许访问。如果被访问版本的事务id小于readview里面的最小事务id，那就代表被访问版本对应的事务已提交，可以访问。如果被访问版本的事务id大于readview里面的最大事务id，说明被访问版本对应的事务是在生成readview以后开启的，不能被当前事务访问。如果被访问版本的事务id在最大最小值之间，那就判断它是否在readview的事务id列表里，如果在，说明生成该版本的事务还是活跃的，不能被访问。如果不在，说明已经提交了，可以访问。如果某个版本不可见的话，就顺着版本链找到前一个版本继续判读。
  读已提交和可重复读的区别在于，读已提交每次读取记录前都生成一个readview，因为每次都生成一个，所以如果期间有事务提交了也会被从readview里面剔除，对应该事务修改过的数据就变成可读的，这样就会出现不可重复读。可重复读只在第一次执行查询语句的时候生成一个readview，之后就不会重复生成了，这样就避免了不可重复读的情况。
  对于二级索引，由于没有trx_id和roll_pointer隐藏列，所以会根据二级索引页面记录的修改该页面最大的事务id来判断，如果这个最大事务id比readview里面最小的事务id还小，就说明可见否则的话就需要回表根据聚簇索引中的trx_id来进一步判断。

• mysql行级锁
  record lock：只对记录本身加锁（有s锁和x锁之分）
  gap lock：作用是防止插入幻影记录，可以锁住记录的间隙，防止别的事务向这个间隙插入新记录
  next-key lock：record lock 和 gap lock 的结合体，普通查询用快照读，对于如update语句是当前读的情况需要next-key锁保证不出现幻读。由于它是对索引加锁，所以一般update或者delete语句where条件中要有索引列或者limit条件。否则就会全表扫描，对整个表都加上了next-key锁
  insert intention lock：为了解决当前事务插入记录是碰到gap锁而进入等待状态而生成的一个锁结构
  隐式锁：比如进行插入操作是先不在内存中生成锁结构，如果插入的时候碰到另外一个事务要读取相关记录，那么为了保证一致性，其他事务想读取记录的时候就要通过trx_id判断所读取的记录是否是当前活跃事务，如果不是，可以正常读取。如果是就帮助该事务创建一个x锁，给自己也创建一个锁并进入等待状态。对于二级索引因为本身没有trx_id隐藏列，需要通过二级索引页面记录的对该页面做改动的最大的事务id，如果这个值小于当前最小的活跃事务id，就可以读取。否则就需要回表，然后重复聚簇索引trx_id判断的操作。隐式锁起到延迟生成锁结构的作用。
  MDL元数据锁：主要是为了防止一个事务在对某个表进行增删改查操作的时候另一个事务修改表结构。MDL锁分为写锁和读锁，修改表结构时会申请MDL写锁，增删改查会申请MDL读锁，写锁优先于读锁。所以要避免一种情况，就是某个长事务对某个表进行了增删改查操作，于是就给这个表加上了MDL读锁。此时另一个事务要修改表结构，它就申请MDL写锁，然后阻塞。之后如果又有其他事务对这个表进行查询操作就会被阻塞。因为MDL写锁的优先级高于MDL读锁，要等MDL写锁获取并释放以后才能继续获取MDL读锁。

• 死锁
  不同事务由于互相持有对方需要的锁结构而导致事务互相阻塞，都无法继续执行的情况。死锁发生的时候innodb会选择事务id较小的事务进行回滚。可以通过查看死锁日志来分析死锁发生的过程。（命令 show engine innodb status）

• 形成死锁的四个必要条件是互斥、占有且等待、不可强占用、循环等待
  在数据库层面要解除死锁可以通过打破循环等待条件。比如设置事务等待锁的超时时间或者开启主动死锁检测。

• 为什么用B+树
  因为像平衡二叉查找树，红黑树这样的自平衡二叉树，插入的数据越多就会导致树变得越来越高，从而使磁盘IO次数增多（因为需要不断往下查找多次，树越高查询的期望次数就越多）导致这个问题是因为它们本质上都是二叉树，而如果是n叉树，数据多的时候树高就显著小于二叉树。
  为了解决树高问题，就有了B树，它的一个节点允许有多个子节点。B树的所有节点都放置了索引加数据，B+树只有叶子节点存放索引加数据，所以在数据量相同的时候B+树的非叶子节点可以存放更多的索引，就可以比B树更加矮胖IO次数更少。
  另外B+树有大量的冗余节点，B树没有冗余节点，这样进行插入删除操作的时候B+树只涉及树的一条路径，不像B树可能需要复杂的树的变形操作。
  B+树的叶子节点都用双向链表连接起来了，有利于范围查询，而B树要实现范围查询就需要进行树的遍历，磁盘IO次数更多。所以涉及索引范围查找更适合用B+树，对于大量单个索引查询的情况可以考虑B树。

• 如何防止sql注入
  1.在代码中让sql预编译
  2.规定数据长度
  3.限制数据库权限
  4.封装sql异常信息，自定义异常响应
  5.过滤参数中含有的数据库关键词