利用MRG_MyISAM存储引擎实现分表

逃离我推掉我的手 2024-02-18 14:18 40阅读 0赞

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.