LIMITオフセットの最適化

大きなテーブルにLIMITオフセットが使用されている場合は、遅かれ早かれブレーキが開始されます。 リクエストを表示

SELECT * FROM test_table ORDER BY id LIMIT 100000, 30
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

完了するまでに非常に長い時間がかかる場合があります。 たとえば、私の場合、サイトの1つで、コメントの数が200kを超え、コメントのページナビゲーションが大幅に遅くなり始め、ランタイムが3〜5秒のクエリがmysql-slow.logにますます入り始めました。



問題は、LIMIT 100000、30を使用すると、mysqlが最初の100000レコードを最初に通過してから必要な30を選択することです。これを回避するのは非常に簡単です。フォームのサブクエリを使用します。

 SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 100000, 30) as b ON b.id = test_table.id
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

特定の例を見てみましょう。 私の場合、DLEエンジンが使用され、その中のクエリは次のようになります。

 SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id ORDER BY id desc LIMIT 101000,30
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

修正されたリクエストは次のようになります。

 SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id JOIN (select id FROM dle_comments ORDER BY id desc LIMIT 101000,30 ) as t ON t.id = dle_comments.id
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

グラフで、このような置換の結果を確認できます。





ご覧のとおり、JOINを使用すると、ページ単位のナビゲーションを使用してユーザーがサイトの荒野にどれだけ登ったかに関係なく、パフォーマンスは適切なレベルに維持されます。



PS。 コメントのDLEを修正します（すべてのナビゲーションで同じ方法を実行できます）。 comments.class.phpファイル内

見つける

 $sql_result = $this->db->query( $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この行を次のように置き換えます。

 if( $_GET['do'] == "lastcomments" ) $sql_result = $this->db->query( str_replace("ORDER BY id desc", "JOIN (select id FROM " . PREFIX . "_comments ORDER BY id desc" . " LIMIT " . $this-else $sql_result = $this->db->query( $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages ); >cstart . "," . $this->comments_per_pages .") as t ON t.id = " . PREFIX . "_comments.id",$this->query) );