MYSQL 总结

三大范式（normal forms）

三大范式	解释
1nf	对属性的原子性
2nf	对记录的唯一性
3nf	对字段的冗余性

索引

按数据结构分类：B+tree索引、Hash索引、Full-text索引。
按物理存储分类：聚集索引、非聚集索引（也叫二级索引、辅助索引）。
按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）

explain

system>const>eq_ref>ref>range>index>All

联合索引

确定复合索引中列的正确顺序是一件棘手的事情。
以下是一些需要注意的事项:

1. 最左规则。一个索引(a, b)还可以用于需要索引(a)的查询，但不能用在需要索引(b)的查询。尝试以这样一种方式设计复合索引，使它们能够被尽可能多的查询复用。
2. 范围在右。一个索引(a, b)不能用于满足查询WHERE a BETWEEN x AND y AND B = ‘z’。或者更具体地说：复合索引的其余部分将不会在第一个范围条件之后使用。更通俗的说：a如果是个范围查询，那么b就无效了。
3. 高选择性的列在左边。想想如何尽快消除不需要的行。这通常还可以降低内存的占用，因为需要访问的索引页更少。
4. 更改索引顺序时要小心。混合ASC或DESC可能会影响可以使用多少复合索引。

第2条中同前面说明的，IN(1, 2, 3)这种IN里面是固定值的查询可以用到复合索引的全部列。

事务

MySQL 默认 RR
查看隔离级别

SELECT @@tx_isolation;

事务隔离级别
读未提交（read-uncommitted）
不可重复读（read-committed）
可重复读（repeatable-read）
串行化（serializable）

• 不可重复读针对 修改 和 删除
• 幻读针对 新增

ACID

ACID	解释	实现
原子性（Atomicity）	语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的	undo log
一致性（Consistency）	事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障
隔离性（Isolation）	保证事务执行尽可能不受其他事务影响	RR下主要基于锁机制（包含 next-key lock ）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）
持久性（Durability）	保证事务提交后不会因为宕机等原因导致数据丢失	redo log

next-key lock 是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

Read View

在访问某条记录时，判断记录的某个版本是否可见（除自己以外的活跃trx_id都不可见）

Multi-Version Concurrency Control

MVCC 模型在 MySQL 中的具体实现则是由 3 个隐式字段，undo 日志 ， Read View 等去完成的

• DB_TRX_ID:事务id，每进行一次事务操作，就会自增1。
• DB_ROLL_PTR:回滚指针，用于找到上一个版本的数据，结合undolog进行回滚。

刷盘策略

innodb_flush_log_at_trx_commit=1

• 0 每提交事务，log buffer不写入os buffer，不 fsync 到磁盘，而是每秒执行。
• 1 每提交事务，log buffer 写入os buffer，并 fsync 到磁盘。
• 2 每提交事务，log buffer 写入os buffer，不 fsync 到磁盘，而是每秒执行。
  
  innodb_flush_method=fdatasync
  

最大安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最大性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

参考

MySQL三大范式
MySQL记录锁、间隙锁、临键锁（Next-Key Locks）详解
redo与undo
MVCC多版本并发控制
explain
read view
刷盘策略
刷盘策略