本篇介绍 MySQL 数据库，主要内容有：MySQL 常见的两种存储引擎（InnoDB 与 MyISAM）、数据库存储结构与索引原理以及数据库事务隔离级别、读写锁等。

存储引擎

MyISAM 与 InnoDB

区别：

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；（主键是能确定一条记录的唯一标识，外键用于与另一张表的关联）
InnoDB 是聚集索引，MyISAM 是 非聚集索引（堆表）。
- 聚集索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询（回表），先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
- 聚集索引的顺序就是数据的物理存储顺序，所以 insert 和 update 操作可能会导致数据重排，而导致性能下降。
- 而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；（MyISAM 查询效率更高，且支持全文索引）
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择：

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
系统奔溃后，MyISAM 恢复起来更困难，能否接受，不能接受就选 InnoDB；
MySQL 5.5 版本开始 InnoDB 已经成为 MySQL 的默认引擎（之前是 MyISAM ），说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用 InnoDB，至少不会差。
MyISAM 更适合 读密集 的表，而 InnoDB 更适合写密集的的表。在数据库做主从分离的情况下，经常选择 MyISAM 作为主库的存储引擎。一般来说，如果需要事务支持，并且有较高的并发读取频率（MyISAM的表锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了），InnoDB 是不错的选择。如果你的数据量很大（MyISAM 支持压缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM 是最好的选择。

索引原理

行格式与数据页

在了解 MySQL 的索引之前，必须得先了解下其数据的存储结构以及 B+ 树。

数据是以行为单位存储在 MySQL 的基本存储结构 数据页 之中的。Compact 行格式：

一行记录最大为 64KB
变长字段长度列表（2字节）会记录 varchar 类型数据的当前长度
NULL 值列表（1字节）用来存储那些为 NULL 的字段的位置
记录头则存储到下一条记录的指针
每行数据还存在隐藏列事务 ID、回滚指针列和 row_id
Dynamic 行格式把行中最长的一列放到 off-page，这样可以避免 Compact 那样把太多的大列值放到 B+ 树结点的情况

数据页中，行记录存储在 User Record 中，Free Space 用来存储新插入的行记录或是删除数据后留下的空间。Page Directory 为 页目录，当需要读取某行数据时会先在页目录中查找该行的指针。

行记录以单向链表的形式相连，各个数据页之间又记录着指向上一页 / 下一页的指针，相互间形成双向链表，此外还有 目录页 专门存储所有数据页的页号和页中最小主键的值，形成一个 B+ 树 的结构：

通常情况下，数据库会根据我们设置的 自增主键 按照顺序构建 B+ 树，当没有设置主键时则会使用唯一键或者生成隐藏列 row_id 作为主键。
建议设置自增主键 —— 不自增的话，插入新数据时会由于主键排序，需要调整、拆开链表或是数据页，有时候会把行记录挤到下一页去。自增的话只要新开一个页就可以了
B+ 树：相比 AVL 一个节点可以有多个数据，有效降低树的高度，减少查询次数；最底层数据以指针相互连接，在进行范围查找时只需确定两端便可以直接在行间遍历。
叶子节点：存储着行数据；非叶子节点：存数据页的最小主键（8 字节） + 指针（6 字节）
若一行数据大小为 1KB，那么高度为 2 的 B+ 树大约可存储 1.8w 条行记录，高度为 3 时大约可存储 2000w 条行记录。
目录页可以被缓存以加快查询效率。
至此，在索引数据的时候便可对主键实现 二分查找，先在目录页中定位目标数据所在的数据页，再根据数据页中的页目录定位具体的行记录位置。

索引原理

索引就像是在字典中查找某个词，在一本以 26 个英文字母排序的字典中查找 “mysql” 这个词，我们通常会先定位到以 m 开头的单词，缩小查找范围，在继续找以 my 开头的词，按照顺序依次查找便可实现较快的定位。因此，建立一个好的索引可以有效提高查询效率。

磁盘 IO 与预读

考虑到磁盘 IO 是非常昂贵的操作，计算机操作系统做了一些优化。从磁盘读取数据时，不光读取目标数据，而且会同时读取相邻的数据（一页约 4KB）到内存缓冲区中（局部预读性原理），若下次刚好读取相邻数据，则可以直接从内存中获取数据，加快 IO 效率。

最左前缀匹配原则

当需要在数据库中检索多个数据的时候，会执行类似于字符串的比较过程，依次对比每个字符的大小，所以检索时在左边的索引项具有较大的优先权。

数据库索引的几种方式：

主键索引：一般以自增主键为 B+ 树二分查找索引
联合索引：自建辅助索引，按照最左前缀匹配原则，索引结果为主键，再执行回表操作
全表扫描：执行范围查询 > 约 80% 时会执行全表扫描 / 没有索引

建索引的几大原则

最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。

= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。

尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是0.1以上，即平均1条扫描10条记录。

索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很简单，b+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

查询优化 - explain 命令

Explain 命令可以在建立索引后查看索引是否生效，具体用法和字段含义参考官网 explain output format。

事务与锁

事务特性与并发事务带来的问题

事务的四大特性 ACID：

原子性(A)：事务要么不执行，要么完全执行。（如果执行到一半机器宕机了，已执行的部分需要回滚回去）。
一致性(C)：各种约束条件，比如主键不能为空，参照完整性等。
隔离性(I)：只要事务不是串行的，就需要隔离（一般都是并行的，效率更高嘛），多次操作相互隔离。
持久性(D)：一旦事务提交了，数据不能丢失。

