MySQLのTEXT型とVARCHAR型の速度比較

昨日、「SQLiteとMySQLにおける一括更新と逐次更新でパフォーマンス比較をしてみる」と題して簡単なテストを実施しましたが、一括更新ならまだしも、並列処理においてはあまりに圧倒的な差があるため、そういった部分に関しては優先的にSQLiteからMySQLへ切り替えようと計画しています。

そこで、テーブル構造のコンバートをしていてふと思ったのですが、MySQLのTEXT型とVARCHAR型って処理速度に差はあるのでしょうか？

SQLiteではそもそもどんなデータ型でもセットできますし、TEXT型とVARCHAR型に区別がなく、いずれのデータ型でも文字数が500万文字まで入るという自由っぷりです。実は以前よく使っていたPostgreSQLもTEXT型とVARCHAR型の指定はできるものの、内部的にはどちらもほとんど違いがない（≒パフォーマンスにも差がない）と公式文書で読んだ記憶があります。

たぶんMySQLもそうなんだろうなぁーと思いつつも、歴史的にはMySQLは速度を追求してISAM形式にこだわり、トランザクションのサポートをなかなかしなかった過去もあります。InnoDBになってからはたぶん…おそらく…ほかのデータベースシステムと同様、TEXT型とVARCHAR型に有意な差異はないだろうと予想されますが、あとからデータ型変えるのも面倒なので、いまのうちに軽くテストしておこうと思いました。

TEXT型とVARCHAR型でINSERTテストを行った結果

テストコードは後回しにして、先に結果から行きます。

普通、INDEX付きのINSERTはSELECTとは逆に遅くなるハズなのですが、この結果では少し早くなってるので、キャッシュとかコンパイルとか効いてそうです。いずれにせよ、誤差レベル。

VARCHAR(16383)を作っているのは大きな枠に小さなデータ（10文字）をセットしたときにパフォーマンスが変わるかな？と疑問に思ったからですが、これも特に変わりませんでした。

ちなみに16383文字にしてるのは文字コードを utf8mb4 に設定したときのVARCHAR型の最大文字数が16383文字だからです。正確には65535バイトが最大値ですが、1文字あたり4バイト使うため65535÷4=16383.75となるわけですね。

普通、INDEX付きのINSERTのほうが遅くなるはずですが以下略

TEXT型とVARCHAR型でINSERTテストを行った結果（トランザクションあり）

続いてBEGIN～COMMITのトランザクションを付けたパターン。

やはりトランザクション内で一括更新したほうがかなり早いですねー。インデックス付けたVARCHAR(10)がやや遅いですが、それはまぁ当たり前かなと。

うーん、微妙な差があるようにも見えますが、0.07秒を差と言って良いのかどうか…。

TEXT型とVARCHAR型で検索テスト

お、ここではわかりやすい差が出ましたね。

前方一致検索じゃないとインデックスが効かないので、データには「AAAAA...(以下略)」をセットし、検索語句は（ヒットしないように）「B%」としているのですが、インデックスなしのTEXT型だと20.8秒、VARCHAR(16383)だと27.1秒でした。がしかし、同じ条件でVARCHAR(10)だと1.8秒です。これは最大値が10文字だと総なめしてもすぐに済むからでしょうねー。

それに比べてインデックス付きのvarchar(10)は0.19秒、varchar(768)は0.21秒、TEXT(16383)型も0.22秒とかなり早いです。

varchar(768)という中途半端な数値はこれまたMySQLの制限で、キーの最大長が3072バイトだからです。utf8mb4は1文字4バイトなので、3072÷4=768文字が最大だったんですね。

ところが、理由はよくわかりませんがTEXT/BLOB型の場合はキー長が16383でもインデックス作成できたのでそのようにしています。

CREATE INDEX test_text_idx_idx ON test_text_idx (c1(16383))

たしかMySQLはレコードの最大サイズが65535バイトと決まっているんだったかな？　その関係でキーの最大長も短いのですが、TEXT型だとテーブルの外にデータが作られるためその制限がないとか、どこかで読んだ気がします。

今回のテスト環境

今回のテストコード(PHP)

<?php
//MySQL用
$cfg['PDO_DSN'] = 'mysql:host=localhost;dbname=test;';
$cfg['PDO_USER'] = '[MySQLユーザー]';
$cfg['PDO_PASS'] = '[MySQLパスワード]';
$cfg['MAX_COUNT'] = 1000;
set_time_limit(120);
set_exception_handler('myExceptionHandler');

$db = new PDO($cfg['PDO_DSN'], $cfg['PDO_USER'], $cfg['PDO_PASS'], array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_TIMEOUT => 60));

//テーブル作成
$db->query('DROP TABLE IF EXISTS test_text');
$db->query('DROP TABLE IF EXISTS test_varchar_10');
$db->query('DROP TABLE IF EXISTS test_varchar_16383');
$db->query('DROP TABLE IF EXISTS test_varchar_key_10');
$db->query('DROP TABLE IF EXISTS test_varchar_key_3072');
$db->query('DROP TABLE IF EXISTS test_text_idx');
$db->query('CREATE TABLE IF NOT EXISTS test_text (c1 TEXT) COLLATE utf8mb4_bin');
$db->query('CREATE TABLE IF NOT EXISTS test_varchar_10 (c1 VARCHAR(10)) COLLATE utf8mb4_bin');
$db->query('CREATE TABLE IF NOT EXISTS test_varchar_16383 (c1 VARCHAR(16383)) COLLATE utf8mb4_bin');
$db->query('CREATE TABLE IF NOT EXISTS test_varchar_key_10 (c1 VARCHAR(10) PRIMARY KEY) COLLATE utf8mb4_bin');
$db->query('CREATE TABLE IF NOT EXISTS test_text_idx (c1 TEXT) COLLATE utf8mb4_bin');
$db->query('CREATE TABLE IF NOT EXISTS test_varchar_key_768 (c1 VARCHAR(768) PRIMARY KEY) COLLATE utf8mb4_bin');
$db->query('CREATE INDEX test_text_idx_idx ON test_text_idx (c1(16383))');

