MySQL 5.0 Database new features of the stored procedures

When you submit a query, MySQL analyzes it to see if you can do some optimizations to make it faster to process the query. This section describes how the query optimizer works. If you want to know the optimization method used by MySQL, you can view the MySQL reference manual.

Of course, the MySQL query optimizer also uses the index, but it also uses some other information. For example, if you submit a query like this, MySQL can execute it very quickly no matter how large the datasheet is:

SELECT * from Tbl_name WHERE 0;

In this example, MySQL looks at the WHERE clause and recognizes that there are no rows of data that match the query criteria, so the search data table is not considered at all. You can see this by providing a explain statement that allows MySQL to display some information about a select query that it executes but does not actually execute. If you want to use explain, just place the explain word in front of the SELECT statement:

Mysql> EXPLAIN SELECT * from Tbl_name WHERE 0\g *************************** 1. Row *************************** id:1 select_type:simple table:null type:null possible_keys:null key:null key_len:nu LL ref:null rows:null extra:impossible WHERE

Typically, explain returns more information than the above, as well as non-empty (null) information for scanning the data table's indexes, the type of join used, and the number of rows of data in each datasheet that are estimated to be checked.

How the optimizer works

The MySQL query optimizer has several goals, but the main goal is to use the index as much as possible and use the strictest indexes to eliminate as many rows of data as possible. Your ultimate goal is to submit a SELECT statement to find rows of data instead of excluding rows of data. The reason the optimizer tries to exclude rows of data is that the faster it excludes rows of data, the faster the data rows that match the criteria are found. If you can do the most rigorous testing first, the query can execute faster. Suppose your query examines two data columns, each of which has an index:

SELECT col3 from mytable WHERE col1 = ' Some value ' and col2 = ' some other value ';

Assuming that the test on col1 matches 900 rows of data, the test on col2 matches 300 rows of data, while the test at the same time gets only 30 rows of data. Test Col1 will have 900 rows of data, you need to check that they find 30 of them and col2 in the matching records, 870 of which failed. Test col2 will have 300 rows of data, you need to check that they found 30 of them and the values in the col1 matching records, only 270 failed, so need less calculation and disk I/O. As a result, the optimizer tests the col2 first because it is less expensive to do so.

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

Try to compare data 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 is a bit higher than the performance of different types. For example, int is different from bigint. char (10) is considered to be char (10) or varchar (10), but differs from char (12) or varchar (12). If you are comparing different types of data columns, you can use ALTER TABLE to modify one of them to match their type.

Make the index columns independent in the comparison expression as much as possible. If you use a data column in a function call or in a more complex arithmetic expression condition, MySQL will not use the index because it must compute the expression value for each data row. Sometimes this is unavoidable, but in many cases you can rewrite a query to make the index columns appear independently.

This situation is shown in the WHERE clause below. They have the same functionality, but there are significant differences in the optimization goals:

Where MyCol < 4/2 where MyCol * 2 < 4

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

Let's look at another example. Let's say you indexed the Date_col column. If you submit a query such as the following, you will not use this index:

SELECT * from Mytbl WHERE year (Date_col) < 1990;

This expression does not compare 1990 to the indexed column, and it compares 1990 to the value computed by the data column, and each data row must compute the value. As a result, the index on the date_col is not used because executing such a query requires a full table scan. How to solve this problem? You only need to use the text date, and then you can use the index on the date_col to find the matching values in the column:

WHERE Date_col < ' 1990-01-01 '

However, suppose you don't have a specific date. You may want to find a record of dates that are fixed several days apart today. There are a number of ways to express this type of comparison--they are different in efficiency. Here are three kinds:

where To_days (Date_col)-To_days (Curdate ()) < cutoff where to_days (Date_col) < cutoff + to_days (curdate ()) Where Da Te_col < Date_add (Curdate (), INTERVAL Cutoff day)

