MYSQL効率化

テーブルを作る時の注意点#

テーブル作る時気づいてなかったが、後でデータの量増えたらスピードに影響があるところはいくつある

1. textよりcharかvarcharの方がいい###

charとvarcharの長さは固定で、設定できるもので、デフォルト値も指定できるが、text長さもデフォルト値が指定できない。それでハードウェア上にcharとvarcharの方が効率が高い。charの長さは255の定数で、足りない分はMYSQLがスペースで補充する。varcharの長さはv5.0.3以降65535まで設定できて、長いカラムにおすすめ。

2.NULLを許可しないように、デフォルト値を指定###

MYSQLでNULLと""は大きさが違って、NULLは実際1バイトを持つ。そして、WHEREにIS　NULLなどNULLを判断する文を追加すると、インデックスをやめて全テーブルをスキャンしてしまうので、デフォルト値がNULLにするのは推奨ではない。特に値がINTの時、デフォルト値を0にする方がおすすめ。

ただし、文字の場合は、NULLのダウンロード時間は""より若干早いので、（通信する時""は大きさ持つがNULLは持たないため）、実際の状況を考えながら決めよう。

3.　各カラムの大きさを絞り込む###

これも1と同じ原因である。例えば、値が0と1しかないカラムはINT型で作ったら、長さはデフォルトで11となる。しかし、実際1以上にはならない。こういう場合は長さを1にした方がいい

4. よく使われるカラムにインデックスをつけよう###

インデックスは辞書の「書き数で検索」のようなイメージである。データの量が多い時、インデックスをつけると、全ての組み合わせを探すのをやめるので、検索速度を向上。具体的にどう追加するのは、後で詳しく説明する。

5. できるだけカラムをINT型に###

値が数字の時は必ずINT型に、性別などのカラムも'FEMALE'と'MALE'より0と1の方がいい。なぜなら、文字列を比較する時は各文字を比較しないといけないが、数字は（桁数に関係なく）すぐにできる。

インデックスの裏技

インデックスをつけると、普通のカラムより優先的に見られるので、データ量大きい時、或いは複数のテーブルから検索する時、インデックスをつけるだけでも検索スピードが一気に上がる。

1. つけた方がいい場所###

WHERE、ORDER BY、GROUP BYによく使われるところはインデックスをつけた方がいい
インデックスをつけると優先的にそのカラムを見るので早くなる。また、インデックスがついているカラムはWHERE、ORDER BY、GROUP BYの直後にした方がいい。

2. インデックスの数を控える###

インデックスの数は多いほどお多ければいいものではなく、一つのテーブルに対して、六つ以下の方がおすすめ。
インデックスのテーブルはメモリに保存するので極めに多く設定すると逆効果になる恐れがある。

3. 値が分布によって効果が違う###

平均的に分布したカラムはインデックスつけても効果がない。例えば「性別」、「学年」など、各値に対して割合が同じのカラムはインデックスに推奨しない。
値によって数がかなり変わる方をおすすめ。例えば、あるカラム値0と1があって、0と1の割合は2:8。こういう場合、特に0に絞り込みたい時は効率結構上がる。

4. クエリーによって効果が違う###

SELECTしたカラムはもうすでにインデックスたくさん追加した場合、WHEREに追加しても効果がない

インデックスを追加すると、検索が早くなるが、記録の挿入、削除、変更が遅くなるので、バランス気をつけよう。

条件をつける時の注意点#

ここで「条件」というのは、基本WHEREのあとにつける絞り込み用の文である。書き方によってパーフォマンスが結構変わって、インデックス邪魔になったり場合もある。

1. 複数の条件を指定している時、順番によってパフォーマンスが結構変わる###

まず、インデックスついてるカラムも一番前にした方がいい。
SQL文のアルゴリズムは、複数の条件をしてしている時、条件順でループして排除しながら次の条件に進む感じである。
例えば、あるテーブルに、理工学部の1000人の学生の成績評価を入っている。60点以上の人が8割で、そして、男女比は9:1である。（理工学部あるある）
では、単位を取れた女子学生数は何人いるのだろう

SELECT * FROM GRADE WHERE grade >= 60 AND gender = 'F'

SELECT * FROM GRADE WHERE gender = 'F' AND grade >= 60

