数据量过大问题——分表

背景：底层硬件传输数据量很大，几秒中传输一次数据；而目前的数据库架构是所有的硬件数据都存放在一张表中。

这种数据库的设计下，在一些小数据量的项目中问题不大。但对于我目前的这个项目来说，每几秒就要传输一次数据，底层硬件的数量还不少，没几天数据量就要成千上百万了！还存放在同一张表中，这种查询的速度可想而知。

大体思路

第一种方式：数据库分表：

1. 首先，获取当前的日期，每到第二天时自动创建新表，将逻辑表的数据换到新表中，只留下一个逻辑表存放每天的数据，即新表是以前的历史表；
2. 生成新表前，先判断是否存在逻辑表，存在逻辑表则生成新表的名字为：逻辑表名 + 日期；
3. 根据逻辑表的结构，创建新表，新表内存储着前一天的所有数据。

简单来说，就是我们维持一个逻辑表，平时对当日的数据查询只查这个逻辑表；只有历史查询的时候才会多表联查以前的历史表。每天自动将现有的逻辑表改名成历史表名称，然后创建相同表结构的逻辑表（这个新逻辑表为空）。

具体实现：

1. 第一步：获取当前日期。

		-- 获取当前日期SET @suffix = DATE_FORMAT(DATE_SUB(CURDATE(),interval-1 day),'%Y%m%d');

2. 第二步：检查旧表（逻辑表）是否存在，存在则重命名这个旧表为 逻辑表名 + 日期。

-- 检查旧表是否存在，如果存在，则重命名为new_table_oldIF EXISTS (SELECT * FROM information_schema.tables WHERE table_schema = 'mmo'AND table_name = 'new_table')THEN-- 将当前日期与表名拼接，并创建新表SET @old_table_name = 'new_table';SET @new_table_name = CONCAT('new_table_', @suffix);-- 使用动态SQL修改表名SET @sql = CONCAT('ALTER TABLE ', @old_table_name, ' RENAME TO ', @new_table_name);PREPARE stmt FROM @sql;EXECUTE stmt;END IF;

3. 第三步：创建新的逻辑表。

		-- 然后再创建新表CREATE TABLE IF NOT EXISTS new_table (id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL,email VARCHAR(50) NOT NULL);

综合上述步骤，可以汇总成下列函数过程：

BEGIN-- 获取当前日期SET @suffix = DATE_FORMAT(DATE_SUB(CURDATE(),interval-1 day),'%Y%m%d');-- 检查旧表是否存在，如果存在，则重命名为new_table_oldIF EXISTS (SELECT * FROM information_schema.tables WHERE table_schema = 'mmo'AND table_name = 'new_table')THEN-- 将当前日期与表名拼接，并创建新表SET @old_table_name = 'new_table';SET @new_table_name = CONCAT('new_table_', @suffix);-- 使用动态SQL修改表名SET @sql = CONCAT('ALTER TABLE ', @old_table_name, ' RENAME TO ', @new_table_name);PREPARE stmt FROM @sql;EXECUTE stmt;END IF;-- 然后再创建新表CREATE TABLE IF NOT EXISTS new_table (id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL,email VARCHAR(50) NOT NULL);
END

为了每天能够自动创建新表，我们可以在navicate中创建一个事件，每日自动调用事件执行函数即可。

一些补充：

由于项目中需要根据每天的时间修改表名，所以普通的 ALTER 语句不能根据变量名来修改表名

因为存在表名要和时间变量拼接的问题，我在这里使用了 动态sql语句 来实现拼接功能。上面的案例只是简单的实现，在实际开发中还涉及到sql注入问题、修改的新表名添加到数据库表中等相关实现，需要在项目开发中根据实际情况加以判断和修改。