Official account: Java Xiaokaxiu, website: Javaxks.com

Author: WenAnShi, link: my.oschina.net/floor/blog/4960148

The introduction

On a busy afternoon after the Spring Festival of 2021, Brother Liu (a veteran who has worked in this field for 5 years +) suddenly sighed: “Ah, there is a bug in mysql. There is no index.”

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.

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

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

Adjust the character set consistency

conclusion

Three cases where you might not go to the index

1. Perform function operations on index fields

2. Implicit type conversion. A string is converted to a number by comparing it with a number

3. Implicit character set conversion, UTF8m4 is a utF8 superset, utF8 is compared with UTF8MB4, utF8MB4 will be converted to UTF8MB4.