一文学习MySQL-MySQL必知必会

MySQL

体系结构

​

connect：不同语言的代码程序和mysql的交互（SQL交互）

1、连接池（connection pool）： 管理、缓冲用户的连接，线程处理等需要缓存的需求
2、管理服务和工具组件（Management Service & Utilities） ：系统管理和控制工具，例如备份恢复、Mysql复制、集群等
3、sql接口： 接受用户的SQL命令，并且返回用户需要查询的结果
4、查询解析器： SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
5、查询优化器 ：SQL语句在查询之前会使用查询优化器对查询进行优化；检查索引基数、搜索行数、确定是否走索引；确定映射字段、确定临时表、是否需要进行索引自动优化（FORCE INDEX强制使用索引）

select id,name from user where age = 40; 
# a、这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤 
# b、这个select查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤 
# c、将这两个查询条件联接起来生成最终查询结果

# 强制使用索引 inx_a，不允许自动优化
select * from table_name force index (idx_a) where a = 100；
1
2
3
4
5
6
7

6、缓存池组件： 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

7、插入式存储引擎（Pluggable Storage Enginess）： 存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）；

8.文件系统及文件和日志： 数据和日志在硬盘的存储

1.索引

​ 索引：为了加快查询速度，而使用的一种加快查询速度的方法；对于不同的索引类别有不同数据结构；

在mysql中如果没有自定义默认使用的是BTree索引，对于BTree索引，复合索引的key的顺序十分重要，因为其符合最左前缀匹配原则；

MySQL索引分类

按主键进行分类

​ 索引包括：主键索引和辅助索引；

下面将围绕上图进行解析：

​ 对于InnoDb而言：

​ 索引可以分为：聚簇索引（主键索引）和非聚簇索引（辅助索引）；

​ 对于MYISAM而言：

​ 主键索引和辅助索引的组织方式并无区别；

聚簇索引：

下面俩图分别时InnoDB的聚簇索引组织形式、叶节点数据；

​ 上图可以看出聚簇索引数据（叶子节点）是以页（16k）的形式为单位保存的，每个页的第一个key为该页的最小值；非叶子节点也是以页的形式读出，这样就避免浪费IO资源（我们知道IO也是以页的形式读出，如果是MYISAM的索引结构，那么每个页其实有用的数据可能只有一个（其存储不是连续组织起来的）非常浪费IO资源）

​ 上图可以看出叶子节点除了保存数据（即记录）还有主键列（或者聚簇的键）事务ID和回滚指针（其实它们也是记录的一部分，mysql的InnoDB会给我们创建的表加上上述隐式字段用于MVCC）；

​ 定义：聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。聚簇索引的索引页面指针指向数据页面。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。

​ 特点：

1. 一张表只能有一个聚簇索引；（InnoDB默认使用主键作为聚簇索引，如果没有主键则用唯一非索引代替，如果也没有则用隐式字段ROMID，ROMID隐式字段在MVCC介绍）
2. 聚簇索引在同一个B-Tree中保存了索引列和具体的数据；（这里的B-Tree是mysql优化的B+Tree）
3. 实际的数据保存在叶子页中，中间的节点页保存指向下一层页面的指针（B+Tree的特点）

下图位InnoDB的聚簇索引的优缺点：（还有一条是使用辅助索引查找时可能需要回表。另外缺点中第二条也是InnoDB推荐使用递增主键的重要原因）

非聚簇索引：

​ 定义：非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。

​ 特点：

1. 一个表可以有多个非聚簇索引（显然）
2. 不能“直达”，可能链式地访问多级页表后，才能定位到数据页
3. 非聚簇索引的叶子节点是聚簇索引的值（对于InnoDB而言，如果是Myisam则直接位数据所在地址，这里需要注意的是MYISAM的），因此根据非聚簇索引查询数据时会涉及回表；
4. 当然其默认也是通过索引BTree的形式组织的；

按数据结构组织形式

1. BTree索引
2. 哈希索引
3. 全文索引、前缀索引等

1.哈希索引只保存哈希值和行指针，不保存数据；
2.哈希索引数据并不是按照哈希值存储的所以页无法排序
3.哈希索引如果是组合索引（例如a、b、c同时作为索引的主键那么哈希值也是根据这三个同时算出），则无法使用于最左原则及部分索引；
4.哈希索引无法进行范围查询，范围查询会进行全表扫描
5.对于频繁哈希冲突：InnoDB会自动的创建BTree索引，而如果没有创建Btree索引缓解冲突那么查询时会遍历复合条件的哈希索引的每一行；
1
2
3
4
5

BTree索引

1. 简介

MySQL的BTree索引使用的是B+Tree的优化

1.平衡二叉树数据结构
	（1）非叶子节点最多拥有两个子节点；
    （2）非叶子节值大于左边子节点、小于右边子节点；
    （3）树的左右两边的层级数相差不会大于1;
    （4）没有值相等重复的节点;
2.B树数据结构（多路平衡查找树，就是为了降低平衡二叉树的高度，加快查找效率）
	阶：m
	（1）根结点至少有两个子女；
	（2）每个节点最多有m-1个关键字；
	（3）非根节点至少有m/2个关键字；
	（4）每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它；
	（5）所有叶子节点都位于同一层
3.B+树数据结构
	和B+Tree的区别：
	（1）要保存的数据只存储在叶子节点
	（2）B+Tree的叶子节点保存的数据间相互循环连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

哈希索引

• InnoDB中采用除法散列函数获取hash桶的位置，通过拉链法（链地址法）处理hash冲突；
• 只存在数组，用一个hash函数把key转换成一个确定的内存位置，然后把value放在数组的该位置。

BTree分类和使用

（1）唯一索引：索引列的值必须唯一,但可以为null;

