MySQL利用MERGE存储引擎来实现分表

最新推荐文章于 2025-05-20 15:40:41 发布

原创最新推荐文章于 2025-05-20 15:40:41 发布 · 1.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何使用MySQL的MERGE存储引擎进行分表操作，通过实例演示将一张包含大量数据的城市信息表拆分为两个子表的过程，并讲解了插入方法及表结构调整技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

- 序言
- 正文

序言

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕，可能就死在那了。分表的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。这里主要讲使用MERGE存储引擎来实现分表。

正文

个人是小白，所以在前人的经验上，把自己踩过的坑填上。在这里博主用的是MySQL5.5，有一张city表，举例把它分成两张表。
首先创建两张子表和city表具有同样的表结构

#查看表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.12 sec)

#创建子表
mysql>CREATE TABLE IF NOT EXISTS `city1` (
->  `ID` int(11) NOT NULL AUTO_INCREMENT,
->  `Name` char(35) NOT NULL,
->  `CountryCode` char(3) NOT NULL,
->  `District` char(20) NOT NULL,
->  `Population` int(11) NOT NULL,
->  PRIMARY KEY (`ID`) ) 
->  ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  

mysql>CREATE TABLE IF NOT EXISTS `city2` (
->  `ID` int(11) NOT NULL AUTO_INCREMENT,
->  `Name` char(35) NOT NULL,
->  `CountryCode` char(3) NOT NULL,
->  `District` char(20) NOT NULL,
->  `Population` int(11) NOT NULL,
->  PRIMARY KEY (`ID`) ) 
->  ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

现在来创建主表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。

#创建主表
mysql>CREATE TABLE IF NOT EXISTS `allcity` (
->  `ID` int(11) NOT NULL AUTO_INCREMENT,
->  `Name` char(35) NOT NULL,
->  `CountryCode` char(3) NOT NULL,
->  `District` char(20) NOT NULL,
->  `Population` int(11) NOT NULL,
->  PRIMARY KEY (`ID`) ) 
->  ENGINE=MERGE UNION=(city1,city2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;

创建主表的时候有个INSERT_METHOD，指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。

现在我需要把现有的city表分为分拆到city1和city2表中。

#把2000条数据插入city1表
mysql>INSERT INTO city1(city1.ID,city1.Name,city1.CountryCode,city1.District,city1.Population) SELECT city.ID,city.Name,city.CountryCode,city.District,city.Population FROM city where city.ID <= 2000;   
#把剩下的数据插入city2表
mysql>INSERT INTO city2(city2.ID,city2.Name,city2.CountryCode,city2.District,city2.Population) SELECT city.ID,city.Name,city.CountryCode,city.District,city.Population FROM city where city.ID >= 2000;

这样我就成功的将一张city表，分成了二个表，这个时候有一个问题，代码中的sql语句怎么办，以前是一张表，现在变成二张表了，代码改动很大，这样带来了很大的工作量，有没有好的办法解决这一点呢？办法是把以前的city表备份一下，然后删除掉，上面的操作中我建立了一个allcity表，只把这个allcity表的表名改成city就行了。