了解MySQL一些内部的技术实现

MySQL索引原理

索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500页是目录，它当然效率低，目录是要占纸张的,从而索引是要占磁盘空间的。

磁盘预读原理

计算机中计算和处理数据都是靠CPU，也就是中央处理器，在计算机中一般分为数个层次：依次为

寄存器：比如64位(16个寄存器)，是CPU的工作台，物理存放在CPU内所以不需要IO
一级缓存L1 4×64KB
二级缓存L2 4×256KB
三级缓存L3 8MB
内存 4GB
磁盘 1TB

CPU工作时所用的数据或者地址先从一级缓存里面找，找不到就从二级缓存里面找，依次类推。假如CPU到磁盘才有，那么这个数据就会存入内存，再存入三级缓存、二级缓存、一级缓存，最后存入寄存器，CPU再进行计算，这些模块读写数据的速度在内存中是非常快的，主要的处理时间消耗在和磁盘的I/O

磁盘读取数据依靠机械操作，物理磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动(各个磁盘须同时转动)。磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不动，磁盘转动，但磁臂可以前后动，用于读取不同磁道上的数据。磁道就是以盘片为中心划分出来的一系列同心环。磁道又划分为一个个小段，叫扇区，是磁盘的最小存储单元

磁盘读取时，系统将数据逻辑地址传给磁盘，磁盘的控制电路会解析出物理地址（哪个磁道，哪个扇区），于是磁头需要前后移动到相应的磁道——寻道，消耗的时间为磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；磁盘旋转将对应的扇区转到磁头下（磁头找到对应磁道的对应扇区），消耗的时间也就是旋转延迟，就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计，那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，为了尽量减少I/O操作，计算机系统一般采取预读的方式，预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。

计算机系统是分页读取和存储的，一般一页为4KB（8个扇区），每次读取和存取的最小单元为一页，而**磁盘预读时通常会读取页的整倍数。**当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），所以即使只需要读取一个字节，磁盘也会读取一页的数据

IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段类型要尽量的小，比如int占4字节，要比bigint8字节少一半。

索引的数据结构：B+树

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），索引是基于数据表创建的，对创建索引的列的值记录对应的地址，并且把这些值存储在一个数据结构中用于提高查询效率，它们包含着对数据表里所有记录的引用指针

InnoDB的数据文件本身就是索引文件，MyISAM的索引文件仅仅保存数据记录的地址，MyISAM索引文件和数据文件是分离的；默认数据与索引文件位置: /var/lib/mysql

MyISAM引擎的文件：.myd存储data，表数据文件； .myi存储index索引数据

InnoDB系统表空间文件：ibdata1、ibdata2等存储InnoDB系统信息和用户数据库表数据和索引，所有表共用
InnoDB单表表空间文件：.ibd，每个表使用一个表空间文件，存放用户数据库表数据和索引

InnoDB一个page的默认大小是 16 k(4kb(磁盘一页的大小) + 12kb(预读三个页面) = 16kb)，可以通过innodb_page_size设置；由于是Btree组织，要求叶子节点上一个page至少包含两条记录（否则就退化链表了），所以一个记录最多不能超过 8 k，又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过 4 k；

在Innodb中一张表对应一个聚集索引，而聚集索引元数据中指定了root page的页号，因此Innodb引擎可以根据页号和页大小计算出索引B+树root page的准确地址，从而对整个表数据进行操作，每个页都有一个对应的磁盘地址，也可以叫页号，因为表空间的数据文件会被划分成大小相等的页，所以知道页号，再根据文件的初始位置，就可以计算出页在磁盘中的准确地址

索引IO的每一页都会存储key和指针，指针很显然会存储指向的page页号，如果是B树则key还会存储对应data，所以查询首先找到root页，从对应的指针去IO下一个page，依次找到最终的数据

hash索引：只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

全文索引：用于MyISAM和InnoDB引擎，使用普通索引只匹配文本前几个字符，但是要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引，5.7之后使用ngram插件可以支持中文

B-Tree：B-Tree不是B减树，类似1-1，B+Tree是B树的一个变种，B树是一个有序数组+平衡多叉树，关键字集合分布在整颗树中，任何一个关键字出现且只出现在一个结点中，搜索有可能在非叶子结点结束，其搜索性能等价于在关键字全集内做一次二分查找，性能不稳定

B树的每个节点可以存储多个关键字及对应的行数据，B树的查询相较于平衡二叉树的查询，因为预读的原因主要发生在内存中；红黑树就是一个平衡二叉树，通过对任何一条从根到叶子的简单路径上各个节点的颜色进行约束，确保没有一条路径会比其他路径长2倍，因而是近似平衡的；红黑树每个节点下只有两个子节点，而硬盘IO时是按簇读取的，两个节点中的值可能不够填满簇导致每次IO的浪费，此时红黑树的高度会大于B+树，导致IO次数增加