（2）单值索引：一个索引只包含一个列,一个表中可以有多个单列索引

（3）复合索引：多个列值按顺序组合成索引key

（4）覆盖索引：当一个key索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引

1.索引创建
	Create INDEX indexName IndexType ON TableName(ColumName...)
2.索引删除
	DROP INDEX indexName ON tabelName 	
3.ALTER语句
	
4.索引查看
	SHOW INDEX FROM TableName
1
2
3
4
5
6
7
8

联合索引（复合索引）

1. 最需要注意的是复合索引，我们已经知道了索引BTree的最左前缀匹配原则，那么复合索引的顺序十分重要
2. 覆盖索引就是为了避免回表，在符合最左前缀匹配条件下，但要查询的值全部包含在索引中（例如key(a,b,c)；查询a，b；那么数据本身就在索引中就不用通过获取到的聚簇索引的值再进行回表查询）；
3. 复合索引是索引中功能最强大的一个：其主要体现在：
   1. 当复合最左前缀匹配时，其也可以是单值索引（例如：key(a，b，c);通过a查询，那么直接使用单值索引和组合索引的效果是一样的，同理通过a、b组合查询和直接建立一个key(a，b)后查询是一样的）
   2. 复合索引可以使用到覆盖索引，避免回表，对于经常查询的数据d列拼接到复合索引的后组成索引key(a，b，c，d)那么要通过a、b、c查询d（a、b、c、d）时则可以直接获取到数据避免回表操作；

回表：回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低

2.存储引擎

主要分类

​ 常用存储引擎：InnoDB、MyISAM、MEMORY、MERGE、NDB

​ 其他储存引擎：CSV、BLACKHOLE

MySQL使用最多的俩种存储引擎是MyISAM和InnoDB，其中MySQL5.5之前默认使用MyISAM，5.5之后默认使用InnoDB；

MyISAM

​ 特点：

1. MyISAM不支持事务；
2. 其将表的结构（SDI文件）、表的数据（MYD）、表的索引（MYI）分别放在不同的文件；
3. 数据文件和索引文件可以放在不同的目录，这样就能平均分布IO，加快读写速度；（所谓的平均IO）
4. 表级锁结构，,MYISAM对于读写混合操作的并发性不会太好；
5. MYISAM支持由于任意意外关闭MYISAM表呢,检查和修复操作；像这里所说的修复呢,像这里所说的修复呢,并不是事务恢复（其本身就没有支持事务这一说法），所以其修复会造成数据丢失；
6. 支持三种存储方式：静态表（固定长度的表，默认）、动态表、压缩表；

​ 使用场景：

​ 非事物型应用( MyISAM不支持事务)、数据只读（或绝大多数情况只读）；空间类应用(如存储GPS数据，支持空间函数，可应用空间函数对数据进行计算)；

​

对表进行检查修复：
    方式一：
        检查：check table myIsam;
        修复：repair table myIsam;
	方式二：使用工具修复（如果使用命令行工具对myisam表进行修复的话,则需要咱Mysql服务停止,在mysql服务运行的同时,如果使用命令行工具对表进行修复,可能使表造成更加大的损坏）
		myisamchk --help
1
2
3
4
5
6

InnoDB

https://zhuanlan.zhihu.com/p/158978012

后面的事务、锁和MVCC均基于InnoDB

InnoDB的体系结构

要了解InooDB上面每个字段都很重要：

线程：

主要作用：

• 负责刷新内存池中的数据，保证缓冲池的内存缓冲的是最近的数据
• 已修改的数据文件刷新到磁盘文件
• 保证数据库发生异常的情况下InnoDB能恢复到正常状态。

各线程：

• Master Thread（主线程）:负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新（5.6版本后由清理脏页线程负责），合并插入缓冲（INSERT BUFFER），UNDO页的回收等。
• IO Thread（IO线程）: 负责AIO请求的回调处理
• Pruge Thread（清理线程，回收线程）:事务提交后，undo log可能不再需要，由Purge Thread负责回收并重新分配的这些已经使用的undo页。
• Page Cleaner Thread（清理脏页线程）：是5.6.2引入的一个新线程（单线程），从master thread中卸下buffer pool刷脏页的工作独立出来的线程(默认是启一个线程)；5.7开始支持多线程刷脏页（即表明可通过my.cnf设置线程数）；

内存池：

InnoDB的存储引擎内存池：这是一个十分复杂的结构：

1. 缓存池（buffer pool）：缓冲池就是一块内存区域，主要缓冲数据页和索引页，可以通过innodb_buffer_pool_size设置缓冲池的大小，缓冲池通过LRU算法进行管理（最频繁使用的页在LRU列表前端，最少使用的页在末端）；

   读取数据时：首先判断数据所在的页是否在缓冲池中，如果不在则才读取磁盘对应的数据，当缓冲池数据满时使用LRU算法进行替换。需要强调的是从磁盘读取的页不是放在LRU链表的最前面，而是放到midpoint位置（默认为5/8处，之所以这么做是因为要保留mysql的热点数据，避免读取大量非热点数据时将热点数据刷掉）；

   页修改时：对页的修改操作，首先修改在缓冲池中的页，再以一定的频率（Checkpoint机制）刷新到磁盘。

   

2. 重做日志缓冲：

   重做日志先放到这个缓冲区，然后按一定频率刷新到重做日志文件。
   参数：innodb_log_buffer_size

   刷新的规则：

   • Master Thread每秒将一部分重做日志缓冲刷新到重做日志文件；
   • 每一事务提交时会将重做日志刷新到重做日志文件（如果配置了）
   • 重做日志缓冲区使用空间大于1/2

