MySQL
什么是存储引擎
存储数据、建立索引、更新/查询数据等技术的实现方式
基于表而不是基于库
InnoDB
特点：事务、行级锁、外键
表空间文件: .ibd，存储表结构、数据、索引
逻辑存储结构：
- 表空间
- 段
- 区（1M）
- 页（索引页、数据页，16K）
- 行（最后一次操作的事务、行数据）
场景：对事务的完整性要求比较高、在并发条件下要求数据的一致性
MyISAM
特点：不支持事务、不支持外键、支持表锁不支持行锁、访问速度快，支持R树索引
表空间文件：
- .sdi: 表结构数据
- .MYD: 数据
- .MYI: 索引
场景：以读入和插入操作为主，如业务系统中的日志、电商中的足迹，但是都被Mogodb替代了
MEMORY
由于放在内存，仅能作为临时表或缓存使用
特点：内存存放、也hash索引
表空间文件: .sdi, 仅存储表结构数据
场景：临时表或者缓存，不能太大，但是都被Redis替代了
索引
高效获取数据的数据结构（有序）
优点：提高数据检索效率，降低数据库IO成本
缺点：降低了更新表（如插入、更新、删除）的效率
索引结构
B+树索引（大部分引擎都支持）
Hash索引（不支持范围查询以及排序，MEMORY中有，InnoDB自适应hash）
R树索引（空间索引，用于地理空间数据类型，MyISAM中有）
Full-text（全文索引，建立倒排索引快速匹配文档，ES中有应用，InnoDB、MyISAM都支持）
B树结构（需要存数据导致一页存储的指针变少，层级变高）
B+树结构（每个页存储一个节点，指针固定占用6字节，可以用来计算树高）
Hash索引结构
索引基本分类
主键索引（默认自动创建，仅一个）
唯一索引（UNIQUE关键字创建）
常规索引
全文索引（不需要了解，查找的是文本的关键词，不是索引中的值）
索引分类（按照存储形式）
聚集索引（叶子结点保存整个行数据，必须有，而且只有一个）
选取主键索引 or 第一个唯一索引 or 生成一个rowid
二级索引（叶子结点存储对应的主键，可以有多个，回表查询）
性能分析
SQL执行频率：看增删改查每一类SQL语句频次
慢查询日志：记录超过10秒的SQL语句, 默认没开
profile详情：每一条语句的执行耗时是多少以及耗时在哪里，默认关闭
explain执行计划：执行过程中是否用到索引、表的连接情况、表的连接顺序，使用EXPLAIN
索引使用规则
失效情况（whele部分）
最左前缀法则
范围查询(<、>右侧列会失效，>=、<=可以)导致右侧列索引失效
索引列运算，会导致索引失效
字符串不加引号，索引失效
模糊查询，头部模糊会导致索引失效，尾部模糊不会失效
or分割的条件，只有两侧都有索引才能走索引，否则索引失效
统计评估使用索引比全表慢，则不使用索引（比如value>=minVal的查询，整张表都满足）
SQL提示
- use index, 仅建议，不强制
- ignore index
- force index, 强制
高效情况（返回的字段）
使用覆盖索引（需要返回的列，在该索引中全部能找到，不需要回表查询）
注意：在二级索引中，data部分仅存id，但是索引节点上的索引列（包括联合索引）也可以作为返回值
前缀索引：应对长文本情况
联合索引（推荐，应考虑顺序，最左前缀法则）
SQL优化
插入数据
批量插入
开启事务
主键顺序插入
大批量数据用load
主键插入
避免页分裂，页合并（删除是假删除）
降低主键长度（避免二级索引效率过低）
顺序插入，选择自增主键
尽量不使用UUID或者其他自然主键（如身份证号）
避免对主键的修改
order by优化
通过有序索引直接返回有序数据，不需要额外排序
group by优化
添加索引
也满足最左前缀法则
limit优化
进行limit分页查询时，越往后，分页查询效率越低，因为需要对前面的数据进行排序
覆盖索引+子查询
count优化
MYISAM引擎把一个表的总行数存在磁盘，count(*)效率会很高（在没有whele等语句情况下）
InnoDB需要一行一行读出来然后累计计数
推荐用redis手动维护计数（插入+1，删除-1）
推荐count(*)，因为引擎做了优化
update
根据索引字段或者主键更新，因为没有索引的update会从行锁升级为表锁
锁
全局锁
场景：全库的逻辑备份
表级锁
表锁
表共享读锁
表独占写锁
元数据锁
访问数据时自动加上
在表上有活动事务的时候，不可以对元数据进行写入操作
为了避免DML与DDL冲突，保证读写的正确性
MDL读锁（共享）
对一张表进行增删改查时自动加上
MDL写锁（排他）
对表结构进行变更时自动加上
意向锁
避免DML在执行时，加的行锁与表锁的冲突
表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
意向共享锁
与表锁共享锁 (read)兼容，与表锁排他锁(write)互斥
意向排他锁
与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥
行级锁
在RR事务隔离级别，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
行锁
锁定单个行记录，RC、RR隔离级别支持
共享锁 LOCK IN SHARE MODE
排他锁 FOR UPDATE
场景
针对唯一索引进行检索时，自动优化为行锁
行锁针对索引加锁，没索引将升级为表锁
间隙锁
锁定间隙，防止产生幻读现象，RR隔离级别支持
场景
索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁
索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时， 退化为间隙锁
临键锁(next-key)
行锁和间隙锁的组合，锁住数据和间隙，RR隔离级别支持
场景
默认场景
索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止
InnoDB引擎的逻辑存储结构
表空间：.ibd文件。
段：数据段、索引段、回滚段。数据段就是B+树的叶子节点，索引段即非叶子节点。
区：每个区1M。
页：磁盘管理的最小单元，每个页大小16KB，一个区一共有64个连续的页。为了保证连续性，存储引擎每次从磁盘申请4-5个区
行：trx_id、Roll_pointer、col1、col2、col3...
隐藏列Trx_id: 对记录进行修改时，对应的事务id
隐藏列Roll_pointer：对记录进行修改时，指向旧的版本(在undo日志中)
InnoDB内存架构
缓冲池Buffer Pool：空闲页、干净页、脏页
更改缓冲区Change Buffer：如果数据页不在缓冲池，先将数据变更存(DML)到更改缓冲区，数据被读取时再合并到缓冲池中。
日志缓冲区Log Buffer：日志数据(redo log、undo log)，每次事务提交刷新/每秒刷新
自适应哈希索引Adaptive Hash Index：如果hash索引可以提升速度，则自动建立hash索引
磁盘结构
系统表空间
存放更改缓冲区change Buffer数据
每张表的单独表空间
每张表的表空间文件.bd
通用表空间
手动指定使用该空间，略
撤销表空间(Undo表空间)
自动创建两个默认的undo表空间文件，用于存储 undo log 日志
双写缓冲区
将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件，便于系统异常时恢复数据
Redo Log
重做日志，用来实现事务的持久性，用于保证异常时数据恢复
双日志文件循环写
后台线程
Master Thread: 负责调度其他线程、缓冲池中的数据异步刷新，刷新脏页到磁盘
IO Thread: 负责AIO请求的回调（读、写、日志缓冲区刷新、更改缓冲区刷新）
Purge Thread: 回收事务已经提交了的undo log
Page Cleaner Thread：协助刷新脏页到磁盘，减轻Master Thread工作压力
事务原理
原子性 | 一致性 | 持久性：redo log、undo log
隔离性：锁、MVCC
Redo log（持久性）: 当脏页刷新到磁盘错误时，方便恢复数据。（循环写）
0. 原理：WAL(Write-Ahead Logging)
1. 增删改数据
2. Buffer Pool中产生脏页
3. Reedolog buffer记录数据页变化
4. 事务提交，将Redolog buffer中的数据页变化刷新到磁盘中
undo log（原子性）：用于记录数据被修改前的信息，作用是提供回滚（系统中断、死锁）和 MVCC
undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为可能还用于MVCC
insert事务，产生的undo log日志只在回滚时需要，提交后可以立即删除
update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即删除
当前读 | 快照读
当前读
读取记录的最新版本，其他并发事务不能修改当前记录，会对读取的记录加锁。
lock in share mode
for update
update
insert
delete
快照读
简单的select（不加锁）就是快照读，读取的是记录数据的可见版本
读已提交：每次select都生成一个快照读
可重复读：开启事务后第一个select语句是快照读
串行化：快照读退化为当前读
MVCC
记录中的三个隐藏字段
TRX_ID：最近修改事务ID
ROLL_PTR：指向这条记录的上一个版本
ROW_ID：隐藏主键（如果表结构没有主键，会生成）
undolog版本连
不同事务或相同事务对同一条记录进行修改，会导致记录的undolog生成一条记录版本链条
读视图
核心字段
当前活跃事务ID集合
最小活跃事务ID
预分配事务ID（当前最大事务ID+1，因为是自增的）
创建者（读视图）的事务ID
版本链数据访问规则
当前事务为ID为版本链上的记录中的事务ID
其他参数是读视图里面的，在快照读的时候已经创建了
1. 当前事务ID == 创建者事务ID：可以访问该版本，数据是当前这个事务更改的
2. 当前事务ID < 最小活跃事务ID：可以访问该版本，说明数据已经提交了
3. 当前事务ID > 最大活跃事务ID：不可访问该版本，说明该事务是在ReadView生成后才开启的
4. 最小活跃事务ID <= 当前事务ID <= 最大活跃事务ID, 当前事务ID不在活跃事务ID集合中：可以访问，说明已经提交了
生成时机
读已提交：每一次执行快照读都会生成读视图
可重复读：仅在事务中第一次执行快照读时生成读视图，后续复用该读视图
日志
二进制日志(BINLOG)
记录了所有的DDL语句与DML语句。默认开启
- 灾难时的数据恢复
- 主从复制
查询日志
查询日志中记录了所有的操作语句，默认不开启
慢查询日志
所有执行时间超过参数（默认10秒）的操作语句
主从复制
将主库的BINLOG同步到从库，然后重新执行，从而使得从库和主库数据保持一致
优点：
- 主库出现问题，可以快速切换到从库提供服务
- 实现读写分离，降低主库的访问压力
- 可以在从库中执行备份，避免备份期间影响主库服务
分库分表
单数据库问题：
- IO瓶颈：磁盘IO、网络IO
- CPU瓶颈
分库分表的中心思想：
将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能 问题，从而达到提升数据库性能的目的。
拆分策略：
- 垂直拆分：垂直分库、垂直分表
- 水平拆分：水平分库、水平分表
实现技术：
- shardingJDBC：基于AOP原理，在应用程序中对SQL进行拦截，解析、改写、路由处理。
- MyCat：分库分表中间件，简单，性能不及前者
读写分离
主库提供写操作，从库提供读操作
