概述
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.
最后
以上就是粗犷路人为你收集整理的利用MRG_MyISAM存储引擎实现分表的全部内容,希望文章能够帮你解决利用MRG_MyISAM存储引擎实现分表所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错,欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复