程序博客网 > 衣品好的女明星知乎

利用MRG_MyISAM存储引擎实现分表

来源：互联网 发布：衣品好的女明星知乎 编辑：程序博客网 时间：2024/05/16 11:40

1)MySQL官方文档中的一段话:MERGE存储引擎,也被认识为MRG_MyISAM引擎,是一个相同的可以被当作一个来用的MyISAM表的集合."相同"意味着所有表同样的列和索引信息.你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表.而且,任何或者所有的表可以用myisampack来压缩.

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                             | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>
 CREATE TABLE t_api_access_flow_merge_1 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;


 CREATE TABLE t_api_access_flow_merge_2 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;


 CREATE TABLE t_api_access_flow_merge_3 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;


 CREATE TABLE t_api_access_flow_merge_4 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;

 CREATE TABLE t_api_access_flow_merge_5 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;


 CREATE TABLE t_api_access_flow_merge_6 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;

 CREATE TABLE t_api_access_flow_merge_7 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;


 CREATE TABLE t_api_access_flow_merge_8 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;

 CREATE TABLE t_api_access_flow_merge_9 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;


 CREATE TABLE t_api_access_flow_merge_10 (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MyISAM AUTO_INCREMENT=959904280 DEFAULT CHARSET=utf8;


 CREATE TABLE t_api_access_flow_merge (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access_time` datetime DEFAULT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `device` varchar(32) DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `imei` varchar(100) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `request_url` varchar(128) DEFAULT NULL,
  `success_flag` bit(1) NOT NULL,
  `token` text,
  PRIMARY KEY (`id`),
  KEY `customerId_requestRrl` (`customer_id`,`request_url`),
  KEY `access_time_index` (`access_time`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 UNION=(t_api_access_flow_merge_1,t_api_access_flow_merge_2,t_api_access_flow_merge_3,t_api_access_flow_merge_4,
                                                t_api_access_flow_merge_5,t_api_access_flow_merge_6,
                                                t_api_access_flow_merge_7,t_api_access_flow_merge_8,t_api_access_flow_merge_9,t_api_access_flow_merge_10
                                                ) INSERT_METHOD=LAST AUTO_INCREMENT=1;


iNSERT INTO t_api_access_flow_merge_1  SELECT * FROM `t_api_access_flowchangname201707210144`  where id <=100000000;


iNSERT INTO t_api_access_flow_merge_2  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 100000000  and id<=200000000;


iNSERT INTO t_api_access_flow_merge_3  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 200000000  and id<=300000000;


iNSERT INTO t_api_access_flow_merge_4  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 300000000  and id<=400000000;


iNSERT INTO t_api_access_flow_merge_5  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 400000000  and id<=500000000;


iNSERT INTO t_api_access_flow_merge_6  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 500000000  and id<=600000000;


iNSERT INTO t_api_access_flow_merge_7  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 600000000  and id<=700000000;


iNSERT INTO t_api_access_flow_merge_8  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 700000000  and id<=800000000;


iNSERT INTO t_api_access_flow_merge_9  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 800000000  and id<=900000000;


iNSERT INTO t_api_access_flow_merge_10  SELECT * FROM `t_api_access_flowchangname201707210144`  where id > 900000000   ;

insert into t_api_access_flow_merge  select * from `t_api_access_flowchangname201707210144` ;



mysql>SELECT COUNT(*) FROM  t_api_access_flow_merge_1 ;

+--------------------+
| COUNT(*)           |
+--------------------+
| 100000000          |
+--------------------+
共返回 1 行记录,花费 32775 ms.
mysql>SELECT COUNT(*) FROM  t_api_access_flow_merge_2 ;
+--------------------+
| COUNT(*)           |
+--------------------+
| 99999998           |
+--------------------+
共返回 1 行记录,花费 23939 ms.
mysql>SELECT COUNT(*) FROM  t_api_access_flow_merge_10 ;
+--------------------+
| COUNT(*)           |
+--------------------+
| 59904279           |
+--------------------+
共返回 1 行记录,花费 19950 ms.









阅读全文
0 0
衣品好的女明星知乎 衣品好的女明星知乎
原创粉丝点击
热门IT博客
可以写日记的软件可以写日记的软件
linux常用命令面试linux常用命令面试
网络诈骗怎么报警电话
如何安装linux mint
vb中debug是什么意思
网络上传速度怎么解决
什么可以传软件
劫持刘嘉玲的是谁知乎
淘宝购买魔术道具
关联规则挖掘算法
最好的同声翻译软件
cf手游刷枪软件绿色版
知乎日报 吐槽精选
有限元优化软件 比较
梦幻西游玩着网络错误
单片机蜂鸣器简单音乐
js如何创建对象
网络协议抽象成软件
陶瓷信 知乎
华为java开发规范
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 肉灵芝是什么 肉芝 肉灵芝 太歲 女记者肉灵芝太岁 天上降魔种人间太岁神 太岳山 太岳 安泽太岳大酒店 沁源太岳国际大酒店 江湖风云录太岳三清风 沁源太岳国际酒店 盛唐太师 灭绝师太 妙真师太 庞太师 风陵师太为什么杀郭襄 董卓太师椅干伏皇后 师太 太师 风陵师太 闻太师 五枚师太 庞太师霸占穆桂英 大唐少年太师 太子太师成语答案 定逸师太 我当师太的那些年 我当师太那些年 大唐之少年太师 灭绝师太图片 大唐太师 师妹太妖娆 灭绝师太什么意思 家有仙师太妖娆 太师椅图片 庞太师虐杨门女将 师太你就从了老衲吧 第章品尝灭绝师太 老爷破嫩瓜绑太师椅 方世玉25岁被师太掏肛而死

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里