Mysql数据库

抽抽了

已于 2023-10-17 16:52:11 修改

阅读量967

点赞数 1

分类专栏： mysql 文章标签： mysql

于 2021-03-07 17:36:03 首次发布

本文链接：https://blog.csdn.net/qq_33507618/article/details/114486035

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Mysql数据库

一、事务
二、索引
三、存储引擎中索引的实现
- 3.1 索引和存储引擎之间有什么关系？
四、慢SQL
五、分库分表

一、事务

事务就是⼀组原⼦性的SQL查询，或者说是⼀个独⽴的⼯作单元。事务内的语句要么全部成功，要么全部失败。

1.1 ACID

原子性（Atomcity）
一个事务必须被视为一个不可分割的最小单位，整个事务中所有的操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
一致性（Consiistency）
数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性（Isolation）
通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。
持久性（Durability）
一旦事务提交。则其所做的修改就会永久的保存到数据库中。这里持久性是个模糊的概念，实际上持久性也分很多不同的级别。

1.2 事务并发访问的问题

// 关闭⾃动提交 
set session autocommit=0;

// 查看自动提交状态
show variables like 'autocommit';

// 设置当前会话事务隔离级别
set session transaction isolation level read uncommitted;
// 设置全局事务隔离级别
set global transaction isolation level read uncommitted;

// 查看当前会话事务隔离级别
select @@tx_isolation; 
// 查看全局事务隔离级别
select @@global.tx_isolation;

脏读
事务A读取到了事务B中未提交的数据。

时间	事务A	事务B
1	开始事务	-
2	-	开始事务
3	-	查询余额有100
4	-	取出100，余额为0
5	查询余额为0	-
6	-	撤销掉事务（事务回滚）
7	存入50，余额为50	-
8	提交事务	-

不可重复读
事务A读取到了事务B已经提交了的数据。（两个事务并发的执行，结果A两次读取的结果不一样，这是因为两次查询有间隔，期间被其他事务修改并提交了事务）

时间	事务A	事务B
1	开始事务	-
2	-	开始事务
3	-	查询余额有100
4	查询余额有100	-
5	-	取出100，余额为0
6	-	提交事务
7	查询余额为0	-
8	提交事务	-

幻读
事务A读取到了事务B新增的提交了的数据。（两个事务正在并发的执⾏，事务A第⼀次统计和第⼆统计的结果不⼀样，是因为事务B新增了⼀条数据，和不可重复读⼀样，都是读取了另外⼀个事务的数据，不同的是不可重复读查询的是同⼀条数据，⽽幻读则是针对批量的数据，或者说不可重复读是A读取了B的更新数据。

时间	事务A	事务B
1	开始事务	-
2	-	开始事务
3	统计总金额1000	-
4	-	存入100
5	-	提交事务
6	统计总金额1100	-
8	提交事务	-

1.3 隔离级别

由低到高分别为：读未提交 < 读已提交 < 可重复读 < 可串行化。

READ UNCOMMITED（读未提交）
事务的修改，即使没有提交，对其他事务也都是可见的。事务可以读取到未提交的数据，这也被称为脏读。
READ COMMITED（读已提交）
大多数数据库默认的隔离级别都是读已提交（Mysql不是），一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。
REPEATABLE READ（可重复读）
解决了脏读的问题。该级别保证了在同一事务中多次读取同样的记录的结果是一只的。但是无法解决幻读的问题，所谓幻读，指的是当某个事务在读取某个范围内的记录时，会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制MVCC解决了幻读的问题。Mysql的默认事务隔离级别。
SERIALIZABLE（可串行化，几乎不用）
是最高的隔离级别。他通过强制事务串行执行，避免了前面说的幻读的问题。SERIALIZABLE会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争夺的问题，实际中很少使用。

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
可串行化	×	×	×

1.4 Spring的事务传播行为

@Transactional(propagation = Propagation.REQUIRED)
这个注解来设置事务的传播行为。

只读事务 readOnly属性：设置为只读事务，对于只读事务，它就不能进⾏更新操作，⼀般只存在数据读取的时候，可以将readOnly属性设置为true，可提供效率。

