1. Introduction

Select * from amj_devinfo where devcho = 77 LIMIT 20000, 10; Because the field devcho is of type vARCHar and THE argument I pass in is an int; Therefore, index failure is caused; Since found the problem, we will learn about the related problems caused by SQL type conversion index failure, and the knowledge behind the knowledge;

2. Relevant conclusions

In this paper, the conclusion is given first, and then, through the test to check whether the conclusion is correct; Conclusion: Index fields cannot be functionally manipulated. However, parameters of index fields can be functionally manipulated.

2.1 Construction of the predicate sentence

create table table1(    
id int NOT NULL PRIMARY KEY AUTO_INCREMENT, 
col1 int, col2 varchar(10),
key(col1),key(col2)
)ENGINE = INNODB DEFAULT CHARSET=utf8mb4;
Copy the code

We index COL1 and COL2; SQL directly;

2.2 Related Tests

EXPLAIN select id, col2 from table1 where col2 = '2';
Copy the code

Col2 = ‘2’; col2= ‘2’; col2= ‘2’;

EXPLAIN select id, col2 from table1 where col2 = 2;
Copy the code

At this time, change ‘2’ to 2, explain analysis result is:

First of all: To be clear, this is alsoIndexed. However, it is traversal based on index tree; Col2 = ‘2’ is not located by index tree; As we all know, index tree-based traversal is basically the same as a full table scan; So, that’s what index failure refers to. That is,If the index field is a string and the parameter is an integer, the index is invalid; What if the index field is an integer and the parameter is a string?

EXPLAIN select id, col1 from table1 where col1 = '2';
Copy the code

After testing, found that there is no problem; Why is that okay? Going back to the conclusion given earlier:Index fields cannot be functionally manipulated; however, parameters of index fields can be functionally manipulated. So, we can deduce that the index field col1 is an integer and the parameter is a string; Mysql casts parameter strings to integers, so indexes are not invalidated; Mysql > alter table col2; alter table col2; alter table col2; Therefore, index failure is caused;

validation

select "10" > 9;

If the answer is 1, you’re changing the “10” into a 10

If the answer is 0, that means you’re changing 9 to 9;

The result is 1;

conclusion:When a string conflicts with an integer, the string is converted to an integer;

This also verifies that index fields cannot be functionally manipulated, but index fields can be functionally manipulated.

supplement: Actually, as long as the rightThe index fieldThere are theFunction operation, no matter how simple, can cause index invalidation;

explain select id, col1 from table1 where col1 + 1 = 3;
Copy the code

Indexes are also invalid;

3. Implicit character coding conversion

In fact, the implicit character encoding conversion is also a function operation on the index field; It’s just that it’s hidden; Two tables with different character encoding types are required; Construction sentences:

 CREATE TABLE `order_error_log` (
  `id` varchar(32) NOT NULL,
  `order_id` varchar(32) DEFAULT NULL COMMENT 'order id'.PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order Log Table';


CREATE TABLE `order` (
  `id` varchar(32) NOT NULL,
  `create_user` varchar(32) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order sheet';

Copy the code

Analyze the following SQL:

explain select o.* from order_error_log oe INNER JOIN `order` o on  o.id = oe.order_id where oe.id ="Eleven";Copy the code

The analysis results are as follows:

Instead, we wanted the SQL statement to run like this:

  1. Id =”11″ in order_error_log, find the corresponding order_id;
  2. Order_id = oe.order_id; order_id = oe.order_id; order_id = oe.order_id;

At this point, both SQL queries should be primary key indexes; However, the current implementation process is as follows:

  1. Id =”11″ in order_error_log, find the corresponding order_id;
  2. Order_id = oe.order_id; order_id = oe.order_id; order_id = oe.order_id;

Order_id is of type UTF8MB4, while O. ID is of type UTF-8. Utf8mb4 is a superset of UTF8, so unt8 is converted to UTF8MB4 to ensure accuracy. Order_id = oe.order_id; order_id = oe.order_id; Therefore, the oid field is converted to UTf8MB4. In this case, the index cannot be used because of the function operation on the index field. That is, on the table O, there is no primary key index; Solution: Because the index field was cast so that the index cannot be used, we only need to change the conflicting index field; For example, change order_ID from order_error_log to UTF-8;

alter table order_error_log change order_id order_id varchar(32) character set utf8;
Copy the code

SQL statement analysis again: as expected