Mysql multi-table joint query efficiency analysis and optimization

I. A simple optimization of correlated subqueries.

In many cases, the performance of subqueries implemented on mysql is poor, which sounds a little sad. IN particular, when an IN () subquery statement is used, it is difficult to estimate the time consumption of a table of a certain magnitude. I am not very familiar with mysql, so I can only gradually understand the mystery.

Suppose there is such an exists query statement:

Select * from table1
Where exists
(Select * from table2 where id >= 30000 and table1.uuid = table2.uuid );

Table1 is a 100,000-row-level table, table2 is a million-row-level table, and the local test result takes 2.40 s.

Through the explain command, we can see that the SUBQUERY is a dependent SUBQUERY. Mysql scans the entire table of table1 first, and then executes the SUBQUERY successively based on the returned uuid. If the outer table is a large table, we can imagine that the query performance will be worse than this test.

A simple optimization solution is to use the inner join method to replace subqueries. The query statement can be changed:

Select * from table1 innner join table2 using (uuid) where table2.id >=30000;

The local test result is 0.68 s.

Through the explain command, we can see that mysql uses the SIMPLE type (subquery or query method other than union); Mysql Optimizer first filters table2, perform Cartesian product on table1 and table2 to obtain the result set, and then filter the data using the on condition.

II. Analysis and optimization of multi-table joint query efficiency

1. Multi-table connection type
1. Cartesian products (cross join) can be considered as cross join in MySQL, or CROSS is omitted, or ',' such:

01. SELECT * FROM table1 cross join table2
02. SELECT * FROM table1 JOIN table2
03. SELECT * FROM table1, table2
SELECT * FROM table1 cross join table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1 and table2 are generally not recommended when the WHERE, ON, or USING conditions exist because the returned results are the product of the two connected data tables, because when there are too many data table projects, it will be very slow. Generally, LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN is used.

2. inner join: In MySQL, inner join is called an equijoin. That is, the equijoin conditions must be specified. In MySQL, CROSS and inner join are divided together. Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. mySQL outer join is divided into left outer join and right join. In addition to returning results that meet the connection conditions, the left table (left join) or right table (right join) is also returned) results that do not meet the connection conditions, corresponding to the use of NULL.

Example:

User table:

Id | name
---
1 | libk
2 | zyfon
3 | daodao

User_action table:

User_id | action
-----
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

SQL:

01. select id, name, action from user as u
02. left join user_action a on u. id = a. user_id
Select id, name, action from user as u
Left join user_action a on u. id = a. user_idresult:
Id | name | action
-----------
1 | libk | jump ①
1 | libk | kick ②
1 | libk | jump ③
2 | zyfon | run ④
3 | daodao | null ⑤

Analysis:
Note that user_action has a user_id = 4, action = swim record, but it does not appear in the result,
In the user table, the id = 3 and name = daodao users do not have corresponding records in user_action, but they appear in the result set.
Because it is left join, all work is subject to left.
Result 1, 2, 3, 4 are records in both the left table and the right table. 5 is a record in only the left table, not in the right table.

 

Working principle:

Read one record from the left table and select all records (n records) in the right table that match on to form n records (including duplicate rows, for example: result 1 and result 3). If there is no table matching the on condition on the right side, all connected fields are null. then read the next one.

Extended:
If there is no on matching in the right table, we can display the null rule to find all records in the left table, not in the right table. Note that the column to be judged must be declared as not null.
For example:
SQL:

01. select id, name, action from user as u
02. left join user_action a on u. id = a. user_id
03. where a. user_id is NULL
Select id, name, action from user as u
Left join user_action a on u. id = a. user_id
Where a. user_id is NULL
(Note:

1. If the column value is null, it should be "is null" instead of "= NULL".
2. Here the. user_id column must be declared as not null.

)
Result of the preceding SQL statement:
Id | name | action
---------
3 | daodao | NULL

---------------------------

General usage:

A. LEFT [OUTER] JOIN:

In addition to returning results that meet the connection conditions, you must also display data columns that do not meet the connection conditions in the left table.

01. SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column
B. RIGHT [OUTER] JOIN:

The difference between RIGHT and left join is that in addition to displaying results that meet the connection conditions, you also need to display data columns that do not meet the connection conditions in the RIGHT table.

01. SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.columnTips:

1. on a. c1 = B. c1 is equivalent to using (c1)
2. inner join and (comma) are semantically equivalent.
3. When MySQL retrieves information from a table, you can prompt which index it chooses.
This feature is useful if the EXPLAIN command shows that MySQL uses an index that may be incorrect in the index list.
By specifying the use index (key_list), you can tell MySQL to USE the most appropriate INDEX to find record rows in the table.
The optional syntax ignore index (key_list) can be used to tell MySQL not to use a specific INDEX. For example:

01. mysql> SELECT * FROM table1 use index (key1, key2)
02.-> WHERE key1 = 1 AND key2 = 2 AND key3 = 3;
03. mysql> SELECT * FROM table1 ignore index (key3)
04.-> WHERE key1 = 1 AND key2 = 2 AND key3 = 3;
Mysql> SELECT * FROM table1 use index (key1, key2)
-> WHERE key1 = 1 AND key2 = 2 AND key3 = 3;
Mysql> SELECT * FROM table1 ignore index (key3)
-> WHERE key1 = 1 AND key2 = 2 AND key3 = 3;

2. Constraints for table join
Add the WHERE, ON, and USING display conditions.

1. WHERE Clause

Mysql>

01. SELECT * FROM table1, table2 WHERE table1.id = table2.id;
SELECT * FROM table1, table2 WHERE table1.id = table2.id;
2. ON

Mysql>

01. SELECT * FROM table1 left join table2 ON table1.id = table2.id;
02.
03. SELECT * FROM table1 left join table2 ON table1.id = table2.id
04. left join table3 ON table2.id = table3.id;
SELECT * FROM table1 left join table2 ON table1.id = table2.id;

SELECT * FROM table1 left join table2 ON table1.id = table2.id
Left join table3 ON table2.id = table3.id;
3. USING clause. If the two columns of the join two tables have the same names, you can use USING

For example:

Select from left join using ()

 

Examples of connecting more than two tables:

Mysql>

01. SELECT artists. Artist, cds. title, genres. genre
02.
03. FROM cds
04.
05. left join genres N cds. genreID = genres. genreID
06.
07. left join artists ON cds. artistID = artists. artistID;
SELECT artists. Artist, cds. title, genres. genre

FROM cds

Left join genres N cds. genreID = genres. genreID

Left join artists ON cds. artistID = artists. artistID;

 

Or mysql>

01. SELECT artists. Artist, cds. title, genres. genre
02.
03. FROM cds
04.
05. left join genres ON cds. genreID = genres. genreID
06.
07. left join artists-> ON cds. artistID = artists. artistID
08.
09. WHERE (genres. genre = 'pop ');
SELECT artists. Artist, cds. title, genres. genre

FROM cds

Left join genres ON cds. genreID = genres. genreID

Left join artists-> ON cds. artistID = artists. artistID

WHERE (genres. genre = 'pop ');

--------------------------------------------

In addition, you need to note that when MySQL involves multi-table queries, you need to determine which connection method is more efficient based on the query conditions.

1. cross join (Cartesian product) or inner join [INNER | CROSS] JOIN

2. left outer join left [OUTER] JOIN or right outer join right [OUTER] JOIN. Specify the connection conditions WHERE, ON, and USING.

3. How does MySQL optimize left join and right join?
In MySQL, the execution process of a left join B join_condition is as follows:

1) · Set Table B based on all tables on which table A and table A depend.

2). Set table A according to all tables used in the left join condition (except B.

3) the left join condition is used to determine how to search rows from Table B. (In other words, do not use any conditions in the WHERE clause ).

4) · All standard Joins can be optimized, except for tables read from all the tables it depends on. If a circular dependency occurs, MySQL prompts an error.

5) perform all standard WHERE optimizations.

6) If A has A row that matches the WHERE clause, but B does not have A row that matches the ON condition, another B row is generated, and all columns are set to NULL.

