SQL チューニングの話

#SQL チューニング
メモ書き程度です。

##1.マルチインデックスの使用
公式
簡単に言うと：単一の複合インデックスにより、同じテーブルへの複数の種類のクエリーを高速化できます。

create table user (
  id int(5),
  age int(3),
  salary int(5),
  primary key (id),
  key cols_index (id, age, salary) #マルチインデックス
) 

上記マルチインデックスを含めたテーブル構造の場合、マルチインデックスは、クエリ文がINDEX作成時のカラム順に基づいていないと、INDEXが使えない。

つまり、下記INDEXが使用可能

（id,age,salary）
（id,age)
（salary)

##2.where句の「!=」 or 「<>」について
where句の中「!=」 または 「<>」を利用する場合、INDEXが効かない、FULL SCANになってしまう。

#チューニング前
SELECT id FROM A WHERE ID != 5
#チューニング後
SELECT id FROM A WHERE ID > 5 OR ID < 5

##3.where句の「IS NULL」 or 「IS NOT NULL」について
where句の中「IS NULL」 または 「IS NOT NULL」を利用する場合、INDEXが効かない、FULL SCANになってしまう。

#チューニング前
SELECT id FROM A WHERE num IS NULL 
#チューニング後(numのデフォルト値は0が設定し、かつnumの中にNULLが含まれていない)
SELECT id FROM A WHERE num = 0

##4.where句の「OR」について
where句の中「or」を利用する場合、INDEXが効かないため、「union all」を使った方がいい。

#チューニング前
SELECT id FROM A WHERE num = 10 or num = 20
#チューニング後
SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num = 20

##5.where句の「IN」 or 「NOT IN」について
「in」と「not in」もFULL SCANになるため、利用する時を注意しましょう。
代替案①：in → between

#チューニング前
SELECT id FROM A WHERE num in(1,2,3)
#チューニング後
SELECT id FROM A WHERE num between 1 and 3

代替案②：in → exist

#チューニング前
SELECT id FROM A WHERE num in(select num from B )
#チューニング後
SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)

代替案③：in → inner join

#チューニング前
SELECT id FROM A WHERE num in(select num from B)
#チューニング後
SELECT id FROM A INNER JOIN B ON A.num = B.num

##6.where句の関数・計算の利用について
where句"="の左側もし関数・計算を利用したら、INDEXが効かないになる。

例①：

#チューニング前
SELECT id FROM A WHERE num/2 = 100
#チューニング後
SELECT id FROM A WHERE num = 100 * 2

例②：

#チューニング前
SELECT id FROM A WHERE substring(name,1,3) = 'abc'
#チューニング後
SELECT id FROM A WHERE LIKE 'abc%'

例③：

#チューニング前
SELECT id FROM A WHERE datediff(day,createdate,'2018-10-18')=0
#チューニング後
SELECT id FROM A WHERE createdate >= '2018-10-18' and createdate <'2018-10-19'

例④

#チューニング前
SELECT id FROM A WHERE year(addate) < 2018
#チューニング後
SELECT id FROM A where addate < '2018-01-01'

##7.「select * from table」について
①.select * from tableを利用しないでください。
②.**"*"**を利用せず、具体的なカラムを利用してください。
③.使わないカラムを返さないようにしてください。

##8.INSERT文について

#チューニング前
INSERT into person(name,age) values('A',14)
INSERT into person(name,age) values('B',14)
INSERT into person(name,age) values('C',14)
#チューニング後
INSERT into person(name,age) values('A',14),('B',14),('C',14);

##9. LIMITのページネーションについて

#チューニング前
SELECT id FROM A LIMIT 90000,10;
#チューニング案①
select id from A order by id limit 90000,10;
#チューニング案②
select id from A order by id  between 90000 and 100000;

##10.キャストの利用について
キャストすると、INDEXが使えない。

#引数：utime 中身：2018-10-18 型：datetime

#チューニング前
date（utime）> "2018-10-17"
#チューニング後
utime > "2018-10-17 00：00：00" 

##11.tableの結合(JOIN)について
なるべくinner joinを使いましょう！

#チューニング前
SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
#チューニング後
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN B ON A.id = B.id;

##12.INDEXを作成すべき列について
①.INDEXは、必要なものだけ!
INDEXが多ければ多くほど、update、insert、delete文にも影響してしまう。

②.INDEXの作成
1.検索をよく利用する列
　・WHERE句の条件、または結合の条件として頻繁に使用する。
2.更新が少ない列
　・データの挿入、更新、削除の処理では索引のメンテナンス作業が発生し、速度の低下につながります。
3.種類が多い列(カーディナリティが高い)
　・区分、0又は1の値しか持っていない場合は効果はあまり得られません。

#参考URL
https://www.itcodemonkey.com/article/10341.html
https://www.casleyconsulting.co.jp/blog/engineer/97/
https://qiita.com/rm-rf-slant/items/8023500788352646b6c2