事务超时 timeout属性：代表事务的超时时间，默认为30s，⼀般情况下都不需要设置超时时间。如果超过时间就回滚。

七种事务传播行为：

REQUIRED
业务⽅法需要在⼀个事务中运⾏。如果⽅法运⾏时，已经处在⼀个事务中，那么这个时候就会加⼊到该事务中，如果当前没有事务环境的话，就会为⾃⼰创建⼀个新的事务。
SUPPORTS
这⼀事务属性表明，如果业务⽅法A在某个事务范围内被调⽤，则⽅法成为事务的⼀部分；如果业务⽅法在事务范围外被调⽤，则⽅法在没有事务的环境下执⾏。
即当标注了事务传播属性——SUPPORTS的业务⽅法在另⼀个bean的业务⽅法中执⾏时，如果另⼀个bean的业务⽅法开启了事务，它就会处在事务中执⾏，如果另⼀个bean的业务⽅法也没开启事务，那么它也在没有事务的环境中进⾏。
MANDATORY
该属性指定业务⽅法只能在⼀个已经存在的事务中执⾏，业务⽅法不能发起⾃⼰的事务。如果业务⽅法在没有事务的环境下调⽤，容器就会抛出异常。⼀种⽐较强硬的⽅式。
REQUIRES_NEW
该属性表明不管当前是否存在事务，业务⽅法总会为⾃⼰发起⼀个新的事务。如果⽅法已经运⾏在⼀个事务中，则原有事务会被挂起，新的事务会被创建，直到⽅法执⾏结束，新事务才算结束，原先的事务才会恢复执⾏。
NOT_SUPPORTED
声明⽅法不需要事务。如果⽅法没有关联到⼀个事务，容器不会为它开启事务。如果⽅法在⼀个事务中被调⽤(在其他业务bean的⽅法中被调⽤了，⽽其他业务bean的⽅法是开启了事务的)，该事务会被挂起，在⽅法调⽤结束后，原先的事务便会恢复执⾏。
NEVER
指定业务⽅法绝对不能在事务范围内执⾏。如果业务⽅法在某个事务中执⾏，容器会抛出异常，只有业务⽅法没有关联到任何事务，才能正常执⾏。⽐较强硬的⽅式，就是不⽀持事务。
NESTED
（嵌套事务）如果⼀个活动的事务存在，则当前⽅法运⾏在⼀个嵌套的事务中。如果没有活动事务，就创建⼀个新的事务。它使⽤了⼀个单独的事务，这个事务拥有多个可以回滚的保存点。内部事务的回滚不会对外部事务造成影响。外部事务回滚会导致内部事务的回滚。

二、索引

2.1 什么是索引？

可以比喻成书的目录和书的正文内容的关系。

索引是按照特定的数据结构把数据表中的数据放在索引⽂件中，以便于快速查找；
索引存在于磁盘中，会占据物理空间。

2.2 索引的类型

FULLTEXT
FULLTEXT即为全文索引，目前只有MyISAM引擎支持。可在 CHAR、VARCHAR ，TEXT列上创建全文索引。它并不是和MyISAM一起诞生的，它的出现是为了解决like ‘%文字%’ 这类针对文本的模糊查询效率较低的问题。
HASH
由于HASH的唯⼀（⼏乎100%的唯⼀）及类似键值对的形式，很适合作为索引。HASH索引可以⼀次定位，不需要像树形索引那样逐层查找，因此具有极⾼的效率。但是这种⾼效是有条件的，即只在 “=” 和 “in” 条件下⾼效，对于范围查询、排序及组合索引仍然效率不⾼。
BTREE
BTREE索引就是⼀种将索引值按⼀定的算法，存⼊⼀个树形的数据结构中（⼆叉树），每次查询都是从树的⼊⼝root开始，依次遍历node，获取leaf。这是MySQL⾥默认和最常⽤的索引类型。不使用B-树的原因是：减少IO操作，降低查询次数。
RTREE
RTREE在MySQL很少使⽤，仅⽀持地理数据类型，Mysql⼏乎不⽤。

2.3 索引的种类

