MySQL数据库全面剖析

存储引擎

InnoDB
InnoDB 是 MySQL 默认的事务型存储引擎，InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读。

MyISAM
设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。不支持事务，不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。

InnoDB 和 MyISAM 的比较
事务： InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句，MyISAM不具备事务。
并发： MyISAM 只支持表级锁，而 InnoDB 支持行级锁和表级锁。
外键： InnoDB 支持外键，MyISAM不支持外键。
备份： InnoDB 支持在线热备份。
崩溃恢复： MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

索引

在面试中常问道一个问题，问什么索引的数据结构采用的是B+树？
Hash表：
Hash表的查询速率在数据少量的时候可以说是最快的，时间复杂度为O（1）。但hash表不能进行范围查询，还需要维护hash算法，如果算法维护不当，那么就会延伸成一条链表时间复杂度升级为O（n）。
二叉树
时间复杂度O（log2n），如果索引是连续递增或递减的话，会退化成一条链表，时间复杂度会升级为O（n）。
平衡二叉树
平衡二叉树，一般是用平衡因子差值决定并通过旋转来实现，左右子树树高差不超过1，那么和红黑树比较它是严格的平衡二叉树，平衡条件非常严格（树高差只有1），只要插入或删除不满足上面的条件就要通过旋转来保持平衡。由于旋转是非常耗费时间的。所以 AVL 树适用于插入/删除次数比较少，但查找多的场景。并且数据多的时候数的高度会非常高，很不利于树的遍历。
B树
系统从磁盘读取数据到内存时是以磁盘块为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，每个磁盘块中存放：键值、指针、数据。因为B+树的磁盘块中只存放指针和键值，因此每个磁盘块能容纳的键值更多，对于磁盘的IO要低于B树。
B+树
所有data信息都移动叶子节点中，而且子节点和子节点还有指针指向形成一条链表，大大提升范围查询效率，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，不再需要中序遍历。

B+树和其他数据结构的差距

B + 树与红黑树的比较
红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：
（一）磁盘 IO 次数
B+ 树一个节点可以存储多个元素，相对于红黑树的树高更低，磁盘 IO 次数更少。
（二）磁盘预读特性
为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道。每次会读取页的整数倍。

B + 树与 B 树的比较
（一）B+ 树的磁盘 IO 更低
B+ 树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
（二）B+ 树元素遍历效率高
B 树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）。

B+树索引

是大多数 MySQL 存储引擎的默认索引类型。
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

注：MyISAM中是非聚簇索引，原因MyISAM的数据文件有三个 .frm（表结构）。MYI（存储索引）、MYD（存储数据），因此MYISAM中只有非聚簇索引

哪些情况需要建索引？

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引
5. where条件里用不到的字段不创建索引

使用索引的注意事项

重点1：遵循最左匹配原则
数据库表中有主键字段id，姓名字段name，年龄字段age，身高字段length，现建立复合索引 NA（name，age）
当我们查询数据时：select * from 表A where name = 张三 and age = 20；此时是走我们的NA的。
当我们查询数据时：select * from 表A where age = 30；此时是不走索引的，因为违背了最做匹配原则。
但是，在数据库5.6后对数据库做了优化，在这种情况下，即使违背最左匹配原则，也是可以走索引的
建立复合索引NAL（name，age，length）
当我们查询数据时：select * from 表A where age = 20 and length = 170；这种情况下虽然违背了最左匹配原则，但是仍然会走索引，因为索引字段的是数据库表除主键外的全部字段。另外一种说法是数据库优化器会做出判断是不走索引的代价大还是走索引后两个字段的代价大，因此选择继续走索引。

重点2：不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM staff where name = ‘Alice’ AND age = 22 AND pos = ‘HR’;

EXPLAIN SELECT * FROM staff where name = ‘Alice’ AND age > 21 AND pos = ‘HR’;

重点3：尽量使用覆盖索引
覆盖索引的意思就是你查询的这个字段尽量在索引字段中，尽量避免使用select * 这种查询方式

重点4：like以通配符开头，索引失效


重点5：字符串类型不加 ’ 单引号 '，索引失效

数据库的三大范式

