パフォーマンスチューニング

MySQL/パフォーマンスチューニング/EXPLAIN

1月 1, 2003 · Posted in MySQL, パフォーマンスチューニング · Comment

パフォーマンスチューニングの第一歩。
SELECTクエリの解析を行う。
UPDATEやDELETEの解析はできないので、その場合はSELECT+WHERE句で代用しよう。

書式

explain [Query]

例

explain select * from MYTABLE;

テスト用データ

CREATE TABLE MYTABLE (
id int(8) NOT NULL,
name varchar(20),
age smallint(1),
PRIMARY KEY (id),
KEY index_text (name)
) TYPE=InnoDB;

insert into MYTABLE values (1, ’1′, ’1′);
insert into MYTABLE values (2, ’2′, ’2′);
insert into MYTABLE values (3, ’3′, ’3′);
insert into MYTABLE values (4, ’4′, ’4′);
insert into MYTABLE values (5, ’5′, ’5′);
insert into MYTABLE values (6, ’6′, ’6′);
insert into MYTABLE values (7, ’7′, ’7′);
insert into MYTABLE values (8, ’8′, ’8′);
insert into MYTABLE values (9, ’9′, ’9′);
insert into MYTABLE values (10, ’10′, ’10′);
insert into MYTABLE values (11, ’11′, ’11′);
insert into MYTABLE values (12, ’12′, ’12′);
insert into MYTABLE values (13, ’13′, ’13′);
insert into MYTABLE values (14, ’14′, ’14′);
insert into MYTABLE values (15, ’15′, ’15′);
insert into MYTABLE values (16, ’16′, ’16′);
insert into MYTABLE values (17, ’17′, ’17′);
insert into MYTABLE values (18, ’18′, ’18′);
insert into MYTABLE values (19, ’19′, ’19′);
insert into MYTABLE values (20, ’20′, ’20′);

type:テーブルスキャンのタイプを最適化する

ALLはフルスキャンなので、一番パフォーマンスが悪い。

目標とするtypeを理想的な順序で。

１．const

検索結果が１件。オプティマイザにより定数として扱われるため超高速。

２．ref系

eq_ref, ref, ref_or_nullがある。ユニークではないインデックスを利用して結果を検索。

３．range

インデックスを利用した範囲検索。

古いバージョンのMySQL(3.x, 4.xくらいまで？)の場合、１テーブルにつき１つのインデックスしか利用されないため、SQL改善時には注意が必要。
不恰好なSQLに書き換える(e.g.無理やりUNIONで結合)か、ある程度妥協しなければなりません。

４．ALL

フルスキャン。rows=行数。

rows:スキャン対象行数

rowsの項目は、SELECTによって結果を取得するために、スキャンしなければならない行数を表している。
この行数をできる限り小さくすることを目標とする。

同じ結果となるSQLでも、記述方法によって効率が変わってきます。

単純な例
(idが1〜6まであるとして、)

select * from MYTABLE where id in (’1′, ’3′, ’5′);
と、

select * from MYTABLE where id not in (’2′, ’4′, ’6′);
では、同じ結果が取得できるにもかかわらず、前者はrowsが3、後者はrowsが6となります。

Extra：付加情報

以下のようなものがあります。
ファイルソートと一時表の利用を避けるようにがんばりましょう。Using indexとwhere usedはウレシイ。

-where used

where句を利用した問い合わせ

-Distinct

レコードが見つかった場合、その時点で処理を中断する。

-Not exists

LEFT JOIN最適化が行われている

-Using filesort

ファイルソートが利用されている。

-Using index

インデックスツリーだけ検索されている。

-Using temporary

データ検索のために一時表が作成されている。

Tags: MySQL, パフォーマンスチューニング

MySQL/パフォーマンスチューニング/インデックス

1月 1, 2003 · Posted in MySQL, パフォーマンスチューニング · Comment

インデックス追加

テーブルにインデックスを追加する。

書式

alter table [対象テーブル] add index [インデックス名] ([インデックスを追加するカラム]);

例

alter table USER add index idx_user_1 (userName, delFlag);

mysql> alter table USER add index idx_user_1 (userName, delFlag);
Query OK, 1427 rows affected (0.19 sec)
Records: 1427 Duplicates: 0 Warnings: 0

インデックス削除

テーブルに設定されているインデックスを削除する。

書式

alter table [対象テーブル] drop index [インデックス名];

例

alter table USER drop index idx_user_1;

mysql> alter table USER drop index idx_user_1;
Query OK, 428 rows affected (0.05 sec)
Records: 428 Duplicates: 0 Warnings: 0

インデックス参照

テーブルに設定されているインデックスの一覧を表示する。

書式

show index from [テーブル名];

例

show index from USER;