MySQL JOINパフォーマンスの調査

JOINがかなり高価な操作と見なされることは誰にとっても秘密ではないと思います。多くの初心者プログラマー（MySQLを使用している）は、JOINが悪いことを怖がらせたいです。



この質問をさらに詳しく調べて、JOINが本当に悪いかどうか、そしていつそれについて考えるかを見てみましょう。

私が書いていないこと

そもそも、私はしないとすぐに言いたいです。



-MySQLのチューニング：すべての設定がデフォルトで取得されます（ innodb_buffer_pool_size = 8 MB以上を含む ）

-プログラミング言語との統合： すべての要求は、 Sequel Proの MySQLクライアントを介して行われ、読み取り値に基づいて時間が測定されます。

-3行をフェッチするときの結合などの明らかなこと： 質問は、マッチで保存するかどうかです。私は考慮しません-数十パーセントではなく、数十回保存することを検討します

初期条件

不名誉に簡単な2つのプレートがあります （ テーブルは、たとえば、ランダムデータで作成されただけです ）。



呼び出し-1000万行：

id	INT PRIMARY KEY AUTO_INCREMENT
user_id	INT
費用	INT
call_dt	日時
tariff_id	INT

ユーザー-10万行：

id	INT PRIMARY KEY AUTO_INCREMENT
生年月日	DATE
お名前	VARCHAR（10）
性別	ENUM（「M」、「F」）

名前は、私たちが思うに、インデックスは主キー（id）にのみあると思います。 純粋に、原則として、調査するクエリの実行に役立つ何らかの種類のインデックスを作成できますが、目標は異なります。つまり、JOINの動作速度を調査することです。

テーブルの種類

調査の目的で、 CallsテーブルはMyISAMとInnoDBの2つのタイプで取得され、 Userテーブルは3つのタイプのMyISAM、InnoDB、MEMORYのJOINを作成します

最初の血

すべてのテストは、MAMPのデフォルト設定でMac OS X上のMySQLバージョン5.5.9を使用して、ラップトップで実行されました。 すべてのテーブルがメモリに完全に収まり、すべてがキャッシュに入れられるようにクエリを数回実行しました。



まず、このようなクエリを実行して、MyISAMおよびInnoDBで行を表示する速度を見てみましょう（価格またはuser_idによるインデックスはないことを思い出してください-MySQLでFULL SCAN速度を測定します）。

SELECT MAX(cost) FROM calls WHERE user_id = 5000; --  №1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、そのような要求：

 SELECT MAX(cost) FROM calls; --  №2
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果（エラーが5％未満）：

いや	InnoDB、ms	Myisam ms
1	5 360	862
2	5,390	1,150

MyISAMの集計選択がWHEREに該当する行の数によってそれほど影響を受ける理由をこれ以上詳しく調べたくありませんが、事実はそのままです。テーブルの完全な順次スキャンにより、MyISAMはInnoDBの4.5倍高速です。 したがって、InnoDBは「ブレーキ」であり、MySQL自体（MyISAMを使用）は非常に賢明であるという意見です。



ミニ結論： MyISAMを完全に連続して表示すると、InnoDBより5倍高速

参加する

ここで、Usersテーブルをケースに接続しましょう。作成したものではありません。

このような計画のリクエストを調査します。

 SELECT MAX(calls.cost) FROM calls JOIN users ON calls.user_id = users.id WHERE calls.cost > %d AND users.sex = 'M'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

呼び出しのテーブルの特定の割合のレコードが選択に該当するように、コストのパラメーターが選択されます



行のN％に対応するコストパラメーターを使用してマッスルに対して上記のクエリを実行すると、MySQLはN％のケースでのみ結合を行い、残りの（100-N）％の行ではJOINを実行しません。 これがそのような賢いMySQLです。



JOINでテストを始めましょう。

FULL SCAN + JOIN 0.1％of lines

ユーザー\通話	InnoDB、ms（JOIN、msのみ）	MyISAM、ms（JOIN、msのみ）
Innodb	5,450（〜0）	857（〜0）
マイサム	5,450（〜0）	937（〜100）
記憶	5 350（〜0）	845（〜100）