3. 额外的内存池
   内存堆，对InnoDB内部使用的数据结构对象进行管理

4. 检查点机制（CheckPoint）

   1.上文提到脏页数据刷新回磁盘使用的是检查点技术；首先只有增、删、改会产生脏页；而数据库使用的是write Ahead log策略，也就是说脏页数据不会立刻刷新到磁盘中；

   2.当数据库出现问题是，为了恢复数据库的一致性，我们会使用undo log进行重做和回滚，为了避免使用整个日志进行undo和redo所以使用了检查点的技术；

   3.当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页则也要利用CheckPoint刷新数据；

   4.当重做日志出现不可用时，*因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的，重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。*如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

   脏页：当进程修改了高速缓存里的数据时，该页就被内核标记为脏页，内核将会在合适的时间把脏页的数据写到磁盘中去，以保持高速缓存中的数据和磁盘中的数据是一致的

CheckPoint主要有俩种类型：

​ 1.Sharp Checkpoint

​ 2.Fuzzy Checkpoint

1.Master Thread Checkpoint
	Master Thread每个1秒或10秒按一定比例将缓存池的脏页列表刷新会磁盘
2.FLUSH LRU LIST Checkpoint
	Page Cleaner线程发现LRU列表中可用页数量少于innodb_lru_scan_depth(1024)【这个检查被放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024】，就将LRU列表尾端移除，如果这些页中有脏页，就需要Checkpoint；
3.Async/Sync Flush Checkpoint
	重做日志文件空间不可以用时，将一部分脏页刷新到磁盘。
4.Dirty Page too much Checkpoint:
	脏页数量太多（超过比例innodb_max_dirty_pages_pct,默认75），执行Checkpoint。
1
2
3
4
5
6
7
8

文件：磁盘上的数据文件叫表空间文件；表空间包括系统表空间、undo log 表空间、甚至可以让create出来的每张table都有本身单独的表空间

特点

1. 支持事务，所以具有事务的ACID属性：即原子性、一致性、隔离性、持久性；
2. 同一数据库的所有其表索引、表数据储存在同一个文件中（共享表空间结构，InnoDB也可以设置为独立表空间结构），所以其读取会占用更多内存以同时保存数据和索引在内存中；
3. 支持外键约束；支持索引属性值自动增长，自动增长默认由1开始；
4. 行级锁结构，因此其读写的效率会更高；

1.InnoDB默认采用的事务隔离级别是可重复读
2.InnoDB采用MVCC来应对高并发的读写、写读问题
3.InnoDB使用间隙锁防止幻读
4.如上文InnoDB使用的是聚簇索引（主键索引）加二级索引（非聚簇索引）
5.以页的形式管理和组织数据
1
2
3
4
5

共享表空间结构和独立表空间结构

​ https://blog.csdn.net/weixin_33766168/article/details/92676952

1、共享表空间和独占表空间介绍
共享表空间以及独占表空间都是针对数据的存储方式而言的。
共享表空间:  每一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1  初始化为10M。
独占表空间:  每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件(这个文件包括了单独一个表的数据内容以及索引内容)。

2、共享表空间和独占表空间的区别
共享表空间：
    优点：
    1）可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。
    所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。
    2）表数据和表描述放在一起方便管理。 
    缺点：
    1）所有的数据和索引存放到一个文件中，将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间。

独立表空间（在配置文件（my.cnf）中设置innodb_file_per_table=1）：
    优点：
    1）每个表都有自已独立的表空间。
    2）每个表的数据和索引都会存在自已的表空间中。
    3）可以实现单表在不同的数据库中移动。
    4）空间可以回收。
    对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理（表空不能自已回收）,处理方式如下：
    Drop table操作自动回收表空间
    如果对于统计分析或是日志表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间
    对于使innodb-plugin的Innodb使用turncate table也会使空间收缩
    5）使用独占表空间的效率以及性能会更高一点。
    缺点：
    1）单表增加过大，如超过100个G：
    当使用独享表空间来存放Innodb的表的时候，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了。
3、共享表空间以及独占表空间之间的转化
修改独占空表空间配置，以下几个参数必须在一起加入
innodb_data_home_dir = "/usr/local/MySQL/var/"  数据库文件所存放的目录
innodb_log_group_home_dir = "/usr/local/mysql/var" 日志存放目录
innodb_data_file_path=ibdata1:10M:autoextend  设置配置一个可扩展大小的尺寸为10MB的单独文件（共享数据文件），名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内（如 /db/mysql/ibdata1）。
innodb_file_per_table=1  是否使用共享以及独占表空间（1 为使用独占表空间，0 为使用共享表空间）

mysql>show variables like "innodb_file_per_table"
on=1=独立表空间
off=0=共享表空间

innodb_file_per_table 通过这个参数来实现的转化，如果为OFF说明所使用的是共享表空间【默认情况下，所使用的表空间为共享表空间】
innodb_file_per_table值来进行修改即可，但是对于之前使用过的共享表空间则不会影响，除非手动的去进行修改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

MEMORY

• 基于内存的存储引擎，文件系统只保存表的结构，所有数据均放在内存中，数据库关闭后数据将丢失，因此要实现恢复的方法；

MERGE

• merge表是组合多个结构完全相同的MyISAM表，该表会自动复制俩表的数据；

日志

基础

• MySQL通过binlog实现主从复制、备份等

• steal/no-steal主要决定了磁盘上是否会包含uncommitted的数据。
  • steal：允许脏页提前刷回数据库，而不用等到事务必须提交；
    • 其优势是每次都尽可能将IO的buffer做到最大；
    • 但是缺点页很明显，就是需要可能刷回的脏页存在对应的事务没有提交，但是服务器宕机了；所以需要undo log辅助回滚
