关于mysql面试用

1、mysql 的引擎有 innodb 和 myisam，默认是innodb
2、innodb 的引擎数据结构是B+树，节点存放key，所有数据均存储在叶子节点，而且数据是按照顺序排列的
3、节点是双向链表左右连接的，叶子节点中的数据是通过单向链表连接的
4、B树的 数据和key都存在节点，节点一般都会被加载到内存，内存有限，因此B树很费内存
5、索引优化是最左原则，开启慢sql查询功能，查看sql执行计划
6、主库多用innodb，从库多用myisam，myisam支持全文检索
7、innodb 的数据都存储在叶子节点
8、myisam的叶子节点存的key 和数据是引用地址
9、innodb就是聚集性索引（数据和key在一起），myisam是非聚集性索引（数据不在树结构上）

MyISAM 中，聚集索引和非聚集索引的叶子节点都会存储数据的文件地址
innodb数据结构图

事务的隔离级别

Read UnCommitted 读未提交 脏读
Read Committed 读已提交 不可重复读
Repeatable Read 可重复读 幻读 mysql 默认隔离级别
Serializable 串行化

什么是幻读？如何解决幻读？

幻读: 用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户在读取该范围数据行时，会发现新的幻影
如何解决幻读？ MVCC +间隙锁
MVCC
1.每次开启事务后都会递增创建一个版本号（version），之后的增删查改都是基于这个版本号进行操作的。
2、SELECT (version)读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。version >= createVersion and version < deleteVersion
3、INSERT (createVersion)将当前事务的版本号保存至行的创建版本号。 createVersion = version
4、UPDATE (createVersion)新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号。 新行createVersion = version，旧行deleteVersion = version
5、DELETE (deleteVersion)将当前事务的版本号保存至行的删除版本号。 deleteVersion = version

例子：
初始数据
表名：test
id number createVersion deleteVersion
1 1 1 0
select * from test; //当前version = 2，由于id=1的createVersion=1小于当前version所以可以查出来。

插入一条数据

id number createVersion deleteVersion
1 1 1
2 3 3
这时候如果上面的selct事务没结束继续查询的话并不会查询到id=2的数据。由于新插入的数据createVersion = 3 大于查询的version，这样就避免了幻读的情况。

更新一条数据，update test set number = 10 where id = 2

id number createVersion deleteVersion
1 1 1
2 3 3 6
2 10 6
如果事务在update之前开启（如version=5）那么只能看到createVersion<=5 and deleteVersion > 5 的数据，那么看到的数据就是update前的

间隙锁
mysql的间隙所是基于索引的，对于唯一索引innode会把间隙所降级为行锁，非唯一索引的话就需要用到间隙锁（也叫范围锁）

id number
1 1
2 3
13 3
23 3
31 11
40 40
事务一：select * from test where number = 3 for update
对于number索引可以分为多个范围
(无穷小,1)(1,3)(3,3)(3,11)(11，无穷大)
这时候锁住的是(3,3)区间，对应的临界记录是(id=1,number=1)(id=31,number=11)，对于这范围内的数据都是被锁住的。

事务二：insert into test(id, number) value(5, 3) //是会被阻塞

事务三：insert into test(id, number) value(25, 4) //也是会被阻塞

事务四：insert into test(id, number) value(35, 4) //也是会被阻塞

事务五：insert into test(id, number) value(22, 12) //插入成功 （因为12>11所以在锁区间外）

事务六：insert into test(id, number) value(71, 11) //插入成功 （number值一样，但是id71>31所以在锁区间外）

sql执行计划

explain select * from table
table | type | possible_keys | key | key_len | ref | rows | Extra
table：哪个表
type：这个很重要，是说类型，all（全表扫描），const（读常量，最多一条记录匹配），eq_ref（走主键，一般就最多一条记录匹配），index（扫描全部索引），range（扫描部分索引）
possible_keys：显示可能使用的索引
key：实际使用的索引
key_len：使用索引的长度
ref：联合索引的哪一列被用了
rows：一共扫描和返回了多少行
extra：using filesort（需要额外进行排序），using temporary（mysql构建了临时表，比如排序的时候），using where（就是对索引扫出来的数据再次根据where来过滤出了结果），using index 表示mysql使用覆盖索引

聚集索引 VS 非聚集索引

聚集索引（聚簇索引）
以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。
这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。

非聚集索引（非聚簇索引）
以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。
在叶子节点中，不再存储所有的数据了，存储的是键值和主键。对于叶子节点中的 x-y，比如 1-1。左边的 1 表示的是索引的键值，右边的 1 表示的是主键值，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

redo log、binlog、undo log

前两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同
1、redo log(重做日志)是InnoDB存储引擎层的日志，
2、binlog(归档日志)是MySQL Server层记录的日志

redo log
redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性,也就达到了crash-safe。

redo log日志再内存的的大小是固定的，一边写内存，一遍刷磁盘，即记录满了以后就从头循环写

binlog
binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的

redo log和binlog区别
1、redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
2、redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
3、redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
4、binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

undo log
保存了事务发生之前的数据的一个版本，可以用于回滚

图来自极客时间的mysql实践