LIMIT字句常用的语法类似于:LIMITm,n,针对不同的情况,MySQL会对查询做一些优化.总的来说性能主要由以下几个条件决定:
LIMIT遍历数据量少,性能高LIMIT通过索引实现筛选,性能比较高LIMIT找到所需的数据就停止排序,性能优于先完整排序再截取数据语句整体能被索引覆盖,不需要回表,性能比较高下面分别举例说明:
普通SELECT+LIMIT
这是最简单的场景,按照存储顺序遍历所有数据,直到遍历到目标位置才停止.
createtbl6(
...
PRIMARYKEY(id),
index(col1)
)ENGINE=InnoDB
mysqlroot
localhost:test_dbselect*fromtbl6limit,10;#resultSet...
Time:0.s
mysqlroot
localhost:test_dbexplainselect*fromtbl6limit00,10;+------+---------------+---------+--------------+--------+-----------------+--------+-----------+--------+--------+------------+---------+
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra
------+---------------+---------+--------------+--------+-----------------+--------+-----------+--------+--------+------------+---------
1
SIMPLE
tbl6
null
ALL
null
null
null
null
null
+------+---------------+---------+--------------+--------+-----------------+--------+-----------+--------+--------+------------+---------+
mysqlroot
localhost:test_dbselect*fromtbl6limit00,10;#resultSet...
Time:0.s
可以看到遍历的数量少时,速度更快,LIMIT操作需要扫描的数据增加,就需要通过一些方式来有优化.
覆盖索引优化
当SELECT的字段被某个索引覆盖,由于不需要回表查询,效率少许提升.
mysqlroot
localhost:test_dbexplainselectcol1fromtbl6limit00,10;+------+---------------+---------+--------------+--------+-----------------+----------+-----------+--------+--------+------------+-------------+
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra
------+---------------+---------+--------------+--------+-----------------+----------+-----------+--------+--------+------------+-------------
1
SIMPLE
tbl6
null
index
null
idx_col1
69
null
Usingindex
+------+---------------+---------+--------------+--------+-----------------+----------+-----------+--------+--------+------------+-------------+
mysqlroot
localhost:test_dbselectcol1fromtbl6limit00,10;#resultSet...
Time:0.s
延迟索引优化
但有的时候我们需要的字段确实无法覆盖索引,那可以通过延迟索引的方式来实现.通过主键索引id的覆盖索引子查询可以加速遍历数据,最后通过join操作将索引效果延迟影响到所有非覆盖索引字段.
mysqlroot
localhost:test_dbexplainselecttbl6.*fromtbl6innerjoin(selectidfromtbl6limit00,10
)Xontbl6.id=X.id;
+------+---------------+------------+--------------+--------+-----------------+----------+-----------+--------+--------+------------+-------------+
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra
------+---------------+------------+--------------+--------+-----------------+----------+-----------+--------+--------+------------+-------------
1
PRIMARY
derived2
null
ALL
null
null
null
null
10
null
1
PRIMARY
tbl6
null
eq_ref
PRIMARY
PRIMARY
4
X.id
1
null
2
DERIVED
tbl6
null
index
null
idx_col1
69
null
Usingindex
+------+---------------+------------+--------------+--------+-----------------+----------+-----------+--------+--------+------------+-------------+
mysqlroot
localhost:test_dbselecttbl6.*fromtbl6innerjoin(selectidfromtbl6limit00,10
)Xontbl6.id=X.id;
#resultSet...
Time:0.s
索引跳表优化
但上面两种优化都需要遍历非必要数据,假设我们这里的id主键是有序递增的,我们可以通过
idn
的方式来跳过数据扫描,这对性能有显著提升.
mysqlroot
localhost:test_dbexplainselectcol1fromtbl6whereid00limit10;+------+---------------+---------+--------------+--------+-----------------+---------+-----------+--------+--------+------------+-------------+
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra
------+---------------+---------+--------------+--------+-----------------+---------+-----------+--------+--------+------------+-------------
1
SIMPLE
tbl6
null
range
PRIMARY
PRIMARY
4
null
Usingwhere
+------+---------------+---------+--------------+--------+-----------------+---------+-----------+--------+--------+------------+-------------+
mysqlroot
localhost:test_dbselectcol1fromtbl6whereid00limit10;#resultSet...
Time:0.s
ORDERBY+LIMIT
在和ORDERBY组合使用的场景下,如果SELECT字段+ORDERBY字段满足覆盖索引条件,MySQL会使用索引进行LIMIT.但如果无法满足覆盖索引的条件,MySQL会做不同的处理:
当LIMIT筛选的数据量小,依然使用ORDERBY字段对应的索引(如果有的话)做LIMIT筛选.如果LIMIT需要的数据量大,则先将符合条件的所有数据检索出来,之后再根据ORDERBY字段做文本排序,直到找到LIMIT所需的数据则停止排序.createtbl6(
...
index(col1)
)ENGINE=InnoDB
mysqlroot
localhost:test_dbexplainselectcol1fromtbl6orderbycol1limit00,10\G;#由于满足覆盖搜索引条件,数据量再大也会使用索引进行排序LIMIT
***************************[1.row]***************************
id
1
select_type
SIMPLE
table
tbl6
partitions
None
type
index
possible_keys
None
key
idx_col1
key_len
69
ref
None
rows
10
filtered
.0
Extra
Usingindex
mysqlroot
localhost:test_dbexplainselect*fromtbl6orderbycol1limit00,10\G;#数据量大,且不满足覆盖索引条件,使用文本排序,效率低
***************************[1.row]***************************
id
1
select_type
SIMPLE
table
tbl6
partitions
None
type
ALL
possible_keys
None
key
None
key_len
None
ref
None
rows
filtered
.0
Extra
Usingfilesort
mysqlroot
localhost:test_dbexplainselect*fromtbl6orderbycol1limit,10\G;#数据量小,虽然不满足覆盖索引条件,但是还会使用索引进行排序
***************************[1.row]***************************
id
1
select_type
SIMPLE
table
tbl6
partitions
None
type
index
possible_keys
None
key
idx_col1
key_len
69
ref
None
rows
filtered
.0
Extra
None
需要注意的是,如果orderby字段不唯一,实际返回的顺序由执行计划不同可能会有变化.而LIMIT字句是会影响执行计划的,所以如果使用中对返回结果的顺序有严格要求,请显式使用唯一建做orderby操作.
LIMIT0
LIMIT0会返回一个空集,这在校验SQL正确性的时候很适用.也可以作为获取应用中通过ResultSet来判断返回数据类型的一种方法.
写在最后
SQL优化跟应用场景密不可分,通常一个查询的优化总是有极限的,往往在业务上和架构上作出修改才是最优的解法,比如:
对于直接面向用户的查询,控制LIMIT分页的最大数量限制SELECT字段的组合分库分表使用更适合大数据的分布式数据库