SQLパフォーマンス・チューニング・チュートリアル(翻訳)

本記事はSQL Performance Tuning Tutorial – MySQL Query Optimization Tips を訳したもの。


これは、SQLパフォーマンスチューニングシリーズの最初の部分です。 この記事では、MySQL関連の例を中心に説明しますが、他の多くのリレーショナルデータベースにも同じ概念を適用できます。

現在、ソフトウェア・エンジニアはSQLパフォーマンス・チューニングに関する膨大な知識を必要としています。 小さなスタートアップ・巨大なエンタープライズ企業、両方で変化が起こっています。 現在、開発者はSQLとDBへのアクセスレイヤを両方共担当しています。

どんなクエリでも、自動で高速化できます。無料で試してみましょう。

技術が進歩するにつれて、技術に明るくない人間ですら、あなたのアプリケーションについて、あなたが想定しているよりも素早く動作することを期待しています。 したがって、私たちはソフトウェア開発者として、いつでもどこでも高速にレスポンスされるという、終わりのない要求に応える必要があります。

データベースを抽象化するFW/ミドルウェア(Hibernate、JOOQ、Entity Framework、Sqlalchemy、Djangoなど)を使用している場合でも、ネイティブSQLクエリを記述している場合でも、 最終的にはクエリをチューニングすることにチャレンジするコットになるでしょう。

インデックスを作成する、しかし賢くやろう

インデックスはSQLのチューニングの最も重要な部分であると言う人もいます。 多くの場合、それは間違いなく真実です。 まず、最適なインデックスを選択すべきである、ということを考えるべき局面であるかどうか、にç²¾通してください。

インデックスを作る際には、クエリの重要なフィルタリング部分が含まれているため、問合せのWHERE句と表JOINには注意を払う必要があります。

また、データ検索における主要なボトルネックは、GROUP BYとORDER BYの部分です。 それは、ここで説明したように、場合によってはインデックスを作成できない可能性があるということです。 したがって、インデックスを作成する前に、クエリの設計を再考する必要があり、 優れたクエリを作成するだけでなく、インデックスを効かせやすいも作成する必要があります。

1つのクエリに対応するインデックスを見つけても、そこで止まらないでください。 視野を広げ、アプリケーション内の他の重要なクエリを調べます。 あなたが見るクエリが増えるほど、作成すべきベストなインデックスについて考えることが多くなります。 可能な限りインデックスを結合して、不要なインデックスを削除するようにしてください。 アプリケーション全体を見ることは、単一のクエリを見るよりも常に優れています。

また、インデックスを作成しすぎると、逆効果にもなりえます。というのは、INSERT/UPDATEなどの書き込み操作が遅くなる可能性があるためです。 したがって、クエリを最適化するためにインデックスを作成しますが、それを賢く行ってください。

インデックスの障害になってはいけない

私たちに “データベースが私のインデックスを使ってくれない理由は何ですか?”と尋ねている顧客から、私たちは多くのことを学んでいます。 実際、それは答えが無限に存在する質問です。 しかし、この記事では、よく見かけるいくつかの一般的なオプションを提供しようとしていますので、うまくいけば、自分のユースケースに役立つでしょう。

例 1 – 関数でラッピングされたインデックスは避ける

このクエリでは、2018年に米国で購入されたホットドッグの数を数えます。 あなたが好奇心が強い場合に備えて、2018年に米国で18,000,000,000個のホットドッグが販売されたということにしましょう。

SELECT count(*) FROM us_hotdog_purchases WHERE YEAR(purchase_time) = ‘2018’

どんなクエリでも、自動で高速化できます。無料で試してみましょう。

見ての通り、YEAR関数を使用してpurchase_timeから年の部分を取得しています。 このYEAR関数の呼び出しにより、purchase_timeはインデックスがあるが、YEAR(purchase_time)にの戻り値はインデックスが作成されていないため、 DBはpurchase_timeの検索にインデックスを使用できなくなります。

この問題を解決してこのSQLクエリをチューニングするには、MySQL 5.7.5以降で利用可能なGenerated Columnsを使用して、 関数の結果をインデックス化できます。

別の解決策は、関数呼び出しを使用せずに、同じクエリを書く別の方法を見つけることです。 この例では、その条件を範囲指定に変換します。これは全く同じ結果を返します。

SELECT count(*) FROM us_hotdog_purchases WHERE purchased_at >= ‘2018-01-01’ AND purchased_at < ‘2019-01-01’

例 2 – OR条件を避ける

新年の前夜に投稿されたFacebook上の投稿数、またはMarkという名前のユーザーの投稿数を選択するこのクエリを考えてみましょう。

SELECT count(*) FROM fb_posts WHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

