mysql 逻辑架构

最上层不是 Mysql 独有的，比如连接处理，授权认证，安全 等等
第二层核心服务功能，包括查询解析，分析，优化，缓存以及所有内置函数，存储过程，触发器，视图等都在这层实现
第三层 存储引擎，存储引擎 API 包含几十个底层函数。

优化与执行：每个连接都会在 mysql 服务端产生一个线程（内部通过线程池管理线程），比如一个 select 语句进入，mysql 首先会在查询缓存中查找是否缓存了这个 select 的结果集，如果没有则继续执行 解析、优化、执行的过程；否则会之间从缓存中获取结果集。
MySQL 解析查询会创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。（用户可以通过特殊的关键字提示优化器，影响它的决策过程。也可以请求优化器解释优化过程的各个因素）
MySQL 原理与实践, 一共六篇

存储引擎

mysql 数据库支持插件式存储引擎。就是说支持多种存储引擎，甚至用户可以按照自己的需要定制和使用自己的存储引擎。
mysql5.5 之前默认存储引擎是 MyISAM，5.5 后改为 InnoDB。
MySQL 数据库各个版本的区别、以及不同引擎的适用环境

日志

执行 update 时，更新语句涉及到了数据的更改，所以必不可少的需要引入日志模块。
redo log 是 InnoDB 引擎特有的日志模块；binlog 是 Server 层自带的日志模块。

redo log 重做日志

在更新数据写入内存的同时，会先更新内存记录 redo log，然后顺序写入磁盘，而不是等内存占满之后再持久化，这样当数据库异常宕机之后，我们可以根据 redo log 重做日志来恢复数据，保证之前提交的数据不会丢失。

各种不同操作有不同的重做日志格式。

bin log 归档日志

binlog 是逻辑日志，记录本次修改的原始逻辑，说白了就是记录了修改数据的 SQL 语句。
通过 mysqlbinlog 可以解析查看 binlog 日志。

在今天中午 12 点的时候，发现上午 10 点执行了错误的 SQL 语句，想把数据库状态恢复到上午 10 点，错误语句执行之前。那么该怎么办呢？

数据恢复步骤如下：首先你要拿到最近一次的备份库
拿到最近备份库开始到出错时候的所有 binlog（binlog 日志保留时间可以自定义）使用 binlog 重放到错误发生之前。

undo log 回滚日志

主要用于事务的回滚。

索引

是一种优化查询的数据结构。

索引数据结构

常用的有哈希表、完全平衡二叉搜索树、B 树、B+ 树等数据结构。
一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考，哪个数据结构更适合从磁盘中读取数据，或者哪个数据结构能够提高磁盘的 IO 效率。
MySQL 中的索引是 B + 树实现的，MySQL 为何选择使用 B + 树？得看看其他数据结构实现的索引的特点：

哈希表

比如给 name 字段建立 hash 索引的情况下
首先数据库底层会计算 name 字段各行值对应的 hash 值作为数组下标，其中可能会有 hash 冲突，存储对应的那一行数据的地址。
当直接执行 select * from users where name = 'tom'; 的时候，数据库会计算 ’tom’ 的 hash 值，得到数组下标，然后直接从数据中取出数据并拿到锁对应的那一行数据的地址，进而在数据表文件中查询那一行数据。
但是当执行 select * from users where name > 'tom'; 时，索引将不再起作用。
哈希表并不是有序的，可以快速的精确查询，但是不支持范围查询。所以只适合做等值查询，区间查询的效率很低！好像字典里的索引去掉了排序。

完全平衡二叉搜索树

二叉搜索树的特点：每个节点的左儿子小于父节点，父节点又小于右儿子。
由于完全平衡二叉搜索树是有序的，所以支持范围查找。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。“N”取决于数据块的大小。

B 树

同样的数据，B 树表示的要比完全平衡二叉搜索树要 “ 矮 ”, 原因在于 B 树中的一个节点可以存储多个元素。

B+ 树

B+ 树是 B 树的升级版，只是把非叶子节点冗余一下，这么做的好处是 为了提高范围查找的效率。
同样的元素，B+ 树的表示要比 B 树要 “ 胖 ”，原因在于所有的叶子结点包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
非叶子节点也会冗余一份所有的中间节点元素，它们是最大（或最小）元素。

B+ 树作为索引的优势

