Must-know SQL statement is not a useful tool for troubleshooting when indexing

Foreword:

When index optimization, we often see a sentence: If the index field has an implicit character set conversion, then the index will be invalid, and then converted to a full table scan, query efficiency will be greatly reduced, to avoid implicit character set conversion ；

Here I would like to ask students:

• Do you know why the implicit character set conversion will cause the index to become invalid?
• Have you ever encountered a scenario where the implicit character set conversion caused index failure in the actual scene? The specific investigation process;

Main line of this article:

The main line of this article is drawn from the above two questions;

• Briefly describe the reasons for the index failure caused by implicit character set conversion
• Then simulate the actual scene to investigate the process of implicit character set conversion causing index failure

Implicit character set conversion causes index failure

The data structure of the MySQL index is B+Tree. If you want to use the index query, you must meet its leftmost prefix principle , otherwise you cannot search through the index tree and can only perform a full table scan;

For example: the following SQL uses a function to perform operations index field

select * from t_user where SUBSTR(name, 1, 2) = '李彤'

How can the above SQL be modified to make the index effective? As follows:

select * from t_user where name like '李彤%'

From the above small example, you can know that if you use function operations on the index field, the index will fail, and the implicit character set conversion of the index field is because MySQL will automatically add the conversion function This will cause the index to become invalid;

Then we will use the simulated actual scene to see if the index is invalid due to the automatic conversion function added by MySQL;

Simulation scenario + troubleshooting

Since there are many reasons for index failure, if you write SQL yourself, you can see that there is no problem, but by looking at the execution plan, it is found that there is no index query. At this time, many people will get into trouble. How did this happen?

At this time, the tools that this article will focus on are about to debut: explain extended + show warnings ;

Use this tool to display some extended information of the executed SQL statement, including: MySQL may add character set conversion functions when optimizing, so that SQL that does not match the character set can be executed correctly;

Let’s talk about the use of explain extended + show warnings in detail below;

Simulate implicit character set conversion scenarios:

First create two 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;

and then use the stored procedure to construct the data:

# 如果存储过程存在则删除 
DROP PROCEDURE IF EXISTS proc_initData;

DELIMITER $
# 创建存储过程
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT  DEFAULT 1;
    WHILE i<=30 DO
        # 新增数据
        INSERT INTO t_employees ( em_no, de_no, info, em_name , age) VALUES ( CONCAT('001', i), '003', 'test11', 'test2', i ); #执行的sql语句
        SET i = i+1;
    END WHILE;
END $

# 调用存储过程
CALL proc_initData();

Note: When constructing the data, remember de_no Field Values will t_employees table discrete some , because if the index field values distinguish very low words, then MyQSL optimizer through statistical analysis of the sample and found that the index query Similar to the full table scan performance, the full table scan will be performed directly;

The query SQL statement for

After constructing the table and data, we use SQL statements 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

The execution plan is as follows:

It is found that the de_no field in the t_employees table has an index, but there is no index query, a full table scan with type=ALL, but there is no problem by checking the SQL statement. It seems that the conditions for the index query are satisfied on the surface. I found that I was in trouble and distressed!

Fortunately, through traveling in the online world, I finally discovered the explain extended + show warnings tool, used it to quickly find the root cause of the index failure, and then quickly found a solution;

Let’s talk about the specific use of this weapon, Kaisen!

Use sharp tools to quickly troubleshoot problems:

Note: The keyword EXTENDED (extended information) following explain is obsolete in MySQL 5.7 and later versions, but this syntax is still recognized as backward compatible, so in version 5.7 and later versions, it is not necessary to follow explain Added EXTENDED;

Specific reference materials for the EXTENDED keyword: https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html

specific usage of 160e461f06fe69 is as follows:

① First, open a command line interface in the MySQL visualization tool: Tools --> Command Line Interface

②. 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 press Enter, the content as shown in the figure below will appear:

Through the displayed extended SQL execution information, it is found that MySQL automatically adds the character set conversion function when the character set is inconsistent, because it is the conversion function added on the index field de_no , which causes the index to fail;

And if we didn't look at the extended information, then we may not find out that it is caused by the inconsistency of the character set until we look at the table structure, which will take a lot of time;

Extension: implicit type conversion

Let’s talk about the situation where the above implicit character set conversion causes the index to fail, let’s talk briefly about another implicit type conversion causes the index to fail;

Implicit type conversion: Simply put, when the type of the field is inconsistent with the type assigned to it, an implicit conversion will be performed;

The small example is as follows:

select * from t_employees where em_name = 123;

In the above SQL, em_name is an index field, and the field type is varchar. When assigning an int type value to it, the index will be found to be invalid. You can also view it through explain extended + show warnings , and you will find the content shown in the following figure:

So far this article enters the end, and I will explain here that the MySQL version used in the above test is 5.7 ;

❤ Like + comment + forward

If this article is helpful to you, please wave your little hand that loves to make a fortune and like it. Your support is the motivation for my continuous creation, thank you!

You can search on [ public account, a large number of Java learning dry goods articles, you can come and have a look!