1 数据库事务
1.1 隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
| 读未提交 | 1 | 1 | 1 |
| 读已提交 | 0 | 1 | 1 |
| 可重复读(默认) | 0 | 0 | 1 |
| 串行化读 | 0 | 0 | 0 |
1.1.1 脏读
一个事务读取到其他事务未提交的修改
1.1.2 不可重复读
在一个事务内,多次读取同一条数据之间, 其他事务修改了这条数据, 造成两次读取内容不一致
1.1.3 幻读
在一个事务内,多次读取同一批数据, 在其他事务新增/删除了这条数据, 两次读取到的数量不一致
总结 :
1 如果想在一个事务中,查询出其他事务中最新的提交, 可在查询SQL后+ for update
2 四个级别逐渐增强，每个级别解决一个问题。事务级别越高,性能越差
3 幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。
1.2 死锁
1.2.1 检测方式
- mysql设置锁等待阈值,超过阈值自动回滚事务
- wait-for graph算法来主动进行死锁检测
1.2.2 解决方案
更新多条数据时, 按照顺序更新
2 索引
2.1 页(Page)概念
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：
mysql> show variables like 'innodb_page_size';
而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
2.2 BTree(B-Tree)
等值查找9的过程
查询路径为：磁盘块1->磁盘块2->磁盘块7。
第一次IO：加载磁盘块1，在内存中遍历比较，9<17，走左边，找到磁盘块2。
第二次IO：加载磁盘块2，在内存中遍历比较，8<9<12，走中间，找到磁盘块6。
第三次IO：加载磁盘块6，依次遍历，找到9=9，取出data。
BTree的问题点
第一个weB树在范围查询时，性能并不理想。假如要查询13到30之间的数据，查询到13后又要回到根节点再去查询后面的数据，就会产生多次的查询遍历。
第二个问题：因为非叶子节点和叶子节点都会存储数据，所以占用的空间大，一个页可存储的数据量就会变少，树的高度就会变高，磁盘的IO次数就会变多。
2.3 B+Tree
1 特点
- 所有的叶子结点中包含了全部关键字的信息，非叶子节点只存储键值信息
- 所有叶子节点之间都有一个链指针。
- 数据记录都存放在叶子节点中。
范围查找, 9-15
查询路径为：磁盘块1->磁盘块2->磁盘块7。
第一次IO：加载磁盘块1，在内存中遍历比较，9<17，走左边，找到磁盘块2。
第二次IO：加载磁盘块2，在内存中遍历比较，8<9<12，走中间，找到磁盘块6。
第三次IO：加载磁盘块6，依次遍历，找到9=9，取出data。
第四次IO：由于最底层是有序的双向链表，所以继续往右遍历即可，直到不符合小于15的数据为止。
所以在范围查询的时候，是不需要像B树一样，再回到根节点，这就是底层采用双向链表的好处。
2 深度与记录
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为103。也就是说一个深度为3的B+Tree索引可以维护103 * 103 * 103 = 10亿条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
2.4 B+Tree和B-Tree区别
| B-Tree | B+Tree | |
| 非叶子节点存放内容 | key+data+指针 | key+指针 |
| 叶子节点存放内容 | key+data | key+data |
- B-Tree非叶子节点不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。
- B+树的最底层的叶子节点会形成一个双向有序链表，而B树不会。
- 所以B+树的优势在于，能保证等值查询和范围查询的快速查找。
2.5 聚集索引
1 描述
- 每个InnoDB表有且仅有一个聚簇索引
- 在表中，如果定义了主键，InnoDB会将主键索引作为聚簇索引。
- 如果没有定义主键，则会选择第一个不为NULL的唯一索引列作为聚簇索引。
- 如果以上两个都没有。InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
优先级 : 主键 > 不为null的唯一索引 > 隐式字段 ROWID
2 底层数据结构
比如有一张user表，如图所示：
底层的数据结构就像这样：
2.6 普通索引
1 底层数据结构
普通索引属于非聚簇索引，所以叶子节点存储的是主键值，底层的数据结构大概长这个样子：
比如要查询age=33的数据，那么首先查到磁盘块7的age=33的数据，获取到主键值，主键值为4。
接着再通过主键值等于4，查询到该行的数据。所以总得来说，底层会进行两次查询。
这种先通过查询主键值，再通过主键值查询到数据的过程就叫做回表查询。
2.7 聚集索引和非聚集索引区别
1 聚集索引(主键)叶子节点中保存的是该行的数据, 查询单行时不需要回表查询
2 非聚集索引叶子节点保存的是聚集索引(主键),查询单行时需要回表查询
3 根本区别是表记录的排列顺序和与索引的排列顺序是否一致
| 聚集索引 | 非聚集索引 | |
| 叶子节点存储内容 | 整张表的相应行记录数据 | 相应行数据的聚集索引键，即主键 |
| 索引数量 | 全表唯一,默认为主键 | 全表可有多个 |
| 字典类比 | 字母查询方式(顺序存放) | 偏旁查询方式(无序存放) |
2.8 回表与索引覆盖
1 回表
非聚集索引查询步骤
1 通过普通索引找到其叶子节点的聚集索引
2 通过聚集索引找到其叶子节点对应的行数据
2 索引覆盖
就是避免回表查询,把经常需要查询的列建立联合索引
常见的方法是：将被查询的字段，建立到联合索引里去。
3 主从复制
3.1 原理
-
master提交完事务后，写入binlog
-
slave连接到master，获取binlog
-
master创建dump线程，推送binglog到slave
-
slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
-
slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
-
slave记录自己的binglog
3.2 延迟原因
1 发现延迟
输入 show slave status 可查看主从延迟,0为不延迟, >0延迟
2 原因
1 当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了
2 slave的大型query语句产生了锁等待。首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。
3.3 延迟解决方案
1. 优化网络
2. 升级Slave硬件配置
3. 升级MySQL版本到5.7，使用并行复制
4. 单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
5 服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
3.4 数据丢失解决方案
1. 半同步复制
从MySQL5.5开始，MySQL已经支持半同步复制了，半同步复制介于异步复制和同步复制之间，主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到relay log中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。
2. 主库配置sync_binlog=1，innodb_flush_log_at_trx_commit=1
sync_binlog的默认值是0，MySQL不会将binlog同步到磁盘，其值表示每写多少binlog同步一次磁盘。
innodb_flush_log_at_trx_commit为1表示每一次事务提交或事务外的指令都需要把日志flush到磁盘。
注意:将以上两个值同时设置为1时，写入性能会受到一定限制，只有对数据安全性要求很高的场景才建议使用，比如涉及到钱的订单支付业务，而且系统I/O能力必须可以支撑！
BTree和B+Tree详解_lucky多多的博客-CSDN博客_btree和b+tree的区别
4 雪花ID
1 为什么不用UUID作为主键
在以前的项目中，最常见的两种主键类型是自增Id和UUID，在比较这两种ID之前首先要搞明白一个问题，就是为什么主键有序比无序查询效率要快，因为自增Id和UUID之间最大的不同点就在于有序性。
我们都知道，当我们定义了主键时，数据库会选择表的主键作为聚集索引(B+Tree)，mysql 在底层是以数据页为单位来存储数据的。
也就是说如果主键为自增 id 的话，mysql 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。如果一个数据页存满了，mysql 就会去申请一个新的数据页来存储数据。如果主键是UUID，为了确保索引有序，mysql 就需要将每次插入的数据都放到合适的位置上。这就造成了页分裂，这个大量移动数据的过程是会严重影响插入效率的。
一句话总结就是，InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。
2 为什么不用自增Id做主键
- 容易导致主键重复。比如导入旧数据时，线上又有新的数据新增，这时就有可能在导入时发生主键重复的异常。为了避免导入数据时出现主键重复的情况，要选择在应用停业后导入旧数据，导入完成后再启动应用。显然这样会造成不必要的麻烦。而UUID作为主键就不用担心这种情况。
- 不利于数据库的扩展。当采用自增id时，分库分表也会有主键重复的问题。UUID则不用担心这种问题。
3 java实现雪花Id
learningSummary/什么是雪花ID？.md at master · yehongzhi/learningSummary · GitHub
5 mysql的三种log
1 binlog
binlog是用于记录数据库表结构和表数据变更的二进制日志，比如insert、update、delete、create、truncate等等操作，不会记录select、show操作，因为没有对数据本身发生变更。
binlog的作用
主从复制,一般在公司中做一主二从的结构时，就需要master节点打开binlog日志，从机订阅binlog日志的信息，因为binlog日志记录了数据库数据的变更，所以当master发生数据变更时，从机也能随着master节点的数据变更而变更，做到主从复制的效果。
数据恢复,因为binlog记录了数据库的变更，所以可以用于数据恢复。我们看到上面图中有个字段叫Position，这个参数是用于记录binlog日志的指针。当我们需要恢复数据时，只要指定**--start-position和--stop-position**，或者指定**--start-datetime和--stop-datetime**，那么就可以恢复指定区间的数据。
2 redo log
UPDATE `user` SET `name`='刘德华' WHERE `id`='1';
我们想象一下mysql修改数据的步骤，肯定是先把id='1'的数据查出来，然后修改名称为'刘德华'。再深层一点，mysql是使用页作为存储结构，所以MySQL会先把这条记录所在的页加载到内存中，然后对记录进行修改。但是我们都知道mysql支持持久化，最终数据都是存在于磁盘中。
假设需要修改的数据加载到内存中，并且修改成功了，但是还没来得及刷到磁盘中，这时数据库宕机了，那么这次修改成功后的数据就丢失了。
为了避免出现这种问题，MySQL引入了redo log。
如图所示，当执行数据变更操作时，首先把数据也加载到内存中，然后在内存中进行更新，更新完成后写入到redo log buffer中，然后由redo log buffer在写入到redo log file中。
redo log file记录着xxx页做了xxx修改，所以即使mysql发生宕机，也可以通过redo log进行数据恢复，也就是说在内存中更新成功后，即使没有刷新到磁盘中，但也不会因为宕机而导致数据丢失。
redo log与事务机制是如何配合工作的？
如图所示：
第1-3步骤就是把数据变更，然后写入到内存中。
第4步记录到redo log中，然后把记录置为prepare(准备)状态。
第5，6步提交事务，提交事务之后，第7步把记录状态改成commit(提交)状态。
保证了事务与redo log的一致性。
binlog和redo log都可以数据恢复，有什么区别？
redo log是恢复在内存更新后，还没来得及刷到磁盘的数据。
binlog是存储所有数据变更的情况，理论上只要记录在binlog上的数据，都可以恢复。
举个例子，假如不小心整个数据库的数据被删除了，能使用redo log文件恢复数据吗？
不可以使用redo log文件恢复，只能使用binlog文件恢复。因为redo log文件不会存储历史所有的数据的变更，当内存数据刷新到磁盘中，redo log的数据就失效了，也就是redo log文件内容是会被覆盖的。
binlog又是在什么时候记录的呢？
提交事务的时候
3 undo log
undo log的作用主要用于回滚，mysql数据库的事务的原子性就是通过undo log实现的。我们都知道原子性是指对数据库的一系列操作，要么全部成功，要么全部失败。
undo log主要存储的是数据的逻辑变化日志，比如说我们要insert一条数据，那么undo log就会生成一条对应的delete日志。简单点说，undo log记录的是数据修改之前的数据，因为需要支持回滚。
那么当需要回滚时，只需要利用undo log的日志就可以恢复到修改前的数据。
undo log另一个作用是实现多版本控制(MVCC)，undo记录中包含了记录更改前的镜像，如果更改数据的事务未提交，对于隔离级别大于等于read commit的事务而言，不应该返回更改后数据，而应该返回老版本的数据。
4 总结
三种日志的用途
| 作用 | |
| binlog | 主要用于复制和数据恢复 |
| redo log | 恢复在内存更新后，还没来得及刷到磁盘的数据 |
| undo log | 实现回滚和多版本控制 |
三种日志的创建时机
| 创建时间 | |
| binlog | 提交事务时生成 |
| redo log | update在内存中更新完毕后记录 |
| undo log | 与redo log一致 |
https://github.com/yehongzhi/learningSummary/tree/master/MySQL%E6%95%B0%E6%8D%AE%E5%BA%93
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
