性能测试

• 如下SQL语句为Q1，访问主表且无谓词条件。

  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)
  ​

• 带有谓词条件的查询，如下SQL语句为Q2。访问二级索引，且包含二级索引范围条件，同时需要回表获取其他列的信息。

  mysql> EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10;
  +----+-------------+----------+------------+-------+---------------------------------+-------------+---------+------+----------+----------+----------------------------------------------+
  | id | select_type | table   | partitions | type | possible_keys                   | key         | key_len | ref | rows     | filtered | Extra                                       |
  +----+-------------+----------+------------+-------+---------------------------------+-------------+---------+------+----------+----------+----------------------------------------------+
  | 1 | SIMPLE     | lineitem | NULL       | range | i_l_partkey_suppkey,i_l_partkey | i_l_partkey | 4       | NULL | 10949662 |   100.00 | Using offset pushdown; Using index condition |
  +----+-------------+----------+------------+-------+---------------------------------+-------------+---------+------+----------+----------+----------------------------------------------+
  1 row in set, 1 warning (0.00 sec)
  ​

• 带有谓词条件的查询，如下SQL语句为Q3, 带有Order by且可以利用索引排序。

  mysql> EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10;
  +----+-------------+----------+------------+-------+---------------------------------+-------------+---------+------+----------+----------+----------------------------------------------+
  | id | select_type | table   | partitions | type | possible_keys                   | key         | key_len | ref | rows     | filtered | Extra                                       |
  +----+-------------+----------+------------+-------+---------------------------------+-------------+---------+------+----------+----------+----------------------------------------------+
  | 1 | SIMPLE     | lineitem | NULL       | range | i_l_partkey_suppkey,i_l_partkey | i_l_partkey | 4       | NULL | 10949662 |   100.00 | Using offset pushdown; Using index condition |
  +----+-------------+----------+------------+-------+---------------------------------+-------------+---------+------+----------+----------+----------------------------------------------+
  1 row in set, 1 warning (0.00 sec)
  ​

基于TPCH 10 scale的数据，针对上述的查询示例Q1、Q2、Q3。开启与关闭LIMIT OFFSET下推功能的性能对比如下。

图1 性能对比