こんにちは。技術チームの孫です。
技術チームの陳小華さんが「SQL雑談」と題して、３月の技術勉強会を開催しました。 「Index」、「NULL問題」、「存在チェック」、「Paging」と「Explain」などを例に挙げて、sql文の注意点と改善方法を解説しました。
先ず、下記の３つのselect文を見てください。
同じ処理に見えますけど、実際には効率が異なります。EXPLAINで一緒に三つのqueryを分析しましょう。
１つ目のselect文の「rows」は62070です。下の2個(「rows」は31035)より取得したレコード数が多いです(効率が悪い)。
結論：Where条件の中で、テーブルのカラムに対して、SQLの関数を使わないように。
次、「Indexの順番」について、例の問題を見ましょう。
テーブルの構造は以下：
CREATE TABLE `index_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT ”,
`password` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_PW` (`username`,`password`)
)
下記の４つselect文の中、Indexが効いていないのは、どれでしょうか？
EXPLAINで分析してみましょう！
答えは４番です。４番目の書き方はindexは効かないです。当たりましたか？
結論：一番左のマッチング特性
Key(c1, c2, c3)
✔key(c1, c2, c3)
✔key(c1, c2)
✔key(c1, c3)
✔key(c1)
✖key(c2)
■ NULL問題
null_testテーブルに４つのレコードが入っています。Null、001、002と003です。
もし下記のqueryを実行すると、何個のデータが返ってきますか？
答えは2個です。nullのデータは引掛けられないです。
結論： スキーマ定義の中、DEFAULT NULL はNG。
■ 存在チェック
上記のqueryは「sync_order_invoice」が存在するかどうかをチェックしています。０以上の場合、「sync_order_invoice」は存在います。
実際にはこのqueryは改善の余地があります。下記のように変えば検索時間が短縮できます。
SELECT SQL_NO_CACHE 1 FROM test WHERE ACTION = “sync_order_invoice” LIMIT 1;
結論： ベースデータタイプとlimit 1を活用する。
■ Paging
100万行のデータをロードしてから、次の20行を取得するqueryです。かなり時間がかかりますが、何かチューニング方法がありますか？
上記のようにinner joinしてからselectのは、検索時間が大幅に短縮できました。大量データのpaging処理の役に立ちそうですね。
■ Explain
Explainのtype、select_type、key、refについても説明しました。
Explainのtype
system：システムテーブル、少量データ、Disk I/O なし；
const：PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速；
eq_ref：JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う；
ref：ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ；
range：インデックスを用いた範囲検索；
index：フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い；
ALL：やめてください；
system > const > eq_ref > ref > range > index > ALL
Explainのselect_type
単一のテーブルの場合：
SIMPLE
サブクエリが絡む場合：
PRIMARY：外部クエリ、一番外のクエリ
SUBQUERY：相関関係の無いサブクエリ
DEPENDENT SUBQUERY：相関関係のあるサブクエリ
UNCACHEABLE SUBQUERY：実行する度に結果が変わる可能性のあるサブクエリ
DERIVED： FROM句で用いられているサブクエリ
Explainのkey
possible_keys ： optimizerがテーブルのアクセスに利用可能だと判断したインデックス；
key ：実際にoptimizerによって使用されたキー ；
key_len：選択されたキーの長さ。長さは短いほうが高速。
Explainのref
検索条件でkeyと比較されている値やカラムの種類。
定数の場合： const
JOINを使用している場合：結合する相手側のテーブルで検索条件として利用されているカラムが表示される。
■ レコードのバージョン管理
最後に陳さんがレコードのバージョン管理について説明をしました。
DB設計時に「version」カラムを入れることを勧められました。
データそのものに対してロックは行わずに、更新対象のデータがデータ取得時と同じ状態であることを確認してから更新することで、データの整合性を保証する方式です。楽観ロックを使用する場合は、更新対象のデータがデータ取得時と同じ状態であることを判断するために、Versionを管理するためのカラム(Versionカラム)を用意します。更新時の条件として、データ取得時のVersionとデータ更新時のVersionを同じとすることで、データの整合性を保証することができます。
■ 感想
DBチューニングはシステムの最適なパフォーマンスを実現するための不可欠な部分です。日々増加するデータに対し、レスポンスの遅延なく処理できるのは大事です。
