MYSQL数据库总结

以下为MYSQL八股文

数据库

• 事务
• • 日志
  • • bin log：用于主从复制环境的建立
    • • MySQL的主从复制
    • redo log（持久性）：
    • undo log（原子性）：
  • `ACID`
  • ACID关系
  • 并发一致性问题
  • 隔离级别
  • 解决办法
  • • RC/RR MVCC
    • 幻读 快照读RR就可解决（表面解决）
• 索引
• • 索引类型
  • B+树索引分为主键索引和辅助索引
  • 聚簇与非聚簇索引
  • 唯一和主键索引
  • B+树索引，哈希索引
  • B+树与红黑树区别
  • B+树与B树区别
  • 选择在哪些列建索引
  • 最左匹配原则(说得不好，再找找)
  • 覆盖索引
• 锁
• • 死锁
  • #MYSQL语法

---

事务 日志 bin log：用于主从复制环境的建立 MySQL的主从复制

SLAVE开启两个线程，IO 和SQL Thread.
Master 中有一个binlog记录master的改变，SLAVE 的IO thread如果监测到改变，就去读binlog，并写入slave的relay log里。SQLthread会读取中继日志，并进行重放，即再次执行sql语句，达到复制。
（只有重放是随机读写，其他都是顺序读写）。

MYSQL为什么需要主从同步？
主写从读 如果sql某语句需要锁表，导致暂时不能使用读的服务，就很影响运行中的业务。而使用主从复制，主库负责写，从库负责读，这样，即使主库出现了锁表的情况，通过读从库也可以保证业务的正常运作。

redo log（持久性）：

物理日志。记录物理页的修改操作。

undo log（原子性）：

逻辑日志。回滚操作。

binlog和redo log区别
1.逻辑 物理日志
2.binlog在MYSQL数据库上层产生的，redo log在InnoDB存储引擎层产生的。
3.bin log事务提交后才写入。 redo log事务进行中不断写入。

ACID

1.原子性：事务的所有操作要么全成功，要么全失败
（失败，可以回滚，undo log 回滚日志记录事务所执行的操作，回滚时反向执行这些操作，** 逻辑日志）
2.一致性：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。
3.隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
4.持久性
一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务的执行结果也不能丢失。
（redo log实现持久性，发生系统崩溃可以用重做日志进行恢复，记录数据页的物理修改 ，** 物理日志）

ACID关系

这几个特性并非平级关系：
只有满足一致性，才能保证执行结果正确。
在无并发的情况下，事务串行执行，隔离性一定能满足。只要满足原子性就能满足一致性。
在并发的情况下，多个事务并行执行，事务不仅要满足原子性而且要满足隔离性，才能保证一致性。
事务满足持久化，是为了应对系统崩溃的情况。

AUTOCOMMIT
MYSQL默认采用自动提交模式，每个查询操作都会被当做一个事务并自动提交。

并发一致性问题

丢失修改：T2覆盖了T1的修改。（一个改了，另一个跟风改，把第一个覆盖了，后来者居上）
eg：T1先修改并提交生效，T2随后修改，T2的修改覆盖了T1的修改。

脏读：当前事务读到了其他事务未提交的数据。
（人家改，他偷看，看到了不该看的

T2偷看了T1修改但未提交的数据，T1搞了假动作，又不提交了，于是T2读到了脏数据，所以勿偷看）

不可重复读：在A事务未结束前，B事务也访问了同一数据，并做了修改，造成A事务两次读取结果不一致。
（两读之间数据被重写，导致两读不和谐。
A事务对一数据进行两次读操作，但两次读操作之间，B事务对这数据进行修改，导致事物两次数据读取不一致）

幻影读：也属于不可重复读。但这次T2事务做的是插入删除操作。

产生并发不一致问题的主要原因：破坏了事务的隔离性。解决办法：并发控制-----通过封锁或事务的隔离级别来实现。 封锁要用户自己控制，相当复杂。 数据库管理系统提供了事务的隔离级别，是更轻松的处理方式。

隔离级别

隔离级别并非越高越好，因为隔离级别越高并发度越低，要根据实际业务选择
未提交读：A事务没有提交，对其他事务也是可见的。
提交读：A事务只有提交了，别的事务才能看到。也就是没提交之前，别的事务不能看到修改。
可重复读：保证同一事务中多次读取同一数据的结果是一样的。
可串行化：强制事务串行执行，不会出现并发问题。

