Mysql大表处理方案一：分区详解教程

前言

在我们日常处理海量数据的过程中，如何有效管理和优化数据库一直是一个既重要又具有挑战性的问题，较为通用的做法就是分区、分表、分库了。那我们应该如何选择这几种方式呢？下面本人记录以下几种方式的优劣，希望能够帮助到大家。

首先分区:

数据库中分区是将表或索引的数据划分成更小、更可管理的部分的一种技术。这些部分被称为分区，每个分区可以独立地进行维护和管理，从而提高了整体数据库操作的性能。

在 MySQL 中，如果存储引擎是 MyISAM，那么在 data 目录下会看到 3 类文件：.frm、.myi、.myd，文件含义如下：

• *.frm：这个是表定义，是描述表结构的文件。
• *.myd：这个是数据信息文件，是表的数据文件。
• *.myi：这个是索引信息文件。

如果存储引擎是 InnoDB, 那么在 data 目录下会看到两类文件：.frm、.ibd，文件含义如下：

• *.frm：表结构文件。
• *.ibd：表数据和索引的文件。

无论是哪种存储引擎，只要一张表的数据量过大，就会导致 *.myd、*.myi 以及 *.ibd 文件过大，从而数据的查找就会变的很慢。

为了解决这个问题，我们可以利用 MySQL 的分区功能，在物理上将这一张表对应的文件，分割成许多小块，如此，当我们查找一条数据时，就不用在某一个文件中进行整个遍历了，我们只需要知道这条数据位于哪一个数据块，然后在那一个数据块上查找就行了。

另一方面，如果一张表的数据量太大，可能一个磁盘放不下，这个时候，通过表分区我们就可以把数据分配到不同的磁盘里面去。

通俗地讲表分区就是将一大表，根据条件分割成若干个小表。

如：某用户表的记录超过了 600 万条，那么就可以根据创建时间将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

MySQL 从 5.1 版本开始添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。

对于开发者而言，分区后的表使用方式和不分区基本上还是一模一样，只不过在物理存储上，原本该表只有一个数据文件，现在变成了多个，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

需要注意的是，分区功能并不是在存储引擎层完成的，常见的存储引擎如 InnoDB、MyISAM、NDB 等都支持分区。

但并不是所有的存储引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分区，因此在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

分区索引

mysql分区后每个分区成了独立的文件，虽然从逻辑上还是一张表其实已经分成了多张独立的表

从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID,由于Innodb数据和索引都是保存在".ibd"文件当中（从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和unique也不例外）），所以分区表的索引也是随着各个分区单独存储。

在INNODB_SYS_INDEXES系统表中type代表索引的类型：

0:一般的索引,

1:(GEN_CLUST_INDEX)不存在主键索引的表,会自动生成一个6个字节的标示值，

2:unique索引,

3:primary索引

当我们在分区表中创建索引时其实也是在每个分区中创建索引，每个分区维护各自的索引（其实也就是local index）。

对于一般的索引(非主键或者唯一)没什么问题由于索引树中只保留了索引key和主键key(如果存在主键则是主键的key否则就是系统自动生成的6个的key)不受分区的影响。

但是如果表中存在主键就不一样了，虽然在每个分区文件中都存在主键索引但是主键索引需要保证全局的唯一性就是所有分区中的主键的值都必须唯一（唯一键也是一样的道理），所以在创建分区时如果表中存在主键或者唯一键那么分区列必须包含主键或者唯一键的部分或者全部列（全部列还好理解，部分列也可以个人猜测是为了各个分区和主键建立关系），由于需要保证全局性又要保证插入数据更新数据到具体的分区所以就需要将分区和主键建立关系,由于通过一般的索引进行查找其它非索引字段需要通过主键如果主键不能保证全局唯一性的话那么就需要去每个分区查找了，这样性能可想而知。

索引方式：

性能依次降低

1.主键分区

主键分区即字段是主键同时也是分区字段，性能最好

2. 部分主键+分区索引

使用组合主键里面的部分字段作为分区字段，同时将分区字段建索引

3.分区索引

没有主键，只有分区字段且分区字段建索引

4.分区+分区字段没有索引

