。

Mysql数据库单表数据量到达500万左右#xff0c;达…互联网行业企业都倾向于mysql数据库虽说mysql单表能支持亿级别的数据量加上索引优化下查询速度勉强能使用但是对于追求性能和效率的互联网企业这是远远不够的。

Mysql数据库单表数据量到达500万左右达到性能最佳点可是对于需要亿级别的业务来说500万是远远不够的。

既然数据放在一个位置不行那我们就把数据拆分放到多个位置。

如果寻找数据位置的时间成本忽略不计的话那么在亿级别的数据量里面查询数据的时间成本就相当于从单张表力查询数据的时间成本一样。

这就是分库分表的最初思想。

对表进行分区是为了能最大限度的提高数据库的IO能力分区能让数据库将同一张表中的数据放在不同的磁盘下提高数据库IO能力类似多核多线程的思想。

因此分区能提高单标的高并发能力。

range分区range方式创建分区语句如下#根据表结构中的时间字段来作为分区键如下的year()方法或者to_char()方法create

table

range(year(created_on))(partition

p2018

表示最大的可能的整数值)#或者使用id作为范围分区create

table

表示最大的可能的整数值)范围分区所有范围区间不能重叠。

查询条件里包括分区键免全表扫描分区表查询都应该注意这个。

分区键一般是时间或是唯一的索引值一般都会在每条数据上计算并保存分区字段。

实例假如我们现在有一张大表需要做分区过程应该如下建新表–备份–停机-》删原表–改名〉恢复CREATE

TABLE

InnoDB);②备份备份的方式有两类a、在线备份数据一直在数据库中不离线。

最好的方式是把备库拉出来做做分区。

再做主备切换insert

into

t_send_message_send);b、离线备份数据先导出到本地再从本地导回数据库。

这种需要先停机时间较长③删原表drop

table

t_send_message_send;注意删原表前一定要确认数据备份完成且完整。

后面再做增量同步保证数据不丢失。

rename

table

t_send_message_send_bak;④改名rename

table

t_send_message_send;附1、查询表分区情况select

partition_name

table_namet_send_message_send;12、查询表分区数据select

from

partition(p2020);mysql分区表对用户来说分区表是一个独立的逻辑表但是底层由多个物理子表组成。

实现分区的代码实际上是对一组底层表的句柄对象的封装。

mysql在创建表时使用PARTITIONBY子句定义每个分区存放的数据。

在执行查询的时候优化器会根据分区定义过滤那些没有我们需要数据的分区这样查询就无须扫描所有分区——只需要查询包含需要数据的分区就可以了。

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中这样做可以将相关的数据放在一起另外如果想一次批量删除整个分区的数据也会变得很方便。

amysql

的分表是真正的分表一张表分成很多表后每一个小表都是完正的一张表都对应三个文件

一个.MYD

表结构文件。

在下面的场景中分区可以起到非常大的作用1.表非常大以至于无法全部都放在内存中或者只在表的最后部分有热点数据其他均是历史数据。

2.分区表的数据更容易维护。

例如想批量删除大量数据可以使用清除整个分区的方式。

另外还可以对一个独立分区进行优化、检查、修复等操作。

3.分区表的数据可以分布在不同的物理设备上从而高效地利用多个硬件设备。

4.可以使用分区表来避免某些特殊的瓶颈例如InnoDB的单个索引的互斥访问ext3文件系统的inode锁竞争等。

5.如果需要还可以备份和恢复独立的分区这在非常大的数据集的场景下效果非常好。

分区表本身也有一些限制下面是其中比较重要的几点1.一个表最多只能有1024个分区。

2.在mysql5.1中分区表达式必须是整数或者是返回整数的表达式。

在mysql5.5中某些场景中可以直接使用列进行分区。

3.如果分区字段中有主键或者唯一索引的列那么所有主键列和唯一索引列都必须包含进来。

4.分区表中无法使用外键约束。

分区表上的操作按照下面的操作逻辑进行select查询当查询一个分区表的时候分区层先打开并锁住所有的底层表优化器先判断是否可以过滤部分分区然后再调用对应的存储引擎接口访问各个分区的数据。

insert操作当写入一条记录时分区层先打开并锁住所有的底层表然后确定哪个分区接收这条记录再将记录写入对应底层表。

delete操作当删除一条记录时分区层先打开并锁住所有的底层表然后确定数据对应的分区最后对相应底层表进行删除操作。

update操作当更新一条记录时分区层先打开并锁住所有的底层表mysql先确定需要更新的记录在哪个分区然后取出数据并更新再判断更新后的数据在哪个分区最后对底层进行写入操作并对原数据所在的底层表进行删除操作。

虽然每个操作都有“先打开并锁住所有的底层表”但这并不是说分区表在处理过程中是锁住全表的。

如果存储引擎能够自己实现行级锁例如innoDb则会在分区层释放对应表锁。

这个加锁和解锁过程与普通InnoDB上的查询类似。

mysql数据库中的数据是以文件的形势存在磁盘上的默认放在/mysql/data下面可以通过my.cnf中的datadir来查看一张表主要对应着三个文件一个是frm存放表结构的一个是myd存放表数据的一个是myi存表索引的。

如果一张表的数据量太大的话那么myd,myi就会变的很大查找数据就会变的很慢这个时候我们可以利用mysql的分区功能在物理上将这一张表对应的三个文件分割成许多个小块这样呢我们查找一条数据时就不用全部查找了只要知道这条数据在哪一块然后在那一块找就行了。

如果表的数据太大可能一个磁盘放不下这个时候我们可以把数据分配到不同的磁盘里面去reate

table

字段如果查询是基于分区表的话会显示查询将访问的分区。

查询到这条数据是从p1里面找到的。

找不到所以不是从哪个分区找到的。

查询条件不是分区建立的条件所以走所有分区。

我们增加分区条件id阿里不建议分区而是分表因此如果要使用分区表就不要创建太多分区。

我见过一个用户做了按天分区策略然后预先创建了10年的分区。

这种情况下访问分区表的性能自然是不好的。

这里有两个问题分区并不是越细越好

单表或单分区的数据一千万行只要没有特别大的索引对于现在的硬件能力来说都已是小表分区不要提前预留太多在使用之前预先创建即可

比如如果是按月分区每年年底时再把下一年度的12个新分区创建上即可。

对于没有数据的历史分区及时drop分区表的其他问题比如查询需要跨多个分区取数据查询性能就会比较慢基本上就不是分区表本身的问题而是数据量或说使用方式问题。

如果你的团队已经维护了成熟的分库分表中间件用业务分表对业务开发同学没有额外的复杂性对DBA也更直观自然更好。