分区

InnoDB逻辑存储结构

所有数据都被逻辑地存放在表空间,表空间又由段,区,页组成。

常见的段有数据段、索引段、回滚段等,在InnoDB存储引擎中,对段的管理都是由引擎自身所完成的。

区是由连续的页组成的空间,无论页的大小怎么变,区的大小默认总是为1MB。为了保证区中的页的连续性,InnoDB存储引擎一次从磁盘申请4-5个区,InnoDB页的大小默认为16kb,即一个区一共有64(1MB/16kb=16)个连续的页。每个段开始,先用32页(page)大小的碎片页来存放数据,在使用完这些页之后才是64个连续页的申请。这样做的目的是,对于一些小表或者是undo类的段,可以开始申请较小的空间,节约磁盘开销。

也可以叫块。页是InnoDB磁盘管理的最小单位。默认大小为16KB,可以通过参数innodb_page_size来设置。常见的页类型有:数据页,undo页,系统页,事务数据页,插入缓冲位图页,插入缓冲空闲列表页,未压缩的二进制大对象页,压缩的二进制大对象页等。  

分区概述

这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中,几个分区就有几个.idb文件,不是我们刚刚说的区。MySQL在5.1时添加了对水平分区的支持。

分区是将一个表或索引分解成多个更小,更可管理的部分。每个区都是独立的,可以独立处理,也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能,业务代码无需改动。要知道MySQL是面向OLTP的数据,它不像TIDB等其他DB。那么对于分区的使用应该非常小心,如果不清楚如何使用分区可能会对性能产生负面的影响。

MySQL数据库的分区是局部分区索引,一个分区中既存了数据,又放了索引。也就是说,每个区的聚集索引和非聚集索引都放在各自区的(不同的物理文件)。目前MySQL数据库还不支持全局分区。

无论哪种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分。

这个是数据库分的,应用透明,代码无需修改任何东西。

分区类型

MySQL支持一下几种类型的分区,RANGE分区,LIST分区,HASH分区,KEY分区。如果表存在主键或者唯一索引时,分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。

通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区,不会搜索所有分区

如果sql语句有问题,那么会走所有区。会很危险。所以分区表后,select语句必须走分区键。

range 分区

RANGE分区是实战最常用的一种分区类型,行数据基于属于一个给定的连续区间的列值被放入分区。但是记住,当插入的数据不在一个分区中定义的值的时候,会抛异常。RANGE分区主要用于日期列的分区,比如交易表啊,销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据,那么注意了,优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型,那么只用存yyyyMM就好了。也不用关心函数了。

LIST分区

LIST分区和RANGE分区很相似,只是分区列的值是离散的,不是连续的。LIST分区使用VALUES IN,因为每个分区的值是离散的,因此只能定义值。

HASH分区

说到哈希,那么目的很明显了,将数据均匀的分布到预先定义的各个分区中,保证每个分区的数量大致相同。

KEY分区

KEY分区和HASH分区相似,不同之处在于HASH分区使用用户定义的函数进行分区,KEY分区使用数据库提供的函数进行分区。

分区和性能

不是启动了分区数据库就会运行的更快,分区可能会给某些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性能。如果一张表的话,那就只有一个.ibd文件,一颗大的B+树。如果分表后,将按分区规则,分成不同的区,也就是一个大的B+树,分成多个小的树。

读的效率肯定提升了,如果走分区键索引的话,先走对应分区的辅助索引B+树,再走对应分区的聚集索引B+树。

如果没有走分区键,将会在所有分区都会执行一次。会造成多次逻辑IO!平时开发如果想查看sql语句的分区查询可以使用explain partitons select xxxxx语句。可以看到一句select语句走了几个分区。

分库分表

当一张表随着时间和业务的发展,库里表的数据量会越来越大。数据操作也随之会越来越大。一台物理机的资源有限,最终能承载的数据量、数据的处理能力都会受到限制。这时候就会使用分库分表来承接超大规模的表,单机放不下的那种。

区别于分区的是,分区一般都是放在单机里的,用的比较多的是时间范围分区,方便归档。只不过分库分表需要代码实现,分区则是mysql内部实现。分库分表和分区并不冲突,可以结合使用。

实现

分库分表标准
  • 存储占用100G+
  • 数据增量每天200w+
  • 单表条数1亿条+
分库分表字段

分库分表字段取值非常重要
1.在大多数场景该字段是查询字段
2.数值型

分布式数据库中间件

分布式数据库中间件分为两种,proxy和客户端式架构。proxy模式有MyCat、DBProxy等,客户端式架构有TDDL、Sharding-JDBC等。

proxy模式的话我们的select和update语句都是发送给代理,由这个代理来操作具体的底层数据库。所以必须要求代理本身需要保证高可用,否则数据库没有宕机,proxy挂了,那就走远了。

客户端模式通常在连接池上做了一层封装,内部与不同的库连接,sql交给smart-client进行处理。通常仅支持一种语言,如果其他语言要使用,需要开发多语言客户端。

相关问题

事务问题

既然分库分表了,那么肯定涉及到分布式事务,如何保证插入到不同库的多条记录能够要么同时成功,要么同时失败。有些同学可能想到XA,XA性能差而且不需要使用mysql5.7。柔性事务是目前主流的方案,TCC模式就属于柔性事务。

MYSQL的数据库存储引擎InnoDB的事务特性能够保证在存储引擎级别实现ACID,而分布式事务让存储引擎级别的事务扩展到数据库层面,甚至扩展到多个数据库之间,这是通过两阶段提交协议来实现的,MySQL 5.0或者更新版本开始支持XA事务,从下图可知MySQL中只有InnoDB引擎支持XA协议:

XA

可知XA规范中分布式事务有AP,RM,TM组成:

其中应用程序(Application Program ,简称AP):AP定义事务边界(定义事务开始和结束)并访问事务边界内的资源。
资源管理器(Resource Manager,简称RM):Rm管理计算机共享的资源,许多软件都可以去访问这些资源,资源包含比如数据库、文件系统、打印机服务器等。

事务管理器(Transaction Manager ,简称TM):负责管理全局事务,分配事务唯一标识,监控事务的执行进度,并负责事务的提交、回滚、失败恢复等。

Xa主要规定了RM与TM之间的交互,下面来看下XA规范中定义的RM 和 TM交互的接口:

join问题

最后

分表和在用途上不一样,分表是为了承接超大规模的表,单机放不下那种。

分区的话则一般都是放在单机里的,用的比较多的是时间范围分区,方便归档。

性能稳定上的话都是一个个子表,差不多,区别应该是分区表是mysql内部实现的,会比分表方案少一点数据交互