mysql

MySql

MySql简介

mysql是最流行的关系型数据库之一，体积小，速度快，开源免费，简单易用，维护成本低，易于扩展，高可用。

演变过程

• 单机单库：当数据量太大或者读写操作量太大时，会超出一台服务器的承受能力
• 主从架构：结局了高可用和读扩展问题，主库宕机也可以通过主从切换保证高可用，主库抗写压力，从库分担读压力，对于写少读多的应用，主从模式就可以胜任了。但是数据量过大超出一台服务器时问题依然存在，写操作太多超出一台服务器承受的情况下，依然不能承受。
• 分库分表：进行水平拆分，每个实例都有全部数据的1/n的数据，分表分库的关键是数据如何路由，如何保持数据一致性。
• 云数据库：将mysql作为一个服务，服务提供商负责解决可配置，可扩展，高可用等问题。

体系概述

• 客户端连接器：Native ，JDBC，ODBC等支持多种语言。
• 服务层：连接池（对读写场景性能提升很大），系统管理控制工具（备份之类），SQL接口，解析器（接收请求的SQL解析并进行语法检查），查询优化器（生成查询语句的执行计划），缓存
• 存储引擎层：插件式可插拔，存储引擎针对的是数据库表，两张表可能是不同的存储引擎。

系统文件层：

日志文件

错误日志：默认开启

show variables like '%log_err%';

通用查询日志：默认关闭

show variables like '%general%';

二进制日志：记录了队mysql数据库的更改操作，记录语句发生时间，执行时长等，不记录select show等不修改数据库的sql，默认关闭

show variables like '%log_bin%';-- 二进制是否开启 
show variables like '%binlog%'; -- 系统参数查看
show binary logs; --查看日志文件

慢查询日志：记录所有查询时间超时的SQL ,默认时间10秒，默认开启

show variables like '%slow_query%'; -- 查询慢查询是否开启，日志文件
show variables like '%long_query_time%';-- 查询慢查询超时时间
set long_query_time=5; -- 设置慢查询超时时间为5秒

配置文件：存放了MySql中所有的配置文件

数据文件：

• db.opt：记录库的字符集和校验规则
• frm：存储元数据信息，包括表结构定义提信息，每张表有一个frm文件
• MYD：MyISAM存储引擎专用，存放MyISAM表数据，每张表有一个
• MYI：MyISAM存储引擎专用，存放表相关的索引
• ibd和IBDATA：存放InnoDB的数据文件（包含索引）

SQL运行机制概述

1. 建立连接，基于通信协议建立连接，通信方式是半双工（全双工为同时发送和接收数据，半双工指的是某一时刻要么发送数据要么接收数据），对于每一个MySQL连接都有一个线程来标识。

   show processlist; -- 查看线程状态
   show full processlist; -- 查看完整线程状态
   -- id 线程id,可以使用kill xx 杀掉
   -- user 启动线程的用户
   -- host 发送请求的客户端ip和端口
   -- db 哪个库在执行
   -- command 线程执行的操作命令
   -- time 表示线程状态
   -- info 一般记录操作语句，默认显示前100个字符，想查看完整的要使用show full processlist语句;
   

2. 查询缓存，如果命中直接返回，缓存的是查询结果和SQL语句，执行select语句会先查缓存判断是否有可用的记录集，判断语句和参数是否完全相同。

   show variables like '%query_cache%'; -- 查询缓存是否启用，空间大小，限制等
   show variables like '%Qcache%'; -- 查询详细缓存相关信息，缓存块，可用缓存空间，缓存了多少等
   -- 即使开启查询缓存，以下的SQL也不会缓存
   -- 查询语句使用SQL_NO_CACHE
   -- 查询结果大于query_cache_limit 设置
   -- 查询结果中有一些不确定参数，比如now()
   

3. sql解析器检查语法并且生成解析树，预处理器根据规则进一步检查解析树是否合法，最后生成新的解析树

4. 优化查询器生成执行计划，优化器分为两种静态优化（编译时优化），动态优化（运行时优化）

   • 等价交换策略

     • 5=5 and a>5 改成 a>5
     • a<b and a=5 改成 b>5 and a =5

   • 优化count，min，max等函数

     • InnoDB引擎min函数只要找索引最左边

     • InnoDB 引擎max函数只要找索引最右边

     • MyISAM引擎count(*) 不需要计算，直接返回

   • 提前终止查询

     • 使用limit查询，获取limit所需数据，就不在遍历后面的数据

   • in优化

     • mysql对in查询会先排序然后采用二分查找数据 比如 where id in (2,1,3)变成in(1,2,3)

5. 查询引擎执行查询

   查询引擎负责执行sql语句，通过对应的API接口和底层存储引擎或者文件交换，并将结果返回给客户端，如果开启查询缓存，先将查询结果缓存起来，如果返回结果过多，采用增量返回。

MySql的存储引擎

存储引擎位于MySql体系架构中位于第三层，负责MySql数据的存储和提取,它是根据MySql提供的文件访问层接口定制的一种文件访问机制，这种机制叫做存储引擎。

使用show engines;命令可以看到存储引擎有哪些

可以看到默认使用InnoDB作为存储引擎。从5.5版本开始使用InnoDB代替MyISAM作为默认存储引擎

InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全。

MyISAM：不支持事务和外键，访问速度快。

Memory：利用内存创建表，访问速度非常快，而且默认使用了Hash索引，但是一旦关闭，数据就会丢失。

Archive：归档型引擎，仅支持insert和select语句。

Csv：以csv格式文件进行数据存储，由于文件限制，所有的列必须强制指定not null，不支持索引和分区，适合做数据交换的中间表。

BlackHole：黑洞，只进不出型的，所有插入数据都不会保存。

Federated：可以访问远端mysql数据库中的表，一个本地表，不保存数据，访问远程表内容。

MRG_MyISAM：一组MyISAM表组合，要求表结构必须相同，merge表本身没数据。

InnoDB和MyISAM的区别

• 事务和外键

  InnoDB支持事务和外键，适合大量的insert或update操作，MyISAM不支持事务和外键，它提供高速的存储和检索，适合大量的select查询操作。

• 锁机制

  InnoDB支持行级锁，锁定指定记录，基于索引实现加锁。MyISAM支持表级锁，锁定整张表。

• 索引结构

  InnoDB使用聚簇索引，索引和记录一起存储，既缓存索引，也缓存记录。MyISAM使用非聚集索引，索引和记录分开。

• 并发处理能力

  InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。MyISAM使用表锁，会导致写操作并发率低，读之间不阻塞，读写阻塞。

• 存储文件

  InnoDB：.frm（表结构文件），.ibd（数据文件）。最大存储64TB数据

  MyISAM：.frm（表结构文件），.MYD（表数据文件），.MYI（索引文件）从MySql5.0开始最大存储256TB数据

两种引擎如何选择

• 是否需要事务
• 是否存在并发修改
• 是否追求快速查询且数据修改较少

绝大多数场景，推荐使用InnoDB引擎

InnoDB存储结构

InnoDB从MySql5.7版本开始作为默认引擎，主要分为磁盘和内存两部分

内存存储

• Buffer Pool：内部最小单元为Page页，存放缓存记录和索引，Page页默认大小为16K，数据结构为链表。

  • Page页管理机制
    Page有三种类型：

    • free page：空闲page，未被使用
    • clean page：使用过的page，数据没有被修改过
    • dirty page：脏页，被使用的page，数据被修改过，页中的数据和磁盘中的数据不一致。

    通过三种链表结构进行维护和管理：

    • free list：空闲缓冲区，管理free page
    • flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序，先刷修改时间早的数据。dirty page数据即存在于flush list 也存在于 lru list ，但互不影响，LRU链表管理页的可用性和释放，flush list管理页的刷盘操作。
    • lru list：表示正在使用的缓冲区，管理clean page 和dirty page，缓冲区以midpotin为边界，前面的链表称为new链表区存放经常访问的数据大小占63%，后面的链表为old链表区存放使用较少的数据占37%。

  • 改进型LRU算法维护

    普通的LRU算法就是末位淘汰法，新的数据从链头加入，释放空间从链尾淘汰，改进型LRU算法，链表分为new和old两部分，数据插入在midpoint，如果数据很快访问了数据会向new区域移动，如果没有访问数据会逐步的向old区域移动，等待淘汰。每当有新的数据读取到buffer pool，InnoDB会判断是否有free page，如果有将free page从free list 删除并且放入LRU list，如果没有free page ,会根据LRU算法淘汰末尾的page，分配给新的页。

  • Buffer Pool配置参数

• Change Buffer：写缓冲区默认占据25%最大允许50%的Buff pool空间，如果进行数据修改操作且缓冲区没有时，将数据写到Change Buffer中，等到查询时候从磁盘加载数据，然后和Change Buffer进行合并，再写入Buffer Pool，占据了一部分Buffer Pool的空间。

  写缓冲区仅适用于非唯一索引，如果设置了唯一索引，再修改时InnoDB必须做唯一性校验，必有先在磁盘上查出来，IO不可避免所以不适用。

• Log Buffer：存入redo，undo日志。主要记录InnoDB引擎日志，在DML操作时会产生redo和undo日志。

• Adaptive Hash Index：自适应Hash索引，存储引擎会自动地对频繁使用的索引建立Hash索引。

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_buufer_pool_instances可以设置为多个，这样可以避免缓存争夺。

Change buffer：大小

show variables like ‘%innodb_change_buffer_max_size%’;

Log Buffer：

select @@innodb_log_file_size/1024/1024; 大小默认48M，

innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1，

• 0：每隔一秒写文件和刷盘操作（写文件LogBuffer->OS cache，刷盘OS cache->磁盘文件），最多丢失1秒数据
• 1：事务提交，立刻写文件和刷盘，数据不丢失，但是会频繁的IO操作。
• 2：事务提交，立刻写日志文件，每1秒进行刷盘操作

磁盘结构

Tablespace：

