Preface:

In index optimization, we often see a sentence: if the index field appears implicit character set conversion, then the index will be invalid, and then to the full table scan, query efficiency will be greatly reduced, to avoid the occurrence of implicit character set conversion;

I would like to ask the students:

  • Do you know why implicit character set conversions cause index invalidation?

  • In actual scenarios, whether there is an index failure caused by implicit character set conversion and the specific troubleshooting process;

Main line of this article:

From the above two problems traction out the main line of this paper;

  • Briefly describe the cause of index invalidation caused by implicit character set conversion

  • Then the process of index invalidation caused by implicit character set conversion is simulated in a real scenario

Implicit character set conversion causes index invalidation

The data structure of the MySQL index is B+Tree. If you want to query the index, you must meet the rule of the left prefix. Otherwise, you cannot search through the index Tree.

For example, the following SQL invalidates the index by using a function on the index field

select * from t_user where SUBSTR(name, 1.2) = 'li tong'
Copy the code

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

select * from t_user where name like 'li tong %'
Copy the code

MySQL invalidates an index by using an implicit character set conversion function. MySQL invalidates an index by using an implicit character set conversion function. MySQL invalidates an index by using an implicit character set conversion function.

The index is invalid because MySQL automatically adds a conversion function to the index.

Simulation scenario + Troubleshooting

Because there are many reasons for index failure, if you write SQL how to see no problem, but by viewing the execution plan found is not to go index query, at this time will let a lot of people in trouble, this is exactly how to cause it?

Here are the tools that this article will focus on: Explain extended + show warnings;

Use this tool to display some extended information about SQL statements executed, including: MySQL optimization may add character set conversion functions, so that SQL statements that do not match the character set can be executed correctly;

Let’s talk about the use of extended + show Warnings in detail.

Simulation of implicit character set conversion scenarios:

Select * from table_name where character set is different;

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;
Copy the code

Then use stored procedures to construct the data:

Delete the stored procedure if it existsDROP PROCEDURE IF EXISTSproc_initData; DELIMITER $# Create the stored procedureCREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT  DEFAULT 1;
    WHILE i< =30DO # Add dataINSERT INTO t_employees ( em_no, de_no, info, em_name , age) VALUES ( CONCAT('001', i), '003'.'test11'.'test2', i ); # performedsqlstatementsSET i = i+1;
    END WHILE;
END$# Calls the stored procedureCALL proc_initData();

Copy the code

Mysql > select de_NO from T_employees; mysql > select de_NO from T_employees; mysql > select de_NO from T_employees;

Index invalid query SQL statement:

After the table and data are constructed, 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
Copy the code

Its implementation plan is as follows:

SQL > select * from t_employees where de_NO (select * from t_employees where de_NO (select * from t_employees where de_no (select * from t_employees));

Fortunately, I discovered the Explain Extended + Show Warnings tool by traveling around the network world. Using it, I quickly found the root cause of index failure, and then quickly found the solution.

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

Use sharp tools to quickly troubleshoot problems:

Note: The keyword EXTENDED (EXTENDED information) following explain was deprecated in MySQL5.7 and later, but the syntax is still recognized as backward compatible, so you don’t need to add EXTENDED after Explain in 5.7 and later;

EXTENDED keyword: the specific data access dev.mysql.com/doc/refman/…

Specific use methods are as follows:

First, open a command line interface in MySQL visual tools: Tools –> Command line interface

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;
Copy the code

③, then type show warnings; And press Enter, the following content will appear:

Through the displayed SQL extension information, it is found that MySQL automatically adds the character set conversion function when the character set is inconsistent, because it is added to the index field de_no conversion function, so it causes the index failure.

If we don’t look at the extended information, we might not find out that the character set is inconsistent until we look at the table structure, which would take a lot of time.

Extension: Implicit type conversion

After we talk about the above implicit character set conversion that causes index invalidation, let’s briefly talk about another implicit character set conversion that causes index invalidation.

Implicit type conversion: simply put, when the type of a field is inconsistent with the type assigned to it, implicit conversion is performed.

For example:

select * from t_employees where em_name = 123;
Copy the code

Em_name = vARCHAR; vARCHAR = varchAR; vARCHAR = varchAR;

At the end of this article, the MySQL version used in the above tests was 5.7;

♡ like + comment + forwarding yo

If this article is helpful to you, please wave your love to make a fortune of the little hand under the praise ah, your support is my continuous creation of power, thank you!

You can wechat search [Muzilei] public number, a large number of Java learning articles, you can have a look at yo!