发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

MySQL分区表的实现示例

梦西北

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
显示全部楼层
MySQL建立分区的条件是


什么是MySQL分区？

MySQL分区是将一张表分割成独立的子表的技术。每个子表被称为分区，它们有着相同的结构和字段，但存储着不同的数据。这项技术可以提高查询速度，减少日志文件和磁盘空间的使用。

建立分区的条件

要建立MySQL分区，需要满足以下几个条件：
1.所需的MySQL版本：
MySQL 5.1.5及以上版本支持分区，但仅限于使用InnoDB和MyISAM存储引擎的表。
2.分区字段：
必须定义一个或多个分区字段来确定如何将数据行分配到各个分区中。分区字段必须是表的主键或唯一索引之一。
3.分区类型：
MySQL提供了多种分区类型，包括范围分区、哈希分区和列表分区。你需要根据数据特点和查询需求选择合适的分区类型。
4.分区数量：
决定分区数量需要考虑表的大小、查询的复杂度、硬件资源等因素。建议根据具体情况选取合适的分区数量，一般不宜超过1000个。
MySQL分区技术可以大大提高查询效率和管理的便利性，但在实际使用中需要根据具体情况选择合适的分区条件和数量，避免性能瓶颈和资源浪费。

分区表介绍

MySQL 数据库中的数据是以文件的形势存在磁盘上的，默认放在
  1. /var/lib/mysql/
复制代码
目录下面，我们可以通过
  1. show variables like '%datadir%';
复制代码
命令来查看：

我们进入到这个目录下，就可以看到我们定义的所有数据库了，一个数据库就是一个文件夹，一个库中，有其对应的表的信息，如下：

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

    1. *.frm
    复制代码
    ：这个是表定义，是描述表结构的文件。
    1. *.myd
    复制代码
    ：这个是数据信息文件，是表的数据文件。
    1. *.myi
    复制代码
    ：这个是索引信息文件。
如果存储引擎是
  1. InnoDB
复制代码
, 那么在 data 目录下会看到两类文件：
  1. .frm
复制代码
、
  1. .ibd
复制代码
，如下：

    1. *.frm
    复制代码
    ：表结构文件。
    1. *.ibd
    复制代码
    ：表数据和索引的文件。
无论是哪种存储引擎，只要一张表的数据量过大，就会导致
  1. *.myd
复制代码
、
  1. *.myi
复制代码
以及
  1. *.ibd
复制代码
文件过大，数据的查找就会变的很慢。
为了解决这个问题，我们可以利用 MySQL 的分区功能，在物理上将这一张表对应的文件，分割成许多小块，如此，当我们查找一条数据时，就不用在某一个文件中进行整个遍历了，我们只需要知道这条数据位于哪一个数据块，然后在那一个数据块上查找就行了；另一方面，如果一张表的数据量太大，可能一个磁盘放不下，这个时候，通过表分区我们就可以把数据分配到不同的磁盘里面去。
通俗地讲表分区是将一大表，根据条件分割成若干个小表。
如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。
MySQL 从 5.1 开始添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。对于开发者而言，分区后的表使用方式和不分区基本上还是一模一样，只不过在物理存储上，原本该表只有一个数据文件，现在变成了多个，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
需要注意的是，分区功能并不是在存储引擎层完成的，常见的存储引擎如
  1. InnoDB
复制代码
、
  1. MyISAM
复制代码
、
  1. NDB
复制代码
等都支持分区。但并不是所有的存储引擎都支持，如
  1. CSV
复制代码
、
  1. FEDORATED
复制代码
、
  1. MERGE
复制代码
等就不支持分区，因此在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

表分区的优缺点和限制

MySQL分区有优点也有一些缺点，如下：
优点：

  • 查询性能提升：分区可以将大表划分为更小的部分，查询时只需扫描特定的分区，而不是整个表，从而提高查询性能。特别是在处理大量数据或高并发负载时，分区可以显著减少查询的响应时间。
  • 管理和维护的简化：使用分区可以更轻松地管理和维护数据。可以针对特定的分区执行维护操作，如备份、恢复、优化和数据清理，而不必处理整个表。这简化了维护任务并减少了操作的复杂性。
  • 数据管理灵活性：通过分区，可以根据业务需求轻松地添加或删除分区，而无需影响整个表。这使得数据的增长和变化更具弹性，可以根据需求进行动态调整。
  • 改善数据安全性和可用性：可以将不同分区的数据分布在不同的存储设备上，从而提高数据的安全性和可用性。例如，可以将热数据放在高速存储设备上，而将冷数据放在廉价存储设备上，以实现更高的性能和成本效益。
