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

SQL チューニングの話