• System Tablespace(ibdata1)，默认大小12M自动扩展，存放创建表的数据和索引，多个表共享。
  • InnoDB Data Dictionary 数据字典，查找表，索引元数据。
  • Doublewrite Buffer：dirty page的数据会先写入Doublewrite Buffer，写入速度快，innodb_doublewrite，默认为on表示Doublewrite Buffer开启，innodb_flush_method，默认为空，建议设置为o_direct，buffer pool 写入数据时，直接将数据写入Doublewrite Buffer。
  • Undo logs：事务开始前把之前的数据备份，用于例外情况的回滚事务。
• File-Per_Table，innodb_file_per_tables = on 时，数据和索引会存入独立表空间，默认开启。
• General Tablespaces，通用表空间
• Undo Tablespaces：撤销表空间
• Temporary Tablespace：关闭的时候临时表空间的数据会被移除

版本演变

InnDB线程模型

• IO Thread

  InnoDB中使用了大量的AIO，来做读写处理，可以极大提高数据库的性能，早期有4个IO Thread，write，read，insert buffer，log thread。后来版本将write thread 和read thread 分别增加到了4个一共十个。

  • read thread：负责读取操作，将数据从磁盘加载到缓存page页
  • write thread：负责写操作，将缓存脏页刷新到磁盘。
  • insert buffer thread：负责将写缓冲区内容刷新到磁盘。

  show engine innodb status; -- 查看引擎状态
  

  

• Purge Thread

  事务提交后，旧的undo日志不再需要，因此Purge Thread回收已分配的undo页。

  show variables like '%innodb_purge_thread%' -- 大于1表示开启，数量为开启个数
  

• Page Cleaner Thread

  将脏数据刷新到磁盘，脏数据刷盘后相应的redo log 也可以覆盖，即可以同步数据，有能达到redo log循环使用的目的，会调用write thread

  show variables like '%innodb_page_cleaners%'
  

• Master Thread

  是InnoDB的主线程，负责调度其他各个线程，优先级最高，作用是将缓冲池数据异步刷新到磁盘，保证数据一致性。包含：脏页刷新（调用page cleaner thread），undo页回收（调用purge thread），redo日志刷新（调用log thread），合并写缓冲等。内部有两个主要处理，分别是每隔1秒，和每隔10秒的
  每秒的操作

  • 刷新日志缓冲区，刷到磁盘
  • 合并写缓冲区，根据IO读写压力决定是否做
  • 刷新脏页到磁盘，根据脏页比例达到75%才操作（innodb_max_dirty_page_pct-脏页达到多少比例刷新，innodb_io_capacity-每次刷多少页）

  每10秒操作：

  • 刷新脏页到磁盘（Page Cleaner Thread）
  • 合并写缓冲区
  • 刷新日志缓冲区数据
  • 删除不用的undo页（innodb_purge_batch_size默认300表示一次可以删除300页）

InnoDB数据文件

• InnoDB文件存储结构

  InnoDB数据文件存储结构：一个ibd文件–>Segment（段）–>Page（页）–>Row（行）

  • Tablespace： 表空间，存储多个ibd数据文件，用于存储表的记录和索引，一个文件包含多个段。
  • Segment： 段，用于管理多个Extent，分为数据段（Leaf node segment），索引段（Non-leaf node segment），回滚端（Rollback segment）。一个表最少会有两个segment，一个管理数据，一个管理索引，每多创建一个索引，会多两个segment。
  • Extent：区，一个区固定包含64个连续的页，大小为1M。当表空间不足时需要分配新的页资源，会直接分配一个区，不会一页一页分配。
  • Page：页，存储多个Row行记录，大小为16K，包含多种页类型，比如数据源，Undo页，系统页，事务数据页，大的BOLOB对象页。
  • Row：行，包含记录字段值，事务ID，滚动指针（Roll Pointer）

  Page是文件最基本的单位，page由Page Header，Page Trailer，和Page Body组成

InnoDB文件存储格式

查看文件格式

• 通过 show table status
• 通过information_sechema 比如 select * from information_schema.innodb_sys_tables;

文件格式（File-Format）

目前支持两种Antelope和Barracuda。

Antelope：先前未命名，最原始的Innodb，支持两种格式：COMPACT和REDUNDANT

Barracuda：新的文件格式，支持InnoDB所有行格式，包括：COMPRESSED和DYNAMIC

行格式（Row-Format）

每个表的数据一定是分若干页存储的，每个页中采用B树结构存储数据。如果某些字段信息过长，无法存储在B树节点中，InnoDB会单独分配空间，此时称为溢出页。该字段被称为页外列。

• REDUNDANT：早期的行格式，会将变长列的678字节存储在B树列，其余的都会存储到溢出页，对于大于等于678字节的固定长字段InnoDB会把它转换为变长字段，以便在页外存储
• COMPACT：与REDUNDANT相比，减少了20%的行存储空间，代价是增加某下操作的CPU使用量，如果系统负责受缓存命中率和磁盘速度限制，此种模式会快些。
• DYNAMIC：InnoDB将表中可变长的列完全存储在页外，B树中只存20字节的索引，指向溢出页。
• COMPRESSED：与DYNAMIC存储特性和功能相似，但增加了对表和索引数据的压缩支持。

-- 修改行格式
alter table 表名 row_format=格式类型;

Undo Log

Undo：插销或取消，以撤销操作为目的，返回某个特定状态的操作。

Undo Log：数据库事务开始之前，会将修改的数据存储到Undo日志里，当数据回滚或者崩溃时，可以利用Undo日志，撤销未提交的事务对数据库产生的影响。在事务开始之前就产生了。事务提交时，并不会立刻删除Undo Log，InnoDB会将该事务对应的Undo Log 入到删除列表当中，会通过Purge Thread来回收。例如执行一个delete，Undo Log 会记录一个insert，执行一个update，Undo Log 会记录另一个update。Undo Log采用段的方式管理和记录，在InnoDB数据文件中包含一种rollback segment，内部包含1024个undo log segment。

Undo Log 的作用

• 实现事务原子性
• 实现多版本并发控制

Redo Log

Redo：重做，以恢复操作为目的，在数据库发生意外时重现操作。

Redo Log：事务中修改任何数据，将最新的数据被分到位置，被称为重做日志。

生成和释放：事务操作的过程中就会生成，事务提交时写入log buffer，脏页的数据写入磁盘后才释放，仅是擦除日志。

Redo Log写入机制：Redo Log文件内容以顺序循环方式写入，写满时则回溯到第一个文件，进行覆盖写。

Binlog日志

Bin log是MySQL自己的日志，全程Binary Log，记录所有表结构变更以及表数据修改的二进制日志，不会记录select和show这类操作。BinLog日志以事件形式记录，开启BinLog主要有两个场景：

• 主从复制：主库开启Bin log，这样就可以把Bin Log 传递给从库，从库拿到后就可以达到主从数据一致。
• 数据恢复：通过MySQL binlog工具来恢复数据

BinLog文件默认命名为：主机名_binlog-序列号格式，例如oak_binlog-00001。

文件记录模式有STATEMENT，ROW，MIXED三种，具体含义如下。

• ROW：日志记录每一行数据修改情况，然后在slave端对相同的数据修改，优点是能清楚记录每一行数据的修改细节，能完全实现主从数据同步，和数据恢复，缺点是批量操作容易产生大量的日志，尤其是alter table会让日志暴涨。
• STATEMENT：每一条SQL语句会记录到Binlog，从库会重新执行主库执行的日志，优点是日志量小，减小磁盘IO，缺点是遇到SQL中含有可变值时会造成数据不一致，例如now()，last_insert_id()，等函数。
• MIXED：混合模式，根据SQL语句区分一般情况下使用STATEMENT，对于STATEMENT无法复制的操作使用ROW模式保存Bin log。

BinLog文件结构

BinLog是各种log event的集合。常用的有query event，row eveent等。

• BinLog写入机制
  • 根据记录模式和操作出发envent事件生成log event（事件触发执行机制）。
  • 将事务执行过程产生log event写入缓冲区，每个事务线程都有一个缓冲区。log event 保存在binlog_cache_mngr数据结构中，在该结构有两个缓冲区，stmt_cache用于从放不支持事务的信息，另一个trx_cache用于存放支持事务的信息。
  • 事务提交阶段会将产生的log event 写入外部binlog文件中。不同的事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在文件中是连续的，不会插入别的信息。

Binlog文件操作

• Bin log状态查看

  show variables like 'log_bin';
  show variables like '%log_bin%';
  

• show binlog events命令

show binary logs;
show master status; -- 查看正在给哪个文件写入

• mysql binlog 命令

#系统命令
mysqlbinlog "mysqlbinlog.000002"

可以通过设置expire_logs_days参数来启动自动清理binlog，默认值为零表示未启用，单位为天，设置为1表示超过1天binlog文件会被清除。

• Redo Log 和 Binlog区别

  1. Redo Log属于InnoDB引擎功能，而Bin log则是MySQL Server自带功能，并且以二进制文件记录。

  2. Redo Log属于物理日志，记录该数据页状态内容，Bin log是逻辑日志，记录更新过程

  3. Redo Log是循环写的，日志空间大小是固定的，Bin log是追加写入写完一个写下一个，不会覆盖使用

  4. Redo Log是作为服务异常宕机后事务数据自动恢复使用，BinLog可以作为主从复制使用，Binlog没有自动crash-safe能力。

Mysql索引原理

索引类型

索引可以提升查询速度，会影响where查询order by排序，Mysql索引类型如下：

从索引存储结构划分：B Tree索引，Hash索引，FULLTEXT全文索引，R Tree索引

从应用层次划分：普通索引，唯一索引，主键索引，复合索引

从索引键值类型划分：主键索引，辅助索引（二级索引）

数据存储和索引键值逻辑划分：聚集索引（聚簇索引），非聚集索引（非聚簇索引）

1. 普通索引

   基本的索引类型，基于普通字段建立的索引，没有任何限制

   show index from table name;
   drop index indexname on tablename;
   create index indexname on tablename (columnname);
   alter table tablename add index indexname (columnname);
   create table tablename(), index indexname(columnname);
   

