mysql使用 分区表使用，常用sql

时间：2023-03-08 23:40:20
mysql使用 分区表使用，常用sql

前言

本文的原文连接是: https://blog.****.net/freewebsys/article/details/84839478
未经博主允许不得转载。
博主地址是：http://blog.****.net/freewebsys

1，关于分区

也就是说，对于原表分区后，对于应用层来说可以不做变化，我们无需改变原有的SQL语句，相当于MySQL帮我们实现了传统分表后的SQL中间件，当然，MySQL的分区表的实现要复杂很多。

另外，在创建分区时可以指定分区的索引文件和数据文件的存储位置，所以可以把数据表的数据分布在不同的物理设备上，从而高效地利用多个硬件设备。

一些限制：

1.在5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。

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

3.主表的所有唯一索引列（包括主键）都必须包含分区字段。MySQL官方文档中写的是：

All columns used in the partitioning expression for a partitioned
table must be part of every unique key that the table may have.

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html

https://dev.mysql.com/doc/refman/8.0/en/partitioning-management-exchange.html

别人的测试文章：
https://www.cnblogs.com/acpp/archive/2010/08/09/1795464.html

2，使用

mysql使用 分区表使用，常用sql

一般情况按季度进行分区，要小于季度最后一天。
所以小于下个月的第一天。

这个时间要是没有写对，会报错误：

value must be strictly increasing for each partition

