図1●データファイルのフラグメンテーション(MyISAM)
図1●データファイルのフラグメンテーション(MyISAM)
[画像のクリックで拡大表示]

  随分前から噂されていた日本法人MySQL株式会社が2月,ついに設立された。米Oracleによる買収騒動など,MySQLは台風の目になりつつある。技術解説は,前回に引続きMYSQLDUMPが出力するバックアップ・ファイルの便利な使用法を取り上げる。

バックアップ・ファイルの内容

 MySQLで基本的なバックアップツールであるMYSQLDUMPが出力するファイルは,多くの用途に使用できる。

 MYSQLDUMPは,次のような内容のファイルを作成している。最初と最後にコメントおよび実行時のServer環境変数が記述されている。そして,各テーブルの削除と作成とデータの追加を行うようになっている。

リスト1●MYSQLDUMPが作成したバックアップ・ファイル

1.MySQL dump 10.10
2.--
3.-- Host: localhost    Database: databank
4.------------------------------------------------------
5.-- Server version	5.0.12-beta-nt-log

6./*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
7./*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
8./*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
9./*!40101 SET NAMES utf8 */;
10./*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
11./*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
12./*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
13./*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
14.--
15.Table structure for table `countrylanguage`
16.--

17.DROP TABLE IF EXISTS `countrylanguage`;
18.CREATE TABLE `countrylanguage` (
19.`Country` char(3) character set utf8 NOT NULL default '',
20.`Language` char(10) character set utf8 NOT NULL default '',
21.`IsOfficial` enum('T','F') character set utf8 NOT NULL default 'F',
22.`Percentage` float(3,1) NOT NULL default '0.0',
23.KEY `FK_countrylanguage_1` (`Country`),
24.CONSTRAINT `FK_countrylanguage_1` FOREIGN KEY (`Country`) REFERENCES `country` (`Code`)
25.) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

26.--
27.Dumping data for table `countrylanguage`
28.--

29./*!40000 ALTER TABLE `countrylanguage` DISABLE KEYS */;
30.LOCK TABLES `countrylanguage` WRITE;
31.INSERT INTO `countrylanguage` VALUES ('AFG','Pashto','T',52.4),('NLD','Dutch','T',95.6),…;
32.UNLOCK TABLES;
33./*!40000 ALTER TABLE `countrylanguage` ENABLE KEYS */;
(以下省略,全体表示

 テーブル「countrylanguage」を例にとると,17行目でテーブルを削除している。次に18行目から25行目で,テーブル「countrylanguage」を作成している。31行目で,テーブルにデータを格納する。このように非常に簡単であり,テキストファイルなので,必要に応じて内容を変更することも容易い。

 データベースに蓄積されるデータは,ファイルとして格納されている。MyISAMとデータベースのフラグメンテーションの解消
データベースに蓄積されるデータは,具体的にはファイルとして格納されている。MyISAMとInnoDBでは,ファイルの構造が異なるがファイルに格納されている点では同じである。

 データベースは,データ(レコード)の削除と追加を繰り返すとファイル内にデータが格納されていない空白の部分が発生する。このような状況をフラグメンテーション(断片化)という(図1)。多少のフラグメンテーションが発生してもまったく問題ないが,数が増えるにつれてスピードの劣化につながる。また,格納しているデータ量よりも,多くの容量を消費することになる。その場合には,フラグメンテーションを解消する必要がある。勿論,レコードが連続して配列されていれば,効率よく処理できる。

 MySQLには,いくつかのフラグメンテーション対象方法が提示されている。しかし,最も確実な解消策は,MYSQLDUMPでバックアップをとり,テーブルを削除して,再構築する。MyISAMは,テーブルごとにファイルを3つ作成するので,即座にフラグメンテーションが解消される。

リスト2●テーブル「city」のフラグメンテーションが発生している

mysql> show table status like 'city' \G
*************************** 1. row ***************************
           Name: city
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3805
 Avg_row_length: 32
    Data_length: 140366  Rows × Avg_row_length より多い
Max_data_length: 281474976710655
   Index_length: 100352
      Data_free: 18358   空き領域がなくなっている
 Auto_increment: 4080
    Create_time: 2006-02-24 23:32:19
    Update_time: 2006-02-24 23:42:51
     Check_time: 2006-02-24 23:32:40
      Collation: ascii_bin
       Checksum: NULL
 Create_options:

 バックアップ・ファイルには,標準でDrop Table ステートメントが記述されている。そのため先にテーブルの削除を行う必要はない。

 次はMYSQLDUMPによるバックアップの作成とバックアップからのテーブル「city」の構築を実施する。

リスト3●MYSQLDUMPによるバックアップ

MYSQLDUMPの実施 MYSQLDUMP --user=root --password --socket -x -F --databases databank --tables city > /tmp/city.dmp

リスト4●バックアップからテーブル「city」の作成

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql < /tmp/city.dmp

リスト5●テーブル「city」のフラグメンテーションを解消

mysql> show table status like 'city' \G
*************************** 1. row ***************************
           Name: city
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3805
 Avg_row_length: 32
    Data_length: 122008  減少している
Max_data_length: 281474976710655
   Index_length: 100352
      Data_free: 0       空き領域がなくなっている
 Auto_increment: 4080
    Create_time: 2006-02-24 23:32:19
    Update_time: 2006-02-24 23:42:51
     Check_time: 2006-02-24 23:32:40
      Collation: ascii_bin
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

 このように,テーブル「city」はすっきりした状態になった。

 但し,InnoDBは,複数のファイルで全体のデータを格納する構造なので,この方法ではファイルサイズは減らない。

 注意が必要なのは,FOREIGN KEY設定されたデータベースの場合だ。