主键索引：MySQL中主键必须唯⼀且不能有空值，因此在主键上的索引也是唯⼀索引。⼀个表上的唯⼀索引可以有多个，但主键只有⼀个。
唯⼀索引：唯⼀索引顾名思义，索引必须唯⼀，唯⼀索引中允许有空值出现。
普通索引
组合索引 INDEX(A, B, C)

2.4 索引的操作

创建索引

// 创建普通索引
CREATE INDEX index_name ON table_name(col_name);

// 创建唯⼀索引 
CREATE UNIQUE INDEX index_name ON table_name(col_name);

// 创建普通组合索引 
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

// 创建唯⼀组合索引 
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

// 通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);

// 创建表时直接指定索引
CREATE TABLE table_name (
   ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);

// 删除索引
DROP INDEX index_name ON table_name;

三、存储引擎中索引的实现

3.1 索引和存储引擎之间有什么关系？

在MySQL中，索引是在存储引擎中实现的；不同的存储引擎可能⽀持不同的索引类型；不同的存储引擎对同⼀种索引类型可能有不同的实现⽅式。

InnoDB存储引擎
B+树非叶子节点不存数据只存指针。
叶子结点是双向链表。
MyISAM存储引擎

四、慢SQL

⽬前数据库基本上都是读写分离架构，读在从库（slave）上执⾏，写在主库（master）上执⾏。由于从库的数据都是从主库上复制过去的，主库等待较多的，会加⼤与从库的复制时延。执⾏次数多的SQL优先治理，如果有⼀类SQL⾼并发集中访问某⼀张表，应当优先治理。

4.1 为何要对慢SQL进行治理？

从数据库⻆度看：每个SQL执⾏都需要消耗⼀定I/O资源，SQL执⾏的快慢，决定资源被占⽤时间的⻓短。假设总资源是100，有⼀条慢SQL占⽤了30的资源共计1分钟。那么在这1分钟时间内，其他SQL能够分配的资源总量就是70，如此循环，当资源分配完的时候，所有新的SQL执⾏将会排队等待。
从应⽤的⻆度看：SQL执⾏时间⻓意味着等待，在OLTP应⽤当中，⽤户的体验较差。治理的优先级上master数据库->slave数据库

4.2 MySQL执行原理

解析：词法解析->语法解析->逻辑计划->查询优化->物理执⾏计划检查是否存在可⽤查询缓存结果，如果没有或者缓存失效，则调⽤mysql_execute_command执⾏。
执⾏：检查⽤户、表权限->表上加共享读锁->取数据到query cache->取消共享读锁。

4.3 慢查询

通过查询mysql的读写⽐例,可以做相应的配置优化。

// 显示全部mysql操作信息
show status; 

// 获得mysql的插⼊次数;
show status like "com_insert%"; 

// 获得mysql的删除次数;
show status like "com_delete%"; 

// 获得mysql的查询次数;
show status like "com_select%"; 

// 获得mysql服务器运⾏时间
show status like "uptime"; 

// 获得mysql连接次数
show status like 'connections'; 

// 如果你不写 [session|global] 默认是session会话只取出当前窗⼝的执⾏，如果你想看所有(从mysql 启动到现在，则应该 global)
show [session|global] status like ....

当Mysql性能下降时，通过开启慢查询来获得哪条SQL语句造成的响应过慢，进⾏分析处理。当然开启慢查询会带来CPU损耗与⽇志记录的IO开销，所以我们要间断性的打开慢查询⽇志来查看Mysql运⾏状态。

慢查询能记录下所有执⾏超过long_query_time时间的SQL语句, ⽤于找到执⾏慢的SQL, ⽅便我们对这些SQL进⾏优化。

// 是否开启慢查询;
show variables like "%slow%"; 

// 查询慢查询SQL状况;
show status like "%slow%"; 

// 慢查询时间
show variables like "long_query_time";

慢查询开启设置

// 默认情况下，mysql认 为10秒才是⼀个慢查询
show variables like 'long_query_time';  

// 修改慢查询时间,只能当前会话有效;
set long_query_time=1;  

