Best practices and optimization for analytical scenarios

Schema design

Schema design involves selecting the optimal table type, partition key, primary key, and clustered index to maximize table performance.

Use partitioned tables or broadcast tables

1. A broadcast table stores a full copy of its data on every data node in the cluster. Best practice: Keep the data volume in a broadcast table small, with no more than 200,000 rows per table. This allows join operations between a large table and a broadcast table to be pushed down to the data layer for processing, which avoids pulling large amounts of data to the compute nodes.

2. For other business data, use a partitioned table whenever possible to fully leverage the query capabilities of a distributed system. In theory, more partitions lead to better performance because multiple partitions can be scanned in parallel. This also simplifies horizontal scaling for the storage layer, which can then store tens or hundreds of millions of records. In practice, we recommend that each partition contain between 5 million and 50 million rows.

Choose an appropriate partition key

Design subpartitions effectively

When dealing with large data volumes or data skew, selecting the right subpartition is critical. Poorly designed or missing subpartitions in large tables can degrade performance. PolarDB-X in DRDS mode supports the subpartition feature. If your business requires incremental data imports and mainly runs reports on recent data, we recommend that you use a date-based column to create a subpartition. This helps avoid scanning historical or expired data.

// Create a primary partition on the col column.
PARTITION BY HASH(col) 
SUBPARTITION BY LIST (ds) 
// Create a subpartition by month based on the ds column.
SUBPARTITION TEMPLATE (
   PARTITION p1 VALUES LESS THAN ('2021-08-00'),
   PARTITION p2 VALUES LESS THAN ('2021-09-00'),
)

Design indexes properly

If you have already designed a suitable partition key based on join columns, but some complex queries still involve joins on other columns, a join pushdown is not possible. In this case, consider creating a global secondary index (GSI) on the non-partition key column. This transforms the query to use a join with the GSI instead of the original table. To avoid the overhead of a table lookup, we recommend that you create all GSIs as a clustered index for analytical scenarios.

Query optimization

In analytical scenarios that involve large datasets and can tolerate some latency for simple queries, we recommend that you use the massively parallel processing (MPP) mode. This mode utilizes the resources of multiple compute nodes (CNs) to handle complex computations. By default, MPP is enabled only on a read-only instance. If you need to run analytical workloads on your primary instance, contact Alibaba Cloud technical support.

During a query, PolarDB-X uses its optimizer to select an appropriate distributed execution plan and then dispatches the plan to compute nodes, using the cluster's full computational power to accelerate the query. The optimizer's choice of a distributed execution plan is entirely cost-based and relies on statistics. Therefore, collecting timely statistics is crucial. Since the plan generated by the optimizer may not always be optimal, this article also provides best practices for writing and optimizing SQL queries.

Collect statistics

If a distributed execution plan from PolarDB-X is not optimal, you can manually collect statistics for a specific table by using the ANALYZE TABLE command. PolarDB-X automatically collects statistics on a regular basis.

Follow SQL writing best practices

• Remove unnecessary columns

  Analytical queries are often high-throughput, so you should remove unnecessary columns from the result set to reduce network bandwidth pressure. When you write SQL queries, specify only the columns that your business requires instead of using a wildcard (*) to select all columns.

  // Inefficient query
  select * from T1 where a1>100 and a2<1000;
  // Better query, returns only the required columns
  select a1, a2 from T2 where a1>100 and a2<1000;

• Filter data by using a local secondary index (LSI)

  Many analytical use cases benefit from creating a subpartition on a time-based column. When you scan large amounts of data, you can use this column as a filter condition to exclude most of the historical data.

  select a1,c2 from T1 where time >='2010-01-01 00:00:00';

  To avoid a full table scan, a local secondary index (LSI) is created on this partition column by default. Similarly, for other high-throughput scanning scenarios, consider creating an LSI based on your filter conditions.

• Avoid inefficient SQL syntax

  If a table contains a very large number of records, scanning can be slow, which directly leads to slow queries. Pay attention to the following points when you write SQL queries:

  • Avoid invalidating indexes

    1. Do not perform operations on an indexed column, such as calculations, functions, or automatic or manual type conversions. These operations can invalidate the index, which results in a full table scan.

       explain execute select * from staffs where name= 'hu';
       +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
       | id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
       +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
       |  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |      100 | NULL  |
       +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
       1 row in set , 1 warning (0.00 sec)
       
       // Operations on the indexed column cause the index to become invalid.
       explain execute select * from staffs where left(name,4)= 'hu';
       +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
       | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
       +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
       |  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  198 |      100 | Using where |
       +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
       1 row in set , 1 warning (0.00 sec)

    2. Using inequality operators (!= or <>) prevents the use of an index and leads to a full table scan.

    3. IS NULL and IS NOT NULL conditions also prevent the use of an index.

       explain execute select * from staffs where name is null ;
       +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
       | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
       +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
       |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
       +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
       1 row in set

    4. A LIKE condition with a leading wildcard (%) in MySQL causes the index to be ignored, which results in a full table scan.

       explain exeucte select * from staffs where name like '%hu' ;
       +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
       | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
       +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
       |  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  198 |    11.11 | Using where |
       +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
       1 row in set
       
       explain execute select * from staffs where name like 'hu%' ;
       +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
       | id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
       +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
       |  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | NULL |    1 |      100 | Using index condition |
       +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
       1 row in set

  • Minimize the use of LIKE. LIKE operations are generally inefficient. Use range conditions, such as BETWEEN ... AND ..., whenever possible.

  • In multi-table join scenarios:

    1. Always try to include a condition on the partition column. If this is not possible, use the WHERE clause to filter out as much data as possible.

    2. In an OUTER JOIN, the ON and WHERE clauses have different scopes. The ON clause is applied during the join process, while the WHERE clause is applied to the result of the join. Therefore, place any conditions that can filter data early in the join process in the ON clause or in a subquery on the joined table. This reduces the amount of data in the original tables that needs to be joined.

Check for and handle data skew

Execution strategy