MySQL由入门到熟悉，MySQL常见面试问题

MySQL组成部分

1.Server

        连接器: 管理连接, 权限验证。
        分析器: 词法分析, 语法分析。
        优化器: 执行计划生成, 索引的选择。

2.存储引擎

        存储数据, 提供读写接口。

MySQL存储引擎

innoDB

        适用于更新操作频繁，或者要保证数据的完整性，并发量高，支持事务和外键的场景。比如OA自动化办公系统。
        如果没有特别的需求，使用默认的Innodb即可。

MyISAM

        适用于管理非事务表，它提供高速存储和检索， 以及全文搜索能力的场景。比如博客系统、新闻门户网站。

MySQL索引

什么是索引

        索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。

索引优缺点

        优点：可以大大加快数据的检索速度，这也是创建索引的最主要的原因。通过使用索引，可以在查询的过程中，使用优化器，提高系统的性能。

        缺点：创建索引和维护索引要浪费时间。对表进行增删改的时候需要动态维护索引

MySQL为什么选择b+树而不是b树，二叉树？

        1.二叉树一个节点只能存储一条数据，数据多时，层级太深，极端情况会变成线性结构，查询速度会变慢。
        2.b树是一个可以自平衡的树，极端情况下不会变成线性结构，b树数据和索引是存在一起的，一次读入到内存的数据不多。
        3.b+树，数据与索引分开，只在主键索引叶子节点冗存储数据，索引可直接放在内存中查询，查询速度快。

索引类型

        按数据结构分为B+树索引，hash索引

        按物理结构分为聚簇索引，辅助索引

        按字段特性分为主键索引，唯一索引，普通索引，前缀索引

        按字段个数分为单列索引，联合索引

创建索引原则

        1、经常查询条件的字段，where子句中的列，连接子句中的指定列需要排序，分组操作的列
        2、更新频繁的字段不适合创建索引，但实际项目中，如果有查询需求还是会建立
        3、对text，image，bit的数据类型的列不要建立索引
        4、尽量使用组合索引与最左前缀匹配原则
        5、索引字段尽量避免非空字段
        6、不要过度使用索引，数据量少没必要添加索引

什么情况使用了索引，查询还是慢

        索引未触发
        索引过滤性不好
        频繁的回表查询

索引未触发情况

        1、类型匹配不一致
        2、左模糊查询
        3、mysql通过分析器分析sql后，发现走索引还不如全部扫描快，会未触发索引。
        4、where的or有一处字段没有添加上索引
        5、被索引的字段使用了表达式或者函数
        6、联合索引不符合最左前缀匹配，最左前缀匹配前面字段使用范围后面字段则无法触发索引
        7、排序后深度分页

MySQL使用增长主键id的好处

        1、自增主键按顺序存放，增加数据速度快
        2、数字型，占用空间小，易排序
        3、不用担心主键重复问题

索引优化的方法

        1、前缀索引：当一个字段为字符串太长，且尾部字符串相同（如邮箱），可只截取前几个不相同字符做索引
        2、覆盖索引
        3、主键索引最好是自增的
        4、防止索引失效
        5、索引列最好设置为非空

MySQL事务

事务的acid

        原子性：事务是最小的执行单位，事务包含的所有操作要么全部成功，要么全部失败进行回滚。
        一致性：事务执行前和执行后数据都必须处于一致性
        隔离性：一个事务不受其他事务影响。
        持久性：mysql事务提交后，数据保存到磁盘不会丢失

事务的隔离级别

        读未提交，读已提交，可重复读，串行化。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

脏读，不可重复读，幻读问题，MySQL在使用innoDB下是如何解决这些问题的

读未提交(Read Uncommitted)：

        允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。

        Read Uncommitted这种级别，数据库一般都不会用，而且任何操作都不会加锁。

读已提交(Read Committed)：

        只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

        实现：在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。也就是在事务中执行insert update 和delete时会加锁，但是执行select语句的时候是不会加锁的那对于上锁， 在RC级别中使用的锁机制是Record Lock：单个行记录的上锁，mysql的innoDB引擎的行锁机制锁的是索引，不是行记录，所以如果没有使用索引，那么会使用表锁，直接锁住整张表。
