MySql分库分表与分区的区别和思考
原文：GrimMjx
https://www.cnblogs.com/GrimMjx/p/11772033.html?tdsourcetag=s_pctim_aiomsg
1.分分合合
说过很多次，不要拘泥于某一个技术的一点，技术是相通的。重要的是编程思想，思想是最重要的。当数据量大的时候，需要具有分的思想去细化粒度。当数据量太碎片的时候，需要具有合的思想来粗化粒度。
1.1 分
很多技术都运用了分的编程思想，这里来举几个例子，这些都是分的思想
- 集中式服务发展到分布式服务
- 从Collections.synchronizedMap(x)到1.7ConcurrentHashMap再到1.8ConcurrentHashMap，细化锁的粒度的同时依旧保证线程安全
- 从AtomicInteger到LongAdder，ConcurrentHashMap的size()方法。用分散思想，减少cas次数，增强多线程对一个数的累加
- JVM的G1 GC算法，将堆分成很多Region来进行内存管理
- Hbase的RegionServer中，将数据分成多个Region进行管理
- 平时开发是不是线程池都资源隔离
1.2 合
很多技术也运用到了合的编程思想，这里举几个例子，这些都是合的思想
- TLAB（Thread Local Allocation Buffers），线程本地分配缓存。避免多线程冲突，提高对象分配效率
- 逃逸分析，将变量的实例化内存直接在栈里分配，无需进入堆，线程结束栈空间被回收。减少临时对象在堆内分配数量
- CMS GC算法下，虽然使用标记清除，但是也有配置支持整理内存碎片。如：-XX:UseCMS-CompactAtFullCollection（FullGC后是否整理，Stop The World会变长）和-XX:CMSFullGCs-BeforeCompaction（几次FullGC之后进行压缩整理）
- 锁粗化，当JIT发现一系列连续的操作都是对同一对象反复加锁和释放锁，会加大锁同步的范围
- kafka的网络数据传输有一些数据配置，减少网络开销。如：batch.size和linger.ms等等
- 平时开发是不是都个叫批量获取接口
2.分区
本文一切基于MySql InnoDB
说了这么多，接下来说主体，先说分区，因为之前博主写过一篇MySql分区的博客所以这里不会多费笔墨来写，具体见：https://www.cnblogs.com/GrimMjx/p/10526821.html
2.1 InnoDB逻辑存储结构
首先要先介绍一下InnoDB逻辑存储结构和区的概念，它的所有数据都被逻辑地存放在表空间，表空间又由段，区，页组成。
段
段就是上图的segment区域，常见的段有数据段、索引段、回滚段等，在InnoDB存储引擎中，对段的管理都是由引擎自身所完成的。
区
区就是上图的extent区域，区是由连续的页组成的空间，无论页的大小怎么变，区的大小默认总是为1MB。为了保证区中的页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区，InnoDB页的大小默认为16kb，即一个区一共有64（1MB/16kb=16）个连续的页。每个段开始，先用32页（page）大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表或者是undo类的段，可以开始申请较小的空间，节约磁盘开销。
页
页就是上图的page区域，也可以叫块。页是InnoDB磁盘管理的最小单位。默认大小为16KB，可以通过参数innodb_page_size来设置。常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页，未压缩的二进制大对象页，压缩的二进制大对象页等。
2.1.2 分区概述
分区
这里讲的分区，此“区”非彼“区”，这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件，不是我们刚刚说的区。MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。要知道MySQL是面向OLTP的数据，它不像TIDB等其他DB。那么对于分区的使用应该非常小心，如果不清楚如何使用分区可能会对性能产生负面的影响。
MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。目前MySQL数据库还不支持全局分区。
无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。
2.1.3 分区类型
目前MySQL支持一下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。
RANGE分区
RANGE分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型，那么只用存yyyyMM就好了。也不用关心函数了。
CREATE TABLE `m_test_db`.`Order` ( `id` INT NOT NULL AUTO_INCREMENT, `partition_key` INT NOT NULL, `amt` DECIMAL(5) NULL, PRIMARY KEY (`id`, `partition_key`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901), PARTITION part1 VALUES LESS THAN (201902), PARTITION part2 VALUES LESS THAN (201903), PARTITION part3 VALUES LESS THAN (201904), PARTITION part4 VALUES LESS THAN (201905)) ;
这时候我们先插入一些数据
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');
现在我们查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区
如果sql语句有问题，那么会走所有区。会很危险。所以分区表后，select语句必须走分区键。
以下3种不是太常用，就一笔带过了。
LIST分区
LIST分区和RANGE分区很相似，只是分区列的值是离散的，不是连续的。LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。
HASH分区
说到哈希，那么目的很明显了，将数据均匀的分布到预先定义的各个分区中，保证每个分区的数量大致相同。
KEY分区
KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。
2.1.4 分区和性能
一项技术，不是用了就一定带来益处。比如显式锁功能比内置锁强大，你没玩好可能导致很不好的情况。分区也是一样，不是启动了分区数据库就会运行的更快，分区可能会给某些sql语句性能提高，但是分区主要用于数据库高可用性的管理。数据库应用分为2类，一类是OLTP（在线事务处理），一类是OLAP（在线分析处理）。对于OLAP应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。在OLTP应用中，分区更加要小心，通常不会获取一张大表的10%的数据，大部分是通过索引返回几条数据即可。
比如一张表1000w数据量，如果一句select语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3，现在有10个分区。那么不是要(3+3)*10次的逻辑IO？（3次聚集索引，3次辅助索引，10个分区）。所以在OLTP应用中请小心使用分区表。
在日常开发中，如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取，partitions标识走了哪几个分区。
mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00'; +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | ClientActionTrack | p20160825 | ALL | NULL | NULL | NULL | NULL | 33868 | Using where | +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec)
2.2实现方式
具体如何实现上面链接里有写，这里只需记住如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。
这个是数据库分的，应用透明，代码无需修改任何东西。
2.3内部文件
先去data目录，如果不知道目录位置的可以执行：
接下来看下内部文件：
从上图我们可以看出，有2中类型的文件，.frm文件和.ibd文件
- .frm文件：表结构文件
- .ibd文件：InnoDB中，索引和数据都在同个文件.ibdata（你的执行结果可能是.MYD索引文件和.MYI数据文件，没关系，这是MyIsAm存储引擎，对应着InnoDB的.ibd文件）。因为Order这张表分为5个区，所以有5个这样的文件
- .par文件：你执行的结果可能有.par文件也可能没有。注意：从MySql 5.7.6开始，不再创建.par分区定义文件。分区定义存储在内部数据字典中。
2.4 数据处理
分区表后，提高了MySql性能。如果一张表的话，那就只有一个.ibd文件，一颗大的B+树。如果分表后，将按分区规则，分成不同的区，也就是一个大的B+树，分成多个小的树。
（PS：如果想研究一颗聚集索引B+树可以放多少行数据，请看：https://www.cnblogs.com/GrimMjx/p/10540263.html）
读的效率肯定提升了，如果走分区键索引的话，先走对应分区的辅助索引B+树，再走对应分区的聚集索引B+树。
如果没有走分区键，将会在所有分区都会执行一次。会造成多次逻辑IO！平时开发如果想查看sql语句的分区查询可以使用explain partitons select xxxxx语句。可以看到一句select语句走了几个分区。
mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | ClientActionTrack | p20160825 | ALL | NULL | NULL | NULL | NULL | 33868 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
row in set (0.00 sec)
3.分库分表
当一张表随着时间和业务的发展，库里表的数据量会越来越大。数据操作也随之会越来越大。一台物理机的资源有限，最终能承载的数据量、数据的处理能力都会受到限制。这时候就会使用分库分表来承接超大规模的表，单机放不下的那种。
区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。
3.1 实现
3.1.1 分库分表标准
- 存储占用100G+
- 数据增量每天200w+
- 单表条数1亿条+
3.1.2 分库分表字段
分库分表字段取值非常重要
- 在大多数场景该字段是查询字段
- 数值型
一般使用userId，可以满足上述条件
3.2 分布式数据库中间件
分布式数据库中间件分为两种，proxy和客户端式架构。proxy模式有MyCat、DBProxy等，客户端式架构有TDDL、Sharding-JDBC等。那么proxy和客户端式架构有何区别呢？各自有什么优缺点呢？其实看一张图便可知晓。
proxy模式的话我们的select和update语句都是发送给代理，由这个代理来操作具体的底层数据库。所以必须要求代理本身需要保证高可用，否则数据库没有宕机，proxy挂了，那就走远了。
客户端模式通常在连接池上做了一层封装，内部与不同的库连接，sql交给smart-client进行处理。通常仅支持一种语言，如果其他语言要使用，需要开发多语言客户端。
各自的优缺点如下：
3.3 内部文件
找了一个分库分表+分区的例子，基本上和分区表的差不多，只是多了多了很多表的.ibd文件，上面有文件的解释：
[miaojiaxing@Grim testmydata]# ls | grep 'base_info'
base_info_00.frm
base_info_00#P#p_2018.ibd
base_info_00#P#p_2019.ibd
base_info_00#P#p_2020.ibd
base_info_00#P#p_2021.ibd
base_info_00#P#p_init.ibd
base_info_00#P#p_max.ibd
base_info_01.frm
base_info_01#P#p_2018.ibd
base_info_01#P#p_2019.ibd
base_info_01#P#p_2020.ibd
base_info_01#P#p_2021.ibd
base_info_01#P#p_init.ibd
base_info_01#P#p_max.ibd
base_info.frm
base_info.ibd
3.4 问题
3.4.1 事务问题
既然分库分表了，那么肯定涉及到分布式事务，如何保证插入到不同库的多条记录能够要么同时成功，要么同时失败。有些同学可能想到XA，XA性能差而且不需要使用mysql5.7。柔性事务是目前主流的方案，TCC模式就属于柔性事务。
对于分布式事务问题每家公司有自己的实现，华为用saga，阿里用TXC，蚂蚁用DTX，支持FMT模式和TCC模式。
3.4.2 join问题
tddl、MyCAT等都支持跨分片join。但是尽力避免跨库join，比如通过字段冗余的方式等。
如果出现了这种情况且中间件支持分片join，那么可以这样使用。如果不支持可以手工查询。
4.总结
分表和在用途上不一样，分表是为了承接超大规模的表，单机放不下那种。分区的话则一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。性能稳定上的话都是一个个子表，差不多，区别应该是分区表是mysql内部实现的，会比分表方案少一点数据交互
扫一扫
201
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