缺点：

  • 复杂性增加：分区引入了额外的复杂性，包括分区策略的选择、表结构的设计和维护、查询逻辑的调整等。正确地设置和管理分区需要一定的经验和专业知识。
  • 索引效率下降：对于某些查询，特别是涉及跨分区的查询，可能会导致索引效率下降。由于查询需要在多个分区之间进行扫描，可能无法充分利用索引优势，从而影响查询性能。
  • 存储空间需求增加：使用分区会导致一定程度的存储空间浪费。每个分区都需要占用一定的存储空间，包括分区元数据和一些额外的开销。因此，对于分区键的选择和分区粒度的设置需要权衡存储空间和性能之间的关系。
  • 功能限制：在某些情况下，分区可能会限制某些MySQL的功能和特性的使用。例如，某些类型的索引可能无法在分区表上使用，或者某些DDL操作可能需要更复杂的处理。
在考虑使用分区时，需要综合考虑业务需求、查询模式、数据规模和硬件资源等因素，并权衡分区带来的优势和缺点。对于特定的应用和数据场景，分区可能是一个有效的解决方案，但并不适用于所有情况。
同时分区表也存在一些限制，如下：
限制：

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

分区适用场景

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

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

分区方式

分区有2种方式，水平切分和垂直切分。MySQL 数据库支持的分区类型为水平分区，它不支持垂直分区。
此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

分区策略


RANGE分区

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

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

  • 范围划分：根据指定列的范围进行分区，适用于需要按范围进行查询和管理的情况。
  • 灵活的范围定义：可以定义任意数量的分区，并且每个分区可以具有不同的范围。
  • 高效查询：根据查询条件的范围，MySQL能够快速定位到特定的分区，提高查询效率。
  • 动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。
以下是一个使用RANGE分区的代码示例：
  1. CREATE TABLE sales (
  2.     id INT,
  3.     sales_date DATE,
  4.     amount DECIMAL(10, 2)
  5. )
  6. PARTITION BY RANGE (YEAR(sales_date)) (
  7.     PARTITION p1 VALUES LESS THAN (2020),
  8.     PARTITION p2 VALUES LESS THAN (2021),
  9.     PARTITION p3 VALUES LESS THAN (2022),
  10.     PARTITION p4 VALUES LESS THAN MAXVALUE
  11. );
复制代码
在上述示例中，我们创建了名为
  1. sales
复制代码
的表，使用RANGE分区策略。根据
  1. sales_date
复制代码
列的年份范围将数据分布到不同的分区。

    1. PARTITION BY RANGE (YEAR(sales_date))
    复制代码
    ：指定使用RANGE分区，基于
    1. sales_date
    复制代码
    列的年份进行分区。
    1. PARTITION p1 VALUES LESS THAN (2020)
    复制代码
    ：定义名为
    1. p1
    复制代码
    的分区，包含年份小于2020的数据。
    1. PARTITION p2 VALUES LESS THAN (2021)
    复制代码
    ：定义名为
    1. p2
    复制代码
    的分区，包含年份小于2021的数据。
    1. PARTITION p3 VALUES LESS THAN (2022)
    复制代码
    ：定义名为
    1. p3
    复制代码
    的分区，包含年份小于2022的数据。
    1. PARTITION p4 VALUES LESS THAN MAXVALUE
    复制代码
    ：定义名为
    1. p4
    复制代码
    的分区，包含超出定义范围的数据。
RANGE分区允许根据列值的范围将数据分散到不同的分区中，适用于按范围进行查询和管理的情况。它提供了更灵活的数据管理和查询效率的提升。

LIST分区


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


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

  • 列值离散：根据指定列的具体取值进行分区，适用于具有离散值的列。
    灵活的分区定义：可以定义任意数量的分区，并且每个分区可以具有不同的列值列表。
    高效查询：根据查询条件的列值直接定位到特定分区，提高查询效率。
    动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。
以下是一个使用LIST分区的代码示例：
  1. CREATE TABLE users (
  2. id INT,
  3. username VARCHAR(50),
  4. region VARCHAR(50)
  5. )
  6. PARTITION BY LIST (region) (
  7. PARTITION p_east VALUES IN ('New York', 'Boston'),
  8. PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco'),
  9. PARTITION p_other VALUES IN (DEFAULT)
  10. );
复制代码
在上述示例中，我们创建了名为
  1. users
复制代码
的表，使用LIST分区策略。根据
  1. region
复制代码
列的具体取值将数据分布到不同的分区。

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