事务与事务并发地操作数据库的表记录，可能会导致下面几类问题：

问题	描述
脏读	一个事务 A 读取了另一个未提交的事务 B 的数据，但是事务 A 提交之前，事务 B 又回滚了，导致事务 A 刚刚读到的就是一个脏数据（RC 隔离级别可解决）。
不可重复读	同一个事务两次查询同一行记录，得到的结果不一样。因为另一个事务对该行记录进行了修改操作（行排它锁可解决）。
幻读	同一个事务两次查询某一范围，得到的记录数不一样，因为另一个事务在这个范围内进行了增加或删除操作（临键锁可解决）。
丢失更新	两个事务同时修改同一行记录，事务 A 的修改被后面的事务 B 覆盖了（需要自己加锁来解决）。

事务隔离级别与读写锁

InnoDB 一共有四个事务隔离级别，可解决上面的三个问题，最后一个问题只能在业务代码中解决。

名称	描述
READ_UNCOMMITTED	不使用，会出现幻读
READ_COMMITTED	防止了脏读，但会出现不可重复读、幻读
REPEATABLE_READ（默认）	解决了不可重复，但是还是会出现幻读
SERIALIZABLE	对读-写并发操作串行化，效率低会阻塞，一般不使用

这四种隔离级别是 SQL 的标准定义，不同的数据库会有不同的实现，特别需要注意的是：MySQL 在 REPEATABLE READ 隔离级别下，禁止了幻读的发生。
修改事务级别：set session transaction isolation level read committed

读取时事务隔离的实现：

读取的时候通过 MVCC Multi-Version Concurrentcy Control 多版本并发控制实现：

一个事物对数据修改时，会在 版本链 中更新数据内隐藏字段的事务 ID，commit 后会更新自己的事务 ID，回滚指针（指向提交前的那个版本），新提交的数据会更新在版本链的最前面，不论事务ID。
对于使用 READ UNCOMMITTED 隔离级别的事务来说，直接读取版本链中记录的最新版本就好了；对于使用 SERIALIZABLE 隔离级别的事务来说，使用加锁的方式来访问记录。对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，就需要用到我们上边说的版本链了，核心问题是：需要判断一下版本链中的哪个版本是当前事务可见的。
【读已提交】A 事务在读取的时候会生成一个 ReadView 里面有个重要的属性 m_ids ：表示当前系统中活跃的读写事务的事务 ID 列表（还没提交的事务列表） —> 将此列表与版本链进行比对，读取最新一次提交的数据。当事务 B 提交 200 号事务后，A 事务再去读取时，200 号事务就不在 m_ids 列表中了，也就可以读取 B 事务提交的更新数据了。
【可重复读】在 B 事务提交后，A 事务的 Read View 并不更新，所以还是可以获取之前获取的数据，有效的防止了不可重复读的出现。
MVCC 指 RC、RR 在执行查询语句时访问版本链的过程，使不同事务的读-写、写-读操作并发执行，从而提升系统性能。读已提交和可重复读这两个隔离级别的一个很大不同就是：生成 ReadView 的时机不同，读已提交在每一次进行普通 select 操作前都会生成一个 ReadView，而可重复读只在第一次进行普通 select 操作前生成一个 ReadView，之后的查询操作都重复使用这个 ReadView。

并发写入时的读写锁：

读锁：共享锁、Shared Locks、S锁 select...lock in share mode

写锁：排他锁、Exclusive Locks、X锁 select...for update

select：不加锁，无视读写锁、不会阻塞

	X 锁	S 锁
X 锁	冲突	冲突
S 锁	冲突	不冲突

写操作：

DELETE：删除一条数据时，先对记录加 X 锁（不让别人用），再执行删除操作。
INSERT：新插入一条记录时（其他事务读的话，没有必要加锁，因为根据 MVCC 它们读不到），会先加 “隐式锁” 来保护这条新插入的记录在本事务提交前不被别的事务访问到
UPDATE
1. 如果被更新的列，修改前后没有导致存储空间变化，那么会先给记录加 X 锁，再直接对记录进行修改。
2. 如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加 X锁，然后将记录删掉，再 insert 一条新记录。

隐式锁：一个事务插入一条记录后，还未提交，这条记录会保存本次事物 ID，而其他事务如果想对这个记录加锁会发现事物 ID 不对应，这时会产生 X 锁，所以相当于在插入一条记录时，隐式的给这条记录加了一把隐式 X 锁。

行锁

LOCK_REC_NOT_GAP：单个行记录上的锁。
LOCK_GAP：间隙锁，锁定一个范围，但不包括记录本身。GAP 锁的目的，是为了防止同一事物的两次当前读，出现幻读的情况。
LOCK_ORDINARY：锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。（是上面两个锁的合并版）

【读已提交】

主键 where a = 1 + for update — 只会锁住一行
唯一索引：和主键锁住效果一样
普通索引：对所有查出来的行加锁，但可以进行插入，然后出现幻读
没有索引：就算进行的是全表扫描，也只会对查出来的行加锁

【可重复读】

主键 / 唯一索引：与 RC 隔离级别一样。
普通索引：同个索引值，insert 插不进去 — 对间隙进行了加锁，避免了幻读
范围查找：会对所有满足范围的加锁，包括之后即将插入的。
没有索引：没有索引 where c = 1 的 value 是可以更改的，所以会对所有的行加锁，防止把其他行数据中的 c 被改成 1 ！！（而并不是表锁）