作者:上村崇 フリーランスのIT系エンジニア
twitter:@uemera facebook:uemura

MySQL、MariaDB サブクエリ付きSELECT文SQLを高速化するノウハウ


MariaDBの SELECT(参照系)高速化のために試したことを記録しておきます。
2700万件対象のサブクエリ含むSQLに100秒近くかかっていたのが、10秒を切るところまで高速化できました。
環境: さくらのVPS(SSD200GB、メモリ8GB)、CentOS7、MariaDB 10.3.15

主ににやったこと。
1. index対象のカラムのバイト数削減、EncodingをUS ASCIIに。
2. my.cnfにてグローバル環境設定を行う
3. InnoDBからMyISAMに変更

1. index対象のカラムのバイト数削減、EncodingをUS ASCIIに。

SELECTを高速化するためにindexを定義することは有効ですが、index対象のカラムサイズを節約するとさらに高速化できます。
5倍くらい速くなりました。めちゃ効果あります。

変更前:indexは以下のカラムに対して定義していました。指定するカラムが複数ある複合indexです。

COL名 Type Encoding
colA VARCHAR(2) UTF-8
colB INT
colC VARCHAR(4) UTF-8
colD INT
colE VARCHAR(2) UTF-8

 
 
 
explainでクエリ解析した結果は以下。
key_lenが38になってます。
indexを構成するのに1行あたり38バイト必要になっていることを意味します。

id select_type table type key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL 2000000 Using temporary; Using filesort
2 DERIVED t2 range idx_abcde 38 NULL 2000000 Using index for group-by

VARCHAR(2)で8バイト使うみたいです。INT型は4バイト消費します。
 
 
変更後:これを以下のようにしました。

COL名 Type Encoding
colA CHAR(1) US ASCII
colB TINYINT
colC CHAR(4) US ASCII
colD TINYINT
colE CHAR(2) US ASCII

 
 
explainは以下です。key_lenが9になりました。

id select_type table type key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL 2000000 Using temporary; Using filesort
2 DERIVED t2 range idx_abcde 9 NULL 2000000 Using index for group-by

key_lenを小さくすると、indexで使用する必要バイト数が削減できます。
indexを定義すると、MariaDBはテーブルの領域とは別にindex領域を確保します。これは、紙の本における目次ページのようなものです。目次を載せるには本文とは別に目次用のページが必要ということです。
key_lenが小さいと目次ページが少なくて済みますし、目次を読み込む時間も短くなります。
 
 
key_len削減のために以下の変更をしました。
●INTは4バイト領域(最大値2147483647)ですが、ここまで大きな数値が必要ではないカラムはTINYINT(1バイト、最大値127)で置き換えました。
●VARCHARは定義した文字数プラス終端用の領域(2バイト)が必要ぽいのでCHAR型にして必要領域を削減しました。CHAR型は終端バイトが付きません。
●文字型(Encoding)について、UTF-8は1文字につき3バイトほど使います。これを1バイトしか消費しないUS ASCIIに変更しました。
ただし、日本語などマルチバイト文字が入る場合はASCIIは使えません。

 
 
このような対応はindexを使用する際に有利というだけでなく、indexを使わないフルスキャンにも有効です。
なぜならば、カラムサイズを節約することでテーブルの全体サイズが小さくなり、フルスキャンする対象も小さくなるからです。
 
 
使用したのはこのようなSQL文です。テーブルを5つのカラムでDISTINCTして、さらに特定のカラムでGROUP BYするものです。
サブクエリ外側のSQL文は、一時テーブルに対するクエリになるのでindexが使えず遅くなります。DekaiTableは2700万件、それをDISTINCTしたt1テーブルは200万件あります。

2. my.cnfにてグローバル環境設定を行う

/etc/my.cnf.d/server.cnfの[mysqld]セクションを以下のようにしました。

# 1. 2回目以降のクエリが高速化する。default:32M
query_cache_size         = 320M

# 2. コネクションのたびに毎回スレッドを作るオーバーヘッドを減らす。default:8
thread_cache_size        = 100

# 3. MyISAMでソート時に使うバッファサイズ。default:64M
myisam_sort_buffer_size  = 512M

# 4. インデックスを使わないフルスキャン時に利用。default:2M
read_buffer_size         = 1000M

# 5. インデックスブロック用に使用されるバッファメモリ default:402M
key_buffer_size          = 2000M

# 6. クエリキャッシュ最大サイズ default:1M
query_cache_limit        = 32M

# 7. tmp_table_sizeのaliasらしい。MariaDB固有の値。default:16M
tmp_memory_table_size    = 1000M

# 8. 実行時に確保可能な一時テーブル用のメモリサイズ。default:16M
tmp_table_size           = 1000M

# 9. MEMORYテーブルを使う場合の最大サイズを指定。default:16M
max_heap_table_size      = 2000M

# 10. インデックスを使わない結合に利用される。default:262K
join_buffer_size         = 512M

グローバル設定はあんまり良くわかってないまま変更したので、改善の余地はまだあると思います。
今回効いてそうなのはtmp_table_sizeです。上記SQLはサブクエリで一時テーブルを作っているはずですので、このバッファを有効に使っていると思われます。
 
 

3. InnoDBからMyISAMに変更

MariaDBのデフォルトエンジンはInnoDBですけど、更新が発生しない検索・参照メインのテーブルだとMyISAMの方が高速です。MyISAMに変更して3割くらいSELECTが速くなりました。
ちなみに、MariaDBにはAriaという、MyISAMを洗練させたエンジンがありますが、これはダメでした。index設定しているのに使ってくれない… MyISAMよりも遅くなる結果となりました。
今後に期待したいです。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です