// 启⽤慢查询 ,加上global，不然会报错的
set global slow_query_log='ON';

4.4 SQL语句常见优化

不使⽤⼦查询

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='test');

⼦查询在MySQL5.5版本⾥，内部执⾏计划器是这样执⾏的：先查外表再匹配内表，⽽不是先查内表t2，当外表的数据很⼤时，查询速度会⾮常慢。在MariaDB10/MySQL5.6版本⾥，采⽤join关联⽅式对其进⾏了优化，这条SQL会⾃动转换为：

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE⼦查询⽆效，生产环境尽量应避免使用⼦查询！！！！

避免函数索引

SELECT * FROM t WHERE YEAR(d) >= 2016;

由于MySQL不像Oracle那样⽀持函数索引，即使d字段有索引，也会直接全表扫描。应改为

SELECT * FROM t WHERE d >= '2016-01-01';

⽤IN来替换OR

低效查询

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

⾼效查询

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

LIKE双百分号⽆法使⽤到索引

SELECT * FROM t WHERE name LIKE '%de%'; // 索引失效
SELECT * FROM t WHERE name LIKE 'de%'; // 索引有效

读取适当的记录LIMIT M,N
分组统计可以禁⽌排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默认情况下，MySQL对所有GROUP BY col1，col2…的字段进⾏排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁⽌排序。

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

禁⽌不必要的ORDER BY排序

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = .user_id WHERE 1 = 1 ORDER BY u.create_time DESC; // 默认是创建时间排序
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

正确使⽤组合索引（最前原则） index （a,b,c）。a |a,b |a,b,c|

五、分库分表

5.1 为什么要分库分表？

关系型数据库本身⽐较容易成为系统瓶颈，单机存储容量、连接数、处理能⼒都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进⾏切分了，切分的⽬的就在于减少数据库的负担，缩短查询时间。

数据库分布式核⼼内容⽆⾮就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单⼀数据库中的数据量变小，通过扩充主机的数量缓解单⼀数据库的性能问题，从⽽达到提升数据库操作性能的⽬的。

数据切分根据其切分类型，可以分为两种⽅式：垂直（纵向）切分和水平（横向）切分。

5.2 垂直（纵向）切分

垂直切分常⻅有垂直分库和垂直分表两种。

垂直分库

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与⼤系统拆分为多个⼩系统类似，按业务分类进⾏独⽴划分。与"微服务治理"的做法相似，每个微服务使⽤单独的⼀个数据库。如图：

垂直分表

垂直分表是基于数据库中的"列"进⾏，某个表字段较多，可以新建⼀张扩展表，将不经常⽤或字段⻓度较⼤的字段拆分出去到扩展表中。在字段很多的情况下（例如⼀个⼤表有100多个字段），通过"⼤表拆⼩表"，更便于开发与维护，也能避免跨⻚问题，MySQL底层是通过数据⻚存储的，⼀条记录占⽤空间过⼤会导致跨⻚，造成额外的性能开销。另外数据库以⾏为单位将数据加载到内存中，这样表中字段⻓度较短且访问频率较⾼，内存能加载更多的数据，命中率更⾼，减少了磁盘IO，从⽽提升了数据库性能。

垂直切分的优点：

解决业务系统层⾯的耦合，业务清晰, 与微服务的治理类似，也能对不同业务的数据进⾏分级管理、维护、监控、扩展等；
⾼并发场景下，垂直切分⼀定程度的提升IO、数据库连接数、单机硬件资源的瓶颈。

垂直切分的缺点：

部分表⽆法join，只能通过接⼝聚合⽅式解决，提升了开发的复杂度；
分布式事务处理复杂；
依然存在单表数据量过⼤的问题（需要⽔平切分）。

5.3 水平（横向）切分

当⼀个应⽤难以再细粒度的垂直切分，或切分后数据量⾏数巨⼤，存在单库读写、存储性能瓶颈，这时候就需要进⾏⽔平切分了。⽔平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同⼀个表按不同的条件分散到多个数据库或多个表中，每个表中只包含⼀部分数据，从⽽使得单个表的数据量变⼩，达到分布式的效果。