2. 唯一索引

   与基本索引类似，不同的就是索引的字段值必须唯一，但允许有空值，在船舰或修改表时追加唯一约束就会创建唯一索引。

   create unique index indexname on tablename(columnname);
   alter table tablename add unique index indexname(columnname);
   create table tablename(),unique indexname(columnname);
   

3. 主键索引

   它是一种特殊索引，不允许有控制，在创建或修改表石追加主键约束即可，每个表只能有一个主键

   create table tablename(),primary key(columnname);
   alter table tablename add primary key(columnname);
   

4. 复合索引

   单一索引指的是索引列为一列的情况，当在多个列上建立索引时，这种索引叫复合索引（组合索引），复合索引在数据库操作期间所需的开销较小，可以代替多个单一索引。

   索引有窄索引和宽索引，窄索引指索引列是1-2列的索引，宽索引就是指列上超过2列的索引，设计索引的一个重要规则就是能用窄索引就不用宽索引，因为窄索引往往比款索引更有效。

   create index indexname on table(columnname1,columnname2);
   alter table tablename add index indexname (columnname1,columnname2);
   create table tablename(), index indexname(columnname1,columnname2);
   

5. FULLTEXT（全文索引）

   查询操作在数据了比较小时，可以使用like做模糊查询，但是对于大量文本数据检索，效率很低，如果使用全文索引，like查询效率会提高很多，在MySQL5.6之前只有MyISAM引擎支持全文索引，从MySQL5.6以后InnoDB也支持全文索引。InnoDB字段长度>=3且<=84时才会建立全文索引，MyISAM的范围是>=4且<=84，只有切词处理后才能模糊匹配，例如baaa需要切成b+aaa，默认采取等值匹配，a匹配a，而不会匹配ac，ab，如果想要匹配则要使用布尔模式。

   create fulltext index indexname on tablename(columnname);
   alter table tablename add fulltext index indexname(columnname);
   create table tablename(), fulltext index indexname(columnname);
   -- 查询用法 模糊查询 column 包含 xxxx的记录
   select * from tablename where match(column) against('xxxx');
   select * from tablename where match(column) against('xxxx*' in boolean mode);
   

索引原理

Mysql对索引的定义：是存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，也在数据文件中（InnoDB，ibd文件）利用数据页（page）存储。
• 索引可以加快检索的速度，但是会降低增删改操作的速度，索引维护是需要代价的。

二分查找法

二分查找是在有序的数组中查找指定数据的搜索算法。它的优点是等值查询，范围查询性能优秀，缺点是更新数据，新增数据，删除数据的维护成本高。

Hash结构

Hash底层由Hash表来实现，具有<key,value>的结构。Hash索引可以方便提供等值查询，但是范围查询就需要全表扫描。Hash索引在Mysql中主要应用在Memory引擎，和InnoDB的自适应Hash索引。InnoDB当注意到某下索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个Hash索引。能够快速定位频繁访问的索引页。

B+Tree结构

Mysql数据库索引普遍采用B+Tree结构，在B-Tree（B Tree）结构上做了优化。

B Tree

• 索引值和数据分布在整颗树中
• 每个节点可以存放多个索引值及对应的data数据
• 树节点中的多个索引值可以从左到右升序排列

B树的搜索：从根节点开始查找，对节点内的索引值序列采用二分发查找，如果命中结束查找，否则进入子节点重复查找的过程。直到所对应的节点指针为空或者已经是叶子节点才结束。

B+Tree

• 非叶子节点不存data数据，只存索引值，便于存储更多的索引值
• 叶子节点包含所有索引值和数据
• 叶子节点用指针连接，提高区间访问性能

相比B Tree来说B+Tree进行范围查找时，著需要定位两个节点的索引值，就可以利用叶子节点指针便利，而B Tree需要便利范围内的索引节点和数据，显然B+Tree效率更高。

聚簇索引和辅助索引

聚簇索引（聚集索引）

在InnoDB引擎中聚簇索引激素hi按照主键顺序构建的B+Tree结构，B+Tree的叶子节点就是行记录，行记录和主键值紧凑的存放在一起，这意味着InnoDB的主键索引就是数据本身。InnoDB表要求必须有聚簇索引，如果表定义了主键，则主键索引就是聚簇索引，如果没定义主键，则第一个非空的unique列作为聚簇索引，否则InnoDB会创建一个隐藏的row-id作为聚簇索引。

辅助索引（二级索引）

结构也是采用B+Tree结构，在叶子节点存储索引值和主键值，辅助索引占的空间比聚簇索引小很多，通常创建辅助索引为了提升查询效率。一个表只能创建一个聚簇索引，可以创建多个辅助索引。

非聚簇索引

MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引。

索引的分析和优化

EXPLAIN

MySQL提供了一个EXPLAIN命令，它可以对SELECT语句进行分析，并输出SELECT执行的详细信息，提供开发人员针对性优化

explain select * from user where id < 3 \G;

• select_type

  表示查询类型，常用的有：

  • SIMPLE：不包含子查询或union
  • PRIMARY：表示此查询是最外层查询
  • UNION：表示此查询是UNION的第二个或后续查询
  • DEPEDENT UNION：UNION中的第二个或后续查询使用到外边的查询结果
  • UNION RESULT：UNION的结果
  • SUBQUERY：子查询语句
  • DEPENDENT SUBQUERY：子查询语句用到外边的查询结果

• type

  表示存储引擎查询数据时采用的方式，是一个比较重要的属性，可以判断出查询是全表扫描还是基于索引的部分扫描，常用的有：

  • all：全表扫描性能最差
  • index：基于索引的全表扫描，先扫描索引再扫描全表数据
  • range：表示使用索引的范围查询
  • ref：表示使用非唯一索引
  • eq_ref：出现在多表连接join查询，表示前面表每个记录都只能匹配后面一行记录
  • const：使用主键或者唯一索引做等值查询
  • null：表示不用访问表，速度最快

• possible_keys：表示查询时候能用到的索引，并不一定真正使用

• key：表示真正实用的索引的名称

• rows：扫描的行数，需要读取多少行记录，可以直观了解到SQL的效率

• key_len：表示查询使用索引字节的数量，可以判断是否全部使用组合索引。key_len计算规则如下：

  • 字符串类型：字符串长度和字符集有关：latin1=1，gbk=2，uft8=4。

    • char(n): n * 字符集长度
    • varchar(n)：n*字符集长度+2

  • 数值型：

    • TINYINY：1
    • SMALLINT：2
    • MEDIUMINT：3
    • INT，FLOAT：4
    • BIGINT，DOUBLE：8

  • 时间类型：

    • DATE：3

    • TIMESTAMP：4

    • DATETIME：8

  • 字段属性

    null占用1字节，如果设置了not null 则没有此项

• Extra：表示很多额外信息，各种操作会在Extra提示相关信息：

  • Useing Where：表示查询需要通过索引回表查询数据。
  • Useing index：查询需要通过索引并且索引就可以满足查询需要的数据
  • Useing filesort：表示查询出来的结果需要额外排序，数据量小的话可以在内存做，因此有Usering filesort建议优化
  • Useing temprorary：查询使用到了临时表空间，比如分组，去重。

回表查询

InnoDB的索引有聚簇索引和辅助索引，通常下情况下，需要扫描两遍索引树，先通过辅助索引定位主键值，再通过聚簇索引定位行记录，这种查询叫做回表查询，它的性能比扫描一遍索引树低。

覆盖索引

非聚簇索引，查询所用的字段都在索引树上能覆盖，就会发生索引覆盖，这样就不用回表查询，效率会更高。实现索引覆盖的常见方法，把查询的字段建组合索引。

最左前缀原则

复合索引使用时遵循最左前缀原则，查询条件使用到了索引最左边的列就会走这个复合索引（字段和复合索引的全部字段重合时SQL执行优化器会帮忙排序此时顺序不重要）。

like查询

面试题：Mysql使用like查询时索引是否起作用？Mysql使用like模糊查询时索引是可以被使用的，但是是有条件的只有把%字符写道后面才会使用到索引

NULL查询

面试题：Mysql表的列含有null值，那么含该列的索引是否有效？null是特殊值，不能用=，<,运算符，对NULL做算术运算结果都是NULL，count不会包含NULL行，NULL比空字符需要额外的存储空间来表明是否为NULL值。Mysql可以在NULL的列上使用索引，但是MySQL不建议这样用，如果要用可以设置默认值，设置not null。

索引和排序

MySQL查询支持file sort和index两种方式排序，file sort是先把结果查出，然后在缓存或者磁盘中进行排序，效率较低，使用index指索引自动实现排序，不需要另作排序操作，效率会较高。如果使用EXPLAN分析SQL，给出Extra属性显示Using filesort表示使用了filesort排序，需要优化。如果Extra显示Using index表示覆盖索引，此时排序是索引排序。

• filesort会有两种排序算法：双路排序和单路排序。

  • 双路排序：需要两次磁盘扫描读取最终得到用户数据，第一次将排序字段拿出来然后排序，第二次读取其他字段的数据，最后合并返回。
  • 单路排序：从磁盘查询所有数据，然后在内存缓存中排序将结果返回，当数据量很大的情况下超出了sort_buffer的容量，会导致多次磁盘读取并且会创建临时表，最后产生多次IO，反而会增加负担。解决负担：少用select * ，增加sort_buffer_size容量和max_length_for_sort_data容量。

• 以下场景会index排序

  • ORDER BY 字句索引列组合满足最左前缀
  • WHERE 字句+ORDER BY子句索引列满足最左前缀

• 以下场景会file sort 排序

  • 对索引列同时使用了升序和降序排序

    explain select id from user order by age asc,name desc; -- 对应（age,name）索引
    

  • WHERE字句和ORDER BY字句满足最左前缀，但WHERE字句使用了范围查询

    explain select id from user where age>10 order by name; -- 对应（age,name）索引
    

  • ORDER BY 或者WHERE+ORDER BY索引列没有满足最左前缀

    explain select id from user order by name; -- 对应(age,name)索引
    

  • 使用不同的索引，Mysql每次只采用一个索引，ORDER BY 涉及两个索引

    explain select id from user order by name,age; -- 对应(name)、(age)两个索引
    

  • WHERE字句和ORDER BY 字句，使用了不同索引

    explain select id from user where name='tom' order by age; -- 对应 (name)、(age)索引
    

  • WHERE字句或者ORDER BY字句索引列使用了表达式，或者函数表达式

    explain select id from user order by abs(age); -- 对应(age)索引
    

