更新时间:2024-09-11 GMT+08:00
分享

使用方法

表1 参数说明

参数名称

级别

描述

optimizer_switch

Global,Session

查询优化的总控制开关。

其中,计算下推的子控制开关如下:

offset_pushdown:LIMIT OFFSET下推优化开关,默认值为OFF。

  • ON:开启LIMIT OFFSET下推优化开关。
  • OFF:关闭LIMIT OFFSET下推优化开关。

除了使用上述特性开关来控制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)

相关文档