导图社区 Mysql核心原理笔记
- 67
- 9
- 1
- 举报
Mysql核心原理笔记
一张思维导图带你学习Mysql核心原理，包括mysql数据库事务、日志锁、索引等原理，结构型知识框架方便学习理解！
编辑于2021-10-16 17:27:07- 日志锁
- 相似推荐
- 大纲
Mysql核心原理
数据库架构演变
单机单库
数据量太大，一台机器无法承受
读写操作量太大，超出一台机器承受
一旦数据库宕机，应用整体崩溃，可用性低
主从架构（读写分离）
数据量太大，超出一台服务器承受能力
写操作量大，超过master承受能力
分库分表
将数据拆分后放在不同机器中，同时每台机器可以做主从，保证高可用和数据备份
垂直拆分
拆分同表多列为多表多列，数据量不变
水平拆分
将数据水平拆分，数据量变为1/n
sass云数据库
体系架构
MySQL与众不同主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。服务器通过API与存储引擎进行通信；
连接层
主要完成一些类似连接处理、授权认证及相关安全方案；引入线程池的概念，为通过认证安全连接的客户端提供线程；可以实现基于SSL的安全连接，服务器也会为安全接入的客户端验证它所具有的操作权限。
连接池
安全连接
认证授权
Server层
主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数；所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等；不同的存储引擎具有的功能不同，我们可以根据实际需要进行选取；
连接器
缓存
分析器
优化器
执行器
存储引擎层
存储引擎真正的负责了MySQL中数据的存储和提取；服务器通过API与存储引擎进行通信；不同的存储引擎具有的功能不同，我们可以根据实际需要进行选取；
InnoDB
MyISm
memory
系统文件层
日志文件
error log
默认开启，show variables like '%log_error%'
general log
记录一般查询语句，show variables like '%general%';开启 general log 将所有到达MySQL Server的SQL语句记录下来。
binlog
**1.说明**记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不记录select、show等不修改数据库的SQL。主要用于数据库恢复和主从复制。show variables like '%log_bin%'; //是否开启show variables like '%binlog%'; //参数查看show binary logs;//查看日志文件**2.记录类型**Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。1.ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。2.STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。优点：日志量小，减少磁盘IO，提升存储和恢复速度缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。3.MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。**3.写入机制**根据记录模式和操作触发event事件生成log event（事件触发执行机制）将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。**4.相关操作**1.使用show binlog events命令show binary logs; //等价于show master logs; show master status; show binlog events; show binlog events in 'mysqlbinlog.000001';2.使用mysqlbinlog 命令mysqlbinlog "文件名" mysqlbinlog "文件名" > "test.sql"使用 binlog 恢复数据//按指定时间恢复 mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop- datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234 //按事件位置号恢复 mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234mysqldump：定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作3.删除Binlog文件purge binary logs to 'mysqlbinlog.000001'; //删除指定文件 purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件 reset master; //清除所有文件
slow query log
记录所有执行时间超时的查询SQL，默认是10秒。show variables like '%slow_query%'; //是否开启show variables like '%long_query_time%'; //时长slow_query.log是文本日志，可直接查看或使用mysqlslowdump分析查看
InnoDB日志
redo log
每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为ib_logfile0和ib_logfile1。可以通过下面一组参数控制Redo Log存储：show variables like '%innodb_log%';Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作。1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OScache -> flush cache to disk 的操作。一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。
undo log
提供版本连，结合readView实现MVCC，purge thread定时清理
配置文件
my.cnf/my.ini
数据文件
db.opt 文件：记录这个库的默认使用的字符集和校验规则。frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个.MYD 文件。MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等 。ib_logfile0、ib_logfile1 文件：Redo log 日志文件
InnoDB
物理及内存结构图
子主题
内存结构
Buffer Pool
缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率。free page ：空闲page，未被使用clean page：被使用page，数据没有被修改过dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致Buffer Pool配置参数show variables like '%innodb_page_size%'; //查看page页大小show variables like '%innodb_old%'; //查看lru list中old列表参数show variables like '%innodb_buffer%'; //查看buffer pool参数建议：将innodb_buffer_pool_size设置为总内存大小的60%-80%，innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。
free list
表示空闲缓冲区，管理free page
flush list
表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作
lru list
表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。改进型LRU算法维护普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰改进LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。
Change Buffer
写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数innodb_change_buffer_max_size;当更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。如果该记录在BufferPool不存在（没有命中），会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进性磁盘读取，然后再从ChangeBuffer中读取信息合并，最终载入BufferPool中。写缓冲区，仅适用于非唯一普通索引页
Log Buffer
日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少磁盘IO频率innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为10 ： 每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS cache，刷盘OScache-->磁盘文件），最多丢失1秒数据1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作
Adaptive Hash Index
自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。
原理
Buffer Pool在线扩容
Buffer Pool由多个chunk组成，当需要扩容时，不需要挪动数据，只需要再分配拓展部分的chunk
数据库偶发抖动
大数据量查询导致Buffer Pool需要腾出空间，淘汰未使用的脏页需要刷入磁盘
redolog写满后触发的刷脏
Buffer Pool线程安全问题
Buffer Pool分为多个instance，共享资源加锁，多个实例可以降低锁的粒度
磁盘结构
系统表空间
包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。该空间的数据文件通过参数innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名ibdata1、12MB、自动扩展)。
数据字典
InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件（.frm文件）中存储的信息重叠
Doublewrite Buffer
**磁盘同步是按照Page为单位写入的**。想法是在写入数据文件之前将数据写入主表空间中的顺序日志。如果发生部分页面写入（换句话说，写入损坏），InnoDB和XtraDB将使用缓冲区来恢复数据。位于系统表空间，是一个存储区域。在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个好备份。在大多数情况下，默认情况下启用双写缓冲区，要禁用Doublewrite 缓冲区，可以将innodb_doublewrite设置为0。使用Doublewrite 缓冲区时建议将innodb_flush_method设置为O_DIRECT。
Change Buffer
Undo Logs
undo log默认在系统表空间，也可以单独创建表空间
独立表空间
默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表空间中。否则，innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。
文件结构
子主题
TableSpace
表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。
Segment
段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf nodesegment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。
Extent
区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会一页一页分，直接分配一个区。
Page
页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。
Row
行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息。
数据存储格式
通过 SHOW TABLE STATUS 命令一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda。通过 information_schema 查看指定表的文件格式:select * from information_schema.innodb_sys_tables;
文件格式
Antelope
只支持REDUNDANT、COMPACT行格式
Barracuda
支持REDUNDANT、COMPACT、DYNAMIC、COMPRESSED行格式
行格式(Row_format)
四种行格式支持
通用表空间
1.通用表空间的元数据存储在内存中，可以加快元数据相关操作的效率。2.通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。通用表空间数据文件可以放在相对于MySQL数据目录或独立于MySQL数据目录的目录中，该目录提供了单独表空间的许多数据文件和存储管理功能 。与单独表空间一样，将数据文件放在MySQL数据目录之外的功能允许您分别管理关键表的性能，为特定表设置RAID或DRBD，或者将表绑定到特定磁盘。CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空 间ts1 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1 表空间
undo 表空间
撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0。参数值为0表示使用系统表空间ibdata1;大于0表示使用undo表空间undo_001、undo_002等。撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。
临时表空间
分为session temporary tablespaces 和global temporary tablespace两种。session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace储存用户临时表的回滚段（rollback segments ）。mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。
redo log
重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。MySQL以循环方式写入重做日志文件，记录InnoDB中所有对Buffer Pool修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件。读写事务在执行的过程中，都会不断的产生redo log。默认情况下，重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示。MySQL的innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。设置O_DIRECT表示数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从InnodbBuffer写到磁盘文件。默认的fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文件与redo log的缓存信息。
示意图
后台线程
IO Thread
在InnoDB中使用了大量的AIO（Async IO）来做读写处理，这样可以极大提高数据库的性能。在InnoDB1.0版本之前共有4个IO Thread，分别是write，read，insert buffer和log thread，后来版本将read thread和write thread分别增大到了4个，一共有10个了。read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。4个write thread：负责写操作，将缓存脏页刷新到磁盘。4个log thread：负责将日志缓冲区内容刷新到磁盘。1个insert buffer thread ：负责将写缓冲内容刷新到磁盘。1个
Purge Thread
事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo页。show variables like '%innodb_purge_threads%';
Page Cleaner Thread
作用是将脏数据刷新到磁盘，脏数据刷盘后相应的redo log也就可以覆盖，即可以同步数据，又能达到redo log循环使用的目的。会调用write thread线程处理。show variables like '%innodb_page_cleaners%';
Master Thread
Master thread是InnoDB的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘 ，保证数据的一致性。包含：脏页的刷新（page cleaner thread）、undo页回收（purge thread）、redo日志刷新（log thread）、合并写缓冲等。内部有两个主处理，分别是每隔1秒和10秒处理。每1秒的操作：1.刷新日志缓冲区，刷到磁盘2.合并写缓冲区数据，根据IO读写压力来决定是否操作3.刷新脏页数据到磁盘，根据脏页比例达到75%才操作（innodb_max_dirty_pages_pct，innodb_io_capacity）每10秒的操作：1.刷新脏页数据到磁盘2.合并写缓冲区数据3.刷新日志缓冲区4.删除无用的undo页
日志
Undo Log
Redo Log
索引
索引类型
从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分：主键索引、辅助索引（二级索引）
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）
Full-Text全文索引
全文索引是MyISAM的一种特殊索引类型，主要用于全文索引；InnoDB从MYSQL5.6版本提供对全文索引的支持。
它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。
Hash索引
主要就是通过Hash算法，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。
检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。
MySQL目前有Memory引擎和NDB引擎支持Hash索引。
自适应哈希索引
示意图
Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升
经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
优点
无序，没有树高
降低对二级索引树的频繁访问资源
自适应
缺点
hash自适应索引会占用innodb buffer pool；
自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'
极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读
MySQL自动管理，人为无法干预
状态监控
show engine innodb status\G
Hash table size 34673, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s
34673：字节为单位，占用内存空间总量
通过hash searches、non-hash searches计算自适应hash索引带来的收益以及付出，确定是否开启自适应hash索引
查看开启状态
innodb_adaptive_hash_index
B树索引原理
二分查找
Hash表
B-tree
B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。
索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列
B+tree(mysql索引使用的存储结构)
相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。
非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能
执行计划
type
NULL：表示不用访问表，速度最快。
const：表示使用主键或唯一索引做等值查询，常量查询。
eq_ref：多表join查询，表示前表每一个记录都只能匹配后面表的一行结果。
ref：表示使用非唯一索引进行单值查询。
range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
ALL：表示全表扫描，性能最差。
key
表示查询时真正使用到的索引，显示的是索引名称。
rows
MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
Extra
Using where
表示查询需要通过索引回表查询数据
Using index
覆盖索引，表示查询需要通过索引，索引就可以满足所需数据
Using index Condition
索引下推，无需回到主键索引过滤
Using filesort
表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化
Using temprorary
查询使用到了临时表，一般出现于去重、分组等操作
索引优化
优化原则
离散度高的键做索引
覆盖索引和索引下推，优化回表查询
Null查询可用到索引，但最好设置为not null,因为会涉及到字段额外存储空间
虚拟列：alter table student add first_name varchar(2) generated always as (left(name, 1))
代码先行，索引后上
联合索引尽量覆盖条件
不要在小基数字段上加索引
长字符串采用前缀索引
group和order会用不上
where与order 冲突优先where
全值(等值)匹配 是最优选择；
最佳左前缀法则，如联合索引(a,b,c)，可利用的索引就有(a), (a,b), (a,b,c)；
不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描；
存储引擎不能使用索引中范围条件右边的列；
尽量使用覆盖索引，减少select；
is null ,is not null 也无法使用索引；
like "xxx%" 是可以用到索引的，like以通配符开头('%abc'或'%abc%')索引失效会变成全表扫描的操作；
字符串不加单引号索引失效；
少用or，用它来连接时会索引失效；
<、<=、>、>=、BETWEEN、IN 可用到索引，<>、not in、!= 则不行，会导致全表扫描；
分页查询优化
limit ?,?两个值的增大都会导致查询变慢
如果可以找到偏移的那条数据，就可以达到优化目的
根据自增且连续的主键排序的分页查询
先用主键过滤
根据非主键字段排序的分页查询
使用索引排序
select * from employees ORDER BY name limit 90000,5;优化后select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
join关联优化
常用算法
嵌套循环连接算法NLJ
驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优 化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。 当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表， 当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算 法是 NLJ。
基于块的嵌套循环连接BNLJ
优化
关联字段加索引，让mysql做join操作是尽量选择NLJ算法
小标驱动大表，写多表连接sql时如果明确知道哪张表是小表可使用straight_join写法固定驱动表
in和exsits优化
select A in /exists(B)，如果A>B,则用in，反之用exists
count查询优化
将总数维护到redis里
增加数据库计数表
show table status
order by 关键字优化
MySQL支持两种方式的排序：Index和FileSort；index 效果高（扫描索引本身完成排序），FileSort效率较低。
order by 子句尽量使用 Index方式排序，避免使用FileSort方式排序；
order by 语句使用索引最左前列；
使用where子句与order by子句条件组合满足索引最左前列；
filesort方式排序有两种算法
双路排序：MySQL4.1之前是使用该方式，两次扫描磁盘，最终得到数据。
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序。
优化策略
增大 sort_buffer_size 参数的设置；
增大 max_length_for_sort_data 参数的设置；
group by 关键字优化
group by 实际是先排序后进行分组，遵照索引建的最佳左前缀；
当无法使用索引列，增大 max_length_for_sort_data和sort_buffer_size参数的设置；
where 高于having，能在where限定的条件就不要去having限定了；
sql查询流程
流程示意图
1.客户端请求
2.连接器（验证用户身份，给予权限）
3.查询缓存（存在缓存则直接返回，不存在则执行后续操作）
4.分析器（对SQL进行词法分析和语法分析操作）
5.优化器（主要对执行的sql优化选择最优的执行方案方法）
6.执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）
7.去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
8.返回给客户端
该过程会话状态为sending data，主要做了三件事：读取，处理(过滤、排序，去重等)，发送
事务
事务及其ACID属性
持久性
指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失
原子性
事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
隔离性
隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。锁和多版本控制（MVCC）技术就是用于保障隔离性的（后面课程详解）。
一致性
一致性：指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括两方面的内容，分别是约束一致性和数据一致性。1.约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，可惜在 MySQL 中不支持Check 。2.数据一致性：是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这三个特性又是通过 Redo/Undo 来保证的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属于业务逻辑范畴。ACID 及它们之间的关系如下图所示，4个特性中有3个与 WAL 有关系，都需要通过 Redo、Undo 日志来保证等。WAL的全称为Write-Ahead Logging，先写日志，再写磁盘。
并发事务带来的问题
脏读
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B 事务回滚，A读取的数据无效，不符合一致性要求。
不可重复读
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读
前提条件：InnoDB引擎，可重复读隔离级别，使用当前读时
快照读，是不会看到别的事务插入的数据的
表现：一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行
事务A读取到了事务B提交的新增数据，不符合隔离性
幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。
解决方法
将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。
隔离级别
读未提交
读已提交
可重复读
可串行化
事务的演进
2.1 并发事务事务并发处理可能会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读等。更新丢失：当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。脏读：一个事务读取到了另一个事务修改但未提交的数据。不可重复读：一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。幻读：一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了或少了几行记录
排队执行
使用队列执行并发事务，如同redis
排他锁
引入锁之后就可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。
读写锁
读和写操作：读读、写写、读写、写读。
读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，让两个事务就可以同时被执行。
读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。
MVCC
多版本控制MVCC，也就是Copy on Write的思想。
MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。
在事务开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行。
MVCC
MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。如何生成的多版本？每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。
作用
MVCC最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极<br>大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目<br>前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。
读操作类型
快照读（SnapshotRead）
读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
当前读（CurrentRead）
读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select... for update 或lock in share mode，insert/delete/update）
MVCC原理
快照链
版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚 日志，并且用两个隐藏字段trxid和rollpointer把这些undo日志串联起来形成一个历史记录版本链
ReadView
在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束 之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事 务id数组（数组里最小的id为minid）和已创建的最大事务id（maxid）组成，事务里的任何sql查询结果需要从对应 版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。简单来说，就是你执行一个事务的时候，就给你生成一个ReadView，里面比较关键的东西有4个
事务链表
事务链表：ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3; trx11等均为当前活跃事务的ID readview:readview->creatortrx_id = ct-trx; 当前事务ID readview->uplimit_id = trx3; 低水位 readview->lowlimit_id = trx11; 高水位 readview->trxids = [trx11, trx9, trx6, trx5, trx3]; 如读取到的行数据事务ID 大于lowlimitid高水位，说明这个数据的事务是在当前事务后面后面完成的，则不可见 如读取到的行数据事务ID 小于uplimitid低水位，说明该数据的事务在本事务开启之前已不再活跃已提交的，则该数据可见 如读取到的行数据的事务ID 在低水位和高水位之间，则看事务链表中是否存在该事务ID,如存在则说明当前事务开启时，这个数据的事务正在活跃还未提交所以不可见，如果不存在则可见
m_ids
这个就是说此时有哪些事务在MySQL里执行还没提交的
min_trx_id
就是m_ids里最小的值
检索的行对应的事务id小于该值，说明已提交的，可见
max_trx_id
这是说mysql下一个要生成的事务id，就是最大事务id
大于该id，说明未提交，不可见
creator_trx_id
就是你这个事务的id
当前事物id
事务执行过程
流程图
1、从磁盘文件加载缓存数据
2、写入数据旧值到undo日志文件
3、更新内存数据
4、将变更写入redo日志缓冲区，处于prepare准备提交阶段
5、redo处于准备提交事务状态(期间redo日志不定期刷入磁盘）
6、执行器将该操作生成binlog日志并写入磁盘
7、提交事务，写入binlog文件与位置和commit标记到redo日志文件
8、IO线程根据刷盘策略将脏数据写入数据文件
redo与binlog的区别
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的
redo是物理日志，记录某个数据页上做的修改,binlog是逻辑日志,记录语句的原始逻辑
redo是循环写的,空间固定会用完；binlog是追加写,到一定大小后会切换且不会覆盖
两阶段提交
binlog作为事务的协调者
redo log 的写入拆成了两个步骤：prepare 和 commit
目的：为了让两份日志(redo和binlog)之间的逻辑一致
写redo prepare成功，binlog写之前数据库崩溃
重启恢复后发现没有commit,事务回滚，binlog没有记录，备份恢复后与源库数据一致
写redo prepare成功，binlog成功，commit之前崩溃
重启恢复后虽然没有提交成功，但是满足prepare和binlog的完整，重启后会从binlog日志的Xid中获取提交的信息重新将该事务重做并commit。备份恢复后与源库数据一致
步骤
1、事务写入redo，处于prepare阶段
2、写入binlog日志
3、事务写入redo，提交事务
不采用两阶段提交存在的问题
先写redo在写binlog
redo记录了变更，之后发生宕机，binlog没有记录，实例恢复后仍然能够把数据恢复回来，但日后通过binlog做数据恢复时则没有对应数据变更
先写binlog再写redo
binlog记录了变更，之后发生宕机，redo没有记录，实例恢复后该变更无效，但日后通过binlog做数据恢复时则会恢复对应数据变更
相关参数
innodb_flush_log_at_trx_commit
针对innodb的redo，事务日志刷盘的策略
0
在提交事务时，每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存
1
在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，并调用操作系统fsync刷新IO缓存
2
在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，但并不马上调用fsync来刷新IO缓存，而是每秒只做一次磁盘IO缓存刷新操作
sync_binlog
针对binlog的，表示commit多少次就同步一次，同步时会触发dump线程发送到从库
0
每秒刷盘，写入os系统缓存
1
每次提交刷盘，不经过系统缓存，直接写入磁盘
n
每n次提交刷盘
组提交
前提：设置了双1
也就是每次事务提交，都会立即刷盘(redo和binlog)写到磁盘
innodb_flush_log_at_trx_commit=1
sync_binlog=1
示意图
目的：将binlog的多个刷盘操作合并成一个，最大化每次刷盘的收益，弱化磁盘瓶颈，提高性能
三个阶段
Flush 阶段
将Redo log中prepare阶段的数据刷盘
将binlog数据写入文件，此时只是写入文件系统的缓冲
Flush阶段队列的作用是提供了Redo log的组提交
如果这一步完成后数据库崩溃，由于协调者binlog中不保证有该组事务的记录，所以MySQL可能会在重启后回滚该组事务
Sync 阶段
这里为了增加一组事务中的事务数量，提高刷盘收益
binlog刷盘
binlog_group_commit_sync_delay=N
在等待N μs后，开始事务刷盘
默认0为不开启，单位微秒
最大 1000000ms == 1s
binlog_group_commit_sync_no_delay_count=N
如果队列中的事务数达到N个，忽略时间的等待，直接开始刷盘
默认0为不开启，依赖上一参数
Sync阶段队列的作用是支持binlog的组提交
如果在这一步完成后数据库崩溃，由于协调者binlog中已经有了事务记录，MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交
Commit 阶段
获取队列中的事务组，依次将Redo log中已经prepare的事务在引擎层提交
Commit阶段不用刷盘
Flush阶段中的Redo log刷盘已经足够保证数据库崩溃时的数据安全
此处队列的作用是承接Sync阶段的事务，完成最后的引擎提交，使得Sync可以尽早的处理下一组事务，最大化组提交的效率
锁
按性能分
悲观锁
借助数据库锁机制，在修改数据之前先锁定，再修改的方式 具有强烈的独占和排他特性。它指的是对数据被外界修改持保守态度 是一种对数据的修改持有悲观态度的并发控制方式。总是假设最坏的情况
悲观锁的实现
1、传统的关系型数据库使用这种锁机制，比如行锁、表锁、读锁、写锁等
2、Java 里面的同步 synchronized 关键字的实现
缺点
处理加锁的机制会会产生额外的开销，增加产生死锁的几率。降低并行性
优点
为数据处理的安全提供了保证
乐观锁
乐观锁假设数据一般情况不会造成冲突，在数据进行提交时，才会正式对数据的冲突进行检测 乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去， 直到提交的时候才去锁定
乐观锁的实现
1、CAS 实现：Java 中java.util.concurrent.atomic包下面的原子变量
2、版本号控制：在数据表加上数据版本号 version 字段，表示数据被修改的次数。当数据被修改时，version 值会 +1。当线程 A 要更新数据时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。
优点
不会产生任何锁和死锁
按类型分(都是悲观)
读锁
共享锁
写锁
排它锁
按粒度分
行锁
在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。当遇到无索引时，将升级为表锁
记录锁Record Lock
锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）
间隙锁Gap Lock
GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
临键锁Next-key Lock
Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）
表锁
读锁
写锁
意向读锁
用来处理行锁和表锁冲突衍生出的一种锁类型，当获取行锁时，会获取意向读锁，当添加表锁时，也会获取意向锁
意向写锁
用来处理行锁和表锁冲突衍生出的一种锁类型，当获取行锁时，会获取意向读锁，当添加表锁时，也会获取意向锁
页锁
按层级分
全局锁
锁住整个数据库实例，只能读，任何关于更新操作的语句都会阻塞
加全局锁
非innodb引擎，需要使用Flushtablewithreadlock命令
innodb引擎，mysqldump命令
使用--single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行
解全局锁
mysql>unlock tables
断开加锁session的连接
影响
如果在主库上做全局锁操作，业务基本停摆
如果在从库上做全局锁操作，备份期间从库不能更新主库同步过来的binlog，可能导致主从不一致
如果不加锁，备份完成后可能得到不一致的状态，不安全，所以一定要加锁
服务层锁
元数据锁metadatalock
元数据锁主要是面向DML和DDL之间的并发控制，如果对一张表做DML增删改查操作的同时，有一个线程在做DDL操作，不加控制的话，就会出现错误和异常。元数据锁不需要我们显式的加，系统默认会加。
对于MDL锁而言，select会阻塞alter，而alter不会阻塞select。在rename的瞬间，alter是会阻塞select的
表上存在未提交的事务，导致alter等待MDL，对于MDL锁而言，update会阻塞alter，同样alter也会阻塞update。
存在一个查询失败的语句，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。
原理
当做DML操作时，会申请一个MDL读锁
当做DDL操作时，会申请一个MDL写锁
读锁之间不互斥，读写和写写之间都互斥
引擎层锁
表锁
锁住整张表，通过不同的表锁设置，控制并发访问
备份恢复
物理备份
逻辑备份
Mysql集群
可用性
保证高可用的方法是冗余。但是数据冗余带来的问题是数据一致性问题。
一致性
最近数据读主库
拓展性
主从读拓展
分库分表读写拓展
主从模式
binlog日志
用于记录数据库执行的写入操作（不包括查询信息），以二进制的形式保存在磁盘中，是mysql的逻辑日志，由server层记录，mysam和innodb都会记录通过追加sql的方式写入，通过maxbinlogsize参数设置每个binlog文件的大小，满了以后生成新的，逻辑日志可以简单理解为记录sql物理日志可以理解为数据也变更。
使用场景
主从复制
数据恢复
刷盘时机
sync_binlog参数控制
日志格式
statment
row
mixed
作用
数据备份，避免影响业务（高可用）
读写分离，提供查询服务（读扩展）
实时灾备，用于故障切换（高可用）
主从异步复制原理
步骤
1.主库将数据库的变更操作记录到Binlog日志文件中
2.从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中
3.从库读取中继日志信息在从库中进行Replay,更新从库数据信息
上述过程都是异步操作，俗称异步复制，存在数据延迟现象
有损半同步复制
为了提升数据安全，MySQL让Master在某一个时间点等待Slave节点的 ACK（Acknowledgecharacter）消息，接收到ACK消息后才进行事务提交
主库事务写入分为 4个步骤
1、事务写入redo，处于prepare阶段
2、写入binlog日志
3、事务写入redo，提交事务
4、发送到从库
当Master需要在第三步等待Slave返回ACK时，即为 after-commit，半同步复制（MySQL 5.5引入）
当Master需要在第二步等待 Slave 返回 ACK 时，即为 after-sync，增强半同步（MySQL 5.7引入）
主库等待从库写入 relay log 并返回 ACK 后才进行Engine Commit。
无损半同步复制
区别于有损，无损半同步在ack之后再提交
开启时默认创建了单独的应答接收线程，变成了双工模式，发送和接收互不影响
半同步复制是跟 IO_THREAD 有直接关系，跟 SQL_THREAD 没关系
SLAVE 从库接收完二进制日志后给主库一个确认并不管relaylog中继日志是否正确执行完
即使SQL线程报错了，半同步复制还是不会切换成异步复制
相关参数
主
rpl_semi_sync_master_wait_for_slave_count
控制slave应答的数量，默认是1，表示master接收到几个slave应答后才commit
rpl_semi_sync_master_wait_point
AFTER_SYNC
slave响应后再提交
after_commit
提交后再等待响应
rpl_semi_sync_master_enabled
表示主上是否开启半同步复制功能，可以动态修改。可选值：ON\OFF
rpl_semi_sync_master_timeout
超过这个时间值没有收到信息，则切换到异步复制，默认10000毫秒
rpl_semi_sync_master_wait_no_slave
如果没有Slave链接，会切换到异步复制。是否允许master每个事务提交后都要等待slave的接收确认信号。默认为on，每一个事务都会等待
从
rpl_semi_sync_slave_enabled
表示从上是否开启半同步复制功能，可以动态修改。可选值：ON\OFF
Rpl_semi_sync_master_clients
说明支持和注册半同步复制的已连Slave数
Rpl_semi_sync_master_net_avg_wait_time
master等待slave回复的平均等待时间，单位毫秒
Rpl_semi_sync_master_net_wait_time
master总的等待时间
Rpl_semi_sync_master_net_waits
master等待slave回复的的总的等待次数
Rpl_semi_sync_master_no_times
master关闭半同步复制的次数
Rpl_semi_sync_master_no_tx
master没有收到slave的回复而提交的次数
Rpl_semi_sync_master_status
ON是活动状态（半同步），OFF是非活动状态（异步）
Rpl_semi_sync_slave_status
Slave上的半同步复制状态，ON表示已经被启用，OFF表示非活动状态
Rpl_semi_sync_master_tx_avg_wait_time
master花在每个事务上的平均等待时间
Rpl_semi_sync_master_tx_wait_time
master总的等待时间
Rpl_semi_sync_master_tx_waits
master等待成功的次数，即master没有等待超时的次数，也就是成功提交的次数
Rpl_semi_sync_master_wait_pos_backtraverse
master提交后来的先到了，而先来的还没有到的次数
Rpl_semi_sync_master_wait_sessions
前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx
master成功接收到slave的回复的次数，即半同步模式成功提交数量
主从并行复制
5.5单线程复制
5.6基于库的并行复制
从库为每一个库分配了一个线程。以此提高复制的效率。
开启并行复制SQL线程就变为了coordinator线程
判断可以并行执行，那么选择worker线程执行事务的二进制日志
如不可以并行执行，如DDL或是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志
5.7基于组复制的并行复制
所有处于 prepare 阶段的事务同属于一个组，一个组内的事务可以并行提交
同组事务到了 slave 端就可以并行处理
判断事务是否在同一组
开启GTID的情况
last_committed 相同的属于同一个组
last_committed = 0 中只有 1 个事务
last_committed = 1 中有 2-8 7个事务
假设 slave 端的 parallel 是 10，那么就可以同时执行7个事务
未开启GTID情况
Anonymous_Gtid
匿名GTID
存在的问题
如何设置binlog_group_commit_sync_delay的大小控制好并发以及延长等待的问题
组大小越大，对事物的延迟等待就越高
相关参数
slave-parallel-type
DATABASE（默认）
基于库的并行复制方式
LOGICAL_CLOCK
基于组提交(逻辑时钟)的并行复制方式
slave_parallel_workers
并发数，如为0则为单线程，大于1则为多线程
binlog_group_commit_sync_delay
多少微妙提交一次
binlog_group_commit_sync_no_delay_count
在等待上面参数超时之前达到多少个事务提交一次
slave_preserve_commit_order
强一致性提交顺序，控制Slave上的binlog提交顺序和Master上的binlog的提交顺序一样，保证GTID的顺序 经常是“跨DB”操作，那么可以考虑使用此参数限定顺序 要求任何worker线程执行事务时，只有当前事务中之前的所有事务都执行后（被其他worker线程执行），才能执行和提交 每个事务中，都记录了当前GTID的privious GTID，只有privious GTID被提交后，当前GTID事务才能提交 开启该参数可能会有一点的消耗，因为会让slave的binlog提交产生等待
强一致性的提交顺序
slave_pending_jobs_size_max
在队列中pening等待执行当前事务需要的内存大小，该值要比主库的max_allowed_packet大
开启组复制
[mysqld] # 只需在slave端设置 slave_parallel_workers = 4 ###并行复制的线程数 slave_parallel_type = LOGICAL_CLOCK ###并行复制的类型，默认database master_info_repository = table relay_log_info_repository = table relay_log_recovery = 1
5.7.22以上基于写集的并行复制
writeset
MySQL 会对这个提交的事务中的一行记录做一个 HASH值，这些 HASH 值称为 writeset。writeset会存入一张 HASH 表。其他事务提交时会检查这张 HASH 表中是否有相同的记录，如果不相同，则视为同组，如果有相同，则视为不同组
writeset的思想是：不同事物修改了不同行的数据，那么可以视为同一组
WriteSet是站在组提交这个巨人的基础之间建立起来的，且是在master上做的自适应打包分组
判断是否同一组：与组提交一样，通过last_committed，sequence_number
开启writeset(主库)
set global binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
关闭writeset
set global binlog_transaction_dependency_tracking = COMMIT_ORDER
解决的问题
组提交的尿性延迟问题
相比“组提交”来说更加灵活
并发度和性能上组提交会更加好
在一些WriteSet没有办法是否冲突时，能平滑过度到“组提交”模式
相关参数
哈希表存储大小:binlog_transaction_dependency_history_size
最高并发可达到该大小的一半左右，如12500
多源复制
mysql5.7之后支持N个Master对应1个Slave
不同的主库通过cannal来区分
延迟复制
允许Slave延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失
CHANGEMASTERTOmaster_delay=3600--落后Master服务器1个小时
常用于备份架构设计中
级联复制
以从库作为主库再做一级复制
全同步复制
MGR
PXC
GTID
全局事务ID(Global Transaction ID)Mysql5.6开始
强化数据库的主备一致性， 故障恢复及容错能力。取代过去传统的主从复制
主备切换的情况下其他slave可以自动在新主上找到正确的复制位置
基于GTID的复制可忽略已经执行过的事务减少数据发生不一致的风险
组成
GTID是由server_uuid和事务id组成：GTID=server_uuid:transaction_id
server_uuid
MySQL第一次启动时自动生成唯一值并持久化到auto.cnf文件
transaction_id
从1开始的自增计数表示在这个主库上执行的第n个事务，事务与GTID之间1:1映射
一组连续的事务用"-"连接的事务序号范围表示
如：b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-5
作用
发生切换时通过内部机制自动找点同步
实现多线程复制（基于库）
多线程复制是基于组提交方式实现的，而组提交信息是存储在GTID中
复制实现的工作原理
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中
2、slave端的I/O线程将变更的binlog，写入到本地的relay log中
Slave连接到Master时，会把gtid_executed中的gtid发给Master，Master会自动跳过这些事务，只将没有复制的事务发送到Slave去
3、SQL线程从relaylog中获取GTID，再对比slave端的binlog是否有记录（MySQL5.6 slave端必须开启binlog）
4、如果有记录，说明该GTID的事务已经执行，slave会忽略
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果再没有就用全部扫描
使用限制
1、 MySQL5.7之后才开始支持动态切换GTID相关的参数
2、 不支持CREATE TABLE ... SELECT statements
3、 不支持CREATE TEMPORARY TABLE statements inside transactions
4、 transaction or statement 既更新了事务表又更新了非事务表
5、 使用GTID复制从库跳过错误时，不支持执行sql_slave_skip_counter参数的语法
相关参数
gtid_mode
是否开启GTID复制功能
OFF
不产生GTID，Slave只接受不带GTID的事务
OFF_PERMISSIVE
不产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
ON_PERMISSIVE
产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
ON
产生GTID，Slave只能接受带GTID的事务
enforce-gtid-consistency
启动强制GTID的一致性
如果开启GTID功能则此参数必须要开启
slave在做同步复制时，无须找到binlog日志和POS点，直接change master to master_auto_position=1即可
gtid_executed
在当前实例上执行过的GTID集合，包含了所有记录到binlog中的事务
设置set sql_log_bin=0后执行的事务不会生成binlog事件，也不会被记录此中
执行reset master可以将该变量清空
gtid_purged
记录已经被清除了的binlog事务集合
是gtid_executed的子集，只有gtid_executed为空时才能手动设置该变量，此时会同时更新gtid_executed为和gtid_purged相同的值
gtid_executed为空意味着没有启动过基于GTID的复制或执行过reset master
执行reset master时同样也会把gtid_purged置空
即始终保持gtid_purged是gtid_executed的子集
gtid_next
会话级变量，指示如何产生下一个GTID
AUTOMATIC
自动生成下一个GTID，实现上是分配一个当前实例上尚未执行过的序号最小的GTID
ANONYMOUS
设置后执行事务不会产生GTID
显式指定的GTID
可以指定任意形式合法的GTID值，但不能是当前gtid_executed中的已经包含的GTID，否则下次执行事务会报错
binlog_gtid_simple_recovery(5.7.7之后)
mysql启动或重启时，mysql在搜寻GTID时如何迭代使用binlog文件
开启(5.7默认，建议)
mysql-server只需打开最老的和最新的这2个binlog文件
gtid_purged和gtid_executed值根据这些文件中的Previous_gtids_log_event或者Gtid_log_event计算得出
关闭(5.6默认)
所有的binlog都要被检查。可能需要非常长的时间
session_track_gtids
控制用于捕获GTIDs和在OK PACKE返回的跟踪器
OFF
关闭
OWN_GTID
返回当前事务产生的GTID
ALL_GTIDS
返回系统执行的所有GTID，也就是GTID_EXECUTED
gtid_owned
表示正在执行的事务的gtid以及对应的线程ID
gtid_executed_compression_period
表示控制每执行多少个事务，对此表（mysql.gtid_executed）进行压缩
默认1000个事务
5.7新特性
在线开启和关闭GTID
存储GTID信息到表中mysql.gtid_executed，slave无需开启binlog
GTID信息的记录
开启了binlog
在切换binlog时将当前binlog的所有GTID插入gtid_executed表中
未开启binlog
每个事务在提交之前会执行一个等价的INSERT的操作
此操作是该事务的一部分，和事务的其他操作整体保持原子性。 需要保证gtid_executed是innodb存储引擎
主从延迟
大事务延迟
状态为:reading event from the relay log
一个事务主库执行多久，从库回放就需要多久
大表DDL延迟
状态为:altering table
长期未提交的事务延迟
会造成延迟的瞬时增加
没有主键或者唯一键
状态为:system lock 或者 reading event from the relay log
产生的原因：event回放时候使用了二级索引让回放速度慢且进行了大量的内存数据查找造成了CPU 100%而没有I/O的现象
解决方法：增加主键
innodb层锁造成延迟
状态为:system lock 或者 reading event from the relay log
从库参数设置不合理
从库大批量查询
双主模式
存在Id重复和更新丢失问题
MMM
MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双主故障切换的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一个节点进行写入操作。下图是基于MMM实现的双主高可用架构。
主从MHA
MHA（Master High Availability）是一套比较成熟的 MySQL 高可用方案，也是一款优秀的故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA还支持在线快速将Master切换到其他主机，通常只需0.5－2秒目前MHA主要支持一主多从的架构，要搭建MHA，要求一个复制集群中必须最少有三台数据库服务器。
架构图
子主题
子主题
组成
MHA Manager（管理节点）
MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。负责检测master是否宕机、控制故障转移、检查MySQL复制状况等。
和MHA Node（数据节点）
MHA Node运行在每台MySQL服务器上，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点，负责保存和复制master的二进制日志、识别差异的中继日志事件并将其差异的事件应用于其他的slave、清除中继日志
原理
MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。
故障切换流程
（1）从宕机崩溃的master保存二进制日志事件（binlogevents）；
（2）识别含有最新更新的slave；
（3）应用差异的中继日志（relaylog）到其他slave；
（4）应用从master保存的二进制日志事件（binlogevents）；
（5）提升一个slave为新master；
（6）使其他的slave连接新的master进行复制。
优点
自动故障转移快
主库崩溃不存在数据一致性问题
性能优秀，支持半同步复制和异步复制
一个Manager监控节点可以监控多个集群
缺点
只支持BINLOGV4版本，要求MySQL5.0或更高版本。
候选master节点必须开启log-bin参数，如果所有从节点都为开启，则不进行故障转移
在MHA0.52版本前不支持多master模式
HA默认不支持多级主从复制，通过修改配置文件和设置multi_tier_slave参数
Orchestrator
Orchestrator 是另一款开源的 MySQL 高可用套件，除了支持 failover 的切换，还可通过Orchestrator 完成 MySQL 数据库的一些简单的复制管理操作 MHA 的升级版，而且提供了 HTTP 接口来进行相关数据库的操作，比起 MHA 需要每次登录 MHA Manager 服务器来说，方便很多
架构图
子主题
MGR
官方在 MySQL 5.7 版本推出的一种基于状态机的数据同步机制。与半同步插件类似，MGR 是通过插件的方式启用或禁用此功能
架构图
子主题
子主题
使用场景
特别适合应用于对于数据一致性要求极高的金融级业务场景
组件
MGR集群
Mysql Shell
MySQL Shell 用来管理 MGR 集群的创建、变更等操作
Mysql Router
MySQL Router 是一个轻量级的代理，用于业务访问 MGR 集群中的数据，当 MGR 发生切换时（这里指 Single Primary 模式），自动路由到新的 MGR 主节点为了减少引入 MySQL Router 带来的性能影响，官方建议 MySQL Router 与客户端程序部署在一起
原理
MGR之间的数据同步并没有采用复制技术，而是采用GCS（GroupCommunicationSystem）协议的日志同步技术。
GSC本身是一种类似Paxos算法的协议，要求组中的大部分节点都接收到日志，事务才能提交
Paxos算法，集群的节点要求数量是奇数个
模式
单主
单主模式只有1个节点可以写入
MGR可以自动进行Failover切换，不用依赖外部的各种高可用套件，所有的事情都由数据库自己完成
多主
多主模式能让每个节点都可以写入。
如果存在变更同一行的冲突，MySQL会自动回滚其中一个事务，自动保证数据在多个节点之间的完整性和一致性。
缺点与限制
仅支持InnoDB表，并且每张表一定要有一个主键；
目前一个MGR集群，最多只支持9个节点；
有一个节点网络出现抖动或不稳定，会影响集群的性能；
表分区
数据量不是十分大时，可以起到和分库分表类似的效果，同时对于需要删除旧数据的业务来说，十分适合，按业务分区，删除时只需要删除对应分区的.ibd文件，效率十分高。特点：1.MySQL 在第一次打开分区表的时候，需要访问所有的分区；2.在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；3.在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。分区策略：1.MyISAM 分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由 server 层控制。通用分区策略，是 MySQL 一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。从 2.MySQL 5.7.9 开始，InnoDB 引擎引入了本地分区策略（native partitioning）。这个策略是在 InnoDB 内部自己管理打开分区的行为。
将整个表按每个分区分成多个ibd文件
对于引擎层来说，这是 多个表；
对于 Server 层来说，这是 1 个表。
分库分表
垂直拆分
拆分后数据量不变，解决表中字段过多的问题。优点：1.降低锁粒度2.冷热数据分离3.略微提升查询效率缺点：1.数据存在冗余2.可能需要join3.需要修改应用层代码
一个库中的表拆到多个库
一个表拆按字段和业务成多个表
水平拆分
解决单表数据量过多的问题水平拆分重点考虑分片规则：例如范围、时间或Hash算法等。优点：1.提升读写效率2.按时间分片可做到冷热隔离缺点：1.读写复杂化2.可能需要跨库操作3.需要添加路由层
分片
分片（Sharding）就是用来确定数据在多台存储设备上分布的技术。Shard这个词的意思是“碎片”，如果将一个数据库当作一块大玻璃，将这块玻璃打碎，那么每一小块都称为数据库的碎片（Database Sharding）。将一个数据库打碎成多个的过程就叫做分片，分片是属于横向扩展方案。1.分片：表示分配过程，是一个逻辑上概念，表示如何实现2.分库分表：表示分配结果，是一个物理上概念，表示最终实现的结果
范围
hash取模
一致性hash
扩容方案
停机扩容
平滑扩容
平滑扩容方案能够实现n库扩2n库的平滑扩容，增加数据库服务能力，降低单库一半的数据量。其核心原理是：成倍扩容，避免数据迁移。1.新增一倍数据库2.配置双主进行数据同步（先测试、后上线）3.数据同步完成之后，配置双主双写（同步因为有延迟，如果时时刻刻都有写和更新操作，会存在不准确问题）4.数据同步完成后，删除双主同步，修改数据库配置，并重启；5.此时已经扩容完成，但此时的数据并没有减少，新增的数据库跟旧的数据库一样多的数据，此时还需要写一个程序，清空数据库中多余的数据，如：User1去除 uid % 4 = 2的数据；User3去除 uid % 4 = 0的数据；User2去除 uid % 4 = 3的数据；User4去除 uid % 4 = 1的数据；