MYSQL

MySQL高级

视频地址：https://www.bilibili.com/video/BV1iq4y1u7vj?p=96&vd_source=a86a01b1a234e978987331d02659b4c2

1.MySQL架构介绍

1.1 MySQL逻辑架构

若要命中数据库缓存要保证sql语句完全相同（用key-value键值对存储，key为sql语句，value为查询结果）

cache 缓存，用于读
buffer 缓冲，用于写

1.2 MySQL中SQL执行流程

1.查询缓存
为什么说大多数情况下查询缓存就是个鸡肋？
①查询匹配时只有相同的查询操作才会命中缓存查询，使得鲁棒性大大降低。两个查询请求在任何字符上的不同都会导致缓存不会命中，因此查询缓存命中率很低。
②一些请求中包含某些系统函数、用户自定义变量和函数、一些系统表时，这个请求就不会被缓存，如now()，即使查询请求文本信息一致，不同时间的两次查询也应该得到不同的效果。
③缓存有缓存失效时间。对于更新压力大的数据库来说，缓存命中率会非常低。
（一般建议在静态表中使用查询缓存,query_cache_type用于设置查询缓存是否开启）
2.解析器
在解析器中对SQL语句进行词法分析与语法分析。
3.优化器
确定SQL语句的执行路径，比如是根据全表检索还是索引检索等。
一条查询可以有多种执行方式，最后都返回相同的结果。优化器的作用是找到其中最好的执行计划。
4.执行器
调用存储引擎API，执行文件查询

1.3 MySQL存储引擎

存储引擎就是指表的类型，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

• show engines查询当前存储引擎。
  
• set DEFAULT_STORAGE_ENGINES=MYISAM修改存储引擎

InnodB引擎：具备外键支持功能的事务存储引擎

1. MySQL5.5及之后默认使用的存储引擎；
2. InnodB是MySQL的默认事务型引擎，被设计用来处理大量的短期（short-lived）事务，可以保证事务的完整提交（Commit）和回滚（Rollback）；
3. 更新和删除操作效率更高；
4. 若无特别原因，一般选用InnodB；
5. InnodB是为处理巨大数据量的最大性能设计；
6. 缺点：写处理效率较差，且对内存要求较高（底层存储表数据的方式决定的，不仅要缓存索引，还要缓存真实数据）。

MyISAM引擎：主要的非事务处理存储引擎

1. 提供了包括全文索引、压缩、空间函数（GIS）等的特性，但不支持事务、行级锁、外键；
2. 崩溃后无法安全恢复；
3. 访问速度快，对事物没有完整性要求或者以查询和增加为主的应用；
4. 针对数据统计有额外的常数存储，count(*)效率高；
5. 应用场景：只读应用或者以读为主的业务。

InnodB VS MyISAM

	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会被锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作 有可能出现死锁
缓存	只缓存索引，不缓存真实数据（也就是说，即使缓存命中，也会有一次查表操作）	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源

Archive引擎：用于数据存档

1. 仅支持插入和查询；
2. zlib压缩库；
3. 行级锁；
4. 支持AUTO_INCREMENT列属性；
5. 适合存储大量的独立的作为历史记录的数据。

BlackHole引擎：丢弃写操作，读操作会返回空内容

CSV引擎：存储数据时，以逗号分隔各个数据项

1. 可以将普通的csv文件作为MySQL的表来使用，但不支持索引；
2. 可以作为一种数据交换的机制；
3. 数据快速导入、到处效率高；
4. 字段属性必须添加not null。

MEMORY引擎：置于内存的表

1. 表结构在物理磁盘中，数据存储到内存中——>响应速度快，但是当mysqld守护进程崩溃时数据会丢失。
2. 同时支持哈希索引和B+树索引；
3. 表大小受限；
4. 数据文件与索引文件分开存储；
5. 应用场景：①目标数据小且访问频繁；②数据临时且必须立即可用；③数据即使丢失也没有太大关系。

2.索引

2.1 索引的数据结构

1. 目的：减少磁盘I/O的次数，加快查询效率。
2. 索引：帮助MySQL高效获取数据的数据结构。
3. 本质：数据结构。“排好序的快速查找数据结构”
4. 索引是在存储引擎中实现的。
5. 优点：①降低数据库I/O成本；②唯一索引保证数据唯一性；③外键加速表与表之间连接；④减少查询中分组和排序的时间。
6. 缺点：①创建、维护索引需要耗费时间；②索引需要占用磁盘空间；③降低更新表的速度（当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，降低数据维护速度）

提示：
索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

数据页默认大小为16kb

2.2 索引

索引（Index）：
数据库系统中，以某种方式引用（指向）数据的数据结构，满足特定查找算法。（排好序的快速查找数据结构）

一般来说索引本身也很大，不可能全部存在内存中，因此索引往往以索引文件的形式存储在磁盘上。

索引优点：

1. 提高效率，降低IO成本
2. 降低数据排序成本，降低CPU消耗

索引缺点：

1. 降低更新表的速度。因为更新表时，不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段；
2. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

2.2.1 聚簇索引&非聚簇索引

索引按照物理实现方式，可以分为两种：聚簇索引和非聚簇索引。非聚簇索引也称为二级索引或者辅助索引。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

聚簇表示数据行和相邻的键值聚簇的存储在一起。

特点：

