Mysql 表分区

CodeFans
75 阅读7分钟

表分区sql对比：

#分区表创建
CREATE TABLE orders ( id INT, order_date DATE ) PARTITION BY RANGE (YEAR(order_date)) 
( PARTITION p2015 VALUES LESS THAN (2016), 
  PARTITION p2016 VALUES LESS THAN (2017), 
  PARTITION p2017 VALUES LESS THAN (2018) ); 

#普通表创建
CREATE TABLE orders ( id INT, order_date DATE )；

以下是两个 SQL 语句创建的表在结构和使用上的详细区别：

1. 结构上的区别

(1) 分区 vs 非分区

  • 第一个表（分区表） ：

    sql

CREATE TABLE orders (

    id INT,

    order_date DATE

    )

    PARTITION BY RANGE (YEAR(order_date)) (

    PARTITION p2015 VALUES LESS THAN (2016),

    PARTITION p2016 VALUES LESS THAN (2017),

    PARTITION p2017 VALUES LESS THAN (2018)

    );

-   **结构特点** ：

    -   表数据被划分为多个物理分区（`p2015`、`p2016`、`p2017`）。

    -   每个分区存储符合 `YEAR(order_date)` 范围的数据：

        -   `p2015`：`order_date < 2016`（即 2015 年及之前的数据）。
        -   `p2016`：`2016-01-01 <= order_date < 2017-01-01`。
        -   `p2017`：`2017-01-01 <= order_date < 2018-01-01`。

  • 第二个表（普通表） ：

    sql

 CREATE TABLE orders (

    id INT,

    order_date DATE

    );

-   **结构特点** ：

    -   数据存储在一个单一的物理表中，无分区。
    -   所有数据（无论年份）混合存储。

(2) 元数据差异

  • 分区表 ：

    • 通过 SHOW CREATE TABLE orders; 可以看到分区定义。
    • 在 INFORMATION_SCHEMA.PARTITIONS 中会记录分区信息。

  • 普通表 ：

    • 无分区相关元数据。

2. 使用上的区别

(1) 数据存储与查询

  • 分区表 ：

    • 查询优化 ：
      当查询条件包含 order_date 的范围条件时，数据库会自动仅扫描相关分区 （分区裁剪，Partition Pruning）。
      例如：

      sql

      -- 查询 2015 年分区的所有数据
SELECT * FROM orders PARTITION (p2015);

-- 查询 2015 年分区中订单 ID 为 100 的记录
SELECT * FROM orders PARTITION (p2015) WHERE id = 100;

SELECT * FROM orders WHERE order_date BETWEEN '2015-01-01' AND '2015-12-31';

      该查询只会扫描 p2015 分区，速度更快。

    • 数据管理 ：
      可通过 ALTER TABLE ... DROP PARTITION 快速删除历史数据（如 DROP PARTITION p2015），无需逐行删除。

  • 普通表 ：

    • 查询性能 ：
      所有查询需要全表扫描，数据量大时性能下降明显。
    • 数据删除 ：
      删除历史数据需执行 DELETE 语句，逐行操作效率低，且产生大量事务日志。

(2) 索引行为

  • 分区表 ：

    • 索引是局部索引 （每个分区独立维护索引）。
    • 查询时仅需访问相关分区的索引，减少 I/O 开销。

  • 普通表 ：

    • 索引是全局索引 ，所有数据共享同一索引结构。
    • 数据量大时，索引维护成本高。

(3) 维护与扩展

  • 分区表 ：

    • 支持动态增加新分区（如 ALTER TABLE ... ADD PARTITION）。
    • 适合存储时间序列数据（如日志、订单），便于归档和清理。

  • 普通表 ：

    • 无法按时间范围自动划分数据，维护成本高。
    • 数据量过大时可能需要手动分表（如按年分表）。

3. 示例对比

(1) 插入数据

  • 分区表 ：

    sql

    INSERT INTO orders VALUES (1, '2015-05-01'); -- 自动存储到 p2015
    INSERT INTO orders VALUES (2, '2016-08-15'); -- 自动存储到 p2016

  • 普通表 ：

    sql

    INSERT INTO orders VALUES (1, '2015-05-01'); -- 存储到同一物理表
    INSERT INTO orders VALUES (2, '2016-08-15');

(2) 查询数据

  • 分区表 ：

    sql

    EXPLAIN SELECT * FROM orders WHERE order_date = '2015-05-01';

    -- 输出中会显示 "partitions: p2015"

  • 普通表 ：

    sql

    EXPLAIN SELECT * FROM orders WHERE order_date = '2015-05-01';

    -- 输出中无分区信息，全表扫描