查询优化

要做查询优化首先开启慢查询日志，记录查询较慢的SQL

set global slow_query_log = on;   -- 开启慢查询日志
set global slow_query_log_file = 'OAK-slow.log'; -- 指定慢查询日志文件名称
set global log_queries_not_using_indexes = on;  -- 表示记录没有使用索引的SQL就会记录日志，前提是slow_query_log状态为on
set long_query_time = 10; -- 慢查询时间阀值单位（秒）超过阀值就会记录日志

慢查询日志查看

文本方式查看

#Time: 2021-06-06T05:03:18.110267Z
#User@Host: root[root] @ localhost [::1] Id: 2
#Query_time: 0.344317 Lock_time: 0.004300 Rows_sent: 131072 Rows_examined: 1048576
SET timestamp=1622955798;
select * from slow where name =‘java’;

• time：日志记录的时间
• User@Host：执行的用户及主机
• Query_time：执行的时间
• Lock_time：锁表时间
• Rows_sent：发送给请求方的记录数，结果数量
• Rows_examined：语句扫描的记录条数
• SET timestamp：语句执行的时间点
• select…：执行的具体的SQL语句

慢查询优化

• 若何判断是否慢查询：查询所消耗的时间大于log_query_time所配置的阀值就是慢查询

• 如何判断是否使用了索引：使用EXPLAIN命令分析查看检查结果中key的值是否为null

• 是否用到索引就一定快：如果使用索引但是并没有缩小扫描的记录行数则失去了意义

查询是否所用索引，只是表示SQL语句执行过程，而是否慢查询由执行时间决定，使用索引和慢查询之间没有必然联系。

在使用索引时，不要只关注是否起作用，应该关注索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会提升

产生慢查询的原因总结

• 全表扫描：explain分析是type属性为ALL
• 全索引扫描：explain分析时type属性为index
• 索引过滤性不好：靠索引字段选型，数据数量和状态，表设计
• 频繁回表查询开销：尽量少用select * ，尽量使用覆盖索引

分页查询优化

• 一般分页

  select * from user limit 偏移量，行数
  select * from slow limit 10000,100000
  

  查询的时候查询的数量低于100时，时间基本没有太大差距，随着查询数量越大所花的时间越大，查询记录数量一样时，偏移量大于100时随着偏移量增大查询时间急剧增加。

• 利用覆盖索引优化

  select id from slow limit 10000,1000;
  

• 利用子查询优化

  select * from slow where id >= (select id from slow limit 10000,1) limit 1000;
  

Mysql事务和锁

ACID特性

一个逻辑单元要成为事务必须满足4个特性，ACID，原子性，一致性，隔离性，持久性。

• 原子性：事务是一个原子操作单元，对其数据修改要么全部执行，要么全都不执行

  ​ 修改->buffer pool 修改 ->刷盘。此时可能存在以下两种情况：

​ 1、事务提交，buffer pool 脏页没有刷盘，如何保证修改的数据生效？ Redo日志恢复

​ 2、事务没有提交，但是buffer pool 脏页刷盘了，如何保证不该存在的数据撤销掉？Undo日志撤销

• 持久性：一个事务一旦提交，对数据的改变是永久性的，后续操作或故障不应该有任何影响。通过启动时Redo log可以修复数据，从而保障事务的持久性，通过原子性可以保障逻辑上的持久性，通过存储引擎刷盘可以保证物理上的持久性

• 隔离性：指一个事务执行不能被其他事务干扰，一个事务内部的操作使用数据对气压的并发事务是隔离的。InnoDB支持隔离级别有4中，读未提交，读提交，可重复读，可串行化。

• 一致性：指事务开始之前和事务结束之后，数据库的完整性未被破坏，分为数据一致性和约束一致性，约束一致性值表结构以及外键索引等约束，数据一致性依赖于原子性，持久性，隔离性。

一致性可以理解为数据的完整性，完整性通过原子性，隔离性，持久性来保证，这三个特性通过Redo Undo log来保证。

ACID 4个特性中有三个与WAL有关（Write-Ahead-Logging 先写日志，后写磁盘）都需要通过Redo Undo日志来保证。

事务控制演进

并发事务

事务并发处理可能带来一些问题，比如：更新丢失，脏读，不可重复读，幻读等。

• 更新丢失：多个事务同时更新一条记录会产生更新丢失

  • 回滚覆盖：一个事务回滚操作时，把另一个事务提交的数据覆盖了
  • 提交覆盖：一个事务提交操作时，把另一个事务提交的数据覆盖了

• 脏读：一个事务读取到了另一个事务修改但未提交的数据。

• 不可重复读：在一个事务中，多次读取同一行记录前后读到的数据不一致

• 幻读：一个事务中多次按相同条件查询结果不一致，后续查询的结果和前面的结果不同，多或少了几行记录。

演进过程

1. 排队：最简单的方法，完全顺序执行所有事务，不需要加锁，每个时刻数据库只能执行一个事务，具有强一致性，处理性能低下。

2. 排他锁：引入锁后就支持并发处理事务，如果事务之间涉及相同的数据项，会使用排他锁（互斥锁），先进入的事务独占数据项后，别的事务被阻塞，直到前面的事务释放锁。

3. 读写锁：读写锁是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，这样两个事务就可以同时进行了，读写，写写之间还是需要增加排他锁。

4. MVCC：多版本控制MVCC，也就是copy on write 思想，MVCC除了支持读读并行，还支持读写，写读并行，但保持了数据一致性，实现读写并行。

   每次事务修改操作之前，都会在Undo日志记录修改前的数据状态以及事务号，该备份记录可以用于其他数据的读取，也可以用于必要时的数据回滚。

   MVCC目前只可以在Read Commited 和 Repetable Read两种隔离级别下工作

   在MVCC并发控制中，读操作可以分为两类：快照读，当前读

   • 快照读：读取的记录是快照版本（可能是历史版本），不用加锁（Current Read），普通select。
   • 当前读：读取的记录是最新版本，并且当前读返回的记录都会加锁，保证其他事务不会触发修改记录，select … for update 或 lock in share mode , insert/update/delete等

   具体更新过程：

   • 用排他锁锁定该行，并记录Redo log
   • 把改行修改前的值复制到Undo log
   • 修改当前的值，填写事务编号，使回滚指针指向Undo log中修改的行。
   • 接下来事务2操作时和上面一样，那么Undo log中则会有两个记录，并且通过回滚指针连接在一起，通过当前记录的混滚指针回溯到改行创建时的初始资源。

   MVCC已经实现读读，写读，读写并发处理，如果想进一步解决写写并发处理，可以采用下面两种方案：

   • 乐观锁
   • 悲观锁

隔离级别类型

一个事务和其他事务的相互影响程度被称为隔离级别，Mysql提供了以下4中隔离级别

• 读未提交：Read Uncommitted，只解决了回滚覆盖类型的更新丢失，但是可能发生脏读现象。
• 已提交读：Read Committed，只能读到其他会话已经提交的数据，解决了脏读。但可能发生不可重复读。
• 可重复读：Repeatable Read 可重复读，解决了不可重复读
• 串行化：事务串行进行，不会出现并发问题，但是处理能力下降。

事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）。可以根据系统特点选择合适隔离级别。事务隔离级别是针对InnoDB这个支持事务的引擎，与MyISAM引擎无关。

事务隔离级别和锁的关系

• 事务隔离级别是SQL92制定的标准，是事务并发控制的整体解决方案，本质上是对锁和MVCC的封装，隐藏了底层细节。
• 锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应的操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。
• 对用户来讲处理并发，首先应该选用隔离级别，一般来说使用时建议采用默认隔离级别，Mysql默认隔离级别是可重复读，Oracle和SQL server的默认隔离级别是读已提交，当选用的隔离级别不能解决并发问题和需求时，才有必要在开发中手动设置锁。

Mysql隔离级别的控制

查看当前数据库事务隔离级别和隔离级别的控制

show variables like 'tx_isolation'; -- 查看数据库当前隔离级别
select @@tx_isolation; -- 查看数据库当前隔离级别
set global tx_isolation = ''; -- 将隔离级别设置为对应的隔离级别，全局生效但是需要重新打开会话

锁机制

锁分类

MySQL中锁有不同的分类，从操作粒度可以分为表级锁，行级锁，页级锁。

• 表级锁：每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在M有ISAM，InnoDB等存储引擎
• 行级锁：每次操作锁住一行数据，锁的粒度最小，发生锁冲突概率最低，并发度最高，应用在InnoDB中
• 页级锁：每次锁定相邻一组记录，粒度介于表锁和行锁之间，开销介于表锁和行锁之间，应用在BDB存储引擎中

从操作类型分为读锁，写锁。

• 读锁（S锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响，事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁需要等记录的S锁全部释放完毕。
• 写锁（X锁）：排他锁，当前写操作没有完成前，它会阻塞其他写锁和读锁，事务A对记录添加了X锁，可以对记录进行读和写操作，其他事务不能对记录进行读写操作。

IS锁，IX锁：意向读锁，意向写锁，属于表级锁，S和X主要针对行级锁，在对表记录添加S或X锁之前，会针对表添加IS锁或IX锁。

从操作性能上可以分为乐观锁和悲观锁

• 乐观锁：一般实现方式是队记录数据版本进行比对，在数据更新提交时才会进行冲突检测，如果发现冲突了，则提示错误信息。
• 悲观锁：在对一条数据修改之前，为了避免同时被他人修改，在修改之前就先锁定，在修改控制方式，共享锁和排他锁都是悲观锁的不同的实现方式。

行锁原理

InnoDB中可以使用行锁和表级锁，其中行锁分为共享锁和排他锁，InnoDB行锁是通过队索引数据页上记录加锁实现的，主要实现算法有3中：record lock，gap lock，next-key lock。