库内分表只解决了单⼀表数据量过⼤的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压⼒来说，帮助不是很⼤，⼤家还是竞争同⼀个物理机的CPU、内存、⽹络IO，最好通过分库分表来解决。

⽔平切分的优点：

不存在单库数据量过⼤、⾼并发的性能瓶颈，提升系统稳定性和负载能⼒；
应⽤端改造较⼩，不需要拆分业务模块。

⽔平切分的缺点：

跨分⽚的事务⼀致性难以保证跨库的join关联查询性能较差；
数据多次扩展难度和维护量极⼤。

分片规则

⽔平切分后同⼀张表会出现在多个数据库/表中，每个库/表的内容不同。⼏种典型的数据分片规则：

根据数值范围

按照时间区间或ID区间来切分。
例如：按⽇期将不同⽉甚⾄是⽇的数据分散到不同的库中。将userId为1-9999的记录分到第⼀个库，0000-20000的分到第⼆个库，以此类推。某种意义上，某些系统中使⽤的"冷热数据分离"，将⼀些使⽤较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

根据数值范围分片的优点：

单表大小可控；
天然便于⽔平扩展，后期如果想对整个分⽚集群扩容时，只需要添加节点即可；
⽆需对其他分⽚的数据进⾏迁移；
使⽤分⽚字段进⾏范围查找时，连续分⽚可快速定位分⽚进⾏快速查询，有效避免跨分⽚查询的问题。

根据数值范围分片的缺点：

热点数据成为性能瓶颈。连续分⽚可能存在数据热点，例如按时间字段分⽚，有些分⽚存储最近时间段内的数据，可能会被频繁的读写，⽽有些分⽚存储的历史数据，则很少被查询。

根据数值取模

⼀般采⽤hash取模mod的切分⽅式。
例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0的放到第⼀个库，余数为1的放到第⼆个库，以此类推。这样同⼀个⽤户的数据会分散到同⼀个库中，如果查询条件带有cusno字段，则可明确定位到相应库去查询。

在这里插入图片描述

优点：

数据分⽚相对⽐较均匀，不容易出现热点和并发访问的瓶颈。

缺点：

后期分⽚集群扩容时，需要迁移旧的数据（使⽤⼀致性hash算法能较好的避免这个问题。对2³² [ip地址的最大值] 进行取余，结果会顺顺时针落到物理机的hash环上的节点上。优点：所有数据都在hash环中，某一个节点宕机，尽量影响一小部分的数据，不影响其他节点的数据。hash偏环的解决：虚拟出来很多hash环节点，这样数据越均匀）；
容易⾯临跨分⽚查询的复杂问题。⽐如上例中，如果频繁⽤到的查询条件中不带cusno时，将会导致⽆法定位数据库，从⽽需要同时向4个库发起查询，再在内存中合并数据，取最⼩集返回给应⽤，分库反⽽成为拖累。（解决方案：做数据冗余，对cusno和查询条件的字段[如：name]在插入的时候，做映射关系并存储到redis/mysql中，这样，在查询时，先根据映射关系找到cusno，根据cusno就可以定位数据库查询想要的结果。）

5.4 分库分表带来的问题

事务⼀致性问题

当更新内容同时分布在不同库中，不可避免会带来跨库事务问题。跨分⽚事务也是分布式事务，没有简单的⽅案，⼀般可使⽤ “XA协议” 和 “两阶段提交” 处理。

分布式事务能最⼤限度保证了数据库操作的原⼦性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延⻓了事务的执⾏时间。导致事务在访问共享资源时发⽣冲突或死锁的概率增⾼。随着数据库节点的增多，这种趋势会越来越严重，从⽽成为系统在数据库层⾯上⽔平扩展的枷锁。

最终⼀致性对于那些性能要求很⾼，但对⼀致性要求不⾼的系统，往往不苛求系统的实时⼀致性，只要在允许的时间段内达到最终⼀致性即可，可采⽤事务补偿的⽅式。与事务在执⾏中发⽣错误后⽴即回滚的⽅式不同，事务补偿（开异步线程）是⼀种事后检查补救的措施，⼀些常⻅的实现⽅法有：对数据进⾏（异步线程进行）对账检查，基于⽇志进⾏对⽐，定期同标准数据来源进⾏同步等等。事务补偿还要结合业务系统来考虑。（详见分布式事务解决方案的本地消息表解决方案）

