作者:上村崇 フリーランスの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]セクションを以下のようにしました。

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

3. InnoDBからMyISAMに変更

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

関連記事


Postfixメールサーバー:送信不可のユーザーを設定する
メールサーバーPostfixにて、送受信ができないユーザーを設定をする方法です。Linux、debian環境です。特定のメールアドレスを送受信不可にします。メールを許可しないLinuxユーザーを作るときに有効です。

Web開発・Web制作のツールや用語を初心者向けに大量紹介。これだけ知っておけば安心。
Webページ製作やWebのシステム開発ではいろんなツールやテクニック、用語を使いますが、全体像がなかなか掴みにくいと思ったので、初心者向けに用語をたくさんまとめました。これさえ押さえておけば業界の常識用語はだいたい網羅できると思います。

KVM仮想マシンをWebからアクセスできるKimchiをdebianにインストール
自宅サーバーにてKVMの仮想マシンを動かしていますが、外からでもリモートで操作できるようにkimchiを導入しました。 GitHub – kimchi-project/kimchi: An HTML5 man …

Linuxの権限について解説。setuidとかsetgidとかスティッキービットとか
setuidとかsetgidとかスティッキービットの解説です。Linuxのこれらの機能については考え方が難しいですが、setuidは「一般ユーザーがroot権限で動かすことができる仕組み」です。setgidはそのグループ版です。詳細なメカニズムを解説します。

Raspberry Pi を買ってLチカ(LEDライトを点灯)するまで
Raspberry Piを買ってからLチカ(LEDライトを点灯)するまでのノウハウを書きます。 買ったもの Raspberry Pi 3 Model B+ ヤフオクで4400円で買いました。定価は5300円くらい。 Ra …

コメントを残す

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