只建了分区，但是分区字段没有建索引

分区优劣

优点：
1、性能提升： 分区可以提高查询性能，特别是当查询只涉及到某个分区的数据时，数据库可以仅扫描相关分区，而不是整个表。这样可以减少IO操作，提高查询速度。特别是在处理大量数据或高并发负载时，分区可以显著减少查询的响应时间。

2、数据维护简化： 分区使得数据的维护更加灵活和简便。可以更容易地执行针对某个特定分区的数据备份、恢复、重新构建索引和数据清理等操作，而不会影响整个表的数据。

3、空间管理： 分区可以帮助更有效地管理存储空间。例如，可以将历史数据移动到不同的分区，以便更容易地进行归档或删除。也可以根据业务需求轻松地添加或删除分区，而无需影响整个表。这使得数据的增长和变化更具弹性，可以根据需求进行动态调整。这有助于降低整个数据库的存储成本。

4、更好的并发控制： 在某些情况下，使用分区可以提高并发性，因为不同的分区可以并行处理不同的查询请求。

5、更容易维护大型表： 对于非常大的表，分区可以帮助提高查询性能和维护效率，使其更容易处理和管理。

6、改善数据安全性和可用性：可以将不同分区的数据分布在不同的存储设备上，从而提高数据的安全性和可用性。例如，可以将热数据放在高速存储设备上，而将冷数据放在廉价存储设备上，以实现更高的性能和成本效益。

缺点：
1、复杂性增加： 分区的实施和管理可能会增加数据库的复杂性。在设计和维护分区方案时，需要考虑额外的管理和维护工作，包括分区策略的选择、表结构的设计和维护、查询逻辑的调整等。

2、性能下降： 在某些情况下，分区可能会导致性能下降，特别是当查询涉及到多个分区时。此时，数据库可能需要扫描多个分区，可能无法充分利用索引优势，从而影响查询性能。

3、不适用于所有场景： 分区并不适用于所有类型的数据库表。在某些情况下，使用分区可能没有显著的性能优势，甚至可能引入不必要的复杂性。

4、分区键选择： 选择合适的分区键是关键的，如果选择不当，可能会导致不均匀的数据分布，进而影响性能。

5、存储空间需求增加：使用分区会导致一定程度的存储空间浪费。每个分区都需要占用一定的存储空间，包括分区元数据和一些额外的开销。因此，对于分区键的选择和分区粒度的设置需要权衡存储空间和性能之间的关系。

6、功能限制：在某些情况下，分区可能会限制某些 MySQL 的功能和特性的使用。例如，某些类型的索引可能无法在分区表上使用，或者某些 DDL 操作可能需要更复杂的处理。

• 在 MySQL 5.6.7 之前的版本，一个表最多有 1024 个分区，从 5.6.7 开始，一个表最多可以有 8192 个分区。
• 分区表无法使用外键约束。
• NULL 值会使分区过滤无效。
• 所有分区必须使用相同的存储引擎。

总体而言，数据库中分区是一种有益的技术，在考虑使用分区时，需要综合考虑业务需求、查询模式、数据规模和硬件资源等因素，并权衡分区带来的优势和缺点。对于特定的应用和数据场景，分区可能是一个有效的解决方案，但并不适用于所有情况。

分区适用场景

分区表在以下情况可以发挥其优势，适用于以下几种使用场景：

• 大型表处理：当面对非常大的表时，分区表可以提高查询性能。通过将表分割为更小的分区，查询操作只需要处理特定的分区，从而减少扫描的数据量，提高查询效率。这在处理日志数据、历史数据或其他需要大量存储和高性能查询的场景中非常有用。
• 时间范围查询：对于按时间排序的数据，分区表可以按照时间范围进行分区，每个分区包含特定时间段内的数据。这使得按时间范围进行查询变得更高效，例如在某个时间段内检索数据、生成报表或执行时间段的聚合操作。
• 数据归档和数据保留：分区表可用于数据归档和数据保留的需求。旧数据可以归档到单独的分区中，并将其存储在低成本的存储介质上。同时，可以保留较新数据在高性能的存储介质上，以便快速查询和操作。
• 并行查询和负载均衡：通过哈希分区或键分区，可以将数据均匀地分布在多个分区中，从而实现并行查询和负载均衡。查询可以同时在多个分区上进行，并在最终合并结果，提高查询性能和系统吞吐量。
• 数据删除和维护：使用分区表，可以更轻松地删除或清理不再需要的数据。通过删除整个分区，可以更快速地删除大量数据，而不会影响整个表的操作。此外，可以针对特定分区执行维护任务，如重新构建索引、备份和优化，以减少对整个表的影响。

