SQL tuning methods and practices

Read an EXPLAIN output

Before applying any tuning method, understand what the key operators in an EXPLAIN output mean:

Push down more computations

PolarDB-X 1.0 pushes computations down to the storage layer MySQL, reducing data transferred over the network and cutting overhead at the PolarDB-X 1.0 layer. Pushdown applies to the following operators:

• Filter conditions in WHERE or HAVING

• Aggregation operators such as COUNT and GROUP BY (split into two phases)

• Sorting operators such as ORDER BY

• JOIN and subqueries — the shard keys on both sides must match, or one side must be a broadcast table

Example: the following query joins customer (sharded by c_custkey) and nation (sharded by n_nationkey). Because the JOIN keys c_nationkey and n_nationkey use different sharding dimensions, the JOIN cannot be pushed down. The execution plan shows BKAJoin, which fetches rows from the right table in batches. When the left table is large, this results in many round trips and slow performance.

> EXPLAIN SELECT * FROM customer, nation WHERE c_nationkey = n_nationkey AND n_regionkey = 3;
Project(c_custkey="c_custkey", c_name="c_name", c_address="c_address", c_nationkey="c_nationkey", c_phone="c_phone", c_acctbal="c_acctbal", c_mktsegment="c_mktsegment", c_comment="c_comment", n_nationkey="n_nationkey", n_name="n_name", n_regionkey="n_regionkey", n_comment="n_comment")
  BKAJoin(condition="c_nationkey = n_nationkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="nation", shardCount=2, sql="SELECT * FROM `nation` AS `nation` WHERE (`n_regionkey` = ?)")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT * FROM `customer` AS `customer` WHERE (`c_nationkey` IN ('?'))")

The nation table is small and rarely updated, so it is a good candidate for a broadcast table. Rebuild it with the BROADCAST keyword:

CREATE TABLE `nation` (
  `n_nationkey` int(11) NOT NULL,
  `n_name` varchar(25) NOT NULL,
  `n_regionkey` int(11) NOT NULL,
  `n_comment` varchar(152) DEFAULT NULL,
  PRIMARY KEY (`n_nationkey`)
) BROADCAST;  -- Declare as a broadcast table

After the change, the JOIN is eliminated from the execution plan. All computations are pushed into LogicalView and MySQL handles the join locally. The upper layer only uses Gather to collect results, significantly improving performance:

> EXPLAIN SELECT * FROM customer, nation WHERE c_nationkey = n_nationkey AND n_regionkey = 3;

Gather(concurrent=true)
  LogicalView(tables="customer_[0-7],nation", shardCount=8, sql="SELECT * FROM `customer` AS `customer` INNER JOIN `nation` AS `nation` ON ((`nation`.`n_regionkey` = ?) AND (`customer`.`c_nationkey` = `nation`.`n_nationkey`))")

For more information, see SQL rewrite and pushdown.

Add indexes

For slow SQL statements that are already pushed down to MySQL, add indexes on the physical table shards to speed up individual shard queries.

When the JOIN itself cannot be pushed down due to different sharding dimensions, create a global secondary index (GSI) to enable the logical table to be sharded in multiple dimensions. GSI support was added in PolarDB-X version 5.4.1.

Example: orders is sharded by o_orderkey and customer is sharded by c_custkey. The JOIN key is o_custkey, which does not match orders's shard key. The resulting cross-shard HashJoin is slow, especially when many orders match the date and price filters.

> EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders, customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-11' AND o_totalprice > 100;

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  HashJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` > ?))")

Create a GSI on orders using o_custkey as the shard key. Add o_orderdate and o_totalprice as covering columns to avoid table access:

> CREATE GLOBAL INDEX i_o_custkey ON orders(`o_custkey`) covering(`o_orderdate`, `o_totalprice`)
        DBPARTITION BY HASH(`o_custkey`) TBPARTITION BY HASH(`o_custkey`) TBPARTITIONS 4;

Use FORCE INDEX(i_o_custkey) to direct the query to the GSI. The cross-shard JOIN becomes a local IndexScan on MySQL, and covering columns eliminate the need for table access:

> EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders FORCE INDEX(i_o_custkey), customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-11' AND o_totalprice > 100;

Gather(concurrent=true)
  IndexScan(tables="i_o_custkey_[0-7],customer_[0-7]", shardCount=8, sql="SELECT `i_o_custkey`.`o_orderkey`, `customer`.`c_custkey`, `customer`.`c_name` FROM `i_o_custkey` AS `i_o_custkey` INNER JOIN `customer` AS `customer` ON (((`i_o_custkey`.`o_orderdate` = ?) AND (`i_o_custkey`.`o_custkey` = `customer`.`c_custkey`)) AND (`i_o_custkey`.`o_totalprice` > ?))")

For more information, see Use global secondary indexes and Global secondary indexes.

Optimize execution plans

This section applies to PolarDB-X 5.3.12 or later.

The query optimizer automatically generates execution plans based on statistics. When statistics are missing or stale, the optimizer may choose a suboptimal plan. Use Hints to override the optimizer's decision.

Example: for the following query, the optimizer generates a HashJoin. However, when only a handful of orders match both o_orderdate = '2019-11-15' and o_totalprice < 10, a BKAJoin (LookupJoin) is more efficient because it fetches only the matching rows from customer rather than hashing both sides.

> EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders, customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15' AND o_totalprice < 10;

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  HashJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")

Add the /*+TDDL:BKA_JOIN(orders, customer)*/ Hint to force BKAJoin:

> EXPLAIN /*+TDDL:BKA_JOIN(orders, customer)*/ SELECT o_orderkey, c_custkey, c_name FROM orders, customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15' AND o_totalprice < 10;

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  BKAJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer` WHERE (`c_custkey` IN ('?'))")

BASELINE FIX SQL /*+TDDL:BKA_JOIN(orders, customer)*/ SELECT o_orderkey, c_custkey, c_name FROM orders, customer WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15';