Query optimizer for MySQL Query optimization series lectures (1) _ MySQL

Query optimizer of MySQL Query optimization series (1) when you submit a query, MySQL will analyze it to see if some optimization can be done to make it faster. This section describes how the query optimizer works. If you want to know the optimization methods used by MySQL, you can refer to the MySQL Reference Manual.

Of course, the MySQL Query optimizer also uses indexes, but it also uses other information. For example, if you submit a query as shown below, MySQL runs the query very quickly regardless of the data table size:

　　
SELECT * FROM tbl_name WHERE 0;

In this example, MySQL checks the WHERE clause and recognizes that there are no data rows that meet the query conditions. Therefore, it does not consider searching data tables. You can see this situation by providing an EXPLAIN statement, which allows MySQL to display some information about the SELECT query that has been executed but has not actually been executed. If you want to use EXPLAIN, you only need to put the EXPLAIN word before the SELECT statement:

　　
Mysql> explain select * FROM tbl_name WHERE 0G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: NULL
Type: NULL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: NULL
Extra: Impossible WHERE

In general, EXPLAIN returns more information than the above information, it also includes non-NULL information such as the index used to scan the data table, the join type used, and the estimated number of data rows in each data table to be checked.

How the optimizer works

The MySQL Query Optimizer has several goals, but the primary goal is to use the index as much as possible and use the strictest index to eliminate as many data rows as possible. Your final goal is to submit a SELECT statement to find data rows, rather than exclude data rows. The reason the optimizer tries to exclude data rows is that the faster it can exclude data rows, the faster it can find data rows that match conditions. If you can perform the strictest test first, the query can be executed faster. Assume that your query checks two data columns, each of which has an index:

SELECT col3 FROM mytable
WHERE col1 = 'some value' AND col2 = 'some other value ';

Assume that the test on col1 matches 900 data rows, the test on col2 matches 300 data rows, and the test at the same time only has 30 data rows. First, test Col1 and there will be 900 data rows. check them to find 30 matching records with the values in col2, and 870 of them will fail. First, test col2 will have 300 data rows. check them to find 30 records matching the values in col1. only 270 of the records failed, therefore, less computing and disk I/O are required. The result is that the optimizer tests col2 first, because the overhead is smaller.

You can use the following guidance to help the optimizer make better use of indexes:

Try to compare columns with the same data type. When you use indexed data columns in a comparison operation, use columns of the same data type. The same data type has higher performance than different types. For example, INT and BIGINT are different. CHAR (10) is considered as CHAR (10) or VARCHAR (10), but is different from CHAR (12) or VARCHAR (12. If the data columns you compare have different types, you can use alter table to modify one of them to match their types.

Try to make the index column independent in the comparison expression. If you use a data column in a function call or more complex arithmetic expression condition, MySQL does not use an index because it must calculate the expression value of each data row. Sometimes this can be avoided, but in many cases you can re-compile a query to make the index column appear independently.

The WHERE clause below shows this situation. They have the same functions, but they differ greatly in the optimization objectives:

　　
WHERE mycol< 4/2
WHERE mycol * 2 <4

For the first line, the optimizer simplifies expression 4/2 to 2, and then uses indexes on mycol to quickly find values smaller than 2. For the second expression, MySQL must retrieve the mycol value of each data row, multiply it by 2, and then compare the result with 4. In this case, indexes are not used. Each value in the data column must be retrieved to calculate the value on the left of the comparison expression.