MySQL总结

MySQL逻辑架构图

SQL语句的执行过程
1、经过连接器，建立TCP连接
2、查看缓存中是否有对应语句的key，如果有，就返回缓存中的数据
3、缓存中没有，会到分析器，这一步主要是分析SQL语句的语法是否正确，类似于java的编译，对语句校验
4、语句语法都没问题之后，会到优化器，这一步主要是对语句做优化，是否走索引以及先查那个子表等做效率最优优化
5、然后到执行器，这一步就是执行SQL语句了，调用存储引擎对数据做相应的处理

MySQL有哪些存储引擎

​ 常见的MyISAM和、InnoDB、Memory

MyISAM和InnoDB存储引擎区别

​ 1、InnoDB 支持事务，MyISAM 不支持事务。
​ 2、InnoDB 支持外键，而 MyISAM 不支持。
​ 3、InnoDB 是聚集索引，MyISAM 是非聚集索引。
​ 4、InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。
​ 5、InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。

B+树索引

索引一般是以索引文件的形式存放在磁盘中，所以用到索引的时候，遍历一个节点就是一次磁盘IO搜索，这也就是为什么B+树的高度决定了索引的效率，每次读取一页数据，数据是存储在叶子节点，非叶子节点只存取索引列的值和下一个节点的指针，每个叶子节点间也是有指针相连的，这个是为了范围读取的时候，只需要拿到初始节点，再按照偏移量取值即可。

MVCC（多版本并发控制）

一个行记录可能不止一个快照数据，一般称这种技术位行多版本技术，快照数据是指该行的之前版本数据，该实现束腰式通过undo log来完成的，没对一行数据修改，记录下当前的事务id和修改前的数据，这样可以提高读数据的并发从而提高查询效率。
如果需要实时的数据，不读区快照的，成为当前读，select * from tbl for update，或者select * from tbl lock in share mode，for update会加上排他锁，lock in share mode会给当前行记录加一个共享锁（S锁），其他事务可以向被锁定的行加S锁，但是如果加排他锁（X锁）会被组赛，当前读读取的值是最新的。

索引有哪些类型

​ 按照存储类型区分：
​ 哈希索引和B+树索引
​ 按照逻辑区分：
​ 唯一索引、普通索引、主键索引、全文索引

说一下哈希索引和B+树索引底层数据结构和区别

​ 哈希索引是散列表，对于等值查询时间复杂度是O(1)，但是对于范围查询效率比较低，不能使用hash索引排序。
​ B+树索引底层数据结构是B+树，是一个m阶多路平衡查找树，是由平衡二叉树演化而来的，树的高度决定磁盘IO的次数，结构如下图：

B树和B+树的区别

1、B+树的非叶子节点真正的data
2、B+树的所有叶子节点增加了链指针。

聚集索引和非聚集索引的区别

聚集索引：按照每张表的主键构造的一棵B+树，叶子节点中存放表的行记录，聚集索引的叶子节点也可以成为数据页，每个数据页通过一个双向链表连接。每张表只能拥有一个聚集索引。
非聚集索引：叶子节点不包含行记录的全部数据，叶子节点除了键值外，会有表的主键数据，通过找到主键，再通过主键索引（聚集索引）找到一个完整的行记录（注：这个操作也叫做回表）。

有什么情况可以避免回表

1、覆盖索引：（InnoDB支持覆盖索引），即从非聚集索引中就可以得到查询的记录，不需要通过聚集索引再查询，通俗讲就是select中查询的值是当前索引key和主键的值。
2、索引下推：在select * from tuser where name like ‘张%’ and age=10，B+树索引的叶子节点双链表特性，在一次回表找到指定的name=张*的主键之后，做一次回表，把当前节点的页数据加载到内存，通过指针获取下一个值，知道不符合条件的为止。

创建索引的时候要注意什么

1、不要盲目创建索引，因为索引的维护是有成本的，如果每列都建立索引，插入一行数据的时候，要维持B+树平衡，会做树的转换，如果是递增的还好，如果不是自增列，会有一定耗时。
2、可以利用索引的最左前缀原则，合理创建组合索引，对于select * from tbl where a=xxx and b=xxx，是可以使用(a,b)组合索引的，对于select * from tbl where a=xxx这个查询可以用这个组合索引，select * from tbl where b=xxx不能用这个索引。（这里面试官可能会问，select * from tbl where b=xxx and a=xxx，a和b这个顺序换了能不能击中索引，答案是会的，这个是优化器处理了，会重新排序，击中索引）。
3、选择建立索引列需要有区分度高的，性别地区这种区分度久很低，建立索引也不会有太高的查询效率，还会增加索引维护的负担。
4、尽量选取的主键长度小，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。
5、尽量选取自增字段做主键，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
6、建立索引的列尽量选择经常访问的列作为索引，还有就是如果建立索引的字段太长，最好是用能够区分度高的前缀做索引。
7、频繁更新的字段不要使用索引。

什么情况索引会失效

1、模糊查询，select * from tuser where name like ‘%张%’，注：这个如果没有左边的%号，是会击中索引的，因为最左前缀原则。
2、where中索引列有运算，select * from tuser where age = age + 1。
3、where中索引列使用了函数，select * from tuser where ABS(age) = 10。
4、如果数据量少的时候，优化器觉得不用索引更快的时候也会不走索引。
5、存在索引列的数据类型隐形转换。select * from tuser where name = 34，name是varchar类型。
6、复合索引没有用到左列的索引。

创建索引场景问题

