はじめに

最近、MyISAMのこと調べる機会が多かったので、今までの人生でMyISAMテーブルを触ることなんてほぼ0だったし、MySQL的には今更な感じもするけどとりあえず書き出してみる。

(殴り書きに近い。あとで調べるときにとっかかりになればと思ってる)

なので、後日付け足していける感じの自分用のメモ扱い。

基本的にドキュメントは5.6以降大きな変更無いので、5.6ベースのもののリンクを貼っとく。

MyISAMとは

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2 MyISAM ストレージエンジン

MySQL5.5以降はInnoDBがデフォルトのストレージエンジン

MyISAM 起動オプション

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.1 MyISAM 起動オプション

キーに必要な容量

インデックスファイルのサイズは、キーを (key_length+4)/0.67 と計算し、すべてのキーに対してその値を合計して概算できます。
これは、すべてのキーがソート順に挿入され、テーブル内のキーが圧縮されていなときの最悪なケースです。

MyISAM テーブルでは、テーブルの作成時に PACK_KEYS=1 テーブルオプションを指定することで、数値のプリフィクスを圧縮することもできます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.2 キーに必要な容量

MyISAM テーブルのストレージフォーマット

以下の３つがある

静的 (固定長) テーブル
動的テーブル
圧縮テーブル

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.3 MyISAM テーブルのストレージフォーマット

OPTIMIZE TABLEで再編成

MyISAM テーブルの場合、OPTIMIZE TABLE は次のように機能します。

* テーブルが行を削除または分割した場合は、そのテーブルを修復します。
* インデックスページがソートされていない場合は、それをソートします。
* テーブルの統計が最新でない (そのため、インデックスのソートによって修復を実行できない) 場合は、それを更新します。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.4 OPTIMIZE TABLE 構文

myisamchk -r でも可。

MyISAM テーブルの破損が起きるとき

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.4.1 MyISAM テーブルの破損

適切に閉じられなかったテーブルの問題

MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.4.2 適切に閉じられなかったテーブルの問題

MyISAM テーブルの保守とクラッシュリカバリ

MySQL :: MySQL 5.6 リファレンスマニュアル :: 7.6 MyISAM テーブルの保守とクラッシュリカバリ

MyISAM テーブルの保守は、myisamchk が実行するものと似た操作を実行する SQL ステートメントを使用しても実行できます。
* MyISAM テーブルをチェックするには、CHECK TABLE を使用します。
* MyISAM テーブルを修復するには、REPAIR TABLE を使用します。
* MyISAM テーブルを最適化するには、OPTIMIZE TABLE を使用します。
* MyISAM テーブルを分析するには、ANALYZE TABLE を使用します。

MyISAMテーブルの修復

REPAIR TABLE を実行する。

myisamchk --recover tbl_nameも可

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.5 REPAIR TABLE 構文

myisamchk — MyISAM テーブルメンテナンスユーティリティー

その他MyISAM関連のユーティリティプログラム

myisam_ftdump — 全文インデックス情報の表示

myisamlog — MyISAM ログファイルの内容の表示

myisampack — 圧縮された読み取り専用の MyISAM テーブルの生成

mysqlhotcopy — データベースバックアッププログラム

バックアップ(というかテーブルコピーメイン)

自分の過去の記事↓

MySQLでのテーブルコピー（InnoDBとMyISAM） - 41から始めました

mysqldumpでのバックアップの場合

--lock-tablesまたは -lを使ってダンプするすべてのテーブルをダンプ前にロック（READ LOCK）する。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

DELETE構文

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.2 DELETE 構文

MyISAMテーブルの項目を参照

DELETE ステートメントの速度

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.2.3 DELETE ステートメントの速度

MyISAM テーブル内の個々の行を削除するために必要な時間は、インデックスの数に正確に比例します。
行をもっと速く削除するには、key_buffer_size システム変数を増やして、キーキャッシュのサイズを大きくできます。