以InnoDB的B树为例：

查找过程(B-tree)：以查询15为例，首先从表元数据中得到磁盘page编号，IO后把获取的root page磁盘块1加载到内存，在内存中用二分查找(折半查找)确定15在17之前，锁定磁盘块1的P1指针，通过磁盘块1的P1指针的磁盘地址(page页号)把磁盘块2由磁盘加载到内存，发生第二次IO，15在12之后锁定磁盘块2的p3指针.通过指针加载磁盘块7到内存，发生第三次IO，同时内存中做二分查找找到15，结束查询，总计三次IO.

浅蓝色的块是一个磁盘块，每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块

B+树：是一个有序数组链表+平衡多叉树，所有关键字都在叶子结点出现，非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储，B+树的分支结点仅仅存储着关键字信息和子节点的指针（磁盘块的偏移量），也就是说内部结点仅仅包含着索引信息，每个节点不保存数据，数据都保存在叶子节点，查询性能稳定

聚簇索引：就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中

MyISAM和InnoDB都默认使用B+Tree，在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，而且叶节点的data域存放的是数据记录的地址

聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页；非聚集索引，叶节点存储的是主键索引，然后再通过主键索引来找到一个完整的行记录

主键索引也就是聚集索引，可以不用再次查询，因为主键索引与数据放在一起；普通索引需要再次查询主键索引之后再得到数据；从存储角度来说，根节点都只存储Key键值，Index索引值，非聚集索引叶子节点存储的是Index索引值和主键指针，而聚簇索引的叶子节点包括Key键值，Index索引值，Data数据；

如果建表时不指定主键，数据库会拒绝建表的语句执行，一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，整个表就变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

非聚簇索引：常规索引就是非聚集索引，又叫二级索引

索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引，因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到‘表’的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。

回表：在InnoDB中主键就是聚簇索引，其叶子节点保存着数据，而普通索引叶子节点存储的是所有索引的数据，这就是回表；如果没有定义主键，那么该表第一个非空唯一索引就是聚簇索引，如果也没有非空唯一索引，那么内部会生产一个隐藏的索引；但myisam引擎中，主键就不是聚簇索引，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复

覆盖索引：有一种例外可以不使用聚集索引就能查询出所需要的数据，当为字段建立索引以后， 字段中的内容会被同步到索引之中， 如果为一个索引指定多个字段(建立复合索引)， 那么这多个字段的内容都会被同步至索引之中，例如 select name from table where sex=‘男’，原先执行流程就是先通过非聚集索引找到sex='男’的所有数据的主键ID，然后通过主键id去执行聚集索引查找最终结果；

那么覆盖索引的方式就是创建name和sex的组合索引

ALTER TABLE `table` ADD INDEX `table_sex_name_index`(`name`, `sex`) USING BTREE;

执行流程为：通过非聚集索引找到sex='男’的叶节点的数据，此时叶节点会存在所有索引的数据，即包括了name和sex字段的数据，因此直接返回name即可

使用覆盖索引的方式查询，对于非聚簇索引也就是二级索引，需要找到对应的主键，主键再通过聚簇索引找，而覆盖索引，也可以一次找到数据，将查询的数据列和条件列建立索引就叫覆盖索引

联合索引的原理：之所以会有最左原则，是因为联合索引的B+Tree是按照联合索引关键字的顺序进行排序的，对于单列索引就是单列排序之后根据键值二分查找进行数据匹配，而联合索引则类似order by col1,col2,col3进行排序之后才作为键值，最终通过联合键值找到主键或者直接覆盖索引

order by原理

给order by 字段增加索引，利用索引的有序性获取有序数据，还是一样如果不是主键则根据排序字段查询到主键，再通过主键获取Select的数据放入sort_buffer中

整个的排序动作， 如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成，如果排序数据量太大，内存放不下，则利用磁盘临时文件辅助排序，所以提高排序效率可以设置sort_buffer_size大小

SET GLOBAL sort_buffer_size = 1024*1024;
在排序时候，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer；
依次从排序索引找到主键索引对应的查询结果存入sort_buffer；
对 sort_buffer 中的数据按照排序字段做快速排序；

InnoDB引擎原理

innodb的整个体系架构就是由多个内存块组成的缓冲池及多个后台线程构成，缓冲池缓存磁盘数据，后台线程保证缓存池和磁盘数据的一致性（读取、刷新），并保证数据异常宕机时能恢复到正常状态

