更新时间：2023-12-21 GMT+08:00
使用方法
参数名称 |
级别 |
描述 |
optimizer_switch |
Global，Session |
查询优化的总控制开关。 其中，计算下推的子控制开关如下： offset_pushdown：LIMIT OFFSET下推优化开关，默认值为OFF。
|
除了使用上述特性开关来控制OFFSET下推功能生效或者不生效，还可以使用HINT来实现。
- OFFSET_PUSHDOWN(table_name)：生效OFFSET下推优化。
- NO_OFFSET_PUSHDOWN(table_name)：不生效OFFSET下推优化。
示例：
基于TPCH的Schema进行举例，通过特性开关打开或者使用HINT方式可以生效，执行EXPLAIN SQL查看执行计划时，Extra列会展示为Using limit-offset pushdown。
- 打开特性开关。
mysql> EXPLAIN SELECT * FROM lineitem LIMIT 10000000,10; +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------+ | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59281262 | 100.00 | Using offset pushdown | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
- 使用HINT。
mysql> EXPLAIN SELECT /*+ OFFSET_PUSHDOWN() */ * FROM lineitem LIMIT 10000000,10; +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------+ | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59281262 | 100.00 | Using offset pushdown | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT /*+ NO_OFFSET_PUSHDOWN() */ * FROM lineitem LIMIT 10000000,10; +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+ | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59281262 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
父主题： LIMIT OFFSET下推
