Preface:

If there is an implicit character set conversion (SCS) in the index field, the index will be invalid, and the query efficiency will be greatly reduced. Therefore, it is necessary to avoid implicit character set conversion (SCS).

Here I would like to ask the students:

  • Do you know why implicit character set conversion causes index invalidation?
  • Have you ever encountered an index failure caused by implicit character set conversion in the actual scene? The specific troubleshooting process;

Main line of this article:

By the above two problems traction out of the main line of this article;

  • Briefly describe the reasons why implicit character set conversion causes index invalidation
  • Then, the process of index failure caused by implicit character set conversion is simulated in a real scenario

Causes of index invalidation caused by implicit character set conversion

The data structure of MySQL index is B+Tree. If you want to go to the index query, it must satisfy its leftmost prefix principle. Otherwise, you cannot search through the index Tree and can only scan the whole table.

For example: The following SQL invalidates an index by using a function on an index field

Select * from t_user where SUBSTR(name, 1, 2) = 'SUBSTR'

How can the above SQL be modified to make the index work? This is as follows:

Select * from t_user where name = '%' and name =' %';

The above example shows that using a function on an indexed field will invalidate the index. Implicit character set conversion on an indexed field will invalidate the index because MySQL automatically uses a conversion function on an indexed field.

Then let’s simulate the actual scenario to see if MySQL automatically adds a conversion function to cause index failure;

Simulate scenario + problem troubleshooting

Since there are many reasons for index failure, if you write your own SQL is no problem, but by looking at the execution plan found that there is no index query, this will let a lot of people in trouble, how is this caused?

This is where the focus of this article comes in: Explain Extended + Show Warnings;

Using this tool, you can show some extensions to SQL statements that are executed. These extensions include: SQL optimizations may add a character set conversion function to enable SQL that does not match the character set to execute correctly;

Let’s talk more about Explain Extended + Show Warnings;

Scenarios for simulating implicit character set conversion:

Mysql > create tables with different character sets;

CREATE TABLE `t_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `de_no` varchar(32) NOT NULL,
  `info` varchar(200) DEFAULT NULL,
  `de_name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_de_no` (`de_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `t_employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `em_no` varchar(32) NOT NULL,
  `de_no` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `info` varchar(200) DEFAULT NULL,
  `em_name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_em_no` (`de_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

Then use the stored procedure to construct the data:

DROP PROCEDURE IF EXISTS proc_initData DROP PROCEDURE IF EXISTS proc_initData DELIMITER $# CREATE PROCEDURE proc_initData() BEGIN DECLARE I INT DEFAULT 1; INSERT INTO T_EMPLOYEES (EM_NO, DE_NO, INFO, EM_NAME, AGE) VALUES (CONcat ('001', I), '003', 'test11', 'test2', i ); SET I = I +1; END WHILE; END $# CALL proc_initData();

Note: When structing the data, remember to construct the DE_NO value in the T_EMPLOYEES table discretenously, because if the index value is very low in distinction, then the MyQSL optimizer finds that the index query performs the same performance as the full table scan, and will perform the full table scan directly.

SQL > index invalidated query

After the table and data are constructed, we use the SQL statement to query, and then look at its execution plan;

explain 
select * from t_department a LEFT JOIN  t_employees b on a.de_no = b.de_no where a.id = 16

Its implementation plan is as follows:

SELECT * FROM t_employees WHERE de_no = type=ALL; SELECT * FROM t_employees WHERE de_no = type=ALL; SELECT * FROM t_employees WHERE de_no = type=ALL; SELECT * FROM t_employees WHERE de_no = type=ALL; SELECT * FROM t_employees WHERE de_no = type=ALL;

Fortunately, after traveling around the web world, I finally found the Explain Extended + Show Warnings tool, which enabled me to quickly discover the root cause of index failure and then find a solution.

The following will talk about the specific use of this weapon, open Sen!

Use sharp tools to quickly troubleshoot problems:

Note: The keyword EXTENDED after explain has been deprecated in MySQL5.7 and later, but the syntax is still recognized as backward compatible, so in 5.7 and later, you can eliminate the need to add EXTENDED after explain.

EXTENDED keyword specific data access: https://dev.mysql.com/doc/ref…

Specific use methods are as follows:

1, First open a command column interface in MySQL visualization tool: tool -> command column interface

2, Then enter the following SQL and press Enter:

explain EXTENDED
select * from t_department a LEFT JOIN  t_employees b on a.de_no = b.de_no where a.id = 4019;

③ Then enter the command show warnings; And enter, the content as shown in the figure below will appear:

By displaying the SQL extension information, it is found that MySQL automatically adds the character set conversion function when the character set is inconsistent. Because the conversion function is added on the index field de_no, it causes the index invalidation.

If we do not look at the extension information, we may not realize that it is caused by inconsistent character set until we look at the table structure, which will take a lot of time.

Extension: Implicit type conversion

After discussing the above implicit character set conversion, let’s briefly discuss the other implicit type conversion which can result in index failure.

Implicit conversion: Implicit conversion occurs when the type of a field is inconsistent with the type assigned to it.

For example:

select * from t_employees where em_name = 123;

Explain Extended + Show Warnings and Warnings for EM_NAME = INDEX_NAME = VARCHAR;

At the end of this article, it’s important to note that the versions of MySQL used in the tests above are all the same
5.7 ；

Athumb up + comment + retweet yo

If this article is helpful to you, please wave down your love to make a fortune of the small hand to click the like ah, your support is the power of my continuous creation, thank you!

You can WeChat search [Muzi Lei] public number, a lot of JAVA learning dry goods article, you can have a look at yo!