一条SQL进入MySQL服务器，会依次经过连接池模块（进行鉴权，生成线程），查询缓存模块（是否被缓存过），SQL接口模块（简单的语法校验），查询解析模块，优化器模块（生成语法树），然后再进入innodb存储引擎。进入innodb后，首先会判断该SQL涉及到的页是否存在于缓存中，如果不存在则从磁盘读取相应索引及数据页加载至缓存。如果是select语句，读取数据(使用一致性非锁定读)，并将查询结果返回至服务器层。如果是DML语句，读取到相关页，先试图给这个SQL涉及到的记录加锁。加锁成功后，先写undo 页，逻辑地记录这些记录修改前的状态。然后再修改相关记录，这些操作会同步物理地记录至redo log buffer。如果涉及及非唯一辅助索引的更新，还需要使用insert buffer。事务提交时，会启用内部分布式事务，先将SQL语句记录到binlog中，再根据系统设置刷新redo log buffer至redo log，保证binlog与redo log的一致性。提交后，事务会释放对这些记录所加的锁，并将这些修改的记录所在的页放入innodb的flush list中，等待被page cleaner thread刷新到磁盘。这个事务产生的undo page如果没有被其它事务引用(insert的undo page不会被其它事务引用)，就会被放入history list中，等待被purge线程回收

缓冲池

Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲，主要分为三个部分

- innodb_buffer_pool：包含数据、索引、insert buffer ,adaptive hash index,lock信息及数据字典
- redo log buffer：用来缓存重做日志
- additional memory pool：用来缓存LRU链表、等待、锁等数据结构

Innodb_buffer_pool：当读取数据时，就会先从缓存中查看是否数据的页（page）存在，不存在的话才去磁盘上检索，查到后缓存到这个pool里。同理，插入、修改、删除也是先操作缓存里数据，之后再以一定频率更新到磁盘上，通过Checkpoint机制控制刷盘

默认pool的大小是128M，可以通过show variables like ‘innodb_buffer_pool%’，建议pool的空间设置为物理内存的50%-75%，在mysql5.7.5之后，可以在mysql不重启的情况下动态修改pool的size，设置的pool的size超过了1G的话，应该再修改一下Innodb_buffer_pool_instances

innodb_buffer_pool_size=134217728

查看已经被占用的和空闲的：
show global status like '%innodb_buffer_pool_pages%';

缓冲区LRU淘汰算法：Least Recently Used最近最少使用，当pool的大小不够用就会根据LRU算法来淘汰老的页面，最频繁使用的页在LRU列表的前端，最少使用的页在LRU列表的尾端，淘汰的话，就首先释放尾端的页

insert buffer：buffer_pool的一部分，用来做insert操作时的缓存在Innodb中，主键是行唯一标识，如果插入顺序是按照主键递增进行插入的不需要磁盘的随机读取，找到了页就能插；然而表上有多个二级索引那么当插入时，对于那个二级索引树，就不是顺序的，它需要根据自己的索引列进行排序，这就需要随机读取了。二级索引越多，那么插入就会越慢，因为要寻找的树更多了。所以，Innodb设计了Insert Buffer，对于非聚簇索引的插入、更新操作，不是每次都插入到索引页中，而是先判断该二级索引页是否在缓冲池中，若在，就直接插入，若不在，则先插入一个insert buffer里，再以一定的频率进行真正的插入到二级索引的操作，这时就可以聚合多个操作，一起去插入提高性能

在写密集的情况下，内存会占有很大，默认最大可以占用1/2的Innodb_buffer_pool的空间，可以通过IBUF_POOL_SIZE_PER_MAX_SIZE来进行控制；新版的Innodb引入了Change buffer，其实就是把insert、update、delete都进行缓冲，所有DML操作都会先进缓冲区，进行逻辑操作，后面才会真正落地，通过参数Innodb_change_buffering开始查看修改各种buffer的选项

//默认是所有操作都入buffer，可选值有inserts\deletes\purges\changes\all\none
show variables like 'innodb_change_buffering';
//innodb_change_buffer_max_size为25表示最多使用25%的缓冲池空间
show variables like 'innodb_change_buffer_max_size';

insert buffer的数据结构是一棵B+ tree，全局就一棵B+树，负责对所有的表的二级索引进行插入缓存，该tree存放在共享表空间，默认ibdata1中。通过独立表空间的ibd文件试图恢复表中数据时，可能会有CHECK TABLE错误，就是因为该表的二级索引中的数据可能还在insert buffer里，没有刷新到自己的表空间。这时，可以通过repair table来重建表上的所有二级索引。