• Record Lock：记录锁，锁定单行的记录。（RC，RR隔离级别都支持）
• Gap Lock：间隙锁，锁定索引记录间隙，保证索引记录间隙不变，锁定和改行记录相连的上下索引（范围锁RR隔离级别支持）
• Next-key Lock：记录所和间隙锁的结合，同时锁住数据，并且锁住数据的前后范围（记录锁+范围锁，RR隔离级别支持）

在RR隔离级别中，InnoDB对于记录加锁行为都是先采用Next-key Lock，但当SQL操作含有唯一索引时，InnoDB会队Next-key Lock进行优化，降级为RecordLock，仅锁住索引本身而不是范围。

1. select … from 语句：InnoDB引擎采用了MVCC机制实现非阻塞读，对于普通select语句，InnoDB不加锁
2. select … from lock in share mode 语句：对查询出来的语句追加共享锁，InnoDB会使用Next-key Lock锁进行处理。如果扫描发现唯一索引，可以降级为RecordLock锁。
3. select … from for update 语句 ：追加排他锁，同样会使用Next-key Lock，同样遇到唯一索引，会降级为RecordLock
4. update … where 语句：InnoDB会使用Next-key Lock锁处理，如果发现唯一索引降级为RecordLock
5. delete where 语句：同上
6. insert语句：InnoDB会将要插入的行加入排他的RecordLock锁。

锁定原理

update t1 set name = ‘xx’ where id = 10;

• 如果id是主键，则加锁行为：尽在id=10的主键索引记录上加X锁
• 如果id为唯一索引，则加锁行为：先在唯一索引id上加X锁，然后在id=10的主键索引记录上加X锁。
• 非唯一索引的普通索引记录加锁，则加锁行为：锁定对满足id=10条件记录和主键分别加X锁

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0DlXK9aV-1627872311807)(C:\Users\MACHENIKE\AppData\Roaming\Typora\typora-user-images\image-20210607213422345.png)]

• 无索引加锁，加锁行为，表里所有行和间隙都会加入X锁（当没有索引时会导致全表锁定，因为InnoDB引擎锁机制基于索引实现的记录锁定）

悲观锁

悲观锁，是指数据处理中，将数据处于锁定状态，一般使用数据库的锁机制实现，行锁，表锁，读锁，写锁，共享锁，排他锁，都属于悲观锁。

• 表级锁：每次锁定一张表，并发程度最低，表级读锁会阻塞写操作，但是不会阻塞读操作，而写锁会阻塞读写操作

  • 表级读锁：当前表追加read锁，当前连接和其他连接都可以读操作，但是当前连接写操作会报错，其他连接写操作会阻塞
  • 表级写锁：当前表追加wirte锁，当前连接可以读写操作，其他连接所有操作都阻塞（包括读写）

  -- 手动添加表锁
  lock table 表名1，表名2 锁类型(read|write);
  -- 查看表上加过的锁
  show open tables;
  -- 删除表锁
  unlock tables;
  

• 行级锁：共享锁（行级-读锁），排他锁（行级-写锁），行级锁实现依靠索引实现，如果锁定操作没有使用到索引，则会锁住全表记录。

  • 共享锁：共享锁又称读锁，简称S锁，多个事务可以对同一数据共享一把锁，都能访问数据，都不能修改修改操作被阻塞

    select ... lock in share mode; -- 只适用于查询语句
    

  • 排他锁：又称写锁，X锁，排他锁不能和其他锁共存，如果一个事务获取一个数据行的排他锁那么该事务可以对数据进行读写操作，其他事务就不能对这行数据进行读写操作，也不能。

    select .... for update; 
    

• 乐观锁：它不是数据库提供的功能，需要开发者自己实现。乐观锁和悲观锁多可以解决事务写写并发，在应用中根据并发率进行选择，乐观锁对并发处理效率更高。

  • 使用版本字段（version）：给数据表加 版本字段，每操作一次，将记录的版本号+1，version用来被查看被读的数据有无变化。
  • 使用时间戳：与使用version版本字段相似，需要增加一个数据表字段，字段类型使用timestamp时间戳。

死锁与解决方案

• 表锁死锁

  产生原因：

  用户A锁住了A表，然后又锁住了B表，另一个用户B锁住了B表，又锁住了A表。这时用户A想要B表的锁需要用户B释放B的锁，用户B想要A的锁需要用户A释放A表，此时死锁就产生了。

  解决方案：

  这种属于程序BUG，除了调整业务逻辑没有其他办法。对于数据库夺标操作时，尽量按照相同的顺序进行处理。尽量避免同时锁定两个资源，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

• 行级锁死锁

  产生原因1：

  事务执行了一条没有索引的查询，引发全表扫描，把行锁上升为全表记录锁定，多个事务执行后，就很容易产生死锁和阻塞。

  解决方案：

  SQL语句中不要使用太复杂的关联多表查询，使用explain对SQL进行分析，对有全表扫描和全表锁定的语句，建立相应的索引优化。

  产生原因2：

  两个十五分别想拿到对方持有的锁，相互等待。

  解决方案：

  同一个事务中，尽量做到一次锁定所有资源，按照id对资源排序，然后按照顺序进行处理。

• 共享锁转换排他锁

  产生原因：

  事务A查询一条记录，然后更新该记录，此时事务B也更新这条记录，此时由于事务B的排他锁由于事务A有共享锁，必须等A释放共享锁后才能获取，只能排队等待。

  解决方案：

  • 对按钮控件，点击立刻失效，不让用户重复点击。
  • 使用乐观锁进行控制，需要注意，由于乐观锁是自己实现的，来自外部系统的用户更新操作不受系统控制。

• 死锁的排查：

  • 查看死锁日志（show engine innodb status \G）

  • 查看explain查看SQL执行计划

  • 查看锁状态变量：如果等待次数高，每次等待时间长，需要分析系统中为什么会有如此多的等待，然后定制优化。

    
    show status like 'innodb_row_lock%'
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6XvLsWiO-1627872311808)(C:\Users\MACHENIKE\AppData\Roaming\Typora\typora-user-images\image-20210608002508946.png)]

    innodb_row_lock_current_waits：当前等待锁定的数量

    innodb_row_lock_time：从启动到现在锁定的时间长

    innodb_row_lock_time_avg：每次等待锁花的时间

    innodb_row_lock_max：每次等待锁花的最大时间

    innodb_row_lock_waits：总共等待次数

Mysql集群架构

集群架构设计中，主要遵从下面 三个维度：

• 可用性

  • 站点高可用，冗余站点
  • 服务高可用，冗余服务
  • 数据高可用，冗余数据

  保证高可用的方法是冗余，但是数据冗余可能带来数据一致性问题。

  实现高可用的方案：

  • 主从模式：简单灵活，能满足多种需求，比较主流的用法，但写操作的高可用需要自行处理
  • 双柱模式：有双主双写，双主单写两种方式，建议使用双主单写

• 扩展性

  扩展性主要位置绕着读操作扩展和写操作扩展。

  • 如何扩展以提高读性能

    • 加从库：简单易操作，方案成熟，从库过多会引起性能损耗，建议不要作为长期扩充方案，应该设法用良好的设计避免持续加从库缓解读性能问题。
    • 分库分表：可以分为垂直拆分和水平拆分，垂直拆分可以缓解部分压力，水平拆分理论上可以无限扩容。

  • 如何扩展以提高写性能

    • 分库分表

• 一致性

  一致性主要考虑集群中各数据库数据同步以及同步延迟问题，可以采用方案：

  • 不适用从库：扩展读性能问题需要单独考虑，否则容易出现系统瓶颈
  • 增加访问路由层：可以利用主从同步最长时间t，可以在数据发生修改后的时间内，先访问主库

主从模式

Mysql主从模式是指数据库可以从一个Mysql数据服务器作为主节点复制到一个或多个节点，默认采用异步复制的方式。主从复制具有，实时灾备，用于故障切换（高可用），读写分离，提供查询服务（读扩展），数据备份，避免影响业务（高可用）。

主从部署必要条件：从库服务器能连接主库，主库开启binlog，主从server-id不同

主从复制

主从复制整体分为以下三个步骤：

• 主库将数据库变更操作记录到Binlog文件中
• 从库读取主库Binlog日志文件信息写入到从库的Relay log 中继日志中
• 从库读取中继日志信息在从库中进行Replay，更新从库数据信息

在上述过程中涉及了master的BinlogDump Thread 和 Slave的I/O Thread，SQL Thread，它们作用如下：

• master服务器对数据库的更改操作记录在Binlog中，BinlogDump Thread接到写入请求后，读取Binlog信息推送给Slave的I/O Thread。
• Slave的I/O Thread将读取到的Binlog写入本地 Replay Log中
• Slave的SQL Thread检测到Relay Log的变更请求，解析relay log 中内容在从库上执行

上述过程都是异步操作，俗称异步复制，存在数据延迟。现象主从复制粗壮乃以下问题

• 主库宕机后，数据可能丢失
• 从库只有一个SQL Thread，主库写压力大，复制可能延迟

解决办法：

• 半同步复制–解决数据丢失问题
• 并行复制–解决从库复制延迟问题

半同步复制

为提升数据安全，Mysql让master在一个特定的时间等待Slave节点的ACK（Acknowledge chara）消息，接到ACK消息后才会进行事务提交，这是半同步复制的基础，Mysql从5.5版本引入半同步复制机制降低数据丢失的概率。

主库事务写入分为4个步骤：

• InnoDB Redo File Write (Prepare Write)

• Binlog File Flush & Sync to Binlog File

• InnoDB Redo File Commit（Commit Write）

• Send Binlog to Slave

当Master不需要关注Slave是否接受到Binlog Event时，即为传统的主从复制。

当Master需要在第三步等待Slave返回ACK时，即为 after-commit，半同步复制（MySQL 5.5引入）。

当Master需要在第二步等待 Slave 返回 ACK 时，即为 after-sync，增强半同步（MySQL 5.7引入）。

并行复制