MyISAM テーブルからすべての行を削除するには、TRUNCATE TABLE tbl_name の方が DELETE FROM tbl_name より速くなります。
切り捨て操作はトランザクションセーフではありません。
アクティブなトランザクションやアクティブなテーブルロックの途中で試みるとエラーが発生します。

MyISAMからInnoDBへ

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.4 MyISAM から InnoDB へのテーブルの変換

漢(オトコ)のコンピュータ道: MyISAMからInnoDBへ切り替えるときの注意点

大きな違いはロッキングとデータの堅牢性。

MyISAMはテーブルロック、原子性の非担保による壊れやすさ。

MyISAMの最適化

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.6 MyISAM テーブルの最適化　

パラメータ一覧

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

key_buffer_size

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

値を増やすことで、すべての読み取りおよび複数の書き込みのインデックス処理を改善できます。
システムの主な機能が MyISAM ストレージエンジンを使用して MySQL を実行する場合、マシンの合計メモリーの 25% がこの変数の許容可能な値です。
ただし、値を大きくし過ぎると (マシンの合計メモリーの 50% 超)、システムのページングが始まってきわめて低速になることがあります。
これは MySQL がデータ読み取りのためのファイルシステムキャッシュの実行をオペレーティングシステムに依存しているためで、ファイルシステムキャッシュのためにいくらかの空きを残しておく必要があります。
また、MyISAM に追加して使用するほかのストレージエンジンのメモリー要件も考慮します。

MyISAMテーブルの最大サイズ

MySQL :: MySQL 5.6 リファレンスマニュアル :: D.10.3 テーブルサイズの制限

これ調べてみたら、可変長カラムを含む場合のみっぽい。

カラムが固定長オンリーの場合はこの限界サイズを超えて、可変長カラムの場合の約カラム長倍になるという。

詳しく知りたい人はmi_create.ccあたりを読んでみるといいです。

GTIDとMyISAM

混ぜるな危険。

クラッシュ時はソースとレプリカの整合性が崩れてレプリケーションが停止する。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.3.4 GTID ベースレプリケーションの制約

 GTID を使用する場合、MyISAM などの非トランザクションストレージエンジンを使用するテーブルへの更新は、InnoDB などのトランザクションストレージエンジンを使用するテーブルへの更新と同じステートメントまたはトランザクションで実行できません。
この制約は、非トランザクションストレージエンジンを使用するテーブルへの更新とトランザクションストレージエンジンを使用するテーブルへの更新が、同じトランザクション内に混在していると、複数の GTID が同じトランザクションに割り当てられる可能性があるためです。

統計情報について

MyISAMはインデックスのファイルサイズと格納されているレコード数を管理しているだけ→実質的には統計情報と呼べる情報は管理してない。

なぜならMyISAMには、対象のインデックスエントリがそのインデックス内で何番目のレコードかということを即座に返す機能があるため、インデックスの統計情報を維持する必要がないから。

内部一時テーブルストレージエンジン

MySQL5.6だと以下の通り

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.4 MySQL が内部一時テーブルを使用する仕組み

場合により、サーバーはクエリーの処理中に内部一時テーブルを作成します。
それらのテーブルは、メモリー内に保持して、MEMORY ストレージエンジンによって処理したり、ディスク上に格納して、MyISAM ストレージエンジンによって処理したりできます。
サーバーは最初にインメモリーテーブルとして内部で一時テーブルを作成し、それが大きくなりすぎた場合に、それをディスク上テーブルに変換することがあります。

MySQL5.7ではサーバーがディスク上の内部一時テーブルを管理するために使用するストレージエンジンとしてMyISAMがパラメータ（internal_tmp_disk_storage_engine）で指定可能。

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_internal_tmp_disk_storage_engine

しかし、このパラメータ、8.0.16で削除される。理由はInnoDBに固定となったため。

一方で、internal_tmp_mem_storage_engineというパラメータもできる。こちらは名前がややこしいが別物かつエンジンはTempTable or MEMORY)

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

おわりに

他にもMyISAMってこんな特徴、問題あるよ！って話あれば教えてください。

41から始めました

MyISAMってなんだろう？