提高查询索引时的磁盘 IO 效率，并且可以提高范围查询的效率，并且 B + 树里的元素也是有序的，叶子节点形成有序链表，便于范围查询。
参考的这篇博客

索引类型

主键索引

对于主健，oracle/sql server/mysql 等都会自动建立唯一索引。
主键索引是唯一索引的特殊类型。= 唯一索引 + not null

唯一索引

不允许两行具有相同的索引值。
例外情况是，如果该字段被设置为允许 NULL 值，则插入该字段的值可以包含多个 NULL 值。

它们的一些比较：

(1)对于主健 /unique constraint，oracle/sql server/mysql 等都会自动建立唯一索引；(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；(3)主健可作外健，唯一索引不可；(4)主健不可为空，唯一索引可；(5)主健也可是多个字段的组合；(6)主键与唯一索引不同的是：a. 有 not null 属性；b. 每个表只能有一个。(7)主键索引比普通索引得查询速度快。因为普通索引树上并没有完整得数据，而是先找到主键后，再到主键索引树上去获取所需得数据，这个操作被称为回表。当你没有设主键或者主键突然被删除时，会自动建立一个主键 rowid，保证回表等功能的正常运行。

普通索引

由关键字 KEY 或 INDEX 定义的索引。唯一任务是加快对数据的访问速度。
在多个列上建立索引, 这种索引叫做复合索引(组合索引)。https://www.cnblogs.com/zjdxr…

FULLTEXT 全文索引

用于在一篇文章中，检索文本信息的。

索引优化

覆盖索引

explain 语句 extra 列出现 using index
如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

最左前缀原则

区分度最高的放在最左边。
涉及到在建立复合索引的时候，如何安排索引内的字段顺序的问题。
如果我们创建了 (username,sex,age) 的复合索引，那么其实相当于创建了：
(username,sex,age)，(username,sex)、(username,age)、(username)四个索引，这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
https://www.cnblogs.com/jiqin…

索引下推

在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

自增主键的必要性

1. 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
2. 索引的维护的主要代价是由于有序性的维护引起的，如果每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂，这样就可以减少索引的维护代价。

放弃使用索引而进行全表扫描的情况

1. 在没有建索引的情况下，数据库查找某一条数据，就必须进行全表扫描了，对所有数据进行一次遍历，查找出符合条件的记录。
2.where 子句中对字段进行 null 值判断。
3.where 子句中使用!= 或 <> 操作符
4.where 子句中使用 or 来连接条件
5.in 和 not in
6. 使用非打头字母搜索
7. 在 where 子句中对字段进行表达式操作
8. 在 where 子句中对字段进行函数操作
9. 在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算
10. 复合索引情况下，使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
https://blog.csdn.net/qq_3631…

并发控制 Concurrency control

在多用户环境中，在同一时间可能会有多个用户更新相同的记录，这会产生冲突。这就是并发性问题。

典型的冲突有：
1. 丢失更新：一个事务的更新覆盖了其它事务的更新结果，就是所谓的更新丢失。例如：用户 A 把值从 6 改为 2，用户 B 把值从 2 改为 6，则用户 A 丢失了他的更新。
2. 脏读：当一个事务读取其它完成一半事务的记录时，就会发生脏读取。例如：用户 A,B 看到的值都是 6，用户 B 把值改为 2，用户 A 读到的值仍为 6。

为了解决这些并发带来的问题。我们需要引入并发控制机制。
mysql 采用读写锁来进行并发控制。
两种锁的思想——乐观锁和悲观锁。

悲观锁

指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态，比如表锁，行锁。独占锁、java 中 synchronized 就是悲观锁。
依靠数据库提供的锁机制实现。适用于数据争用严重 / 重试代价大的场景。
缺点：
在多线程竞争下，加锁、释放锁会导致比较多的上下文切换和调度延时，引起性能问题。
一个线程持有锁会导致其它所有需要此锁的线程挂起，对长事务而言，这样的开销往往无法承受。
如果一个优先级高的线程等待一个优先级低的线程释放锁会导致优先级倒置，引起性能风险。
参考悲观锁实践

乐观锁

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。即 冲突检测和数据更新 两步。乐观锁不能解决脏读的问题。ReadCommitted、REPEATABLE READ 隔离级别是通过 MVCC 实现乐观锁的，还有 hibernate 中 @Version 注解。

依靠数据版本记录机制实现。
可以通过使用时间戳（timestamp）字段也可以定义 version 字段（自增长的整数）作为版本标识, 当读取数据时，将版本标识字段的值一同读出，在更新提交的时候检查当前数据库中数据的当前版本标识和自己更新前取到的版本标识进行对比，如果一致则予以更新，否则就是版本冲突返回给用户。
适用于数据争用不严重 / 重试代价不大 / 需要相应速度快的场景。
缺点：在高并发的情况下，乐观锁并不适合。因为致数据库会有很多的更新失败，处理异常再次执行的情况。

乐观锁的实践

了解下 Compare and Swap(CAS)。
CAS 是项乐观锁技术，当多个线程尝试使用 CAS 同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。CAS 是一种非阻塞式的同步方式。

CAS 操作包含三个操作数 —— 内存位置（V）、预期原值（A）和新值 (B)。如果内存位置的值与预期原值相匹配，那么处理器会自动将该位置值更新为新值。否则，处理器不做任何操作。无论哪种情况，它都会在 CAS 指令之前返回该位置的值。（在 CAS 的一些特殊情况下将仅返回 CAS 是否成功，而不提取当前值。）CAS 有效地说明了“我认为位置 V 应该包含值 A；如果包含该值，则将 B 放到这个位置；否则，不要更改该位置，只告诉我这个位置现在的值即可。”这其实和乐观锁的冲突检查 + 数据更新的原理是一样的。
参考 java 对 CAS 的支持

锁的粒度

提高并发性的方式就是让锁定的对象更有选择性，只锁定部分数据，而不是所有的资源，这就是锁粒度要考虑的问题。

表锁 table-level locking

MySQL 数据库的 MyISAM 引擎支持表锁。
当你对一张表进行修改时，会锁死整张表，其他的请求需要在修改完成释放锁才能继续。

特点：开销小，加锁快; 不会出现死锁; 锁定粒度大，发生锁冲突的概率最高，并发度最低。
更适合于查询为主的场景。

MySQL 最基本的锁策略，开销最小的策略。
MyISAM 引擎中表锁分为表共享读锁 (Table Read Lock) 和表独占写锁(Table Write Lock)。
InnoDB 引擎中表锁分为意向共享锁（IS）和意向排他锁（IX）。意向锁是 InnoDB 自动加的，不需要用户干预。

表共享读锁 Table Read Lock

对于读操作，可以增加读锁，一旦数据表被加上读锁，其他请求可以对该表再次增加读锁，但是不能增加写锁 。（当一个请求在读数据时，其他请求也可以读，但是不能写，自己也不能写，因为一旦另外一个线程写了数据，就会导致当前线程读取到的数据不是最新的了。这就是不可重复读现象）
实例

表独占写锁 Table Write Lock

对于写操作，可以增加写锁，一旦数据表被加上写锁，这会阻塞其他用户对该表的所有读写操作。（当一个请求在写数据时，其他请求不能执行任何操作，因为在当前事务提交之前，其他的请求无法看到本次修改的内容。这有可能产生脏读、不可重复读和幻读）

读锁和写锁都是阻塞锁。读和写是串行的。同时请求的情况下，写进程先获得锁。
只有没有写锁时，其他读取用户才能获得读锁，读锁之间是不相互阻塞的。

操作方式

MyISAM 在执行查询语句 (SELECT) 前, 会自动给涉及的所有表加读锁, 在执行更新操作 (UPDATE、DELETE、INSERT 等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

如果用户想要显示的加锁可以使用以下命令：
锁定表：LOCK TABLES tbl_name {READ | WRITE},[tbl_name {READ | WRITE},…]
解锁表：UNLOCK TABLES
注意：当使用 LOCK TABLES 时, 不仅需要一次锁定用到的所有表, 而且, 同一个表在 SQL 语句中出现多少次, 就要通过与 SQL 语句中相同的别名锁定多少次, 否则也会出错!
参考资料

意向共享锁 IS

表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的 IS 锁

意向排他锁 IX

类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的 IX 锁。

行级锁 row-level locking

InnoDB 存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。
当你对一张表的某一行数据修改时，会锁死这一行数据，对表中其他的数据没影响。
行级锁可以最大程度地支持并发处理（最大锁开销），InnoDB 和 XtraDB，还有其他一些存储引擎中实现了行级锁，行级锁只在存储引擎层实现，而 MySQL 服务器层没有实现。

特点：开销大，加锁慢; 会出现死锁; 锁定粒度最小, 发生锁冲突的概率最低，并发度也最高。
适合于大量按索引条件并发更新少量不同的数据，同时又有并发查询的应用。

页级锁 page-level locking

开销和加锁时间界于表锁和行锁之间; 会出现死锁; 锁定粒度界于表锁和行锁之间，并发度一般。
BDB 存储引擎。

共享锁 shared locks

也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源, 互不干扰。但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
在查询语句后面增加LOCK IN SHARE MODE，Mysql 会对查询结果中的每行都加共享锁。

排它锁 exclusive locks

也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。
在查询语句后面增加FOR UPDATE，Mysql 会对查询结果中的每行都加排他锁。

死锁

1. 多个事务不同顺序锁定资源时，会产生死锁，
2. 多个事务同时锁定同一个资源，产生死锁。

InnoDB 目前处理死锁的方法是，将持有最小行级排他锁的事务进行回滚（相对比较简单的死锁回滚方法）
越复杂的系统，越能检测到死锁的循环依赖，并立即返回一个错误，否则会导致出现非常慢的查询。
参考资料 1
参考资料 2

事务

要么都做，要么都不做的。为了避免出现逻辑处理失败导致的脏数据等问题。
具体有这些问题：
1.脏读 :A 事务中读取到了 B 事务中尚未提交的数据。

2.不可重复读：同一个事务中两次读取的数据的内容不一致。其他事务对同一条数据 update 了（提交了），时间上的问题。

3.幻读：同一个事务中两次 count 的数量不一致。其他事务对该表 insert 或 delete 了（提交了）。

事务中数据的隔离级别

定义了事务中，数据库读写方面的控制范围。
1.Serializable 串行化
最严格、最安全的级别，可读，不可写。像 java 中的锁，相当于锁表，对于所有的 query，即使是查询，也会加上读锁，避免其他事务对数据的修改。操作数据必须等待另一个事务结束。
可以解决脏读、不可重复读和幻读，但事务串行执行，资源消耗最大，数据库系统的并发处理能力大大降低, 所以它不会被用到生产系统中。

2.REPEATABLE READ可重复读（mysql 默认）保证了在同一个事务中多次读取同样记录的结果是一致的。因为 只能读取在它开始之前已经提交的事务对数据库的修改，在它开始以后，所有其他事务对数据库的修改对它来说均不可见，避免了“脏读取”和“不可重复读”的情况，但当其他事务往表中新增数据时，有幻读的问题。 不过 mysql 的 innodb 引擎采用了多版本并发控制（MVCC）机制可以解决幻读问题。

mysql 环境做个实验：
1、session1 启动一个事务 start transaction;；
2、执行一条查询语句SELECT * FROM class WHERE teacher_id=1;，观察结果;
3、session2 启动一个事务，执行更新或删除teacher_id=1 的命令，然后执行 commit;，在 session1 中查看teacher_id=1 行，发现并没有被更新或删除。这里变体现了“可重复读”，不管别的事物修改或删除了什么，即便是提交了，在当前事务下，重复读取的结果是一样的。；
4、session2 执行一条插入语句，然后提交，在 session1 中 count 一下发现总条数并没有加 1，不是说可重复读有幻读问题吗？这里是 mysql 的 MVCC 机制解决了幻读问题。

3.READ COMMITTED 读取已提交（oracle 默认，开发里常用）
其他事务对数据库的修改，只要已经提交，其修改的结果就是可见的，与这两个事务开始的先后顺序无关。这种隔离等级避免了脏读，但多次读取的数据结果可能出现不一致的情况，即解决不可重复读和幻读问题。
可以修改数据库的事务隔离级别 set transaction isolation level read committed; 重复上面的实验，看看有没有不可重复读和幻读问题。

4Read Uncommitted 读未提交
查询可以读取到其他事务正在修改的数据，即使其他事务的修改还没有提交。这种隔离等级哪个问题都不能解决。

mysql 一些事务相关的命令

1. 查看当前会话隔离级别：select @@tx_isolation;
2. 查看系统当前隔离级别：select @@global.tx_isolation;
3. 设置当前会话隔离级别：set session transaction isolatin level repeatable read;
4. 设置系统当前隔离级别：set global transaction isolation level repeatable read;
5. 命令行，开始事务时：set autocommit=on; 或者 start transaction;
6. 让当前会话的事务自动提交（更新、插入操作后会自动提交）：set @@session.autocommit=1;
7. 设置手动提交事务：set autocommit = 0;
8. 查看当前事务 id：SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
表中可以查到正在执行的事务信息。

Multiversion Concurrency Control 多版本并发控制技术

它使得大部分支持行锁的事务引擎, 不再单纯的使用行锁来进行数据库的并发控制, 取而代之的是, 把数据库的行锁与行的多个版本结合起来, 只需要很小的开销, 就可以实现非锁定读, 从而大大提高数据库系统的并发性能。

原理

innodb 对每一行加上了两个隐含的列，一列存储行被更新的”时间”，另一列存储行被删除的”时间”。并不是绝对的时间，而是与时间对应的数据库系统的版本号，每当一个事务开始的时候，innodb 都会给这个事务分配一个递增的版本号，所以版本号也可以被认为是事务号 。对于每一个”查询”语句，innodb 都会把这个查询语句的版本号同这个查询语句对应的行的版本号进行对比，然后结合不同的事务隔离等级，来决定是否返回该行。
每一行数据都可能存在多个版本，那么这些行组合起来得到的结果集的版本就更是不计其数，这就是数据库多版本的由来。MVCC 就是通过事务发生的不同的时间点，与数据行的版本来进行对比，从而取回与事务开始的时间点相一致的数据，来实现非阻塞的一致读。

比如对于 select 语句，只有同时满足了下面两个条件的行，才会被返回:
1. 行的被修改版本号小于或者等于该事务号；
2. 行的被删除版本号要么没有被定义（说明该行没有被删除过），要么大于事务的版本号（说明该行是被该事务后面启动的事务删除的，repeatable read 隔离等级下，后开始的事务对数据的影响不应该被先开始的事务看见, 所以该行应该被返回）。
深入理解

比如 INSERT 语句，对新插入的行，行的更新版本被修改为该事务的事务号；
对于删除,innodb 直接把该行的被删除版本号设置为当前的事务号, 相当于标记为删除, 而不是实际删除；
在更新行的时候,innodb 会把原来的行复制一份到回滚段中, 并把当前的事务号作为该行的更新版本。

优缺点

读取数据的时候,innodb 几乎不用获得任何锁, 每个查询都通过版本检查, 只获得自己需要的数据版本, 从而大大提高了系统的并发度。

为了实现多版本，innodb 必须对每行增加相应的字段来存储版本信息，同时需要维护每一行的版本信息，而且在检索行的时候，需要进行版本的比较，因而会降低查询的效率;
innodb 还必须定期清理不再需要的行版本，及时回收空间，这也增加了一些开销。
另外，数据库执行了大事务情况下，数据库仅是频繁更新，没有插入新数据，也会导致表空间占用越来越大，因为 innodb 会把被修改数据的前映像存放到称为回滚段的公共表空间中，而且对于索引和表中的行的多个版本，如果 innodb 来不及 purge，或者这些行因为要提供一致读而不能被 purge，就会占用越来越多的空间, 甚至有可能短时间撑爆你的硬盘。所以应用程序中需要合理控制事务的大小！
优化

另外，由于 innodb 的 mvcc 策略的实施，char 数据类型相对于 varchar 类型几乎没有任何优势，反而 varchar 列可能节省更多的存储空间，建议使用 varchar 数据类型。？？？

MVCC 由于其实现原理, 只支持 read committed 和 repeatable read 隔离等级。

禁用 MVCC

MVCC 本身不支持 read uncommitted 等级，所以可以通过设置 transaction_isolation = read uncommitted 来禁用 MVCC。
但是任何改变 innodb 默认隔离等级的操作，都会起到 innodb_locks_unsafe_for_binlog=off 类似的效果，这会导致诸如 insert into t select * from t_src 之类的语句不再给源表 t_src 加锁，也不再使用 innodb 的间隙锁，从而产生幻读，直接导致 binlog 中记录的 sql 语句不能正确的串行化，从而主从数据库的数据不再一致，而且基于 binlog 的增量备份也不再有效，所以除非不需要记录 binlog，否则别这么做。当然我们可以这样做来优化从库的性能，因为从库不需要记录 binlog。

参考：https://blog.csdn.net/lemon89…