如何基于字符列对MySQL表进行分区

在本文中，我们将介绍如何使用MySQL分区技术来优化表的查询性能。如何在MySQL表中使用分区技术来提高查询效率是MySQL开发者们一直在关注的一个问题。分区技术会将表格分割成多个独立的区域，每个区域可以单独存储和维护数据。

阅读更多：MySQL 教程

为什么要对MySQL表进行分区？

当数据量太大时，使用普通的SQL查询会慢的无法忍受。尤其是对于频繁查询的字段，更是影响查询效率。而分区技术可以帮助我们优化查询效率，提高查询速度。当系统需要查询表中的数据时，MySQL分区技术会直接查找所需分区，而不必扫描整张表格，所以可以提高查询速度。

如何在MySQL中进行表分区？

可以基于不同的列来分区。考虑以下表格：

CREATE TABLE my_table (
    id INT NOT NULL AUTO_INCREMENT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    name CHAR(50) NOT NULL,
    address CHAR(100) NOT NULL,
    staff_id INT NOT NULL,
    INDEX(name,create_time),
    PRIMARY KEY(id)
) ENGINE=InnoDB;

如果根据“address”列对表格进行分区，代码如下：

ALTER TABLE my_table PARTITION BY HASH(address) PARTITIONS 3;

其中，“PARTITION BY HASH”这个部分指定了分区方式。可以通过“RANGE”或“LIST”指定基于范围或列表的分区，也可以通过“HASH”指定基于哈希的分区。

“HASH”分区方式需要指定的“PARTITIONS”表示分区的个数。例如，上述代码分成了3个分区。

分区方式及其优缺点

RANGE分区

优点

RANGE方式就是按照一定的范围对表格进行分区，可以根据范围值来确定将记录插入到那个分区。

该种方式最大的优点是分区比较灵活，方便维护。

缺点

缺点是要求分区的字段必须连续，不能有间隙。而且，分区字段要求为整型。

LIST分区

优点

可以分区的范围不需要连续，可以按照自定义列表进行分区。例如，将多家分店的订单记录分区：

ALTER TABLE orders
PARTITION BY LIST(store_id)
(PARTITION pNorth VALUES IN (3,5,6),
 PARTITION pCentral VALUES IN (1,7,8),
 PARTITION pEast VALUES IN (2,10,11));

缺点

同RANGE方式，该种方式只能基于整型字段进行分区。

HASH分区

优点

通常情况下，如果我们无法确定如何使用RANGE或LIST分区方式时，HASH分区方式是一个不错的选择。一般而言，HASH方式可以确保数据在各个分区中分布均匀，从而降低查询时的IO负担并提高查询速度。

缺点

HASH方式其实是一种弱化版的RANGE分区方式。因为HASH方式处理后，分布不一定是完全均匀的。

实例

以下是一个实例，领略分区的常见用法：

我们需要创建一个大型的在售商品表sales_goods，该表包含了上千万个商品的各种信息，我们可以考虑为其进行分区。

选择售价的范围作为分区依据：

CREATE TABLE sales_goods (
   name CHAR(20),
   price INT,
   cost_price INT
)
PARTITION BY RANGE(price)(
   PARTITION p0 VALUES LESSTHAN (100),
   PARTITION p1 VALUES LESS THAN (200),
   PARTITION p2 VALUES LESS THAN (300),
   PARTITION p3 VALUES LESS THAN (400),
   PARTITION p4 VALUES LESS THAN (500),
   PARTITION p5 VALUES LESS THAN (600)
);

选择商品的品牌作为分区依据：

CREATE TABLE sales_goods (
   name CHAR(20),
   price INT,
   cost_price INT,
   brand CHAR(10)
)
PARTITION BY KEY(brand)(
   PARTITION p0 ENGINE=InnoDB,
   PARTITION p1 ENGINE=InnoDB,
   PARTITION p2 ENGINE=InnoDB,
   PARTITION p3 ENGINE=InnoDB
);