SQL执行过程

1. 连接

   进程间通信方式 TCP/IP
   命名管道和共享内存
   Unix域套接字文件（socket文件）

2. 解析与优化

   查询缓存
   编译 词法分析 语法分析 语义分析 谓词下推 动态编译 生成执行计划
   优化 向量优化 Join优化 执行代价 成本计算 执行计划优化 规则优化 查询重写

3. 存储引擎

4. 常用字符集及最大长度

5. 数据引擎 InnoDB

   1. 数据页 InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB
   2. 行格式（记录格式） 行格式，分别是Compact、Redundant、Dynamic和Compressed行格式

6. 索引

   因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以 不得不 依次遍历所有的数据页
   实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点

   记录头信息里的record_type属性 取值 意思：
   0：普通的用户记录
   1：目录项记录
   2：最小记录
   3：最大记录

   聚簇索引（二级索引）

   1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
      1. 页内的记录是按照主键的大小顺序排成一个单向链表。
      2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
      3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
   2. B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列)

B+树

B+树与聚簇索引有几处不同:

1. 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：
   1. 页内的记录是按照c2列的大小顺序排成一个单向链表。
   2. 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
   3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。
2. B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。
3. 目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

回表
当查询B+树获取记录的主键后，必须再次通过聚簇索引获取完整的用户记录 这个过程称为回表
联合索引
同时以多个列的大小作为排序规则，也就是同时为多个列联合建立索引

1. B+树索引在空间和时间上都有代价，尽量减少索引
2. B+树索引适用于下边这些情况：
3. 全值匹配
4. 匹配左边的列
5. 匹配范围值
6. 精确匹配某一列并范围匹配另外一列
7. 用于排序
8. 用于分组
9. 在使用索引时需要注意下边这些事项：
10. 只为用于搜索、排序或分组的列创建索引
11. 为列的基数大的列创建索引
12. 索引列的类型尽量小
13. 可以只对字符串值的前缀建立索引
14. 只有索引列在比较表达式中单独出现才可以适用索引
15. 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
16. 定位并删除表中的重复和冗余索引
17. 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

独立表空间结构

区（extent）的概念
表空间被划分为许多连续的区，每个区默认由64个页组成，每256个区划分为一组，每个组的最开始的几个页面类型是固定,个区就是在物理位置上连续的64个页,降随机I/O为顺序I/O
段（segment）的概念
一个索引会生成2个段，一个叶子节点段，一个非叶子节点段
区的分类
分为4种类型：

1. 空闲的区：现在还没有用到这个区中的任何页面。
2. 有剩余空间的碎片区：表示碎片区中还有可用的页面。
3. 没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。
4. 附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。

InnoDB统计信息

1. InnoDB以表为单位来收集统计数据，这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据。

2. innodb_stats_persistent控制着使用永久性统计数据还是非永久性统计数据；innodb_stats_persistent_sample_pages控制着永久性统计数据的采样页面数量；innodb_stats_transient_sample_pages控制着非永久性统计数据的采样页面数量；innodb_stats_auto_recalc控制着是否自动重新计算统计数据。

3. 针对某个具体的表，在创建和修改表时通过指定STATS_PERSISTENT、STATS_AUTO_RECALC、STATS_SAMPLE_PAGES的值来控制相关统计数据属性。
4. innodb_stats_method决定着在统计某个索引列不重复值的数量时如何对待NULL值。

缓存LRU
InnoDB的Buffer Pool采用LRU的策略处理缓存加载
为降低随机预读与大规模全表扫描 造成缓存失效淘汰问题

1. LRU缓存 拆分为 yong区（热数据） 和 old区（冷数据）
2. 在对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部
   3.降低young区的缓存移动 某个缓存页对应的节点在young区域的1/4中，再次访问该缓存页时也不会将其移动到LRU链表头部
   目的 尽量高效的提高 Buffer Pool 的缓存命中率。

脏页数据的落盘
后台有专门的线程每隔一段时间负责把脏页刷新到磁盘
1.从LRU链表的冷数据中刷新一部分页面到磁盘
2.从flush链表中刷新一部分页面到磁盘

事务
事务的4个原则：