分区表并非适用于所有情况。在选择使用分区表时，需要综合考虑数据量、查询模式、存储资源和硬件能力等因素，并评估分区对性能和管理的影响。

分区方式

分区有两种方式，水平切分和垂直切分，MySQL 数据库支持的分区类型为水平分区，它不支持垂直分区。

此外，MySQL 数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL 数据库还不支持全局分区，所以要特别注意索引问题。

分区策略

RANGE 分区

RANGE 分区是 MySQL 中的一种分区策略，根据某一列的范围值将数据分布到不同的分区。每个分区包含特定的范围。下面是 RANGE 分区的定义方式、特点以及代码示例。

定义方式：

• 指定分区键：选择作为分区依据的列作为分区键，通常是日期、数值等具有范围特性的列。
• 分区函数：通过PARTITION BY RANGE指定使用 RANGE 分区策略。
• 定义分区范围：使用VALUES LESS THAN子句定义每个分区的范围。

RANGE 分区的特点：

• 范围划分：根据指定列的范围进行分区，适用于需要按范围进行查询和管理的情况。
• 灵活的范围定义：可以定义任意数量的分区，并且每个分区可以具有不同的范围。
• 高效查询：根据查询条件的范围，MySQL 能够快速定位到特定的分区，提高查询效率。
• 动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。

以下是一个使用 RANGE 分区的代码示例：

CREATE TABLE sales (
 id INT,
 sales_date DATE,
 amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sales_date)) (
 PARTITION p1 VALUES LESS THAN (2020),
 PARTITION p2 VALUES LESS THAN (2021),
 PARTITION p3 VALUES LESS THAN (2022),
 PARTITION p4 VALUES LESS THAN MAXVALUE
);

在上述示例中，我们创建了名为sales的表，使用 RANGE 分区策略。根据sales_date列的年份范围将数据分布到不同的分区：

• PARTITION BY RANGE (YEAR(sales_date))：指定使用 RANGE 分区，基于sales_date列的年份进行分区。
• PARTITION p1 VALUES LESS THAN (2020)：定义名为p1的分区，包含年份小于 2020 的数据。
• PARTITION p2 VALUES LESS THAN (2021)：定义名为p2的分区，包含年份小于 2021 的数据。
• PARTITION p3 VALUES LESS THAN (2022)：定义名为p3的分区，包含年份小于 2022 的数据。
• PARTITION p4 VALUES LESS THAN MAXVALUE：定义名为p4的分区，包含超出定义范围的数据。

RANGE 分区允许根据列值的范围将数据分散到不同的分区中，适用于按范围进行查询和管理的情况。它提供了更灵活的数据管理和查询效率的提升。

LIST 分区

LIST 分区是根据某一列的离散值将数据分布到不同的分区。每个分区包含特定的列值列表。下面是 LIST 分区的定义方式、特点以及代码示例。

定义方式：

• 指定分区键：选择作为分区依据的列作为分区键，通常是具有离散值的列，如地区、类别等。
• 分区函数：通过PARTITION BY LIST指定使用 LIST 分区策略。
• 定义分区列表：使用VALUES IN子句定义每个分区包含的列值列表。

LIST 分区的特点：

• 列值离散：根据指定列的具体取值进行分区，适用于具有离散值的列。
• 灵活的分区定义：可以定义任意数量的分区，并且每个分区可以具有不同的列值列表。
• 高效查询：根据查询条件的列值直接定位到特定分区，提高查询效率。
• 动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。

以下是一个使用 LIST 分区的代码示例：

CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50)
)
PARTITION BY LIST (region) (
 PARTITION p_east VALUES IN ('New York', 'Boston'), 
 PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco'), 
 PARTITION p_other VALUES IN (DEFAULT)
);

