Mysql面试相关知识整理

1.如何根据执行计划调优SQL？

        explain select * from table where id = 1 \G ，有几个关键字段，分别是type、key（使用索引）、extra、rows（扫描行数）。

type 连接类型

        查询所属类型，从好到差的连接类型为system、const、eq_reg、ref、range、index和ALL

• system ：const类型的特例表中只有一行数据时
• const ：表中最多匹配一行数据，通常为主键列或唯一索引列的常数值查询（where id = 1）
• eq_reg ：多表查询中用主键列或唯一索引列做=值关联时，索引列表属于该类型
• ref ：使用普通非唯一索引关联查询时
• range ：使用索引进行范围查询时，常见于<、<=、>、>=、between等操作符
• index ：查询条件中没有索引列，但通过扫描索引获取到结果列时
• ALL ：全表扫描

extra 附加信息

        显示 MySQL 在查询过程中的一些详细信息，常见需要注意的有以下几个

• Using index ：索引覆盖，当查询的字段都包含在使用的索引中 或 where 子句的字段也包含在索引中时，无需回表查找性能较好
• Using filesort ：表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果，性能较差应该优化掉
• Using temporary ：表示MySQL需创建临时表来完成最终的操作如排序，一般为多表查询，性能较差应该优化掉

 （索引的作用：快速查找、避免外部排序 filesort、避免使用临时表）

优化 Using filesort

        如 select * from student where name = 'XiaoLi' ORDER by age 中，MySQL会先从所有数据中挑选出满足 name = ‘XiaoLi’ 条件的，然后再根据 age 排序后返回。可以通过建立name + age的复合索引使查找name 条件和排序同时完成。

优化 Using temporary

         1尽量使用驱动表中的字段来进行排序，2无法调整排序字段时让连接表尽可能小。

驱动表：当连接查询没有where条件时，左连接查询时，前面的表是驱动表；右连接查询时相反，内连接查询时，哪张表的数据较少是驱动表；而有where条件时，带where条件的表是驱动表；

2.为什么索引使用B+树存储？ 

• 首先索引是为了更快得查找到数据，所以通过K-V的结构存储
• 要从一堆K数据中查找指定数据时，我们常用的数据结构是哈希表或二叉树。（哈希表存在哈希冲突无法充分利用空间的问题，并且Key链表的存储并不是有序，当范围查询时需要进行多次IO）
• 而二叉树每个节点最多只有两个子节点，当K数据多时就需要增加深度来存储，越深增加的IO次数越多
• 因此出现了B-树，一种自平衡的树，能够保持数据有序。与二叉树的区别，可以有多个子节点，每个节点可以存储多个值（每个节点由三部分组成：Key，指针，数据data）
• B-树节点包含数据也就意味着查找过程中读取到无需的数据产生IO，把树变种为：非叶子节点只存储索引（冗余）不存储数据data，由叶子节点存储数据data并存有相邻节点的指针来提高范围查询的性能。（这样的树便是B+树）

索引的分类 

• 主键索引：K为主键字段
• 唯一索引：唯一字段
• 普通索引：非主键非唯一
• 全文索引：全文检索（通常不用而是利用ES、Solr、Lucene实现）
• 组合索引：多个字段值共同构成

3.聚簇索引和非聚簇索引

     首先了解几个问题：

1. 一个表中只能有一个索引吗？不是，按照需求可以创建多个但不要太多。
2. 每个索引是一棵B+树还是所有索引共用一棵B+树？一个索引一棵。
3. 有多棵B+树的话那么数据data存储几份？一份。
4. 那么其他索引的叶子节点放的是什么？

         跟数据绑定存储的索引列的值。（在innodb引擎中，数据在进行插入时需要跟某一个索引列绑定在一起，这个索引列如果有主键，那么使用主键；没有主键则使用唯一键，没有唯一键则使用6字节的rowid。（应该是存在隐藏字段））

     所以 数据跟索引绑定存储在一起的叫做聚簇索引，而数据跟索引分开存储的叫做非聚簇索引。

