MySQL分库分表与分区的入门指南

什么是MySQL分库分表与分区

MySQL分库分表是将一个大的数据库拆分成多个小型数据库，每个数据库存储不同的数据，以达到提高性能、降低负载的目的。

MySQL分区是将一个表分割成多个分区，每个分区存储不同的数据，以达到提高查询速度、减少磁盘I/O的目的。

MySQL分库分表的实现方式

垂直分库

垂直分库就是将不同的表拆分到不同的数据库中，每个数据库只存储自己需要的数据。这种方式适合于业务耦合度较低、数据关联性不强的系统。

示例：将用户信息和订单信息分别存放在两个数据库中

CREATE DATABASE user_db; -- 创建用户信息数据库
USE user_db;
CREATE TABLE user_info (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT,
    PRIMARY KEY (user_id)
);

CREATE DATABASE order_db; -- 创建订单信息数据库
USE order_db;
CREATE TABLE order_info (
    order_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_time DATETIME NOT NULL,
    order_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id),
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES user_db.user_info(user_id) -- 外键约束
);

水平分库

水平分库就是将同一张表的数据按照一定规则拆分到不同的数据库中。这种方式适合于数据量较大、读写频繁的系统。

示例：将订单信息按照用户ID分散存储到多个数据库中

-- 创建订单信息表
CREATE TABLE order_info (
    order_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_time DATETIME NOT NULL,
    order_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id)
);

-- 创建分表规则函数
DELIMITER $$
CREATE FUNCTION order_db_name(user_id INT) RETURNS varchar(20)
BEGIN
    RETURN CONCAT('order_', MOD(user_id, 5)); -- 按照余数规则将订单存储到不同的数据库中
END$$
DELIMITER ;

-- 创建触发器，实现自动拆分
DELIMITER $$
CREATE TRIGGER order_info_insert_trigger BEFORE INSERT ON order_info
FOR EACH ROW BEGIN
    SET @db_name = order_db_name(NEW.user_id);
    SET @sql = CONCAT('INSERT INTO ', @db_name, '.order_info(order_id, user_id, order_time, order_price) VALUES (?,?,?,?)');
    PREPARE stmt FROM @sql;
    SET NEW.order_id = NULL;
    EXECUTE stmt USING NEW.order_id, NEW.user_id, NEW.order_time, NEW.order_price;
    DEALLOCATE PREPARE stmt;
    SET @stmt = NULL;
END $$
DELIMITER ;

-- 插入测试数据
INSERT INTO order_info(user_id, order_time, order_price) VALUES (1, '2021-01-01 00:00:01', 100.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (2, '2021-01-01 00:01:01', 200.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (3, '2021-01-01 00:02:01', 300.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (4, '2021-01-01 00:03:01', 400.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (5, '2021-01-01 00:04:01', 500.00);

MySQL分区的实现方式

MySQL分区可以将一张表分成多个数据范围相同的部分，每个分区单独进行存储，查询时只需查询相应的分区，提高了查询效率。

数据范围分区

数据范围分区是根据表中某列的值范围来分区，常用来对存储时间较长的数据进行分区。

示例：按照订单时间来对订单信息进行分区

-- 创建分区表（需要用到InnoDB存储引擎）
CREATE TABLE order_info (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_time DATETIME NOT NULL,
    order_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, order_time)
) ENGINE=InnoDB
PARTITION BY RANGE(TO_DAYS(order_time)) (
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2021-01-01')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2021-02-01')),
    PARTITION p3 VALUES LESS THAN (TO_DAYS('2021-03-01'))
);

-- 插入测试数据
INSERT INTO order_info(order_time, order_price) VALUES ('2021-01-01 00:00:01', 100.00);
INSERT INTO order_info(order_time, order_price) VALUES ('2021-01-15 00:00:01', 200.00);
INSERT INTO order_info(order_time, order_price) VALUES ('2021-02-01 00:00:01', 300.00);
INSERT INTO order_info(order_time, order_price) VALUES ('2021-02-15 00:00:01', 400.00);
INSERT INTO order_info(order_time, order_price) VALUES ('2021-03-01 00:00:01', 500.00);

哈希分区

哈希分区是根据表中某列值的哈希值进行分区，常用来对随机访问的数据进行优化。

示例：按照用户ID对订单信息进行哈希分区

-- 创建哈希分区表（需要用到InnoDB存储引擎）
CREATE TABLE order_info (
    order_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_time DATETIME NOT NULL,
    order_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id)
) ENGINE=InnoDB
PARTITION BY HASH(user_id)
PARTITIONS 5; -- 分为5个分区

-- 插入测试数据
INSERT INTO order_info(user_id, order_time, order_price) VALUES (1, '2021-01-01 00:00:01', 100.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (2, '2021-01-01 00:01:01', 200.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (3, '2021-01-01 00:02:01', 300.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (4, '2021-01-01 00:03:01', 400.00);
INSERT INTO order_info(user_id, order_time, order_price) VALUES (5, '2021-01-01 00:04:01', 500.00);

总结

MySQL分库分表与分区是常见的数据库优化手段，可以有效提高查询效率、降低负载。通过本指南的介绍，相信大家已经有了基本的了解和认识。