【分区】mysql 分区办法https://developer.aliyun.com/article/1566517 M

developer.aliyun.com/article/156…

MySQL作为一种关系型数据库管理系统，以其易用性、可靠性和高性能被广泛使用。

当数据量达到上亿级别时，查询性能可能会显著下降，影响应用的响应速度和用户体验。

为了提升查询性能，我们需要深入理解影响查询性能的因素，并应用相应的优化策略。

分区语法

-- 有了max value 就不能 add partion
ALTER TABLE Tab_Bak ADD PARTITION (
    PARTITION p201804  VALUES LESS THAN (737150)
)
> 1481 - MAXVALUE can only be used in last partition definition
 
-- 修改分区必须是已经存在的分区
ALTER TABLE  Tab_Bak REORGANIZE PARTITION pMAX INTO (
    PARTITION p201804 VALUES LESS THAN (737150),
    PARTITION pMAX VALUES LESS THAN MAXVALUE
)

> 1507 - Error in list of partitions to REORGANIZE


如果你有一个分区表t1，并且想要在MAXVALUE分区之前添加一个新的分区p3，你可以执行如下操作：
sql复制
ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
    PARTITION p3 VALUES LESS THAN (200),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);



-- 删除分区
ALTER TABLE my_table DROP PARTITION p0;

分区限制

RDS MySQL对表分区有以下限制：

只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。
最大分区数目不能超过1024。
如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键内。
不支持外键。
不支持全文索引（FULL TEXT）。

**MySQL分区表不支持全文索引（Fulltext Index），这意味着如果你的表有一个全文索引字段，那么这个表就不能被分区。因为分区表的分区键必须是表可能具有的每个唯一键的一部分，包括任何主键。全文索引的实现方式与这种分区键的要求不兼容 **

影响查询性能的因素

硬件配置：包括CPU、内存、磁盘和网络等硬件资源。
数据库设计：包括表结构设计、索引设计和分区策略等。
查询语句：包括SQL语句的编写方式、查询逻辑和索引使用等。
数据库配置：包括MySQL服务器的参数配置，如缓冲区大小、连接数和缓存策略等。

MySQL查询优化的原则

在进行查询优化时，应遵循以下原则：

减少查询数据量：通过优化SQL语句和索引设计，减少需要扫描的数据量。
减少锁定范围：通过合理的事务控制和索引设计，减少锁定的行数和时间。
避免全表扫描：通过合理的索引设计，尽量避免全表扫描，提升查询效率。
利用缓存：充分利用MySQL的查询缓存和操作系统的文件系统缓存，提升查询性能。
分解复杂查询：将复杂查询分解为多个简单查询，分批次处理数据，提升查询性能。

四、索引优化

MySQL支持多种索引类型，包括BTREE、HASH、FULLTEXT和SPATIAL等。在上亿数据的查询优化中，最常用的是BTREE索引。通过合理设计BTREE索引，可以大幅提升查询性能。

选择合适的列：选择查询条件中最常用的列作为索引列。
减少索引数目：索引虽然可以提升查询性能，但过多的索引会影响插入、更新和删除操作的性能。
覆盖索引：在查询中尽量使用覆盖索引，即查询的字段都在索引中，避免回表查询。
前缀索引：对于长字符串列，可以使用前缀索引，减少索引的大小。

分区表的使用

分区表是一种将数据分散存储在多个物理子表中的技术，可以有效提升查询性能。MySQL支持多种分区类型，包括RANGE、LIST、HASH和KEY分区。

2. 分区实例

假设我们有一个日志表logs，包含上亿条数据。表结构如下：

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

我们可以根据created_at列进行RANGE分区：

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 创建普通表结构
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    created_date DATE
);

-- 转换为分区表
ALTER TABLE my_table
PARTITION BY RANGE (YEAR(created_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- 添加分区
ALTER TABLE my_table ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (2023)
);

-- 删除分区
ALTER TABLE my_table DROP PARTITION p0;

-- 修改分区
ALTER TABLE my_table REORGANIZE PARTITION p3 INTO (
    PARTITION p3 VALUES LESS THAN (2024),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);

对于MySQL中已经存在上亿数据量的表，手动修改分区是完全可行的，以下是一些常用的操作：

添加分区：对于RANGE或LIST分区，可以使用ALTER TABLE语句添加新的分区。例如，如果已有按范围分区的表，想要追加新的分区，可以使用以下命令：
```
sql
ALTER TABLE table_name ADD PARTITION (PARTITION new_partition VALUES LESS THAN (value));
```
其中table_name是表名，new_partition是新分区的名称，value是分区的界限值。
合并分区：可以将两个连续的分区合并为一个分区。使用ALTER TABLE ... REORGANIZE PARTITION语句，指定要合并的分区。例如，将p1和p2两个分区合并：
```
sql
ALTER TABLE table_name REORGANIZE PARTITION p1, p2 INTO (PARTITION p12 VALUES LESS THAN (max_value));
```
其中p12是合并后的新分区名称，max_value是新分区的界限值。
拆分分区：可以将一个分区拆分为多个分区。同样使用ALTER TABLE ... REORGANIZE PARTITION语句，指定要拆分的分区与新分区范围。例如，将p12分区拆分为p1和p2两个分区：
```
sql
ALTER TABLE table_name REORGANIZE PARTITION p12 INTO (
    PARTITION p1 VALUES LESS THAN (value1),
    PARTITION p2 VALUES LESS THAN (value2)
);
```
其中value1和value2是新分区的界限值。
修改分区范围：可以通过ALTER TABLE ... REORGANIZE PARTITION语句修改分区范围，扩大或缩小范围。例如，将pn分区的最大值改为2000：
```
sql
ALTER TABLE table_name REORGANIZE PARTITION pn INTO (PARTITION pn VALUES LESS THAN (2000));
```
其中pn是原分区名称。
重命名分区：可以使用ALTER TABLE ... RENAME PARTITION语句重命名分区名。例如，将p1分区重命名为part1：
```
sql
ALTER TABLE table_name RENAME PARTITION p1 TO part1;
```
其中p1是原分区名称，part1是新的分区名称。
删除分区：可以使用ALTER TABLE ... DROP PARTITION语句删除分区。例如，删除p2分区：
```
sql复制
ALTER TABLE table_name DROP PARTITION p2;
```