面试-MySQL

1、事务的四大特性，ACID四个特性是怎么实现的？

        ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

        一致性指的是事务执行前后的状态是一致的。原子性、隔离性和持久性都是为了保证一致性。

        原子性的实现原理：

                原子性表示事务的全部操作，要么全部成功，要么全部失败。原子性实现的关键在于undo log。

                隔离性：MVCC机制

                持久性：事务一旦提交成功，那么对数据库的修改是永久的。这是用redo log来保证的这一特性。

2、数据库的三大范式

        1NF：保证字段的原子性。

        2NF：1NF的基础上保证不存在部分依赖主键的情况。比如主键是A、B两个字段的组合，非主键不能仅仅依赖于A或者B。

        3NF：2NF的基础上保证不存在传递依赖的情况。比如不能存在非主键C依赖于非主键B，非主键B依赖于主键A的情况。

3、事务的隔离级别

        事务的隔离级别主要是为了解决以下几个问题：脏读、幻读、不可重复读的问题。

        脏读：一个事务处理数据的过程中读取到了其他事务未提交的数据

        不可重复读：一个事务多次查询数据的时候返回的值不一样的情况，这是因为在查询过程中有其他事务对数据进行了更改。

        幻读：一个事务在读取某个范围内的记录的时候，其他事务插入了新的记录。比如一个事务要在插入数据前用select查询发现没有这个id，然后执行插入操作却发现冲突了，记录已经存在了。

        为解决这些问题，就设置了隔离级别：读未提交、读已提交、可重复读和串行化

        其中读未提交的隔离级别上面三种问题都会出现；串行化能解决这三种问题但是效率很低。可重复读能防止脏读和不可重复读，但会出现幻读的情况；读已提交能解决脏读的情况，但会出现幻读和不可重复读的问题。

4、索引

        1.什么是索引

                索引就是数据库用来提高数据库表访问的一种数据结构。

        2.索引的优缺点

                优点：加快数据的查找速度，加快排序和分组的速度，加快表与表之间的连接

                缺点：索引需要占用一定的内存空间，当进行增删的时候需要动态维护索引表，会影响效率。

        3.索引的作用

                如果不使用索引就需要进行全盘扫描，效率很慢。使用索引可以加快速度。

        4.什么情况下需要建立索引

                对于经常需要查询的字段，对于经常需要连接的字段，对于经常需要排序的字段

        5.什么情况下不需要建立索引

                where条件用不到的字段，数据量很少的时候，需要进行列值计算的字段，区分度不高的字段（比如性别），经常增删改的的字段

        6.索引的数据结构

                6.1 哈希索引

                哈希索引基于哈希表实现，对索引列计算哈希值映射到哈希槽中，然后将哈希值作为key，将数据行地址作为value。哈希索引多用于精确查找，这样查找的时间复杂度是O(1)

                6.2 B树

                B树是多路平衡查找树，B树将索引和数据存在树的节点中，当数据很多时树的高度就很高，查询效率也会降下来，而且B树查找不一定要查到叶子结点，所以效率不太稳定。

                6.3 B+树索引

                B+树将数据存在叶子节点里，然后非叶子结点存储索引，这样一个节点里面就能存储很多个索引，就可以把树的高度降下来。一般用B+树2~4层就可以。相比于B树，它的查找效率比较稳定。而且B+树的叶子结点使用双向链表进行了连接，这样就适合于排序和范围查找。

                6.4 哈希索引和B+树索引的区别：

                哈希索引适用于精确查找，不支持排序，不支持模糊查询范围查询，并且存在哈希冲突，因此效率不稳定。B+树索引每次查询都是查到叶子结点，因此效率稳定。

        7.索引的分类

                组合索引、全文索引、主键索引、唯一索引（可以为null）

        8.最左匹配原则

                对于组合索引，如果查询语句用到了组合索引最左边的字段，那么这些字段就会使用索引查询。但是，碰到范围查询的时候，就会停止使用索引查询。比如对（a，b，c）这个组合索引，假设有查询条件是wher a = ？ and b > ? and c = ？，这个时候ab就会走索引，但是c不会。此外，假设查询条件是where b = ? and c = ? ，这种情况也不会使用索引。

        9.聚集索引

                一般情况下，聚集索引就是主键索引，但是表中如果不存在主键，聚簇索引会使用第一个唯一索引，如果唯一索引也没有的话，会生成一个隐藏的聚簇索引。

        10.覆盖索引

                对于要查询的数据在索引中就能取得到，这样就不用进行二次回表查询了。回表查询就是使用非聚簇索引查询到主键的值，然后再根据主键的值到聚簇索引中查询数据记录。

        12.索引什么时候会失效

                当使用like查询是%号开头的时候

                不符合最左匹配原则的字段

                对于列值进行计算的字段

                查询条件使用or

        13.前缀索引

                索引太长可能会影响效率，因此可以使用字段前几个字符来建立索引，前缀索引的关键在于选择的长度要保证较高的索引选择性。

        14.什么是索引下推

        索引下推是为了减少回表次数，并将Server层的事情下推到引擎层。索引下推适用于非聚簇索引。对于组合索引，假设查询a>？andb=？，如果不使用索引下推，会先定位到符合a>?的主键id，然后回表查询记录，之后在server层比较b的条件是否成立。如果使用索引下推，则会在定位到符合a>?的时候，直接比较b是否成立，如果不成立就查找符合a条件的下一个记录。