CREATE TABLE `user_part` (

  `id` BIGINT(20)  NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(200) COMMENT '名称',

  `create_date` date NOT NULL COMMENT '日期',

  PRIMARY KEY (`id`,`create_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE(TO_DAYS(`create_date`))

(

    PARTITION p2018q4 VALUES LESS THAN (TO_DAYS('2019-01-01')),

    PARTITION p2019q1 VALUES LESS THAN (TO_DAYS('2019-04-01')),

    PARTITION p2019q2 VALUES LESS THAN (TO_DAYS('2019-07-01')),

    PARTITION p2019q3 VALUES LESS THAN (TO_DAYS('2019-10-01')),

    PARTITION p2019q4 VALUES LESS THAN (TO_DAYS('2020-01-01')),

    PARTITION p2020q1 VALUES LESS THAN (TO_DAYS('2020-04-01')),

    PARTITION p2020q2 VALUES LESS THAN (TO_DAYS('2020-07-01')),

    PARTITION p2020q3 VALUES LESS THAN (TO_DAYS('2020-10-01')),

    PARTITION p2020q4 VALUES LESS THAN (TO_DAYS('2021-01-01')),

    PARTITION p2021q1 VALUES LESS THAN (TO_DAYS('2021-04-01')),

    PARTITION p2021q2 VALUES LESS THAN (TO_DAYS('2021-07-01')),

    PARTITION p2021q3 VALUES LESS THAN (TO_DAYS('2021-10-01')),

    PARTITION p2021q4 VALUES LESS THAN (TO_DAYS('2022-01-01')),

    PARTITION p2022q1 VALUES LESS THAN (TO_DAYS('2022-04-01')),

    PARTITION p2022q2 VALUES LESS THAN (TO_DAYS('2022-07-01')),

    PARTITION p2022q3 VALUES LESS THAN (TO_DAYS('2022-10-01')),

    PARTITION p2022q4 VALUES LESS THAN (TO_DAYS('2023-01-01')),

    PARTITION p2023q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),

    PARTITION p2023q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),

    PARTITION p2023q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),

    PARTITION p2023q4 VALUES LESS THAN (TO_DAYS('2024-01-01'))

);

INSERT INTO user_part VALUES

    (1, 'desk organiser', '2018-10-15'),

    (2, 'CD player', '2019-01-05'),

    (3, 'TV set', '2019-03-10'),

    (4, 'bookcase', '2019-04-10'),

    (5, 'exercise bike', '2019-05-09'),

    (6, 'sofa', '2019-06-05'),

    (7, 'popcorn maker', '2018-01-22'),

    (8, 'aquarium', '2023-08-04'),

    (9, 'study desk', '2022-09-16'),

    (10, 'lava lamp', '2021-12-25');
explain partitions select * from user_part where create_date = '2022-09-16';

 SELECT DISTINCT PARTITION_EXPRESSION

       FROM INFORMATION_SCHEMA.PARTITIONS

       WHERE TABLE_NAME='user_part';

SELECT PARTITION_NAME, TABLE_ROWS

           FROM INFORMATION_SCHEMA.PARTITIONS

           WHERE TABLE_NAME =  'user_part' ;

通过explain语句来分析执行情况，比较下性能

分区表执行扫描了2行，数据量大的话执行时间也少很多。

mysql使用 分区表使用，常用sql

而普通表则扫描了10行

mysql使用 分区表使用，常用sql

mysql使用 分区表使用，常用sql

mysql使用 分区表使用，常用sql

3，总结

mysql 分区还是非常不错的数据方法。
当然以后可以使用tidb 存储数据，处理数据。

本文的原文连接是:
https://blog.****.net/freewebsys/article/details/84839478

博主地址是：http://blog.****.net/freewebsys

mysql 分区所使用的sql

查询分区内全部数据
select * from url_8 partition(p0);//5.5.41不支持对指定分区的查询

(今天写一个数据迁移脚本，需要对表按分区查询，在执行select * from xxx partition (p_xxx)的时候报错。

后来发现是使用的版本5.5.41不支持对指定分区的查询。在5.6增强了分区表的分区的相关操作。其中包括支持了对指定分区的查询。

具体增强的其他特性，可以查看官方文档)

对以存在的表进行分区
ALTER TABLE url_8 PARTITION by HASH(YEAR(`day`)) PARTITIONS 12;

删除表的分区
ALTER TABLE url_8 REMOVE PARTITIONING;

删除表单个分区内的数据

ALTER TABLE url TRUNCATE PARTITION p22;

把分区内的数据复制到另一张表

INSERT INTO url_8 SELECT * FROM url PARTITION(p22);

https://www.cnblogs.com/pejsidney/p/10074980.html

一.分区

　　本文一切基于MySql InnoDB

　　说了这么多，接下来说主体，先说分区，因为之前博主写过一篇MySql分区的博客所以这里不会多费笔墨来写，具体见：https://www.cnblogs.com/GrimMjx/p/10526821.html

2.1 实现方式

　　具体如何实现上面链接里有写，这里只需记住如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

　　这个是数据库分的，应用透明，代码无需修改任何东西。

2.2 内部文件

　　先去data目录，如果不知道目录位置的可以执行：

mysql使用 分区表使用，常用sql

　　接下来看下内部文件：

mysql使用 分区表使用，常用sql

　　从上图我们可以看出，有2中类型的文件，.frm文件和.ibd文件

  • .frm文件：表结构文件
  • .ibd文件：InnoDB中，索引和数据都在同个文件.ibdata（你的执行结果可能是.MYD索引文件和.MYI数据文件，没关系，这是MyIsAm存储引擎，对应着InnoDB的.ibd文件）。因为Order这张表分为5个区，所以有5个这样的文件
  • .par文件：你执行的结果可能有.par文件也可能没有。注意：从MySql 5.7.6开始，不再创建.par分区定义文件。分区定义存储在内部数据字典中。

2.3 数据处理

　　分区表后，提高了MySql性能。如果一张表的话，那就只有一个.ibd文件，一颗大的B+树。如果分表后，将按分区规则，分成不同的区，也就是一个大的B+树，分成多个小的树。

　　（PS：如果想研究一颗聚集索引B+树可以放多少行数据，请看：https://www.cnblogs.com/GrimMjx/p/10540263.html）

　　读的效率肯定提升了，如果走分区键索引的话，先走对应分区的辅助索引B+树，再走对应分区的聚集索引B+树。

　　如果没有走分区键，将会在所有分区都会执行一次。会造成多次逻辑IO！平时开发如果想查看sql语句的分区查询可以使用explain partitons select xxxxx语句。可以看到一句select语句走了几个分区。

相关文章