• force/no-force主要决定了磁盘上是否会不包含已经committed的数据。
  • noforce：允许提交的事务的脏页继续在buffer中，不必每次事务都写入磁盘
    • 同样是为了提高效率，避免频繁IO；
    • 缺点就是可能事务提交了的数据由于没有刷回丢失，因此需要redo log；
• InnoDb的使用的是Steal策略+noforce策略；
• MySQL的InnoDb需要redo log和undo log才能实现事务，而且无论是undo log 还是redo log都需要落盘；同时undo log同样有对应的redo log记录（避免丢失）

redo log

• redo log是innodb才有的（因为需要支持事务才出现的），redo log属于物理日志，记录的是物理地址的数据的变化
• redo log（重做日志）：redo log记录了对实际数据文件的物理变更(数据文件的什么位置数据做了如何的变更，例如**user 表空间页偏移量为128,1变为before = 12，after=10 **)；

重要特点

• redo log同样有俩种形式存在在缓冲中和在文件系统中；
• redo log是在事务进行时，一边进行操作一边写redo log，而且是write ahead log方式（即先写日志）；
• 在没有设置的情况下**，三种情况会使得redo log会刷新到redo log file中；**
  • 到达InnoDB的将redo log回刷时间（每1秒一次）
  • 配置了事务提交自动刷回
    • 可以通过innodb_flush_log_at_trx_commit设置commit的同步机制
      • 0表示当提交事务时，并不将事务的重做日志写入磁盘上日志文件，而是等待主线程每秒刷新。
      • 1表示在执行commit时将重做日志缓冲同步写到磁盘，即伴有fsync的调用
      • 2表示将重做日志异步写到磁盘，即写到文件系统的缓存中。不保证commit时肯定会写入重做日志文件。
  • redo log buffer的使用空间超过1/2

redo log存储

• 尽量保持Redo Log存储在一段连续的空间上。因此在系统第一次启动时就会将日志文件的空间完全分配。以顺序追加的方式记录Redo Log。
• 批量写入日志。InnoDB中的redo log block（512）（重做日志块buffer），redolog是按块，一块一块的写入到磁盘中；
• 操作系统数据的单元是页，所以毫无疑问redo log最后也是按页取出，一页（16k）一般有多个redo log 块
• 并发的事务共享Redo Log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起，以减少日志占用的空间。
• Redo Log上只进行顺序追加的操作，当一个事务需要回滚时，它的Redo Log记录也不会从Redo Log中删除掉。

通过上述描述redo log大致数据结构为：

​ 除此之外考虑到操作系统也有缓冲的概念：传统的UNIX实现的内核中都设置有缓冲区或者页面高速缓存，大多数磁盘IO都是通过缓冲写的。当你想将数据write进文件时，内核通常会将该数据复制到其中一个缓冲区中，如果该缓冲没被写满的话，内核就不会把它放入到输出队列中。因此需要用到fsync系统调用，来刷新某一文件的缓冲到文件中保证当InnoDB从自身redo log buffer提交write后，系统不会等待系统的write buffer满后再写，而是直接进入write队列开始写入文件；

undo log

undo log的基本概念

• undo log：它是逻辑日志。其保存的是旧的行数据（？不确定），本身保存在回滚段的回滚槽中；回滚段有专门的数据页；
• 而且undo log本身又会记录在redo log中（如果是读写事务而非只读事务），同样可以恢复；undo log可以实现事务的一致性（回滚），并且后面的MVCC就是基于undo log进行的；

undo log的重要特征

• 存储的是逻辑日志
• 以段的形式管理undo log（rollback segment 称为回滚段，每个回滚段中有1024个 undo log segment（回滚槽）。）
• undo log以日志链的形式存在
• undo log的回滚段包括临时表链表（临时表的回滚段）和普通表链表（普通表的回滚段），回滚槽包括insert类型和update类型
• mysql共有128个回滚段（其中mysql 5.5.X之前只有1个）
• 每个事务最多占用4个槽位（俩个update、两个insert），每个槽位只能有一个事务，该槽位有header和undo log segment；
  • 之所以每个事务一个槽位是为了方便回滚操作

​ 首先上图展示了Page5是mysql的InnoDB保存回滚段数组位置；回滚段的组织形式；**所有回滚段均有1024个槽位；**下面的图将展示这部分细节；

实现MVCC

在了解完重要的特征后，需要说明的是InnoDB为了在undo log实现MVCC功能在我们创建的表中加上了隐式字段

• 事务ID：一个全局自增的事务id
• 回滚指针：指向当前行前一个回滚undo log的指针，很明显insert没有前一个undo log；
• 隐式主键：（若满足上文解释InnoDB没有合适聚簇索引时会使用该字段作为聚簇索引）
• 删除标志：delete_flag，用来标记该行记录删除

undo log的使用和回滚的实现

​ 首先不考虑redo log记录undo log、页分裂、事务回滚、事务id自增和只读事件等；下面是简要的update过程

​ delete的过程类似，不过只是这里修改的数据是delete_flag字段；同样该行数据不会被删除知道清理线程满足条件清理该数据前该数据一直存在；

​ 看到这里或许你已经知道其怎么实现原子性的了：首先通过undo log保证事务要么全部执行要么均不执行，而undo log记录的是逻辑日志，我们知道InnoDB使用的是行级锁结构，如果使用物理日志那么undo log造成的页分裂后又回滚就会影响其他事务执行（可能）后的结果；redo log之所以可以使用物理日志（redo log使用物理日志是为了恢复的时候快速），redo log本身就不是按事务来写的所有操作均会被并发记录到redo log中，同时页又会有逻辑版本号LSN，不存在这样的问题；

• 回滚日志，同样也是逻辑日志，伴随事务产生，由parge clear线程销毁

