MySQL
性能調整

構文

• DISTINCTとGROUP BYを同じSELECT内で使用しないでください。

• OFFSETを介して改ページしないでください。

• WHERE（a、b）=（22,33）は最適化されません。

• UNIONの後にALLまたはDISTINCTを明示的に言う - より速いALLまたはより遅いDISTINCTを選択することを思い出させます。

• SELECT *を使用しないでください。特に、不要なTEXT列またはBLOB列がある場合は、SELECT *を使用しないでください。 tmpテーブルと送信にオーバーヘッドがあります。

• GROUP BYとORDER BYがまったく同じリストを持つことができる場合は、より高速です。

• FORCE INDEXを使用しないでください。今日は助けになるかもしれないが、明日はおそらく傷つくだろう。

備考

正しいインデックスを追加する

これは大きな話題ですが、最も重要な「パフォーマンス」の問題でもあります。

初心者のための主な教訓は、 "複合"索引を学ぶことです。ここに簡単な例があります：

INDEX(last_name, first_name)

これらのために優れています：

WHERE last_name = '...'
WHERE first_name = '...' AND last_name = '...'   -- (order in WHERE does not matter)

しかし、そうではない

WHERE first_name = '...'   -- order in INDEX _does_ matter
WHERE last_name = '...' OR first_name = '...'   -- "OR" is a killer

キャッシュを正しく設定する

innodb_buffer_pool_sizeは、使用可能なRAMの約70％にする必要があります。

非効率な構造を避ける

x IN ( SELECT ... )

JOIN

可能であれば、 OR避けてください。

WHERE DATE(x) = ...ような関数内のインデックス付きの列を '非表示'にしないでください。 WHERE x = ...とWHERE x = ...

通常、適切な照合を行うことで、 WHERE LCASE(name1) = LCASE(name2)避けることができます。

「ページ区切り」にはOFFSETを使用しないでください。代わりに「中止した場所を覚えてください」。

SELECT * ...避けてくださいSELECT * ... （デバッグしない限り）。

Maria Deleva、Barranka、Batsuへの注意：これはプレースホルダーです。本格的な例を構築する際には、これらの項目を削除してください。あなたができることをやった後、私は残りの部分を詳述し、そして/またはそれらを投げるために移動します。

ネガティブ

パフォーマンスに役立たない可能性のあるものがいくつかあります。古くなった情報や馬鹿に由来しています。

• InnoDBは、MyISAMがより良くなるとは思えないほど改善されました。
• PARTITIONingはパフォーマンスのメリットはほとんどありません。パフォーマンスを損なう可能性もあります。
• query_cache_size 100Mより大きく設定すると、通常はパフォーマンスが低下します。
• my.cnfの値を増やすと、「スワッピング」が発生する可能性があります。これは重大なパフォーマンス上の問題です。
• 「接頭辞索引」（ INDEX(foo(20)) ）は一般的に役に立たない。
• OPTIMIZE TABLEはほとんど常に役に立たない。 （そしてそれはテーブルをロックする必要があります。）

インデックスがある

小さなテーブルでクエリを高速化するために最も重要なことは、適切なインデックスを持つことです。

WHERE a = 12  --> INDEX(a)
WHERE a > 12  --> INDEX(a)

WHERE a = 12 AND b > 78  --> INDEX(a,b) is more useful than INDEX(b,a)
WHERE a > 12 AND b > 78  --> INDEX(a) or INDEX(b); no way to handle both ranges

ORDER BY x  --> INDEX(x)
ORDER BY x, y  --> INDEX(x,y) in that order
ORDER BY x DESC, y ASC  --> No index helps - because of mixing ASC and DESC

関数に隠さないでください

よくある間違いは、関数呼び出しの中でインデックス付きの列を隠すことです。たとえば、これは索引によって助けられません。

WHERE DATE(dt) = '2000-01-01'

代わりに、 INDEX(dt)指定すると、インデックスを使用できます。

WHERE dt = '2000-01-01'  -- if `dt` is datatype `DATE`

これはDATE 、 DATETIME 、 TIMESTAMP 、さらにはDATETIME(6) （マイクロ秒）で動作します。

WHERE dt >= '2000-01-01'
  AND dt  < '2000-01-01' + INTERVAL 1 DAY

または

一般的にORは最適化を殺します。

WHERE a = 12 OR b = 78

INDEX(a,b)使用することはできずINDEX(a,b) "索引マージ"を介してINDEX(a), INDEX(b)を使用する場合と使用しない場合があります。インデックスのマージは何もありませんが、ほんの僅かです。

WHERE x = 3 OR x = 5

に変身する

WHERE x IN (3, 5)

その中にxを持つ索引を使用することがあります。

サブクエリ

サブクエリにはいくつかの味があり、最適化の可能性が異なります。まず、サブクエリは「相関」または「相関なし」のいずれかになることに注意してください。相関は、サブクエリの外部からの値に依存することを意味します。これは、一般に、外部値ごとにサブクエリを再評価する必要があることを意味します。

この相関サブクエリはしばしばかなり良いです。注：最大1つの値を返す必要があります。これは、 LEFT JOINよりも必ずしも高速ではないが、代替として有用であることが多い。

SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c
    FROM u ...
SELECT a, b, ( SELECT MAX(x) ... ) AS c
    FROM u ...
SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c
    FROM u ...

これは通常無相関です：

SELECT ...
    FROM ( SELECT ... ) AS a
    JOIN b ON ...

FROM-SELECTに関する注記：

• それが1行を返す場合、素晴らしい。
• ( SELECT @n := 0 )が( SELECT @n := 0 )であるため、残りの部分またはクエリで使用するための@変数を初期化するという良いパラダイム（やはり "1行"）があります。
• 多くの行を返し、 JOINも多くの行を持つ( SELECT ... )場合、効率はひどくなる可能性があります。 5.6より前はインデックスがなかったので、 CROSS JOINになりました。 5.6+には、テンポラリテーブルの最良のインデックスを推測し、それを生成します。これは、 SELECT終了したときにだけスローします。

JOIN + GROUP BY

非効率的なクエリにつながる一般的な問題は、次のようなものになります。

SELECT ...
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY a.id

まず、 JOINは行数を拡張します。 GROUP BYそれをaの行数に戻します。

この爆発 - 爆縮の問題を解決する良い選択肢はありません。 1つの可能なオプションは、 JOINをSELECT内の相関サブクエリに変換することです。これにより、 GROUP BYも削除されます。