解决办法 RC/RR MVCC

what？MVCC保证读写之间没有锁竞争，提高性能。
名词解释：
快照读：读的不一定是最新的数据，可能是历史版本数据。
版本链：是由undo log和回滚指针连接起来的。
ReadView：当前活跃事务id列表 list（没提交的事务），min_id:活跃列表中最小事务id，max_id：
readview生成时刻系统已创建的最大事务id
trx_id:最近修改事务id

比对算法：
mid_id-max_id 将数据分成了三个区间
[已提交事务]–[未提交事务和已提交事务]–[当前readview时刻还没生成的事务]
版本链上的每个undo log的都有trx_id值,我们一条一条undo log比对，看能读取到的最新数据是什么。
若trx_id< min_id 说明这个事务已经提交，所以一定可以读取，所以这条undo log一定可以被读到。
若trx_id>max_id,说明生成快照读的时刻，这个事务还未出现，根本读不到（快照读没有这条记录）。
若min_id<=trx_id<=max_id,两种情况。
A. trx_id 如果在活跃列表里，则无法读到，因为是未提交的事务。但是如果是当前自己的事务是可见的。
B.trx_id不在活跃列表里，说明是已经提交的事务，可以读取到。

RC和RR：生成时刻不一样。如果是读已提交，是每一个select生成一个快照读。（没提交的数据在活跃列表里，一直读不到，所以可以解决脏读）
RC为什么没有实现可重复读？因为每一次select都生成一次快照读。假设A先读一次为20，B对这个事务进行修改并提交。A再读一次（此时生成了第二个快照读），则可以读到B修改的数据，所以无法解决不可重复读。
可重复读，只有当前事务第一次select生成快照读，之后每一次select都使用上次的readview，所以说每一次读取的值都一样。所以可以防止不可重复读。

回答要点：MVCC是如何解决不可重复读，脏读的问题？首先介绍版本链（undo log（trx_id）+回滚指针），然后RC,RR何时生成read view，read view有三个字段，然后，从版本链里倒着找，根据比对算法找到最新的数据。最后解释为什么可以解决RC，RR（上面的黑体字部分）。

幻读 快照读RR就可解决（表面解决）

幻读，在innodb是解决了的（MVCC）。因为readview只生成了一份，所以外界不管再怎么增加数据都没有影响，读的都是那一份readview

---

索引

索引是数据库中一个排序结构，协助快速查询，更新表中数据
MYSQL的索引存在磁盘，而不是内存。

索引类型

普通索引：针对数据库表创建索引
唯一索引：与普通索引类似，但是索引列的值必须唯一，可以为NULL
主键索引：特殊唯一索引。不允许有NULL

组合索引（联合索引）：多个字段联合起来作为一个组合索引
组合（唯一）索引创建方式

B+树索引分为主键索引和辅助索引

都是叶子节点存数据，辅助索引的数据是主键值，主键索引的数据的完整数据。
主键索引的索引方式为聚簇索引。一个表只能有一个聚簇索引。
辅助索引的索引方式为非聚簇索引。
how？ 辅助索引找到主键值，拿这主键值去聚簇索引，找到这个主键对应的完整数据

聚簇与非聚簇索引

物理上的区别：mysql索引类型跟存储引擎是相关的。innodb存储引擎数据文件跟索引文件全都放在一个文件的是聚簇索引。而myisam索引放在一个文件，数据放在另一个文件。看索引是不是聚簇索引，就看它的数据和索引是否存储在一起。

聚簇索引的叶子节点是数据节点，非聚簇索引的叶子节点的data域记录着主键的值。
who？谁能建立聚簇索引？主键是默认的聚簇索引。 若没定义主键，选择唯一非空索引代替。若没有这种索引，InnoDB会隐式定义一个主键所谓聚簇索引。
聚簇索引值不要求唯一，相同值在硬盘上的物理排序相同。

How many？ 一张表只允许存在一个聚簇索引，因为真实数据在磁盘上的物理顺序只能有一种。
what？ 聚簇索引的顺序，就是数据在硬盘上的物理顺序。
How? 性能好，因为一旦第一个记录被找到，连续索引值的记录紧跟其后（因为就是磁盘上的物理顺序）。
Attention! 经常更新的列不宜建立聚簇索引。（修改速度慢，因为要对已有数据重新进行排序。需要操作磁盘）