binlog

• 特点
  • binlog是属于Server层面的，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的
  • binlog保存的是二进制形式的操作写命令，binlog只是记录DML、DDL、DCL，不记录SELECT；
  • 由于保存的是命令，所以可以通过解析进行sql审计
  • binlog最大优点是比较小，而且由于和物理地址无关适合用来做主从备份；但是如果使用了存储过程，显然binlog在statement模式下只是记录存储过程函数命令
• binlog结构
  • 
• binlog日志级别
  • Row level：就是对每个数据行修改都记录，换句话说每条命令进行一次记录
    • 不记录sql语句上下文相关信息，仅保存哪条记录被修改，也就是说日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。
    • 所有的执行的语句当记录到日志中的时候，都将以每表的行记录的修改作为命令来记录日志，这样可能会产生大量的日志内容。（例如让所有同学成绩+5分，行修改就会对每一行都有一条+5分的命令）
  • Statement level
    • 保存每一条会修改数据的sql，必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证全部语句在slave端被执行的时候可以获得和在master端执行时候相同的结果；对于存储过程和存储函数，可能会有问题；
  • Mixed：是以上两种level的混合
    • 一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种；
      **新版本的MySQL中对row模式也被做了优化，并不是所有的修改都会以rowl来记录，像遇到表结构变更的时候就会以statement模式来记录。**至于update或者delete等修改数据的语句，还是会记录所有行的变更。

慢查询日志

3.事务

基础

• 事务的基本要素（ACID原则）：

1.原子性：Atomicity，数据库事务是不可分割的最小工作单位
2.一致性：Consistency，事务将数据库从一种一致性状态转变为下一种一致性的状态
3.隔离性：Isolation，每个读写事务的对象对其他事务的操作对象在提交前不可见
4.持久性：Durability，事务一旦提交，其结果是永久性的
1
2
3
4

• 事务的并发问题（破坏ACID原则）：