在上述示例中，我们创建了名为users的表，使用 LIST 分区策略。根据region列的具体取值将数据分布到不同的分区：

• PARTITION BY LIST (region)：指定使用 LIST 分区，基于region列的值进行分区。
• PARTITION p_east VALUES IN ('New York', 'Boston')：定义名为p_east的分区，包含值为'New York'和'Boston'的region列的数据。
• PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco')：定义名为p_west的分区，包含值为'Los Angeles'和'San Francisco'的region列的数据。
• PARTITION p_other VALUES IN (DEFAULT)：定义名为p_other的分区，包含其他region列值的数据。

HASH 分区

HASH 分区是使用哈希算法将数据均匀地分布到多个分区中。下面是 HASH 分区的定义方式、特点以及代码示例。

定义方式：

• 指定分区键：选择作为分区依据的列作为分区键。
• 分区函数：通过PARTITION BY HASH指定使用 HASH 分区策略。
• 定义分区数量：使用PARTITIONS关键字指定分区的数量。

HASH 分区的特点：

• 数据均匀分布：HASH 分区使用哈希算法将数据均匀地分布到不同的分区中，确保数据在各个分区之间平衡。
• 并行查询性能：通过将数据分散到多个分区，HASH 分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。
• 简化管理：HASH 分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。

以下是一个使用 HASH 分区的代码示例：

CREATE TABLE sensor_data (
 id INT,
 sensor_name VARCHAR(50),
 value INT
)
PARTITION BY HASH (id) PARTITIONS 4;

在上述示例中，我们创建了名为sensor_data的表，使用 HASH 分区策略。根据id列的哈希值将数据分布到 4 个分区中：

• PARTITION BY HASH (id)：指定使用 HASH 分区，基于id列的哈希值进行分区。
• PARTITIONS 4：指定创建 4 个分区。

KEY 分区

KEY 分区是根据某一列的哈希值将数据分布到不同的分区。不同于 HASH 分区，KEY 分区使用的是列值的哈希值而不是哈希函数。下面是 KEY 分区的定义方式、特点以及代码示例。

定义方式：

• 指定分区键：选择作为分区依据的列作为分区键。
• 分区函数：通过PARTITION BY KEY指定使用 KEY 分区策略。
• 定义分区数量：使用PARTITIONS关键字指定分区的数量。

KEY 分区的特点：

• 哈希分布：KEY 分区使用列值的哈希值将数据分布到不同的分区中，与哈希函数不同，它使用的是列值的哈希值。
• 高度自定义：KEY 分区允许根据业务需求自定义分区逻辑，可以灵活地选择分区键和分区数量。
• 并行查询性能：通过将数据分散到多个分区，KEY 分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。
• 简化管理：KEY 分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。

以下是一个使用 KEY 分区的代码示例：

CREATE TABLE orders (
 order_id INT,
 customer_id INT,
 order_date DATE
)
PARTITION BY KEY (customer_id) PARTITIONS 5;

在上述示例中，我们创建了名为orders的表，使用 KEY 分区策略。根据customer_id列的哈希值将数据分布到 5 个分区中：

• PARTITION BY KEY (customer_id)：指定使用 KEY 分区，基于customer_id列的哈希值进行分区。
• PARTITIONS 5：指定创建 5 个分区。

COLUMNS 分区

MySQL 在 5.5 版本引入了 COLUMNS 分区类型，其中包括 RANGE COLUMNS 分区和 LIST COLUMNS 分区。以下是对这两种 COLUMNS 分区的详细说明：

RANGE COLUMNS 分区： RANGE COLUMNS 分区是根据列的范围值将数据分布到不同的分区的分区策略。它类似于 RANGE 分区，但是根据多个列的范围值进行分区，而不是只根据一个列。这使得范围的定义更加灵活，可以基于多个列的组合来进行分区。

下面是一个 RANGE COLUMNS 分区的代码示例：