1. 使用记录主键值的大小进行记录和页的排序；
2. B+树的叶子节点存储的是完整的用户记录（即这个记录中存储了包括隐藏列在内的所有列的值）

优点：

1. 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快；
2. 聚簇索引对于主键的排序查找和范围查找速度非常快；
3. 按照聚簇索引排列顺序，查询显示一定范围数据时，由于数据紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量io操作。

缺点：

1. 插入速度严重依赖于插入顺序——>对于InnodB，一般会定义一个自增的ID列为主键；
2. 更新主键的代价很高——>一般定义主键不可更新
3. 二级索引访问需要两次索引查找，第一次查找主键值，第二次根据主键值找到行数据。

MySQL的表只能有一个聚簇索引 <—— 数据物理存储排序方式只能有一种
一般情况下聚簇索引就是该表的主键，若未定义主键，InnodB会选择非空的唯一索引替代。

非聚簇索引（二级索引、辅助索引）

非聚簇索引是按照非主键列建立的B+树，需要一次回表操作才能定位到完整的用户记录，也被称为二级索引，或者辅助索引。

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

聚簇索引 VS 非聚簇索引

1. 聚簇索引的叶子节点存储的是数据记录，而非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
2. 一个表只能有一个聚簇索引，因为一个表只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
3. 使用聚簇索引时，数据的查询效率高，但如果对数据进行插入、删除、更新等操作，效率会比非聚簇索引低。

2.2.2 联合索引

联合索引属于非聚簇索引。

2.3 InnodB与MyISAM的索引结构

2.3.1 InnodB的B+树索引的注意事项

1. 根页面位置万年不动
2. 内节点中目录项记录的唯一性（内节点中存储了主键索引）
3. 一个页面最少存储两条记录

2.3.2 MYISAM索引结构

MYISAM索引文件仅仅保存数据记录的地址（都可以看做非聚簇索引）

2.3.2 MyISAM VS InnodB索引

2.4 MySQL数据结构选择

加速查找速度的数据结构，常见的有两类：
（1）树，如平衡二叉搜索树，查询、插入、修改、删除的平均时间复杂度都是O(log2N)；
（2）哈希，如HashMap，查询、插入、修改、删除的平均时间复杂度都是O(1)

1 Hash结构（MEMORY支持）

Hash效率高，为什么索引结构要设计为树型？
①仅能满足=、<>、in查询（即等值查询），如果进行范围查询，哈希型的索引时间复杂度会退化为O(n)；而树型的有序特性，依然能够保持O(log2N)的高效率；
②Hash索引数据存储没有顺序，在ORDER BY情况下，还要对数据重新排序；
③对于联合索引情况，Hash值将联合索引键合并后一起计算，而无法对单独的一个键或者几个键进行查询；
④对于等值索引来说，通常Hash索引效率更高，但在索引列重复度较高的情况下，索引也会降低。

InnodB本身不支持Hash索引，但提供自适应Hash索引。
自适应哈希索引使用情况：若某个数据经常被访问，当满足一定条件时，就会将这个数据页的地址存放到Hash表中，这样下次查询时，就可以直接找到这个页面所在位置。这样B+树也具备了Hash索引的优点。

2. 二叉搜索树

3.二叉平衡搜索树（AVL树）

为了解决二叉搜索树退化为链表的问题，引入平衡二叉搜索树，又称为AVL树。
它是一层空树或者它的左右两个子树的高度差不超过1，并且左右两个子树也是平衡二叉树。

每访问一次节点就需要进行一次I/O操作

4.B-Tree（多路平衡查找树）

1. B树在插入和删除节点时如果导致树不平衡，就通过自动调节节点位置来保持树的特性；
2. 关键字集合分布在整棵树中，即叶子节点和非叶子节点都存放数据。搜索有可能在非叶子节点结束；
3. B树搜索性能等价于在关键字全集内做一次二分查找。
   

5. B+树

B+树和B树的差异主要表现在：

1. B+树中，孩子数量=关键字数；B树中，孩子数量=关键字数+1;
2. B+树中，非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）；而B树中，非叶子节点的关键字不会再出现在叶子节点中；
3. B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都保存在叶子节点中；而B树中，非叶子节点既保存索引，也保存数据记录；
4. B+树中所有关键字都会在叶子节点中出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字大小从小到大顺序链接。

B+树中间节点不保存数据的优势：
①效率稳定。B+树每次只有访问到叶子节点才能找到对应的数据，而B树非叶子节点也会存储数据，查询效率不稳定；
②查询效率更高。B+树由于非叶子节点不存储数据，数据页能够存放的目录项更多，因此B+树相比B树更矮胖（阶数更大，深度更低），查询所需要的磁盘I/O也会更少。同样的磁盘页大小，B+树可以存放更多的节点关键字；
③B+树范围查询效率高。

为了减少IO，索引树会一次性加载吗？

1. 数据库索引是存储在磁盘上的，若数据量很大，必然导致索引大小也会很大；
2. 当利用索引查询时，是不可能将大批量的索引全部加载进内存的，我们能做的只是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO
InnoDB存储引擎中页的大小为16kb，一般主键类型为INT或BIGINT，指针类型一般也为4或8个字节，假定一个数据页可以存放1000条行记录数据，那么一个深度为3的B+树索引可以维护10亿条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+树的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时将根节点常驻内存，也就是说查找某一键值的行记录时最多只需要1-3次磁盘I/O。

Hash索引与B+树索引的区别：

