Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve multi-table join in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Join parallelism

Join parallel 1. Introduction of multi-table join 2. The multi-table Join method does not use Join buffer and uses Join buffer3. Join execution process (old actuator)

1. Introduction of multi-table join

The JOIN clause is used to combine two or more tables based on their related columns. For example:

Orders:

Customers:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID

two。 The way of multi-table Join

Hash join is implemented using a new actuator and will not be discussed here

MySQL supports Nested-Loop Join and its variants.

Do not use Join buffer

A) Simple Nested-Loop

For each row of the r table, scan the s table completely, judge whether the condition is satisfied 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 | filtered | Extra | +-+ -- + | 1 | SIMPLE | T1 | NULL | ALL | NULL | 2 | 100.00 | NULL | 1 | SIMPLE | T3 | NULL | ALL | NULL | 2 | 50.00 | Using where | + -+ 2 rows in set 1 warning (0.00 sec)

B) Index Nested-Loop

For each row of the r table, first query the s table index according to the connection conditions, then return to the table to find the matching data, and return the results that meet the conditions 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 set (0.00 sec) mysql > explain 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 | ALL | NULL | 2 | 100.00 | NULL | | 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)

Use Join buffer

A) Block Nested Loop

Read part of the data from the r table to the join cache, and do the join operation when the r table data is read or the join cache is full.

JOIN_CACHE_BNL::join_matching_records () {do {/ / reads each row of the s table qep_tab- > table ()-> file- > position (qep_tab- > table ()-> record [0]); / / traverses join buffer for (each record in join buffer) {get_record () for each row of s; rc = generate_full_extensions (get_curr_rec ()) / / if the condition is not met, return if (rc! = NESTED_LOOP_OK) return rc;}} while (! (error = iterator- > Read ())} mysql > explain select * from T1, T3 where t1.id = t3.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- +-- -+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | 2 | 100.00 | NULL | | 1 | SIMPLE | T3 | NULL | ALL | NULL | 2 | 50.00 | Using where Using join buffer (Block Nested Loop) | +-- +- -+ 2 rows in set 1 warning (0.00 sec)

B) Batched Key Access

Read part of the data from the r table to the join cache, record the value of the connected column of the r table in the s table as the index, query all the indexes that meet the conditions, then sort the indexes that meet the conditions, and then go back to the table to query the records.

Among them, for each cached record, there will be a key, through this key to the s table to scan the required data.

Dsmrr_fill_buffer () {while ((rowids_buf_cur)

Handler::multi_range_read_next (& range_info)) {/ / index condition if (H3-> mrr_funcs.skip_index_tuple & & H3-> mrr_funcs.skip_index_tuple (H3-> mrr_iter, curr_range- > ptr)) continue; memcpy (rowids_buf_cur, H3-> ref, H3-> ref_length) } varlen_sort (rowids_buf, rowids_buf_cur, elem_size, [this] (const uchar * a, const uchar * b) {return h-> cmp_ref (a, b)

Mrr_funcs.skip_record & & h3-> mrr_funcs.skip_record (h3-> 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;}} 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 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 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 | 3 | 100.00 | Using where | | 1 | SIMPLE | T2 | NULL | ref | F1 | F1 | 4 | test1.t1.f1 | 7 | 11.11 | Using index condition | Using join buffer (Batched Key Access) | +-- +- -+-+ 2 rows in set 1 warning (0.00 sec)

C) Batched Key Access (unique)

Unlike Batched Key Access, the column in r is the only index of s, and when the r record is written to join cache, a hash table of key is recorded and queried in the s table only for different key. (question, why can only be used in this way when unique? If it is not unique, multiple pieces of data may be scanned in the s table, and it can also be processed in this way to reduce the repeated scanning of the s table.

JOIN_CACHE_BKA_UNIQUE::join_matching_records () {while (! (error = file- > ha_multi_range_read_next ((char * *) & key_chain_ptr)) {do (each record in chain) {get_record_by_pos (rec_ptr); rc = generate_full_extensions (rec_ptr); if (rc! = NESTED_LOOP_OK) return rc;}} 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 '0mm, 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 `roomy` (`Code`char (3) NOT NULL DEFAULT'' `Name` char (52) NOT NULL DEFAULT'', `SurfaceArea` float (10Power2) NOT NULL DEFAULT '0.005, `Population` int (11) NOT NULL DEFAULT' 0mm, `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.01 sec) 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 where Using index | | 1 | SIMPLE | city | NULL | ref | Population,Country | Country | 12 | test1.country.Code | 1 | 100.00 | Using where Using join buffer (Batched Key Access (unique)) | +-+- -+-+ 2 rows in set 1 warning (0.01 sec)

3. Join execution process (old actuator)

Sub_select iterator::read () / / read a row of data |-> evaluate_join_record () / / check whether this line of data meets the conditions | |-> next_select ()-+ | sub_select_op Op- > put_record () / / join cache is written to the previous table | |-> put_record_in_cache () | |-> join- > record () | |-> join_matching_records () | |-> (qep_tab- > next_select) (join) Qep_tab + 1,0) / / continue to call next_select |-> end_send () so much about how to implement multi-table join in MySQL. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.