实现MYSQL电话号码表RANGE类型分区

 什么是分区

        在MySQL中，分区是一种将表中的数据按照特定规则划分到不同物理部分的技术。尽管数据被分散存储，但在逻辑层面上，表仍然保持整体性，对用户来说这种分割是透明的。分区通过提升数据管理的效率和查询性能，特别适合于处理大型数据集。

分区有什么用

1. 提高查询性能：通过仅扫描相关分区减少查询所需的数据量，特别适用于范围查询。
2. 简化数据管理：分区使得对大数据集的维护（如备份、恢复、优化）更加便捷，可以对独立的分区执行操作。
3. 增强数据可用性：分区有助于局部化数据和故障，减少由于硬件故障影响的数据量，使得恢复更快速。

MySQL支持的分区类型：

• RANGE分区：基于列的值落在给定的一系列值中的一个来分区。这对于按日期存储数据非常有用。
• LIST分区：和RANGE类似，但是是基于列的值匹配列出的离散值之一来进行分区。
• HASH分区：通过对一个或多个列的值应用散列函数来定义分区。这可以用来确保数据均匀分布在所有分区中。
• KEY分区：和HASH类似，但是散列函数的应用基于MySQL服务器内部算法，且一般基于表的主键。
• LINEAR HASH和LINEAR KEY分区：是HASH和KEY分区的变体，通过线性散列提供更均匀的数据分布

如何实现分区

创建号码表

CREATE TABLE `tel_number` (
`phone_number` BIGINT ( 11 ) NOT NULL COMMENT '电话号码',
`create_time` TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP ( 0 ) COMMENT '创建时间',
`remark` VARCHAR ( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '备注',
INDEX `phone_number` ( `phone_number` ) USING BTREE, 
INDEX `create_time` ( `create_time` ) USING BTREE ) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci COMMENT = '电话表' ROW_FORMAT = Dynamic;

创建表分区

        直接上代码，以下是根据手机号码段来分区，分区需要后续不断维护。

ALTER TABLE `testdb`.`tel_number` PARTITION BY RANGE (phone_number) (
PARTITION p0 VALUES LESS THAN (13100000000),
PARTITION p1 VALUES LESS THAN (13200000000),
PARTITION p2 VALUES LESS THAN (13300000000),
PARTITION p3 VALUES LESS THAN (13400000000),
PARTITION p4 VALUES LESS THAN (13500000000),
PARTITION p5 VALUES LESS THAN (13600000000),
PARTITION p6 VALUES LESS THAN (13700000000),
PARTITION p7 VALUES LESS THAN (13800000000),
PARTITION p8 VALUES LESS THAN (13900000000),
PARTITION p9 VALUES LESS THAN (14000000000),
PARTITION p10 VALUES LESS THAN (14600000000),
PARTITION p11 VALUES LESS THAN (14700000000),
PARTITION p12 VALUES LESS THAN (14800000000),
PARTITION p13 VALUES LESS THAN (15000000000),
PARTITION p14 VALUES LESS THAN (15100000000),
PARTITION p15 VALUES LESS THAN (15200000000),
PARTITION p16 VALUES LESS THAN (15300000000),
PARTITION p17 VALUES LESS THAN (15400000000),
PARTITION p18 VALUES LESS THAN (15500000000),
PARTITION p19 VALUES LESS THAN (15600000000),
PARTITION p20 VALUES LESS THAN (15700000000),
PARTITION p21 VALUES LESS THAN (15800000000),
PARTITION p22 VALUES LESS THAN (15900000000),
PARTITION p23 VALUES LESS THAN (16000000000),
PARTITION p24 VALUES LESS THAN (16300000000),
PARTITION p25 VALUES LESS THAN (16600000000),
PARTITION p26 VALUES LESS THAN (16800000000),
PARTITION p27 VALUES LESS THAN (17200000000),
PARTITION p28 VALUES LESS THAN (17300000000),
PARTITION p29 VALUES LESS THAN (17400000000),
PARTITION p30 VALUES LESS THAN (17500000000),
PARTITION p31 VALUES LESS THAN (17600000000),
PARTITION p32 VALUES LESS THAN (17700000000),
PARTITION p33 VALUES LESS THAN (17800000000),
PARTITION p34 VALUES LESS THAN (17900000000),
PARTITION p35 VALUES LESS THAN (18100000000),
PARTITION p36 VALUES LESS THAN (18200000000),
PARTITION p37 VALUES LESS THAN (18300000000),
PARTITION p38 VALUES LESS THAN (18400000000),
PARTITION p39 VALUES LESS THAN (18500000000),
PARTITION p40 VALUES LESS THAN (18600000000),
PARTITION p41 VALUES LESS THAN (18700000000),
PARTITION p42 VALUES LESS THAN (18800000000),
PARTITION p43 VALUES LESS THAN (18900000000),
PARTITION p44 VALUES LESS THAN (19000000000),
PARTITION p45 VALUES LESS THAN (19100000000),
PARTITION p46 VALUES LESS THAN (19200000000),
PARTITION p47 VALUES LESS THAN (19300000000),
PARTITION p48 VALUES LESS THAN (19400000000),
PARTITION p49 VALUES LESS THAN (19500000000),
PARTITION p50 VALUES LESS THAN (19600000000),
PARTITION p51 VALUES LESS THAN (19700000000),
PARTITION p52 VALUES LESS THAN (19800000000),
PARTITION p53 VALUES LESS THAN (19900000000),
PARTITION p54 VALUES LESS THAN (20000000000)
);

查询分区

        查看是否使用了分区

EXPLAIN select * from tel_number where phone_number = 130********

        sql使用分区查询

select * from tel_number PARTITION (p0) where phone_number = 130********

修改分区

        删除分区后再新增分区就可以了

删除分区

ALTER TABLE tel_number REMOVE PARTITIONING;

总结

MySQL的分区功能提供了一种有效的数据管理和性能优化手段，尤其适用于大规模数据集。通过合理的分区策略，可以显著提升数据库的查询效率和数据维护的便捷性。然而，设计和管理分区需要细致的规划，包括选择适当的分区键、类型和分区策略。恰当使用分区可以使数据库性能和管理效率大幅提升，但也要注意避免滥用，以免适得其反。