编程 一个简单的示例演示了如何在MySQL中进行分库分表及分页查询

2024-11-18 22:28:35 +0800 CST views 891

一个简单的示例演示了如何在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_member1tb_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_member1id 为奇数的数据存入 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_member1tb_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 引擎合并多张表,方便进行分页查询和操作。

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

复制全文 生成海报 数据库 MySQL 数据管理 编程 技术教程

推荐文章

阿里云免sdk发送短信代码
2025-01-01 12:22:14 +0800 CST
rangeSlider进度条滑块
2024-11-19 06:49:50 +0800 CST
如何在Rust中使用UUID?
2024-11-19 06:10:59 +0800 CST
Go语言中实现RSA加密与解密
2024-11-18 01:49:30 +0800 CST
纯CSS绘制iPhoneX的外观
2024-11-19 06:39:43 +0800 CST
Golang中国地址生成扩展包
2024-11-19 06:01:16 +0800 CST
gin整合go-assets进行打包模版文件
2024-11-18 09:48:51 +0800 CST
一些实用的前端开发工具网站
2024-11-18 14:30:55 +0800 CST
Vue3中的组件通信方式有哪些?
2024-11-17 04:17:57 +0800 CST
在Vue3中实现代码分割和懒加载
2024-11-17 06:18:00 +0800 CST
使用 node-ssh 实现自动化部署
2024-11-18 20:06:21 +0800 CST
Vue3中如何处理异步操作?
2024-11-19 04:06:07 +0800 CST
使用 Go Embed
2024-11-19 02:54:20 +0800 CST
三种高效获取图标资源的平台
2024-11-18 18:18:19 +0800 CST
使用xshell上传和下载文件
2024-11-18 12:55:11 +0800 CST
PHP openssl 生成公私钥匙
2024-11-17 05:00:37 +0800 CST
PHP 压缩包脚本功能说明
2024-11-19 03:35:29 +0800 CST
程序员茄子在线接单