4. 总结

特性分区表普通表
数据存储按分区物理分离（如按年）单一物理存储
查询性能分区裁剪优化查询速度全表扫描，性能随数据量下降
数据管理支持快速删除/归档分区需逐行删除，效率低
索引维护局部索引，维护成本低全局索引，维护成本高
适用场景大数据量、时间范围查询、历史归档小数据量或简单查询场景

**

选择建议

  • 如果表数据量大（如千万级）且需按时间范围管理，优先使用分区表 。
  • 如果数据量小或查询模式简单，普通表更简单直接 。

数据分区（Partitioning）是数据库中一种优化技术，通过将大表的数据按特定规则划分为多个独立的物理存储单元（分区），以提升性能、简化管理。以下是数据分区的主要作用及应用场景：

1. 提升查询性能（分区裁剪）

  • 核心原理 ：
    当查询条件包含分区键（如时间、范围）时，数据库会自动跳过无关分区，仅扫描相关分区（称为 Partition Pruning ）。

  • 示例 ：
    对于按年份分区的订单表，查询 2015 年的数据时，数据库只会扫描 p2015 分区，而非全表。

  • 效果 ：

    • 减少 I/O 操作，提升查询速度。
    • 降低内存消耗，避免全表加载。

2. 高效管理海量数据

  • 快速删除/归档历史数据 ：
    直接删除整个分区（如 DROP PARTITION p2015），无需逐行操作，避免锁表和事务日志膨胀。

  • 示例 ：

    sql

ALTER TABLE orders DROP PARTITION p2015; -- 秒级删除 2015 年数据

  • 对比传统 DELETE ：

    • 分区删除：O(1) 时间复杂度，无碎片残留。
    • 逐行删除：O(n) 时间复杂度，可能阻塞其他操作。

3. 存储优化

  • 冷热数据分离 ：
    将频繁访问的热数据（如近期订单）和冷数据（如历史记录）分开存储。

  • 存储策略 ：

    • 热数据：存储在高性能存储介质（如 SSD）。
    • 冷数据：存储在低成本存储（如 HDD 或云存储）。

  • 优势 ：
    降低存储成本，同时保证热数据的访问速度。

4. 分布式存储与并行处理

  • 跨磁盘/节点分布 ：
    将不同分区存储在不同磁盘或服务器上，实现负载均衡。

  • 并行查询 ：
    数据库可以并行扫描多个分区，加速复杂查询（如聚合操作）。

  • 示例 ：

    sql

SELECT SUM(amount) FROM orders; -- 可并行计算各分区的和

5. 简化维护操作

  • 重建索引 ：
    可单独重建某个分区的索引，而非全表重建。

  • 数据迁移 ：
    移动或备份单个分区，而非整个表。

  • 示例 ：

    sql

ALTER TABLE orders REBUILD PARTITION p2016; -- 仅重建 p2016 分区索引

6. 支持大数据量场景

  • 突破单表存储限制 ：
    传统单表可能因数据量过大导致性能下降，分区表通过分治策略支持 TB/PB 级数据。

  • 适用场景 ：

    • 日志表（按时间分区）。
    • 物联网设备数据（按设备 ID 分区）。
    • 交易记录（按用户 ID 或地域分区）。

7. 分区策略的灵活性

  • 多种分区方式 ：

    • 范围分区 （RANGE）：按时间、数值范围划分（如按年）。
    • 列表分区 （LIST）：按枚举值划分（如按地区）。
    • 哈希分区 （HASH）：按哈希算法均匀分布（如按用户 ID）。
    • 组合分区 ：范围 + 哈希（如先按年分区，再按用户 ID 哈希）。

  • 动态扩展 ：
    可随时添加新分区（如为未来年份预创建分区）。

实际应用场景

  1. 时间序列数据 ：

    • 日志、订单、交易记录按时间分区，快速清理历史数据。

  2. 多租户系统 ：

    • 按租户 ID 分区，隔离不同客户的数据。

  3. 高写入负载 ：

    • 分区可分散写入压力，避免单点瓶颈。

对比不分区的劣势

场景分区表普通表
删除历史数据秒级完成（DROP PARTITION）逐行删除，耗时且锁表
大表全表扫描仅扫描相关分区全表扫描，性能差
存储管理冷热数据分离，成本优化混合存储，成本高
索引维护局部索引，维护成本低全局索引，维护成本高

后期修改分区

如果数据表中已有大量数据，现在需要分区，那么sql示例为：

-- 重新定义表结构，添加分区
ALTER TABLE orders
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p_future VALUES LESS THAN MAXVALUE  -- 未来数据
);
avatar
文章
阅读
粉丝