分布式事务解决方案

本地消息表（异步确保）

本地消息表与业务数据表处于同一个数据库中，这样就能利用本地事务来保证在对这两个表的操作满足事务特性，并且使用了消息队列来保证最终一致性。

在分布式事务操作的一方完成写业务数据的操作之后向本地消息表发送一个消息，本地事务能保证这个消息一定会被写入本地消息表中；
之后将本地消息表中的消息转发到 Kafka 等消息队列中，如果转发成功则将消息从本地消息表中删除，否则继续重新转发；
在分布式事务操作的另一方从消息队列中读取一个消息，并执行消息中的操作。
很少用~~（同时可以在A事务的业务代码中提供一个查询接口，供B事务的业务代码调用对比数据）~~。

优点：一种非常经典的实现，避免了分布式事务，实现了最终一致性。

缺点：消息表会耦合到业务系统中，如果没有封装好的解决方案，会有很多杂活需要处理。

两阶段提交（2PC）

两阶段提交（Two-phase Commit，2PC），通过引入协调者（Coordinator）来协调参与者的行为，并最终决定这些参与者是否要真正执行事务。

准备阶段
协调者询问参与者事务是否执行成功，参与者发回事务执行结果。
提交阶段
如果事务在每个参与者上都执行成功，事务协调者发送通知让参与者提交事务；否则，协调者发送通知让参与者回滚事务。
需要注意的是，在准备阶段，参与者执行了事务，但是还未提交。只有在提交阶段接收到协调者发来的通知后，才进行提交或者回滚。

存在的问题：

同步阻塞所有事务参与者在等待其它参与者响应的时候都处于同步阻塞状态，无法进行其它操作。
单点问题协调者在 2PC 中起到非常大的作用，发生故障将会造成很大影响。特别是在阶段二发生故障，所有参与者会一直等待状态，无法完成其它操作。
数据不一致在阶段二，如果协调者只发送了部分 Commit 消息，此时网络发生异常，那么只有部分参与者接收到 Commit 消息，也就是说只有部分参与者提交了事务，使得系统数据不一致。
太过保守任意一个节点失败就会导致整个事务失败，没有完善的容错机制。

补偿事务（TCC）

TCC 其实就是采用的补偿机制，其核心思想是：针对每个操作，都要注册一个与其对应的确认和补偿（撤销）操作。它分为三个阶段：

Try 阶段主要是对业务系统做检测及资源预留；
Confirm 阶段主要是对业务系统做确认提交，Try阶段执行成功并开始执行 Confirm阶段时，默认 Confirm阶段是不会出错的。即：只要Try成功，Confirm一定成功；
Cancel 阶段主要是在业务执行错误，需要回滚的状态下执行的业务取消，预留资源释放。

举个例子，假入 Bob 要向 Smith 转账，思路大概是：我们有一个本地方法，里面依次调用

首先在 Try 阶段，要先调用远程接口把 Smith 和 Bob 的钱给冻结起来；
在 Confirm 阶段，执行远程调用的转账的操作，转账成功进行解冻；
如果第2步执行成功，那么转账成功，如果第二步执行失败，则调用远程冻结接口对应的解冻方法 (Cancel)。

优点：跟2PC比起来，实现以及流程相对简单了一些，但数据的一致性比2PC也要差一些

缺点：缺点还是比较明显的，在2,3步中都有可能失败。TCC属于应用层的一种补偿方式，所以需要程序员在实现的时候多写很多补偿的代码，在一些场景中，一些业务流程可能用TCC不太好定义及处理。

MQ 事务消息

有一些第三方的MQ是支持事务消息的，比如RocketMQ，他们支持事务消息的方式也是类似于采用的二阶段提交，但是市面上一些主流的MQ都是不支持事务消息的，比如 RabbitMQ 和 Kafka 都不支持。