CREATE TABLE sales (
 id INT,
 sales_date DATE,
 region VARCHAR(50),
 amount DECIMAL(10, 2)
)
PARTITION BY RANGE COLUMNS (region, sales_date) (
 PARTITION p1 VALUES LESS THAN ('East', '2022-01-01'),
 PARTITION p2 VALUES LESS THAN ('West', '2022-01-01'),
 PARTITION p3 VALUES LESS THAN ('East', MAXVALUE),
 PARTITION p4 VALUES LESS THAN ('West', MAXVALUE)
);

在上述示例中，我们创建了一个名为 sales 的表，并使用 RANGE COLUMNS 分区策略。根据 region 和 sales_date 两列的范围将数据分布到不同的分区。每个分区根据这两列的范围值进行划分。

LIST COLUMNS 分区： LIST COLUMNS 分区是根据列的离散值将数据分布到不同的分区的分区策略。它类似于 LIST 分区，但是根据多个列的离散值进行分区，而不是只根据一个列。这使得离散值的定义更加灵活，可以基于多个列的组合来进行分区。

下面是一个 LIST COLUMNS 分区的代码示例：

CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50),
 category VARCHAR(50)
)
PARTITION BY LIST COLUMNS (region, category) (
 PARTITION p_east VALUES IN (('New York', 'A'), ('Boston', 'B')), 
 PARTITION p_west VALUES IN (('Los Angeles', 'C'), ('San Francisco', 'D')), 
 PARTITION p_other VALUES IN (DEFAULT)
);

在上述示例中，我们创建了一个名为 users 的表，并使用 LIST COLUMNS 分区策略。根据 region 和 category 两列的离散值将数据分布到不同的分区。每个分区根据这两列的离散值进行划分。

子分区其实是对每个分区表的每个分区进行再次分隔，目前只有RANGE和LIST分区的表可以再进行子分区，子分区只能是HASH或者KEY分区。子分区可以将原本的数据进行再次的分区划分。

子分区

子分区由两种创建方法，一种是不定义每个子分区子分区的名字和路径由分区决定，二是定义每个子分区的分区名和各自的路径

1.不定义每个子分区

CREATE TABLE tb_sub (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

2.定义每个子分区

定义子分区可以为每个子分区定义具体的分区名和分区路径

CREATE TABLE tb_sub_ev (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

3.测试数据

INSERT INTO tb_sub_ev() VALUES(1,'1989-01-01'),(2,'1989-03-19'),(3,'1989-04-19');

当往里面插入三条记录时，其中‘1989-01-01’和‘1989-04-19’存储在p0_s0分区中，‘1989-03-19’存储在p0_s1当中

分区管理

分区管理和RANGE、LIST的分区管理是一样的

1.合并分区

将p0,p1两个分区合并

ALTER TABLE tb_sub_ev REORGANIZE PARTITION p0,p1 INTO (
    PARTITION m1 VALUES LESS THAN (2000)
    ( SUBPARTITION n0,
      SUBPARTITION n1
     ) 
    
);

注意：合并分区的子分区也必须是两个，这点需要理解，因为必须和创建分区时每个分区只有两个子分区保持一致，合并分区不会造成数据的丢失。

2.拆分分区

ALTER TABLE tb_sub_ev REORGANIZE PARTITION m1 INTO (
     PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        )
    
);
同样，拆分分区也必须保证每个分区是两个子分区。
3.删除分区
ALTER TABLE  tb_sub_ev DROP PARTITION P0;
注意：由于分区是RANGE和LIST分区，所以删除分区也是同RANGE和LIST分区一样，这里只能对每个分区进行删除，不能针对每个子分区进行删除操作，删除分区后子分区连同数据一并被删除。

总结

尽管数据库分区技术带来了诸多优点，但在实施和管理过程中也伴随着一些挑战。复杂性的增加、性能下降的风险以及合适性的考量都需要被认真对待。在应用分区时，我们需要根据具体业务需求和数据库特性，精心设计和选择分区策略，以确保它在提升性能的同时，不引入过多的复杂性和不必要的开销。只有综合权衡各方面因素，数据库分区技术才能充分发挥其潜在优势，为数据库管理和应用性能提供可行的解决方案。

感谢阅读，如果本篇文章有任何错误和建议，欢迎给我留言指正。