insert buffer的b+ tree的根节点存放的是查询的search key(哪个表，哪个页面，所在页的偏移量)，当发起了一次插入、更新时，首先判断要操作的数据的页（是二级索引的页）是否已经在Innodb_buffer_pool里了，如果在，直接去修改pool里该页的数据即可，如果不在就需要构造search key，加上被插入、修改的数据，插入到insert buffer的叶节点中

redo log buffer：重做日志缓冲，InnoDB在缓冲池中变更数据时，会首先将相关变更写入重做日志缓冲中，这块区域持有将要写入redo log的数据；redo log buffer大小是通过设置innodb_log_buffer_size实现的，redo log buffer会周期性的flush到磁盘的log file中；一个大的redo log buffer允许大事务在commit之前不写入磁盘的redo log。因此，如果有事务需要update,insert,delete许多记录，增加log buffer来节省磁盘I/O

innodb_flush_log_at_trx_commit选项控制redo log buffer的内容如何写入log file，innodb_flush_log_at_time选项控制redo log flush的频率

**additional memory pool：**额外内存池，用来缓存LRU List、Free List和Flush List这三个队列

数据库中的缓冲池是通过LRU算法来管理，但当数据库刚启动时，LRU了列表是空的，这时页都存放在Free列表中，当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中，否则根据LRU算法，淘汰LRU队列末尾的页，将该内存空间分配给新的页；LRU列表中的页被修改后，即缓冲池中的页和磁盘上的页的数据产生了不一致，通过CHECKPOINT机制将脏页重新刷回磁盘，而FLUSH列表的页即为脏页列表，脏页既存在于LRU列表中，也存在与Flush列表中，LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

后台线程

InnoDB存储引擎是多线程模型，因此后台有多个不同的线程，负责处理不同的任务

master thread：主要负责缓冲池中数据的异步刷新到磁盘，保证数据一致性，包括脏页刷新，合并插入缓冲（INSERT BUFFER）、UNDO页的回收等，master线程每秒或每10秒回进行一次merge insert buffer的操作

