데이터 양이 너무 많아 페이징 쿼리가 느려집니다. 쿼리를 최적화할 수 있는 방법이 있습니까?-PHP 튜토리얼-php.cn

사용자 레코드가 너무 많아(300만) 페이징 쿼리가 매우 느려집니다. 명령문이나 인덱스를 최적화하는 방법은 무엇입니까? 최적화 계획이 있나요?

보충 내용:
여러분의 계획에 감사드립니다. 많은 영감을 받았지만 제 상황에서는 매우 특별하고 적용되지 않습니다. 구체적인 상황은 다음과 같습니다.
1 사용자가 참여 기록 페이지를 열면 가장 최근에 참여한 10명의 기록이 표시되어야 합니다. (이 10명의 기록은 사용자 참여 기록 테이블의 최신 10개 데이터가 아니라 WHERE 조건을 추가하여 필터링된 10개의 데이터입니다.)
2 두 테이블에서 참여자 10명의 최신 데이터를 가져옵니다. (이용자 정보표, 이용자 참여기록표)를 획득한다.
3 사용자가 데이터의 다음 페이지와 이전 데이터 페이지로 넘길 수 있는지 확인하세요.
이제 데이터 쿼리는 두 테이블의 조건이 WHERE인 두 테이블을 연결한 다음 데이터를 제한하는 것입니다. 너무 느린데 혹시 최적화 솔루션이 있는지 여쭤보고 싶습니다.

답글 내용:

사용자 레코드가 너무 많아(300만) 페이징 쿼리가 매우 느려집니다. 명령문이나 인덱스를 최적화하는 방법은 무엇입니까? 최적화 계획이 있나요?

1. 조건을 추가하지 않고 제한을 사용하지 마세요. 제한 1000과 20은 0~10002까지 검색됩니다.
2. 이전 페이지의 마지막 사용자 기본 키를 기억하고 uid> paging.lastUid 페이지 크기 제한
3. 데이터 일관성 요구 사항이 높지 않으므로 데이터베이스 중간에 캐시 계층을 추가하는 것이 좋습니다

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

로그인 후 복사

데이터 양이 너무 많아 페이징 쿼리가 느려집니다. 쿼리를 최적화할 수 있는 방법이 있습니까?

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

로그인 후 복사

데이터 양이 너무 많아 페이징 쿼리가 느려집니다. 쿼리를 최적화할 수 있는 방법이 있습니까?

참고유형 하나는 범위이고 다른 하나는 전체

요약: 뼈아픈 깨달음~

MySQL LIMIT 페이징 최적화(여기서 id는 게시물 테이블의 자동 증가 기본 키):
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 10000
10020개 행을 스캔하고 처음 10000개 행을 건너뛰고 마지막 20개 행을 반환합니다.
더 나은 디자인은 특정 페이지 수를 "다음 페이지" 버튼으로 바꾸는 것입니다.
각 페이지에 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

와 같은 추가 매개변수를 추가할 수 있습니다.

개인적으로는 이것이 두 테이블을 조인한 후 페이지를 매기는 페이징 쿼리 최적화라고 생각합니다

일반적인 페이지 매김 최적화는 다음과 같습니다.

일반 글쓰기:

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

로그인 후 복사

일반적인 제한 M, N 페이지 넘김 방법은 페이지를 더 뒤로 넘길수록 속도가 느려집니다. 그 이유는 mysql이 테이블의 첫 번째 M N 데이터를 읽을수록 성능이 저하되기 때문입니다.

최적화된 글쓰기:

<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></p>

로그인 후 복사

参考：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에 최적화할 여지가 있는지 주의 깊게 검토한 다음 데이터베이스 튜닝, 캐싱, 읽기 및 쓰기 분리를 고려하세요.