一个简单的示例演示了如何在MySQL中进行分库分表及分页查询
背景
数据量大时，分库分表是一种常见的优化方案。为了更好地理解分表及其查询操作，这里我们直接通过一个简单的 demo 演示如何在 MySQL 中进行分表以及分页查询。
一、创建测试数据库和表
首先，创建一个数据库 test 并创建用户表 tb_member，用于存储用户数据。
CREATE DATABASE test;
USE test;
CREATE TABLE tb_member (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age TINYINT NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 COMMENT='用户表';
插入 100 条随机测试数据
使用以下 SQL 语句批量插入 100 条随机数据。
INSERT INTO tb_member (name, age)
SELECT
CONCAT('Member', FLOOR(RAND() * 1000)) AS name,
FLOOR(RAND() * 100) AS age
FROM
information_schema.tables
LIMIT 100;
二、分表操作
接下来，将用户表 tb_member 拆分为两张表 tb_member1 和 tb_member2，并将数据根据 id 奇偶分配到这两张表中。
创建两张新表
-- 表一
DROP TABLE IF EXISTS tb_member1;
CREATE TABLE tb_member1 (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age TINYINT NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- 表二
DROP TABLE IF EXISTS tb_member2;
CREATE TABLE tb_member2 (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age TINYINT NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
数据拆分
将数据拆分到两个表中，id 为偶数的数据存入 tb_member1，id 为奇数的数据存入 tb_member2。
INSERT INTO tb_member1 (id, name, age)
SELECT id, name, age FROM tb_member WHERE id % 2 = 0;
INSERT INTO tb_member2 (id, name, age)
SELECT id, name, age FROM tb_member WHERE id % 2 = 1;
三、分页查询
为了实现跨表的分页查询，我们使用 MERGE 存储引擎，将两张表的查询结果合并到一个虚拟表 tb_member_all 中，便于统一查询。
创建合并表 tb_member_all
DROP TABLE IF EXISTS tb_member_all;
CREATE TABLE tb_member_all (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age TINYINT NOT NULL DEFAULT '0'
) ENGINE=MERGE UNION=(tb_member1, tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1;
说明：tb_member_all 表实际上并不存储任何数据，而是通过 tb_member1 和 tb_member2 中的数据进行查询。在操作时可以将其看作一个逻辑上的表。
分页查询示例
在 tb_member_all 表中进行分页查询，查询第 2 页（从第 11 条开始）的 10 条数据。
SELECT * FROM tb_member_all ORDER BY id LIMIT 10 OFFSET 10;
删除数据示例
从 tb_member_all 中删除 id = 11 的数据，操作会影响原始分表的数据。
DELETE FROM tb_member_all WHERE id = 11;
四、总结
通过上述演示，我们完成了以下几个操作：
- 创建测试数据库并插入数据。
- 将数据表拆分为两张表并将数据划分至不同表。
- 使用
MERGE引擎合并多张表，方便进行分页查询和操作。
这个例子演示了最简单的分表和查询方法，非常适合初学者进行实践。