上のクエリーだと、まず記録数を1000から800に絞り込んで、また800人の中から女子学生を探すので、1000800の記録をループしたが；
下の方だと、まず記録数を1000から100に絞り込んで、また100人の女子学生の中から単位取れた人を探すので、1000100の記録をループした。
これで、効率が8倍も違うことを分かるのだろう。

2. !=や<>などの演算子を使わない###

使うとインデックスをやめて全テーブルをスキャンしてしまう。普通に=、<、>などを使った方がいい。

3. NULLを判断するものを使わない###

NULLを判断する文を追加すると、インデックスをやめて全テーブルをスキャンしてしまう。

4. ORを避けよう###

ORもインデックスをやめて全テーブルをスキャンさせててしまう

SELECT * FROM TABLE WHERE id = 1000 or id = 2000                                 ❌


SELECT * FROM TABLE WHERE id = 1000
UNION ALL                                                                        ⭕️
SELECT * FROM TABLE WHERE id = 2000

5. INとNOT INを避けよう###

INは実際カスタムに範囲を指定するところで便利だが、これもORと同じで、使うとインデックスをやめて全テーブルをスキャンさせててしまう。
例えばWHERE id IN(1,2,3)みたいなところはBETWEENを代わりに使おう。
もしINの中にサブクエりーが入ってる時、INの代わりにEXISTSの方がおすすめ

SELECT num FROM table1 WHERE num in(SELECT num FROM table2)                      ❌


SELECT num FROM table1 WHERE EXISTS(SELECT 1 FROM table2 WHERE num=table1.num)   ⭕️

6. LIKEのパターンは「%」から始まらない###

LIKEのパターンは「%」から始まったら、インデックスの邪魔になる。
例えば

SELECT id from table WHERE name LIKE '%abc%'　　　　　　　　　　　　　　　　　　　　                　　　　         ❌ 

SELECT id from table WHERE name LIKE '%abc'　　　　　　　　　　　　　　　　　　　　                　　　　          ❌ 


SELECT id from table WHERE name LIKE 'abc%'　　　　　　　　　　　　　　　　　　　　                　　　　          ⭕️

7. 比較演算子の左に算式や関数を入れないように###

=などの比較演算子の左に簡潔にカラム名のままにしよう

SELECT id from table WHERE id * 2 < 500     　　　　　　　　　　　　　　　　　　　　                　　　　         ❌ 

SELECT id from table WHERE id < 500　/ 2     　　　　　　　　　　　　　　　　　　　　                　　　　         ⭕️


SELECT id from table WHERE substring(name,1,3)='abc'　　                　　　　          ❌ 

SELECT id from table WHERE name LIKE 'abc%'　　                         　　　　          ⭕️ 

他の注意点##

JOINとWHERE###

JOINはINNER JOINとLEFT JOINとRIGHT JOINとCROSS JOIN4種類があって、JOIN、INNER JOIN、CROSS JOINは同じで、ONを書かない場合だと直積集合を返してくる。

昔はWHEREよりJOINの方が効率高かったが、今だ一つになって、JOINの明示的型(explict)と黙示的型(implict)になって、効率は大体同じ。

# 明示的型(explict)

SELECT * FROM
table1 INNER JOIN table2
ON table1.id = table2.id;


# 黙示的型(implict)

SELECT table1.*, table2.*
from table1, table2
where table1.id = table2.id;

UNIONとUNION ALL###

UNIONは重複を排除しながらテーブルの下に追加する。
UNION ALLは重複を気にせずにテーブルの下に追加する。
重複に気にしないところだとUNION ALLは圧倒的に早い。

数を数える時、UNION ALLも便利。この二つの効率が同じである。

# JOIN

SELECT a+b as count FROM 
(SELECT COUNT(*) AS a FROM table1) AS a,
(SELECT COUNT(*) AS b FROM table2) AS b


# UNION ALL

SELECT SUM(A) as count FROM
((SELECT COUNT(*) AS a FROM table1)
UNION ALL
(SELECT COUNT(*) AS a FROM table2)) as a

カラム数とパフォーマンス###

各カラムは各自大きさ持ってるので、検索する時いらないカラムを外した方がいい。特にTEXT型などの大きいカラム。
これで検索する時だけじゃなく、データを渡す時も軽くなる。

しかし、COUNT()を使う場合だと、逆にカラムを絞り込まず、COUNT(*)の方が一番早い。