Mysql optimizes SELECT statements and other queries

Source: Internet
Author: User
Tags benchmark
  Mysql optimizes SELECT statements and other queries    


(Official Document)

7.2.1. EXPLAIN syntax (obtain SELECT-related information) 7.2.2. estimated query performance 7.2.3. SELECT query speed 7.2.4. how does MySQL optimize the WHERE clause 7.2.5. scope optimized: 7.2.6. index merge optimization 7.2.7. how does MySQL optimize IS NULL7.2.8. how does MySQL optimize DISTINCT7.2.9. how does MySQL optimize left join and RIGHT JOIN7.2.10. how does MySQL optimize nested Join7.2.11. how does MySQL simplify external Union 7.2.12. mySQL how to optimize ORDER BY7.2.13. MySQL how to optimize GROUP BY7.2.14. MySQL how to optimize LIMIT7.2.15. how to avoid table scan 7.2.16. INSERT statement speed 7.2.17. UPDATE statement speed 7.2.18. speed of the DELETE statement 7.2.19. other optimization skills

One factor that affects all statements is that the more complicated your license settings are, the more overhead you need.

A simple license is used to execute a GRANT statement. When a customer executes a statement, MySQL can reduce the license check overhead. For example, if no table-level or column-level permission is granted, the server does not need to check the content of tables_priv and columns_priv tables. Similarly, if no account is restricted, the server does not need to collect statistics on resources. If the query volume is high, you can spend some time using a simplified authorization structure to reduce the license inspection overhead.

BENCHMARK (Loop_count,Expression). For example:

mysql> SELECT BENCHMARK(1000000,1+1);
It displaysMySQLIn this system, 0.32 simple + expression operations can be executed within 1,000,000 seconds.
7.2.1. EXPLAIN syntax (obtain SELECT-related information)
· EXPLAINTbl_nameYesdescribeTbl_nameOr SHOW COLUMNS FROMTbl_name.
· If the keyword "EXPLAIN" is put before the SELECT statement,MySQLIt explains how it processes SELECT and provides the order of how tables are joined and joined.
To force the optimizer to set a SELECT statement to join in the table naming order, the statement should start with STRAIGHT_JOIN, not just SELECT.
EXPLAIN returns a row of information for each table in the SELECT statement..Tables are listed in the order they are read by MySQL during query processing.
MySQLScan multiple links once (Single-sweep multi-join. This meansMySQLRead a row from the first table,
Then find a matching row in the second table,
And then in 3rd tables.
After all the tables are processed, It outputs the selected columns and returns the table list until it finds a table with more matching rows. Read the next row from the table and continue processing the next table.
Each output row of EXPLAIN provides information about a table, and each row includes the following columns:

· Id

SELECT identifier. This is the serial number of the SELECT query.

· Select_type

SELECT type, which can be any of the following:


Simple SELECT (do not use UNION or subquery)


Exclusive SELECT


The second or subsequent SELECT statement in UNION


The second or subsequent SELECT statement in UNION depends on the external query (eg Fa in (select Fa from ))


The result of UNION.


The first SELECT (eg Fa = (select Fa from) in the subquery ))


The first SELECT in THE subquery depends on the external query (eg Fa in (select Fa from ))


SELECT (subquery of the from clause) of the export table)

· Table

The table referenced by the output row.

· Type

Join type. The following describes various connection types,Sort by the best type to the worst type:


O system

The table has only one row (= system table ). This is a special case of the const join type.

O const

A table can have at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column value in this row can be considered as a constant by the rest of the optimizer. Const tables are fast because they are read only once!

Const is used to compare all the parts of a primary key or UNIQUE index with a constant value. In the following query,Tbl_nameIt can be used for const tables:

SELECT * from tbl_name WHERE primary_key=1;
SELECT * from tbl_name
WHEREPrimary_key_part1= 1 andPrimary_key_part2= 2;

O eq_ref

For each row combination from the preceding table, read a row from the table. This may be the best join type except the const type. It is used to join all parts of an index and the index is UNIQUE or primary key.

Eq_ref can be used to compare indexed columns with the = Operator. The comparison value can be a constant or an expression that uses the column of the table read before the table.

In the following example, MySQL can use the eq_ref join for processing.Ref_tables:

SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;

O ref

For each row combination from the preceding table, all rows with matching index values are read from this table. If the join only uses the leftmost prefix of the KEY, or if the KEY is not UNIQUE or primary key (in other words, if the join cannot select a single row based on the keyword), use ref. If the key used matches only a few rows, the join type is good.

Ref can be used for indexed columns using the = or <=> operator.

In the following example, MySQL can use the ref join for processing.Ref_tables:

SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;

O ref_or_null

The join type is like ref, but MySQL can search for rows containing NULL values. The optimization of this join type is often used in solving subqueries.

In the following example, MySQL can use the ref_or_null join for processing.Ref_tables:

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