$data1 = str_repeat('A', 10);
$data2 = str_repeat('A', 16383);

echo '■10文字<br>';
insertData('test_text', $data1, $cfg['MAX_COUNT']);
insertData('test_varchar_10', $data1, $cfg['MAX_COUNT']);
insertData('test_varchar_16383', $data1, $cfg['MAX_COUNT']);
insertDataRandom('test_varchar_key_10', 5, $cfg['MAX_COUNT']);
insertData('test_text_idx', $data1, $cfg['MAX_COUNT']);

echo '■16383文字<br>';
insertData('test_text', $data2, $cfg['MAX_COUNT']);
insertData('test_text_idx', $data2, $cfg['MAX_COUNT']);
insertData('test_varchar_16383', $data2, $cfg['MAX_COUNT']);

echo '■10文字（トランザクションあり）<br>';
insertData('test_text', $data1, $cfg['MAX_COUNT'], true);
insertData('test_varchar_10', $data1, $cfg['MAX_COUNT'], true);
insertData('test_varchar_16383', $data1, $cfg['MAX_COUNT'], true);
insertDataRandom('test_varchar_key_10', 5, $cfg['MAX_COUNT'], true);

echo '■16383文字（トランザクションあり）<br>';
insertData('test_text', $data2, $cfg['MAX_COUNT'], true);
insertData('test_varchar_16383', $data2, $cfg['MAX_COUNT'], true);

echo '■768文字ランダム（トランザクションあり）<br>';
insertDataRandom('test_varchar_key_768', (768/2), $cfg['MAX_COUNT'], true);

echo '■検索テスト<br>';
selectData('test_text', 'B%', $cfg['MAX_COUNT'], true);
selectData('test_varchar_10', 'B%', $cfg['MAX_COUNT'], true);
selectData('test_varchar_16383', 'B%', $cfg['MAX_COUNT'], true);
selectData('test_varchar_key_10', 'B%', $cfg['MAX_COUNT'], true);
selectData('test_varchar_key_768', 'B%', $cfg['MAX_COUNT'], true);
selectData('test_text_idx', 'B%', $cfg['MAX_COUNT'], true);

function insertData($tablename, $test_data, $max_count=1, $transaction=false)
{
	global $db;
	$st = $db->prepare('INSERT INTO '.$tablename.' (c1) VALUES (?)');
	$t1 = microtime(true);
	if ($transaction) $db->query('BEGIN');
	for ($i = 1; $i <= $max_count; $i++) {
		$st->execute(array($test_data));
	}
	echo $tablename.":".(microtime(true) - $t1)." sec.<br>";
	if ($transaction) $db->query('COMMIT');
}
function insertDataRandom($tablename, $max_length, $max_count=1, $transaction=false)
{
	global $db;
	$st = $db->prepare('INSERT INTO '.$tablename.' (c1) VALUES (?)');
	$t1 = microtime(true);
	if ($transaction) $db->query('BEGIN');
	for ($i = 1; $i <= $max_count; $i++) {
		$test_data = bin2hex(openssl_random_pseudo_bytes($max_length));
		$st->execute(array($test_data));
	}
	echo $tablename.":".(microtime(true) - $t1)." sec.<br>";
	if ($transaction) $db->query('COMMIT');
}

function selectData($tablename, $test_data, $max_count=1)
{
	global $db;
	$st = $db->prepare('SELECT * FROM '.$tablename.' WHERE c1 LIKE ?');
	$t1 = microtime(true);
	for ($i = 1; $i <= $max_count; $i++) {
		$st->execute(array($test_data));
		while($row = $st->fetch()) {
		}
	}
	echo $tablename.":".(microtime(true) - $t1)." sec.<br>";
}
function myExceptionHandler($e)
{
	echo $e;
	exit();
}
?>

まとめ

データベースエンジニアからしたらツッコミどころ満載のテストかと思いますが、「だいたいわかれば良いんだよ！だいたいわかれば！」というレベルであればこんなんで十分じゃないかな…。

結論をざっくりまとめるとこんな感じでしょうか。

• INSERT処理においてTEXT型とVARCHAR型に有意な速度差は見られない
• SELECT処理でインデックスが付いている場合も有意な速度差は確認できない
• 但し、インデックスのないVARCHAR型でデータ長が長い項目はTEXT型よりも明確に遅い

この結果から文字列は全部TEXT型で良いんじゃねーかな、とも思いましたが、キー作成がちょっと特殊^※になるので、やはりキーとして使う項目についてはVARCHAR型で良いのかなと思います。

以上、あんまり参考にならない気もしますが、個人的にはTEXT型とVARCHAR型に明確な速度差がないことがわかって安心しました。そもそもVARCHAR(16383)なんて使わんし…。