HASH分区


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


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

  • 数据均匀分布：HASH分区使用哈希算法将数据均匀地分布到不同的分区中，确保数据在各个分区之间平衡。
  • 并行查询性能：通过将数据分散到多个分区，HASH分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。
  • 简化管理：HASH分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。
以下是一个使用HASH分区的代码示例：
  1. CREATE TABLE sensor_data (
  2. id INT,
  3. sensor_name VARCHAR(50),
  4. value INT
  5. )
  6. PARTITION BY HASH (id)
  7. PARTITIONS 4;
复制代码
在上述示例中，我们创建了名为
  1. sensor_data
复制代码
的表，使用HASH分区策略。根据
  1. id
复制代码
列的哈希值将数据分布到4个分区中。

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

KEY分区

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

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

  • 哈希分布：KEY分区使用列值的哈希值将数据分布到不同的分区中，与哈希函数不同，它使用的是列值的哈希值。
  • 高度自定义：KEY分区允许根据业务需求自定义分区逻辑，可以灵活地选择分区键和分区数量。
  • 并行查询性能：通过将数据分散到多个分区，KEY分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。
  • 简化管理：KEY分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。
以下是一个使用KEY分区的代码示例：
  1. CREATE TABLE orders (
  2.     order_id INT,
  3.     customer_id INT,
  4.     order_date DATE
  5. )
  6. PARTITION BY KEY (customer_id)
  7. PARTITIONS 5;
复制代码
在上述示例中，我们创建了名为
  1. orders
复制代码
的表，使用KEY分区策略。根据
  1. customer_id
复制代码
列的哈希值将数据分布到5个分区中。

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

COLUMNS 分区

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

  • RANGE COLUMNS分区： RANGE COLUMNS分区是根据列的范围值将数据分布到不同的分区的分区策略。它类似于RANGE分区，但是根据多个列的范围值进行分区，而不是只根据一个列。这使得范围的定义更加灵活，可以基于多个列的组合来进行分区。
    下面是一个RANGE COLUMNS分区的代码示例：
  1. CREATE TABLE sales (
  2.    id INT,
  3.    sales_date DATE,
  4.    region VARCHAR(50),
  5.    amount DECIMAL(10,2)
  6.    )
  7.    PARTITION BY RANGE COLUMNS(region, sales_date) (
  8.    PARTITION p1 VALUES LESS THAN ('East', '2022-01-01'),
  9.    PARTITION p2 VALUES LESS THAN ('West', '2022-01-01'),
  10.    PARTITION p3 VALUES LESS THAN ('East', MAXVALUE),
  11.    PARTITION p4 VALUES LESS THAN ('West', MAXVALUE)
  12.    );
复制代码
在上述示例中，我们创建了一个名为sales的表，并使用RANGE COLUMNS分区策略。根据region和sales_date两列的范围将数据分布到不同的分区。每个分区根据这两列的范围值进行划分。

  • LIST COLUMNS分区： LIST COLUMNS分区是根据列的离散值将数据分布到不同的分区的分区策略。它类似于LIST分区，但是根据多个列的离散值进行分区，而不是只根据一个列。这使得离散值的定义更加灵活，可以基于多个列的组合来进行分区。
    下面是一个LIST COLUMNS分区的代码示例：
  1. CREATE TABLE users (
  2.    id INT,
  3.    username VARCHAR(50),
  4.    region VARCHAR(50),
  5.    category VARCHAR(50)
  6.    )
  7.    PARTITION BY LIST COLUMNS(region, category) (
  8.    PARTITION p_east VALUES IN (('New York', 'A'), ('Boston', 'B')),
  9.    PARTITION p_west VALUES IN (('Los Angeles', 'C'), ('San Francisco', 'D')),
  10.    PARTITION p_other VALUES IN (DEFAULT)
  11.    );
复制代码
在上述示例中，我们创建了一个名为users的表，并使用LIST COLUMNS分区策略。根据region和category两列的离散值将数据分布到不同的分区。每个分区根据这两列的离散值进行划分。

常见分区命令


是否支持分区

在 MySQL5.6.1 之前可以通过命令
  1. show variables like '%have_partitioning%'
复制代码
来查看 MySQL 是否支持分区。如果
  1. have_partitioning
复制代码
的值为 YES，则表示支持分区。
从 MySQL5.6.1 开始，
  1. have_partitioning
复制代码
参数已经被去掉了，而是用
  1. SHOW PLUGINS
复制代码
来代替。若有 partition 行且 STATUS 列的值为 ACTIVE，则表示支持分区，如下所示：


