資料量太大，分頁查詢變慢，有什麼優化查詢的方法嗎？-php教程-PHP中文網

由於使用者記錄太多（300萬），導致分頁查詢變得很慢，如何最佳化語句或索引？大家有什麼優化方案嗎？

補充內容：
謝謝大家的方案，我看的很受啟發，但結合我的這個情況來說比較特殊，不太適用。具體情況如下：
1當使用者開啟參與記錄頁面時候，要顯示最新參與的10個人的記錄。（這10個人的記錄並不是用戶參與記錄表之中最新的的那十筆數據而是加WHERE條件篩選出來的十個數據。）
2最新的參與10個人的數據是從兩個表中拿取得（使用者資訊表，使用者參與記錄表）。
3要保證用戶能翻下一頁數據，上一頁數據。
現在查資料是連接兩個表，WHERE兩個表的條件，然後limit的資料。這樣太慢了，所以想問下有什麼優化方案？

回覆內容：

由於使用者記錄太多（300萬），導致分頁查詢變得很慢，如何最佳化語句或索引？大家有什麼優化方案嗎？

1.不要在不加條件的情況下用limit，limit 1000,20會從0~10002掃描
2.記住上一頁最後一個用戶主鍵，分頁用select * from user where uid>lastUid limit pagesize
3.資料一致性要求不高，考慮應用於資料庫中間加一層快取

<code class="sql">EXPLAIN SELECT * FROM lagou where id <=100</code>

登入後複製

資料量太大，分頁查詢變慢，有什麼優化查詢的方法嗎？

<code class="sql">EXPLAIN SELECT * FROM lagou LIMIT 100</code>

登入後複製

資料量太大，分頁查詢變慢，有什麼優化查詢的方法嗎？

注意type 一個是range,一個是all

總結：多麼痛的領悟~

MySQL LIMIT分頁最佳化(其中id是posts表自增主鍵):
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 10000
掃描10020行,跳過前面的1000慢.
一個更好的設計是將具體的頁數換成"下一頁"按鈕.
假設每頁顯示20條記錄,那麼我們每次查詢的時候都是LIMIT返回21條記錄並只顯示20條.
如果第21條存在,那麼我們就顯示"下一頁"或"AJAX加載更多"按鈕.

使用"上一頁"和"下一頁"進行分頁:
上一頁(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

我個人認為你這個就是一個分頁查詢優化，只不過是兩張表join之後分頁

典型的分頁最佳化是：

普通寫法：

<code>select * from buyer where sellerid=100 limit 100000,20</code>

登入後複製

普通limit M, N 的翻頁寫法，在越往後翻頁的過程中速度越慢，原因mysql會讀取表中前M+N條數據，M越大，性能就越差

優化寫法：

<code>select t1.* from buyer t1, (select id from buyer where sellerid=100 limit 100000,20 ) t2 where t1.id=t2.id</code>

登入後複製

需要在t表中的sellerid欄位中建立索引，id為表的主鍵

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

假设user_id是自增主键

<code class="sql">SELECT * FROM user
WHERE user_id >= (
SELECT user_id FROM user
LIMIT pageIndex, 1
)
LIMIT pageSize;</code>

登入後複製

参考：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有沒有最佳化空間；再考慮資料庫調優，快取、讀寫分離的辦法