连接池组件
管理服务和工具 SQL接口 查询分析器 优化器 缓冲 插件式存储引擎
物理文件
InnoDB（默认引擎）
MyISAM
Maria
连接操作是一个连接进程和 MySQL 数据库实例进行通信。从程序设计的角度来说，本质上是进程通信，包括管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。
线程池：刷新内存池中的数据，保证缓冲池的内存存储的是最近的数据。将修改的文件刷新到磁盘文件中，同时保证在数据库发生异常的情况下 InnoDB能恢复到正常运行状态。
Master Thread
负责将数据异步的刷到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收等。
IO Thread
AIO 来处理写IO 请求。IO Thread负责这些IO请求的回调处理。
Purge Thread
事务提交之后，对已经使用并分配的 undo 页进行回收。
Page Cleaner Thread
脏页的刷新操作
V1.1
内部有许多的循环来执行操作，通过 thread sleep 的方式来实现。
每秒会将重做日志缓冲中的内容刷新到重做日志文件，即使事务没有被提交。因此再大的时候，提交的时间也很短。
每秒可能执行合并插入缓冲，如果前一秒的 IO 小于5次，也就是IO压力很小的时候，会执行这一操作。
每十秒的操作包括：合并至多5个插入缓冲、刷新日志文件、刷新100个或者10个脏页到磁盘、删除无用的Undo页等。
V1.2 之前
由于之前的脏页刷新、合并缓冲都执行了硬编码，限制了磁盘IO的性能。引入了 INNODB_IO_CAPACITY 来表示吞吐量，根据百分比进行控制。
V1.2
分离出 PURGE THREAD
按照页的方式进行管理。使用 checkpoint 的机制刷新回磁盘。缓存的数据页包括：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息。
缓冲池的管理：LRU 最近最少使用算法：最频繁使用的页在LRU列表的前端，最少使用的页在列表的尾端
InnoDB的LRU优化：增加了一个 midpoint 的位置，把列表分为了 oldList （3/8）和 new List（5/8）。新读取到的页，先放入到 midpoint 的位置，先加入 oldList。
读取旧页子表中的数据会让该页变新（年轻，young），并将其移动到缓冲池的头部（也就是新页子表的头部）。如果是因为用户查询读造成该页被读取，则该页会立即被标识为年轻，并直接插入到列表头部。如果该页因为read-ahead被读取，则首次读取该页并放入缓冲池时不会将该页放入新页列表头部，而是放入列表中点，需要再次读取才能使该页被标识为年轻状态。（该页可能一直没有被标识为年轻状态直到被淘汰）。
原因：有些操作如索引或数据的扫描操作，会访问表中的许多页，甚至是全部的页。避免这些SQL操作使得缓冲池中的页被刷新出去，丢失了真正的活跃的热点数据。
MySQL提供了配置参数innodb_old_blocks_time用来指定该页在放入缓冲池后第一次读之后一定时间内（时间窗口，单位毫秒,milliseconds）读取不会被标识为年轻，也就是不会被移动到列表头部。参数innodb_old_blocks_time的默认值是1000，增大这个参数将会造成更多的页会更快的从缓冲池中被淘汰。
一般情况下8MB，每一秒钟进行一次缓冲刷新到日志文件中。
Write Ahead Log:事务提交的时候，先写重做日志，再修改页。当宕机的时候，通过日志来进行恢复。满足持久性的要求。
Checkpoint 能够缩短数据库的恢复时间；缓冲池不够用的时候，将脏页刷新到磁盘；重做日志不可用的时候，刷新脏页。
具体的实现是通过LSN（log sequence number）来标记版本的，是一个8字节的数字。
数据表一般都有主键而且主键是自增长的，这时插入的索引都是连续的，也就是我们说的聚集索引，聚集索引的好处就是一般数据都是顺序存储的，如果你的sql读的是某一块连续的数据块，这样因为聚集索引的连续性，你不需要访问多个不同的数据页来访问数据，大大减少了IO，提升了查询速度。主键索引的插入也非常快，因为不需要离散的读取数据页。
但是一张表有多个非聚集的辅助索引。在进行插入操作的时候，数据页的存放还是按照主键进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的，这时候就需要离散地访问非聚集索引页。由于随机读取的存在而导致了插入操作性能下降。
注意：顺序与否不是绝对的，要取决于具体的业务数据，如购买时间可能会是顺序的，购买金额就不是了。
因此，InnoDB设计了 插入缓冲，对于非聚集索引的插入、更新操作，不是每一次直接插入到索引页中，而是先插入到内存中。如果该索引页在缓冲池中，直接插入；否则，先将其放入插入缓冲区中，再以一定的频率和索引页合并，这时，就可以将同一个索引页中的多个插入合并到一个IO操作中，大大提高写性能。
使用必须具备两个条件
插入缓冲主要带来如下两个坏处： 1）可能导致数据库宕机后实例恢复时间变长。如果应用程序执行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出现宕机，这时就有大量内存中的插入缓冲区数据没有合并至索引页中，导致实例恢复时间会很长。 2）在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2，这在实际应用中会带来一定的问题。
Insert Buffer 的实现
内部是一颗 B+ 树，全局维护，对所有的表的辅助索引进行缓冲。
非叶节点存放的是 查询的 search key，占用9个字节，包括space (表)、marker、offset（页偏移）。
叶节点存放的是 space、marker、offset、metedata、secondary index record。（13字节+数据）
Insert Buffer Bitmap 用来标记每个辅助索引页的可用空间，每个页可追踪16348个索引页，256个区。
合并操作发生的情况
应用部分写失效的问题，通过拷贝页的副本，当写入失效发生的时候，通过该页的副本进行还原，再进行重做，提升了数据页的可靠性。
doublewrite 分为两个部分，一部分是内存中的doublewrite buffer，另一部分是物理磁盘上的共享表空间。在对缓冲池的脏页进行刷新的时候，先把脏页的内容复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次顺序写入到共享表空间的物理磁盘上，然后调用 fsync函数，同步磁盘，避免缓冲写带来的问题。这个过程的写入是顺序的，开销不大。接下来再写入到各个表空间文件中，此时是离散的。
哈希的时间复杂度为 O(1)，B+的查找次数取决于B+的高度，一般为3~4层
自适应哈希索引：如果观察到建立哈希索引可以带来速度提升，则建立哈希索引 (数据库自优化)。
AHI 的要求：1. 对这个页的连续访问模式（查询条件）必须是一样的 2.以该模式访问 100 次 3. 页通过该模式访问 N 次，N=页中记录 * 1/16
同步 IO: 每进行一次 IO 操作，需要等待此次操作结束后才能继续接下来的操作。
异步 IO：发出一个 IO 请求后立即再发出另一个 IO 请求，当全部 IO 请求发送完毕后，等待所有 IO 操作的完成。
异步IO 能够提高磁盘性能：同时进行多个IO请求，方便 IO Merge，节省IO成本
Native AIO: InnoDB 1.1 之后，提供了内核级别 AIO 的支持。（libaio库、需要操作系统支持）
在 InnoDB 中， read ahead、脏页刷新、磁盘写入操作都由 AIO 完成
当刷新一个脏页的时候，InnoDB存储引擎会检测该页所在区的所有页，如果是脏页，那么就一起刷新。
这样通过AIO 可以进行 IO 合并，但是可能存在问题：1. 是不是可能将不怎么脏的页写入 2. 固态硬盘有着较高的 IOPS，还需要这个特性吗（建议不需要开启）
Mysql 实例的启动过程中对 InnoDB 存储引擎的处理过程。
表是关于特定实体的数据集合，是关系型数据库模型的核心。
根据主键顺序组织存放的表。如果在创建表的时候没有显式定义主键，存储引擎就会按照如下规则选择或创建主键：
从逻辑存储结构上看，InnoDB的数据都存放在一个空间中，称为表空间。
表空间由段、区、页（块）、行记录组成。
表空间是 InnoDB存储结构的最高层，所有的数据都存放在表空间中。默认有一个共享表空间 ibdata1，但是如果 innodb_file_per_table 启用之后，每张表内的数据（仅限数据、索引、插入缓冲Bitmap页）会存放在自己的一个表空间内，但是其他的一些回滚信息、插入缓冲索引、系统事务信息、二次写缓冲还是要存放在共享表空间中。
段是表空间的组成元素，常见的有数据段、索引段、回滚段。由于InnoDB 是索引组织的，所以数据即索引，索引即数据。数据段就是B+树的叶子节点。索引段就是B+树的非索引节点，非叶子节点。对段的管理由引擎完成，DBA没有必要对其进行控制和管理。
区是由连续页组成的空间，大小为1MB。默认情况下，一个页 16 KB， 则一个区中一共有 64 个连续的页。申请的过程是逐步的，先申请32个碎片页，不够的时候再申请64个。
页是磁盘管理的最小单位，默认大小为16KB，可以调整。常见的页有数据页、重做页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。
行是数据的存放单位。每个页存放7992行的记录。
行记录可以以不同的格式存在InnoDB中，行格式分别是Compact、Redundant、Dynamic和Compressed行格 式。
VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用 ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用：
解读：1. varchar类型的列总和不超过 65535 个字节，注意不用编码的字符占用字节数不同。
2. 65535字节除了列本身的数据之外，还包括: 变长字段的长度值（2字节），NULL值标识（1字节）
记录中的数据太多产生的溢出
一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这 样就可能出现一个页存放不了一条记录。
在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分 数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当 然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。
InnoDB 存储引擎表是索引组织的，B+Tree的结构，每个页中应该至少有两条行记录，否则失去了Tree的意义，变成链表了，因此，当一个页只能放下一个行记录的时候，就会把行数据放到溢出页中。
这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处 存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外， Compressed行格式会采用压缩算法对页面进行压缩。
B+树索引：分为聚集索引和辅助索引，都是高度平衡的，叶子结点存放所有的数据，但是不同在于叶子节点存放的是否是一整行的信息。
InnoDB 存储引擎表是索引组织表：表中数据按照主键顺序存放。
聚集索引：按照每张表的主键构造一棵B+树，叶子节点存放整张表的行记录，叶子节点存放数据页。
在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。
聚集索引的好处就是 ：对于主键的排序查找和范围查找速度非常快
聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别 的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。
辅助索引的叶子节点并不包含行记录的全部数据，包括键值和一个书签（指示哪里可以找到行数据）。在Idb中，这个书签对应的就是聚集索引键。
通过辅助索引查找数据时：先遍历辅助索引并找到叶节点找到指针获取主键索引的主键，然后通过主键索引找到对应的页从而找到一个完整的行记录。注：每执行一次查询就是一次IO，比如 辅助索引树高度为3，聚集索引树高度为2，则通过辅助索引查询数据时就要进行3+2次逻辑IO最终得到一个数据页。
二级索引与聚簇索引有几处不同：
以多个列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。
能针对多个列的查询条件，并且第二个键值做了排序处理
从辅助索引中就可以得到查询的记录，不需要查询聚集索引中的记录。
在访问表中很少一部分的时候使用索引才有意义。对于性别、地区、类型这些低选择性的字段，添加索引时没有必要的。Cardinality就是一个反映选择性的值，它是索引中不重复记录数量的预估值。
更新C值的时机和方法： 1. 表中1/16的数据发生变化 2. 发生变化的次数 大于 20亿次的时候。具体方法：取得树中索引叶子节点的数量，记为A；通过采样的方式随机获取8个叶子节点，统计每个页中不同记录的个数，即 P1...P8; 利用平均值进行预估。
优化器选择是否使用索引的标准：能否缩小查询的结果集，能否提高查询效率
哈希表 映射函数 碰撞 冲突机制
InnoDB的哈希算法：链表方式、除法散列
B+树索引对于范围查找，包含关系类的查找都不能够解决，只能针对前缀进行查找。
全文检索是将存储与数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。
倒排索引
全文检索使用倒排索引来实现，在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。通常利用关联数组来实现，拥有两种表现形式：
倒排索引的数据存放在辅助表上，为了提高并行性能，共有6张辅助表，存放于磁盘上。
为了进一步提高全文检索的性能，使用了 FTS Index Cache 全文检索索引缓存
FTS Index Cache：红黑树结构，根据（word，list）进行排序。（类似于Insert Buffer，但是数据结构不同）
我们把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务。
A ：整个过程要么做，要么不做；事务是不可分割的最小工作单位。如果事务中的任何语句操作失败，应该把成功执行的语句撤回，防止引起数据库状态的变化。
C ：事务将数据库从一种状态转变为下一种一致的状态（状态包括如数据库的完整性约束）。
I ：每个读写事务的对象对其他事务的操作对象能相互分离。
D：事务一旦提交，结果是永久性的。即使发生宕机，也能把数据进行恢复。
开始事务 BEGIN / START TRANSACTION
提交事务 COMMIT ，在开始之后执行多条语句，commit会提交上述语句
中止事务 ROLLBACK 。正常事务执行失败，会自动回滚
自动提交 一个语句是一个独立的事务，事务会自动提交。关闭自动提交的方式有： 显式的开启一个事务，或者把系统变量 set autocommit=off
隐式提交 特殊语句引起事务提交。CREATE ALERT DROP BEGIN START TRANSACTION LOAD DATA / ANALYZE TABLE /CACHE INDEX/ CHECK TABLE/ FLUSH/ LOAD INDEX INTO CACHE /
保存点 在事务对应的数据库语句中打点，可以指定回滚到保存点的位置。
set seeion transaction isolation level read uncommitted
select %%tx_isolation
未提交读 READ UNCOMMITED
已提交读 READ COMMITED （绝大多数默认）
可重复读 REPEATABLE READ （InnoDB 默认级别）
串行化 SERIALIZABLE
对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了
对于使用 SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。
对于使用READ COMMITTED和 REPEATABLE READ隔离级别的事务来说，就需要用到版本链，核心问题就是：需要判断一下 版本链中的哪个版本是当前事务可见的。
ReadView 包括：
有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：
数据库系统使用锁支持对共享资源进行并发访问，保证数据的一致性和完整性。InnoDB 提供了一致性的非锁定读、行级锁支持。行级锁没有额外相关的开销，并可以同时得到并发性和一致性。
lock：目标对象是事务，锁定数据库中的对象、有死锁机制。如 行锁、表锁、意向锁
latch：轻量级的锁，对象是线程，保护内存数据结构，通过顺序加锁保证不发生死锁。如 读写锁、互斥量
行级锁：
SELECT
Delete：X锁
Insert：隐式锁来保护新插入的记录在提交前不被访问到
UPDATE
意向锁：将锁定的对象分为多个层次，表明事务希望在更细粒度（行锁、表锁）上进行加锁。
在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的 S锁或者X锁的。
锁升级：将当前锁的粒度提升，如把1000个行锁升级为1个页锁，防止系统使用过多资源来维护锁，提升效率。但是IDB的情况不同，根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。不管锁住一个页中的一个还是多个记录，开销通常是一致的。
在对某个表执行ALTER TABLE、DROP TABLE这些DDL语句时，其他事务对这个表执行SELECT、INSERT、 DELETE、UPDATE的语句会发生阻塞，或者，某个事务对某个表执行SELECT、INSERT、DELETE、UPDATE语 句时，其他事务对这个表执行DDL语句也会发生阻塞。这个过程是通过使用的元数据锁（英文名：Metadata Locks，简称MDL）来实现的，并不是使用的表级别的S锁和X锁。
IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。
InnoDB 中的意向锁作为表级别的锁，是一种为了在一个事务中揭示下一行将被请求的锁类型。
由于InnoDB支持的是行级别的锁，因为意向锁不会阻塞除全表扫描以外的任何请求。
意向锁之间彼此兼容，意向锁跟行级锁的兼容与原来的行级锁兼容情况相同。（S-IS 兼容，其他都不兼容）
一致性非锁定读：存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。
一致性锁定读:
用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。
SELECT ... FOR UPDATE #对读取的行记录加一个X锁
SELECT ... LOCK IN SHARE MODE #对读取的行记录加一个S锁
当事务提交之后，锁也就释放了。因此要加上 BEGIN, START TRANSACTION 或者 SET AUTOCOMMIT=0
对于唯一键值的锁定，NKL会被降级为RL，仅存在于查询所有的唯一索引列。
问题一：脏读
脏数据：事务对缓冲池中行记录的修改，还没有被提交，读取脏数据会违反隔离性
脏页：缓冲池中被修改的页，还没有刷新到磁盘中，源于内存和磁盘的异步，不影响一致性。
脏读：不同的事务下，当前事务可以读到另外事务未提交的数据。
解决：提升隔离级别
问题二：不可重复读 (幻象读)
一个事务内多次读取同一数据集合，期间被第二个事务访问同一数据集合，并做了修改操作。此后第一个事务再次读到的数据可能会不一样。一个事务两次读到的数据是不一样的情况，就是不可重复读。违反了一致性的要求。
这一问题是可以接受的，如果需要避免则可以通过 Next-key Lock算法：对于索引的扫描，不仅锁住索引，还锁住了这些索引覆盖的范围。在这个范围内的插入都是不允许的。
问题三：丢失更新
一个事务的更新操作会被另一个事务的更新操作所覆盖，导致数据的不一致。
解决：事务串行化
多个事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。
解决思路：发现后回滚 -->拒绝等待 超时 死锁检测 (等待图)
在每个事务请求锁并发生等待的时候都会判断是否存在回路，若存在，则回滚undo量最小的事务。
原文：https://www.cnblogs.com/suntorlearning/p/12405495.html