MySql之分区分表

分表的概念

分表：将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。

常用的算法：hash或求余（取模）等方式

分表的好处：减小数据库的负担，缩短查询时间

分表的类型：①垂直切分：是指数据表列的拆分，把一张列比较多的表拆分为多张表

            ②水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放

 

分表的方式：①mysql集群

            ②预先估计会出现大数据量并且访问频繁的表，将其分为若干个表

            ③利用merge存储引擎来实现分表

分表的案例：

1.创建库、表、数据

wKiom1lOShfAxaXDAAEMrqy1hZs585.png

使用：select  * from member；  查询表

2.将member分为两个表tb_member1,tb_member2。

tb_member1

wKioL1lOShiTcwx_AABORvNaY8s674.png

tb_member2

wKioL1lOShjSv091AABNT-Y1IFg669.png

创建主表

wKiom1lOShmCvzkOAACHpvNTFmk645.png

使用降序查看b_member表的结构

wKiom1lOShnQIZRjAABb5WH5l00982.png

3.将数据分到两个表中

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;

wKioL1lOShripZKKAABYdY5ynZs728.png

mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;

wKioL1lOShryNGgZAAA5c659Vbc091.png

查看主表：mysql> select * from tb_member;  此时 总表只是一个外壳，存取数

据发生在一个一个的子表里面。

mysql> select * from tb_member where id=3; 查询基于id的条件语句更加快捷

4.查看文件表：每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件

wKiom1lOShuRKNmDAAEQ3Ct0Jac076.png

分区的概念

分区：分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置。app读写的时候操作的还是表名字，db自动去组织分区的数据。

分区的主要形式：①水平分区（Horizontal Partitioning）：这种形式分区是对表的行进行分区，所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。

               ②垂直分区（Vertical Partitioning）这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。

分区技术支持：①在5.6之前，使用这个参数查看当将配置是否支持分区

                mysql> SHOW VARIABLES LIKE '%partition%'; 

              ②在5.6及以采用后，则采用如下方式进行查

                mysql> show plugins;

wKioL1lOShvCBoUqAACQ8eyT3dk043.png

分区案例

1.创建库、表、范围分区表

wKiom1lOShySxHT1AADcHqhK6a8757.png

插入15条数据

wKioL1lOShyipV7QAAA7Ak_sXCk562.png

查看存放数据库表文件

wKiom1lOSh2C_mUXAABfuI6iIKY090.png

计算基于id值的有多少行

wKiom1lOSh2gxWGBAAAq44hgpFU114.png

从information_schema系统库中的partitions表中查看分区信息

wKioL1lOSh6xd-wzAAEmWJULYXs159.png

从某个分区中查询数据

wKioL1lOSh6iTySeAABc-6tuabs766.png

当删除了一个分区，也同时删除了该分区中所有的数据。

wKiom1lOSh_C77VKAABUZ6XolAQ781.png

新增分区

wKioL1lOSh_Tz99oAABhfP_ERDM312.png

分区合并

wKiom1lOSiDgY-wYAACfAQU5Lv4693.png

再次查看存放数据库表文件

wKioL1lOSiCQlkIEAAC_5B531-U431.png

查看合并后的分区

wKiom1lOSiHT4qmHAABir1R1tyI463.png

案例：未分区表和分区表性能测试

1.创建数据库和一个未分区的表

wKioL1lOSiHTJYESAAB8JsYWebk328.png

2.创建分区表,按日期的年份拆分

wKiom1lOSiKD4gXlAADqqKBXhWA396.png

3.插入10万条测试语句

wKioL1lOSiKS6Jq0AACWLIDLOq0942.png

4.在表中插入数据

wKiom1lOSiOD3bQNAAA64M5jeeo472.png

查询表明分区表比未分区表的执行时间少很多。

wKiom1lOSiPgKKWmAAA_SEjhVvc218.png

在通过explain语句来分析执行情况

explain语句显示了SQL查询要处理的记录数目可以看出分区表比未分区表的明显扫描的记录要少很多。

wKioL1lOSiTyaAFSAADGcM3D9ms328.png

创建索引后与不不创建索引差异不大 1不创建索引重启mysql服务在次查询即可

wKiom1lOSiXQ5EnTAAC4N9w5Nlo076.png

分区表的类型

1.RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。

案例：

wKioL1lOSiayLbceAAFAKKNxTUA211.png

创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在的分区中。

wKioL1lOSibDnqlSAABOdXGEjQM107.png

2.LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

案例：

wKiom1lOSieT03V9AAEowXwuxHs143.png

查询语句

wKioL1lOSieQ8Kn-AABcsOMztfc518.png

3.HASH分区 ：这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。

hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MYSQL自动完成这些工作，用户所要定一个列值或者表达式，以及指定被分区的表将要被分割成的分区数量。

wKiom1lOSijTiZ4gAAA453C6k9g301.png

hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数，不加的partitions子句的话，默认为分区数为1。

wKiom1lOSkvhO-FdAAA-ic5aEdU084.png

该记录会被放入分区p2中。因为插入2010-04-01进入表t_hash,那么

MOD(YEAR('2010-04-01'),4)=2

wKioL1lOSkyBDPdaAAAymr1HQVg165.png

wKioL1lOSkyAq6i9AAGzDmMqzrA582.png

4.key分区：key分区和hash分区相似，不同在于hash分区是用户自定义函数进行分区，key分区使用mysql数据库提供的函数进行分区，NDB cluster使用MD5函数来分区，对于其他存储引擎mysql使用内部的hash函数。

wKiom1lOSk2BX3bTAAA0X06lQyg309.png

上面的RANGE、LIST、HASH、KEY四种分区中，分区的条件必须是×××，如果不是×××需要通过函数将其转换为×××。

 

5.columns分区：mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非×××数据进行分区。COLUMNS分区支持以下数据类型：
　　所有×××，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
　　日期类型，如DATE和DATETIME。其余日期类型不支持。
　　字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
　　COLUMNS可以使用多个列进行分区。