Mysql的主从复制延迟一是一个被关注的问题，5.6版本开始追加了并行复制的功能，目的为了改善复制延迟问题，并行复制称为enhanced muti-threaded salve（简称MTS）。

在从库中I/O Threa 和 SQL Thread 都是单线程模式工作，因此有了延迟问题，可以采用多线程机制加强，减少从库复制的时间。（SQL Thread 多线程即可提升复制性能，而I/O多线程意义不大）

• Mysql5.6并行复制：基于库的并行复制，如果存在多个库，对于从库复制有很大提升，但是对于只有一个库多个表的系统就没有提升。

• Mysql5.7并行复制：基于组提交的并行复制，可以认为是真正的并行复制，通过对事务进行分组，将单个操作写入二进制日志，如果多个事务能同时提交意味着没有冲突，因此可以在Slave上并行执行，通过主库在Binlog中添加组提交信息。5.7的并行复制给予一个前提，所有的处于prepare阶段的事务可以进行并行提交：

  InnoDB事务提交是两个阶段，prepare阶段，和commit阶段。事务之间是否有冲突prepare阶段就可以检测出来。

  同时5.7可以配置基于库的并行复制 slave-parallel-type:DATABASE |LOGICAL_CLOCK，同时日志多了last_committed字段表示这些事务存在与一个分组中，可以并行回放。

• Mysql8.0并行复制：基于write-set机制并行复制。用集合存储修改事务记录信息（主键哈希值），所有的提交记录修改的主键值经过和集合中的对比，来判断是否冲突，以此来确定并行的关系，没有冲突即可并行，这样把粒度降低到row级别，并行速度更快。

  并行配置和调优：

  • binlog_transaction_dependency_history_size用于控制集合变量的大小。

  • binlog_transaction_depandency_tracking

    ​ 用于控制binlog文件中事务之间的依赖关系，即last_committed值。

    ​ COMMIT_ORDERE: 基于组提交机制

  ​ WRITESET: 基于写集合机制

  ​ WRITESET_SESSION: 基于写集合，比writeset多了一个约束，同一个session中的事务

  ​ last_committed按先后顺序递增

  • transaction_write_set_extraction

    用于控制事务的检测算法，参数值为：OFF、 XXHASH64、MURMUR32

  • master_info_repository

    开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。

  • slave_parallel_workers

    若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，但将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差。

  • slave_preserve_commit_order

    MySQL 5.7后的MTS可以实现更小粒度的并行复制，但需要将slave_parallel_type设置为LOGICAL_CLOCK，但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照relay log中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。

并行复制监控

show tables like 'repalication%' -- 5.7追加performance_schema库下
show slave status \G

读写分离

实际业务中，往往是读多写少，此时数据库的读就会成为瓶颈，如果优化SQL但是读操作已久是瓶颈时，这时候就可以选择读写分离。读写分离是在主从基础上一个主库负责增删改，多个从库完成数据操作，主库之间通过主从复制机制进行数据同步。应用中可以在从库追加索引优化查询，主库可以不加索引用于提高写效率。

• 主从同步延迟

  读写分离架构时，数据主从同步具有延迟，数据一致性会有影响有以下解决方案：

  • 写后立刻读：写入数据后，在某个时间段内操作主库，之后访问从库（取决于主从复制时间）
  • 二次查询：先去从库读取数据，找不到是再去主库，容易将压力返还给主库，为了避免恶意攻击，需要对数据库访问进行封装。
  • 根据业务特殊处理：根据业务特点和重要程度进行调整，一致性要求高的去主库，否则去从库

读写分离落地

读写分配机制是实现读写分离的关键操作，控制合适去主库写，何时去从库读。有以下两种方案：

• 基于编程实现的配置和实现：代码中由操作类型决定主从分配，应用广泛，实现简单，不需要增加硬件开支，缺点是需要开发人员实现，运维无法下手。
• 基于服务器端代理实现：请求先进入代理服务器，然后在进入master和slave

Mysql Proxy：官方提供的中间件实现负载平衡，读写分离

Mycat：阿里开源产品基于java编写的中间件

ShardingSphere：开源分布式中间件解决方案，由Sharding-Jdbc和Sharding-Proxy

双主模式

主库如果发生单点故障，从库切换主库还需要做改动，如果是双主或者多住，就可以增加主库的可用性。

使用双主双写还是双主单写好：建议双主单写，双主双写存在问题：

• ID冲突：A主库写入，当A主库未同步到B主库时，B主库写入，如果自动递增容易发生ID主键冲突，可以采用Mysql自身自动增长步长来解决，例如A ：1，3，5 。B：2，4，6。但是对数据库运维和扩展都不友好，扩展需要调整步长。
• 更新丢失：同一条数据两个库同时更新，有可能丢失更新。

MMM架构

用来监管双主复制，支持双主故障切换的第三方软件。MMM包含writer和reader两个角色，分别对应写节点和读节点。双主的情况下，一个一个为写节点，当出现异常时，将异常的主去除掉，然后另一个主置为写节点，其他的读节点改为它的从库。

MHA结构

比较成熟的Mysql高可用方案，可以实现故障切换和主从提升的高可用软件，MHA能在30秒内完成故障数据库自动切换。MHA由两部分组成：MHA Manger和MHA Node：

• MHA Manger：可以单独部署在一台独立的机器上管理多个master-salve集群，可以部署在一台slave节点上，负责检测master是否宕机。
• MHA Node：运行在每个Mysql服务器上，不管是Master还是Salve，都能称为Node，是被监控管理的对象。

优点：

• 自动故障转移快
• 主库崩溃不存在数据不一致
• 性能优秀，支持半同步复制和异步复制
• 一个Manger可以监控多个集群

主备切换策略

主备切换指备库变主库，主库变悲苦，有可用性优先和可靠性优先的策略。

• 主备延迟问题

  主备延迟由数据同步延迟导致就是同一个事物在备库执行完毕和主库执行完毕之间的时间差。同步延迟主要原因：

  • 备库集群性问题：机器性能差，甚至一台机器充当多个主库的备库
  • 分工问题：备库提供了读操作或者执行一些后台分析处理操作消耗大量的CPU资源。
  • 大事务操作：大事务耗时较长导致主备复制时间长，比如大量数据的delete或者大表的DDL操作。

可靠性优先

专门的HA高可用组件完成，但切换过程中会存在短时间的不可用，在切换的某一时刻都是处于只读状态。

可用性优先

不等待主从同步完成，直接把业务请求切换到从库，并让从库可读写，这样几乎不存在不可用时间，但可能会数据不一致。

分库分表

系统处理大量的数据时，读写分离和缓存往往都不合适，此时比较普遍的方案就是NoSQL或者采用分表分库

使用分表分库时，主要有垂直拆分和水平拆分两种模式。

• 垂直拆分：由于表多导致单库大

• 水平拆分：由于表记录多导致单库大，将表柴刀多个库中，将表记录拆分到多个表中

分库分表方案：只分库，只分表，分表分库

水平拆分解决表记录过多问题，垂直拆分解决表过多或者表字段过多问题

垂直拆分

垂直铲粪又称纵向拆分，指将表按库进行分离，或者修改表结构将表字段进行拆分，所以分为垂直分库和垂直分表两种方式，一般谈到垂直拆分指的是垂直分库，根据业务进行拆分，相关的表放一个库里。垂直分表可以解决数据跨页存储，有的字段常用有的字段不常用，大的text字段等问题。

水平拆分

水平拆分又称横向拆分，相对于垂直拆分，根据某个字段按照某种规则将数据分散到多个表或者多个库中，每个表包含数据的一部分。

水平拆分的优点：

• 拆分规则设计好，join操作基本可以数据库做
• 不存在单裤大数据，并发性能强
• 表结构相同应用层改造相对较少，只需要增加路由规则
• 提高了系统的稳定性和负载能力

水平拆分的缺点：

• 拆分规则难抽象
• 跨库join性能较差
• 分片事务一致性难以解决
• 数据库扩容的难度高

分库分表的主键生成策略

• UUID：16字节长度能表示唯一标识，如果UUID作为主键，在InnoDB引擎下，因为UUID无须可能引起数据位置频繁变动影响性能。
• COMB：UUID的变种，可以理解为改进的UUID解决了UUID无序问题性能比UUID好
• 雪花算法：Twitter开源的ID生成算法，结果是一个long的ID。优点性能较好，缺点对系统时钟有强依赖如果有系统时钟不一致则会冲突。
• 数据库ID表：创建一个存储生成ID的ID表从ID表拿ID，性能无法保障。
• Redis生成ID：每次队ID进行操作，拿到全局ID。

分片策略

分片是用来确定数据在多台存储设备上分布的技术。分片是分配过程，是一个逻辑上的概念，表示如何实现，分库分表表示分配的结果，属于物理的概念，是最终的结果。

数据库扩展方案：

• 横向扩展：一个库变多个库。加机器，分片是横向扩展。
• 纵向扩展：一个库还是一个库，优化机器性能，加CPU加内存

分片策略

数据分片各分局指定的分片键和分片策略将数据水平拆分，拆分多个数据分散到多个存储节点中，分片键用于划分的字段，一般使用一般使用ID或者时间，分片策略指的是分片规则。

• 基于范围分片：比如用户ID，订单时间，产品价格等

  优点：新数据可以落在新的节点上，集群扩容数据无需迁移

  缺点：数据热点不均匀，导致节点符合不均。

• 哈希取模分片：整形的键值可以直接取模

  优点：实现简单数据比较均匀，不容易出现冷热不均。

  缺点：扩容是会产生大量的数据迁移。

• 一致性哈希分片：基于一致性哈希算法，实现使得扩容时受到影响的数据量很小，只会影响相邻节点，不会发生大规模数据迁移。

扩容方案

当数据进行高速增长期时，即使对数据进行分表分库，但数据的容量还是到达天花板，这时就需要横向扩容。

