mysql 分表策略

基础概念

MySQL分表策略是一种数据库优化技术，用于解决单个数据库表数据量过大导致的性能瓶颈问题。通过将一个大表拆分成多个小表，可以提高查询效率、减少单表数据量，从而提升数据库的整体性能。

优势

提高查询效率：分表后，查询操作可以分散到多个小表上，减少了单个表的查询压力。
减少单表数据量：通过分表，可以有效控制每个表的数据量，避免单表数据量过大导致的性能问题。
便于数据维护：分表后，可以对每个小表进行独立的数据维护操作，如备份、恢复等。

类型

垂直分表：根据字段的访问频率和业务逻辑，将不同的字段拆分到不同的表中。通常将高频访问的字段放在一张表中，低频访问的字段放在另一张表中。
水平分表：根据某种规则（如范围、哈希等），将同一个表的数据拆分到多个表中。每个表的结构相同，但存储的数据不同。

应用场景

数据量巨大的表：当单个表的数据量达到一定程度时，查询、插入、更新等操作的性能会显著下降，此时可以考虑使用分表策略。
高频访问的表：对于经常需要被查询的表，通过分表可以分散查询压力，提高查询效率。
业务逻辑复杂的表：当表中的字段较多，且不同字段的访问频率和业务逻辑差异较大时，可以考虑使用垂直分表策略。

常见问题及解决方法

数据一致性问题：在分表后，需要确保跨表的数据一致性。可以通过使用分布式事务、最终一致性等方案来解决。
跨表查询问题：分表后，原本的单表查询可能变为跨表查询，这会增加查询的复杂性。可以通过使用中间件（如MyCat、ShardingSphere等）来实现跨表查询，或者在应用层进行多次查询并合并结果。
分片规则选择问题：选择合适的分片规则对于分表的性能至关重要。需要根据业务特点和数据分布情况来选择合适的分片算法，如范围分片、哈希分片等。

示例代码（水平分表）

假设我们有一个用户表user，结构如下：

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

我们可以根据用户的ID范围进行水平分表，例如将ID在1-10000的用户数据存储在user_1表中，ID在10001-20000的用户数据存储在user_2表中，以此类推。

CREATE TABLE user_1 LIKE user;
CREATE TABLE user_2 LIKE user;
-- ... 创建更多分表

-- 插入数据时根据ID选择对应的分表
DELIMITER //
CREATE PROCEDURE insert_user(IN p_id INT, IN p_name VARCHAR(50), IN p_age INT, IN p_email VARCHAR(100))
BEGIN
    DECLARE table_name VARCHAR(20);
    SET table_name = CONCAT('user_', FLOOR((p_id - 1) / 10000) + 1);
    SET @sql = CONCAT('INSERT INTO ', table_name, ' (id, name, age, email) VALUES (', p_id, ', ''', p_name, ''', ', p_age, ', ''', p_email, ''')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;