データ量が多すぎるとページングクエリが遅くなります。クエリを最適化する方法はありますか?

ユーザー レコードが多すぎるため (300 万件)、ページング クエリが非常に遅くなります。ステートメントまたはインデックスを最適化するにはどうすればよいですか?最適化計画はありますか?

補足内容:
皆さん、プランをありがとうございます。とても刺激を受けましたが、私の状況には特殊であり、当てはまりません。具体的な状況は次のとおりです:
1 ユーザーが参加記録ページを開くと、最新の 10 人の参加者の記録が表示される必要があります。 (この10人の記録は、ユーザー参加記録テーブルの最新10件のデータではなく、WHERE条件を追加してフィルタリングした10件のデータです。)
2 参加者10人の最新データは、2つのデータから取得します。テーブル（ユーザ情報テーブル、ユーザ参加記録テーブル）。
3 ユーザーがデータの次のページと前のページに移動できることを確認します。
ここでのデータクエリは、2 つのテーブルを接続し、2 つのテーブルの WHERE 条件を指定して、データを制限します。これでは遅すぎるので、最適化ソリューションがあるかどうかお聞きしたいのですが。

返信内容:

ユーザー レコードが多すぎるため (300 万件)、ページング クエリが非常に遅くなります。ステートメントまたはインデックスを最適化するにはどうすればよいですか?最適化計画はありますか?

補足内容:
皆さん、企画をありがとうございます。とても刺激を受けましたが、私の状況には特殊で、あまり当てはまりません。具体的な状況は次のとおりです:
1 ユーザーが参加記録ページを開くと、最新の 10 人の参加者の記録が表示されます。 (この10人の記録は、ユーザー参加記録テーブルの最新10件のデータではなく、WHERE条件を追加してフィルタリングした10件のデータです。)
2 参加者10人の最新データは、2つのデータから取得します。テーブル（ユーザ情報テーブル、ユーザ参加記録テーブル）。
3 ユーザーがデータの次のページと前のページに移動できることを確認します。
ここでのデータクエリは、2 つのテーブルを接続し、2 つのテーブルの WHERE 条件を指定して、データを制限します。これでは遅すぎるので、最適化ソリューションがあるかどうかお聞きしたいのですが。

1. 条件を追加せずに制限を使用しないでください。制限 1000、20 は 0 ～ 10002 をスキャンします。
2. 前のページの最後のユーザーの主キーを覚えておいて、ページ分割には select * from user where uid>lastUid を使用します。
3. データの一貫性要件はそれほど高くないため、データベースの中央にキャッシュ層を追加することを検討してください

リーリー

リーリー

注意タイプ 1 つは範囲で、もう 1 つはすべてです

要約: なんとも辛い現実ですね~

MySQL LIMIT ページングの最適化 (id は Posts テーブルの自動インクリメントされる主キー):
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 10000
10020 行をスキャンし、最初の 10000 行をスキップし、最後の 20 行を返します。これは遅いです
より良い設計は次のとおりです。特定のページを区切るには、数値を「次のページ」ボタンに置​​き換えます。
各ページに 20 レコードが表示されると仮定すると、クエリを実行するたびに、LIMIT は 21 レコードを返し、20 件のみが表示されます。
21 番目の項目が存在する場合、 「次のページ」または「AJAX Load More」ボタンが表示されます。

ページングには「前ページ」と「次ページ」を使用します:
前のページ (id: 60~41)
現在のページ (id: 40~21)
次のページ (id: 20~01)

前のページ (新しい記事 20 件):
//page.php?id=40 正の数字は前のページの新しい記事を示し、ここでの 40 は現在のページのトップ記事の ID を示します。
SELECT * FROM posts WHERE id > 40 ORDER BY id ASC LIMIT 20;
ここで得られるのは昇順の ID シーケンスです。PHP は array_reverse を使用して配列を反転し、降順の出力を実現します。

次のページ (古い記事 20 件):
//page.php?id=-21 負の数字は次のページの古い記事を表し、ここでの 21 は現在のページの下部にある記事の ID を表します。
SELECT * FROM posts WHERE id < 21 ORDER BY id DESC LIMIT 20;
負の数値は見栄えが悪いと思います。その場合は、page.php?id=21&next

などのパラメータを追加できます。

個人的には、これはページングクエリの最適化であり、2つのテーブルを結合した後の単なるページネーションであると思います