これまで、フルスキャンとの時間差はほとんどありません。 理解できる-結局のところ、JOINはわずかな数の行に対して行われます。

フルスキャン+行の1％に参加

ユーザー\通話	InnoDB、ms（JOIN、msのみ）	MyISAM、ms（JOIN、msのみ）
Innodb	5,660（300）	999（140）
マイサム	6 530（1 200）	1 810（950）
記憶	5,460（100）	911（65）

おかしい？ 行の1％のみが結合され、MyISAM + MyISAMの結果はMyISAM + InnoDBの2倍になります。 この場合のInnoDBへのJOINがMyISAMへのJOINよりも速いのはとても面白いです。 そして、まだテストを開始していません:)！

FULL SCAN + JOIN行の10％

ユーザー\通話	InnoDB、ms	Myisam ms
Innodb	7,230（1,900）	2 190（990）
マイサム	16100（8800）	10,200（9,000）
記憶	6,080（700）	1,440（580）

Power（MyISAM）にとっては恥ずべきことですが、私にできることは... MyISAMがそれほど賢くないということです...それともそうではありませんか？ 最終的なテスト結果を見てみましょう。

FULL SCAN + JOIN 100％of lines

ユーザー\通話	InnoDB、ms	Myisam ms
Innodb	18,000（14,650）	12,500（11,655）
マイサム	100,000（96,650）	91 600（90 750）
記憶	10500（7150）	5,280（4,435）

MyISAMでJOINを使用して、巨大な（！）サンプル時間に注意してください。 しかし、InnoDBは驚きました。そのアーキテクチャにより、JOINはInnoDBにとって高価な操作ではありません。 正直に言うと、2番目に速いJOINオプションがInnoDBがMyISAMに接続しているという結果になったとき、私はとても驚きました。



MEMORYの場合、すべてが明らかだと思います-MEKRYはPKでの結合ごとに525％（4,435ミリ秒）のオーバーヘッドを与えます。InnoDBは1,380％（11,655ミリ秒）のオーバーヘッドを与えます。MyISAMについて話すのは残念です。

JOINをINに置き換える（...）

鋭い目は、私たちのシナリオ（ユーザーにJOINを行ってコールテーブルからすべての女性を除外する場合）に、JOINなしで、IN（）内のすべてのuser_idの単純なリストを使用する方法があることに気付くことがあります

 SET group_concat_max_len = 10000000; SELECT GROUP_CONCAT(id) FROM users WHERE sex = 'M'; --    50 ,  -   SELECT MAX(cost) FROM calls WHERE user_id IN(%s); -- %s   
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

タイプMyISAMのテーブルへのこのようなクエリは3,730ミリ秒で動作し、InnoDBに対して8,290ミリ秒で動作します。 鋭い目は、この方法がJOIN to MEMORYよりも速いことに気付くかもしれませんが、それほどではありません。 この方法は、MySQLへの接続が非常に高速な場合（UNIXソケットなど）に適しています。 それ以外の場合、非常に多くのレコードがあるIMHOでは、MySQLサーバーとアプリケーションサーバー間で大量のトラフィックを駆動することは最善のアイデアではないことは明らかです。



自分で結論を出すことができます。多くの参加が必要で、何らかの理由で高いパフォーマンスが必要な場合は、MyISAM + InnoDBの組み合わせ、または複数の参加がある場合はInnoDB + InnoDBのみを使用します。 極端な場合は、MyISAM + MEMORYを使用して、あらゆるシナリオで最大のパフォーマンスを得ることができます。 MEMORY + MEMORYには別のオプションがありますが、多数のレコードについてはそうしません;）。



UPD：非常に便利なコメント、たとえば次のコメントを寄せてくれたhomm habrayuzerに感謝したいと思います。 一般に、コメントを読むことを強くお勧めします。コメントは、何らかの理由で読者には明らかではなかった多くのことを説明しています。

-要求キャッシュは無効です

-JOINは主キーで行われます

-特定のクエリを最適化するタスクを設定していないため、Callsテーブルのインデックスは作成されません。