以阿里的 RocketMQ 中间件为例，其思路大致为：

第一阶段Prepared消息，会拿到消息的地址；
第二阶段执行本地事务，第三阶段通过第一阶段拿到的地址去访问消息，并修改状态。

也就是说在业务方法内要想消息队列提交两次请求，一次发送消息和一次确认消息。如果确认消息发送失败了RocketMQ会定期扫描消息集群中的事务消息，这时候发现了Prepared消息，它会向消息发送者确认，所以生产方需要实现一个check接口，RocketMQ会根据发送端设置的策略来决定是回滚还是继续发送确认消息。这样就保证了消息发送与本地事务同时成功或同时失败。
在这里插入图片描述
优点：实现了最终一致性，不需要依赖本地数据库事务。

缺点：实现难度大，主流MQ不支持，RocketMQ事务消息部分代码也未开源。

跨节点关联查询 join问题

切分之前，系统中很多列表和详情⻚所需的数据可以通过sql join来完成。⽽切分之后，数据可能分布在不同的节点上，此时join带来的问题就⽐较麻烦了，考虑到性能，尽量避免使⽤join查询。

解决这个问题的⼀些⽅法：

全局表
全局表，也可看做是"数据字典表"，就是系统中所有模块都可能依赖的⼀些表，为了避免跨库join
查询，可以将这类表在每个数据库中都保存⼀份。这些数据通常很少会进⾏修改，所以也不担⼼⼀
致性的问题。
字段冗余
⼀种典型的反范式设计，利⽤空间换时间，为了性能⽽避免join查询。例如：订单表保存userId时
候，也将userName冗余保存⼀份，这样查询订单详情时就不需要再去查询"买家user表"了。
数据组装
在系统层⾯，分两次查询，第⼀次查询的结果集中找出关联数据id，然后根据id发起第⼆次请求得
到关联数据。最后将获得到的数据进⾏字段拼装。

跨节点分页、排序、函数问题

跨节点多库进⾏查询时，会出现limit分⻚、order by排序等问题。分⻚需要按照指定字段进⾏排序，当排序字段就是分⽚字段时，通过分⽚规则就⽐较容易定位到指定的分⽚；当排序字段⾮分⽚字段时，就变得⽐较复杂了。需要先在不同的分⽚节点中将数据进⾏排序并返回，然后将不同分⽚返回的结果集进⾏汇总和再次排序，最终返回给⽤户。如图所示：
在这里插入图片描述

全局主键避重问题

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使⽤的⾃增⻓将⽆⽤武之地，某个分区数据库⾃⽣成的ID⽆法保证全局唯⼀。因此需要单独设计全局主键，以避免跨库主键重复问题。有⼀些常⻅的主键⽣成策略：

UUID
UUID标准形式包含32个16进制数字，分为5段，形式为844412的36个字符，例如：
550e8400e29b41d4a716446655440000。UUID是主键是最简单的⽅案，本地⽣成，性能⾼，没有⽹络耗时。但缺点也很明显，由于UUID⾮常⻓，会占⽤⼤量的存储空间；另外，作为主键建⽴索引和基于索引进⾏查询时都会存在性能问题，在InnoDB下，UUID的⽆序性会引起数据位置频繁变动，导致分⻚。
结合数据库维护主键ID表
这⼀⽅案的整体思想是：建⽴2个以上的全局ID⽣成的服务器，每个服务器上只部署⼀个数据库，每个库有⼀张sequence表⽤于记录当前全局ID。表中ID增⻓的步⻓是库的数量，起始值依次错开，这样能将ID的⽣成散列到各个数据库上。如下图所示：由两个数据库服务器⽣成ID，设置不同的auto_increment值。第⼀台sequence的起始值为1，每次步⻓增⻓2，另⼀台的sequence起始值为2，每次步⻓增⻓也是2。结果第⼀台⽣成的ID都是奇数（1, 3, 5, 7…），第⼆台⽣成的ID都是偶数（2, 4, 6, 8 …）。这种⽅案将⽣成ID的压⼒均匀分布在两台机器上。同时提供了系统容错，第⼀台出现了错误，可以⾃动切换到第⼆台机器上获取ID。
但有以下⼏个缺点：系统添加机器，⽔平扩展时较复杂；每次获取ID都要读写⼀次DB，DB的压⼒还是很⼤，只能靠堆机器来提升性能。
Snowflake分布式⾃增ID算法
Twitter的snowflake算法解决了分布式系统⽣成全局ID的需求，⽣成64位的Long型数字，组成部分：