5、常见的索引引擎

        InnoDB：使用B+树索引，叶子结点存储的是数据

        MyISAM：使用B+树索引：但是叶子节点放的是数据的地址，因此它会将表存储在两个文件中，一个是索引文件，一个是数据文件。

        MyISAM只有表级锁，没有行级锁，InnoDB默认为行级锁

        MyISAM不支持事务，InnoDB支持事务，具有回滚和崩溃修复的能力。

        MyISAM不支持外键，InnoDB支持

        MyISAM不支持聚簇索引，InnoDB是聚簇索引。

6、MVCC

MVCC即多版本并发控制，它是基于undolog版本链和readview实现的。版本链的实现主要基于三个隐藏字段，一个是当前事务id，一个是回滚指针，用于指向上一条记录，一个是主键id，如果表里面没有主键则会自动生成一个6字节大小的隐藏主键。当某个事务对数据进行更改的时候，就会将回滚指针指向修改前的记录，然后记录这个事务的id到修改后的数据中，这样就形成了一个版本链。然后是readview，readview是读视图。对于读已提交的隔离级别，每一次快照读的时候都会产生一个readview；对于可重复读的隔离级别，则会复用之前的readview。可重复读的隔离级别下依旧会出现幻读的情况，因为在一个事务两次快照读之间如果出现了当前读，那么就会生成新的readview，而不是复用之前的，这样读出的数据记录就不一样了。对于readview，它会记录当前活跃事务id的集合，活跃列表中最小的事务id和预分配事务id也就是最大事务id+1。在进行快照读的时候，会从版本链中进行判断。如果版本链中第一条记录的事务id和进行快照读这个事务的id一致，那么说明这条记录可以读取。如果当前事务id小于最小事务id，就说明这个记录已经提交，可以读取。如果当前事务id大于预分配事务id，说明这个事务还没提交，不能访问。如果当前事务id在活跃列表中，说明这个事务还没提交，就不能访问。之后就会用版本链中下一条记录进行判断。

9、快照读和当前读

        快照读就是普通的select语句，主要是依靠MVCC实现。当前读读的是最新的数据，使用行锁和间隙锁实现。update、insert、select...for update这些都是当前读。

10、一条select语句执行流程

        MySQL分为Server层和存储引擎，Server层包含连接器、查询缓存、解析器、预处理器、优化器和执行器这些。当一条查询语句执行的时候，它会首先通过连接器去连接，验证用户的身份；然后如果查询语句命中缓存，就直接返回结果。在查询缓存里面是以键值对的形式把语句作为key，查询结果作为value进行缓存的，但是这个功能比较鸡肋，因为数据库更新比较频繁的换缓存命中率会很低；如果查询缓存未命中的话，就会到解析器中进行语法和词法的解析；解析完成后在预处理器中进行预处理，预处理器主要就是比如检查字段是否存在，将select *扩展为所有的列这些操作；然后在优化器中会选择合适的执行计划，比如用什么索引执行这些；最后通过执行器调用执行引擎的接口返回结果。

11、B+树的查询过程

        在B+树中，每个节点都是一个数据页，在非叶子节点上存储的是索引，数据都存在叶子结点上。在查询的时候，是通过二分查找定位的，当定位到数据页后，再遍历这个数据页中的记录，返回查找结果。此外，叶子结点由双向链表连接，这样方便范围查询。

12、MySQL锁

1.全局锁

// 加锁语句
// 执行后整个数据库处于只读状态，主要用于全库逻辑备份，会造成业务停滞。
flush tables with read lock

// 解锁
unlock tables

2.表级锁

        2.1 表锁

// 给student表加锁

// 读锁
lock tables student read
// 写锁
lock tables student write
// 解锁（释放当前会话的所有表锁）
unlock tables

        2.2 元数据锁

        元数据锁不需要显示调用，当我们给数据库表进行操作时，会自动给这个表加上元数据锁。

        2.3 意向锁

        在对记录加锁之前会对表进行加意向锁，意向锁有意向共享锁和意向独占锁。意向锁主要是为了判断表里面是否有记录加锁了，如果给记录加锁前不加意向锁，那么判断的时候就需要遍历记录去判断。

        2.4 自增锁（AUTO-INC）

3.行级锁

        3.1 记录锁

        锁住一条记录。

        3.2 间隙锁

        锁住一个范围的记录，比如给开区间(3,5)的记录加锁，那么当执行插入4这条记录的时候就会被阻塞，可以防止幻读的问题。

        3.3 next-key lock（记录锁+间隙锁）

               又称临键锁，也是锁住一个范围的记录，比如给(3,5]加锁，其中3为开区间，5为闭区间，那么就不能插入4这条记录，也不能修改记录5。

        3.4 插入意向锁

        当进行插入操作的时候，会判断插入位置是否已经加锁。如果加了就会生成一个插入意向锁。

13、日志

        1.undolog

        undolog是InnoDB引擎的日志，用来保证原子性。undolog版本链用于回滚。假设事务还没提交就崩溃了，这时候需要进行回滚就要用到undolog。

        2.redolog

        redolog是InnoDB引擎的日志，用于保证持久性。

        3.binlog

        binlog是数据库层面的日志，用于数据库备份和主从复制。

        4.redolog和binlog的区别

        redolog是InnoDB引擎的日志，binlog是数据库server层的日志。

        redolog记录的是对数据页的修改，binlog记录的是所有对数据库修改的操作。

        redolog是循环写，写满了会覆盖；binlog是追加写，写满了会创建新文件继续写，保存的是全量的日志。

        redolog用于掉电等恢复故障；binlog用于数据库备份和主从复制。