所在的位置: mysql >> mysql发展 >> MysqlLimit字句优化

MysqlLimit字句优化

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字段的组合分库分表使用更适合大数据的分布式数据库




转载请注明:http://www.aierlanlan.com/tzrz/2258.html