SQL optimization case: implicit conversion

MySQL is one of the most popular relational databases at present. With the rapid development of the Internet, MySQL database is widely used in the production systems of e-commerce, finance and many other industries.

In the actual development, operation and maintenance process, we must often encounter the problem of slow SQL. A SQL with poor performance will often bring too much performance overhead, which will lead to the excessive use of the resources of the whole operating system, and even cause session accumulation and online failure.
In the scenario of SQL tuning, a common problem is implicit type conversion. So what is implicit conversion?

In MySQL, when operators are used with operands of different types, type conversion occurs to make operands compatible, and implicit conversion occurs. Implicit conversion often means that the execution efficiency of SQL will be greatly reduced.
Next, the author will combine several common scenarios to let you actually experience what implicit conversion is and how to deal with the situation of implicit conversion. Please read the following cases.

Implicit conversion caused by inconsistent transfer data type and field type

A classic scenario is the implicit conversion caused by the inconsistency between the transfer data type and the field type, which is also the most common scenario we usually encounter. See the following example for details:

1) Scenarios to be optimized

SQL and execution plan are as follows:

select * from dt_t1 where emp_no = 41680;

The table index is as follows:

key idx_empno (`emp_no`)

2) Scene analysis

From the Type part of the execution plan: ALL, full table scanning without idx_empno index. In general, the data Type passed may be inconsistent with the actual field Type. Let's take a look at the specific table structure.

root@localhost mysql.sock 5.7.28-log :[employees] 14:48:10>desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | varchar(14)   | NO   | MUL | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

It can be seen in the table structure that the field type is varchar and the transfer field is integer, resulting in implicit conversion and no index.

3) Scene optimization

The SQL can be rewritten simply to avoid implicit conversion, as follows:

select * from dt_t1 where emp_no='41680';

When the incoming data is a varchar type consistent with the matching field, the index can be used normally. The optimization effect is as follows:

Implicit conversion caused by inconsistent associated field types

In addition to the query scenario of constant matching, there will also be implicit conversion when the associated fields of associated queries are inconsistent.

1) Scenarios to be optimized

SELECT  count(*) from t1  as a
JOIN  `t2`  b on a.`id` = b.`alipay_order_no` ;

2) Scene analysis

From the execution plan, we can see the driven table b, Extra:Range checked for each record (index map: 0x8)

Generally, when we see the Range checked for each record (index map: 0x8), implicit conversion may occur. Let's see how the official document explains it

Range checked for each record (index map: N) (JSON property: message)
MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a range or index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The applicability criteria are as described in Section 8.2.1.2, "Range Optimization", and Section 8.2.1.3, "Index Merge Optimization", with the exception that all column values for the preceding table are known and considered to be constants.
Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map value N is a bitmask value that indicates which indexes are candidates. For example, a value of 0x19 (binary 11001) means that indexes 1, 4, and 5 will be considered.

View the structure of the following table:

CREATE TABLE `t2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `alipay_order_no` varchar(45) DEFAULT NULL,
 xxxx
 PRIMARY KEY (`id`),
 KEY `idx_alipay_order_no_temp` (`alipay_order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2539968 DEFAULT CHARSET=utf8
 A total of 1 line of records is returned,Cost 5 ms.
 CREATE TABLE `t1` (
 `id` bigint(20) NOT NULL,
 xxxxxx
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 A total of 1 line of records is returned,Cost 5 ms.

We observed from the table structure that the associated field data is of type int and varchar.

When this scenario occurs, how should we optimize it?

Let's go back to the specific execution plan. The drive table is a and the driven table is B; Association condition: a.id = b.alipay_order_no ; When the field ID of table a is a constant, it is passed to b.alipay_ order_ When no, column occurs_ If the type is inconsistent and the index cannot be used, let's make the field type passed by a.id and b.alipay_ order_ If no is consistent, you can use the index?

3) Scene optimization

We can perform explicit type conversion on the associated field of the driven table to make it consistent with the type of the associated field of the driven table. The SQL after rewriting is as follows:

SELECT  count(*)
from `t1`a
 JOIN `t2` b on CAST( a.`id` AS CHAR ) = b.`alipay_order_no`

After rewriting, you can normally use the index for association. The execution plan is as follows:

Implicit conversion caused by inconsistent character sets