可重复读(Repeated Read)：

        可重复读也是MySQL默认隔离级别，innoDB在此级别解决了幻读问题。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。

        实现：mysql通过MVCC多版本并发控制机制解决了不可重复读问题。

        MySQL通过undo日志版本链与read view机制实现可重复读

undo日志版本链与read view机制
        undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。如下图

在 可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的 一致性视图read-view，该视图在事务结束之前都不会变化( 如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

版本链比对规则：

        1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

        2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若row 的 trx_id 就是当前自己的事务是可见的）；

        3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

              a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若         row 的 trx_id 就是当前自 己的事务是可见的)；

              b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的可见。

         对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

总结：

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

串行读(Serializable)：

完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

MySQL在使用innoDB下如何解决的幻读问题

innoDB里面引入了间隙锁，和next-key lock机制，解决了幻读问题。

分为以下2种情况：

        1、固定区间范围：4<id<7开区间，直接添加间隙锁。

        2、id>10，这种超大范围非区间时，引入next key-lock机制，它相当于间隙锁和记录锁的合集，记录锁锁定存在记录的行，间隙锁锁住的是记录行之间的间隙。next-key是锁住两者的和。

如下图

        每个数据行上的非唯一索引列都会存在一把next-key lock，当某一行数据持有next-key lock的时候，会锁住一段在左开右闭区间的数据。当通过范围查找的时候，innoDB会去加一个next-key lock锁。间隙锁和next-key lock的区别是在于加锁的范围，间隙锁锁定的是两个索引之间的间隙。而next  key lock会锁定多个索引区间，它包含记录锁和间隙锁。
        当我们使用范围查询，不仅仅命中记录，还包含了间隙的时候，在这种情况下，使用的就是临键锁（next-key log），它是mysql里面默认的行锁算法。虽然mysql使用间隙锁+临键锁解决了幻读的问题，但是一定会影响到并发的性能。因此，对于性能较高的一些业务场景，我们可以把性能设置为读已提交。

MySQL的当前读，快照读

        当前读：当前读，顾名思义就是读取当前最新的数据，并且对读取的数据加锁，阻止其他事务同时修改相同的记录，避免出现安全问题。
下面这些场景会使用当前读:

• update、delete、insert
• select … lock in share mode (主动加共享锁)
• select … for update （主动加排他锁）

        快照读：快照读取意味着InnoDB使用多版本控制在某个时间点向查询提供数据库的快照。

• InnoDB在隔离级别读已提交级别和可重复读级上别使用
• 一致读不会在访问的表上加任何锁，其他会话可以同时修改表上的数据。
• 读未提交级别下，不需要快照，因为怎么读都是最新的，不管是否有没有提交过
• 读已提交级别下，每次select都会生成一个快照。
• 可重复读级别下，开启事务之后第一个select才会生成快照，而不是事务一开始就生成快照。

怎么知道执行的语句是当前读还是快照读

        1.在默认隔离级别下，select 语句默认是快照读

        2.select 语句加锁是当前读

                # 共享锁
                select a from t where id = 1 lock in share mode;

                #排他锁
                select a from t where id = 1 for update;
        3.update 语句是当前读

        update t set a = a + 1;

行锁，间隙锁，表锁，临键锁

        行锁：当一个事务更新一条数据时，其他事务再更新这条数据会阻塞，直到加锁的事务提交

        间隙锁：间隙锁会在可重复读下生效，当我们通过sql范围更新一条数据时，如id<10条件去更新，则后面的事务无法在id<10的范围内进行插入数据

        表锁：当我们的更新语句没有触发到索引时，会由行锁升级为表锁，整张表将会锁住。

        临键锁（next-key log）：不仅会锁上间隙，还会锁定多个索引区间，包含记录锁和间隙锁

