mysql的分区表学习

标签: mysql

以下内容来自 <高性能mysql> 以及 https://www.cnblogs.com/wy123/p/9778590.html


 

1:使用场景

1.1:数据非常大,以至于无法全部放在内存中

1.2:只有部分是热点数据,其他的都是历史数据



2:分区的限制

2.1:一个表最多只能有1024个分区

2.2:在5.1.中分区表达式只能为整数或者是返回整数的表达式,。5.5中可以直接使用列进行分区

2.3:分区字段中有主键或者唯一索引的列,那么所有的主键和唯一索引的列必须都包含进来

2.4:分区表不能使用外键约束

 

3:分区表的原理

InnoDB会在分区层释放对应的表锁。

select查询

    1:分区层打开并锁住所有的底层表

    2:优化器先判断是否可以过滤部分分区

    3:调用对应的存储引擎接口访问各个分区的数据

insert:

    1:分区层打开并锁住所有的底层表

    2:确定那个分区接收该条记录

    3:将该条记录写进对应的底层表

delete:

    1:分区层打开并锁住所有的底层表

    2:确定数据对应的分区

    3:删除该条数据

update:

    1:分区层打开并锁住所有的底层表

    2:确定需要更新的记录在哪个分区

    3:取出数据并更新

    4:判断更新后的数据应该放在哪个分区,对底层表进行写入

    5:对原数据所在的底层表进行删除操作



例子:

创建表并且创建12个分区

CREATE TABLE test_range_partition(
    id INT auto_increment,
    createdate DATETIME,
    primary key (id,createdate)
) 
PARTITION BY RANGE (TO_DAYS(createdate) ) (
   PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
   PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
   PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
   PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
   PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
   PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
   PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
   PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
   PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
   PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
   PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
   PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
);



insert into test_range_partition (createdate) values ('20180105');
insert into test_range_partition (createdate) values ('20180205');
insert into test_range_partition (createdate) values ('20180206');
insert into test_range_partition (createdate) values ('20180305');
insert into test_range_partition (createdate) values ('20180405');
insert into test_range_partition (createdate) values ('20180505');
insert into test_range_partition (createdate) values ('20180605');
insert into test_range_partition (createdate) values ('20180705');
insert into test_range_partition (createdate) values ('20180805');
insert into test_range_partition (createdate) values ('20180905');
insert into test_range_partition (createdate) values ('20181005');
insert into test_range_partition (createdate) values ('20181105');

查看分区的数据 : 【table_schema数据库的名称 】

select 
    table_schema,
    table_name,
    partition_name,
    partition_ordinal_position,
    partition_method,
    partition_expression,
    table_rows
from information_schema.`PARTITIONS` where table_schema = 'mysql_test' and table_name = 'test_range_partition';

 

对应的物理文件也是分了12个区来存储这些数据

ibd是MySQL数据文件、索引文件

frm是MySQL表结构定义文件

查询的时候where条件里面一定要带上分区列当做条件,这样可以让优化器过滤掉无须访问的分区,如果没有分区列的条件,mysql就会让存储引擎访问该表的所有分区。如果表非常的的时候查询速度会很慢

版权声明:本文为weixin_42557402原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_42557402/article/details/103794632