一般的なページネーションの最適化は次のとおりです:

通常の書き込み:

リーリー

制限 M、N の通常のページめくり方法では、さらに後ろにページをめくると遅くなります。その理由は、mysql がテーブル内の最初の M+N データを読み取るためであり、M が大きいほどパフォーマンスが低下します。

最適化されたライティング:

リーリー

tテーブルのselleridフィールドにインデックスを作成する必要があります。idはテーブルの主キーです

如果id主键是自增且连续的（未出现删除记录）那么可以改用where between

假设user_id是自增主键

SELECT * FROM user
WHERE user_id >= (
SELECT user_id FROM user
LIMIT pageIndex, 1
)
LIMIT pageSize;

参考：https://mp.weixin.qq.com/s?__biz=MjM5NzMyMjAwMA==&mid=2651477279&idx=1&sn=4a799a4aa2f8563d482973e262e906b8&scene=1&srcid=0629Qe4RKqmpBhwOUzFJeK1B&key=77421cf58af4a65302d0d5987b2f5d1610469f10ba9f2ac129747c2578d1a68463e33f8c7f388537de80a7bc38d00c05&ascene=0&uin=ODYxODM1MDE4&devicetype=iMac+MacBookAir6%2C2+OSX+OSX+10.9.5+build(13F34)&version=11020012&pass_ticket=aZDru5jCHRXbpBvmRG0m%2BkUwb4sJZ2hIfzTt6LDC1xnW2ZMj0mgDjwuyZgisQJxA

                            
            

                                    
请根据自己的查询条件定义where子句，同时不要反复count记录数量。
1- 记忆当前页的最后一条记录，下次查询的时候根据order by添加对应的where子句代替跳过N行
2- 数据量大的时候总共多少页在实际使用的时候已经失去意义，但是count的消耗却很大，确实需要的话首次查询时获得一次就好了。
                            
            

                                    

同意楼上的意见
1.完善搜索及索引建立
2.设计数据表默认排序作为排序规则，降低查询排序耗时
3.在id为有序数字情况下，利用最后一次id作为下一次检索条件，极大降低返回数据集
4.减少count的次数和精度，超大数据可以缓存count值
5.更大型数据可根据分页规则、类型等对数据表进行拆分，降低单次查询数据量

                            
            

                                    
1 你减少sql语句的函数使用
2 减少order by 和group by 
3 对查询条件的字段添加索引
4 300w的记录应该操作记录，不是实时需要展示的数据，那么可以做数据缓存。
5 js使用$(obj).load("url #id") 进行ajax局部刷新翻页，这样可以避免你的资源重新加载
我知道的就这么多了
                            
            

                                    
按我的理解你的情况是限制条件在用户参与记录表中，而用户信息表实际上只要按前一个表的id取数据就可以了。我估计你索引已经做了，剩下能有大的改观的就是数据分区和预排序，参与记录表按主要查询条件，用户信息表按id分区。然后参与记录表一定要预排序，如果到查询时再排序，再怎么样也快不起来。
                            
            

                                    
增加where条件 主键>0 然后只取主键，这个是分页用，取内容用 where IN 直接取主键，比你查的快多了
                            
            

                                    
把某个用户的信息缓存，分页查询就查用户参与记录表
                            
            

                                    
两张表关联，而且显示最新参与的10个人的记录。这样关联的时候，查询的sql会现的复杂。所以建议通过两条sql+php的遍历实现。查询需要的个人记录一个sql，查询会员一个sql，然后遍历数组生成需要的数组。总之就是将复杂的sql拆分成简单的sql
                            
            

                                    
建立合适的索引
查询先分页主键，然后通过主键结果查内容 曾经做过7kw的日志数据查询，分页很快的。
                            
            

                                    
对数据实效性要求不高的话 可以用sphinx做查询
                            
            

                                    
limit 基数比较大时使用 between , 取比较后面的数据时用 desc 反向查找 .
                            
            

                                    
可以写个定时脚本，10分钟跑一次这样的，将取到的数据保存起来，用户打开页面时就从这个表里取。只是展示最新参与的话，来个几分钟的延迟应该是可以接受的

                                    
まず SQL に最適化の余地があるかどうかを慎重に検討し、次にデータベースのチューニング、キャッシュ、読み取りと書き込みの分離を検討します