SQL チューニングの話

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

##1.マルチインデックスの使用
[公式](https://dev.mysql.com/doc/refman/5.6/ja/multiple-column-indexes.html)
簡単に言うと：単一の複合インデックスにより、同じテーブルへの複数の種類のクエリーを高速化できます。

```sql
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になってしまう。

```sql
#チューニング前
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になってしまう。

```sql
#チューニング前
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」を使った方がいい。

```sql
#チューニング前
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**

```sql
#チューニング前
SELECT id FROM A WHERE num in(1,2,3)
#チューニング後
SELECT id FROM A WHERE num between 1 and 3
```
**代替案②：in → exist**

```sql
#チューニング前
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**

```sql
#チューニング前
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が効かないになる。

**例①：**

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

**例②：**

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

**例③：**

```sql
#チューニング前
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'
```

**例④**

```sql
#チューニング前
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文について
```sql
#チューニング前
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のページネーションについて
```sql
#チューニング前
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が使えない。

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

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

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

```sql
#チューニング前
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