第⼀位未使⽤
接下来41位是毫秒级时间，41位的⻓度可以表示69年的时间
5位datacenterId，5位workerId。10位的⻓度最多⽀持部署1024个节点
最后12位是毫秒内的计数，12位的计数顺序号⽀持每个节点每毫秒产⽣4096个ID序列

理论上QPS约为409.6w/s（1000*2^12），并且整个分布式系统内不会产⽣ID碰撞；可根据⾃身业务灵活分配bit位。
不⾜就在于：强依赖机器时钟，如果时钟回拨，则可能导致⽣成ID重复。
加粗样式

Leaf——美团点评分布式ID⽣成系统考虑到了⾼可⽤、容灾、分布式推荐使⽤。

数据迁移、扩容问题

当业务⾼速发展，⾯临性能和存储的瓶颈时，才会考虑分⽚设计，此时就不可避免的需要考虑历史数据迁移的问题。⼀般做法是先读出历史数据，然后按指定的分⽚规则再将数据写⼊到各个分⽚节点中。此外还需要根据当前的数据量和QPS，以及业务发展的速度，进⾏容量规划，推算出⼤概需要多少分⽚（⼀般建议单个分⽚上的单表数据量不超过1000W）

如果采⽤数值范围分⽚，只需要添加节点就可以进⾏扩容了，不需要对分⽚数据迁移。如果采⽤的是数值取模分⽚，则考虑后期的扩容问题就相对⽐较麻烦。

什么时候考虑切分？

能不切分尽量不要切分并不是所有表都需要进⾏切分，主要还是看数据的增⻓速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要⼯作之⼀。不到万不得已不⽤轻易使⽤分库分表这个⼤招，避免"过度设计"和"过早优化"。分库分表之前，不要为分⽽分，先尽⼒去做⼒所能及的事情，例如：升级硬件、升级⽹络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。
数据量过⼤、增⻓过快，正常运维影响业务访问。
– 对数据库备份，如果单表太⼤，备份时需要⼤量的磁盘IO和⽹络IO。例如1T的数据，⽹络传输占50MB时候，需要20000秒才能传输完毕，整个过程的⻛险都是⽐较⾼的
– 对⼀个很⼤的表进⾏DDL修改时，MySQL会锁住全表，这个时间会很⻓，这段时间业务不能访问此表，影响很⼤。⼤表会经常访问与更新，就更有可能出现锁等待。将数据切分，⽤空间换时间，变相降低访问压⼒
随着业务发展，需要对某些字段垂直拆分。
安全性和可⽤性。
鸡蛋不要放在⼀个篮⼦⾥。在业务层⾯上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为⼀个业务把数据库搞挂⽽牵连到其他业务。利⽤⽔平切分，当⼀个数据库出现问题时，不会影响到100%的⽤户，每个库只承担业务的⼀部分数据，这样整体的可⽤性就能提⾼。

数据迁移方案

双写法
在原来数据库基础上增加一个库，在此时，业务代码写入老库的同时，也写入新的库，并使用同步工具同步老库的数据到新库，删改新库时业务代码会报错，但是最终一致性没问题。
追日志法
mysql的binlog日志发送kafka，读取完毕后，kafka客户端一条一条顺序消费到新业务，新业务CUD操作新库。

Mycat分库分表的常用场景

Mycat是一个开源的分布式数据库系统，是一个实现了Mysql协议的Server，前端用户可以把它看成是一个数据库代理，用Mysql客户端工具和命令行访问，而其后端可以用Mysql原生协议与多个Mysql服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分库分表，即将一个大表水平分割为N个小表，存储在后端Mysql服务器里或者其他数据库里。

常用场景：