mysql分区与分表

2022-10-19 17:48:36

当数据库表数据量多起来了一般100w以上了，这时候查询特别是连表查询的时候就会非常慢了，这时我们就要想着将数据量细分不用查那么多数据因为很多数据并不是我们所需要的，所以我们就要想着分，分是现在一个很重要的编程思想，从以前的集中式服务到现在的分布式服务，微服务等，都散发着分和细化的思想。编程思想很重要，大家一定要多去了解了解

mysql现在基本上都是InnoDB引擎了下面所讲的默认是innodb引擎

分区

这里讲的分区，此“区”非彼“区”，这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件，不是我们刚刚说的区。MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。要知道MySQL是面向OLTP的数据，它不像TIDB等其他DB。那么对于分区的使用应该非常小心，如果不清楚如何使用分区可能会对性能产生负面的影响。

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

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

创建分区表

1、range分区

行数据基于一个给定的连续区间的列值放入分区。

CREATE TABLE `test_11` (
`id` int(11) NOT NULL,
`t` date NOT NULL,
PRIMARY KEY (`id`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(t))
(PARTITION p20170801 VALUES LESS THAN (736907) ENGINE = InnoDB,
PARTITION p20170901 VALUES LESS THAN (736938) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB);123456789

然后插入4条数据：

insert into test_11 values (1,"20170722"),(2,"20170822"),(3,"20170823"),(4,"20170824");1

然后查看information下partitions对分区别信息的统计：

select PARTITION_NAME as "分区",TABLE_ROWS as "行数" from information_schema.partitions where table_schema="mysql_test" and table_name="test_11";
+-----------+--------+
| 分区      | 行数   |
+-----------+--------+
| p20170801 |      1 |
| p20170901 |      3 |
+-----------+--------+
2 rows in set (0.00 sec)12345678

可以看出分区p20170801插入1行数据，p20170901插入的3行数据。
可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换，然后分区。

2、list分区

和range分区一样，只是list分区面向的是离散的值

mysql> CREATE TABLE h2 (
    ->   c1 INT,
    ->   c2 INT
    -> )
    -> PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.11 sec)123456789

与RANGE分区的情况不同，没有“catch-all”，如MAXVALUE; 分区表达式的所有预期值应在PARTITION … VALUES IN（…）子句中涵盖。包含不匹配的分区列值的INSERT语句失败并显示错误，如此示例所示：

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 312

3、hash分区

根据用户自定义表达式的返回值来进行分区，返回值不能为负数

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;123

如果你插入col3的数值为’2005-09-15’，那么根据以下计算来选择插入的分区：

MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1123

4、key分区

根据MySQL数据库提供的散列函数进行分区

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;1234567

KEY仅列出零个或多个列名称。用作分区键的任何列必须包含表的主键的一部分或全部，如果该表具有一个。如果没有列名称作为分区键，则使用表的主键（如果有）。如果没有主键，但是有一个唯一的键，那么唯一键用于分区键。但是，如果唯一键列未定义为NOT NULL，则上一条语句将失败。

与其他分区类型不同，KEY使用的分区不限于整数或空值。例如，以下CREATE TABLE语句是有效的：

CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;12345

注意：对于key分区表，不能执行ALTER TABLE DROP PRIMARY KEY，因为这样做会生成错误 ERROR 1466 (HY000): Field in list of fields for partition function not found in table.

5、Column分区

COLUMN分区是5.5开始引入的分区功能，只有RANGE COLUMN和LIST COLUMN这两种分区；支持整形、日期、字符串；RANGE和LIST的分区方式非常的相似。

COLUMNS和RANGE和LIST分区的区别
1）针对日期字段的分区就不需要再使用函数进行转换了，例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。
2）COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。

column支持的数据类型：
1）所有的整型，float和decimal不支持
2）日期类型：date和datetime，其他不支持
3）字符类型：CHAR, VARCHAR, BINARY和VARBINARY，blob和text不支持
单列的column range分区mysql> show create table list_c;
CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
多列的column range分区mysql> show create table list_c;
CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1,c3)
(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */
单列的column list分区mysql> show create table list_c;
CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST COLUMNS(c3)
(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */

6、子分区（组合分区）

在分区的基础上再进一步分区，有时成为复合分区;

MySQL数据库允许在range和list的分区上进行HASH和KEY的子分区。例如：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );
[root@mycat-3 ~]# ll /data/mysql_data_3306/mysql_test/ts*
-rw-r----- 1 mysql mysql 8596 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts.frm
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p0#SP#p0sp0.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p0#SP#p0sp1.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p1#SP#p1sp0.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p1#SP#p1sp1.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p2#SP#p2sp0.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p2#SP#p2sp1.ibd
1234567891011121314151617

ts表根据purchased进行range分区，然后又进行了一次hash分区，最后形成了3*2个分区，可以从物理文件证实此分区方式。可以通过subpartition语法来显示指定子分区名称。

注意：每个子分区的数量必须相同；如果一个分区表的任何子分区已经使用subpartition，那么必须表明所有的子分区名称；每个subpartition子句必须包括子分区的一个名字；子分区的名字必须是一致的

另外，对于MyISAM表可以使用index directory和data direactory来指定各个分区的数据和索引目录，但是对于innodb表来说，因为该存储引擎使用表空间自动的进行数据和索引的管理，因此会忽略指定index和data的语法。

普通表转换为分区表

1、用alter table table_name partition by命令重建分区表（sh必须为主键）

alter table jxfp_data_bak PARTITION BY KEY(SH) PARTITIONS 8;

分区还有很多细化操作，这里只讲比较常用的，总结来说分区相当于把表拆分为几个区块，但是表的结构数量都没变，只是表的底层区变多了，操作更细化了所以不需要改逻辑代码

分表

同样的，也是因为数据表数据太多了，而且每天的新数据还在不停的增加，这样容易造成数据库要大，性能慢，那分表是啥意思？就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户id来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在200万以内。一般可以由运维通过中间件cobar、TDDL、atlas、sharding-jdbc、mycat来部署

一般代码里可以约定好库表名称之类的按照id 或者时间来操作数据表比如一个月一个表或者1-10000 id为一个表之类的

码农公寓

分区