第一范式(确保每列保持原子性）
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

数据库事务的四大特性

原子性
事务被视为不可分割的最小单元，事务的所有操作要么全部成功，要么全部失败回滚。
一致性
数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。
隔离性
一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性
一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。

隔离级别

**读未提交：**事务中的修改，即使没有提交，对其他事务也是可见的。
**读已提交：**一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。
**可重复读：**保证在同一个事务中多次读取同样数据的结果是一样的。
**可串行化：**强制事务串行执行。

并发事务带来哪些问题?

脏读：脏读指的是不同事务下，当前事务可以读取到另外事务未提交的数据。
例如：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据
不可重复读：不可重复读指的是同一事务内多次读取同一数据集合，读取到的数据是不一样的情况。
例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻读：在同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。幻影读是一种特殊的不可重复读问题。
丢失更新：一个事务的更新操作会被另一个事务的更新操作所覆盖。
例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

MVCC

多版本并发控制是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现读已提交和可重复读这两种隔离级别。
MVCC主要为了提高读写的并发性能，不用加锁就能让多个事务并发读写

当事务A更改这一条记录的过程是什么？

1. 排它锁锁定这一行数据
2. 将需要修改的数据复制一份到undolog中
3. 将回滚指针指向undolog中旧数据的row_id，并修改当前需要修改的数据
   

MVCC原理讲解

当执行查询sql语句时，会生成一致性试图read-view，（注意：数据库默认的隔离级别可重复读当执行sql语句后生成read-view，之后的查询语句一直使用这个read-view），read-view它由执行查询时所有未提交的事务id数组和已创建事务的最大id组成，查询的数据结果需要通过read-view做比对从而得到快照结果。

版本链对比：

版本链对比规则：

1. 如果落在黄色部分（min_id <= trx_id <= max_id），那就包括两种情况：
   a. 若row的trx_id在数组中，表示这个版本是由还未提交的事务生成的，当前事务仅自己可见
   b. 若row的trx_id不在数组中，表示这个版本是已经提交的事务生成的，是所有事务可见的。
2. 如果落在绿色部分，表示这个版本是已经提交事务生成，对所有事务可见的

主从复制

MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中；
主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。
binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：
1.主从服务器负责各自的读和写，极大程度缓解了锁的争用；
2.从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
3.增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

分库分表数据切分

水平切分
水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。
当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分
垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

MySQL 基础架构分析

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。

什么是redo log？

假设我们有一条sql语句：

MySQL执行这条SQL语句，肯定是先把id=3的这条记录查出来，然后将name字段给改掉。这没问题吧？
实际上Mysql的基本存储结构是页(记录都存在页里边)，所以MySQL是先把这条记录所在的页找到，然后把该页加载到内存中，将对应记录进行修改。
现在就可能存在一个问题：如果在内存中把数据改了，还没来得及落磁盘，而此时的数据库挂了怎么办？显然这次更改就丢了。

如果每个请求都需要将数据立马落磁盘之后，那速度会很慢，MySQL可能也顶不住。所以MySQL是怎么做的呢？
MySQL引入了redo log，内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么修改。

所以，redo log的存在为了：当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以根据redo log来对数据进行恢复。因为redo log是顺序IO，所以写入的速度很快，并且redo log记载的是物理变化（xxxx页做了xxx修改），文件的体积很小，恢复速度很快。

什么是bin log？

binlog我们可以简单理解为：存储着每条变更的SQL语句。
binlog记录了数据库表结构和表数据变更，比如update/delete/insert/truncate/create。它不会记录select（因为这没有对表没有进行变更）。

bin log主要有两个作用：复制和恢复数据
MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致，这就是通过binlog来实现的
数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。

bin log和redo log区别

binlog记载的是update/delete/insert这样的SQL语句，而redo log记载的是物理修改的内容（xxxx页修改了xxx）。

redo log的作用是为持久化而生的。写完内存，如果数据库挂了，那我们可以通过redo log来恢复内存还没来得及刷到磁盘的数据，将redo log加载到内存里边，那内存就能恢复到挂掉之前的数据了。

binlog的作用是复制和恢复而生的。
主从服务器需要保持数据的一致性，通过binlog来同步数据。如果整个数据库的数据都被删除了，binlog存储着所有的数据变更情况，那么可以通过binlog来对数据进行恢复。

数据库查询数据很慢问题

数据库在刷新脏页（flush）

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。
什么是脏页？
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
刷脏页有下面2种场景：
redolog写满了： redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
内存不够用了： 如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。

拿不到锁我能怎么办

这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。

数据库慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
慢查询分析工具
通过Mysqldumpslow慢查询分析工具找出慢查询日志当中的sql语句

1. 先通过观察，避免有子查询的出现，尽量用关联查询代替子查询
2. 反范式化设计，允许表中有部分字段冗余
3. Explain + 慢SQL分析（索引的优化）
4. Show profile分析