MySQLのinnoDBとMyISAMの違い

この記事は、12年前に書かれました。

はじめに

ウェブ制作でデータベースといえばMySQLとPostgresのどちらかを使用されていると思います。
MySQLを使用するうえで大事なのがinnoDBとMyISAMの違いです。今回はその辺りの説明をしたいと思います。

ストレージエンジン

innoDBとMyISAMの違いを大まかに言うとストレージエンジンの違いです。
とは言っても何のことか分かりませんよね。
機能的な部分での大きな違いはロックレベルとロールバックでしょうか。
SQLで更新する際、MyISAMはテーブルロックとなりますが、innoDBは行レベルロックとなります。MyISAMは、あるテーブルのあるレコードを更新する際に、書き込み操作が終わるまでは次の更新は待ち状態になるということです。反対にinnoDBは行レベルでロックされるため、該当行以外の更新は可能となります。
また、複数のSQL文で複数のテーブルを更新した場合、どこかでエラーが発生した場合、テーブルが中途半端な状態で不整合となってしまいます。その場合、innoDBにはロールバックという機能があり、更新を無かったことにしてくれます。これをトランザクションと言います。

実務ではどちらを使用するべきか

特段の理由がない限りinnoDBを利用しましょう。私も以前は何も考えずMyISAMを使用していましたが、今ではinnoDBオンリーです。
当然、ロールバック処理などを書かなくてはいけないので、コード量は増えます。ただ、安全な運営には代えられません。

PHPでinnoDBを使用する

では、PHPでinnoDBを使用する場合、どのようなコードを書けばロールバックなどに対応できるのかを簡単に紹介します。
PDOを使用していますが、DBへの接続処理などは割愛しています。

以下のような例を考えてます。

ソースの前後は端折って重要な部分だけ抜粋していますので、このままコピペしても動作しないと思います。あくまで、イメージを掴んでいただくためのものとなります。

まずはMyISAMの場合の処理です。
5行目でSQL文を組み立てています。6行目exuteでクエリ実行となりテーブルが更新されます。
MyISAMの場合は大体このような感じです。

$filename = $_FILES['upload_file']['name']; //ファイル名
$itemname = $_POST['item_name']; //アイテム名

try{
	$sth = $db->prepare( "INSERT INTO `item` ( `itemname`, `filname` ) VALUES ( ".$itemname.",".$filename." );
    $rs = $sth->execute($vals);
} catch(PDOException $e){
	$this->error = $e->getMessage().':fase1';
	throw new Exception($this->error);
}

//ファイル保存
$result = move_uploaded_file($_FILES['upload_image']['tmp_name'],$uploaded_path);
 
if($result){
  $msg = 'アップロード成功：'.$filename;
}else{
   $msg = 'アップロードエラー：'.$_FILES['upload_image']['error'];
   exit;
}

この場合、例えば15行目のif文でエラーが発生した場合どなるでしょうか。
テーブルは既に更新されていますが、画像はアップロードされていません。データの不整合が発生してしまいます。

innoDBを使用するとこの不整合を回避することが可能です。
ではinnoDBのパターンを書いてみます。
先程と違うのは、４，21，25行目です。

$filename = $_FILES['upload_file']['name']; //ファイル名
$itemname = $_POST['item_name']; //アイテム名

$db->beginTransaction(); //追加

try{
	$sth = $db->prepare( "INSERT INTO `item` ( `itemname`, `filname` ) VALUES ( ".$itemname.",".$filename." );
    $rs = $sth->execute($vals);
} catch(PDOException $e){
	$this->error = $e->getMessage().':fase1';
	throw new Exception($this->error);
}

//ファイル保存
$result = move_uploaded_file($_FILES['upload_image']['tmp_name'],$uploaded_path);
 
if($result){
  $msg = 'アップロード成功：'.$filename;
}else{
   $msg = 'アップロードエラー：'.$_FILES['upload_image']['error'];
　　$db->rollBack();　 //追加
　　exit;
}

$db->commit(); //追加

beginTransaction()は、これからトランザクション処理を始めますよという合図です。PDOの関数になります。
17行目のif文でエラーが発生した場合はelseのほうが実行されますが、rollBack()という関数があります。これは、直前のSQL文の発行を無かったことにしてくれます。巻き戻しをしてくれるということですね。
例えば、直前に３つの更新処理を実行していたとすると、３件すべてを巻き戻しで無かったことにしてくれます。UPDATEでもDELETEでも同様です。

では、25行目のcommit()はというと、画像のアップロードも全てOKということであれば、さきほどのSQL発行の更新処理を確定させますよ～という関数になります。
要は、トランザクションが開始されると、commit()という関数が実行されるまでは、テーブルへの更新処理は仮状態というとになります。

まとめ

innoDBとMyISAM、どちらが安全にデータベースを操作できるかお分かりでしょうか。私は、クライアントワークではinnoDBしか使用していません。
データ不整合が発生した場合の追跡調査やテーブルのメンテンナンス処理に時間を掛けたくないからです。
今回紹介したソースコードは、try~catchも適切ではないかもしれませんので、あくまで参考程度にとどめておいてください。