一致性（Consistency）
原子性（Atomicity）
隔离性（Isolation）
持久性（Durability）

事务的5个状态：

活动的（active）事务处于执行过程
部分提交的（partially committed） 未进行落盘 持久化
失败的（failed）执行异常|手动停止后的状态
中止的（aborted）执行过程中被终止的状态 需要回滚操作
提交的（committed）执行成功，顺利落盘

只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算是结束

开始事务
BEGIN/START TRANSACTION

READ ONLY：标识当前事务是一个只读事务， 属于该事务的数据库操作只能读取数据，而不能修改数据。
READ WRITE：标识当前事务是一个读写事务，属于该事务的数据库操作既可以读取数据，也可以修改数据
WITH CONSISTENT SNAPSHOT：启动一致性读

默认情况下，读写模式

ROLLBACK（回滚事务）

支持事务的存储引擎（InnoDB和NDB）
默认 自动提交 SET autocommit = OFF;关闭自动提交
一些语句会自动进行隐式的事务 如ddl，modify table，load data。。。

savepoint （保存点)
保存点（英文：savepoint）,在事务对应的数据库语句，调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点

redo|undo日志
会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来
判断某些redo日志占用的磁盘空间是否可以覆盖的依据就是它对应的脏页是否已经刷新到磁盘里
为了实现事务的原子性，InnoDB存储引擎在实际进行增、删、改一条记录时，都需要先把对应的undo日志记下来
通过记录的roll_pointer指向对应undo日志

事务隔离级别

事务并发读写的问题

1. 脏写（Dirty Write）
   一个事务修改了另一个未提交事务修改过的数据
2. 脏读（Dirty Read）
   一个事务读到了另一个未提交事务修改过的数据
3. 不可重复读（Non-Repeatable Read）
   一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值
4. 幻读（Phantom）
   一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来
   注意：幻读是再次读取 出现之前未出现的记录；若删除记录，再次读取记录减少，标记为 不可重复读

SQL标准中的四种隔离级别

脏写 > 脏读 > 不可重复读 > 幻读
隔离级别：

1. READ UNCOMMITTED：未提交读。
2. READ COMMITTED：已提交读。
3. REPEATABLE READ：可重复读。
4. SERIALIZABLE：可串行化。

1. READ UNCOMMITTED隔离级别下，可能发生脏读、不可重复读和幻读问题。
   1.READ COMMITTED隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
   1.REPEATABLE READ隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
   1.SERIALIZABLE隔离级别下，各种问题都不可以发生
   MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的

设置事务隔离级别
默认隔离级别为REPEATABLE READ

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {level};
level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

MVCC原理（Multi-Version Concurrency Control ，多版本并发控制）
使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程

聚簇索引中隐藏列
1. trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。
2. roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，
然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

版本链
对一条记录多次更新，每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表。
版本链的头节点就是当前记录最新的值
ReadView
ReadView包含4个信息

1. m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
2. min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
3. max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
4. creator_trx_id：表示生成该ReadView的事务的事务id。
   生成ReadView的时机
   READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView
   REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView

解决并发事务带来问题的两种基本方式

• 读-读情况：即并发事务相继读取相同的记录。
• 写-写情况：即并发事务相继对相同的记录做出改动。
• 读-写或写-读情况：也就是一个事务进行读取操作，另一个进行改动操作。

一致性读（Consistent Reads）
事务利用MVCC进行的读取操作称之为一致性读/一致性无锁读/快照读(无锁状态)

锁定读（Locking Reads）
共享锁（S锁）和独占锁（X锁）

多粒度锁
行锁 表锁

给表加S锁：
如果一个事务给表加了S锁：
别的事务可以继续获得该表的S锁
别的事务可以继续获得该表中的某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中的某些记录的X锁
给表加X锁
该事务要独占这个表，那么：
别的事务不可以继续获得该表的S锁
别的事务不可以继续获得该表中的某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中的某些记录的X锁

意向共享锁IS和意向独占锁IX

意向共享锁，英文名：Intention Shared Lock，简称IS锁。
当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

意向独占锁，英文名：Intention Exclusive Lock，简称IX锁。
当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。