更新时间:2024-09-06 GMT+08:00

性能测试

  • 如下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 性能对比