7). If left join is used to locate rows that do NOT exist in some tables and perform the following test: col_name is null in the WHERE section, WHERE col_name IS a column declared as not null, mySQL finds a row that matches the left join condition and stops searching for other rows (for a specific keyword combination.

The execution of right join is similar to that of left join, but the role of the table is the opposite.

The order in which the join Optimizer calculates the table to be joined. The forced read sequence of left join and STRAIGHT_JOIN can help the JOIN Optimizer to work faster, because fewer table exchanges are checked. Note that if the following type of query is executed, MySQL performs full scan B, because LEFT JOIN forces it to read before d:

01. SELECT *
02. FROM a, B LEFT JOIN c ON (c. key = a. key) LEFT JOIN d ON (d. key = a. key)
03. WHERE B. key = d. key;
SELECT *
FROM a, B LEFT JOIN c ON (c. key = a. key) LEFT JOIN d ON (d. key = a. key)
WHERE B. key = d. key;
In this case, the reverse order of a is used for restoration, and B is listed in the FROM clause:

01. SELECT *
02. FROM B, a LEFT JOIN c ON (c. key = a. key) LEFT JOIN d ON (d. key = a. key)
03. WHERE B. key = d. key;
SELECT *
FROM B, a LEFT JOIN c ON (c. key = a. key) LEFT JOIN d ON (d. key = a. key)
WHERE B. key = d. key;
MySQL can perform the following left join optimization: if the NULL row is generated, the WHERE condition is always false, and the left join is changed to a normal JOIN.

For example, in the following query, if t2.column1 is NULL, the WHERE clause is false:

01. SELECT * FROM t1 left join t2 ON (column1) WHERE t2.column2 = 5;
SELECT * FROM t1 left join t2 ON (column1) WHERE t2.column2 = 5; therefore, you can safely convert a query to a normal JOIN:

01. SELECT * FROM t1, t2 WHERE t2.column2 = 5 AND t1.column1 = t2.column1;
SELECT * FROM t1, t2 WHERE t2.column2 = 5 AND t1.column1 = t2.column1; this can be faster, because MySQL can use table T2. To force table order, use STRAIGHT_JOIN.

III. Implementation using cache

Community sharing websites are very popular now. Let's take fanwe shopping sharing websites as an example. It is also a summary of the secondary development fanwe shopping sharing website. Experts can fly over.

Key shopping tables include: sharing tables, image tables, file tables, comment tables, label tables, and classification tables.
There are a lot of tables shared around. Wow, there are also a lot of tables. When we view the details of an image, we need to display the information in the above table. Displays the category of the image, tags for the image, comments for the image, and file download information if there is a file. Do we have 6 tables to join for query? Of course we can only query one table instead of so many tables to query data? Here, the shared table is a master table. We can create a cache field in the master table. For example, we call the cache_data field and assign it the text type, so that long strings can be stored without exceeding the maximum storage of fields.

How to use this cache field? A share ID is generated after a new share information is added. If you publish an image or file, the image information is included in the image table, the file information is included in the file table, and the newly generated image or file information is written into the cache field. Similarly, if you select a category or tag, you can write the corresponding information to the cache field. For comments, there is no need to store all comments in the cache field. Because you don't know how many records he has, you can store the latest 10 in the cache field for display, in this way, the cached field becomes a two-dimensional or three-dimensional array, serialized and stored in the shared table.

Array (
 
'IMG '= array (
Name => '123.jpg ',
Url => 'http: // tech.42xiu.com/123.jpg ',
Width = & gt; 800,
Width = & gt; 600,
),

'File' = array (
Name => 'abc.zip ',
Download_url => 'http: // tech.42xiu.com/abc.zip ',
Size => 1.2 Mb,
),

'Category '= array (
1 => array (
Id => 5,
Name => PHP Lezhi blog
),

2 => array (
Id => 6,
Name => PHP Technical Blog
),
),

'Tag' => array (
Tag1
Tag2
......
),

'Message' => array (
1 => array (id, uid, name, content, time ),
2 => array (id, uid, name, content, time ),
3 => array (id, uid, name, content, time ),
4 => array (id, uid, name, content, time ),
),

)
// For example, the above array structure is serialized into the database.

UPDATE share SET cache_data = mysql_real_escape_string (serialize ($ cache_data) WHERE id = 1; in this way, the query becomes simple. You only need to query one and retrieve the cached field, deserialization: extracts array information and displays it on the page. If it is the previous structure, it is estimated that it will crash early in the case of hundreds of thousands of data records. The data caching method may not be the best. If you have a better method, you can learn from each other and discuss with each other.