MySQL是一種開放源代碼的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，廣泛應(yīng)用于 Web 應(yīng)用程序開發(fā)。當(dāng)數(shù)據(jù)量增長(zhǎng)到一定程度，數(shù)據(jù)庫表變得越來越大，這時(shí)需要對(duì)表進(jìn)行分區(qū)（分表）處理，以提高數(shù)據(jù)庫的查詢效率。

以下是如何將已有的表分表：

-- 1. 建立一個(gè)分區(qū)表
CREATE TABLE `orders_partition` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`order_date` date NOT NULL,
`order_amount` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`,`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN MAXVALUE
);
-- 2. 將已有表數(shù)據(jù)導(dǎo)入分區(qū)表
INSERT INTO orders_partition (user_id, order_date, order_amount)
SELECT user_id, order_date, order_amount FROM orders;
-- 3. 重命名已有表，將分區(qū)表的名字改為已有表的名字
RENAME TABLE orders TO orders_backup, orders_partition TO orders;
-- 4. 清空已有表的自增長(zhǎng)ID
ALTER TABLE orders_backup AUTO_INCREMENT = 1;
-- 5. 添加觸發(fā)器，使已有表在插入數(shù)據(jù)時(shí)自動(dòng)插入分區(qū)表
DELIMITER $$
CREATE TRIGGER `trig_insert_orders`
BEFORE INSERT ON `orders`
FOR EACH ROW
BEGIN
DECLARE d DATE;
SET d = NEW.order_date;
SET @part_num = YEAR(d);
SET @part_name = CONCAT('p', @part_num);
SET @sql = CONCAT('INSERT INTO orders_partition VALUES (NULL, NEW.user_id, "', d, '", NEW.order_amount) PARTITION (', @part_name, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

以上是將已有的表分表的方法。