"mysql optimization topic" You want to multi-table query optimization come! " Please find (4)

One, the choice of multi-table query connection:

Believe that the connection, the left connection what we are more familiar with, of course, there are left outside the connection, the basic use of I do not post it out. This figure just lets us recall that various connection queries. And then to tell you that you need to use the query to find out which kind of connection is more efficient. (This is a technical article)

Second, the MySQL join implementation principle

In MySQL, there is only one join algorithm, which is the famous nested Loop join, and he does not have a hash join provided by many other databases, and there is no sort Merge join. As the name implies, the Nested loop Join is actually using the result set of the driver table as the loop base data, and then one line of data from the result set as a filter to the next table to query the data, and then merge the results. If there is a third participating join, then the join result set for the first two tables is used as the circular base data, and once again, the data is queried in the third table by looping through the criteria, and so forth. --from "MySQL Performance tuning and Architecture design"

–

Third, add: MySQL on the SQL statement fault tolerance problem

That is, if the SQL statement does not fully conform to the writing proposal, MySQL will allow this situation to be explained as much as possible:

1) The General Cross join is followed by a where condition, but the cross Join+on is also interpreted as cross join+where;

2) The general internal connection is required to add on qualification, such as the above scene one, if not added will be interpreted as cross-linking;

3) If the connection table uses a comma, it will be interpreted as a cross connection;

NOTE: The SQL Standard also has union join and natural inner Join,mysql not supported, and in itself does not make much sense, in fact, is to "robust." However, the results can be obtained by using the above-mentioned methods of connection.

Super-Large data try not to write a subquery, use a connection (join) to replace it:

Of course, that is not necessarily the case with respect to this statement.

1) because in large-scale data processing, sub-query is very common, especially in the query out of the data need to further deal with the situation, whether it is readability or efficiency, at this time the sub-check are more excellent.

2) However, in some specific scenarios, you can read directly from the database, such as a table (a Table a,b,c field, the need for internal data intersection) to join their own efficiency is necessarily better than to put a sub-check in where the much faster. (This is really a technical article)

Iv. using Union (union) instead of manually created temporary tables

Union will sort out the results!!!

Union query: It can combine two or more select queries that need to use temporary tables (that is, merge two or more query results). ）。 At the end of the client's query session, the temporary table is automatically deleted, ensuring that the database is neat and efficient. When using union to create a query, we only need to concatenate multiple SELECT statements with union as a keyword, and be aware that the number of fields in all SELECT statements is the same.

#

Required: Two the number of columns in the query must be the same (the column type can be different, but the recommended query for each column, the corresponding type to be the same)

Data that can come from multiple tables: The column names taken out by multiple SQL statements can be inconsistent, with the column name of the first SQL statement as a standard.

If the rows taken in different statements are identical (in this case, the values for each column are the same), then union merges the same rows and eventually retains only one row. It is also possible to understand that union removes duplicate rows.

If you do not want to remove duplicate rows, you can use union ALL.

If there is an order by,limit in the clause, enclose it in parentheses (). After all clauses are recommended, the result of the final merge is sorted or filtered.

Attention:

1. The column name in the UNION result set is always equal to the column name in the first SELECT statement

2. The SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same

The function and syntax of UNION all:

By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated.

V. Summary

(1) For comprehensive results, we need to use the connection operation (left Join/right join/full JOIN);

(2) You should try to avoid null values for the field in the WHERE clause, otherwise it will cause the engine to abandon using the index for a full table scan, such as:

Comments, descriptions, comments, and so on can be set to NULL, and it is best not to use NULL.

Do not assume that NULL does not require space, such as: char (100) type, when the field is established, the space is fixed, regardless of whether the insertion value (NULL is also included), is occupied 100 characters of space, if it is varchar such a variable length field, NULL does not occupy space.

You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:

Select ID from t where num = 0

(3) In and not in also to use caution, otherwise it will result in a full table scan, such as:

For consecutive values, you can use between instead of in:

A lot of times it's a good choice to replace in with exists:

(4) Try to use a numeric field, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of query and connection, and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.

(5) Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index).

(6) Do not think that some of the use of MySQL connection operation to query how much improvement, in fact, the core is the index (do not hit me, the next one) (this is absolutely technical text)

"mysql optimization topic" You want to multi-table query optimization come! " Please find (4)