In the two previous scenarios, the data types on both sides of the operator are different. In fact, implicit conversion may occur if the data types are the same. For example, the following example of implicit conversion caused by inconsistent character sets:

1) Scenarios to be optimized

SQL and execution plan are as follows:

SELECT COUNT(*)
FROM `t1`  o
join `t2`  og  ON `o`.`def8`= `og`.`group_id`
WHERE  o.`def1`= 'DG21424956'

2) Scene analysis

From this execution plan, we can see that the second list og contains using join buffer (Block Nested Loop), TYpe=ALL

Generally, in this case: using join buffer (Block Nested Loop), a. the associated field has no index, b. implicit conversion, etc

See the structure in the following table:

create table t1(
 ..... 
 `group_id` varchar(20) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `group_id` (`group_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
create table t2(
 ..... 
 `def8` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_tr_def1` (`def8`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

We can see from the table structure that there are indexes in the associated fields, but the character set is different, t1 utf8, t2 utf8mb4

3) Scene optimization

The idea of SQL rewriting is similar to the above example. We convert the character set of the associated fields of the driving table, as follows:

SELECT COUNT(*)   FROM `t1`  o
left join `t2` og  ON CONVERT(  o.`def8`  USING utf8 ) = `og`.`group_id`
WHERE  o.`def1`= 'DG21424956

After being converted into a consistent character set, it can be associated through the index

Implicit conversion caused by inconsistent verification rules

So, as long as the data types and character sets on both sides of the operator are consistent, will there be no implicit conversion?

The answer is no, because another important attribute of the character set is the verification rules. When the verification rules are inconsistent, there will be implicit conversion. See the following example for details:

1) Scenarios to be optimized

SELECT *
FROM `t1`
WHERE `uuid` in (SELECT uuid  FROM t2 WHERE project_create_at!= "0000-00-00 00:00:00")

The SQL execution plan is as follows:

2) Scene analysis

The table structure of the two tables is as follows:

CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,  `
 uuid` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'UUID',
 xxxxxx
 PRIMARY KEY (`id`),
UNIQUE KEY `uuid_idx` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=2343994 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `t2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `uuid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'project uuid',
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=5408 DEFAULT CHARSET=utf8

From the table structure, we can see that t1 table, as the driven table uuid, has a unique index, and the data types and character sets of associated fields are consistent, but the different verification rules make it impossible to use the index in this scenario.

3) Scene optimization

We can rewrite the following to display and define the verification rules of the associated fields of the driving table to make it consistent with the driven table

explain extended
select b.*
from (select  uuid COLLATE utf8_unicode_ci as uuid
from t1 where project_create_at != "0000-00-00 00:00:00") a, t2 b
where a.uuid = b.uuid
+--------------+-----------------------+--------------------+----------------+-----------------------+-------------------+---------------+----------------+-----------------------+
| id           | select_type           | table              | type           | key                   | key_len           | ref           | rows           | Extra                 |
+--------------+-----------------------+--------------------+----------------+-----------------------+-------------------+---------------+----------------+-----------------------+
| 1            | PRIMARY               | <derived2>         | ALL            |                       |                   |               | 51             |                       |
| 1            | PRIMARY               | b                  | eq_ref         | uuid_idx              | 386               | a.uuid        | 1              |                       |
| 2            | DERIVED               | volunteer_patients | range          | idx-project-create-at | 6                 |               | 51             | Using index condition |
+--------------+-----------------------+--------------------+----------------+-----------------------+-------------------+---------------+----------------+-----------------------+
A total of 3 lines of records are returned,Cost 4 ms.

It can be seen that the rewritten SQL normally uses the index for Field Association, which achieves our expected effect.

summary

The scenarios of implicit conversion mainly include inconsistent field types, inconsistent associated field types, inconsistent character set types or inconsistent proofreading rules. When there are SQL performance problems caused by implicit conversion, analyze the corresponding scenarios and apply the medicine to the case.

In addition, implicit conversion may lead to inaccurate query result sets, inconsistent character sets, and master-slave synchronization errors. Therefore, we should try to avoid them in practical use.

For more technical articles, please pay attention to the official account "YUNCHE"
You can also view it on the official website https://www.dtstack.com/dtsmart/

Tags: Database SQL cloud serving

Posted by volant on Tue, 10 May 2022 09:16:01 +0300