1. Hash不能进行范围查询，而B+树可以；
2. Hash索引不支持联合索引的最左侧原则，即联合索引的最左侧原则无法使用，而B+树可以。对于联合索引情况，Hash值将联合索引键合并后一起计算，而无法对单独的一个键或者几个键进行查询；
3. Hash索引不支持ORDER BY排序，也不支持模糊查询；
4. InnoDB不支持哈希索引。

2.4 InnoDB数据存储结构

2.5 索引的创建与设计原则

2.6 索引优化与查询优化

都有哪些维度可以进行数据库调优？

1. 索引失效、没有充分利用到索引——索引建立
2. 关联查询太多JION（设计缺陷或不得已的需求）——SQL优化
3. 服务器调优以及各个参数设置（缓冲、线程数等）——调整my.cnf（或.ini）
4. 数据过多——分库分表

SQL查询优化分类：物理查询优化和逻辑查询优化

• 物理查询优化：通过索引和表连接方式等技术来进行优化
• 逻辑查询优化：通过SQL等价变换提升查询效率

2.6.1 索引失效案例

1. 全值匹配我最爱
2. 最佳左前缀法则

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引

3. 主键插入顺序

我们自定义的主键列一般拥有AUTO_INCREMENT属性，在插入记录时存储引擎会自动填入自增的主键值，这样的主键占用空间小、顺序写入、减少页分裂。

4. 计算、函数、类型转换（自动或手动）导致索引失效
5. 类型转换导致索引失效
6. 范围条件右边的列索引失效

设计联合索引时最好将带范围条件的字段放在联合索引的最右边

7. 不等于（!= 或者 <>）索引失效
8. is null 可以使用索引，is not null无法使用索引

最好在设计数据表时就将字段设置为 NOT NULL约束，比如可以将INT类型的字段默认值设置为0，将字符类型的默认值设置为空字符串

同理，在查询中使用not like也无法使用索引，导致全表扫描

9. like以通配符%开头索引失效
10. OR 前后存在非索引的列，索引失效
11. 数据库和表的字符集使用不统一导致索引失效

数据库和表的字符集要统一使用utf8mb4，统一使用utf8mb4兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

2.6.2 关联查询优化

1. 外连接
   以左外连接为例，左边的表作为驱动表，右边的表作为被驱动表
2. 内连接
   （1）对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的；
   （2）对于内连接来说，如果表的连接条件只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表。
   （3）对于内连接来说，在两个表的连接条件都（不）存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”

JOIN语句原理

join方式链接多个表，本质就是各个表之间数据的循环匹配。
MySQL只支持一种表间关联方式，就是嵌套循环（Nested Loop Join）

1. 驱动表和被驱动表
   驱动表就是主表，被驱动表就是从表、非驱动表

2. Simple Nested-Loop Join（简单嵌套连接）
   粗暴的进行表的连接——>两种对Nested-Loop Join的优化算法

3. Index Nested-Loop Join（索引嵌套循环连接）
   优化思路：
   为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。
   通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，极大地减少了对内层表的匹配次数。

4. Block Nested-Loop Join（块嵌套循环连接）
   优化思路：
   在不存在索引的情况下，减少被驱动表的IO次数
   不再是逐条获取驱动表的数据，而是一块一块儿的获取，引入了jion buffer缓冲区，将驱动表中join相关的部分数据列缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性与join buffer中的所有驱动表记录进行匹配，降低被驱动表访问频率。

5. join小结：

   1. 整体效率比较：INLJ > BNLJ > SNLJ；
   2. 永远用小结果集驱动大结果集（其本质是减少外层循环的数据量）（小的度量单位是 表行数*每行大小）
   3. 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
   4. 增大join buffer size 大小（一次缓存数据越多，扫描内层表次数越少）
   5. 减少驱动表不必要的字段查询（增大join buffer缓存的数据量）

6. Hash join
   从MySQL8.0.18废弃BNLJ，加入hash join，默认使用hash join

2.6.3 子查询优化

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，但是子查询效率不高，原因：
① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

结论：
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。

结论：尽量不要使用NOT IN 或者 NOT EXISTS（子查询），用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

2.6.4 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
回答：
在MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。

• Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高；
• FileSort排序则一般在内存中进行排序，占用CPU较多，如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率极低。

优化建议：

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排
   序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

小结：
INDEX a_b_c(a,b,c) order by
能使用索引最左前缀

• ORDER BY a
• ORDER BY a,b
• ORDER BY a,b,c
• ORDER BY a DESC,b DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引

• WHERE a = const ORDER BY b,c
• WHERE a = const AND b = const ORDER BY c
• WHERE a = const ORDER BY b,c
• WHERE a = const AND b > const ORDER BY b,c

不能使用索引进行排序

• ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
• WHERE g = const ORDER BY b,c /* 丢失a索引*/
• WHERE a = const ORDER BY c /* 丢失b索引*/
• WHERE a = const ORDER BY a,d /* d不是索引的一部分*/
• WHERE a in (…) ORDER BY b,c /* 对于排序来说，多个相等条件也是范围查询*/

结论：

1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择 idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的 。
2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

2.6.5 GROUP BY优化

（类似ORDER BY）

2.6.7 优化分页查询

eg:EXPLAIN SELECT * FROM student LIMIT 20000,10;
优化思路一：
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

优化思路二：
该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

2.6.8 优先考虑覆盖索引

覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

覆盖索引的利弊：
好处：

1. 避免Innodb表进行索引的二次查询（回表），减少IO次数
2. 可以把随机IO变成顺序IO加快查询效率

由于覆盖索引可以减少树的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端：
索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

2.6.9 索引下推

索引条件下推（Index Condition Pushdown,ICP），是一种在存储引擎层使用索引过滤数据的优化方式。

在不使用ICP索引扫描的过程：
storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层。
server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行。

使用ICP扫描的过程：
storage层：
首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。
server 层：
对返回的数据，使用table filter条件做最后的过滤。

使用前后的成本差别
使用前，存储层多返回了需要被index filter过滤掉的整行记录
使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。
ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。

ICP的使用条件：
①只能用于二级索引(secondary index)
②当SQL使用覆盖索引时，不支持ICP优化方法。
③explain显示的执行计划中type值（join 类型，表访问类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
④ ICP可以用于MyISAM和InnnoDB存储引擎，包括分区表InnoDB表和MyISAM表。
⑤ 相关子查询的条件不能使用ICP。

2.6.10 其它查询优化策略

1. EXISTS 和 IN 的区分
   Question：哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗？
   Answer：索引是个前提，选择与否要看表的大小，选择的标准是“小表驱动大表”，比如SELECT * FROM A WHERE cc IN (SELECT cc FROM B) 和 SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc =A.cc)；当A小于B时，选择EXISTS；当A大于B时，选择IN。

2. COUNT()与COUNT(具体字段)效率
   Question：在 MySQL 中统计数据表的行数，可以使用三种方式： SELECT COUNT() 、 SELECT COUNT(1) 和 SELECT COUNT(具体字段) ，使用这三者之间的查询效率是怎样的？
   Answer：COUNT(*)、COUNT(1) 本质没有差别，执行效率一样，不同存储引擎会导致效率不一样（MyISAM效率O(1)，InnoDB效率O(n)）；count(具体字段)应当尽量使用二级索引，效率更高些（占用空间更小）。

3. LIMIT 1 对优化的影响
   针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
   如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加
   上 LIMIT 1 了。

3 数据库设计规范

3.1 范式（Normal Form,NF）

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。
高阶的范式一定符合低阶范式的要求。
一般来说，在关系型数据库设计中，最高也就遵循到巴斯·科德范式（BCNF），普遍还是3NF。

3.1.1 相关概念

超键：能唯一标识元组的属性集叫做超键。
候选键：如果超键不包括多余的属性，那么这个超键就是候选键。
主键：用户可以从候选键中选择一个作为主键。
外键：
主属性：包含在任一候选键中的属性称为主属性。
非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

3.1.2 第一范式（1NF）

确保数据表中的每个字段的值必须具有原子性，也就是说数据表的每个字段的值都不能再拆分。

3.1.3 第二范式（2NF）

第二范式要求在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可以唯一标识的，而且苏颇有非主键字段，就必须完全依赖主键，不能只依赖主键的一部分。（消除部分依赖）

2NF要求实体的属性完全依赖主关键字，如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

1NF告诉我们字段属性需要是原子性的，而2NF告诉我们一张表就是一个独立的对象，一张表只表达一个意思

3.1.4 第三范式（3NF）

第三范式要求数据表中的所有非主键字段不能依赖于其他非主键字段，确保每列和主键列直接相关（消除传递依赖）

范式的优点：
降低数据冗余
范式的缺点：
可能降低查询的效率，因为范式等级越高，设计出来的数据表也就越多、月精细，数据的冗余度就越低，进行数据查询时就可能需要关联多张表，不但代价高昂，也可能使一些索引策略无效。

3.1.5 巴斯范式（BCNF）

若一个关系到达了第三范式，并且它直邮一个候选键，或者它的每个候选键都是单属性，则该关系自然达到巴斯范式。

3.2 反范式化

规范化 vs 性能

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要
2. 在数据规范化的同时 , 要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4. 通过在给定的表中插入计算列，以方便查询

3.2.2 反范式化的新问题

• 存储 空间变大 了
• 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致
• 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源
• 在 数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加 复杂

3.2.3 反范式化的应用场景

当冗余信息有价值或者能大幅度提高查询效率时，才会使用反范式化。
增加冗余字段建议：①冗余字段不需要经常修改 ②冗余字段查询时不可或缺。

1. 历史快照
2. 数据仓库（存储历史数据，对增删改要求不高）

3.3 ER模型

ER模型也叫做关系实体模型，是用来描述现实生活中客观存在的事物、事物的属性，以及事物间关系的一种数据模型。在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮助我们理清业务逻辑，从而设计出优秀的数据库。

3.3.1 ER模型三要素

ER 模型中有三个要素，分别是实体、属性和关系。
实体 ，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用 矩形 来表
示。实体分为两类，分别是 强实体 和 弱实体 。强实体是指不依赖于其他实体的实体；弱实体是指对另
一个实体有很强的依赖关系的实体。
属性 ，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用 椭圆形 来表示。
关系 ，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模
型中用 菱形 来表示。

注意：实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在
的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。

3.3.2 关系类型