创建分区表
  1. CREATE TABLE sales (
  2.     id INT,
  3.     sales_date DATE,
  4.     amount DECIMAL(10,2)
  5. )
  6. PARTITION BY RANGE (YEAR(sales_date)) (
  7.     PARTITION p1 VALUES LESS THAN (2020),
  8.     PARTITION p2 VALUES LESS THAN (2021),
  9.     PARTITION p3 VALUES LESS THAN (2022),
  10.     PARTITION p4 VALUES LESS THAN MAXVALUE
  11. );
复制代码
向分区表添加新的分区
  1. ALTER TABLE sales ADD PARTITION (
  2.     PARTITION p5 VALUES LESS THAN (2023)
  3. );
复制代码
删除指定的分区
  1. ALTER TABLE sales DROP PARTITION p3;
复制代码
重新组织分区
  1. ALTER TABLE sales REORGANIZE PARTITION p1, p2, p5 INTO (
  2.     PARTITION p1 VALUES LESS THAN (2020),
  3.     PARTITION p2 VALUES LESS THAN (2022),
  4.     PARTITION p3 VALUES LESS THAN MAXVALUE
  5. );
复制代码
合并相邻的分区：
  1. ALTER TABLE sales COALESCE PARTITION p1, p2;
复制代码
分析指定分区的统计信息：
  1. ALTER TABLE sales ANALYZE PARTITION p1;
复制代码
MySQL分区表的正确使用方法

MySQL分区表概述
我们经常遇到一张表里面保存了上亿甚至过十亿的记录，这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除（一般where条件是时间）。 这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。
分区一个最大的优点就是可以非常高效的进行历史数据的清理。
1. 确认MySQL服务器是否支持分区表
命令：
  1. show plugins;
复制代码
|

2. MySQL分区表的特点
在逻辑上为一个表，在物理上存储在多个文件中
HASH分区（HASH）
HASH分区的特点

  • 根据MOD(分区键，分区数)的值把数据行存储到表的不同分区中
  • 数据可以平均的分布在各个分区中
  • HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型