共享锁，排它锁，乐观锁，悲观锁

        共享锁：又称为读锁，获得到共享锁后，可以查看，但是无法删除和修改数据，其他事务此时获取到锁可以查看，但是无法修改或删除。

        排它锁：又称写锁，通过for update添加，若事务T1对数据对象a添加上排它锁，则只有事务T1可以对对象a精选修改，其他事务修改会阻塞。其他事务不能再对a添加任何锁，直到T1事务释放锁。这保证了其他事务在T1释放了A上的锁之前不能再读取和修改A。

        乐观锁：假定会发生并发冲突，每次去查询数据的时候都认为别人会修改，每次查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

        悲观锁：假设不会发生并发冲突，每次去查询数据的时候都认为别人不会修改，所以不会上锁，在修改数据的时候才把事务锁起来。实现方式：乐观锁一般会使用版本号机制或CAS算法实现

乐观锁，悲观锁的使用场景

        乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

        但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

如何避免MySQL的死锁

        1、防止更新语句没触发索引变成表锁。

        2、在不同的事务中，以相同的顺序获得锁。

sql优化

如何定位慢sql

        1、使用explain关键字
        2、项目中使用com.alibaba.druid数据库连接池，该连接池支持mysql日志记录

sql执行顺序

        书写顺序，SELECT ...FROM 表名WHERE ...GROUP BY ...HAVING ...ORDER BY ...LIMIT;

        执行顺序，FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

sql常见优化方法

        1、尽量避免使用select *
        2、尽量小表驱动大表
        3、批量操作，不要循环查询或循环插入
        4、多使用limit
        5、in里面值太多
        6、高效的分页
        7、尽量少联表查询
        8、合理设计字段与索引
        9、使用索引覆盖
        10、排序尽量使索引排序，而不是走文件排序

优化MySQL的方法

        1、SQL 语句及索引的优化
        2、数据库表结构的优化
        3、系统配置的优化
        4、硬件的优化

MySQL深度分页优化方法

        使用书签标记（不支持大跳页）

        子查询优化（有点局限性）

explain关键字执行计划返回的核心参数，重点参数返回字段的意义

字段设计原则

MySQL执行引擎

分库分表

MySQL常见面试题

MySQL读已提交，可重复度，幻读实现区别

        读已提交下的事务在每次查询的开始都会生成一个独立的read view。

        可重复读时在第一次读的时候生成了一个read view，之后的读都复用之前的read view。

        幻读在可重复度的基础上添加了next key lock，通过间隙锁+记录锁锁住范围操作，从而不会出现幻读的问题。

MySQL缓存弊端，何时开启关闭?

        频繁更新数据会导致缓存生效
        读多写少才考虑使用缓存
        8.0版本删除了缓存

MySQL如何恢复半月前的数据？

        通过整库备份+binlog进行恢复. 前提是要有定期整库备份且保存了binlog日志

做过哪些MySQL索引相关的优化？

        尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗.
        MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗.
        若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表.
        联合索引将高频字段放在最左边

数据量大导致查询慢，如何设计？

        根据业务来进行分表
        使用分库分表插件
        使用es搜索引擎

MySQL左模糊查询一定触发不了索引吗？

        是的，因为不符合

为什么左模糊查询触发不了索引？

为什么组合索引不遵循最左前缀就触发不了索引？

高度为3的B+树能存多少条数据？

innoDB是如何支持范围查询能走索引的？

范围查找导致索引失效的原因？

innoDB是如何实现事务的？

        1、innoDB在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在buffer pool中

        2、执行update语句，修改buffer pool中的数据，也就是内存中的数据

        3、针对update语句生成一个redo log对象，并存放在log buffer中

        4、针对update语句生成undo log日志，用户事务回滚

        5、如果事务提交，那么则把redo log对象进行持久化，后续还有其他机制将buffer pool中所修改的数据页持久化到磁盘中

        6、如果事务回滚，则利用undo log日志进行回滚

order by为什么会导致索引失效？

MySQL种的数据类型转换有哪些需要注意？

MySQL中varchar最多能存储多少数据？

什么是三星索引？

如何提高insert的性能？

        1、合并多条insert为一条，因为可以减少日志刷盘的数据量和频率，减少sql语句解析次数。

        2、修改参数bulk_insert_buffer_size，调大批量插入的缓存。

        3、将innodb_flush_log_at_trx_commit设置为0

                0：每一秒写入一次log file中。

                1：在每次事务提交时，

小破站高质量面试题：MySQL 夺命连环50问（高频面试题及解析）_哔哩哔哩_bilibili