【大阪 / 横浜】インフラ / サーバーサイドエンジニア募集中!

【大阪 / 横浜】インフラ / サーバーサイドエンジニア募集中!

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始!

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始!

【導入実績 500社以上】AWS 構築・運用保守・監視サービス

【導入実績 500社以上】AWS 構築・運用保守・監視サービス

【CentOS 後継】AlmaLinux OS サーバー構築・移行サービス

【CentOS 後継】AlmaLinux OS サーバー構築・移行サービス

【WordPress 専用】クラウドサーバー『ウェブスピード』

【WordPress 専用】クラウドサーバー『ウェブスピード』

【格安】Webサイト セキュリティ自動診断「クイックスキャナー」

【格安】Webサイト セキュリティ自動診断「クイックスキャナー」

【低コスト】Wasabi オブジェクトストレージ 構築・運用サービス

【低コスト】Wasabi オブジェクトストレージ 構築・運用サービス

【予約システム開発】EDISONE カスタマイズ開発サービス

【予約システム開発】EDISONE カスタマイズ開発サービス

【100URLの登録が0円】Webサイト監視サービス『Appmill』

【100URLの登録が0円】Webサイト監視サービス『Appmill』

【中国現地企業に対応】中国クラウド / サーバー構築・運用保守

【中国現地企業に対応】中国クラウド / サーバー構築・運用保守

【YouTube】ビヨンド公式チャンネル「びよまるチャンネル」

【YouTube】ビヨンド公式チャンネル「びよまるチャンネル」

MySQLで運用中のシステムにSQLチューニングする方法 その2 ~SQL解析~

ビヨンドの原岡です。

前回の内容からの続きになります。

MySQLで運用中のシステムにSQLチューニングする方法~ボトルネックを発見せよ~

今回はボトルネックとして見つけたSQLの解析方法となります。

重いSQLを発見したらその原因を取り除きます。
SQLの修正方法はいろいろありますが、
基本はインデックスを適切に使用するようにすることです。

・インデックスとは

インデックスとはインデックス(索引)の言葉通り、
データに素早くアクセスするための見出しです。
最もよく使われる基本的なインデックスはBツリーインデックスという構造です。

【詳しくは下記参照】
wiki 索引 (データベース)
B-treeインデックス入門

インデックスを適切に使う、使わないで
数倍どころか数百倍、数千倍以上の差が出ることも多いため、効果が非常に高いです。

・EXPLAINの使い方

SQLの解析にはEXPLAINを利用します。
使い方は簡単で、解析したいSQLの前に「EXPLAIN」を付けます。

explainを使って解析すると下記のような解析結果が出力されます。

主キー検索の場合

mysql > explain select col_b from tbl_a where col_b=100000;
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | tbl_a | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

インデックス検索の場合

mysql > explain select col_b from tbl_a where col_c=2000000;
+----+-------------+---------------+-------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | tbl_a | const | col_c | col_c | 4 | const | 1 | NULL |
+----+-------------+---------------+-------+---------------+-------------+---------+-------+------+-------+

フルスキャンの場合

mysql > explain select col_b from tbl_a where col_d='aaa';
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+

「using filesort」がある場合

mysql > explain select col_b from tbl_a where col_d='aaa' order by col_e;
+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------------------+

表 8.1 EXPLAIN 出力カラム

カラム 意味
id SELECT 識別子。
select_type SELECT
table 出力行のテーブル
type 結合型
possible_keys 選択可能なインデックス
key 実際に選択されたインデックス
key_len 選択されたキーの長さ
ref インデックスと比較されるカラム
rows 調査される行の見積もり
Extra 追加情報

【参照】
EXPLAIN 出力フォーマット

・注目する箇所

select_type
DEPENDENT SUBQUERY(相関サブクエリ)があれば要注意

type
ALL テーブルをフルスキャンしてるので重い、インデックスを使いましょう
ただしデータ件数が少ない(数百件くらい)場合はあまり影響は出ない
INDEX インデックスのフルスキャンが行われている、重い

key
使用されたインデックス、NULLの場合使用されていない

rows
読み込まれるデータ検索の推定値、基本的にこの値が大きい場合は要注意

Extra
Using filesortとUsing temporaryがあれば要注意

【参照】
MySQL初心者に贈るインデックスチューニングのポイントまとめ2014

・テーブル構造とインデックスの確認

descでテーブル構造を確認

mysql > desc tbl_a;
+-----------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+-------+
| col_b | int(15) unsigned | NO | PRI | NULL | |
| col_c | int(9) | NO | MUL | NULL | |
| col_d | varchar(20) | NO | | 0 | |
| col_e | int(5) | NO | | NULL | |
+-----------------------+---------------------+------+-----+---------------------+-------+

show indexでインデックスを確認

mysql > show index from acount_public;
+---------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_a | 0 | PRIMARY | 1 | col_b | A | 500000 | NULL | NULL | | BTREE | | |
| tbl_a | 0 | col_c | 1 | col_c | A | 80000 | NULL | NULL | | BTREE | | |
+---------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

「Cardinality」という行がありますが、これはカラムの値の種類になります。
この数が大きいほどインデックスの絞り込み効率が高く(カーディナリティーが高い)、
小さいほどインデックスの絞り込み効率が低いです(カーディナリティーが低い)。
なるべく効率の良いインデックスを作成しましょう。

・インデックスを張る

ALLのフルスキャン、インデックスフルスキャン、Using filesortなどを見つけたら対象のテーブルにインデックスを作成します。

CREATE INDEX col_d ON tbl_a(col_d);

参照
インデックスの作成・削除・情報表示・追加

インデックスが張れたら、再度explainをおこなって効果を確認します。
またSQLをそのまま実行してインデックスを張る前後での実行速度を比較します。
効果が出る方法を見つけるまで根気よく繰り返しましょう。

・まとめ

  • SQLの解析にはEXPLAINを使う
  • できるだけインデックスを使った検索に変える
  • type=ALL,type=INDEXの場合はインデックスを使わせる
  • Using filesort、Using temporaryは要注意
  • rowsが大きい場合はなるべく小さくする
  • カーディナリティーの大きい高いインデックスを作る、使う

ついでに

  • サブクエリは遅いのでなるべく避ける
  • 無駄な結合をしない

【その他参考記事】
ヤフー社内でやってるMySQLチューニングセミナー大公開

この記事がお役に立てば【 いいね 】のご協力をお願いいたします!
0
読み込み中...
0 票, 平均: 0.00 / 10
4,173
X facebook はてなブックマーク pocket
【2024.6.30 CentOS サポート終了】CentOS サーバー移行ソリューション

【2024.6.30 CentOS サポート終了】CentOS サーバー移行ソリューション

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始いたします!

【2024年2月~】25年卒 エンジニア新卒採用の募集を開始いたします!

【大阪 / 横浜】インフラエンジニア・サーバーサイドエンジニア 積極採用中!

【大阪 / 横浜】インフラエンジニア・サーバーサイドエンジニア 積極採用中!

この記事をかいた人

About the author

原岡昌寛

株式会社ビヨンドの代表取締役です。

Oracle DBA(UNIX、Linux、Windows SeverでDBのお世話)
⇒Linuxエンジニア(DCでサーバー、FW、L3などをまるっと構築・運用)
⇒クラウドエンジニア(マルチクラウドAWS、GCP、Azure等)
⇒経営者

趣味は読むこと。マンガ、ネット小説、ビジネス書などなんでも読みます。最近ソロキャンプをはじめました。