O index_merge

The join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used.

O unique_subquery

This type replaces the ref of the in subquery IN the following format:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

Unique_subquery is an index lookup function that can replace subqueries completely, improving efficiency.

O index_subquery

The join type is similar to unique_subquery. An IN subquery can be replaced by an IN subquery, but it is only applicable to non-unique indexes IN the following forms:

value IN (SELECT key_column FROM single_table WHERE some_expr)

O range

Only retrieve rows in a given range and use an index to select rows. The key column shows the index used. Key_len contains the longest key element of the index used. In this type, the ref column is NULL.

When using the =, <>,>,> =, <, <=, is null, <=>, BETWEEN, or IN operator, you can use the range:

SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);

O index

The join type is the same as that of ALL except that the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.

MySQL can use this join type when querying only columns that are part of a single index.


Perform a full table scan for each row combination from the previous table. If the table is the first table without the const mark,

This is usually not good, and usually in its caseVeryPoor. Generally, you can add more indexes instead of ALL, so that the rows can be retrieved Based on the constant values or column values in the preceding table.

· Possible_keys

The possible_keys column indicatesMySQLWhich index can be used to find rows in the table. Note,

This column is completely independent of the order of tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot be used in the order of the generated table.

To see what INDEX a table has, use SHOW INDEX FROMTbl_name.

· Key

Key Column DisplayMySQLThe key (INDEX) actually used ). If no index is selected, the key is NULL. To force MySQL to use or ignore the index in the possible_keys column,


Use force index, use index, or ignore index in the query

· Key_len

Key_len Column DisplayMySQLDetermines the length of the key used. If the key is NULL, the length is NULL. Note that using the key_len value, we can determine the parts of MySQL that will actually use multiple keywords.

· Ref

The ref column shows which column or constant is used together with the key to select rows from the table.

· Rows

Rows Column DisplayMySQLThe number of rows that must be checked during query execution.

· Extra

This column contains detailed information about MySQL queries. The following describes the different text strings that can be displayed in this column:

O Distinct

After MySQL finds 1st matching rows, it stops searching for more rows for the current row combination.


O Not exists

MySQL can perform left join Optimization on queries. After finding one row that matches the left join standard, it no longer checks more rows for the preceding row combination in the table.

The following is an example of the query type that can be optimized as follows:

SELECT * from t1 left join t2 ON =

Assume that is defined as not null. In this case, MySQL uses the value of to scan t1 and find the rows in T2. If MySQL finds a matched row in t2,

It knows that will never be NULL and does not scan rows with the same id value in t2. In other words, for each line of t1, MySQL only needs to find it once in t2, regardless of the actual number of matched rows in t2.

ORange checked for each record(Index map :#)

MySQL does not find any indexes that can be used. However, if the column values from the preceding table are known, some indexes may be used. For each row combination in the preceding table, MySQL checks whether the range or index_merge access method can be used to obtain rows.

For descriptions of applicability standards, see section 7.2.5, "range optimization" and section 7.2.6, "index merge optimization". The difference is that all column values in the preceding table are known and considered as constants.

This is not very fast, but it is much faster than executing a join without an index.

O Using filesort

MySQL requires an additional pass to find out how to retrieve rows in order. You can browse all rows based on the join type and save the sorting keywords and row pointers for all rows matching the WHERE clause.

Then the keywords are sorted and the rows are retrieved in the order of sorting. See section 7.2.12, "how to optimize order by in MySQL ".

O Using index

You can use only the information in the index tree without further searching and reading the actual row to retrieve the column information in the table. This policy can be used when queries only use columns that are part of a single index.

O Using temporary

To solve the query, MySQL needs to create a temporary table to accommodate the results. In typical cases, if a query contains the group by and order by clauses that can list columns according to different situations.

O Using where

The WHERE clause is used to limit which row matches the next table or sends it to the customer. Unless you request or check ALL rows from a table, if the Extra value is not Using where and the table join type is ALL or index, the query may have some errors.

If you want to make the query as fast as possible, you should find the Extra values of Using filesort and Using temporary.

O Using sort_union (...), Using union (...), Using intersect (...)

These functions describe how to merge index scans for the index_merge join type. For more information, see section 7.2.6 "index merge optimization ".

O Using index for group-

Similar to the Using index method for accessing tables, Using index for group-by indicates that MySQL has found an index and can be used to query all columns queried by group by or DISTINCT,

Instead of searching for the actual table by hard disk. In addition, indexes are used in the most effective way to read only a small number of index entries for each group. For more information, see section 7.2.13 "how MySQL optimizes group ".

By multiplying all the values in the rows column output by EXPLAIN, you can get a prompt about how a join works. This should roughly tell youMySQLYou must check the number of rows for query.

When you use the max_join_size variable to limit queries, you can also use this product to determine which multi-Table SELECT statement to execute. See section 7.5.2 "Adjust server Parameters ".

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.