4.回表查询、索引覆盖、最左匹配 

        表有id，name，age字段，id为主键，name为普通索引，name+age有组合索引的情况下。

• 回表查询：select * from table where name = "abc"; 会先根据name条件到name的B+树找到对应叶子节点的id值，然后再拿id值去id的B+树读取行记录，这种查询方式便是回表。
• 索引覆盖：select id, name from table where name = "abc"; 同理查找name的B+树，但叶子节点已经包含了全部要查询的字段，无需回表查询，这样便是索引覆盖（using index）。
• 最左匹配：where name = "abc" | where name = "abc" and age = "8" | where age = "8" and name = "abc";（会自动优化） 最三种方式会走索引，而 where age="8"; 不会走索引，也就是组合索引遵循左边开始匹配原则。

5. mysql的主从复制

        （1）Master节点将操作语句记录到binlog日志中，然后授予slave节点远程连接的权限（binlog记录了所有DDL（数据库定义语言）和DML（数据操作语言）语句。）

        （2）Slave节点开启两个线程：IO线程和SQL线程。其中IO线程负责读取Master的binlog日志内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容并更新到数据库里，这样就保证了Slave节点和Master节点的数据一致了。

        但需要注意的是主节点写binlog和从节点的IO线程写relay log均为顺序读写，而SQL线程为随机读写（因为对同一条记录操作的SQL之间可能存在其他记录的SQL）成本较高且为单线程，所以当主库的并发较高时，产生的数据超过SQL线程所能处理的速度，或者当Slave中产生了锁等待，那么复制延时就产生了。

        如何解决？Mysql 5.7后实现了并行复制（MTS），通过对事务进行分组提交的方式可以在从节点进行并行回放。

6.读写分离与分库分表

        读写分离和分库分表都是为了数据库响应速度更快，能支持更大的并发。

        读写分离是指按规则区分写入使用的数据库和读取使用的数据库，一般为1写多读或1写1读写多读；

        分库分表是指单个数据库中表过多数据量大时 或 单个表中字段过长数据过大时对表和数据库进行拆分，分为水平切分和垂直切分两种方式：

• 垂直分表（按字段）：把表中不常用的、 数据较大、长度较长的字段拆分为一个表，剩余的为一个表；
• 水平分表（按行数）：按照某种规则把一定范围内的行数据拆分为一个表；
• 垂直分库（按业务）：把库中业务关联性或查询关联性较大的表拆分为一个库；
• 水平分库（按数据量）：按照某种规则（如增长系数？）把一定数据量的表拆分为一个库；

（分库分表后需要创建全局唯一的id主键来标识数据如雪花算法，以及水平切分的方式下创建分片键来定位数据所在分片；建议使用ShardingSphere框架来实现读写分离和分库分表，但相关配置具有一定的复杂度。） 

7.事务ACID的实现原理 

• 原子性：要么全部成功要么全部失败，基于undo log实现
• 一致性：在某一特定时间，所有用户访问到的数据相同且准确，通过其他三个特性保证
• 隔离性：事务的执行尽可能不受其他事务影响，基于MVCC和锁实现
• 持久性：事务提交后不会因为宕机等原因导致数据丢失，基于redo log实现

        Undo log用于实现事务的原子性，还用于实现多版本并发控制（MVCC） ，在操作任何数据之前，首先将数据备份到一个地方（Undo log）。然后进行数据的修改，如果出现了错误或RollBack，系统可以利用Undo log中的备份将数据恢复到事务开始前的状态；

（undo log是逻辑日志，进行一个操作时它会记录一条对应相反的sql记录。）

        数据更新的流程与redo log

执行流程：

