自己对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 |
结论：因为没有命中主键和唯一索引，所以执行新增操作
微信- 支付宝
