スポンサーリンク

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テストを行った結果

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

10文字のINSERT×1000件
TEXT型インデックスなし4.302011013031秒
VARCHAR(10)インデックスなし4.6347458362579秒
VARCHAR(16383)インデックスなし4.1622998714447秒
VARCHAR(10)PRIMARY KEY4.1339030265808秒
TEXT型TEXT(16383)のインデックスあり4.0906219482422秒

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

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

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

16383文字のINSERT×1000件
TEXT型インデックスなし7.2746560573578秒
VARCHAR(16383)インデックスなし7.849182844162秒
TEXT型TEXT(16383)のインデックスあり7.7437219619751秒

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

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

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

10文字のINSERT×1000件(トランザクションあり)
TEXT型インデックスなし0.13764119148254秒
VARCHAR(10)インデックスなし0.13758301734924秒
VARCHAR(16383)インデックスなし0.14498400688171秒
VARCHAR(10)PRIMARY KEY0.15025591850281秒

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

16383文字のINSERT×1000件(トランザクションあり)
TEXT型インデックスなし1.2210500240326秒
VARCHAR(16383)インデックスなし1.2925131320953秒

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

TEXT型とVARCHAR型で検索テスト

LIKE句で前方一致検索1000回
TEXT型インデックスなし20.842261075974秒
VARCHAR(10)インデックスなし1.8624589443207秒
VARCHAR(16383)インデックスなし27.101673841476秒
VARCHAR(10)PRIMARY KEY0.19202589988708秒
VARCHAR(768)PRIMARY KEY0.21625185012817秒
TEXT型TEXT(16383)のインデックスあり0.22027492523193秒

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

前方一致検索じゃないとインデックスが効かないので、データには「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型だとテーブルの外にデータが作られるためその制限がないとか、どこかで読んだ気がします。

今回のテスト環境

ハードウェア
機種名ACEPC GK3
CPUIntel Celeron J4125
メモリ8GB
ストレージSSD 256GB
ソフトウェア
OSWindows 10 Pro
HTTPサーバーnginx 1.21.6
PHP7.4.27
mariadb10.3.34

今回のテストコード(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)なんて使わんし…。

コメント

タイトルとURLをコピーしました