如何建立HASH分区表
以INT类型字段 customer_id为分区键
  1. CREATE TABLE `customer_login_log` (
  2. `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  3. `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
  4. `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  5. `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'

  8. PARTITION BY HASH(customer_id) PARTITIONS 4;
复制代码
以非INT类型字段 login_time 为分区键(需要先转换成INT类型)
  1. CREATE TABLE `customer_login_log` (
  2. `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  3. `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
  4. `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  5. `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'

  8. PARTITION BY HASH(UNIX_TIMESTAMP(login_time)) PARTITIONS 4;
复制代码
customer_login_log 表如果不分区，在物理磁盘上文件为
  1. customer_login_log.frm # 存储表原数据信息
  2. customer_login_log.ibd # Innodb数据文件
复制代码
如果按上面的建HASH分区表，则有五个文件
  1. customer_login_log.frm
  2. customer_login_log#P#p0.ibd
  3. customer_login_log#P#p1.ibd
  4. customer_login_log#P#p2.ibd
  5. customer_login_log#P#p3.ibd
复制代码
演示



使用起来和不分区是一样的，看起来只有一个数据库，其实有多个分区文件，比如我们要插入一条数据，不需要指定分区，MySQL会自动帮我们处理

查询

范围分区（RANGE）
RANGE分区特点

  • 根据分区键值的范围把数据行存储到表的不同分区中
  • 多个分区的范围要连续，但是不能重叠
  • 默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值
如何建立RANGE分区

如果没有定义p3分区，当插入的customer_id大于29999时会报错，定义了则超过的数据都存入p3中
RANGE分区的适用场景

  • 分区键为日期或是时间类型 （可以使得各个分区表的数据比较均衡，如果按上面的例子中以整型id为分区键，假如活跃用户集中在10000-19999之间，则p1中的数据量就会比其他分区的数据量大很多，这就失去了分区的意义；而且按时间类型分区，如果要按时间顺序进行数据的归档，则只需要对某一个分区进行归档就可以了）
  • 所有查询中都包括分区键（避免跨分区查询）
  • 定期按分区范围清理历史数据
LIST分区
LIST分区的特点

  • 按分区键取值的列表进行分区
  • 同范围分区一样，各分区的列表值不能重复
  • 每一行数据必须能找到对应的分区列表，否则数据插入失败
如何建立LIST分区

如果插入一条login_type为10的数据行，则会报错
3. 如何为登录日志表（customer_login_log）分区
业务场景

  • 用户每次登录都会记录customer_login_log日志
  • 用户登录日志保存一年，1年后可以删除或者归档
登录日志表的分区类型及分区键

  • 使用RANGE分区
  • 以login_time为分区键
分区后的用户登录日志表
按年份分区存储，所以用YEAR函数进行了转化
  1. CREATE TABLE `customer_login_log` (
  2. `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  3. `login_time` DATETIME NOT NULL COMMENT '用户登录时间',
  4. `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  5. `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
  6. ) ENGINE=InnoDB
  7. PARTITION BY RANGE (YEAR(login_time))(
  8. PARTITION p0 VALUES LESS THAN (2017),
  9. PARTITION p1 VALUES LESS THAN (2018),
  10. PARTITION p2 VALUES LESS THAN (2019)
  11. )
复制代码
插入并查询数据

查询指定表中的分区数据情况
  1. SELECT table_name,partition_name,partition_description,table_rows FROM
  2. information_schema.`PARTITIONS` WHERE table_name = 'customer_login_log';
复制代码

再插入2条18年的日志，会存入p2表中

之前说过建立分区表时，最好建立一个MAXVALUE的分区，这里之所以没有建立，是为了数据维护的方便，如果我们建立了MAXVALUE分区，很容易忽视一个问题，当我们2019年有的数据插入时，会自动存入那个MAXVALUE分区中，之后在做数据维护时会不方便，所以没有建立MAXVALUE分区
而是通过计划任务的方式，在每年年底的时候增加这个分区，比如我们现在在2018年年底，我们需要在日志表中为2019年建立日志分区，否则2019年的日志都会插入失败

我们可以通过下面语句
增加分区
  1. ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))
复制代码
增加分区，并插入数据

删除分区
假如我们现在要删除2016年到2017年间一年的数据，因为我们已经做了分区，所以只需要通过一条语句，删除p0分区即可
  1. ALTER TABLE customer_login_log DROP PARTITION p0;
复制代码

可以发现p0分区已被删除，且2016年的日志全部被清除了
归档分区历史数据
我们可能有另一种需求对数据进行归档
Mysql版本>=5.7，归档分区历史数据非常方便，提供了一个交换分区的方法
分区数据归档迁移条件：

  • MySQL>=5.7
  • 结构相同
  • 归档到的数据表一定要是非分区表
  • 非临时表；不能有外键约束
  • 归档引擎要是：archive
建表并交换分区
  1. CREATE TABLE `arch_customer_login_log` (
  2. `customer_id` INT unsigned NOT NULL COMMENT '登录用户ID',
  3. `login_time` DATETIME NOT NULL COMMENT '用户登录时间',
  4. `login_ip` INT unsigned NOT NULL COMMENT '登录IP',
  5. `login_type` TINYINT NOT NULL COMMENT '登录类型:0未成功 1成功'
  6. ) ENGINE=InnoDB ;

  8. ALTER TABLE customer_login_log
  9. exchange PARTITION p1 WITH TABLE arch_customer_login_log;
复制代码



可以发现，原customer_login_log表中的2017年的数据（p1分区中的数据）已转移到了arch_customer_login_log表中，但是p1分区未删除，只是数据转移了,所以我们还需要执行DROP命令删除分区，以免有数据插入其中
将归档数据的存储引擎改为归档引擎
最后我们将归档数据的存储引擎改为归档引擎，命令为
  1. ALTER TABLE customer_login_log ENGINE=ARCHIVE;
复制代码
使用归档引擎的好处是：它比Innodb所占用的空间更少，但是归档引擎只能进行查询操作，不能进行写操作
4. 使用分区表的主要事项

  • 结合业务场景选择分区键，避免跨分区查询
  • 对分区表进行查询最好在WHERE从句中包含分区键
  • 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分（这也是为什么我们上面分区时去掉了主键登录日志id(login_id)的原因，不然就无法按照上面的按年份进行分区，所以分区表其实更适合在MyISAM引擎中）
关于MyISAM和Innodb的索引区别
1.关于自动增长
myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。
2.关于主键
myisam允许没有任何索引和主键的表存在，
myisam的索引都是保存行的地址。
innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)
innodb的数据是主索引的一部分，附加索引保存的是主索引的值。
3.关于count()函数
myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值
innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。
4.全文索引
myisam支持 FULLTEXT类型的全文索引
innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询）
5.delete from table
使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最 好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限)
6.索引保存位置
myisam的索引以表名+.MYI文件分别保存。
innodb的索引和数据一起保存在表空间里。
到此这篇关于MySQL分区表的实现示例的文章就介绍到这了,更多相关MySQL分区表内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/326769tr2.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: IntelliJ IDEA 2024与MySQL 8连接以及driver问题解决办法

下一篇: MySQL WITH AS创建临时表的实现

发表于 2024-9-11 13:59:27

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4