By Qimu
For the most part, JOIN operations are applied to relational databases through Nested-Loop Join, Hash Join, and Sort Merge Join. In online transaction processing (OLTP) scenarios, the most commonly used approach is Index Nested-Loop Join based on the index point queries, thus enabling Join to return result in a very short period of time. This is also how most developers feel about Join.
As a distributed database, PolarDB-X not only is syntactically compatible with MySQL, but also aims to maintain a consistent experience with standalone databases. Under distributed scenarios, both two tables of Join can be distributed. Therefore, the corresponding data must be obtained through multiple network requests. The question is, how can we achieve this efficiently?
Let's first see how Join is done on a standalone database. MySQL supports only a few Join algorithms:
If join key columns in any of the tables on both sides of the Join operation have indexes, MySQL usually uses BKA Join or NL Join based on indexes, which are applicable in most cases. If there are no indexes available on both sides of the Join, MySQL has to choose Block Nested-Loop Join or Hash Join as the next priority depending on MySQL versions. Today, we mainly focus on NL Join and BKA Join.
Nested-Loop Join is the simplest form of the Join operation and can be seen as a two-level For loop. For each row in the foreign tables or inner tables with cycle check (both known as the driving table), they are output as Join results if the Join conditions are met. If the join key has an index available in the inner table, then its loop can be greatly simplified by querying the index to get the rows that can be joined, without traversing the entire table. This indexed NL Join is also called Index Nested-Loop Join.
# Nested-Loop Join
for outer_row in outer_table:
for inner_row in inner_table:
if join_condition is True:
output (outer_row, inner_row)
# Index Nested-Loop Join
for outer_row in outer_table:
for inner_row in inner_index.lookup(outer_join_key):
if join_condition is True:
output (outer_row, inner_row)
In the following example, the orders table is joined with the customer table by the primary key c_custkey. Then, MySQL uses the Index NL Join to complete the Join operation.
/* Query 1 */
SELECT o_orderkey, o_custkey, c_name
FROM orders JOIN customer ON o_custkey = c_custkey
WHERE o_orderkey BETWEEN 1001 AND 1005
BKA Join is considered as a performance-optimized Index Nested-Loop Join. The reason why it is called Batched is that its implementation uses the Multi-Range Read (MRR) interface provided by the storage engine to perform index queries in batches. Moreover, it converts random indexed retrieval to sequential retrieval queries by primary key sorting, accelerating the disk I/O of index query to a certain extent.
In the following example, the join key hits a secondary index, and columns of the SELECT statement include columns that are not included in the secondary index. Therefore, indexing and retrieval need to be performed to obtain complete Join results.
/* Query 2 */
SELECT c_name, c_custkey, o_orderkey, o_totalprice
FROM customer JOIN orders ON c_cutkey = o_custkey
WHERE c_custkey BETWEEN 13 AND 15
Generally, the data volume driven by Join in OLTP queries is not large, and Join often has matching indexes. If this is the case, the costs of NL Join and BKA Join are linearly related to the data volume on the driving side, thus results can be quickly computed.
The architecture of PolarDB-X is quite different from that of MySQL, which can be divided into the SQL layer and the storage layer. The compute node in the SQL layer needs to compute the shard where the data is stored, and then pull the required data from multiple data nodes.
For Join queries, if the join key and the shard key are the same, they can be pushed down to the DN layer for execution. Otherwise, the Join operation will be performed on the CN node. PolarDB-X supports a variety of Join algorithms, including Lookup Join, Nested-Loop Join, Hash Join, Sort-Merge Join, and other execution methods. The most commonly used one in OLTP queries is the Lookup Join, which is similar to BKA Join in MySQL. This article mainly introduces Lookup Join. Other Joins such as Hash Join and Nested-Loop Join will be described in future articles.
In addition to the functional requirements of Join, the following two performance requirements should be also considered in the design of the Lookup Join statement of PolarDB-X:
The Lookup Join execution process is as follows without indexed retrieval
/* Query 1 */
SELECT o_orderkey, o_custkey, c_name
FROM orders JOIN customer ON o_cutkey = c_custkey
WHERE o_orderkey BETWEEN 1001 AND 1005
Here are some interesting details about this process. For example, when more than one column (such as X = A AND Y = B) is found to perform lookup join, how to deal with them? For this purpose, a multi-column IN condition can be formed based on row-expression. What if NULL appears under the multi-column IN condition? What are the ways to process Anti-Join? These will not be detailed here. You are welcome to exchange comments.
For the vast majority of TP queries, Lookup Join only need to be performed once, minimizing the latency.
In addition, PolarDB-X also supports global indexes. Users can create global indexes for shard tables to accelerate queries of index keys. Similar to a local index, if a query contains columns that are not covered by the index, a global index also needs to be retrieved from the table. This is exactly the same as that in the Lookup Join method described in the previous section. Indexing and retrieval can be considered as a special 1:1 JOIN operation.
The Join dependent on global indexes is more complex. Recalling BKA Join in MySQL, two times of lookup joins are required:
/* Query 2 */
SELECT c_name, c_custkey, o_orderkey, o_totalprice
FROM customer JOIN orders ON c_cutkey = o_custkey
WHERE c_custkey BETWEEN 13 AND 15
Most OLTP data queries can be completed in a single batch, with a total latency of three rounds-trip operations. It is not difficult to prove that this is the best in distributed cases.
In addition, PolarDB-X also allows users to manually add more columns to the coverage column of the global index, sacrificing some write performance for better read performance. If all columns are overwritten, there is no need to retrieve the table. Instead, just two round-trip operations are required. PolarDB-X further encourages users to push down Join through a well-designed shard key as much as possible.
Detailed Explanation of Query Rewriting Based on Materialized Views
ApsaraDB - June 3, 2021
ApsaraDB - August 15, 2024
Alibaba Cloud Product Launch - August 30, 2018
ApsaraDB - September 30, 2021
ApsaraDB - September 29, 2021
Alibaba Cloud_Academy - July 25, 2023
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn MoreAlibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAlibaba Cloud PolarDB for Xscale (PolarDB-X) is a cloud-native high-performance distributed database service independently developed by Alibaba Cloud.
Learn MoreLeverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by ApsaraDB