mysql数据库优化（三）--分区

mysql的分区，分表

分区：把一个数据表的文件和索引分散存储在不同的物理文件中。特点：业务层透明，无需任何修改，即使从新分表，也是在mysql层进行更改（业务层代码不动）

分表：把原来的表根据条件分成多个表，如原来的表为 user;现在分成2个小表 user_1,user_2; 特点：业务层需要修改代码。如过业务改变，可能需要从新分表，导致维护困难

当数据量达到一定级别后，需要通过分区或分表来提高用户体验

如下知识点为分区

如：现在生产环境有用户表 account_user，对其按照日期（每季度）进行分区。

表结构如下：

输入命令：show create table account_user;

由于此表有主键和unique键，在分区时，必须要求被用来匹配分区的字段被包含在主键，和unique键中（也就是复合主键和复合unique键）；

通过如下命令进行操作把 create_time分别放在主键和unique键中（这时mobile字段不能保证唯一性，这是个大问题，需要解决）

添加unique键： ALTER TABLE account_user ADD UNIQUE KEY (mobile,create_time);

删除unique键： ALTER TABLE account_user DROP UNIQUE KEY ;

添加主键：ALTER TABLE account_user ADD PRIMARY KEY (id,create_time);

删除主键：ALTER TABLE account_user DROP PRIMARY KEY;

然后根据官网教程：

如下根据range分区进行添加：

ALTER TABLE account_user PARTITION BY RANGE (TO_DAYS(create_time))
(
PARTITION account_user_2018_01 VALUES less than (TO_DAYS('2018-01-01')),
PARTITION account_user_2018_04 VALUES less than (TO_DAYS('2018-04-01')),
PARTITION account_user_2018_07 VALUES less than (TO_DAYS('2018-07-01')),
PARTITION account_user_2018_10 VALUES less than (TO_DAYS('2018-10-01')),
PARTITION account_user_2018_more VALUES less than MAXVALUE
)

然后查看结果：

验证分区效果：

优点：根据create_time进行范围查询，会使用分区，避免全表扫描

使用分区的情况下：

只是查询了 3351行，或者说是查询了（account_user_2018_01,account_user_2018_04,account_user_2018_07）三个分区

在没有分区的情况下：

发现进行全表扫描，行数为46808行

相关操作：

查看行数据所在分区：SELECT * FROM account_user PARTITION (account_user_2018_07) WHERE id=1;

增加分区： ALTER TABLE account_user ADD PARTITION (PARTITION account_user_2019_01 VALUES LESS THAN (TO_DAYS('2019-01-01'))); 如果对应range分区有 MAXVALUE ，要先删除，否则报错

删除分区： ALTER TABLE account_user DROP PARTITION account_user_2019_01;

删除分区数据：ALTER TABLE account_user TRUNCATE PARTITION account_user_2019_01，account_user_2019_04;

rebuild重建分区：ALTER TABLE account_user REBUILD PARTITION account_user_2019_01; #相当于drop所有记录，然后再reinsert；可以解决磁盘碎片

优化分区：ALTER TABLE account_user OPTIMIZE PARTITION account_user_2019_01; #在删除数据后回收空间和碎片整理

analzye分区：ALTER TABLE account_user ANALZYE PARTITION account_user_2019_01;

check分区：ALTER TABLE account_user CHECK PARTITION account_user_2019_01;

所有分区方式：

list: 每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值. 将要匹配的任何值都必须在值列表中找到。

如：

ALTER TABLE account_user PARTITION BY LIST (TO_DAYS(create_time))
(
PARTITION account_user_2018_01 VALUES IN (TO_DAYS('2018-01-01'),TO_DAYS('2018-01-02')),
PARTITION account_user_2018_04 VALUES IN (TO_DAYS('2018-01-03'),TO_DAYS('2018-01-04'))
)

range:每个分区包含那些分区表达式的值位于一个给定的连续区间内的行

如：

ALTER TABLE account_user PARTITION BY RANGE (TO_DAYS(create_time))
(
PARTITION account_user_2018_01 VALUES less than (TO_DAYS('2018-01-01')),
PARTITION account_user_2018_04 VALUES less than (TO_DAYS('2018-04-01')),
PARTITION account_user_2018_07 VALUES less than (TO_DAYS('2018-07-01')),
PARTITION account_user_2018_10 VALUES less than (TO_DAYS('2018-10-01')),
PARTITION account_user_2018_more VALUES less than MAXVALUE
)

在使用范围查询 create_time 时，会使用分区进行查询（时间复杂度：O(log N)），所有速度比没有使用分区(时间复杂度：O(N))的快。

hash:无需定义分区的条件，数据会平均分配到每个分区。只需要指明分区数即可。

如：

ALTER TABLE account_user PARTITION BY HASH(TO_DAYS(create_time))
PARTITIONS 5

LINEAR HASH分区:在数据量大的场景，譬如TB级，增加、删除、合并和拆分分区会更快,缺点是，相对于HASH分区，它数据分布不均匀的概率更大。

ALTER TABLE account_user PARTITION BY LINEAR HASH(TO_DAYS(create_time))
PARTITIONS 5

key分区:

1. KEY分区允许多列，而HASH分区只允许一列。

2. 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

3. KEY分区对象必须为列，而不能是基于列的表达式。

4. KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

分区优点：

分区可以分在多个磁盘，存储更大一点

根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

进行大数据搜索时可以进行并行处理。

跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

分区缺点：

其分区对应的key必须包含主键或者unique键，导致 unique 键的字段失效（如用户注册表，手机号唯一性）

需要仔细考虑评估业务系统对表进行操作的侧重点，然后选择字段和分区方式进行分区，尽量平均分配数据到每个分区。分区后进行相关验证性测试是否有效果

案例：

1.公司通过推荐注册可以提现红包的方式拉取用户，造成其他人利用接口恶意注册僵尸用户，导致用户表数据量过多，影响正常用户的使用。需求：活跃用户只有总用户的5%，如何提高活跃用户的体验？

方案：

1.在用户表中增加一个代表活跃度的字段，在用户每次活跃后，其值相应增加。通过分区的方式（通过活跃度字段进行range分区）,提高访问速度。

优点：无需系统层改变代码，活跃度改变后，会自动分区

2.在用户表中增加一个代表活跃度的字段，在用户每次活跃后，其值相应增加。通过分表的方式（根据活跃度），

　　　缺点：需要系统层（应用程序）改变代码。

在用户活跃度变化后，需要手动的从一个表变到另一个表，导致需要定期维护，较为复杂