1.脏读（读写锁\多版本并发控制）和丢失修改(写锁）：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据；丢失修改就是事务A修改后，事务B也进行了修改（导致事务A的修改丢失）
2.不可重复读：事务 A 多次读取同一数据，期间事务 B对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3.幻读：事务A修改了某一表的全部行，让其 x=1，然后读取的时候发现读取到了存在 x=2。
1
2
3

InnoDB的隔离级别

由上图可以看出mysql的InnoDB的四种隔离级别分别是：

• READ UNCOMMITTED（未提交读）:
• READ COMMITTED（以提交读）：大多数数据库默认的隔离级别
• REPEATABLE READ（可重复读）：InnoDB默认的隔离级别，需要说明的是InnoDB通过多版本并发控制(MVCC)+ next-key lock（间隙锁） 解决了幻读问题。
• SERIALIZABLE（可串行化）：读写均需要加锁，以保证可串行化；

在了解了InnoDB的隔离级别后重点关注mysql如何实现各个隔离级别或说InnoDB如何实现ACID；

InnoDB实现持久性和原子性

LSN

https://www.cnblogs.com/f-ck-need-u/p/9010872.html#auto_id_8

​ LSN称为日志的逻辑序列号(log sequence number)，在innodb存储引擎中，lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大，而且每次修改的记录的重做日志字节数就是LSN增大的数，重做日志总量就是当前LSN-开始的LSN序号。由上述信息可以得出：LSN和下面三个数据相关：

1.数据页的版本信息。

2.写入的日志总量，通过LSN开始号码和结束号码可以计算出写入的日志量。

3.可知道检查点的位置。

​ 上图是关于LSN和数据记录页、redo log、check point的关系；上图为简化的情况，不考虑事件由redo log buffer提交到 redo log file和错误情况及data buffer因另外俩种原因提交到data file；下面是上图各个字段的解释；

innodb从执行修改语句开始：

(1).首先修改内存中的数据页，并在数据页中记录LSN，暂且称之为data_in_buffer_lsn；

(2).并且在修改数据页的同时(几乎是同时)向redo log in buffer中写入redo log，并记录下对应的LSN，暂且称之为redo_log_in_buffer_lsn；

(3).写完buffer中的日志后，当触发了日志刷盘的几种规则时，会向redo log file on disk刷入重做日志，并在该文件中记下对应的LSN，暂且称之为redo_log_on_disk_lsn；

(4).数据页不可能永远只停留在内存中，在某些情况下，会触发checkpoint来将内存中的脏页(数据脏页和日志脏页)刷到磁盘，所以会在本次checkpoint脏页刷盘结束时，在redo log中记录checkpoint的LSN位置，暂且称之为checkpoint_lsn。

(5).要记录checkpoint所在位置很快，只需简单的设置一个标志即可，但是刷数据页并不一定很快，例如这一次checkpoint要刷入的数据页非常多。也就是说要刷入所有的数据页需要一定的时间来完成，中途刷入的每个数据页都会记下当前页所在的LSN，暂且称之为data_page_on_disk_lsn
1
2
3
4
5
6
7
8
9
10
11

InnoDB实现原子性

https://blog.csdn.net/qq_39459385/article/details/84644005

InnoDB实现隔离性

​ MVCC和锁机制

4.锁

​ 首先回顾一下数据库理论课学的读、写锁和意向锁；

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

锁分类

多粒度封锁协议：存在一个多粒度树，该粒度从上至下粒度的精细程度递增，例如粒度树为：数据库–>表–>前后多行–>跳跃本数据的前后多行—>本数据行加下一行–>本数据行

意向锁就是对一个节点加意向锁，则该节点的下层节点正在加锁

乐观锁和悲观锁

​ 乐观锁：假定并发的事物在处理时不会彼此互相影响，只在提交时检查有没有其它事物修改了该数据，冲突回滚重新执行；

​ 悲观锁：并发的事物在处理时都会引起并发冲突，每次操作数据的时候都会上锁，实行先取锁再执行的策略；

乐观锁常用的实现方式就是：数据版本Version方式和CAS机制方式；

​ 数据版本就是给字段添加一个属性version作为版本，version是一个递增的版本字段，每次获取需要更新的数据时把version字段页一并获取，将version+1再将字段写回（更新），更新时比较版本号是否大于数据库表中version的版本号，大于则写回否则重做；

​ CAS机制比较复杂：

MYISAM锁机制

​ MYISAM使用的是表级锁结构；

​ MYISAM的加锁和InnoDB有很多的不同；在MYISAM中加锁时：

加锁方式

• 加锁方式
  • MYISAM所加的锁均为表锁，所以具有表锁的特点（不会死锁和开销小，但高并发效果差）
  • MYISAM具有读写锁的概念，所以符合上文的读写锁理论（读锁不能加写锁、可以加读锁，写锁不能加读写锁）
  • MYISAM加锁规则是：先写锁、后读锁；也就是说如果具有高并发的读写操作，读操作可能超时都还未能加锁；
  • 读锁（写锁）暂时得不到锁是会加入到读（写）锁定队列等待获取锁；

InnoDB锁

• InnoDB支持多粒度锁，支持行锁，这是本文的重点之一；
• 锁机制归根结底就是保证事务的隔离性，影响事务的隔离性主要是：读写（写读）、写写操作，如果所有事务都是串行执行当然不会有问题，但是显然不可能所以首先需要可并发执行（可串行化）、然后解决并发带来的隔离性的问题；

​ 首先回顾两段锁协议：是指所有的事务必须分两个阶段对数据项加锁和解锁，在申请锁阶段不释放锁，释放锁阶段不申请锁；（两段锁协议可以保证调度是可串行化的调度，可串行化的调度就是多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行它们时的结果相同，我们称这种调度策略为可串行化的调度；保证可串行化调度可以让事务并发执行）；

InnoDB的锁结构

​ InnoDB使用的是MVCC加两段锁协议实现并发控制；

​ 首先InnoDB支持多粒度锁（行锁和表锁）对于表锁和MYISAM类似支持读锁和写锁，还存在一类特殊的锁自增锁；重点介绍行锁；

	自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。
1

行锁

​ MySQL的InnoDB行锁是加在索引的叶子节点上，也就是说只有通过索引查询才会使用行锁否则使用的是表锁，也就是说全表扫描都使用的是表锁；除此之外，MySQL的InnoDB默认使用的是快照读（RR模式），所以一般的SELECT语句不会加锁；只有当前读才会加锁；

​ 有了以上基础，下面介绍类型；行锁主要包括：读、写锁（S、X）、意向锁（IS、IX）、间隙锁（Gap Locks、Next-key Locks、Insert Intention Locks）、记录锁（Record Locks）；

​ 加锁规则

​ 读、写锁：

• 默认情况下SELECT语句使用的是MVCC快照读，并不会加锁；

• 可以通过在SELECT语句尾部加上lock in share mode表示加上读锁（共享锁）；

• 可以通过在SELECT语句后尾部上for update表示加上写锁（间隙锁）；【主要解决当前读和快照读的幻读问题】

• insert、update、delete会自动加上写锁；

  意向锁：加锁规则和上文介绍一样；

  ​

  间隙锁和记录锁

  ​ 间隙锁主要是为了解决当前读的幻读问题（MVCC已经解决了快照读的幻读问题）；

  间隙锁和记录锁：

  • Gap Locks（间隙锁）：对索引项之间的间隙加锁，加锁之后索引间隙范围内不允许插入数据；

  • Insert Intention Locks（插入意向锁）：

        Insert Intention Locks意为插入意向锁，插入意向锁是Innodb gap锁的一种类型，这种锁表示要以这样一种方式插入:如果多个事务插入到相同的索引间隙中，如果它们不在间隙中的相同位置插入，则无需等待其他事务。比如说有索引记录4和7，有两个事务想要分别插入5，6，在获取插入行上的独占锁之前，每个锁都使用插入意图锁锁定4和7之间的间隙，但是不要互相阻塞，因为行是不冲突的，意向锁的涉及是为了插入的正确和高效。
    1

  • Next-key Locks（临键锁）：临键锁是间隙锁和记录锁的组合，临键锁遵循左开右闭原则（主要是为了包含等值记录）InnoDB默认加的行锁；

  • Record Locks（记录锁）：很显然该锁只锁定一行指定索引对应的数据行；

    加锁规则：（参考了博客https://blog.csdn.net/qq_40174198/article/details/111835482）

    • 唯一索引等值查询：当索引项存在时，next-key lock 退化为 record lock；当索引项不存在时，默认 next-key lock，访问到不满足条件的第一个值后next-key lock退化成gap lock；

    • 唯一索引范围查询：默认 next-key lock，(特殊’<=’ 范围查询直到访问不满足条件的第一个值为止)；

    • 非唯一索引等值查询：默认next-key lock ，索引项存在/不存在都是访问到不满足条件的第一个值后next-key lock退化成gap lock；

    • 非唯一索引范围查询：默认 next-key lock，向右访问到不满足条件的第一个值为止；
      （这里参考博客不太确定，后面补）

  死锁

  ​ 略；

5.MVCC多版本控制

当前读和快照读

• 首先需要明确的是：读并不等同于SELECT语句，我们在update、insert、delete等进行连接操作让 table1.a = table2.a、条件判断等等都会需要读取数据
• 当前读：每次读取最新的已提交的数据，换句话说，每次读取都能感知到任意commit的事务的数据，即使，这些事务在当前事务后启动；
  • update、insert、delete都是当前读
• 快照读：就是只能根据读视图（Read View）确定当前能读到什么数据；
  • select是快照读

Read View

• Read View（读视图）:当使用MVCC进行快照读的时候，InnoDB会自动生成记录当前数据库事务情况和当前事务以判断该事务能读取到哪一个版本的数据的数据结构；
  • 有如下字段：当前事务的id（creator_trx_id）；【必然可读】
  • 当前所有活跃的未提交的事务的id（trx_ids）；【可能可读】
  • 当前活跃中的事务的id的最大值+1（ low_limit_id）；【大于等于他都必然不可读】
  • 当前活跃中的事务id最小值 （up_limit_id）；【比起小则必然可读】
• 下面是寻找合适数据版本的规则：
  • 如果表中事务号（trx_id）比up_limit_id还要小，显然当前事务可以读取该行的数据；
  • 如果表中的事务号比最大的low_limit_id大或者相等，显然当前事务不能访问该版本的数据，通过回滚指针找undo log（中的update undo log）的“历史版本”，在undo log中由头直尾，知道找到合适的；
  • 如果事务号在俩者之间且和creator_trx_id相等那么自然可以访问（本事务做了update操作）；
  • 如果事务号在俩者之间且和creator_trx_id不相等，那就需要判断一下trx_id属性值是不是在trx_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

MVCC实现

• 多版本控制是基于4个隐式字段【隐含的自增ID、事务id、回滚指针、删除标志】、undo log【undo log中的日志链】、Read View【可读判断】实现的；上文已经介绍了4个隐式字段、undo log和Read View；
• Read View生成的时机：Read View直接决定了当前事务和其他事务的隔离程度，
  • RR隔离级别：如果我们每次使用SELECT均生成一个Read View，显然会出现不可重复读；
  • RC隔离级别：**只在事务开启时生成一个Read View；在这种隔离级别下，由于事务一执行select就已经确定了唯一快照读版本；**我们知道MVCC不能解决幻读，准确来说是不能解决事务中写操作的幻读问题；

RC隔离级别的幻读

• MVCC是可以让SELECT不存在幻读问题的，（select查询后，确定当前事务能够select的数据是确定的（即当前读视图能够读取的数据）
• 写操作的当前读存在幻读，先SELECT检查没有 x=1；
  • 然后企图在没有x=1下插入一条数据 x=1；由于是当前读，如果在这之前恰好有一个事务也插入了一条数据：x=1，并且已经提交；那么这时会发现居然有x=1；
• 所以为了让当前读和快照读一致，当SELECT结果会左右作为后续写操作的依据时，需要加上 for update

​

分区

• 分区类似MongoDB的分片，就是将数据在service层将表逻辑的划分为若干分区表，每个分区表本质是表的一部分，和在存储引擎层没有本质区别，但是查询的解析和优化器可以根据分区表将一个大表分为若干小表

执行过程

MySQL优化

SQL语句的优化

基础知识

• explain查询分析

  • 

  • id:查询id
    
    select_type:查询类型
        simple：查询语句不包含union或子查询的查询；
        primary：对于包含union、unionall或子查询的大查询来说，最左边的那个查询类型就是primary；
        union：对于包含union、unionall或子查询的大查询来说，除了最左边的那个查询外，其他查询都是union，和primary是一组；
        union result：当mysql选择使用临时表来完成union查询的去重工作时，针对该临时表的查询类型
    
    TYPE    
    	type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    
    table：表名；每条记录对应某个单表的访问方法，多个表关联会输入多条记录；非关联查询查询id相同，驱动表在前面；
    
    possible_keys：可能用到的索引
    
    key：实际使用的索引
    
    filtered：经过搜索条件过滤后，剩余记录条数的百分比
    
    rows：预估需要读取的记录数
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21

• DML、DDL、DCL、DQL：

  • DML：数据操纵语句（增删改）
    • 增：INSERT INTO tableName (cols……) VALUES (values[]……)
    • 删（和SELECT查询语法类似）：DELETE FROM tableName WHERE condition [可以使用limit]
    • 改：UPDATE tableName SET colName INTO value WHERE condition；[可以使用limit、order by]
  • DQL：数据查询语句（查）
    • SELECT colName……[函数] [AS 别名] FROM tableNames [连接操作 LEFT JOIN、RIGHT JOIN、INNER JOIN ON condition] WHERE condition [LIMIT、ORDER BY、GROUP BY、GROUP BY和HAVING字句]
  • DCL：数据控制语句（权限）
    • GRANT
  • DDL：数据定义语句（视图、索引、表（各数据项及数据约束：数据定义、范围、外键、主键等）、簇和同义词）
    • 增：CREATE VIEW\、INDEX、TABLE name
    • 删：DROP TABLE、INDEX、VIEW name
    • 改：ALTER TABLE、INDEX、VIEW name xxx
    • 查：SHOW INDEX FROM tableName

• 查询执行顺序问题

  • 查询过程中的表

    • 虚拟表：虚拟表，就是实际上并不存在（物理上不存在），但是逻辑上存在的表。主要有：临时表、内存表和视图，派生表；select语句可以返回虚拟表的是视图和派生表
    • 派生表就是子查询产生的临时表，所以可以当表使用；

  • 连接

    • 连接主要分为笛卡尔积与内连接、全连接及左(外)连接和右(外)连接、自然连接；

      • 笛卡尔积：(笛卡尔积的结果是表的行数相乘，会出现无效结果)，如果加上WHERE table1.xxx = table2.xxx就变成全连接

        --笛卡尔积
        SELECT XXX FROM table1, table2
        SELECT XXX FROM table1 INNER JOIN table2
        --内连接
        SELECT XXX FROM table1, table2 WHERE table1.XX = table2.XX
        1
        2
        3
        4
        5

      • 自然连接：mysql自动会将所有列名相等的列进行等值连接并且自动合并；

        SELECT XXX FROM table1 NATURAL JOIN table2
        1

      • 全连接及左连接和右连接：全连接就是即左连接又右连接，左\右外连接和左\右连接效果一样；左连接和右连接主要如果某一数据在另一边不存在（NULL）时，仍然记录；

        SELECT XXX FROM table1 LEFT JOIN table2
        SELECT XXX FROM table1 RIGHT JOIN table2
        SELECT XXX FROM table1 FULL JOIN table2
        1
        2
        3

  • 子查询

    • 关联查询和非关联查询

    • 关联查询：在嵌套子查询中，外层先进行查询，根据外层结果再进入子查询进行查询；

      • EXIST （字句）、一些使用了外部查询条件的嵌套查询（例如 子查询需要外部查询表的某一字段）

    • 非关联查询：在嵌套子查询中，先进行嵌套查询，再进行外层查询、外层查询根据嵌套查询结果进行；

      • IN （字句）、HAVING子查询

  • 当前查询

    • 

    • FROM—>ON—>JOIN—>WHERE—>GROUP BY---->WITH—>HAVING—>SELECT—>select list(筛选)—>ORDER BY —>LIMIT

      • FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

      • ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。

      • JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为 止。

      • WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

      • GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.

      • CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.

        • CUBE:生成的结果集显示了所选列中值的所有组合的聚合。【mysql不支持CUBE】

        • ROLLUP：生成的结果集显示了所选列中值的某一层次结构的聚合。

          SELECT *,COUNT(*) AS `num`
          FROM test a
          GROUP BY a.b,a.a,a.c
          with ROLLUP;
          //ROLLUP简单来说就是对分组后再次进行分组操作
          1
          2
          3
          4
          5

      • HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。

      • SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。

      • DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.

      • ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.

      • LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

• 索引有效问题

  • 失效条件：
    • 列索引：
      • LIKE：以 %、_开头时索引失效、但是以xxx%、_索引有效
      • OR：只有在 OR前后均可使用索引进行才会使用索引
      • 对索引进行操作（运算、聚合运算【特别是HAVING 中】、类型转换【例如字符串不加单引号】）
      • 对索引列进行IS NOT NULL、IS NULL
      • NOT、！=、<>均不能使用索引
      • 其他mysql判断全表扫描更快的情况；
    • 联合索引：
      • 符合最左匹配原则才可能有效，符合最优匹配原则后需要满足列索引规则；
  • 失效转有效策略
    • OR失效：为其中参与OR运算的列设置索引
    • 不等于（!=、<>)操作：转为 c1>xxx OR c1<xxx

