MySQL Query optimization program [group chart] _ MySQL

The MySQL Query optimization program [group chart] analyzes a selection row query to see if it can be optimized to make it run faster. This article will study how the query optimization program works. For more information, see "Getting Maximum Performance from MySQL" in MySQL Reference Guide. This article describes various optimization measures adopted by MySQL. (MySQL online reference guide At http://www.mysql.com/is constantly updated .)

The MySQL Query optimization program uses indexes. Of course, it also uses other information. For example, if the following query is published, MySQL runs it very quickly, regardless of the size of the corresponding table:

SELECT * FROM tb1_name WHERE 1 = 0

In this case, MySQL examines the WHERE clause and does not search the table if it realizes that it cannot meet the query. You can use the EXPLAIN statement to know this. the EXPLAIN statement requires MySQL to display some information about which SELECT query should be executed but not actually executed. To use e x p l a I N, you only need to place the EXPLAIN statement before the SELECT statement, as shown below:

Explain select * FROM tb1_name WHERE 1 = 0

Generally, EXPLAIN returns more information than this, including the index used to scan the table, the connection type to be used, and the number of rows to be scanned in each table.

1. how does the optimization program work?

The MySQL Query optimization program has several goals, but its main goal is to make full use of indexes and use the most restrictive indexes to exclude as many rows as possible. This may be counterproductive because the purpose of releasing a SELECT statement is to find rows rather than reject them. The reason for the optimization program to work like this is that the faster the row is excluded from the row to be considered, the faster it will be to find the row that actually meets the given criteria. If you can perform the most restrictive test first, the query can be performed faster. Suppose there is a query for two columns, each column has an index:

WHERE coll = "some value" AND col2 = "some other value"

It is also assumed that there are 900 rows in line with the test on col1, 300 rows in line with the test on col2, and 30 rows in both tests. If you test c o l 1 First, you must check the 900 rows to find 30 rows that match the col2 value. 870 of the tests will fail. If you first Test c o l 2, you only need to check 300 rows to find 30 rows that match the col1 value. The test failed 270 times, resulting in less computing and less disk I/O. The following guidelines are followed to help optimize the app's use of indexes:

1. compare columns of the same type. When using index columns in comparison, you should use columns of the same type. For example, CHAR (10) is considered to be the same as CHAR (10) or VARCHAR (10), but different from CHAR (12) and VARCHAR (12 ). INT is different from BIGINT. Before MySQL 3.23, you must use columns of the same type. Otherwise, the index of the column does not work. This is not strictly required since version 3.23, but the same column type provides better performance than different types. If the types of the two columns are different, you can use the alter table statement to modify one of them to match their types.

2. try to make the index column independent during the comparison. If a column is used in a function call or arithmetic expression, MySQL cannot use such an index because it must calculate the expression value for each row. Sometimes this is inevitable, but in many cases, you can rewrite the query that only takes the index column itself. The WHERE clause below illustrates how to do this. In the first line, the optimizer simplifies expression 4/2 as value 2, and then uses the index on my_col to quickly locate a value smaller than 2. In the second expression, MySQL must retrieve the my_col value of each row, multiply it by 2, and then compare the result with 4. No index is available, because each value in the column must be retrieved to evaluate the expression on the left:

WHERE my_col< 4/2

WHERE my_col * 2 <4

Let's consider another example. Assume that there is an index column date _ c o l. If the following query is published, the corresponding index is not used:

SELECT * FROM my_tb1WHERE YEAR (date_col) <1990