＜学習する内容＞
処理速度を上げるためテーブルにindexを設定する方法を学びます
１）indexの設定
indexとは本に書かれている「索引」のことです。例えば、phpの入門書があります。substr()を調べたいときはまず
索引を見てページを確認しそのページを開くかと思います。データベースも同じテーブルごとにこの索引を設定します。
この設定がないと抽出したい条件にて一から順番にデータを読んでいくため非常に時間がかかります。indexの設定により
10～1000倍程度処理が上がります。
＜設定方法＞
alter table 対象テーブル名 add index インデックス名(対象カラム名);
この対象カラムはカンマ区切りで複数設定が可能(カラム1,カラム2,カラム3)
また、インデックス名は任意で省略可能。省略時は最初のカラム名がインデックス名となります。
ではどのカラムを設定すれば良いか。
・WHERE句で条件設定しているカラム
・JOINさせているカラム
・GROUP BY で指定しているカラム
・ORDER BY で指定しているカラム
＜index設定内容確認方法＞
SHOW INDEX FROM テーブル名;
＜index設定削除方法＞
alter table 対象テーブル名 drop index インデックス名;
4.1で使っているSQL文を例に設定してみます。
SELECT sum(sales) AS sales
,sum(profit) AS profit
,EXTRACT(YEAR_MONTH FROM salesDate) AS salesMonth
FROM saleslist
WHERE salesDate >= '$startDate'
AND salesDate < '$endDate'
AND staffCode = $staffCode
GROUP BY salesMonth
ALTER TABLE saleslist ADD INDEX SDSC(salesDate,staffCode);
この場合GROUP BY の salesMontyはエイリアスのためカラムがなくindexの設定はできません。
SELECT a.sales
,a.profit
,a.salesDate
,cus.customerName
,sec.sectionName
,stf.staffName
FROM saleslist AS a
LEFT JOIN Mcustomer AS cus ON a.customerCode = cus.customerCode
LEFT JOIN Msection AS sec ON a.sectionCode = sec.sectionCode
LEFT JOIN Mstaff AS stf ON a.staffCode = stf.staffCode
WHERE a.salesDate >= '$startDate'
AND a.salesDate < '$endDate'
AND a.sectionCode = $sectionCode
AND a.staffCode = $staffCode
ORDER BY a.sales
ALTER TABLE saleslist ADD INDEX(salesDate,sectionCode,staffCode,sales);
マスターテーブルM******はそれぞれのコード自体がprimary keyなのでindexの設定は必要はありません。
indexを4つのカラムを設定してますが、この設定の順番やすべて設定が必要なのか悩ましいところです。
上記のSQL文の目的は、まず抽出したいのが年月度集計で次に部課検索、その次に担当者、最後にソート順のsalesとなります。
すべてのカラムが必要なのかは、実際設定して検索をしてPoewerShellからSQL文を実行させQuery処理時間を見て判断すれば良いかと思います。
その際indexを設定しているカラムの減らして処理時間を確認ください。一番早い設定を採用でいいと思います。
このSQL文の前にEXPLAINを入れて
EXPLAIN SELECT a,sales ・・・・・・・ ; を実行させるとindexの利用状況が確認できます。
possible_keys にて設定したSDSCが使われてるのがわかります。
※設定したindexが使わるかどうかがmysql側で判断してます。またデータが1,000件とか少ない場合は大して早くなった実感はありません。
何十何百万件以上になるとはっきり速くなったことが実感できます。
他にもチューニング方法は数多くありますが、まずはindexの設定で速度改善を行って頂ければと思います。