一对一 ：指实体之间的关系是一一对应的，比如个人与身份证信息之间的关系就是一对一的关系。一个
人只能有一个身份证信息，一个身份证信息也只属于一个人。
一对多 ：指一边的实体通过关系，可以对应多个另外一边的实体。相反，另外一边的实体通过这个关
系，则只能对应唯一的一边的实体。比如说，我们新建一个班级表，而每个班级都有多个学生，每个学
生则对应一个班级，班级对学生就是一对多的关系。
多对多 ：指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中，供货商与超市之
间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购
商品。再比如一个选课表，有许多科目，每个科目有很多学生选，而每个学生又可以选择多个科目，这
就是多对多的关系。

3.4 数据表的设计原则

“三少一多”

1. 数据表的个数越少越好
2. 数据表中的字段个数越少越好
3. 数据表中联合主键的字段个数越少越好
4. 使用主键和外键越多越好

4.数据库事务

4.1 事务

4.1.1 数据库事务概述

基本概念

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态，只有InnoDB支持。
事务处理的原则：保证所有事务都作为 一个工作单元 来执行，即使出现了故障，都不能改变这种执行方
式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就 永久 地保
存下来；要么数据库管理系统将 放弃 所作的所有 修改 ，整个事务回滚( rollback )到最初状态。

事务的ACID特性

原子性（atomicity）：
原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

一致性（consistency）：
根据定义，一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态
是 语义上 的而不是语法上的，跟具体的业务有关。
那什么是合法的数据状态呢？满足 预定的约束 的状态就叫做合法的状态。通俗一点，这状态是由你自己
来定义的（比如满足现实世界中的约束）。满足这个状态，数据就是一致的，不满足这个状态，数据就
是不一致的！如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作
之前的状态。

隔离性（isolation）：
事务的隔离性是指一个事务的执行 不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对 并发 的
其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）：
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的 ，接下来的其他操作和数据库
故障不应该对其有任何影响。
持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改
的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做
的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执
行，从而使事务具有持久性。

总结：
ACID使事务的四大特性，原子性使基础，一致性是约束条件，隔离性是手段，而持久性是最终目的。

数据库事务，其实就是数据库设计者为了方便起见，把需要保证原子性、一致性、隔离性、持久性的一个或多个数据库操作称为一个事务。

事务的状态

活动的（active）
部分提交的（partially committed）
失败的（failed）
中止的（aborted）
提交的（committed）

4.1.2 事务隔离级别

数据并发问题

1. 脏写
   对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写。
2. 脏读
   对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。
   之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。
3. 不可重复读
   对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。
4. 幻读
   对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插 入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。（删除不算幻读）

SQL四种隔离级别

• READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
• READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。
• REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍 然存在。这是MySQL的默认隔离级别。
• SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避 免脏读、不可重复读和幻读。
  （隔离级别越高，一致性越好，但并发性能越差）

4.2 MySQL事务日志

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？
事务的隔离性由 锁机制 实现。
而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。

• REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持 久性。
• UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

REDO LOG 和 UNDO LOG都可以视为是一种恢复操作，但是：

• redo log：是存储引擎层（InnoDB）生成的日志，记录的是“物理级别”上的页修改操作，比如页号xxx，偏移量yyy写入了zzz数据，主要是为了保证数据的可靠性。
• undo log：是存储引擎层（InnoDB）生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了INSERT操作，那么undo log就记录一条与之相反的DELETE操作。主要用于事务的回滚（undo log记录的是每个修改操作的逆操作）和一致性非锁定读（undo log回滚行记录到某种特定的版本——MVCC，即多版本并发控制）

4.2.1 redo日志

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘（checkPoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

1. 好处、
   redo日志降低了刷盘频率
   redo日志占用的空间非常小
2. 特点
   • redo日志是顺序写入磁盘的
   • 事务执行过程中，redo log不断记录

（redo log和bin log的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，知道这个事务提交才会一次性的写入到bin log文件中）

3. redo的组成
   • 重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的。
   • 重做日志文件 (redo log file) ，保存在硬盘中，是持久的。
4. redo整体流程
   以一个更新事务为例
   

第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
第4步：定期将内存中修改的数据刷新到磁盘中

5. redo log刷盘策略
   
6. 小结
   

4.2.2 Undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个undo log。
注意，由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo日志。

1. Undo日志的作用
   作用1：回滚数据
   只是将数据库逻辑地恢复到原来的样子，所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
   作用2：MVCC（多版本并发控制）
   在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该纪录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
2. 小结
   
   undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。
   redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程

4.3 锁

事务的隔离性由锁机制来实现。

4.3.1 概述

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对 并发操作进行控制 ，因此产生了 锁 。同时 锁机制 也为实现MySQL的各个隔离级别提供了保证。 锁冲突 也是影响数据库 并发访问性能 的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

4.3.2 MySQL并发事务访问相同记录

1. 读-读情况
   读-读 情况，即并发事务相继 读取相同的记录 。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。

2. 写-写情况
   写-写 情况，即并发事务相继对相同的记录做出改动。
   在这种情况下会发生 脏写 的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时，需要让它们 排队执行 ，这个排队的过程其实是通过 锁 来实现的。这个所谓的锁其实是一个 内存中的结构 ，在事务执行前本来是没有锁的，也就是说一开始是没有 锁结构 和记录进行关联的。如图所示：
   
   当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的 锁结构 ，当没有的时候就会在内存中生成一个 锁结构 与之关联。比如，事务 T1 要对这条记录做改动，就需要生成一个 锁结构与之关联：
   

   trx信息：代表这个锁结构是为哪个事务生成的
   is_waiting：代表当前事务是否在等待

   
   

   小结几种说法：

   • 不加锁
     意思就是不需要在内存中生成对应的 锁结构 ，可以直接执行操作。
   • 获取锁成功，或者加锁成功
     意思就是在内存中生成了对应的 锁结构 ，而且锁结构的 is_waiting 属性为 false ，也就是事务可以继续执行操作。
   • 获取锁失败，或者加锁失败，或者没有获取到锁
     意思就是在内存中生成了对应的 锁结构 ，不过锁结构的 is_waiting 属性为 true ，也就是事务需要等待，不可以继续执行操作。