• 停机扩容：发布公告－＞停机－＞进行数据扩容数据迁移－＞重启服务

  回滚方案：万一迁移失败，重新将配置和数据回滚，改天再挂公告。

  优点：简单

  缺点：

  • 停止服务，缺乏高可用
  • 程序员压力大，需要在指定时间内完成
  • 如果有问题没有即使测试出来，运行后发现问题，数据会丢失一部分，难以回滚

  使用场景：

  • 小型网站
  • 大部分游戏
  • 对高可用需求不高的服务

平滑扩容

如果想持续对外提供服务，保证服务的可用性，平滑扩容就是最好的选择，平滑扩容将数据库成倍扩容。

• 新增一倍的数据库
• 配置双主进行数据同步
• 数据同步完成后配置双主双写（有同步延迟，如果时时刻刻都有写更新操作会存在不准确问题）
• 双柱同步删除，修改数据库配置，并重启
• 改变分片规则，清楚表中多余数据。

优点：

• 扩容期间，服务正常运行，保证高可用
• 相对停机扩容，时间长，项目组压力小，出错率低
• 遇到问题随时解决，不怕影响线上服务
• 可以将每个数据库数据量减少一半。

缺点：

• 程序复杂，需要配置双主同步，双主双写，检测数据同步等
• 后期数据库扩容，比如代价比较高

适用场景：

• 大型网站
• 高可用要求高的业务

ShardingSphere

Apache ShardingSphere是一款开源的分布式数据库中间件组成的生态圈。由Sharding JDBC，Sharding Proxy，Sharding-Sidecar这三款独立的产品组成，提供标准化的数据分片，分布式事务和数据库治理功能。

Sharding-JDBC：轻量级Java框架，在Java的JDBC层提供额外的服务，一jar包形式使用。

Sharding-Proxy：透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。

Sharding-Sidecar：被定义为Kubernetes或Mesos的云原生数据库代理，以DaemonSet的形式代理所有对数据库的访问。

Sharding-JDBC

定位为轻量级Java框架，在JDBC层提供额外的服务，以jar包形式提供服务，无需额外部署和依赖，可以理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

• 适用于任何基于Java的ORM框架：Hibernate，Mybatis等。
• 基于任何第三方的数据库连接池：C3P0，DBCP，Durid，HikariCp等
• 支持任何实现JDBC规范的数据库，Mysql，Oracle，SQL Server，PostgreSql

Sharding-JDBC主要功能：

• 数据分片：分库，分表，读写分离，制定分片策略，无中心化的分布式主键
• 分布式事务：标准化的事务接口，XA强一致性事务，柔性事务
• 数据库治理：配置动态化，编排治理，数据脱敏，可视化链路追踪

Sharding-JDBC使用过程：

• 引入maven依赖
• 规则配置
• 创建DataSource

Sharding-JDBC初始化过程：

• 根据配置的信息生成Configuration对象
• 通过Factory会将Configuration对象转换为Rule对象
• 通过Factory将Rule对象与Datasource对象封装
• Sharding-JDBC使用Datasource进行分表分库和读写分离操作

数据分片

表的概念：

• 真是表：数据库真是存在的物理表
• 逻辑表：进行分片后同一类表结构的名称
• 数据节点：在分片后由数据源和数据表组成，例如ds0.b_order1，定义了某个数据元上的某个表
• 绑定表：分片规则一致的关系表（主表，子表）例如b_order和b_order_item，则此两表互为绑定表关系。绑定表之间的多表关联查询，不会出现笛卡尔积关联，提升查询效率。
• 广播表：使用中有些表没有必要做分片，如字典表，配置表，数据不大，可能与其他表进行关联查询，广播表会在不同节点上存储，春初的表结构和数据完全相同，可以避免跨库。

分片算法

Sharding-JDBC提供了四种分片算法接口需要使用者自己实现：

• 精确分片算法（PerciseShardingAlgorithm）：处理使用但一键作为分片键的=与in进行分片场景
• 范围分片算法（RangeShardingAlgorithm）：处理使用但一键作为分片键的BETWEEN、AND、>、<、>=、<=进行分片的场景。
• 复合分片算法（ComplexKeysShardingAlgorithm）：用于处理多个键作为分片键进行分片的场景。
• Hint分片算法（HintShardingAlgorithm）：用于处理Hint行分片场景。

分片策略：

• 标准分片策略（StandardShardingStrategy）：只支持单分片键，提供=，>等分片操作支持，提供PerciseShardingAlgorithm（必选），RangeShardingAlgorithm（可选）两个分片算法。但是SQL中使用了范围操作，如果不配置RangeShardingAlgorithm就会全路由扫描效率低。
• 复合分片策略（ComplexShardingStrategy）：支持多分片键，提供对=，>支持
• 行表达式分片策略（InlineShardingStrategy）：只支持单分片键，日用Groovy表达式，提供对SQL的=和in的支持
• Hint分片策略：支持Hint算法

数据分片流程解析：

• SQL解析：分为词法解析，语法解析，通过词法解析器将SQL拆分成单词，在使用语法解析器对SQL进行理解，提炼出上下文。

  • Mysql解析器
  • Oracle解析器
  • SQLServer解析器
  • PostgreSQL解析器

• 查询优化：合并优化分片条件

• SQL路由：逻辑表名路由到真是表名，此外还有广播路由。

• SQL改写：改写成真实的数据源和表名称

• SQL执行：多线程执行器，异步执行多个SQL

• 结果归并：多个SQL执行结果进行归并。流式归并，内存归并，装饰着追加归并。

数据分片SQL使用规范

• SQL使用规范

  • 支持项

    • 路由至单节点时，Mysql数据库全兼容，其他数据库完善中
    • 多数据节点时，支持DQL，DDL，DCL，TCL。支持分页，去重，排序，分组，聚合，关联查询（不支持跨库关联）。

  • 不支持项

    • 不支持CASE　WHEN、HAVING、UNION（ALL）
    • 除了分页查询外的子查询，只支持第一层，一旦嵌套子查询超过两层就会解析异常
    • 由于归并限制，子查询中包含聚合函数目前不支持
    • 不支持包含schema的SQL
    • 分片键处于运算表达式或者函数中的SQL时，将采用全路由的形式获取结果。
    • Values语句不支持表达式
    • insert…select不支持
    • 不支持同时使用sum和distinct

  • 分页查询

    Mysql和Oracle全面支持，SQL Server不支持

    分页时保证ID连续，通过ID进行分页是比较好的解决方案。或者通过上次查询的结果最后一条记录的ID进行下一页的查询。

Inline行表达式

InlineShardingStrategy：采用Inline行表达式进行分片配置

语法格式:

行表达式比较直观，只需要在配置中使用$expression或者$->{expression}标识表达式即可。行表达式中如果出现多个表达式，整个表达式结果会将每个子集表达式结果进行笛卡尔积组合。

自定义主键生成器

• 自定义主键类：实现ShardingKeyGenerator接口
• 按SPI规范配置自定义主键类
• 自定义主键类配置

读写分离

通过主从架构，将查询请求均匀分散到多个数据副本，进一步提升系统处理能力。根据SQL语义进行路由。

核心功能：

• 提供一主多从的读写分离配置，仅支持单主库，可以支持独立使用，也可以配合分表分库使用
• 独立使用读写分离，支持SQL透传
• 同一线程且同一数据连接内，能保证数据一致性，如果有写操作，后续的读操作均从主库读取
• 基于Hint的强制主库路由，可以强制走主库查询实时数据，避免主从同步数据延迟

不支持项：

• 主从数据同步（Mysql实现）
• 主从数据同步延迟
• 主库双写或者多写
• 跨主库和从库之间事务数据不一致，建议在主从建构中，事务的读写均用主库操作。

Hint强制路由

当分片条件不存在于SQL中时，而存在于外部业务逻辑，此时就要使用Hint。Hint指定强制路由后，SQL将无视原有分片逻辑，直接路由至指定的数据节点操作。

使用场景：

• 数据分片操作：如果数据分片没有在SQL或者数据表中，而是在业务逻辑代码中。
• 读写分离操作：如果强制在主库进行某些数据操作

使用过程：

• 编写分库或分表策略，实现HintShardingAlgorithm接口
• 配置文件中指定分库分表策略
• 代码中查询前使用HintManger指定执行策略值

在读写分离结构中，可以强制指定从主库读取避免出现数据一致性问题。

数据脱敏

将数据的脱敏功能从业务代码中剥离出来。更新操作时通过队SQL进行解析，根据配置的脱敏规则队SQL进行改写，实现队原文加密。查询数据时从数据库中提取密文数据，最终将解密后的原始数据返回给用户。

脱敏规则

脱敏配置主要包含四部分：数据源配置，加密器配置，脱敏表配置以及查询属性配置。

加密策略

提供了两种加密策略用于数据脱敏，两种策略分别对应两个加密接口，Encryptor，和QueryAssistedEncryptor。Encryptor包含两种加密实现：AES和MD5。而QueryAssistedEncryptor的思想是即使密码相同不同的用户加密出来的密文应该不同。针对密文的等值查询提供了辅助查询列，加密密文一致但是不可逆，需要自己实现。

使用过程

• 定义实体类时需要定义逻辑列（需要加密字段）
• 配置脱敏信息，需要脱敏的表名，需要脱敏的逻辑字段和实际列的映射关系，加密的算法，给加密列指定算法，指定查询时使用明文列还是密文列。

分布式事务

分布式事务理论

• CAP（强一致性）C一致性，A可用性，P分区容错性，最多能满足CP或AP
• BASE（最终一致性）BA基本业务可用性，支持分区失败。S表示柔性状态，短时间内允许不同步。E表示最终一致性，数据最终一致性，但是实时可能不一致。

分布式事务模式

• 2PC（强一致）两段提交

  • 准备阶段：协调者像所有参与者发送事务内容，询问是否可以提交事务，等待所有参与者答复。
  • 提交阶段：如果协调者收到了超时或者失败消息，直接给每个参与者发送回滚消息，否则发送提交消息

• 2PC存在的问题

  • 性能问题：所有参与者事务提交阶段都处于同步阻塞状态，占用系统资源，容易瓶颈
  • 可靠性问题：如果协调者单点故障，参与者就一直锁定
  • 数据一致性问题：如果局部网络问题，一部分收到了提交信息，另一部分事务参与者没有收到提交信息，则会导致数据不一致。