The index is not used for the first row because each data row must be retrieved to calculate the value of To_days (Date_col). The second line is better. Cutoff and To_days (Curdate ()) are constants, so the right side of the comparison expression can be calculated one at a time without requiring each data row to be evaluated before the query is processed. However, the Date_col column still appears in the function call, which prevents the use of the index. The third line is the best of the few. Similarly, the right side of the comparison expression can be calculated as a constant before the query is executed, but now its value is a date. This value can be directly compared to the Date_col value and no longer needs to be converted. In this case, the index is used.

Do not use wildcard characters at the beginning of like mode. Some string searches use the WHERE clause as follows:

WHERE col_name like '%string% '

If you want to find a string that appears anywhere in the data column, the statement is right. But don't put "%" on either side of the string simply because you're used to it. If you are looking for a string that appears at the beginning of the data column, delete the preceding "%". Suppose you're looking for names like MacGregor or MacDougall that start with "Mac." In this case, the WHERE clause looks like this:

WHERE last_name like ' mac% '

The optimizer looks at the text of the first words in the pattern and uses the index to find the rows of data that match the following expression. The following expression is another form of using the Last_Name index:

WHERE last_name >= ' Mac ' and last_name < ' Mad '

This optimization cannot be applied to pattern matching using the regexp operator. RegExp expressions are never optimized.

Help the optimizer better judge the efficiency of the index. By default, when you compare the value of an indexed column with a constant, the optimizer assumes that the key value is evenly distributed within the index. When deciding whether to use an index for a constant comparison, the optimizer quickly examines the index and estimates how many entities (entry) will be used. For MyISAM, InnoDB, and BDB data tables, you can use analyze table to let the server perform analysis of key values. It provides better information for the optimizer.

Use the explain to verify the optimizer's actions. The Explain statement tells you whether to use an index. This information is helpful when you try to write a statement in a different way or check whether adding an index will improve the efficiency of your query execution.

Give the optimizer some hints when necessary. Under normal circumstances, the MySQL optimizer freely determines the order in which data tables are scanned to retrieve data rows as quickly as you can. In some cases the optimizer does not make the best choice. If you are aware of this phenomenon, you can use the Straight_join keyword to overload the optimizer's selection. Joins with Straight_join are similar to cross joins, but force the data tables to be joined in the order specified in the FROM clause.

There are two places in the SELECT statement where you can specify Straight_join. You can specify the location between the SELECT keyword and the select list, which will affect all cross joins in the statement, or you can specify it in the FROM clause. The following two statements have the same functionality:

SELECT Straight_join ... From T1, T2, T3 ...; SELECT ... from t1 straight_join T2 straight_join T3 ...;

Run this query separately with straight_join and without straight_join; MySQL may not use the index for whatever reason you think it is best (you can use explain to check MySQL's execution plan for each statement).

You can also use force index, using index, or ignore index to instruct the server on how to use the index.

Use the optimizer for more complete areas. MySQL can perform joins and subqueries, but subqueries are recently supported and added in MySQL 4.1. Thus, in many cases, the optimizer adjusts the join operation better than the subquery. This is a practical hint when your subquery executes slowly. Some subqueries can be expressed again using logically equal joins. If possible, you can rewrite the query to join to see if the execution is faster.

Test the standby form of the query and run it multiple times. When you test the alternate form of a query (for example, a subquery is compared to an equivalent join operation), each method should run multiple times. If both forms run only once, you will usually find that the second query is faster than the first, because the information from the first query remains in the cache so that the second query does not actually read the data from the disk. You should also run queries while the system load is relatively stable to avoid other transactions affecting the results of the system.

Avoid excessive use of MySQL automatic type conversions. MySQL performs automatic type conversion, but if you can avoid this conversion, you will get better performance. For example, if Num_col is an integer data column, the following queries will return the same result:

SELECT * from mytbl WHERE num_col = 4; SELECT * from mytbl WHERE num_col = ' 4 ';

But the second query involves type conversions. The conversion operation itself makes the performance worse by comparing the integer and string conversions to double. More seriously, if Num_col is indexed, comparison operations involving type conversions do not use indexes.