3. 读-写 或 写-读

    方案一：读操作利用多版本并发控制（ MVCC ），写操作进行加锁 。
   

   普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
   在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了 事务不可以读取到未提交的事务所做的更改 ，也就是避免了脏读现象；
   在REPEATABLE READ 隔离级别下，一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个ReadView，之后的SELECT操作都 复用 这个ReadView，这样也就避免了不可重复读和幻读的问题。

    方案二：读、写操作都采用 加锁 的方式。
   

   小结对比发现：

   采用 MVCC 方式的话， 读-写 操作彼此并不冲突， 性能更高 。 采用 加锁 方式的话， 读-写 操作彼此需要 排队执行 ，影响性能。
   一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行。下面就讲解下MySQL中不同类别的锁。

4.3.3 锁的分类

4.3.3.1 从数据操作的类型划分：读锁、写锁

读锁 ：也称为 共享锁（Shared Lock,S Lock） 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
写锁 ：也称为 排他锁（Exclusive Lock,X Lock） 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上

读操作可以加S锁，也可以加X锁；但写操作只能加X锁

4.3.3.2 从数据操作的粒度划分：表级锁、页级锁、行锁

1. 表锁（Table Lock）
   该锁会锁定整张表，并不依赖于存储引擎，且表锁是开销最小的策略。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。但锁的粒度大导致锁资源竞争频繁，并发率降低。

   ① 表级别的S锁、X锁
   （一般情况下，不会使用InnoDB提供的表级别的S锁和X锁，只会在一些特殊情况下，比如说崩溃恢复过程中使用到）
   手动获取InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写：

    LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。 
    LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。
   

   ② 意向锁 （intention lock）
   InnoDB 支持 多粒度锁（multiple granularity locking） ，它允许 行级锁 与 表级锁 共存，而意向锁就是其中的一种 表锁 。
   意向锁分为两种：
   意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

    -- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
    SELECT column FROM table ... LOCK IN SHARE MODE;
   

   意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

    -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。 
    SELECT column FROM table ... FOR UPDATE;	
   

   即：意向锁是由存储引擎 自己维护的 ，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在数据表的对应意向锁 。

   小结：

   1. InnoDB 支持 多粒度锁 ，特定场景下，行级锁可以与表级锁共存。
   2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥 。
   3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
   4. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求。

   ③ 自增锁
   ④ 元数据锁（MDL锁）
   当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。不需要显示使用

2. InnoDB中的行锁
   行锁也称为记录锁，顾名思义，就是锁住某一行（某条记录row）。需要注意的是，MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。
   优点：锁定粒度小，发生锁冲突概率低，可以实现的并发度高。
   缺点：对于锁的开销比较大，加锁会比较慢，容易出现死锁。
   ① 记录锁（Record Locks）
   记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。
   记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。
   - - 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
   - - 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。
   ② 间隙锁（Gap Locks）
   MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用 加锁 方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些 幻影记录 加上 记录锁 。InnoDB提出了一种称之为Gap Locks 的锁，官方的类型名称为： LOCK_GAP ，我们可以简称为 gap锁 。

    gap锁的提出仅仅是为了防止插入幻影记录而提出的。
   

   ③ 临键锁
   有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY。
   Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁，innodb默认的锁就是Next-Key locks。
   临键锁 = 记录锁 + 间隙锁

    begin; select * from student where id <=8 and id > 3 for update;
   

   ④ 插入意向锁
   我们说一个事务在 插入 一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁 （ next-key锁也包含 gap锁 ），如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个 间隙 中 插入 新记录，但是现在在等待。

    插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁，而非意向锁
    插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。
   

3. 页锁
   页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量超过了这个层级的阈值时，就会进行 锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

4.3.3.3 从对待锁的态度划分:乐观锁、悲观锁

乐观锁和悲观锁并不是锁，而是锁的 设计思想 。

1. 悲观锁（Pessimistic Locking）
   悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

   悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现。

select … for update是MySQL中的悲观锁，它在执行过程中所有扫描的行都会被锁上，因此在MySQL中用悲观锁必须确定使用了索引，而不是全表扫描，否则会把整个表锁住。

2. 乐观锁（Optimistic Locking）
   乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的一种实现方式：CAS实现的。

   1. 乐观锁的版本号机制
      在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE … SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改，修改就不会成功。
   2. 乐观锁的时间戳机制
      时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

   乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或者时间戳），从而证明当前拿到的数据是否最新。

3. 两种锁的适用场景
   从这两种锁的设计思想中，我们总结一下乐观锁和悲观锁的适用场景：

   1. 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
   2. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。
      

4.3.3.4 按加锁的方式划分：显式锁、隐式锁