MasterThread
{

    //每1秒一次操作涉及的行为
    PerSecondOperation()
    {
        1，刷新redo日志缓冲到磁盘，不管是否提交（总是）

        2，合并插入缓冲（可能），根据一秒之内发生的IO次数：
           if(过去1秒钟的ios<5%innodb_io_capacity）
           {    
               执行合并5%innodb_io_capacity个page的插入缓冲的操作
           }

        3，刷新缓冲池中的脏页到磁盘（可能）：
           if(buf_get_modified_ratio_pct>buf_max_dirty_pages_pct)
                刷新脏页个数：100%*innodb_io_capacity
           if(buf_get_modified_ratio_pct<buf_max_dirty_pages_pct)
                刷新脏页个数：10%*innodb_io_capacity
　　　　　4. checkpoint
　　　　　　
        5，如果当前没有用户活动，切换到background模式

    }
    //每10秒一次操作涉及的行为
    TenSecondOperation()
    {
    
        1，刷新缓冲池中的脏页到磁盘（可能）：
           if(buf_get_modified_ratio_pct>buf_max_dirty_pages_pct)
                刷新脏页个数：100%*innodb_io_capacity
           if(buf_get_modified_ratio_pct<buf_max_dirty_pages_pct)
                刷新脏页个数：10%*innodb_io_capacity

        2，合并插入缓冲（可能），根据过去一秒之内发生的IO次数：
            if(ios<5%innodb_io_capacity）
            {    
                执行合并5%innodb_io_capacity个page的插入缓冲的操作
            }

        3，日志缓存刷新到磁盘（总是）
        
        4，删除无用的undo页（总是）
           根据参数：innodb_purge_batch_size　　　　 5. checkpoint　　　　　
    }

    BackgroundOperation()
    {
        1，删除无用的Undo页（总是）
        2，合并innodb_io_capacity个插入缓冲（总是）
        3，跳回到主循环（总是）
        4，不断刷线innodb_io_capacity个page到磁盘，直到满足(脏页小于参数buf_max_dirty_pages_pct)。
    }

}

IO thread ：分为 insert buffer、log、read、write进程，分别用来处理insert buffer、重做日志、读写请求的IO回调

purge thread：事务被提交之后，其所使用的undolog可能不再需要，因此需要用这个线程来回收已经使用并分配的undo页(保存了事务发生之前的数据的一个版本，用于回滚)

page cleaner thread：将之前InnoDB旧版本中脏页的刷新操作，从Master Thread线程中剥离出来，放到这个单独的线程里进行，以减轻主线程的工作

Checkpoint技术

事务数据库系统普通采用了write ahead log策略，即当事务提交时，先写重做日志（redo log）再修改页，当发生故障时，通过redo log来进行数据的恢复。增删改时，首先顺序写入redo log（顺序写磁盘，类似于kafka），然后修改pool页（pool里没有的，插入insert buffer），之后各种线程，会按照规则从缓存里将数据刷入到磁盘，进行持久化，发生故障了，就从redo log恢复

checkpoint（检查点）的作用就是将缓冲池脏页写回磁盘，缓冲池不够用时将脏页刷新到磁盘，redo log不够用时刷新脏页(脏页指的是执行了DML语句使缓存数据比磁盘新)

目前有两种Checkpoint：
1 数据库关闭时，将所有脏页刷新到磁盘，这是默认的方式
2 Master Thread操作，这个主线程会每秒、每10秒从脏页列表刷新一定比例的页到磁盘，这是个异步的操作，不会阻塞查询
3 LRU 列表空闲页不足时，需要刷新一部分来自LRU列表的脏页
4 redo log文件不可用时，需要强制刷新一部分，为了保证redo log的循环利用
5 pool空间不足时，脏页太多，需要刷新

MySQL事务

在数据库中,所谓事务是指不能分割开的多个操作就称之为一个事务，对于MySQL中常见的InnoDB引擎支持事务，而myIsam不支持事务

start transaction标识事务开始，commit提交事务

start transaction;
……  #一条或多条sql语句
commit;

//MySQL中默认采用的是自动提交（autocommit）模式
show variables like 'autocommit';
set autocommit = 0;//关闭自动提交事务

如DDL语句(create table/drop table/alter/table)会强制commit

事务的ACID

事务是逻辑上的一组操作，要么都执行，要么都不执行

InnoDB存储引擎提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)，其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础

原子性（Atomicity）：原子性是指事务是一个不可分割的整体

依靠undo log回滚日志，当事务回滚时能够撤销所有已经成功执行的sql语句

一致性（Consistency）：保证结果的数据守恒

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性；C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段

隔离性（Isolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，事务相互之间不被干扰.

通过锁机制和MVCC多版本并发控制机制保证隔离性，一个行记录数据有多个版本对快照数据，这些快照数据在undo log中，如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本

持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响.

向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新脏页到磁盘中，如果Buffer Pool中修改的数据还没有刷新到磁盘出现宕机，就会导致数据的丢失，事务的持久性无法保证；redo log用于保证事务持久性，当数据修改时，所有修改先写入redo log日志，再更新到Buffer Pool；当事务提交时，会调用fsync接口对redo log进行刷盘，即使宕机在重启时也可以读取redo log中的数据，对数据库进行恢复，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求

事务隔离级别

**事务并发问题：**A,B两个事务并发操作同一条数据

1. 回滚丢失：
   A,B同时查询到数据，A事务更新并commit，而B事务进行rollback，导致A事务的更新丢失
2. 提交覆盖丢失：
   最后是B事务也进行更新并且commit，将A的更新数据覆盖，导致数据丢失
3. 幻读：两次读之间有别的事务增删，有新增或删除数据
   B已查询到数据，此时A事务进行更新并commit，B再查询数据发现有变化
4. 不可重复读：两次读之间有别的事务修改，有修改原数据
5. 脏读：
   A更新但未commit,此时B查询到已经更新的数据

设置MySQL的会话级别隔离级别：

set session transaction isolation level read committed;

不同的隔离级别可能会导致不同的数据库并发问题,隔离级别越高,性能越低

READ UNCOMMITTED（读未提交）：事务还没提交，而别的事务可以看到他其中修改的数据的后果，也就是脏读，隔离性失效；事务对当前被读取的数据不加锁(加的都是共享锁)；其实就是会话过程任何操作都不加锁

READ COMMITTED（读已提交）：在进行DML时加排他锁，行锁锁的是索引，不是行记录，所以如果没有使用索引，那么会使用表锁，直到事务结束才释放，在commit之前各个并发事务只能操作读的共享数据，所以不存在脏读问题，但是如果一个事务在两次同一数据查询之间出现另一个事务修改了该数据并commit，RC级别是可以读取已commit的数据，所以两次查询的数据不一致

REPEATABLE READ（可重复读）：A事务在读取时加读锁直到事务结束，在这个期间即使B事务更新了数据并commit，MVCC机制减了锁处理引入快照使select不需要加锁保证了A事务在commit前读取的都是其事务开启时的数据；Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们

不可重复读是有修改导致原数据不一致，而幻读是新增或者删除导致数据条数不一致，RR级别并不一定能保证幻读，只有在有索引的情况下Innodb会触发间隙锁从到避免幻读

SERIALIZABLE（可串行化）：以上在事务进行时加的都是行级锁，而seriazable 直接加表级锁，写操作时直接锁表，从而既不能修改，也不能新增和删除

事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；
事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。

MVCC多版本并发控制

普通处理并发的方式就是加上读写锁，但是这样会由于等锁而造成性能问题，而Multi-Version Concurrency Control提供了时间一致性处理思路，当用户连接数据库时，看到的都是某一特定时刻的数据库快照，在事务没有提交之前，始终读到的是某一特定时刻的数据库快照，直到事务提交，才会读取修改内容。在更新某些数据时，并非使用新数据覆盖旧数据，而是标记旧数据是过时的，同时在其他地方新增一个数据版本。因此，同一份数据有多个版本存储，但只有一个是最新的，乐观锁也是基于mvcc实现的

InnoDB的MVCC实现是通过保存数据在某个时间点的快照，事务快照是用来存储数据库的事务运行情况；Innodb在每行记录后面保存三个隐藏的列：

//delete操作在innodb也是update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除
事务ID(DB_TRX_ID)：用来标识最近一次对本行记录做修改(insert|update)的事务标识符(事务id)

//如果一行记录被更新, 则 undo log record 包含 '重建该行记录被更新之前内容' 所必须的信息
回滚指针(DB_ROLL_PTR):写入回滚段(rollback segment)的 undo log record (撤销日志记录)

DB_ROW_ID:包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中

事务更新过程：

锁机制原理

MySQL常见引擎锁

InnoDB（MySQL默认存储引擎）：默认的隔离级别是Repeatable Read（可重复读），innoDB引擎的行锁机制锁的是索引，不是行记录，所以如果没有使用索引会使用表锁，直接锁住整张表

MyISAM：不支持事务和行级锁，所以速度很快，性能优秀。表级锁，支持并发插入，支持全文索引

MEMORY：表级锁，支持Hash索引，内存表，将数据存储在内存中，表结构不是存储在内存中的，查询时不需要执行磁盘I/O操作，所以要比MyISAM和InnoDB快很多倍，但是数据库断电或是重启后，表中的数据将会丢失，表结构不会丢失

悲观锁和乐观锁

悲观锁：事务在操作时,悲观认为其他事务会进来干扰自己.

使用数据库自身的排它锁机制，DML操作自动会加上排它锁，但是DQL操作需要我们手动加上排他锁.

begin;
select * from emp for update;
//如果不commit,那么其他查询将造成堵塞，无法查询
commit;

乐观锁：无锁技术，事务在操作时,乐观认为其他事务不会进来干扰自己.

增加一个版本号，每次更新都会产生一个不同的版本号，需要将查询到的版本号作为更新条件

UPDATE person set name  = 'Java' ,version =  version + 1 WHERE id = 10 AND version = 刚刚查询出来的版本号

锁的模式

共享锁(S)：发生在数据查找之前，多个事务的共享锁之间可以共存

排他锁(X)：发生在数据更新之前，排他锁是一个独占锁，与其他锁都不兼容

更新锁(U)：发生在更新语句中，更新锁用来查找数据，当查找的数据不是要更新的数据时转化为S锁，当是要更新的数据时转化为X锁

意向共享锁(IS) ，意向排他锁(IX)，意向更新锁(IU)，共享意向排他锁(SIX)，共享意向更新锁(SIU)，更新意向排他锁(UIX)

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁

排它锁

记录锁（也叫行锁），间隙锁，next-key锁统统属于排他锁

Record Locks（记录锁）：在索引记录上加锁

Gap Locks（间隙锁）：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁

Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁，它相当于是Record Locks与Gap Locks的一个结合

行锁：共享锁和排它锁，对应的就是读写锁；行锁必须有索引才能实现且两个事务不能锁同一个索引**，**insert ，delete ， update在事务中都会自动默认加上排它锁

共享锁(一般用于读，读锁)：lock in share mode
select  * from xxx whereid>6 lock in share mode；

排它锁(一般用于写，写锁)：for update
select  * from xxx whereid>6 for update；

//两个事务不能锁同一个索引（主键索引id），会造成后面的事务堵塞
A事务：select  * from xxx where id=7 for update；
B事务：select  * from xxx where id=8 for update；

间隙锁：默认隔离级别下，如果索引不为主键索引，且索引可重复，或者多个查询条件时都会有间隙锁(封锁锁索引记录中的间隔，防止其它事务在这个区域内修改数据，防止出现幻读)

比如记录中 id:1,2,3,4,5,6  的值分别为value： 4,0,3,3,3,8,5，如果where value =3 ，结果为id(3,4,5)，那么锁定的间隙为 id(2~6)

MySQL日志

binlog

MySQL Server层面的日志，记录的是sql语句，所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志，不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，可以通过查询通用日志来查看MySQL执行过的所有语句，binlog主要的功能是Replication复制和Recovery恢复

可以作为恢复数据，主从复制搭建使用；binlog记录会直接追加写，当一份日志写到一定大小的时候会更换下一个文件，不会覆盖，以二进制的形式存储在磁盘上

运行MySQL服务器时，若启用二进制日志，系统的性能会降低，还会占用一定的存储空间，因此MySQL默认是关闭二进制日志功能的，需要手动启用

//首先查看mysql是否开启binlog同步功能
show variables like 'log_bin';

通过修改/etc/my.inf文件开启binlog

server-id = 1  # 确保在整个Mysql集群中唯一
log-bin = /var/log/mysql/mysql-bin.log   #日志存放位置
binlog-format = ROW #选择row模式

#存完之后重启mysql
service mysql restart

#看二进制日志文件用mysqlbinlog 
shell> mysqlbinlog mail-bin.000001 
或者shell> mysqlbinlog mail-bin.000001 | tail  

#恢复日志文件：开启二进制日志后，通过工具mysqlbinlog来处理
mysqlbinlog bin_log.000002 丨 mysql -u root -p 123456

#indows：配置文件为 my.ini，一般在 MySQL 的安装目录下或者 c:\Windows 下。 
log-bin=mysql-bin

binlog模式：有三种模式：ROW（行模式）, Statement（语句模式）, Mixed（混合模式）

ROW（行模式）：记录那条数据修改了，注意：记录的是这条记录的全部数据，即使只更新了一个字段，binlog里也会记录所有字段的数据，binlog日志会非常大，mysql主从同步时，会产生大量磁盘IO

Statement（语句模式）: 每一条会修改数据的sql都会记录在binlog中，不需要记录每一行的变化，减少了binlog日志量，由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果

Mixed（混合模式）：在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种

推荐用mixed，默认使用statement

#查看binlog模式
show variables like 'binlog_format'
set session/global binlog_format=mixed;

MySQL日志类型：

错误日志：   -log-err  
查询日志：   -log  
慢查询日志:  -log-slow-queries  
更新日志:   -log-update  
二进制日志： -log-bin 

开启慢查询：

long_query_time =2  --是指执行超过多久的sql会被log下来，这里是2秒 
log-slow-queries= /usr/local/mysql/log/slowquery.log  --将查询返回较慢的语句进行记录 
log-queries-not-using-indexes = nouseindex.log  --就是字面意思，log下来没有使用索引的query 
log=mylog.log  --对所有执行语句进行记录

redolog

innodb事务日志包括redo log和undo log，是InnoDB存储引擎层的日志

redolog用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来，记录是为了保证事务安全，当数据库挂掉之后重启仍能通过redolog执行未完成事务，由于记录的是数据的内容从而可以crash-safe(作为异常宕机或者介质故障后的数据恢复使用)

在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，redo log日志的大小是固定的，即记录满了以后就从头循环写

在缓存池中，刷脏时需要将页刷入磁盘，对于一个页面16kb大小，只改其中一点点东西，就要将16kb的内容刷入磁盘，而且一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO，所以采用redo log解决上面问题；当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作,当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)，当数据库宕机重启的时候，会将日志内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据

