MySQL连接MyISAM文件

Recently, I found myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table, we were talking of 5 TB of data, 23B rows. Not small… I calculated that with something likeinsert into MyISAM_table… select * from Innodb_table… would take about 10 days. The bottleneck was clearly the lack of concurrency on the read part from InnoDB and then the key management for MyISAM. The server has many dozen drives so it was easy to add more concurrency so I kicked off, from a script, insertions into 16 identical MyISAM files for distinct parts of the table. That was much faster and would complete within a day.

Then, while the Innodb extraction was running at a nice pace, I thought about the next phase. My first idea was simply to do insert into MyISAM_table select * from MyISAM_table1 and so on for the 16 files. Since MyISAM are flat files, that should be faster, especially with the keys disabled. At that point, I remembered, from a previous disaster recovery work where a database directory has been wiped out that the MyISAM files have no headers which make them difficult (read almost impossible) to locate on a drive with tools like ext3grep. No headers… that means the first byte of byte of a file is the first byte of the first row… So we should be able to concatenate these files. Let’s see.

[root@test ~]# /service/mysql5.5/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.25-log ZWC

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@(none) 03:17:59>use test
Database changed
root@test 03:18:02>create table test_concat(id int unsigned not null, primary key (id)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

root@test 03:18:11>create table test_concat_part like test_concat;
Query OK, 0 rows affected (0.02 sec)

root@test 03:18:28>insert into test_concat(id) value(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@test 03:19:05>insert into test_concat_part(id) value(4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@test 03:19:26>flush tables;
Query OK, 0 rows affected (0.00 sec)

root@test 03:19:29>

[root@test test]# ls
test_concat.frm  test_concat.MYD  test_concat.MYI  test_concat_part.frm  test_concat_part.MYD  test_concat_part.MYI
[root@test test]# cat test_concat_part.MYD >> test_concat.MYD
[root@test test]# /service/mysql5.5/bin/myisamchk -rq test_concat
- check record delete-chain
- recovering (with sort) MyISAM-table 'test_concat'
Data records: 3
- Fixing index 1
Data records: 6
[root@test test]# 

root@test 03:32:13>flush tables;
Query OK, 0 rows affected (0.00 sec)

root@test 03:32:16>select * from test_concat;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

So, yes, you can concatenate MyISAM files, even when multiple keys are defined.  Not for everyday use but still pretty cool.