說明：分區(qū)表，顧名思義，就是一張表根據(jù)規(guī)則，劃分多個(gè)區(qū)，通過分區(qū)，實(shí)現(xiàn)一種“邏輯隔離”，這在Saas系統(tǒng)中是非常常見的。本文介紹如何在MySQL中分區(qū)。

創(chuàng)建分區(qū)

在MySQL中，多種分區(qū)模式，如下：

• Range：范圍分區(qū)，根據(jù)數(shù)據(jù)庫表某列數(shù)值劃分，像日期、數(shù)值類型的主鍵值；

• List：列表分區(qū)，可選定一個(gè)集合，像group_name字段，有淘寶、天貓，按照不同的集團(tuán)名分區(qū)；

• Hash：哈希分區(qū)；

• Key：鍵分區(qū)；

• Subpartitioning：復(fù)合分區(qū)；

這里介紹前面兩種常見的分區(qū)，Range、List。

首先，創(chuàng)建兩張表，用戶表、集團(tuán)表，如下：

CREATE TABLE `tb_user`
(
    `id`          int      NOT NULL AUTO_INCREMENT,
    `username`    varchar(20) DEFAULT NULL,
    `password`    varchar(20) DEFAULT NULL,
    `create_date` datetime NOT NULL,
    PRIMARY KEY (`id`, `create_date`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT ='用戶表，range分區(qū)';

CREATE TABLE `tb_group` (
  `id` int NOT NULL AUTO_INCREMENT,
  `group_name` varchar(20) DEFAULT NULL,
  `group_code` varchar(20) NOT NULL COMMENT '集團(tuán)編碼',
  PRIMARY KEY (`id`,`group_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='集團(tuán)表，list分區(qū)';

需要注意

• 創(chuàng)建分區(qū)表依靠的字段需要是主鍵或者聯(lián)合主鍵的其中一個(gè)

• 而且，在創(chuàng)建分區(qū)后，依靠分區(qū)的字段不能修改名稱；

給用戶表創(chuàng)建分區(qū)，根據(jù)創(chuàng)建時(shí)間（create_date）字段，如下：

-- 創(chuàng)建分區(qū)
alter table `tb_user`
    partition by range columns(create_date) (
        partition tb_user_1735660800000 values less than ('2025-01-01 00:00:00'),
        partition tb_user_1767196800000 values less than ('2026-01-01 00:00:00'));

這表示，創(chuàng)建時(shí)間在2025年內(nèi)的記錄為一個(gè)分區(qū)，大于2025年，小于2026年的在第二個(gè)分區(qū)。使用Range分區(qū)需要注意以下幾點(diǎn)：

• Range分區(qū)，嚴(yán)格遵循遞增分區(qū)，后面分區(qū)的less than 不能小于上一個(gè)分區(qū)；

• Range分區(qū)，數(shù)據(jù)會落在符合條件的第一個(gè)分區(qū)，如2024年的數(shù)據(jù)，會落到小于2025年的分區(qū)里，而不會落在小于2026年的分區(qū)；

創(chuàng)建成功，插入兩條數(shù)據(jù)到用戶表里；

insert into tb_user(username, password, create_date)
values ('張三', '123456', now()),
       ('李四', 'abcdef', '2025-09-22 14:05:45')

敲下面的SQL，看下分區(qū)情況：

select partition_ordinal_position,
       partition_method,
       partition_expression,
       partition_description,
       table_name,
       table_rows
from information_schema.partitions
where table_name = 'tb_user';

可以看到tb_user有兩個(gè)分區(qū)，分區(qū)的字段，數(shù)值，以及后面兩個(gè)分區(qū)各有一條記錄，說明上面插入的兩條記錄分到了兩個(gè)分區(qū)里。

（注：下面展示的是所有數(shù)據(jù)庫的tb_user表的分區(qū)情況，其他數(shù)據(jù)庫有重名的表，沒有重名的話，應(yīng)該只有兩條記錄）

再試下，List分區(qū)，這次給tb_group創(chuàng)建一個(gè)分區(qū)，如下：

-- 創(chuàng)建一個(gè)分區(qū)
alter table `tb_group`
    partition by list columns(group_code) (
        partition tb_group_001 values in ('001'));

表示，當(dāng)記錄的集團(tuán)編碼是001時(shí)，為一個(gè)分區(qū)，下面再添加一個(gè)002分區(qū)；

-- 添加一個(gè)分區(qū)
alter table tb_group
    add partition (partition tb_group_002 values in ('002'));

需要注意

• 創(chuàng)建分區(qū)和新增分區(qū)的SQL是不相同的；

• 而新增分區(qū)的前提，是這張表需要是一張分區(qū)表；

插入數(shù)據(jù)之前，先看一下分區(qū)情況，兩個(gè)分區(qū)，都沒有記錄；

插入數(shù)據(jù)

insert into tb_group(group_name, group_code)
values ('總公司', '001'),
       ('分公司', '002');

再看下分區(qū)情況，可以看到兩條數(shù)據(jù)被分到了不同分區(qū)；

如果我們插入一條數(shù)據(jù)，集團(tuán)編碼是003，即不在任何一個(gè)分區(qū)里面，會怎么樣，如下：

會報(bào)錯(cuò)，所以需要注意

• 沒有符合條件的分區(qū)，數(shù)據(jù)會插入失敗

刪除分區(qū)

刪除某張表的分區(qū)，用下面的SQL

alter table tb_group drop partition tb_group_001;

表示，刪除tb_group表的tb_group_001分區(qū)，需要注意，

• 刪除分區(qū)后，所處分區(qū)的數(shù)據(jù)也會被刪除

• 另外，不能刪除表的所有分區(qū)，或者僅剩的一個(gè)分區(qū)

綜合前面的注意點(diǎn)，如果根據(jù)某個(gè)字段創(chuàng)建分區(qū)，后續(xù)發(fā)現(xiàn)設(shè)計(jì)不合理，想再修改字段類型或者首個(gè)分區(qū)的范圍，只好刪表重建了，所以分區(qū)前要考慮清楚。

分區(qū)性能

這里創(chuàng)建一張有100萬條記錄的表，表結(jié)構(gòu)如下

CREATE TABLE `test_user_1`(
	id INT auto_increment primary key ,
	username VARCHAR(32),
	`password` VARCHAR(32),
	sex VARCHAR(6)
);

用下面這個(gè)存儲過程，創(chuàng)建一百萬條記錄

-- 創(chuàng)建存儲過程
DELIMITER $$
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
	START TRANSACTION;
    WHILE(i<1000000)DO
        INSERT INTO `test_user_1`(username, password, sex) VALUES(CONCAT('zhangsan',i) ,MD5(i), 'male');
        SET i=i+1;
    END WHILE;
	COMMIT;
END$$
DELIMITER ;

-- 調(diào)用
CALL auto_insert();

劃分為10個(gè)分區(qū)，每個(gè)分區(qū)存10萬條

現(xiàn)在，來查詢一條記錄

select id, username, password, sex from test_user_1 where username='zhangsan500025';

1秒沒到

現(xiàn)在，刪除表重建，這次不建分區(qū)，再查一次，如下：

（沒有分區(qū)）

（1秒多點(diǎn)）

老實(shí)說，我也不知道建立分區(qū)對查詢有沒有優(yōu)化，好像是有點(diǎn)……大家可以創(chuàng)建一千萬條記錄試下

總結(jié)

本文介紹了MySQL分區(qū)，及創(chuàng)建分區(qū)時(shí)的一些注意點(diǎn)，匯總?cè)缦拢?/p>

• 創(chuàng)建分區(qū)表依靠的字段需要是主鍵或者聯(lián)合主鍵中的一個(gè)；

• 創(chuàng)建分區(qū)后，依靠分區(qū)的字段不能修改名稱；

• RANGE分區(qū)，嚴(yán)格遵循遞增分區(qū)，后面分區(qū)的less than 不能小于上一個(gè)分區(qū)；

• RANGE分區(qū)，數(shù)據(jù)會落在符合條件的第一個(gè)分區(qū)；

• 創(chuàng)建分區(qū)和新增分區(qū)的SQL不同，新增分區(qū)的前提，是這張表需要是一張分區(qū)表；

• 沒有符合條件的分區(qū)，數(shù)據(jù)會插入失敗

• 刪除分區(qū)后，所處分區(qū)的數(shù)據(jù)也會被刪除

• 不能刪除表的所有分區(qū)，或者僅剩的一個(gè)分區(qū)

到此這篇關(guān)于MySQL分區(qū)表的使用的文章就介紹到這了,更多相關(guān)MySQL分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論