username列とpost_time列の両方にインデックスを付けると役立つかもしれませんが、ほとんどの場合使用されません。 理由は2つの条件の間の接続をORで行っていることです。ORは、データベースに条件の各部分の結果を個別にフェッチします。

このクエリを見る別の方法は、OR条件を「分割」してUNION句を使用して結合することです。 この方法を使用すると、各条件を個別にインデックスを使用できるため、インデックスを使用して結果を検索し、その結果をUNION句と組み合わせることができます。

SELECT …
FROM …
WHERE username = ‘Mark’
    UNION
SELECT …
FROM …
WHERE post_time > ‘2018-01-01’

結果にレコードが重複しても構わない場合は、UNION ALL(デフォルトのUNION DISTINCTよりも優れたパフォーマンスを発揮する)を使用することもできます。

例 3 – Avoid sorting with a mixed order

Facebookからのすべての投稿を選択し、ユーザー名で昇順に並べ替え、投稿日順に降順で並べ替えるこのクエリを考えてみましょう。

SELECT username, post_type FROM fb_posts ORDER BY username ASC, post_type DESC

MySQL(および他の多くのリレーショナルデータベース)は、複数の順序でソートするときにインデックスを使用できません(同じORDER BY句でASCとDESCの両方)。これは、逆インデックス機能とMySQL 8.xのリリースによって変更されました。

もしまだ最新のMySQLバージョンにアップグレードしていないのであれば、何ができますか? まず、複数ソート自体を再検討することをお勧めします。それは本当に必要ですか?そうでない場合は、避けてください。

あなたはそれが必要だと決めたとしても、あなたのプロダクトマネージャーはこう言うかもしれない。 「それなしで管理することはできないだろうか?」 もう1つのオプションは、Generated Columns(MySQL 5.7.5以降で利用可能)を使用して逆のカラムを作成し、 元のカラムではなくそのカラムをソートすることです。 例として、数値列でソートしていると仮定して、元の数と相関する負の数値を持つ生成列を作成し、 その逆の順序で新しい列をソートすることができます。 これにより、すべての列はORDER BY句で同じソート順になりますが、ソートは製品の要件によって最初に定義されたとおりに行われます。

最後の潜在的な解決策は常に選択肢に存在しうるわけではないので、 最後の手段はインデックスを使用した混合注文ソートをサポートする最新のMySQLバージョンにアップグレードすることです。

例 4 – Avoid conditions with different column types

森の中の赤い果物の数を選択するこのクエリを考えてみましょう。

SELECT count(*) FROM forest WHERE fruit_color = 5;      /* 5 = red */

fruit_color列の型がVARCHARであるか、または数値以外の何らかのものであると仮定すると、 必要な暗黙的キャストは、データベースがフィルター処理に索引を使用できなくなるため、その列にインデックスを効かせることあまり役に立ちません。

では、このSQLクエリをどのようにチューニングできますか? このクエリを最適化するには2つの選択肢があります。最初の選択肢は、列の型と一致する定数値と列を比較することです。 したがって、VARCHAR列の場合は、 ‘5’(’シングルクォーテーション’で囲んでください)と比較します。

より良い選択肢は、カラムの型を、カラムが保持する値に最も適した型に合わせるようにチューニングすることです。 この例では、列をINT型に変更する必要があります。 列の型を変更することは複雑な作業になる可能性があるので、それに向かう前にその作業について調べてください。

接頭辞ワイルドカードを使用したLIKE検索を避ける

文字列 ‘Mar’を含むユーザー名からすべてのFacebook投稿を検索するこのクエリを考えてください。 Mark、Marcus、Almarなどのユーザーが投稿したすべての投稿を検索します。

SELECT * FROM fb_posts WHERE username like ‘%Mar%’

パターンの先頭にワイルドカード ‘%‘を付けると、データベースはこの列の検索にインデックスを使用できなくなります。 このような検索には時間がかかることがあります。

この場合、このクエリのパフォーマンスを向上させる2つのオプションがあります。 最初のものは簡単です - プレフィックスワイルドカードが本当に重要かどうかを検討してください。 それなしで管理することができる場合、それを取り除いてください。

フルテキストインデックスを使用することもできます。 しかし、これらのインデックスとMATCH … AGAINST構文は、MySQLのおなじみのLIKE式と比較して、いくつかの違いがあることに注意してください。

まとめ

SQLクエリパフォーマンスチューニングチュートリアルの最初のパートでは、賢明なインデックス作成の重要性について説明しました。 また、クエリでインデックス付きのカラムを使用しているときのいくつかの問題の例を挙げました。 これらで、クエリのパフォーマンスを向上できます。次の投稿で会いましょう。