自己对 MySQL 的理解
索引
索引失效的情况
如果 WHERE 条件中还有 OR 除了 OR 前后使用了索引列
如果 mysql 判断全表扫描比使用索引查询快，不会使用索引
执行 LIKE 模糊查询并以 % 开头
索引出现了隐式的类型转换。比如数据类型是 varchar，我们输入的是一个数值。那么不会使用索引
explain 执行计划
可以模拟优化器执行 SQL 查询语句，用来分析 sql 语句的性能
- Id: 标识符
- Select_type: 查询的类型
- type: 表的连接类型
- const：通过主键或唯一键查询，并且结果只有 1 行（也就是用等号查询）。因为仅有一行，所以优化器的其余部分可以将这一行中的列值视为常量。
- eq_ref：通常出现于两表关联查询时，使用主键或者非空唯一键关联，并且查询条件不是主键或唯一键的等号查询。
- ref：通过普通索引查询，并且使用的等号查询。
- range：索引的范围查找（>=、<、in 等）。
- index：全索引扫描。
- All：全表扫描
- possible_keys 预测用的索引
- key: 实际使用的索引
- key_len 使用索引的长度
- ref: 表之间的引用
- rows: 要检查的行数
索引的数据结构
常见的索引类型有 hash、b 树和 b + 树
Hash: 底层是 hash 表，查找时根据 key 获取对应的 hashcode，然后根据 hashcode 获取对应的数据行地址，根据地址拿到对应的数据
B 树：是一种多路搜索树，每个节点存储 key，指向 key 数据记录的地址和指向下一层节点的指针。查询时，从根节点向下查找，知道找到对应的 key
B + 树：是 B 树的一种变种。主要区别是 B + 树的非叶子节点只存储 key 和指向下一层节点的指针。B + 树的叶子节点之间通过指针来连接。构成一个有序链表，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。
那么为什么选用 b + 树作为索引呢？
不使用红黑树是因为红黑树的 I/O 操作比 B 树多得多。比如我们的数据量特别大。红黑树层数很高。从树的根节点每向下一层，就相当于一次 I/O 操作
不使用 hash 索引。对于单个数据，hash 是很快的。但是 hash 不支持范围查询。不支持索引值的排序操作以及不支持联合索引的最左匹配
B 树：因为 B 树相对于 B + 树，查询的时候会做局部中部遍历。也会有多余的 I/O 操作。并且相对于 B + 树的链表，B 树的非叶子节点多存储了一个指向 key 的地址元素。导致了层数变高，效率没有 B + 树
事务和锁
事务的隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 有 | 有 | 有 |
| 读已提交 | 无 | 有 | 有 |
| 可重复读 | 无 | 无 | 有 (InnoDB 无) |
| 串行化 | 无 | 无 | 无 |
脏读：一个事务对某一个数据进行了修改，但是并没有提交到数据库中。然后另外一个事务读取到了这个没提交的数据并使用。但这个数据我们并没有提交，所以是一个” 脏数据”，可能会产生一连串的后果
不可重复读：在一个事务中多次读取同一个数据时，结果不一致。比如事务 A 第一次读取到的成绩是 100. 然后这个时候事务 B 将成绩修改为 50. 那么下次事务 A 再次读取发现成绩变为了 50. 这个时候数据一致性就被破坏了。也就是不可重复读问题
幻读：和不可重复读很像，只不过幻读是针对增加操作的。在一个事务中使用相同的 SQL 查询，第二次读到了其他事务刚插入的行
不可重复读通过 MVCC（多版本并发控制）机制解决，幻读通过间隙锁解决
不可重复读注重数据的修改，而幻读注重于数据的插入
行锁、表锁和页锁
行锁: 锁粒度最细的一个锁。对当前行进行加锁。因为加锁粒度小，所以加锁的开销大。会出现死锁。但是发生冲突的概率最低，并发度也最高。
行锁有三种锁定方式：
记录锁（Record Locks）：锁的是单个记录
间隙锁（Gap Locks）：锁定一个范围，不包含记录本身
临键锁（Next-Key Locks）：锁定一个范围，包含记录本身，是以上两种锁的结合。不仅锁定记录，也锁定间隙
表锁: 锁粒度最粗的一种锁。 当整个表进行加锁。消耗的资源少。开销小，加锁快。不会出现死锁。但是发生冲突的概率高。并发度低。
页锁: 页锁是介于行锁和表锁中间的一种锁。锁的是相邻的一组数据。开销和加锁粒度介于表锁和行锁之间。会出现死锁。并发一般。
对于 MyISAM 存储引擎来说，只支持表级锁，而 InnoDB 则支持行级锁
间隙锁
mysql 在可重复读和 innoDB 的双重条件下。是会解决幻读的。因为 InnoDB 中使用了间隙锁来保证。
间隙锁只有在可重复读的情况下才会生效。
具体的工作原理：我们执行 SELECT * FROM A WHERE ID BETWEEN 10 AND 20，此时间隙锁锁住了 id 在 10-20 的记录，如果我现在插入一条 id 为 15 的新纪录则会堵塞，直到间隙锁所在的事务提交或回滚。
共享锁和排他锁
无论是行级锁还是表级锁都存在 2 种锁机制：共享锁和排他锁
共享锁 (S 锁)：也就是读锁，允许多个事务同时读取一个数据，但不能进行更新操作
排他锁 (X 锁)：也叫写锁或者独占锁。一次只允许一个事务获取锁并对其做读写操作。
意向锁
这个东西比较抽象。意向锁可以更容易地支持多粒度的锁。由数据引擎维护，用户无法手动操作。当我们手动的给数据行加共享锁或者排他锁的时候。InnoDB 引擎会先获取该数据行所在数据表的意向锁。
为什么要使用意向锁呢？假设我们对一个表加排他锁，就要去判断表里的记录有没有行锁（排他锁不兼容任何锁，也就是说不能和任何锁冲突），详细的关系如下
| 锁名 | X（排他锁） | S（共享锁） |
|---|---|---|
| X（排他锁） | ✖ | ✖ |
| S（共享锁） | ✖ | ✔ |
我们一行一行排查太慢。可以借助意向锁来实现。意向锁是表级锁，有两种：
意向共享锁（IS）：有意向加共享锁，加共享锁之前必须先获取该表的 IS 锁
意向共享锁（IX）：有意向加排他锁，加排他锁之前必须先获取该表的 IX 锁
值得一提的是，意向锁互相兼容。因为他们只是意想加锁，并不是真正意义上的加锁
分库分表
常用的工具：阿里巴巴的 TDDL (JDBC 直连) mycat (proxy 代理) sharding-JDBC (当当网)
拆分策略：
水平拆分：水平分表 水平分库分表
垂直拆分 垂直分表，垂直分库
水平分表：将一个表的数据分开存储。比如数据库 1 存储用户 id 结尾为偶数的 数据库 2 存储 id 为奇数的。每个表的结构是一致的。有 2 种解决方案。一种是范围拆分。也就是 1-10000 一个表 10001-20000 一个表 但这样会造成热点数据不均匀，访问压力还是不平衡。所以我们可以选择 hash 拆分，根据 id 取模决定存到哪个表中。数据是分散的存储。但是未来扩容比较麻烦，涉及到数据迁移 扩展能力差点。还有一些地理位置分片和时间分片
水平分库分表：将单个表的数据分到多个数据库中，继续分表
分库分表是个双刃剑，有利有弊👇
分布式事务的问题：因为我们涉及到了跨库跨表的业务操作。所以需要使用分布式事务的解决方案。我们使用 seata 来解决。在一个就是分布式的主键 ID 冲突问题。我们使用推特的 Snowwake 雪花 id 来保证。记得确保我们每台机器上的时间 (雪花 ID = 符号位 (总是 0) + 时间戳 (41bit)+ 机器码 + 流水号)
跨库 join 问题：我们在拆分的时候就尽量的将有关系的表放在一个库里面。使用全局表，每个数据库中都保存一份
垂直分表一般就是大表拆小表，以字段为依据 根据字段将不同的字段拆分到不同表中 每个表的结构都不一样 (比如用户表拆分为 专门登录的 和用户详细信息表 可以将表里面不常用的数据给他拆出来)
垂直分库：按照业务模块切分，将不同模块的表切分到不同的数据库中
一般来说数据量大我们选择水平拆分，拆出更多的小表。而表太多我们可以选择垂直拆分。可以按照模块进行切分到不同的数据库中
MVCC
MVCC 是多版本并发控制，可以做到读写之间不冲突。读取数据通过一种类似快照的方式将数据保存下来。之后的查询就去读取快照。这样读锁和写锁就不冲突了。不同事务看到自己特定版本的数据。只在 RC 和 RR 两个隔离级别下工作。包含两个组成部分 undolog 和 readview。
undolog 版本链中有 3 个隐藏列。
trx_id：行 id
db_roll_ptr trx_id：事务版本号
db_roll_ptr：指向上一条数据的指针
undo_log 版本链：MVCC 使用 undo_log 来确保隔离性。undo_log 主要用来记录数据被修改之前的日志，在表信息修改之前会把数据拷贝到 undo_log 中 当事务回滚时通过 undo_log 来还原。
ReadView：是一个数据结构，记录了当前事务的事务列表
包含 4 个字段
m_ids (当前活跃的事务编号集合)
min_trx_id (最小活跃事务编号)
max_trx_id (预分配事务编号，当前事务编号 + 1)
create_trx_id (创建者的事务编号)
快照读 (只有快照读才会使用 MVCC)。快照读就是 select 查询 SQL 语句 也就是 select
RC: 每一次查询都生成快照读 RR: 仅在第一次执行查询时生成快照读，后面复用第一次的快照读
当前读指的是 insert，update，delete。还有 select 后跟 for update， lock in share mode
存储引擎
InnoDB 和 MyISAM 的区别
- InnoDB 支持事务，MyISAM 不支持事务
- InnoDB 支持外键，MyISAM 不支持
- InnoDB 是聚集索引，MyISAM 是非聚集索引
- InnoDB 不支持全文索引，MyISAM 支持全文索引
- InnoDB 支持表、行锁，MyISAM 支持表级锁
存储引擎的类型
MyISAM 引擎的结构:
.frm 存储表定义
.myd 存储数据文件
.myi 存储索引文件
InnoDB 只有 ibd 文件 存放了索引和文件
其他
五大范式
- 第一范式：数据库表中每一列都是不可分割的原子数据
- 第二范式：表里的非主键字段，必须完全依赖主键，不能只依赖主键的一部分
- 第三范式：确保表里的每一列数据都和主键直接相关，不能间接相关。也就是非主键列互不依赖 如下图班主任性别和班主任年龄就是多余的，不符合第三范式
- 第四范式 (巴斯 - 科德范式): 禁止主键列和非主键列一对多关系不受约束
- 第五范式 (完美范式): 将表分割成尽可能小的块，为了排序在表中所有的冗余
MySQL 的 7 大日志系统
1. 重做日志 (read log)
用来保证 mysql 宕机情况下保存不完整的事务执行数据。记录的是事务执行后的状态
read log 流程分为 4 步
先将原始数据从磁盘读到内存里。修改数据的内存拷贝
生成一条重做日志并且写入到 read log buffer 记录的是数据被修改后的值
(read log buffer) 因为 IO 的的读取性能很低，所以引入 BufferPool 缓冲池来进行性能优化
当事务提交的时候。将 readlogbuffer 中的内容刷新到 read log file
就这样定期的将内存中修改的数据刷新到磁盘里
readlog 就是为了恢复由于宕机，将那些没有被刷入磁盘的数据持久化到数据库。
2. 回滚日志 (undo log)
保存数据的原子性，保存了事务发生之前的数据版本。比我我们执行了一条 delete 语句。那么 undolog 日志会生产出一条对应的 insert 语句。事务回滚时或者数据库崩溃时，可以利用 undo log 来进行回滚
比如用户读取某一行记录的时候。这个记录已经被其他事务占用了。我们不可能让用户这么一直等着。于是我们就可以通过 undolog 日志来读取之前的版本。也就是多版本并发控制器 MVCC。
undo log 的存储由 InnoDB 引擎实现。并使用 sement 分段的方式存储。undolog 日志的结构主要是三个字段。第一个是行 id。第二个是事务 id，第三个是指向上一条 undolog 日志的回滚指针。这样一旦并发上来。就形成了一条完整的回滚链。很方便就找到了对应记录的历史版本。
3. 二进制日志 (bin log)
主要用来实现主从复制，记录数据库的一些更新操作。
4. 错误日志 (error log)
记录错误的日志，帮助我们排查错误
5. 慢查询日志 (slow query log)
慢查询用来记录超过指定时间阈值的 SQL 语句
通过 **show variables like “% slow_query%”** 来查看是否开启
通过配置 slow_query_log = 1 开启慢查询日志
6. 一般查询日志 (general log)
记录普通的增删改查的信息
7. 中继日志 (relay log)
SQL 的执行过程
客户端发送查询语句给服务器，服务器先会现在缓存中查询是否存在该条 SQL 的结果，存在直接取出，不存在则对 sql 解析，语法检查和预处理，然后用优化器生成对应的执行计划。Mysql 的执行器根据优化器生成的执行计划，调用存储引擎的接口进行查询，服务器将查询结果返回给客户端
ON DUPLICATE KEY UPDATE 语句
ON DUPLICATE KEY UPDATE 用在插入语句的末尾，如果出现主键或唯一索引冲突，则将数据更新。
例如我现在有张表，id 是主键、name、age、sex 三个字段做联合唯一索引数据如下：
| id | name | age | sex | value |
|---|---|---|---|---|
| 1 | sora | 10 | 1 | 10 |
| 2 | sora | 10 | 2 | 10 |
| 3 | sora | 10 | 3 | 10 |
第一条 sql，我们测试如果主键冲突和唯一索引冲突都命中，但不是同一条记录，表会更新哪条数据
1 | INSERT INTO table ( id,name, age, sex) |
结果：
| id | name | age | sex | value |
|---|---|---|---|---|
| 1 | sora | 10 | 1 | 0 |
| 2 | sora | 10 | 2 | 10 |
| 3 | sora | 10 | 3 | 10 |
结论：同时命中主键冲突和唯一索引的情况下，只会更新主键冲突的那条记录。因为 ON DUPLICATE KEY UPDATE 的逻辑会优先根据主键判断。
第二条 sql，命中主键冲突但未命中联合索引冲突
1 | INSERT INTO table ( id,name, age, sex) |
结果：
| id | name | age | sex | value |
|---|---|---|---|---|
| 1 | sora | 10 | 1 | 0 |
| 2 | sora | 10 | 2 | 0 |
| 3 | sora | 10 | 3 | 10 |
结论：符合逻辑更新了仅更新了 id 为 2 的记录 value 值
第三条 sql，命中唯一索引冲突但未命中主键冲突
1 | INSERT INTO table ( id,name, age, sex) |
结果：
| id | name | age | sex | value |
|---|---|---|---|---|
| 1 | sora | 10 | 1 | 0 |
| 2 | sora | 10 | 2 | 99 |
| 3 | sora | 10 | 3 | 10 |
结论：仍然仅更新了 id 为 2 的记录 value 值，由此可以得知，在同时存在唯一索引和主键的情况下，只需要任意命中一个即可
第四条 sql，未命中唯一索引冲突且未命中主键冲突
1 | INSERT INTO table ( id,name, age, sex) |
结果：
| id | name | age | sex | value |
|---|---|---|---|---|
| 1 | sora | 10 | 1 | 0 |
| 2 | sora | 10 | 2 | 99 |
| 3 | sora | 10 | 3 | 10 |
| 10 | sora1 | 10 | 2 |
结论：因为没有命中主键和唯一索引，所以执行新增操作
微信- 支付宝
