Shulou(Shulou.com)06/02 Report--

MySql in how to optimize multi-table query, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

First, the choice of multi-table query connection:

I believe that this internal connection, the left connection what everyone is more familiar with, of course, there are left outer connection what, basically do not need I will not post it out. This picture is just to remind you of all kinds of connection queries. Then I would like to tell you that you need to decide which connection method to use is more efficient according to the situation of the query.

Second, the JOIN realization principle of MySQL.

In MySQL, there is only one Join algorithm, the famous Nested Loop Join, which does not have the Hash Join or Sort Merge Join provided by many other databases. As the name implies, Nested Loop Join actually drives the result set of the table as the basic data loop, and then queries the data in the next table one by one through the data in the result set as a filter condition, and then merges the results. If there is a third participant in the Join, then use the Join result set of the first two tables as the circular base data, and once again query the data in the third table through the circular query condition, and so on. -- from "MySQL performance tuning and Architecture Design"

Third, add: the fault tolerance of mysql to sql statements

That is, in cases where the sql statement does not fully conform to the writing recommendation, mysql will allow this situation and explain it as much as possible:

1) generally cross join is followed by a where condition, but cross join+on is also interpreted as cross join+where

2) in general, internal connections need to be subject to on qualification, as in scenario 1 above; if not, it will be interpreted as cross-connection.

3) if the join table uses a comma, it will be interpreted as a cross-join

Note: union join and natural inner join,mysql are not supported in the sql standard, and they don't make much sense in themselves, just to be "robust". But in fact, the results can be obtained by using the above connections.

Third, very large data try not to write subqueries as much as possible, and replace it with JOIN:

Of course, this is not always the case with regard to this sentence.

1) because in large-scale data processing, subquery is very common, especially when the queried data needs further processing, whether in terms of readability or efficiency, the subquery is better.

2) however, in some specific scenarios, it can be read directly from the database. For example, the efficiency of a table (A table a _ r _ b ~ C field, which requires internal data intersection) join must be much faster than that of putting a sub-lookup in where.

Use UNION instead of manually created temporary tables

UNION will sort the results!

Union query: it can merge two or more select queries that need to use temporary tables in one query (that is, merge the results of two or more queries. ). At the end of the client query session, the temporary table is automatically deleted to ensure that the database is neat and efficient. When using union to create a query, we only need to concatenate multiple select statements using UNION as the keyword, and note that the number of fields in all select statements should be the same.

Requirement: the number of columns in the two queries must be the same (the type of column can be different, but the corresponding type of each column of the recommended query should be the same)

Data that can come from multiple tables: column names fetched by multiple sql statements can be inconsistent, subject to the column name of the first sql statement.

If the rows taken out in different statements are exactly the same (in this case, the values of each column are the same), union merges the same rows, leaving only one row. It can also be understood that union removes duplicate lines.

If you don't want to remove duplicate lines, you can use union all.

If there is an order by,limit in the clause, wrap it in parentheses (). It is recommended that you put it after all clauses, that is, sort or filter the results of the final merge.

Note:

1. Column names in the UNION result set are always equal to the column names in the first SELECT statement

2. SELECT statements within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the column order in each SELECT statement must be the same

The role and syntax of UNION ALL:

By default, the UNION operator selects a different value. If duplicate values are allowed, use UNION ALL. When ALL is used with UNION (that is, UNION ALL), duplicate lines are not eliminated.

V. Summary

(1) for comprehensive results, we need to use the join operation (LEFT JOIN / RIGHT JOIN / FULL JOIN)

(2) try to avoid judging the null value of a field in the where clause, otherwise it will cause the engine to give up using the index and perform a full table scan, such as:

Comments, descriptions, comments, etc., can be set to NULL, and others are best not to use NULL.

Do not think that NULL does not need space, for example: char (100), when the field is created, the space is fixed, regardless of whether the value is inserted (NULL is also included), it takes up 100 characters of space. If it is a variable length field like varchar, null does not take up space.

You can set the default value of 0 on num to ensure that there is no null value for the num column in the table, and then query it like this:

Select id from t where num = 0

(3) in and not in should also be used with caution, otherwise it will lead to full table scanning, such as:

For consecutive values, use between instead of in:

In many cases, using exists instead of in is a good choice:

(4) use numeric fields as much as possible, and try not to design character fields that contain only numerical information, which will reduce the performance of queries and connections, and increase storage overhead. This is because the engine compares each character in the string one by one when processing queries and concatenations, while for numeric types, only one comparison is needed.

(5) use table variables instead of temporary tables as far as possible. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index).

(6) Don't think that some join operations using MySQL improve the query much, in fact, the core is the index (we will continue to talk about it later)

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.