唯一和主键索引

1. 一张表可以建立多个唯一索引；一张表最多只能建立一个主键
2. 唯一索引 允许null；主键不允许null

B+树索引，哈希索引

1. B+树支持范围查询，hash索引不行
2. B+树支持联合索引的最左侧原则，hash不行
3. B+树支持order by排序，hash不行
4. B+树可以用like进行模糊查询，hash不行 ？和1区别？
5. hash索引只支持精确查找，等值查询效率比B+树高。

InnoDB存储引擎有一个特殊的功能叫自适应哈希索引。当某个索引值被使用的非常频繁，会在B+树索引之上再创建一个哈希索引，这样B+树索引就具有哈希索引的快速查找优点。

B+树与红黑树区别

B+树与红黑树比较

1. 更低的树高。因为红黑树出度为2，B+树出度非常大。
2. B+树树高更低。操作系统内存与磁盘以页为单位交换数据，索引一个节点为一个页的大小，一次IO就能完全载入一个节点。如果数据不在同一个磁盘块上，就要移动制动手臂进行寻道，会增加磁盘读取时间，而磁盘寻道的次数与树高成正比，所以B+树更适合磁盘数据的读取。
3. B+树会进行预读，磁盘进行顺序读取，不需要进行磁盘寻道，速度很快，并且相邻节点也能够被预先载入。

B+树与B树区别

B树是平衡查找树，所有叶子节点位于同一层
B+树具有B树的平衡性，并通过顺序访问指针来提高区间查找的性能。一个节点的key从左到右非递减排列。

操作：进行查找操作时，在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找，直到找到叶子节点，然后在叶子节点上进行二分查找，找到key所对应的data。
插入删除会破坏平衡，因此在进行插入删除后，要对树进行分裂，合并，旋转等操作（略）。

B+树是索引实现的底层数据结构，可以用于查找，排序，分组

1. 节点 ：B+树非叶子节点不存储数据，叶子节点存储数据。B树所有节点都存储数据。
2. 时间复杂度：B+ 树 O(logN) 因为都要到叶子节点查询 ; B树，不确定，和Key在树中位置有关，最好为O(1)。
3. 结构特性：B+树叶节点两两相连，增加区间访问性，可以使用范围查询。 B数每个节点key，data在一起，无法区间查找。
4. 适用： B+树更适合外部存储。非叶子节点无data域，每个节点能索引的范围更大更精确。

B树：（每个节点都有数据）

B+树：（叶子节点拥有全部数据）

why？ 为什么MYSQL数据库用B+树？（解释存疑）
存储同数量级的数据时，B+树的高度比B树小，IO操作的次数就少。因为B+树的所有非叶子节点只存索引，数据存在叶子节点，一般3层的树高度，即可存千万级别的数据，而B树不行。

选择在哪些列建索引

1. 主键上建索引
2. 重复度低的列 （eg：比如说性别重复度高，不适合做索引。身份证号重复度低，适合做索引）
3. 经常用于查询的列 比如where条件的列
4. 多表关联查询时作为关联条件的列 。
   A join B on 关联条件
   比如消费者表的订单ID列和订单表关联查询时作为关联条件的列。
5. 值固定不变的列。因为索引在磁盘上，频繁更行的IO操作，很耗性能。

Attention！ 不要建立太多索引，会耗费很大的磁盘空间。

最左匹配原则(说得不好，再找找)

使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

覆盖索引

是一种优化手段，索引包含所有需要查询字段的值。
优点：1.避免回表 2.减少数据量访问
（1.从辅助索引中查询得到记录，而不需要通过聚簇索引查询。不需要查询整行记录，减少大量IO操作.）（2.统计数量 count（*））
details：使用辅助索引，只能拿到主键值，但想要获取数据，还得再查询主键索引。但如果把需要的字段直接放在组合索引的叶子节点上，就可以直接返回了，而不需要回表，这就是覆盖索引。
因为索引中存在这些数据，查询就不会再次检索主键索引，从而避免回表。

---

锁 死锁

---

#MYSQL语法

参考：

MVCC部分：参考b站诸葛+马士兵+IT老哥视频