大量のデータ量での MySQL 最適化ページング戦略

1つ。まえがき

通常、ページングは​​どのように実装するのでしょうか?

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

しかし、データ量が劇的に増加するとどうなるでしょうか?

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

上記の 2 番目のクエリは非常に遅く、死ぬほど遅くなります。

最も重大な理由は、mysql クエリ メカニズムの問題です。

は、最初にスキップしてからクエリを実行するのではなく、最初にクエリを実行してから

を実行します。 (以下説明)

とはどういう意味ですか?たとえば、limit 100000,10 と指定すると、必要な 10 項目が見つかった場合、最初の 100,000 項目のフィールド データが取得され、不要であることが判明した場合は破棄されます。ようやく必要な10項目が見つかりました。

2つ。分析

制限オフセットN、オフセットが非常に大きい場合、効率は非常に低くなります

その理由は、mysqlがオフセット行をスキップせず、N行のみを取るためです
が、オフセット + N 行を受け取り、諦める前にオフセット行を返し、N 行を返します [上記と同じ、最初にクエリを実行してからスキップ]
オフセットが大きいと効率が低くなります
。 3つ。 3 つの最適化提案

1:ビジネスの観点から解決する

方法:100ページ

Baidu を利用する例として

、大体70ページくらいのページをめくります例

:

mysql> select id, from lx_com limit 5000000,10;
+---------+--------------------------------------------+
| id      | name                                       |
+---------+--------------------------------------------+
| 5554609 |温泉县人民政府供暖中心          |
..................
| 5554618 |温泉县邮政鸿盛公司                |
+---------+--------------------------------------------+
10 rows in set (5.33 sec)
 
mysql> select id,name from lx_com where id>5000000 limit 10;
+---------+--------------------------------------------------------+
| id      | name                                                   |
+---------+--------------------------------------------------------+
| 5000001 |南宁市嘉氏百货有限责任公司                |
.................
| 5000002 |南宁市友达电线电缆有限公司                |
+---------+--------------------------------------------------------+
10 rows in set (0.00 sec)

から。 5.3秒以上100 ミリ秒では、クエリ速度が大幅に高速化されますが、データの結果は異なります

利点: where 条件を使用してドロップアウトを回避します

最初にクエリを実行し、その後 の質問をスキップします。代わりに、 条件によって範囲が狭まり、スキップされます。直接。 問題があります: この方法と、limitM、N、 回の 2 回の使用結果が矛盾していることが時々見つかります (上記の例に示すように)理由: データは物理的に削除されています、穴があります

。

解決策

:

データは物理的に削除されていません

(

論理的に削除できます)。

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

(一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 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）！