数据库优化

数据拆分

基础

• 分库分表
  • 分库：数据库的线程模型和服务器的性能决定了数据库具有最大并发处理能力，所以为了提高并发处理能力需要将多数据库多服务器；分库后的数据模型取决于分表的方式
    • 如果不分表，显然是主从数据库模型
    • 垂直拆分：多写多读
    • 水平拆分：同样是多写多读
  • 分表：关系数据库中表结构的大小对于性能有很大的影响，尤其是IO效率，所以对于大量的数据需要将数据分片；
• 数据分片主要有俩个方向：
  • 垂直分片：即将业务关联不大的表分开，将表中业务处理关联不大的拆分，各个表自身保存数据关联大数据列
  • 水平拆分：水平拆分是将一个关系模式的数据行进行拆分，根据规则，将一个关系模式的一部分数据放在一个表，其他放在另一个表

垂直拆分

• 表结构拆分：即将一个表中多个属性拆分成多个表，使得每个表的属性减少；（大表拆小表）
• 表拆分：将业务关联不大的表放入不同的数据库

水平拆分

• 范围拆分：将id（或者某些字段）在某一范围的放在一个表
• 指定方式：各个服务器指定数据保存位置
• hash方式，根据某一字段进行hash决定数据保存位置

分库分表

重点学习怎么实现分库分表（水平拆分）

