By Shengyu
For databases, the correct selection of indexes is a basic requirement. If the index is selected incorrectly, the query will be slow, and if the index is selected incorrectly, the overall database will be unavailable. There are many different indexes in PolarDB-X, including local indexes, global indexes, columnar indexes, and archive table indexes.
● Local indexes are commonly used indexes on standalone databases to avoid full table scans.
● Global index is an index table that uses a partition key different from that of the primary table to avoid full shard scans and a redundant copy of data.
● Columnar index is a column-oriented replica of the primary table and provides HTAP capabilities.
● Archive table index is a Leblon filter on the archive table that provides a certain TP query capability for the archive table.
This article mainly introduces a local index algorithm on CN: XPlan index selection.
The PolarDB for Xscale (PolarDB-X) contains the compute node (CN) data node (DN). The CN parses, optimizes, and executes SQL statements. The DN node is responsible for data persistence. The CN communicates with the DN through RPC. DN is 100% compatible with MySQL and is also sold as a PolarDB-X Standard Edition.
The content of RPC communication between CN and DN is actually standard SQL statements. CN will convert the parsed and optimized syntax tree into SQL statements and send it to DN for re-parsing and optimization. It may sound better to pass the CN's syntax tree directly to the DN for execution [1].
However, this may not be a good choice. The main reason is the storage-compute separation architecture. Data is on DN, and DN can directly perform index dive on data, while CN statistics are static data sampled and are not updated in time. Therefore, cardinality estimation is not as accurate as DN, resulting in inferior index selection accuracy compared with DN. In many scenarios, the cost of DN parsing optimization saved is far less than the consequences of wrong index selection.
However, for the user's core point query scenario, twice optimizations become a bottleneck, so PolarDB-X provides the XPlan mechanism: for the point query scenario, the execution plan is directly transmitted to DN for execution.
This means that XPlan is not a necessary ability, but the icing on the cake. Currently, the applicable scope of XPlan is limited to the DQL of a single table. Only Scan, Filter, and Project operators are supported.
XPlan has improved by more than 10% in Sysbench point check, but slow query problems caused by wrong selection of XPlan index frequently occur online in the real cases. For PolarDB-X, there are two possibilities for wrong indexes: cardinality estimation errors and skewed indexes under the execution plan cache.
Three common causes of cardinality estimation errors are missing statistical information, skewed data, and associated columns. These problems are still unsolved after decades of study by the academia and industry [2]. However, they are easy to detect. PolarDB-X's basic strategy is to disable XPlan when these problems are detected and leave it to DN for local index selection. It is also easy to find that the index is misselected. Through pre- and post-detection, the probability of XPlan misselection can be minimized.
The following figure shows the general process of SQL PolarDB-X optimizer: after RBO and CBO, the best single-machine execution plan is generated, and whether the current query is AP query, this is judged based on the cost of the optimal execution plan generated by CBO. If it is not AP query, the single-machine execution plan is directly constructed, otherwise, further consideration is given to whether the column store index can be used.
If the column store index cannot be used, insert the shuffle operator to construct a distributed execution plan based on the optimal single-machine execution plan. Otherwise, the optimal distributed execution plan is constructed based on the column store index.
Local indexes, global indexes, and archive table indexes are selected in the CBO. The selection of local indexes affects the I/O cost of the Logicalview operator. The selection of global indexes replaces the execution plan for scanning the primary table with the global index back table. The selection of archive table indexes can replace the scan of archive tables with complex filter conditions that cannot be indexed with multiple scans of archive tables with simple indexes. Columnar index selection is the use of column-oriented storage to regenerate the optimal distributed execution plan for AP queries.
XPlan index selection is performed in the final Logicalview of the standalone optimizer. This is different from the local index selection in CBO. The local index selection in CBO only affects the I/O cost of the Logicalview operator and then the cost of the entire execution plan. It is the process by which CN simulates DN to make index selections based on its own statistical information. It is not the index actually used by DN. Only XPlan will specify the index of DN.
The following is the general logic for obtaining the execution plan of the PolarDB-X
getPlan(String sql)
if PlanCache doesn't contain sql :
PlanCache.put(sql, getPlanByOptimizer(sql))
Plan = PlanCache.get(sql)
if PlanManager contiain sql :
Plan = PlanManager.choose(sql)
return PlanAll execution plans are cached in PlanCache. If PlanManager has execution plans, PlanManager selects the execution plan with the lowest cost.
In the article PolarDB-X Optimizer Core Technology: Execution Plan Management, the concepts of Optimize Once and Optimize Always are mentioned. PolarDB-X uses the concept of Optimize Once and minimizes access to the optimizer. The main consideration is that the structure of the PolarDB-X optimizer is quite complex. If you use Optimize Always, the time-consuming cost of the optimizer cannot be ignored in highly concurrent TP queries.
Here's a review of the common problems with parametric queries. Consider the following scenario:
create table hot_select (
id int not null,
c_int int,
c_varchar varchar(20),
PRIMARY KEY (`id`),
KEY i_int(c_int),
KEY i_varchar(c_varchar)
)
select * from hot_select where c_int = 1 and c_varchar = 'a';
select * from hot_select where c_int = 2 and c_varchar = 'a';If there is one row of data meeting the condition c_int = 1, 100 rows of data meeting the condition c_varchar = 'a', and 10,000,000 rows meeting the condition c_int = 2, the first query should use the index i_int, and the second query should use the index i_varchar.
However, the two queries share the same SQL template, and the same SQL template will only Optimize Once. Both SQL will only go i_int, resulting in the second query actually going to the wrong index.
The academia has proposed many solutions to this problem [3]. PolarDB-X has previously verified some of the solutions in the paper online and designed a set of feedback and evolution mechanisms as shown in the following figure. Due to the erratic execution plan, RT is unstable, and finally, the feedback evolution feature is shut down. TiDB has also made a similar attempt, which now is also forced to shut down.
The design of XPlan index selection is based on the premise of incurring no negative optimization. The scheme adopted by PolarDB-X is somewhat similar [4], except that XPlan will not consider the expected cardinality, but the maximum cardinality.
Of course, the same problem also appears in the global index selection, but due to the necessity of global index selection, XPlan's scheme is not applicable. PolarDB-X has a different set of schemes to deal with the skew value of the global index, which will be further expanded in subsequent articles.
There are two core problems with XPlan: how to select indexes and how to perform execution plan transfer and execution. The general logic of execution plan transmission and execution is shown in the following figure: filter is pushed down as much as possible on the operator tree. filter-XplanScan pattern is used for index selection and recorded in XplanScan. Protobuf is filled based on the operator tree, and InnoDB data is directly read and filtered after being transmitted to DN by private protocol for parsing.
Since the main purpose of this article is XPlan index selection rather than XPlan, this topic is no longer expanded and we will focus on how to make XPlan index selection.
XPlan index selection minimizes the probability of wrong selection, as shown in the following figure.
1) First of all, check whether the statistics of the current table have expired. As the statistics may not be updated automatically for various reasons, index selection without statistics is random guessing. Therefore, XPlan will be disabled after the statistics information expires. A small optimization is that queries of pk and uk will not be affected by this. Statistics expires every 7 days. The kernel automatically checks and collects statistics that have not been updated for 3 days every day, and then checks the statistics again. A kernel alarm is issued if there are still tables that have not been updated for more than 3 days. This judgment reduces cardinality estimation errors caused by missing statistical information.
2) The second step is to filter possible skewed indexes. The statistics module provides the ability to check whether a given column set has skewed values. The indexes of skewed columns will not be used by XPlan. This filter reduces the skew value problem caused by Plan Cache. Associated column estimation errors are generally because the cardinality estimate is too small, which is caused by the iterated selectivity multiplication based on the independence assumption between columns. If the skewed columns are filtered, the too-small cardinality estimate caused by the associated columns will not happen.
3) The third step uses the cardinality estimate module to select the index with the best selectivity. Only indexes good enough can go to XPlan. Since XPlan is a Robust Query Optimization and does not select the best index, it may not select a good index. In this case, XPlan will also be disabled directly.
Finally, the selected index is recorded in XplanScan, and the index selection of this XPlan is completed.
Consider the previous example again. Due to the skew of c_int, XPlan will no longer select i_int but i_varchar, thus avoiding the skew value problem.
create table hot_select (
id int not null,
c_int int,
c_varchar varchar(20),
PRIMARY KEY (`id`),
KEY i_int(c_int),
KEY i_varchar(c_varchar)
)
select * from hot_select where c_int = 1 and c_varchar = 'a';
select * from hot_select where c_int = 2 and c_varchar = 'a';The skew value is also called skew data. In XPlan scenarios, you only need to consider whether the combination of prefix columns of all indexes is skewed.
In PolarDB Distributed Edition, 100,000 rows of data are collected from a table. If the sampling frequency is greater than 5 and the ratio of sampling frequency to sample rate is greater than 10,000, the data is judged to be skewed. The logic of this skew value judgment still needs improvement, and the stability against the sample is not strong enough, but for now, it can achieve the desired effect.
Then, the algorithm is simple. Exhaustively enumerate all the prefix columns of n indexes, and judge whether the maximum frequency in the 100,000 rows of the sample meets the above conditions. If the average number of columns in the index is m, the time complexity is O (1e5*nm), which is negligible. Of course, there are finer optimizations. For example, the prefixes of skewed columns must be skewed columns, and a larger set of columns is prioritized for subsequent pruning.
PolarDB-X uses the block sampling [5] to sample data. The primary key of InnoDB is set to Random Walk to create some pages. The primary key is naturally skewed (especially composite primary keys). Therefore, the prefix of the primary key does not do the skew value judgement.
In view of the fact that the index dive capability of DN has better performance for the estimation of a single table, the final strategy selected by PolarDB-X is that DN returns the number of rows scanned by XPlan on InnoDB. Once CN finds that the number of rows scanned by XPlan on the index exceeds the threshold, CN closes XPlan of the current SQL template and issues an alert.
In the next 12 hours, the corresponding sql templates will no longer use XPlan. This simple mechanism is also effective for databases with only Plan Cache: if you find that the query of Plan Cache is extremely slow, you can disable Plan Cache for this template.
PolarDB-X supports the EXPLAIN EXECute syntax to view the physical indexes of a data key (DN). For XPlan, explain execute will transfer the context of XPlan to the executor and intercept it before issuing physical SQL. Otherwise, XPlan will be set in the context of XPlan and return to the normal physical SQL path.
Due to the fallback mechanism, explain execute may be different from the status of online problems, which makes troubleshooting more difficult. Therefore, the index of each XPlan and the number of rows scanned on InnoDB are recorded in the log.
The following figure shows the average daily occurrence rate of XPlan alerts for different versions of instances in the last half month.
In the optimized version of XPlan where the index selection logic is changed, the probability of XPlan index misselection for instances every day is reduced from 5% to 0.1%, which is 1/50 of the original rate. Note that users can close XPlan after selecting the wrong index in the old version of XPlan, so the real probability of misselection will only be higher.
The main reason for the decrease in the probability of alert rate is not that the optimizer can choose the right index, but that the optimizer can not choose the wrong index.
This article details PolarDB-X index selection scheme that specifically optimizes XPlan for point-check scenarios.
For the PolarDB-X optimizer architecture and a variety of index choices involved in it, the index misselection problem faced by XPlan and the cardinality estimation error, and the skew value problem caused by the execution plan cache mechanism, an algorithm that can detect and avoid misselection in advance is specifically designed. In addition, monitoring and alert mechanism, the final mechanism after misselection, and good observability are also provided. This significantly reduces the probability of XPlan index misselection.
Of course, the universality of XPlan, the stability of skew value judgment, and the ability to estimate correlation columns can be further optimized.
[1] Assembling a Query Engine From Spare Parts https://www.firebolt.io/content/firebolt-vldb-cdms-2022
[2] Efficient Query Re-optimization with Judicious Subquery Selections https://arxiv.org/pdf/2202.12535.pdf
[3] Robust Query Optimization Methods With Respect to Estimation Errors: A Survey https://dl.acm.org/doi/10.1145/2854006.2854012
[4] Towards a Robust Query Optimizer: A Principled and Practical Approach https://dl.acm.org/doi/10.1145/1066157.1066172
[5] A Survey of Data Partitioning and Sampling Methods to Support Big Data Analysis https://ieeexplore.ieee.org/document/9007871
PolarDB-X Optimizer Core Technology: Execution Plan Management
ApsaraDB - August 7, 2023
ApsaraDB - October 24, 2023
ApsaraDB - January 3, 2024
ApsaraDB - October 27, 2023
ApsaraDB - November 21, 2023
ApsaraDB - August 8, 2023
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
PolarDB for Xscale
Alibaba Cloud PolarDB for Xscale (PolarDB-X) is a cloud-native high-performance distributed database service independently developed by Alibaba Cloud.
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
Database for FinTech Solution
Leverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by ApsaraDB