1. 执行器先从引擎中找到数据，如果不在内存中查询后返回
2. 执行器拿到数据后会先修改数据，然后调用引擎接口重新写入数据
3. 引擎将数据更新到内存，同时写到redo log中，此时处于prepare阶段并通知执行器执行完成，随时可以操作
4. 执行器生成这个操作的binlog
5. 执行器调用引擎的事务提交接口，让引擎把刚刚写完的redo改成commit状态，完成更新

 （innodb是以页为单位来管理存储空间的，任何增删改查操作都会操作完整的一个页，将页加载到内存中，修改完毕不会立即刷新回磁盘，而且仅仅修改了一条记录刷新整个页浪费性能，但不立即刷新的话数据在内存中如果出现意外会丢失。因此引入Redo log记录日志用于数据丢失后的恢复）

        MVCC（多版本并发控制）与快照读

        MVCC指的是维持一个数据的多个版本，使得读写操作没有冲突属于一种乐观锁，快照读是mysql为了实现MVCC的一个非阻塞读功能。每行记录存在三个隐藏字段：

• TRX_ID（最近修改事务id）：记录创建当前记录或者最后一个修改的事务id
• ROLL_PTR（回滚指针）：指向这条记录的上一个版本
• ROW_ID（隐藏主键）

        Mysql的读操作分为快照读（如 select x from table）与当前读，当前读也叫加锁读，每次读取都是读取数据的最新版本，并对其进行加锁。（如 select ... lock in share mode / for update 和增删改）

快照：InnoDB在事务开启后执行第一个查询时，会创建一个快照(ReadView)，快照有以下信息：

• m_ids（活动事务id列表）：指已开始未提交 / 回滚的事务
• min_trx_id（最小活动事务id）
• max_trx_id
• creator_trx_id（当前事务id）

  然后通过查询语句定位到最新版本的数据行，并根据以下规则获取到可以访问的：

  1.TRX_ID = creator_trx_id （为当前事务所改）

  2.TRX_ID < min_trx_id（为快照生成前已提交）

  3.TRX_ID <= max_trx_id & in m_ids （为快照生成后改，读取历史版本）

  4.TRX_ID <= max_trx_id & ! in m_ids（为快照生成后已提交）                | 已提交 | RV | 未提交 |

（rc 级别下每次select 生成一个快照，rr 级别下只生成一个快照。）

 8.悲观锁

        Mysql中有三种锁：行锁、页锁、表锁，InnoDB使用的是行锁和表锁， InnoDB的行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据时才会使用行锁，否则将使用表锁。

        行锁的三种实现方式：

• RecordLock（记录锁）：锁定单行记录
• GapLock（间隙锁）：锁定索引记录之间的间隙
• Next-key Lock（区间锁）：前两者的组合，锁定记录和前后的间隙

        RR隔离级别中，InnoDB加锁都是先采用Next-key Lock，但是当SQL操作含有唯一索引时会优化降级为记录锁。例如 update name = 'liu' where pat_id = 10，如何保证这次操作的隔离性呢（不会修改到其他事务的数据），1锁住记录本身，2同时锁住记录之间的间隙以免其他事务插入同样pat_id = 10的记录。修改完后其他事务才能插入数据双方结果达到预期，如果pat_id为唯一索引时则本来就无法再插入pat_id = 10的记录，所以优化为RecordLock。

9.事务的并发问题与隔离级别

        当两个事务同时进行时会出现3种并发问题：

• 脏读：事务A读取了事务B更新的数据，然后事务B回滚了，那么事务A读取到的是脏数据。
• 不可重复读（侧重读）：事务A多次读取同一数据，事务B在其过程中更新了并提交，导致事务A多次读取的数据不一致。
• 幻读（侧重改）：事务A将多条值为1的记录改为2，期间事务B插入了一条值为1的记录，随后事务A查看改变结果发现还有1的数据。

        因此定义了4种隔离级别通过不同的算法来解决：                

                                                脏读        不可重复读        幻读

• 读未提交：                         Y                  Y                   Y
• 读已提交（rc）：               N                  Y                   Y
• 可重复读（rr 默认）：       N                  N                   Y
• 串行化：                             N                  N                   N