• 代理模式（MyCat、shardingsphere-proxy）：通过代理进行，将数据提交代理，代理根据分库分表策略保存到相应数据库的相应数据表，取数据同理
• JDBC层进行（shardingsphere-jdbc）：利用JDBC进行分库分表逻辑处理，应用程序只需要配置分库分表逻辑即可；

Shardingsphere

• 具体参考中间件的shardingSphere，下面学习shardingsphere-jdbc使用

shardingsphere-jdbc

快速开始

• 首先导入依赖

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>xxxx</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>xxxx</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10

• 读写分离配置（shardingsphere-jdbc只支持一主多从）

server.port=8070

#配置数据库逻辑名
spring.shardingsphere.datasource.names=master,slave
#逻辑名master的数据库配置
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://ip:port/database?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456
#逻辑名slave的数据库配置
spring.shardingsphere.datasource.slave.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave.jdbc-url=jdbc:mysql://ip:port/database/zy_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

#shardingsphere主从数据库逻辑名
spring.shardingsphere.masterslave.name=ms
#主库
spring.shardingsphere.masterslave.master-data-source-name=master
#从库，可以有多个
spring.shardingsphere.masterslave.slave-data-source-names=slave

#开始sql监控（方便debug-
spring.shardingsphere.props.sql.show=true

# mybatis 配置
mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.type-aliases-package=com.zypcy.sharding.business.entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

配置完成即可（无论是代码方式还是properties（或者yam方式），其都是低侵入的，所以和直接使用JDBC没有区别，真正进行路由都是由中间件完成；