邮件登陆场景，登陆通过邮件找到密码去匹配做登陆校验，会涉及select * from user where email = ‘’，这个时候怎么给email字段加索引，因为邮件是比较长的字符串，如果用整个字段做索引，每个页存储的节点就会少，效率会低点。
1、可以考虑最左匹配原则，通过分析，找到左边多少为区分度较高的位数作为索引。
2、如果像身份证的，前几位区分度不高的，可以考虑用倒叙，后几位区分度高，倒叙后用前几位做索引。
3、可以用hash，新建一个字段，用邮箱hash后作为该字段的值，用新增的字段做索引。

MySQL日志部分（了解）

binlog：逻辑日志，所有对数据的操作都会在binlog中记录，不包括SELECT和SHOW之类对数据本身没有修改的操作，binlog是可以不断生成的，可以通过配置max_binlog_size参数设置binlog文件大小，如果超过了阈值，会新建文件继续记录，文件名是初始的文件名+1，binlog是server层的，不区分存储引擎；主要是用作数据库的主备同步，还有就是对数据做恢复，根据设置，statement的话就是记录逻辑语句，也就是SQL，如果是rows的话就是记录具体数据改变。

InnoDB存储引擎的日志（了解）

重做日志（redo log）默认是有两个日志文件组成的，可以设置多个重做日志文件，以循环写入的方式进行写入。可以通过配置重做日志镜像做到重做日志的可靠性。写入重做日志之前是先会写入重做日志缓冲，然后按照一定顺序写入日志文件中。

​重做日志缓冲刷到重做日志条件：1、主线程定时刷新。2、innodb_flush_log_at_trx_commit参数控制，一般设置为1，就是每当提交一个事务的时候，会把缓冲刷到重做日志。
​
redo log：innodb引擎特有的，主要是为了实现事务的持久性，可以保证即使数据库异常宕机重启，之前提交的记录也不会丢失，redo log是一个固定大小的日志文件，可以通过配置调整大小，是一个环状的，有两个关键点，write pos和checkpoint，如果两个点重合，表示没有空间了，这个时候需要落盘，然后将redo log清空，redo log记录的关于每个页的更改的物理情况，也就是具体哪行改变了哪个值；为了保持逻辑一致性，redo log的写入有个两阶段提交，第一阶段写入redo log之后，设置当前的状态是prepare，然后会去写入binlog，等binlog完成之后，redo log会写入一个commit，这个时候，才是完整的写入。
​
这样的目的主要是为了保持两个日志的一致性，因为，在数据库重启的时候，会根据redo log和binlog去恢复没有执行或者异常的数据，如果这两个日志不一致，会导致一些例如扣款加商品场景下，没扣款却到商品的场景。具备crash-safe能力，也就是先记录到redo log再根据策略落盘，binlog不具备。
​表空间文件：ibdata1，ibdata2
​Innodb是采用表空间的形式存储数据的，/db/ibdata1和/dr2/db/ibdata2两个文件组成表空间
​可以通过innodb_file_per_table，参数设置独立表空间，命名规则为：表明.ibd，这个表空间仅存储该表的数据、索引、和插入缓冲BITMAP信息，其余的信息还是存储在默认表空间。
​表空间，删除了表中的数据，但是表空间大小还是没有变，这是因为引擎对增删操作做了一个标志，表示当前这个空间可以重复使用，空间并没有减少，如果需要降低表空间，去除表空洞，可以通过重建表来实现，具体实现：对A表压缩空间
​ 1、新建一个表B，遍历A表中的数据，插入到B表中。
​ 2、这个时候，对A表中的增删改查操作，会记录到一个row log中。
​ 3、所有记录插入到B表之后，同步row log中的操作，因为在同步数据过程中，还可能存在对A表增删改查的操作。
​ 4、将B表替换成A表，删除原来的A表。

重做日志（redo log）和二进制日志（binlog）的区别：
1、重做日志是Innodb引擎特有的，二进制日志是MYSQL层的，不管什么数据库引擎都会记录二进制日志。
2、重做日志记录的是关于每个页更改的物理情况，二进制日志记录的是关于一个事务的具体操作内容，是逻辑日志。

InnoDB是怎么实现事务的（重要）

要保证事务，主要从四个特性说起，ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

原子性，主要是通过undo log实现的，在执行一条插入语句，比如insert into tbl(a,b) values(1,2)，这个时候就会在undo log记录delete from tbl where a=1 and b=2。

持久性，大意就是事务提交了之后，数据不会丢失，无论是宕机还是服务异常，数据都是写入磁盘了的（通过redolog和binlog实现的）。具体流程如下，两阶段提交：

​ 1）执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
​ 2）执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
​ 3）引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
​ 4）执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
​ 5）执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

为什么必须有“两阶段提交”呢？这是为了让两份日志（binlog和redolog）之间的逻辑一致。要说明这个问题，我们得从一个问题说起：怎样让数据库恢复到半个月内任意一秒的状态？

前面我们说过了，binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

好了，说完了数据恢复过程，我们回来说说，为什么日志需要“两阶段提交”。由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

我们看看这两种方式会有什么问题。仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1、先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

2、先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

隔离性（重重要）

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

隔离级别：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

具体介绍可以看这篇博客https://tech.meituan.com/2014/08/20/innodb-lock.html
mysql优化可以看这篇博客：https://tech.meituan.com/2014/06/30/mysql-index.html

这里说一下就是不可重复读和幻读的区别，不可重复读主要是指一个长事务不断读取某条数据，另一个事务修改了，重点在修改。
幻读主要是一个长事务在不断读取表数据，另一个事务插入或者删除一条数据，侧重点在插入删除导致的行数不一致。

共享锁、排他锁、间隙锁

innodb默认的隔离级别

可重复读，但是可以避免幻读，主要是通过间隙锁，就是一个表，会在范围[1,3],[6,9]类似这种区间加锁，其他事务要插入数据的时候，会阻塞。

一致性

保证了上面的三个特性，就可以保证了一致性。

参考：极客时间MySQL实战45讲