• 3PC 模式（强一致性）

  • canComiit阶段：协调者向参与者发送commit请求，参与者如果能提交则返回yes，否则返回no
  • preCommit阶段：协调者根据canCommit参与者反映情况执行预提交事务或中断事务操作，参与者均返回yes，协调者向所有参与者发出perCommit请求，参与者收到请求后，执行事务操作但不提交，将undo和redo信息记录日志，各参与者想协调者发送ack响应等待最终指令。
  • doCommit阶段：真正的事务提交阶段，根据preCommit阶段的反馈结果提交或者中断事务操作。

• XA规范（强一致性）

  由X/Open组织提出的分布式事务规范，基于两阶段提交协议，XA规范定义了全局事务管理器（TM），和局部资源管理器（RM）之间的接口，目前主流的关系型数据库都是实现了XA接口。

• TCC模式（最终一致性）

  Try-Confirm-Cancel，分为两个个阶段Try，Confirm，Cancle：

  • Try：负责检查资源和预留
  • Confirm：提交操作，执行真正的业务。
  • Cancle：预留资源的取消

  TCC模式相比于XA，解决了如下问题：

  • 解决了协调者单点问题，由主也无妨作为发起者，业务活动管理器可以变成多点。
  • 同步阻塞：引入超时机制，超时后进行补偿，不会锁定整个资源。
  • 数据一致性，有了补偿机制后，由业务活动管理控制一致性。

• 消息队列模式（消息队列实现TCC）

  由将分布式事务拆分成本地事务进行处理，将需要分布式处理的事务通过消息日志的方式来异步执行，消息日志可以存储到本地文件，数据库或MQ中间件，再通过业务规则自动或人工发起重试。

• Saga模式（最终一致性）

  由多个短事务够长一个长事务。把分布式事务看成多个本地事务组成的事务，每个本地事务都有一个补偿事务，如果有某一步执行异常，Saga事务被终止，同时调用补偿事务完成恢复操作。

  基本协议如下：

  • 每个Saga事务由一系列幂等的有序子事务Ti组成
  • 每个Ti对应的幂等补偿操作Ci，补偿用于撤销Ti的结果

  补偿模式分为两种策略：

  • 向前恢复：对于上面第一种执行顺序，发生失败进行重试，适用于必须成功的场景。
  • 向后恢复：对于上面提到的第二种执行顺序，发生错误后撤销之前的所有成功的子事务，使得整个Saga执行结果撤销。

• Seata框架

  Seata是一套一站式分布式解决方案，是阿里和蚂蚁金服的分布式框架。Seata目前事务模式有AT，TCC，Saga三种模式，默认是AT模式AT是2PC的一种实现。

  AT事务模型包含事务管理器（TM），资源管理器（RM），事务协调器（TC）。其中TC是独立服务需要单独部署。TM和RM以jar包方式和业务部署在一起。在整个生命周期内保持RPC通信。

SPI加载剖析

ShardingSphere中很多功能实现类都是根据SPI注入方式完成，是JAVA提供被第三方扩展的API，实现框架扩展和组件替换。

• SQL解析：SQLPareseEntry
• 数据库协议：数据库协议的接口用于Sharding-Proxy解析适配数据库访问协议
• 数据脱敏：Encryptor和QueryAssistedEncryptor，内置实现ASE和MD5的实现类
• 分布式主键：ShardingkeyGenerator，内置实现了UUID，和Snowflake实现
• XA事务管理器：主要用于规定XA事务的是闲着适配为同一的XA事务接口XATrasactionManger
• 注册中心：规定注册中心地址初始化，存取数据更新数据监控等行为接口为RegistryCenter内置的实现类有Zookeeper

数据库治理

编排治理模块包括配置中心/注册中心（规划中的元数据中心）、配置动态化，数据库熔断禁用，调用链路等治理能力。

Mycat

mycat是实现了Mysql协议的Server，用Mysql客户端或者命令行能访问。核心功能是分库分表，读写分离。

Mycat核心概念

• 逻辑库：数据分片处理后会有很多数据库，Mycat操作时使用逻辑库代表完整的数据库集群，便于对整个集群操作
• 逻辑表：分片和用来代替所有的实际表来进行操作。
• 分片表：分片表指原有的很大数据的表，需要被切分的表，每个分片都有一部分数据。
• 非分片表：不需要进行分片的表
• ER表：基于E-R关系的数据分片策略，子表的记录和父表的记录存在同一个数据分片上，通过分组保证数据join不会跨库。
• 全局表：很少变动，数据总体变化不大，数据规模不大，放在每个库里可以防止跨分片join
• 分片节点：一个大表被分到多个分片数据库上面，每个表分片所在的数据库就是分片节点
• 分片节点主机：分片节点不一定会独占主机，同一台机器上可以分片多个数据库，这样一个或多个分片节点所在机器就是节点主机，为了规避单节点主机并发限制，尽量将读写压力高的分片节点均衡的放在不同的主机上

Mycat配置

server.xml配置

• user标签：用于定义登录mycat的用户权限。

  <user name="user">
      <property name="password">user</property>
      <property name="schemas">lg_edu_order</property>
      <property name="readonly">true</property>
      <property name="defaultSchema">lg_edu_order</property>
  </user>
  

• firewall标签：包括IP白名单和IP黑名单

• 全局序列号：分布式全局序列号

  <system>
      <property name="sequnceHandlerType">0</property>
  </system>
  <!--0-基于本地文件，1-基于数据库方式生成，2-基于本地时间戳生成，3-基于ZK与本地配置生成，4-基于ZK递增生成-->
  

schema.xml配置

schema.xml作为Mycat中重要配置文件之一，管理Mycat的逻辑库，逻辑表，分片节点信息

• schema标签：用于定义逻辑库，具有如下属性

  • name：逻辑库名称

  • dataNode：分片节点，可以有多个

  • sqlMaxLimit：查询返回记录数限制，取出门限数量的记录数。

  • checkSQLschema：执行带schema名的表名时去掉schema

• table标签：定义逻辑表所有的拆分的表都需要在这里定义

  • name：逻辑表名
  • dataNode：分片节点
  • rule：分片规则
  • ruleRequired：是否强制绑定分片规则
  • primaryKey：逻辑表对应真实表的主键
  • type：逻辑表类型，全局表，普通表
  • autoIncrement：自增长主键
  • subtables：分表
  • needAddLimit：是否自动加limit

• dataNode标签：用来定义分片姐点，定义具体数据库实例，读写分离配置，心跳语句

  • name：节点主机名

  • maxCon：最大连接数

  • minCon：最小连接数

  • balance：读操作负载均衡类型

  • writeType：写操作负载均衡类型

  • dbType：数据库类型

  • dbDriver：数据库驱动

  • switchType：主从切换类型

• heartbeat标签：检查心跳语句例如：select user(),Oracle可以使用select 1from dual等

• writeHost和readHost标签：指定后端数据库的相关配置给mycat，用于实例化连接池，wirteHost是指定写实例，readHost指定读实例。

  • host：主机名
  • url：连接字符串
  • password：密码
  • user：用户名
  • weight：权重

rule.xml配置

rule.xml用来定义分片规则

• tableRule标签

  • name：指定为一名称表示不同的表规则
  • columns：指定要拆分的列的名字
  • algorithm：使用funcation标签中的name属性，连接表规则和具体路由算法。

• funcation标签

  • name：指定算法名称
  • class：指定算法具体类
  • property：为具体算法用到的一些属性

同的主机上

Mycat配置

server.xml配置

• user标签：用于定义登录mycat的用户权限。

  <user name="user">
      <property name="password">user</property>
      <property name="schemas">lg_edu_order</property>
      <property name="readonly">true</property>
      <property name="defaultSchema">lg_edu_order</property>
  </user>
  

• firewall标签：包括IP白名单和IP黑名单

• 全局序列号：分布式全局序列号

  <system>
      <property name="sequnceHandlerType">0</property>
  </system>
  <!--0-基于本地文件，1-基于数据库方式生成，2-基于本地时间戳生成，3-基于ZK与本地配置生成，4-基于ZK递增生成-->
  

schema.xml配置

schema.xml作为Mycat中重要配置文件之一，管理Mycat的逻辑库，逻辑表，分片节点信息

• schema标签：用于定义逻辑库，具有如下属性

  • name：逻辑库名称

  • dataNode：分片节点，可以有多个

  • sqlMaxLimit：查询返回记录数限制，取出门限数量的记录数。

  • checkSQLschema：执行带schema名的表名时去掉schema

• table标签：定义逻辑表所有的拆分的表都需要在这里定义

  • name：逻辑表名
  • dataNode：分片节点
  • rule：分片规则
  • ruleRequired：是否强制绑定分片规则
  • primaryKey：逻辑表对应真实表的主键
  • type：逻辑表类型，全局表，普通表
  • autoIncrement：自增长主键
  • subtables：分表
  • needAddLimit：是否自动加limit

• dataNode标签：用来定义分片姐点，定义具体数据库实例，读写分离配置，心跳语句

  • name：节点主机名

  • maxCon：最大连接数

  • minCon：最小连接数

  • balance：读操作负载均衡类型

  • writeType：写操作负载均衡类型

  • dbType：数据库类型

  • dbDriver：数据库驱动

  • switchType：主从切换类型

• heartbeat标签：检查心跳语句例如：select user(),Oracle可以使用select 1from dual等

• writeHost和readHost标签：指定后端数据库的相关配置给mycat，用于实例化连接池，wirteHost是指定写实例，readHost指定读实例。

  • host：主机名
  • url：连接字符串
  • password：密码
  • user：用户名
  • weight：权重

rule.xml配置

rule.xml用来定义分片规则

• tableRule标签

  • name：指定为一名称表示不同的表规则
  • columns：指定要拆分的列的名字
  • algorithm：使用funcation标签中的name属性，连接表规则和具体路由算法。

• funcation标签

  • name：指定算法名称
  • class：指定算法具体类
  • property：为具体算法用到的一些属性