1. 隐式锁
   一个事务对新插入的记录可以不显式的加锁（生成一个锁结构），但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务加了一个锁结构，然后自己再生成一个锁结构后进入等待状态。隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。
   隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时，隐式锁转化为显式锁。

   隐式锁的逻辑过程如下：
   A. InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。
   B. 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务，首先将 隐式锁 转换为 显式锁 (就是为该事务添加一个锁)。
   C. 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E。
   D. 等待加锁成功，被唤醒，或者超时。
   E. 写数据，并将自己的trx_id写入trx_id字段。

2. 显式锁
   通过特定的语句进行加锁，我们一般称之为显示加锁，例如：
   显示加共享锁：

    select .... lock in share mode
   

   显示加排它锁：

    select .... for update
   

4.3.3.5 全局锁

全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用 场景 是：做 全库逻辑备份 。
全局锁的命令：

Flush tables with read lock

4.3.3.6 死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。死锁示例：

这时候，事务1在等待事务2释放id=2的行锁，而事务2在等待事务1释放id=1的行锁。 事务1和事务2在互相等待对方的资源释放，就是进入了死锁状态。

产生死锁的必要条件

1. 两个或者两个以上事务；

2. 每个事务都已经持有锁并申请新的锁；

3. 锁资源同时只能被同一个事务持有或者不兼容；

4. 事务之间因为持有锁和申请锁导致彼此循环等待。

    死锁的关键在于：两个或两个以上的事务加锁的顺序不一致。
   

如何处理死锁

当出现死锁以后，有 两种策略 ：
策略一：直接进入等待，直到超时。
即当两个事务相互等待时，当一个事务的等待时间超过阈值，就将其回滚，另外事务继续进行。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
缺点：对于在线服务来说，这个等待时间通常无法接受。（降低等待阈值也不合适，容易误伤到普通的锁等待）
策略二：发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on ，表示开启这个逻辑。

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁

如何避免死锁

• 合理设计索引，使业务SQL尽可能通过索引定位更少的行，减少锁竞争。
• 调整业务逻辑SQL执行顺序，避免update/delete长时间持有锁的SQL在事务前面。
• 避免大事务，尽量将大事务拆成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也更小。
• 在并发比较高的系统中，不要显式加锁，特别是在事务里显式加锁。
• 降低隔离级别。

4.3.4 锁的内存结构

结构解析：

1. 锁所在的事务信息 ：
   不论是 表锁 还是 行锁 ，都是在事务执行过程中生成的，哪个事务生成了这个 锁结构 ，这里就记录这个事务的信息。
   此 锁所在的事务信息 在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比方说事务id等。

2. 索引信息 ：
   对于 行锁 来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

3. 表锁／行锁信息 ：
   表锁结构 和 行锁结构 在这个位置的内容是不同的：

   • 表锁：
     记载着是对哪个表加的锁，还有其他的一些信息。

   • 行锁：
     记载了三个重要的信息：
     Space ID ：记录所在表空间。
     Page Number ：记录所在页号。
     n_bits ：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bits 属性代表使用了多少比特位。

     n_bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后也不至于重新分配锁结构

4. type_mode ：
   这是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，如图所示：
   

5. 其他信息 ：
   为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。

6. 一堆比特位 ：
   如果是 行锁结构 的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性表示的。 锁结构 最后的一堆比特位就对应着一个页面中的记录，即一个比特位映射到页内的一条记录。

4.4 多版本并发控制 MVCC

4.4.1 什么是MVCC

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本（undo log来体现）管理（通过readview体现）来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

4.4.2 快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写冲突 ，做到即使有读写冲突时，也能做到 不加锁 ， 非阻塞并发读 ，而这个读指的就是 快照读 , 而非 当前读 。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

4.4.2.1 快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读；比如这样：

SELECT * FROM player WHERE ...

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。
既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

4.4.2.2 当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。比如：

SELECT * FROM student LOCK IN SHARE MODE; # 共享锁 
SELECT * FROM student FOR UPDATE; # 排他锁 
INSERT INTO student values ... # 排他锁 
DELETE FROM student WHERE ... # 排他锁 
UPDATE student SET ... # 排他锁

4.4.3 MVCC实现原理

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

4.4.3.1 什么是ReadView

在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在undo log中。如果一个事务要查询这个行记录，需要读取哪个版本的行记录由ReadView来控制，ReadView用以解决行的可见性问题。
**ReadView就是某一个事务在使用MVCC机制进行快照读操作时产生的读视图。**当事务启动时，会生成数据库系统当前的一个快照。InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID（“活跃”指的是启动了但还未提交）

4.4.3.2 ReadView设计思路

针对隔离级别：Read Committed 和 Repeatable Read（它们都必须保证读到已经提交了的事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。）

ReadView中主要包含四个重要组成部分：

1.creator_trx_id

creator_trx_id 创建这个ReadView的事务ID

说明：
只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

2.trx_ids

trx_ids 表示在生成ReadView时当前系统中活跃的读写事务的事务id列表

3.up_limit_id

up_limit_id ，活跃的事务中最小的事务 ID。

4.low_limit_id

low_limit_id 表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1， 2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。

4.4.3.3 ReadView设计规则☆

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。

• 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值小于 ReadView中的 up_limit_id值，表明生成该版本的事务在当前 事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值大于或等于 ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
• 如果被访问版本的trx_id属性值在ReadView的up_limit_id 和 low_limit_id 之间，那就需要判断一下trx_id属性值是不是在 trx_ids 列表中。
  • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
  • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

