Source: author: WenAnShi my.oschina.net/floor/blog/4960148

The introduction

One busy afternoon, Liu Ge (old river’s lake, working for 5 years +) suddenly sighed: “Ah, mysql has a bug, there is an index does not go”.

Being a kind-hearted person, I immediately said, “Is there a function operation on the index field?”

Liu Elder brother meditated for 2 seconds, slightly pondering the small eyes looked at me, slowly said: “Brother Temperature, the conventional situation, the index field to do function operation, or string and number comparison caused by implicit conversion, this SQL are not involved.”

I listen to immediately came to the interest, slightly excited with Liu Ge said: “Liu Ge, you send SQL, I also study.”

Liu Ge slightly helpless looked at me, still sent me the SQL:

SELECT
 * 
FROM
 oc_order oo
 JOIN orders_detail od ON oo.order_id = od.order_id
Copy the code

It didn’t take much longer (nearly 20 minutes) for me to figure it out and pretend to be the master and say, word for word, “This is the implicit character set conversion problem.”

Liu elder brother listened to, baidu, straight beat thigh, “right, how to forget this.”

Location problem

1. Run Explain to view the execution plan

explain
SELECT
 * 
FROM
 oc_order oo
 JOIN orders_detail od ON oo.order_id = od.order_id
Copy the code

The table orders_detail is indeed driven without the index

The first table is the driver table, the following are the driven table, will take out data from the driver board as parameters, to the driven table query matching records.

2. View the index of the driven table

SHOW INDEX FROM  test.orders_detail;
Copy the code

3. View the field character set

SELECT
 COLUMN_NAME,
 character_set_name,
 collation_name 
FROM
 INformation_schema.`COLUMNS` 
WHERE
 TABLE_NAME = 'oc_order' 
 AND COLUMN_NAME = 'order_id';
Copy the code

SELECT
 COLUMN_NAME,
 character_set_name,
 collation_name 
FROM
 INformation_schema.`COLUMNS` 
WHERE
 TABLE_NAME = 'orders_detail' 
 AND COLUMN_NAME = 'order_id';
Copy the code

Mysql character set description

Utf8m4 is a superset of UTF8. Utf8 is compared to UTF8MB4, and utF8MB4 is converted to UTF8MB4.

validation

Adjust the SQL statement to cast the order_id of OC_Orders to UTF8.

explain
SELECT
 * 
FROM
 oc_order oo
 JOIN orders_detail od ON CONVERT ( oo.order_id USING UTF8 ) = od.order_id
Copy the code

View execution plan, indeed go index

The solution

1. Adjust SQL statements

SELECT
 * 
FROM
 oc_order oo
 JOIN orders_detail od ON CONVERT ( oo.order_id USING UTF8 ) = od.order_id
Copy the code

2. Adjust the character set to be consistent

conclusion

There are 3 possible cases of not going to the index:

  1. Perform functional operations on index fields
  2. Implicit type conversion, string compared to a number, string converted to a number
  3. Implicit character set conversion, UTF8m4 is a utF8 superset, utF8 is compared with UTF8MB4, utF8MB4 is converted to UTF8MB4.

Recent hot articles recommended:

1.1,000+ Java Interview Questions and Answers (2021)

2. I finally got the IntelliJ IDEA activation code thanks to the open source project. How sweet!

3. Ali Mock is officially open source, killing all Mock tools on the market!

4.Spring Cloud 2020.0.0 is officially released, a new and disruptive version!

5. “Java Development Manual (Songshan version)” the latest release, quick download!

Feel good, don’t forget to click on + forward oh!