대용량 데이터에서 MySQL 최적화-페이징 전략

하나. 머리말

보통 페이징은 어떻게 구현하나요?

아아아아

그런데 데이터 양이 급격하게 늘어나면 어떻게 될까요?

SELECT * FROM table ORDER BY id LIMIT 1000, 10;

위의 두 번째 쿼리는 매우 느리고 멈춰서 죽을 것입니다.

가장 중요한 이유는 mysql 쿼리 메커니즘의 문제입니다.

먼저 건너뛰고 나중에 쿼리하는 것이 아닙니다. >

대신 먼저 쿼리하고 나중에 건너뛰세요. (아래 설명)

은 무슨 뜻인가요? 예를 들어, 필요한 10개 항목이 발견되면 처음 100,000개 항목이 폴링됩니다. 먼저 처음 100,000개 항목의 필드 데이터가 검색된 다음 쓸모 없는 것으로 확인될 때까지 삭제됩니다. 드디어 필요한 10개의 항목이 발견되었습니다.

둘. 분석

limit offset,N, offset이 매우 크면 효율이 극도로 낮다.
이유는 mysql이 그렇지 않기 때문이다. 오프셋 행을 건너뛰고, 그런 다음 N 행을 가져옵니다.
오프셋+N 행을 취하고, 포기하기 전에 오프셋 행을 반환하고, N 행을 반환합니다. [위에서 언급한 것과 동일, 먼저 쿼리한 다음 건너뜁니다. ].
오프셋이 클수록 효율이 떨어진다

셋. 3가지 최적화 제안

1:비즈니스 관점에서 해결

방법:넘기기 불가 100페이지

바이두를 예로 들면 , 일반적으로 70페이지 정도 정도로 페이지를 넘깁니다.

2:필요 없음오프셋, 조건 쿼리.

예 :

SELECT * FROM table ORDER BY id LIMIT 1000000, 10;

현상: 5.3초에서 100밀리초 미만으로 쿼리 속도가 크게 빨라졌지만 데이터 결과는 다릅니다

장점: 사용 where 피해야 할 조건 먼저 쿼리한 다음 의 질문을 건너뜁니다. 대신 조건은 범위를 좁히고 직접 건너뜁니다. [ 위 예시와 같이 ]

원인:데이터가 물리적으로 삭제되었습니다,구멍이 있습니다.

해결책:데이터는 물리적으로 삭제되지 않습니다(논리적으로 삭제 가능).

最终在页面上显示数据时,逻辑删除的条目不显示即可.

(一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1)

3:延迟索引.

非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办?

优化思路：

利用索引覆盖，快速查询出满足条件的主键id;然后凭借主键id作为where条件，达到快速查询。

（速度快在哪里？利用索引覆盖不需要回行就可以快速查询出满足条件的id，时间节约在这里了）

我们现在必须要查,则只查索引,不查数据,得到id.再用id去查具体条目. 这种技巧就是延迟索引.

慢原因：

查询100W条数据的id,name,m每次查询回行抛弃，跨过100W后取到真正要的数据。【就是我们刚刚说的，先查询，后跳过】

优化后快原理：

a.利用索引覆盖先查询出主键id，在索引上就拿到信息了，避免回行

b.找到主键后，根据已知的目标主键在查询，避免跨大数据行去寻找，而是直接定位哪几条数据直接查询。

本方法即延迟索引查询。

mysql> select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);
+---------+-----------------------------------------------+
| id      | name                                          |
+---------+-----------------------------------------------+
| 5050425 | 陇县河北乡大谈湾小学                |
........
| 5050434 | 陇县堎底下镇水管站                   |
+---------+-----------------------------------------------+
10 rows in set (1.35 sec)

 

四。总结：

从方案上来说，肯定是方法一优先，从业务上去满足是否要翻那么多页。

如果业务要求，则用id>n limit m的方式来代替limit n,m,但缺点是不能有物理删除

如果非有物理删除有空缺不能用方法二，则用延迟索引法，本质是利用索引覆盖先快速取出索引值，根据锁定的目标的索引值。一次性去回行取值，效果很明显。

 以上就是Mysql优化-大数据量下的分页策略的内容，更多相关内容请关注PHP中文网（www.php.cn）！