Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to optimize query performance in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

WHERE conditional fields use functions

Suppose we have the following statement to create a table

Mysql > CREATE TABLE `tradelog` (`id` int (11) NOT NULL, `tradeid` varchar (32) DEFAULT NULL, `operator` int (11) DEFAULT NULL, `tmodified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`), KEY `tmodified` (`tmodified`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The above is a business table with a time dimension, at this point if we want to query only the number of transactions in July in all the data. At this point, we might think of the following SQL

Mysql > select count (*) from tradelog where month (t_modified) = 7

From the table-building statement above, we can see that the index is built on t_modified. At this point, if we want to query the above SQL query, the execution process will be as follows:

As can be seen from the above figure, when the index field is functioned, it may cause the index structure to be out of order. Therefore, MySQL will abandon the query structure of the search tree and replace it with a full index scan. (the optimizer chose to traverse the entire table with the t_modified index instead of the primary key index because the t_modified index is relatively small.)

Usually, we need to optimize SQL manually. Of course, this often needs to be handled in combination with specific business data. For example, the above query may be optimized as follows:

Select count (*) from tradelog where (t_modified > = '2016-7-1' and t_modified

< '2016-8-1') or (t_modified >

= '2017-7-1' and t_modified

< '2017-8-1') or (t_modified >

= '2018-7-1' and t_modified

< '2018-8-1'); 对于MySQL 的简单查询来说，还有一个坑就是： SELECT * FROM tradelog WHERE id + 1 = 999; 这个时候，MySQL 也不会主动的去做 "移项"的优化，此时也会造成全表扫描。 字段隐式转换 MySQL 中的字段隐式转换可能会引起索引不可用，下面我们先看一个字符与数字比较的例子。如下所示： mysql>

Select'10'> 9

When we execute the above SQL, we get the following result

As can be seen from the execution results, character types are converted to numeric types by default. Note that: '10'-> 10,' 10A'- > 10, but 'A10'-> 0, the conversion will filter out invalid characters, but you need to start with a number, otherwise it will be converted to 0.

Now let's look at the following sentence:

Mysql > explain select * from tradelog where tradeid = 222

Because tradeid is of type VARCHAR, MySQL converts it to a number and compares it, resulting in an index being unavailable and a full table scan. When we query the int type field, the corresponding value value can be arbitrarily 10 or '10', which will be converted to the number 10, using the index. The above statement execution is equivalent to the following:

Mysql > explain select * from tradelog where CAST (tradeid AS signed int) = 222

That is, a hidden function operation is used on the query field, resulting in a full table scan.

Implicit character coding conversion

The above case describes the type conversion between different types of fields. Implicit conversions may also occur for different character set encodings of the same type (VARCHAR). Let's create another log detail sheet (trade_detail), and then write some data, as shown below:

Mysql > CREATE TABLE `tradeid` (`id` int (11) NOT NULL, `tradeid` varchar (32) DEFAULT NULL, `trade_ step` int (11) DEFAULT NULL, / * procedure * / `step_ info` varchar (32) DEFAULT NULL, / * step information * / PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into tradelog values (1, 'aaaaaaaa', 1000, now ()); insert into tradelog values (2,' aaaaaaab', 1000, now ()) Insert into tradelog values (3, 'aaaaaaac', 1000, now ()); insert into trade_detail values (1,' aaaaaaaa', 1, 'add'); insert into trade_detail values (2,' aaaaaaaa', 2, 'update'); insert into trade_detail values (3,' aaaaaaaa', 3, 'commit'); insert into trade_detail values (4,' aaaaaaab', 1, 'add'); insert into trade_detail values (5,' aaaaaaab', 2, 'update') Insert into trade_detail values (6, 'aaaaaaab', 3,' update again'); insert into trade_detail values (7, 'aaaaaaab', 4,' commit'); insert into trade_detail values (8, 'aaaaaaac', 1,' add'); insert into trade_detail values (9, 'aaaaaaac', 2,' update'); insert into trade_detail values (10, 'aaaaaaac', 3,' update again') Insert into trade_detail values (11, 'aaaaaaac', 4,' commit')

When we need to query all transaction details (trade_detail) in a transaction (trade_log), we might use the following SQL

Mysql > explain select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2

The query above is executed on this record of trade_log 's id = 2, using the primary key index and scanning the number of rows by 1; but is it strange that the second one does not use the tradeid index on trade_detail.

In the above execution plan, first query the id=2 record from the trade_log, and then match the trade_detail. Here, trade_log is called the driven table, and trade_detail is called the driven table. The execution process is as follows:

So why is the second execution plan above not indexed? if you look closely, you will find that the above two tables are created with different character set codes, one is utf8 and the other is utf8mb4. Utfutf8mb4 is a superset of the utf8 character set, and when we compare the fields of the two tables, utf8 is converted to utf8mb4 (to avoid loss of precision).

Step 3 in the figure above can be thought of as doing the following ($L2.tradeid.value is the character value of utf8mb4):

Mysql > select * from trade_detail where tradeid = $L2.tradeid.value

The execution SQL after implicit conversion is as follows:

Mysql > select * from trade_detail where CONVERT (tradeid USING utf8mb4) = $L2.tradeid.value

In view of this, the function is used on the query field tradeid of trade_detail during execution, so the index is not taken. But what happens when we do the reverse query, that is, to disassociate the corresponding trade_log from a trade_detail?

Mysql > explain select l.operator from tradelog l, trade_detail d where d.tradeid=l.tradeid and d.id=4

As you can see from the figure above, the second query uses tradelog's tradeid index. When the execution plan finds the record of id=4 in trade_detail (R4), and then associates the corresponding record in tradelog, the SQL executed is as follows:

Mysql > select operator from tradelog where traideid = $R4.tradeid.value

At this point, the value value to the right of the equal sign needs to be implicitly converted, and there is no functional operation on the index field, as shown below:

Mysql > select operator from tradelog where traideid = CONVERT ($R4.tradeid.value USING utf8mb4)

Solution

The unavailability of indexes caused by different character sets can be solved in the following 2 ways.

Modify the character set encoding of the table.

Mysql > alter table trade_detail modify tradeid varchar (32) CHARACTER SET utf8mb4 default null

Manual character encoding conversion.

Mysql > select d. * from tradelog l, trade_detail d where d.tradeid=CONVERT (l.tradeid USING utf8) and l.idroom2; after reading the above, have you mastered how to optimize query performance in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.