MySQL体系结构
数据库由以下几个部分构成:
连接者：用于不同语言的代码程序和mysql进行交互
连接池：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求，它的处理流程是主线程接收连接，接收连接交由连接池处理；如下图所示
管理服务和工具组件：系统管理和控制工具，例如备份恢复、Mysql复制、集群等；
SQL接口：将SQL语句解析并且生成相应的对象。
查询解析器：将SQL对象交由解析器验证和解析，并生成语法树；
查询优化器：SQL语句执行前使用查询优化器进行优化；
缓冲组件：是一块内存区域，用来弥补磁盘速度较慢对数据库性能的影响；在数据库进行读取页操作，首先将从磁盘读到的页存放在缓冲池中，下一次再读相同的页时，首先判断该页是否在缓冲池中，若在缓冲池命中，直接读取；否则读取磁盘中的页，说明该页被LRU淘汰了；缓冲池中LRU采用最近最少使用算法来进行管理；缓冲池缓存的数据类型有：索引页、数据页、以及与存储引擎缓存相关的数据（比如innodb引擎：undo页、插入缓冲、自适应hash索引、innodb相关锁信息、数据字典信息等）；
数据库设计三范式
确保每列保持原子性；数据库表中的所有字段都是不可分解的原子值；
确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）；
确保每列都和主键直接相关，而不是间接相关；减少数据冗余
索引
InnoDB中数据的存储形式是B+树，每种索引其实表示的就是以对应的属性构建B+树，下边开始介绍索引的种类。
主键索：非空唯一索引，一个表只有一个主键索引，，在 innodb 中，主键索引的B+树包含表数据信息；
PRIMARY KEY(key)
唯一索引：不可以出现相同的值，可以有NULL值；
UNIQUE(key)
普通索引：允许出现相同的索引内容；
INDEX(key)-- ORKEY(key[,...])
组合索引：对表上的多个列进行索引
INDEX idx(key1,key2[,...]);UNIQUE(key1,key2[,...]);PRIMARY KEY(key1,key2[,...]);
关于主键：主键中包含数据信息，而通过其他索引构建出来的B+树并不包含新数据，而是辅助索引(聚集索引之外的都叫辅助索引)所对应的主键，如果需要查找实际的数据，仍然需要去主键查找，这一过程称为回标，所以可以知道，一个数据库中必须包含主键，如果没有主动设置主键，系统会默认第一个非空唯一索引为主键，如果没有非空唯一索引，则会自动生成一个六字节的_rowid作为主键。
索引实现
由于磁盘io的读写很慢，大概在10ms左右，所以在读写数据库的时候，越少次数的磁盘io所运行的时间越短，为了达到这个目的，我们引入了B+树，树高就是我们需要访问io的次数。下边我们来看看索引的实现。
innodb由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由64个连续页构成）；页的默认值为16k，是 innodb 磁盘管理的最小单位，B+树的一个节点的大小就是该页的值；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区；
一棵B+树：
树中，只有叶子节点树存储数据的，其他节点节点存储对应的键值，而且B+ 树中各个页之间是通过双向链表连接的。在查找的时候，我们通过二分查找找到对应的索引信息，如果需要跨页，则直接使用叶子节点的双向指针就可以。
最左匹配原则
对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配；查找时候，按照最左侧，依次向右进行索引，中间不能有值是空的，例如key顺序 id name sex，可以进行 id name查找(最左侧从左到右匹配)但是不可以进行id sex查找(跳过了name)由于优化器的存在，我们可以不按顺序，例如id sex name也是可以的。如果索引找不到的话，会遍历所有数据，找到对应值。
存在最左匹配的原因可以从B+树上找到解释，因为B+树是按联合索引构建的，如果不按建树的顺序查找，中间跳过了，索引会失效，最终会遍历所有数据。
覆盖索引
如果能从辅助索引中找到数据就不需要通过聚集索引查找，因为利用辅助索引构建出来的B+树，一般比聚集索引的树低。
索引失效
select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
索引字段参与运算，则索引失效；例如：from_unixtime(idx) = '2021-04-30';
索引字段发生隐式转换，则索引失效；例如：'1' 隐式转换为 1 ；
LIKE 模糊查询，通配符 % 开头，则索引失效；例如：select * from user where name like'%ark';
在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；
组合索引中，没使用第一列索引，索引失效
索引原则
查询频次较⾼且数据量⼤的表建⽴索引；索引选择使⽤频次较⾼，过滤效果好的列或者组合；
使⽤短索引；节点包含的信息多，较少磁盘io操作；
对于很长的动态字符串，考虑使用前缀索引；
对于组合索引，考虑最左侧匹配原则和覆盖索引；
尽量选择区分度⾼的列作为索引；该列的值相同的越少越好；
尽量扩展索引，在现有索引的基础上，添加复合索引；
不要 select * ；尽量只列出需要的列字段；
索引列，列尽量设置为非空；
事务
事务将数据库从一种一致性状态转换为另一种一致性状态(事务的特性之一，下文会提及)，事务由一条或多条sql语句构成，对应的sql语句：
--显示开启事务START TRANSACTION | BEGIN-- 提交事务，并使得已对数据库做的所有修改持久化COMMIT-- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改ROLLBACK-- 创建一个保存点，一个事务可以有多个保存点SAVEPOINT identifier-- 删除一个保存点RELEASE SAVEPOINT identifier-- 事务回滚到保存点ROLLBACK TO[SAVEPOINT] identifier
ACID特性
原子性(A.atomicity)
事务操作要么都做（提交），要么都不做（回滚）；事务是访问并更新数据库各种数据项的一个程序执行单元，是不可分割的工作单位；通过undolog来实现回滚操作。undolog记录的是事务每步具体操作，当回滚时，回放事务具体操作的逆运算；
持久性(D.durability)
事务提交后，事务DML操作将会持久化（写入redolog磁盘文件 哪一个页 页偏移值 具体数据）；即使发生宕机等故障，数据库也能将数据恢复。redolog记录的是物理日志；
隔离性(I.isolation)
事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，也就是事务提交前对其他事务都不可见；通过 MVCC 和 锁来实现；MVCC 时多版本并发控制，主要解决一致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读操作，从而实现高效并发读性能。锁用来处理并发 DML 操作；数据库中提供粒度锁的策略，针对表（聚集索引B+树）、页（聚集索引B+树叶子节点）、行（叶子节点当中某一段记录行）三种粒度加锁；
一致性（C.consistency）
一致性指事务将数据库从一种一致性状态转变为下一种一致性的状态，在事务执行前后，数据库完整性约束没有被破坏。例如：一个表的姓名是唯一键，如果一个事务对姓名进行修改，但是在事务提交或事务回滚后，表中的姓名变得不唯一了，这样就破坏了一致性；一致性由原子性、隔离性以及持久性共同来维护的。
并发异常
脏读
事务（A）可以读到另外一个事务（B）中未提交的数据；也就是事务A读到脏数据；在读写分离的场景下，可以将slave节点设置为 READ UNCOMMITTED；此时脏读不影响，在slave上查询并不需要特别精准的返回值。
图中SessionB的事务没提交，但是SessionA中第七步可以读到B中插入的事务，此时就是脏读。
不可重复读
事务（A) 可以读到另外一个事务（B）中提交的数据；通常发生在一个事务中两次读到的数据是不一样的情况；不可重复读在隔离级别 READ COMMITTED 存在。一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如Oracle、SQL Server）默认隔离级别就是READ COMMITTED；
SessionA中 第五步进行一次读操作，在SessionB中的提交事务后，A再次读取，此时读到了B中插入的数据，此时两次A中的读取数据不一样，即不可重复读。
不可重复读发生的场景时读--读，两次读取的数据不一样，而下边介绍的幻读则出现在读--写场景。
幻读
事务中一次读操作不能支撑接下来的业务逻辑；通常发生在一个事务中一次读判断接下来写操作失败的情况；例如：以name为唯一键的表，一个事务中查询 select * from t where name ='mark'; 不存在，接下来 insert into t(name) values ('mark'); 出现错误，此时另外一个事务也执行了 insert 操作；幻读在隔离级别 REPEATABLE READ 及以下存在；但是可以在REPEATABLE READ 级别下通过读加锁（使用next-key locking）解决；
下图在第五行加了共享锁，锁到对应的位置，事务B就无法插入，commit后，锁会释放
隔离级别
read uncommitted:读未提交；该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁；
read committed:读已提交；从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读；此时读取操作读取历史快照数据；该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据；
repeatableread:可重复读；该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据；
serializable:可串行化；该级别下给读加了共享锁；所以事务都是串行化的执行；此时隔离级别最严苛；
不同隔离级别下的异常发生
设置隔离级别与锁
-- 设置隔离级别SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 或者采用下面的方式设置隔离级别SET @@tx_isolation = 'REPEATABLE READ';SET @@global.tx_isolation = 'REPEATABLE READ';-- 查看全局隔离级别SELECT @@global.tx_isolation;-- 查看当前会话隔离级别SELECT @@session.tx_isolation;SELECT @@tx_isolation;-- 手动给读加 S 锁SELECT ... LOCK IN SHARE MODE;-- 手动给读加 X 锁SELECT ... FOR UPDATE;-- 查看当前锁信息SELECT * FROM information_schema.innodb_locks;
实现
锁类型：
锁的存在使得对共享资源的并发访问成为可能，在数据库中锁可以实现事务的隔离级别。共享锁和排他锁都是行级锁；MySQL当中事务采用的是粒度锁；针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁；意向共享锁和意向排他锁都是表级别的锁
共享锁(S)（行锁）
事务读操作加的锁；对某一行加锁；
在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁；
在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题；
在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC；
在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC；
排它锁(X)（行锁）
事务删除或更新加的锁；对某一行加锁；
在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁；
意向排它锁(IS)（表锁）
对一张表中某几行加的排他锁；加s锁后，自动加IS锁；
意向共享锁(IX)（表锁）
对一张表中某几行加的共享锁；加x锁后，自动加IX锁;
锁算法
Record Lock：记录锁，单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；全开区间；REPEATABLE READ级别及以上支持间隙锁；如果 REPEATABLE READ 修改 innodb_locks_unsafe_for_binlog = 0 ，那么隔离级别相当于退化为 READ COMMITTED；
Next-Key Lock：记录锁+间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间；
Insert Intention Lock：插入意向锁，insert操作的时候产生；在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突
AUTO-INC Lock：自增锁，是一种特殊的表级锁，发生在 AUTO_INCREMENT 约束下的插入操作；采用的一种特殊的表锁机制；完成对自增长值插入的SQL语句后立即释放；在大数据量的插入会影响插入性能，因为另一个事务中的插入会被阻塞；
加锁示例
示例1：
select * from t where id = 5 for update; lock in share mode-- id 为主键 Read committed 隔离级别-- 在主键 id = 5 行上加 X 锁-- id 是唯一索引 Read committed 隔离级别-- 在唯一索引id=5行上加X锁，在主键索引上对应行加X锁-- id 是非唯一索引 Read committed 隔离级别-- 在非唯一索引上所有id=5行加上X锁，对应的主键索引列加上X锁-- id 不是索引 Read committed 隔离级别-- 在聚集索引上扫描，所有行上加X锁，此处有个优化，不满足的行在加锁后，判断不满足即可释放锁-- id 为主键 repeatable read 隔离级别-- 在主键id=5行上加X锁-- id 是唯一索引 repeatable read 隔离级别-- 在唯一索引id=5行上加X锁，在主键索引上对应列加X锁-- id 是非唯一索引 repeatable read 隔离级别-- 在非唯一索引上查找id=5行，找到则加上X锁和GAP锁，然后对应的聚集索引加上X锁; 没有找到则加上GAP锁-- id 不是索引 repeatable read 隔离级别-- 在聚集索引上扫描，所有行加上X锁和GAP锁
示例2：
-- 在 RR 下-- 不加任何锁select .. from t;-- 扫描到任何索引行上加S锁（next-key lock） 在聚集索引上加X锁select...from t lock in share mode;-- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁select..from t for update;-- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁update..where conditiondelete from..where condition-- 如果是间隙插入，先添加 insert intention lock, 后在该行上添加X锁;-- 如果是递增插入，添加 auto-inc lock 或者 轻量级的互斥锁；insert into ...
MVCC
多版本并发控制；用来实现一致性的非锁定读；非锁定读是指不需要等待访问的行上X锁的释放；
在 read committed 和 repeatable read下，innodb使用MVCC；然后对于快照数据的定义不同；
在 read committed 隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据；而在repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本；
因为没有事务需要对历史的数据进行修改操作；所以读取快照数据并不需要上锁
redo
redo 日志用来实现事务的持久性；内存中包含 redo log buffer，磁盘中包含 redo log file；当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的commit操作完成才完成了事务的提交；
redo log 顺序写，记录的是对每个页的修改（页、页偏移量、以及修改的内容）；在数据库运行时不需要对 redo log 的文件进行读取操作；只有发生宕机的时候，才会拿redo log进行恢复；
undo
undo 日志用来帮助事务回滚以及MVCC的功能；存储在共享表空间中；undo 是逻辑日志，回滚时将数据库逻辑地恢复到原来的样子，根据 undo log 的记录，做之前的逆运算；比如事务中有insert 操作，那么执行 delete 操作；对于 update 操作执行相反的 update 操作；
同时 undo 日志记录行的版本信息，用于处理 MVCC 功能；
MySQL缓存方案
读写分离
之所以引入读写分离是因为在数据库的写入速度大于读取速度，但是实际项目中读操作远大于写操作，所以我们采用读写分离的技术，下图展示了读写而分离的流程
主从服务器之间需要同步数据，因此引入了主从复制的概念，我们先看一下主从复制的基本流程：
主库更新事件(update、 insert、 delete)通过io-thread写到binlog；
从库请求读取binlog，通过io-thread写⼊（write）从库本地 relay log（中继⽇志）；
从库通过sql-thread读取（read） relay log，并把更新事件在从库中执⾏（replay）⼀遍；
缓存方案
直接从磁盘读取数据太慢了，一次磁盘IO大概需要10ms，而读取内存在100ns就可以完成，效率相差10万呗，如果数据不放在磁盘中，而存储在内存，进行数据操作时效率会有很大的提升，所以产生了缓存方案，缓存数据库通常使用redis和memcached。它们所有数据都存储在内存当中，当然也可以将内存当中的数据持久化到磁盘当中；内存的数据和磁盘的数据是⼀⽐⼀的；mysql本身也带缓冲，但是他的缓冲层不受用户控制，所以实际项目中使用较多的是redis。
由于mysql的缓冲层不由⽤户来控制，也就是不能由⽤户来控制缓存具体数据；
访问磁盘的速度⽐较慢，尽量获取数据从内存中获取；
主要解决读的性能；因为写没必要优化，必须让数据正确的落盘；如果写性能出现问题，那
么请使⽤横向扩展集群⽅式来解决；
项⽬中需要存储的数据应该远⼤于内存的容量，同时需要进⾏数据统计分析，所以数据存储
获取的依据应该是关系型数据库；
缓存数据库可以存储⽤户⾃定义的热点数据；以下的讨论都是基于热点数据的同步问题；
存在的同步问题
有了缓冲层，那么我们缓冲层的数据是否能保证和磁盘中的一致呢，此处就设计到同步问题，mysql和缓存之间的数据对应一下集中关系：
mysql有，缓存⽆
mysql⽆，缓存有
都有，但数据不⼀致
都有，数据⼀致
都没有
其中后两种没有问题，对于前三种情况，需要明确我们获取数据的主要依据是mysql，所以mysql数据正确就万事⼤吉，只需要将mysql的数据正确同步到缓存数据库就可以了；同理，缓存有， mysql没有，这⽐较危险，此时我们可以认为该数据为脏数据；所以我们需要在同步策略中避免该情况发⽣；同时可能存在mysql和缓存都有数据，但是数据不⼀致，这种也需要在同步策略中避免；
解决
写：主要数据存储在mysql当中，所以先写mysql，如果mysql不可⽤，直接返回；mysql写成功后，再将数据同步给redis就⾏了。
读：先从redis当中获取数据，如果redis不可⽤，直接去mysql获取；如果redis有，直接返回；如果redis没有，转⽽向mysql请求，如果mysql没有，直接返回；如果MySQL有，则返回并将数据回写到redis当中；
一致性
强一致性：数据库和缓存 时刻都保持一致
先从redis当中获取数据，如果redis不可⽤，直接去mysql获取；如果redis有，直接返回；如果redis没有，转⽽向mysql请求，如果mysql没有，直接返回；如果MySQL有，则返回并将数据回
写到redis当中；
最终一致性：数据可和缓存最终一致
读写分离，主库将数据同步到从库，是需要时间，那么在同步期间，主从之间数据有差异；
这⾥有写两种⽅案：
第⼀种：直接写mysql，等待mysql同步数据到redis；
第⼆种：先写redis，设置key的过期时间为200ms（经验值），等待mysql回写redis，覆盖key，设置更⻓的过期时间；
缓存故障
缓存穿透(缓存和数据库都没有)
假设某个数据redis不存在， mysql也不存在，⽽且⼀直尝试读怎么办？缓存穿透，数据最终压⼒依然堆积在mysql，可能造成mysql不堪重负⽽崩溃;
解决：
1. 发现mysql不存在，将redis设置为 <key, nil> 设置过期时间 下次访问key的时候 不再访问mysql 容易造成redis缓存很多⽆效数据
2. 布隆过滤器，将mysql当中已经存在的key，写⼊布隆过滤器，不存在的直接pass掉
缓存击穿(缓存没有数据库有)
缓存击穿 某些数据redis没有，但是mysql有；此时当⼤量这类数据的并发请求，同样造成mysql过⼤；
解决：
1. 加锁
请求数据的时候获取锁，如果获取成功，则操作，获取失败，则休眠⼀段时间（200ms）再去获取；获取成功，则释放锁⾸先读redis，不存在，读mysql，存在，写redis key的锁整个流程⾛完，才让后⾯的服务器访问
2. 将很热的key，设置不过期
缓存雪崩 (缓存没有数据库有)
表示⼀段时间内，缓存集中失效(redis⽆ mysql 有)，导致请求全部⾛mysql，有可能搞垮数据库，使整个服务失效；
解决：
1. 如果因为缓存数据库宕机，造成所有数据涌向mysql；
采⽤⾼可⽤的集群⽅案，如哨兵模式、 cluster模式；
2. 如果因为设置了相同的过期时间，造成缓存集中失效；
设置随机过期值或者其他机制错开失效；
3. 如果因为系统重启的时候，造成缓存数据消失；重启时间短， redis开启持久化（过期信息也会持久化）就⾏了；重启时间⻓提前将热数据导⼊redis当中；
集群
分库分表
随着用户数据量增多，会导致数据库的性能下降的出现，这时候我么你需要采取分库分表从而达到提升数据库性能的目的：分库分表有两种方式，水平和垂直。
水平分表/库就是每一行的结构都不变
两图展示了水平分表与分库，以分表图为例子，将role base分为role base0 和 role base1，把一个表变成了两个，但是每一行的结构都没有变化。
这样做的优点是保证数据量维持在合理范围在一定程度上提升性能和负载均衡的能力；但是扩充难度大，拆分规则难选取，关联查询也变得困难。
垂直分表/库则是将当前每一行按关键字性质进行拆分
上图展示了垂直分表/库，以分表为例，将role按性质分为了role base、role equip、role hero三个表，每个表的结构都发生了变化，但是为了将各个表建立联系，每个表至少有一列数据是一样的。
垂直分表的优点是业务清晰，维护简单；但是当表的数据量太大查询效率依然低下且关联查询困难。
常见的一些集群
项目开始初期，大多使用单节点的数据可，但是随着用户数量爆发，需要从单节点扩展到多个节点，我们期望程序尽量不做改动。之和就需要使用我们的中间层。mysql中有自带的proxy，上游可接入若干个mysql-client，后端可连接若干个mysql-server。它使用mysql协议，任何使用mysql-proxy的下游无需修改任何代码，即可迁移至mysql-proxy上。
集群为了解决高可用性，数据库中的高可用性指的是如果数据库发生了宕机或者意外中断等故障，能尽快恢复数据库的可用性，尽可能的减少停机时间，保证业务不会因为数据库的故障而中断。
MMM
这种方式是对主从复制的优化，使用的是双主多从结构，虽然叫双主复制，但是同时刻只允许对一个主进行写入，另外一个主备提供部分读服务；
优点是可以较快速的进行主设备切换
缺点是无法完全保证一致性，双主复制有延时，切换后可能不是最新数据，使用VIP，必须部署在同一个IDC机房。
MHA
也是基于主从复制的优化，M（master）H（high）A（availability）是多主多从架构；提供更多的主节点，需要使用keepalived 来实现 VIP；每一个复制组有主节点，主备节点以及从节点构成；
优点是对故障的自动检测和转移、主库宕机时候能最大程度的保持数据的一致性。
缺点是需要引入读写分离相应逻辑，可以考虑 mysql-proxy 做到读的负载均衡；
Galera Cluster
主节点互为其他节点的从节点。原生的主从复制采用的是异步复制，而 Galera 采用的是多主同步复制，并针对同步复制过程中，会大概率出现的事务冲突和死锁进行优化；Galera的复制同步不是通过binlog实现，而是重写了wsrep api；
同步复制过程中，主库的单个事务更新需要在所有从库上同步更新，所以在主库提交事务时，集群中所有节点的数据保持一致。
它的优点是可对任意节点进行读写操作，某个节点宕机不影响其他节点的读写功能，而且不需要做故障切换；扩展性强，新增节点自动拉取在线节点的数据（新节点加入时，集群会选举一个节点专门为新节点提供数据），数据同步完成后，新节点才会对外提供服务，不需手动备份恢复；
缺点是响应会降低，因为实现了数据的强一致性；
