一个简单的示例演示了如何在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;

四、总结

通过上述演示，我们完成了以下几个操作：

1. 创建测试数据库并插入数据。
2. 将数据表拆分为两张表并将数据划分至不同表。
3. 使用 MERGE 引擎合并多张表，方便进行分页查询和操作。

这个例子演示了最简单的分表和查询方法，非常适合初学者进行实践。