redo log进行刷盘比对数据页刷盘效率高，因为redo log体积小，只记录了哪一页的修改，末尾追加日志，属于顺序IO，效率显然比随机IO来的快

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，二是磁盘上的重做日志文件(redo log file)，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化

从redo log buffer写日志到磁盘的redo log file中：为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)，因为MySQL的log buffer处于用户空间的内存中，要写入到磁盘上的log file中，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中

日志刷脏：首先通过变量 innodb_flush_log_at_trx_commit 的值来决定是否刷新log buffer到磁盘，该变量有3种值：0、1、2，默认为1，事务每次提交的时候都会刷事务日志到磁盘中

- 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差
- 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据
- 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

刷新频率由变量innodb_flush_log_at_timeout控制，默认是1秒，和是否commit无关；当log buffer中已经使用的内存超过一半时也会刷新

redo log的格式：innodb存储引擎存储数据的单元是页，默认页大小是16KB( innodb_page_size 变量控制)，页内存放的是log block(每个512字节)记录的是数据页的变化

redo_log_type：占用1个字节，表示redo log的日志类型。
space：表示表空间的ID，采用压缩的方式后，占用的空间可能小于4字节。
page_no：表示页的偏移量，同样是压缩过的。

redo_log_body：数据部分，恢复时会调用相应的函数进行解析，例如insert语句和delete语句写入redo log的内容是不一样的

