mysql分表分库后如何查询

基础概念

MySQL分表分库是一种数据库优化策略，用于解决单表数据量过大、查询效率低下等问题。分表是将一个大表拆分成多个小表，分库是将数据分散到多个数据库实例中。分表分库可以提高查询性能、扩展性和维护性。

类型

垂直分表：根据字段的访问频率和业务逻辑，将不同的字段拆分到不同的表中。
水平分表：根据某种规则（如范围、哈希等），将同一表的数据拆分到多个表中。
垂直分库：根据业务模块或功能，将不同的表拆分到不同的数据库中。
水平分库：根据某种规则（如范围、哈希等），将同一数据库中的数据拆分到多个数据库中。

应用场景

大数据量：当单表数据量过大时，查询效率会显著下降，此时可以考虑分表分库。
高并发：在高并发场景下，分表分库可以提高系统的吞吐量和响应速度。
业务复杂：当业务逻辑复杂，需要频繁进行跨表查询时，分表分库可以简化查询逻辑。

查询方法

1. 垂直分表查询

假设我们有一个用户表user，将其垂直拆分为user_basic和user_detail两个表：

-- user_basic 表
CREATE TABLE user_basic (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

-- user_detail 表
CREATE TABLE user_detail (
    id INT PRIMARY KEY,
    user_id INT,
    address VARCHAR(100),
    phone VARCHAR(20)
);

查询用户信息时，需要联合查询两个表：

SELECT ub.username, ub.email, ud.address, ud.phone
FROM user_basic ub
JOIN user_detail ud ON ub.id = ud.user_id
WHERE ub.id = 1;

2. 水平分表查询

假设我们将用户表user水平拆分为多个表user_0, user_1, user_2等：

-- user_0 表
CREATE TABLE user_0 (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

-- user_1 表
CREATE TABLE user_1 (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

-- user_2 表
CREATE TABLE user_2 (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

查询用户信息时，需要先确定用户所在的表，再进行查询：

-- 假设我们有一个函数 get_user_table(user_id) 可以返回用户所在的表名
SET @table_name = (SELECT get_user_table(1));
SELECT * FROM @table_name WHERE id = 1;

3. 垂直分库查询

假设我们将用户表user拆分到两个数据库db_user_basic和db_user_detail中：

-- db_user_basic 数据库中的 user_basic 表
CREATE TABLE user_basic (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

-- db_user_detail 数据库中的 user_detail 表
CREATE TABLE user_detail (
    id INT PRIMARY KEY,
    user_id INT,
    address VARCHAR(100),
    phone VARCHAR(20)
);

查询用户信息时，需要跨数据库联合查询：

SELECT ub.username, ub.email, ud.address, ud.phone
FROM db_user_basic.user_basic ub
JOIN db_user_detail.user_detail ud ON ub.id = ud.user_id
WHERE ub.id = 1;

4. 水平分库查询

假设我们将用户表user水平拆分到多个数据库db_user_0, db_user_1, db_user_2等中：

-- db_user_0 数据库中的 user 表
CREATE TABLE user (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

-- db_user_1 数据库中的 user 表
CREATE TABLE user (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

-- db_user_2 数据库中的 user 表
CREATE TABLE user (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

查询用户信息时，需要先确定用户所在的数据库，再进行查询：

-- 假设我们有一个函数 get_user_database(user_id) 可以返回用户所在的数据库名
SET @database_name = (SELECT get_user_database(1));
SET @query = CONCAT('SELECT * FROM ', @database_name, '.user WHERE id = 1');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;