提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

优化mysql的limitoffset的例子

mysql 优化 例子
2023-06-13 09:14:45 时间

 经常碰到的一个问题是limit的offset太高，如：limit100000,20，这样系统会查询100020条，然后把前面的100000条都扔掉，这是开销很大的操作，导致查询很慢。假设所有分页的页面访问频率一样，这样的查询平均扫描表的一半数据。优化的方法，要么限制访问后面的页数，要么提升高偏移的查询效率。

    一个简单的优化办法是使用覆盖查询（coveringindex）查询，然后再跟全行的做join操作。如：

复制代码代码如下:

SQL>select*fromuser_order_infolimit1000000,5;

这条语句就可以优化为：
复制代码代码如下:

select*fromuser_order_infoinnerjoin(selectpinfromuser_order_infolimit1000000,5)aslimusing(pin);
SQL>explainselect*fromuser_order_infolimit1000000,5;
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
|1|SIMPLE|user_order_info|ALL|NULL|NULL|NULL|NULL|23131886||
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
1rowinset(0.00sec)
SQL>explainextendedselect*fromuser_order_infoinnerjoin(selectpinfromuser_order_infolimit1000000,5)aslimusing(pin);
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
|1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|5|100.00||
|1|PRIMARY|user_order_info|eq_ref|PRIMARY|PRIMARY|42|lim.pin|1|100.00||
|2|DERIVED|user_order_info|index|NULL|PRIMARY|42|NULL|23131886|100.00|Usingindex|
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
3rowsinset,1warning(0.66sec)


根据两个explain的对比，可以清晰发现，第一个未使用索引，扫描了23131886行，第二个也扫描了同样的行数，但是使用了索引，效率提高了。这样可以直接使用index得到数据，而不去查询表，当找到需要的数据之后，在与全表join，获得其他的列。

相关文章