Why is MySQL query not indexed? This article takes you to a comprehensive analysis

At work, I often encounter such a problem. I obviously added an index to the MySQL table, but why is the index not used when executing the SQL query?

The same SQL query sometimes uses an index, but sometimes it doesn't. What's going on?

The reason may be that the index is invalid. The reasons for the failure are as follows. Have you ever stepped on a similar pit?

1. Data preparation:

There is such a user table, create an index on the name field:

 CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB COMMENT='用户表';

2. Explain in detail:

Want to see if an SQL uses an index? What type of index is used?

You can use the explain keyword to view the SQL execution plan. E.g:

 explain select * from user where id=1;

You can see that type=const, indicating that the primary key index is used.

All types of explain are as follows:

3. Reasons for failure

1. Implicit conversion of data types

The name field is of varchar type. If we use the data type query, data type conversion will occur. Although no error is reported, the index cannot be used.

 explain select * from user where name='一灯';

 explain select * from user where name=18;

2. Fuzzy queries like start with %

 explain select * from user where name like '张%';

 explain select * from user where name like '%张';

3. The index is not used before and after or at the same time

Although an index is added to the name field, the age field does not have an index, and a full table scan is performed when using or.

 # or前后没有同时使用索引，导致全表扫描
explain select * from user where name='一灯' or age=18;

4. Joint index, not using the first column index

If we build a joint index on (name, age), but only the age field is used in the query condition, the index cannot be used.

With a union index, you must follow the leftmost match principle, using the first column field first, then the second column field.

 CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB COMMENT='用户表';

5. Perform calculations on index fields

If we perform a calculation operation on the index column, the index cannot be used.

 # 在主键索引上进行计算操作，导致全表扫描
explain select * from user where id+1=2;

6. Use functions on index field fields

If we use the function on the index column, the index will not be used.

7. The optimizer chooses the wrong index

The same SQL query sometimes uses an index, but sometimes it doesn't. What's going on?

This may be the result of the optimizer's choice whether to use an index or not based on the amount of data in the table.

When most of the names in the table are one lamp, then use name='one lamp' to query, will the index be used?

The index optimizer will think that using an index is not as fast as a full table scan, so don't use an index at all.

Of course, we think that the optimizer is not optimizing correctly, and we can also use force index to force the use of the index.

Summary of knowledge points:

The article is continuously updated, and you can search for "One Light Architecture" on WeChat to read more technical dry goods as soon as possible.