mysql innodb分表

基础概念

MySQL InnoDB分表是一种数据库优化技术，用于处理大规模数据和高并发访问的情况。通过将一个大表拆分成多个小表，可以提高查询性能、减少锁竞争，并提升数据库的整体性能。

类型

垂直分表：根据字段的访问频率和业务逻辑，将不同的字段拆分到不同的表中。例如，将不常用的字段或大字段（如BLOB、TEXT）拆分到单独的表中。
水平分表：根据某种规则（如范围、哈希等），将数据行分散到多个表中。每个表的结构相同，但存储的数据行不同。

应用场景

数据量巨大：当单个表的数据量达到一定规模时，查询和写入性能会受到影响，此时可以考虑分表。
高并发访问：在高并发场景下，为了减少锁竞争和提高系统吞吐量，可以采用分表技术。
业务需求：某些业务场景下，需要根据特定的规则对数据进行划分和管理，分表可以满足这些需求。

遇到的问题及解决方法

数据一致性：分表后，需要确保跨表的数据一致性。可以通过分布式事务、两阶段提交等方式来保证。
跨表查询：分表后，原本的单表查询可能变成跨表查询，需要优化SQL语句或使用中间件来支持跨表查询。
数据迁移：分表后，可能需要进行数据迁移以适应新的表结构。可以使用在线重定义表、双写等方式来实现平滑迁移。

示例代码

以下是一个简单的水平分表示例，使用哈希算法将数据分散到多个表中：

-- 创建主表
CREATE TABLE `user` (
    `id` INT PRIMARY KEY,
    `name` VARCHAR(255),
    `email` VARCHAR(255)
);

-- 创建分表
CREATE TABLE `user_0` LIKE `user`;
CREATE TABLE `user_1` LIKE `user`;
CREATE TABLE `user_2` LIKE `user`;

-- 插入数据时根据ID进行哈希分片
DELIMITER $$
CREATE TRIGGER `trg_before_user_insert`
BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
    DECLARE table_name VARCHAR(255);
    SET table_name = CONCAT('user_', MOD(NEW.id, 3));
    SET NEW.table_name = table_name;
END$$
DELIMITER ;

-- 修改插入语句
INSERT INTO `user` (id, name, email) VALUES (1, 'Alice', 'alice@example.com');