4.4.3.4 MVCC整体操作流程☆

1. 首先获取事务自己的版本号，也就是事务 ID；
2. 获取 ReadView；
3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
5. 最后返回符合规则的数据。

如果某个版本的·数据对当前事务不可见，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，以此类推，直到版本链中的最后一个版本，若最后一个版本也不可见，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

InnoDB中，MVCC是通过Undo Log + Read View进行数据读取，Undo Log 保存了历史快照，而Read View规则则帮助我们判断当前版本的数据是否可见。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View。

注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View。

5.数据库日志

除二进制日志外，其他日志都是文本日志。默认情况下，所有日志都创建于MySQL数据目录中。
日志的弊端：

1. 日志功能会 降低MySQL数据库的性能 。
2. 日志会 占用大量的磁盘空间 。

Redo Log

Undo Log

慢查询日志

记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。

通用查询日志（general log）

通用查询日志用来记录用户的所有操作，包括启动和关闭数据库服务、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

错误日志（error log）

记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。

二进制日志（Binary Log,Bin Log）

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

以事件形式记录并保存在二进制文件中，通过这些信息，我们可以在线数据更新操作的全过程。

binlog主要应用场景：

1. 数据恢复
   如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
2. 数据复制
   由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

binlog的写入机制

上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快；
上图的fsync，才是把数据持久化到磁盘的操作。

write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。

• 为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。
• 为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。
• 最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。（如果机器宕机，会丢失最近N个事务的binlog日志）

binlog VS redolog

• redolog是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层产生的；
• binlog是逻辑日志，记录内容是语句的原始逻辑，类似于“给id=2这一行的c字段加上1”，属于MySQL的server层。
• 虽然它们都属于持久化的保证，但是侧重点不同：
  • redolog让InnoDB存储引擎拥有了崩溃恢复能力；
  • binlog保证了MySQL集群结构的数据一致性。

两阶段提交

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的 写入时机 不一样。

redo log与binlog两份日志之间的逻辑不一致，可能会出现redolog写入完成，而binlog没写完就异常，倒追binlog里面没有对应的修改记录的问题。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

使用两阶段提交后，写入binlog时发生异常也不会有影响。

中继日志

用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件 中，这个从服务器本地的日志文件就叫中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的 数据同步 。

数据定义语句日志

记录数据定义语句执行的元数据操作。

6.主从复制

6.1 主从复制概述

6.1.1 如何提升数据库并发能力

1. 优化SQL和索引
2. 缓存策略
   Redis缓存架构
   3. 主从架构、读写分离
   一般应用对数据库而言都是“ 读多写少 ”，也就说对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做 主从架构 、进行 读写分离 ，这样同样可以提升数据库的并发处理能力。

6.1.2 主从复制的作用

主从同步设计不仅可以提高数据库的吞吐量，还有以下三个方面的作用：

1. 读写分离
   通过主从复制方式来同步数据，然后通过读写分离提高数据库并发处理能力。
   其中一个是Master主库，负责写入数据，即写库；
   其它都是Slave从库，负责读取数据，即读库。

   当主库进行更新时，会自动将数据复制到从库中，而我们在客户端读取数据的时候，会从从库中进行读取。

   面对“读多写少”的需求，采用读写分离的方式，可以实现更高的并发访问。同时，我们还能对服务器进行负载均衡，让不同的读请求按照策略均匀地分发到不同的从服务器上，让读取更加顺畅。

2. 数据备份
   通过主从复制将主库的数据复制到了从库上，相当于一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。

3. 具有高可用性
   数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

6.2 主从复制原理

6.2.1 主从复制原理——“三个线程”

二进制日志转储线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉。
从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。
复制三步骤
步骤1： Master 将写操作记录到二进制日志（ binlog ）。这些记录叫做二进制日志事件（binary log events）
步骤2： Slave 将 Master 的binary log events拷贝到它的中继日志（relay log）；
步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从 接入点 开始复制。

复制最大问题：延时

6.2.2 主从复制基本原则

1. 每个Slave只能有一个Master；
2. 每个Slave只能有一个唯一的服务器ID；
3. 每个Master可以有多个Slave。

6.3 同步数据一致性问题

6.3.1 主从延迟问题

进行主从同步的内容是二进制日志，它是一个文件，在进行 网络传输 的过程中就一定会 存在主从延迟（比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的 数据 不一致性 问题。

主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。

6.3.2 解决一致性问题

读写分离情况下，解决主从同步中数据不一致的问题， 就是解决主从之间 数据复制方式 的问题，如果按照数据一致性 从弱到强 来进行划分，有以下 3 种复制方式。

方式一：异步复制

存在问题：
主库宕机，binlog还未同步到从库，导致主从数据不一致；这时从从库中选择一个作为新主，那么新主可能缺少原来服务器中已提交事务。

异步复制模式下数据一致性最弱。

方法二：半同步复制

半同步复制原理：
客户端提交COMMIT之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了binlog，并且写入到中继日志中，再返回给客户端。
这样做的好处是提高了数据的一致性，但相比于异步复制，至少多增加了一个网络连接的延迟，降低了主库写的效率。

方法三：组复制

组复制即使，简称MGR，基于Paxos协议的状态机复制。

首先我们将多个节点共同组成一个复制组，在 执行读写（RW）事务 的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对 只读（RO）事务 则不需要经过组内同意，直接 COMMIT 即可。

在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性。