undolog

回滚日志，实际提供回滚和多个行版本控制(MVCC)两个主要功能，在数据修改的时候，不仅记录了redo，还记录了相对应的undo，也就是保存了事务发生之前的数据的一个版本，可以用于回滚，提供多版本并发控制下的读（MVCC），即非锁定读(快照读)。

undo log属于逻辑日志，它记录的是sql执行相关的信息，当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：

(1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
(3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

DML语句不同undo页的格式也会不同，insert类型的undo log只记录了主键及对应的主键值，而update、delete则记录了主键及所有变更的字段值

MySQL高可用

对于很多系统高可用的方式都有集群，而MySQL最常见的集群方式是主从复制，读写分离；对于单业务处理，一主多从，多业务处理则是以一主多从为基础单位的集群，各个主从之间尽量分离开；如果是相同业务做分库，那么需要在客户端读写时遵循特定的规则

MySQL主从

主从数据库，主库负责读写，从库只负责读从而提高吞吐率

主从复制的原理：当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中；salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程，当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点，I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log，SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作

主从数据库的配置

进入vi /etc/my.cnf进行修改master服务器配置

# 开启binlog
log-bin=mysql-bin
#服务器唯一ID，默认是1，一般取IP最后一段
server-id=104
# 需要同步的数据库，如果不配置则同步全部数据库
binlog-do-db=test_db
# binlog日志保留的天数，清除超过10天的日志， 防止日志文件过大，导致磁盘空间不足
expire-logs-days=10 

修改slave配置

#主库宕机后升级从库为主库时需要设置
log-bin=mysql-bin
#服务器唯一ID
server-id=102
# 需要同步的数据库，如果不配置则同步全部数据库
replicate-do-db=mydb

修改之后进行重启

service mysql restart

在master服务器上建立用于同步的账户并授权slave

create user 'root'@'%' identified by 'admin';
#192.168.0.102是slave1从机的IP，identified by：指定用户的登录密码
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.102' identified by 'admin';
#192.168.0.103是slave2从机的IP，@后面用%表示所有
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.104' identified by 'admin';
#刷新系统权限表的配置
FLUSH PRIVILEGES;

slave设置链接masterbing启动复制功能

#连接master
CHANGE MASTER TO 
MASTER_HOST='192.168.0.104',//主机IP
MASTER_USER='root',//之前创建的用户账号
MASTER_PASSWORD='admin',//之前创建的用户密码
MASTER_LOG_FILE='mysql-bin.000001',//master主机的binlog日志名称
MASTER_LOG_POS=2409,//binlog日志偏移量
master_port=3306;//端口
#启动slave的复制功能
start slave;

查看主从状态

show master status\G;
show slave status\G;

读写分离

完成主从的配置，目的是为了读写分离，主库用于读写，而从库只用于读；实现方式的原理无非就是对不同的主从数据源进行不同的操作，比如采用AOP的方式，通过方法名判断，方法名中有get、select、query开头的则连接slave，其他的则连接master数据库

ShardingSphere-JDBC是一款分布式数据库分片中间件，实现包括分库分表，读写分离等功能

读写分离的原理：通过源码MasterSlaveDataSource中getDataSource()获取路由的数据源时isMasterRoute() 方法判断当前操作是否应该路由到主库数据源，如果SQL类型是DML则返回true，而从库通过负载均衡策略选择不同的从库数据源

spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.0.108:3306/test_db
        username: yehongzhi
        password: YHZ@1234
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.0.109:3306/test_db
        username: yehongzhi
        password: YHZ@1234
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.0.110:3306/test_db
        username: yehongzhi
        password: YHZ@1234
    config:
      # 主从库配置
      masterslave:
        # 从库负载均衡算法类型，可选值：ROUND_ROBIN(轮询)，RANDOM（随机）
        load-balance-algorithm-type: random
      sharding:
        master-slave-rules:
          master:
            master-data-source-name: master
            slave-data-source-names: slave0,slave1
      props:
        sql:
          show: true //在执行SQL时，会打印SQL，并显示执行库的名称

分库分表

ShardingSphere-JDBC对于多个主库进行的分库分表和读写分离原理上基本一致，依然是sql解析之后，根据路由规则和分片策略生成路由路径；各个master库之间的数据是不同步的，因为主从复制是数据库层面的

配置的所有数据源交给sharding-jdbc进行管理，数据分片的最小物理单元，由数据源名称和数据表组成，例如： DS_0.t_user_0、DS_0.t_user_1