趣味で開発しているWebアプリで、MySQLがだんだんと肥大化して処理が重くなってきたので、既存コードへの変更をできるだけ少なく手軽に高速化できる方法を考えていたところ、MySQL 8系でもMEMORYストレージエンジンによる高速化手法が使えたので、その際のメモ。
最初から永続性のあるインメモリデータベース(RedisとかMemcached)にするのが王道なのですが。。。何事も最初の設計はとても大事。それはそれとして。
LOAD DATA INTO MEMORYコマンド:
MySQL全体のチューニングとしてInnoDBバッファプールなどを大きい値にしても効果が少なく、MySQL 5.x 時代にあったMEMORYストレージが使えればなぁーと思って調べていたところ、MySQL 8.0.23以降では、LOAD DATA INTO MEMORYコマンドというのが使えるということで、試してみました。
前提条件:
基本的に特定のデータを参照・検索してアプリで表示するという処理だけを高速化したく、対象データの新規作成、変更、削除などはたまにしか発生しない、という状態です。
方法:
- 高速化したい部分だけを格納する
MEMORYストレージエンジンのテーブルを新規に作成する - データをメモリにロードする。最初は手動で。うまくいったらアプリ側で自動的にデータロードする処理を書く。
- アプリ内で対象のテーブル/カラムを見に行くクエリ部分だけを
MEMORYストレージエンジン側に書き換える。
効果:
どのくらい効果が出るかを事前確認するため、まずは手元でテストした結果。レコードが576万件程度のテーブルを対象に、ランダムに10回、検索処理をかけた場合の処理時間の比較。同じクエリーが7.8倍くらい早くなった。
Normal MySQL table search time: 12.24 seconds
MEMORY storage engine table search time: 1.57 seconds
これなら十分に効果がありそうでサーバのメモリにも十分な余裕があったため、実際にアプリ側に組み込んで、体感でもだいぶ早くなりました。
normal MySQLだと2～3秒待ちされる処理が、MOMORY storageだと1秒掛からずに結果が帰ってくるようになりました。
-- MySQLにログイン
-- mysql -u username -p
-- 対象のデータベースを選択
USE データベース名;
-- MEMORYストレージエンジンのテーブルを作成
CREATE TABLE 新しいテーブル名 LIKE 元のテーブル名;
ALTER TABLE 新しいテーブル名 ENGINE=MEMORY;
-- データをメモリにロード
LOAD DATA INTO MEMORY 新しいテーブル名 FROM 元のテーブル名;
