** Abstract: ** In the database query, often need to query the data of multiple tables, such as query member information at the same time query about the member’s order information, if the sub-statement query, the efficiency will be very low, you need to use the join keyword to connect the table query.

Join in parallel

Join parallel 1. Introduction to multi-table Join 2. Method of multi-table Join do not use Join buffer use Join buffer3.

1. Introduction to multi-table JOIN

The JOIN clause is used to combine two or more tables based on related columns between them. Such as:

The Orders:

Customers:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Copy the code

2. Multi-table Join mode

Hash Join is implemented using a new executor and is not discussed here

All MySQL supports is nested-loop Join and its variants.

Do not use Join buffer

a) Simple Nested-Loop

For each row of r table, scan S table completely, judge whether the condition is met according to the row composed of R [I]-s[I], and return the result that meets the condition to the client.

mysql> show create table t1; +-------+--------------------------------------------------------------------------------------------------------------- -+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------- -+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  +-------+-------------------------------------------------------------------------------------------------------------- --+ 1 row in set (0.00 SEC) mysql> show create table t3; +-------+--------------------------------------------------------------------------------------------------------------- -----+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------- -----+ | t3 | CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+--------------------------------------------------------------------------------------------------------------- -----+ 1 row in set (0.00 SEC) mysql> explain select /*+ NO_BNL() */ * from t1, t3 where t1.id = t3.id; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | | 1 SIMPLE | t1 | NULL | | NULL ALL | NULL | NULL | NULL | 2 | | NULL 100.00 | | 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | | 50.00 Using the where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 2 Rows in set, 1 Warning (0.00 SEC)Copy the code

b) Index Nested-Loop

For each row of r table, the index of S table is queried according to the connection conditions. Then, matching data is found in the table and the result that meets the conditions is returned to the client.

mysql> show create table t2; +-------+--------------------------------------------------------------------------------------------------------------- ------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------- ------------------------+ | t2 | CREATE TABLE `t2` ( `id` int(11) NOT NULL, KEY `index1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+--------------------------------------------------------------------------------------------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > explain the select * from t1, t2 where t1. Id = t2. Id; +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+------ -------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+------ -- -- -- -- -- -- -- + | | 1 SIMPLE | t1 | NULL | | NULL ALL | NULL | NULL | NULL | 2 | | NULL 100.00 | | 1 | SIMPLE | t2 | NULL | ref | | index1 | index1 | 4 test. T1. Id | 1 | | 100.00 Using index | +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+------ -------+ 2 rows in set, 1 Warning (0.00 SEC)Copy the code

Using the Join buffer

a) Block Nested Loop

Read some data from table R into join cache. When table R reads all data or join cache is full, join operation is performed.

b) Batched Key Access

Join cache reads part of the data from table R. Table S records the values of the joined columns of table R as indexes. Query all indexes that meet the conditions.

For each cached record, there is a key that is used to scan the s table for the desired data.

dsmrr_fill_buffer(){ while((rowids_buf_cur < rowids_buf_end) && ! (res = h2->handler::multi_range_read_next(&range_info))){// Press index condition if (h2->mrr_funcs.skip_index_tuple && h2->mrr_funcs.skip_index_tuple(h2->mrr_iter, curr_range->ptr)) continue; memcpy(rowids_buf_cur, h2->ref, h2->ref_length); } varlen_sort( rowids_buf, rowids_buf_cur, elem_size, [this](const uchar *a, const uchar *b) { return h->cmp_ref(a, b) < 0; }); } dsmrr_next(){ do{ if (rowids_buf_cur == rowids_buf_last) { dsmrr_fill_buffer(); } // first match if (h2->mrr_funcs.skip_record && h2->mrr_funcs.skip_record(h2->mrr_iter, (char *)cur_range_info, rowid)) continue; res = h->ha_rnd_pos(table->record[0], rowid); break; } while(true); } JOIN_CACHE_BKA::join_matching_records(){ while (! (error = file->ha_multi_range_read_next((char **)&rec_ptr))) { get_record_by_pos(rec_ptr); rc = generate_full_extensions(rec_ptr); if (rc ! = NESTED_LOOP_OK) return rc; }}Copy the code

mysql> show create table t1; +-------+--------------------------------------------------------------------------------------------------------------- ----------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------- ----------------------------------+ | t1 | CREATE TABLE `t1` ( `f1` int(11) DEFAULT NULL, `f2` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+--------------------------------------------------------------------------------------------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > show create table t2; +-------+--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `f1` int(11) NOT NULL, `f2` int(11) NOT NULL, `f3` char(200) DEFAULT NULL, KEY `f1` (`f1`,`f2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+--------------------------------------------------------------------------------------------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) mysql > explain SELECT /*+ BKA() */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------- --------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys  | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------- --------------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | | | 100.00 Using the where | | 1 SIMPLE | t2 | NULL | ref 4 | | | | f1 f1 test1. T1. | | | 11.11 7 f1 Using the index condition; Using join buffer (Batched Key Access) | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set, 1 warning (0.00 SEC)Copy the code

c) Batched Key Access(unique)

mysql> show create table city; +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------+ | Table |  Create Table | +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------+ | city | CREATE TABLE `city` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `Country` char(3) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `Population` (`Population`), KEY `Country` (`Country`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------+ 1 row in Set (0.00 SEC) mysql> show create table country; +---------+------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------+ | country | CREATE TABLE `country` ( `Code` char(3) NOT NULL DEFAULT '', `Name` char(52) NOT NULL DEFAULT '', 'SurfaceArea' float(10,2) NOT NULL DEFAULT '0.00', 'Population' int(11) NOT NULL DEFAULT '0', `Capital` int(11) DEFAULT NULL, PRIMARY KEY (`Code`), UNIQUE KEY `Name` (`Name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +---------+------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------+ 1 row in set (0.01sec) mysql> EXPLAIN SELECT city.name, country.Name FROM city,country WHERE city.country=country.Code AND country.Name LIKE 'L%' AND city.Population > 100000; +----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+ ----------+--------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+ ----------+--------------------------------------------------------------+ | 1 | SIMPLE | country | NULL | index | PRIMARY, Name | Name | 208 | NULL | 1 | | 100.00 Using the where; Using the index | | | 1 SIMPLE | city | NULL | ref | Population, the Country 12 | | Country | test1. Country. Code 100.00 | | | 1 Using where; Using join buffer (Batched Key Access (unique)) | +----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.01 SEC)Copy the code

3. Join execution process (old executor)

Click to follow, the first time to learn about Huawei cloud fresh technology ~