This article is participating in “Java Theme Month – Java Swipe Card”, see the activity link for details

Title: MySQL index failure scenario

Knowledge:

SQL queries can be optimized in the case of index failure

Verify the index failure scenario based on the following table structure

CREATE TABLE `dynamic_info` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL COMMENT 'Working Dynamic Title',
  `content` text COMMENT 'Work dynamic Content',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `creator` varchar(64) DEFAULT NULL COMMENT 'Founder',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Update time',
  `docid` text COMMENT 'Love number file ID',
  `version_num` int(11) DEFAULT NULL COMMENT 'Version number'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Work Dynamics';
Copy the code

Add a composite index and a full-text index

alter table dynamic_info 
    add index 
    idx_dynamic_titleCreatorVersionNum(title,creator,version_num);
   
 alter table dynamic_info 
    add FULLTEXT 
 idx_dynamic_content(content);
Copy the code

Lists the scenarios in which the index takes effect

  1. Use full value matching, all search conditions use index query, the index is valid
EXPLAIN select * from dynamic_info where title = 'notice' and creator = '5' and version_num = 1;
Copy the code

According to the execution plan, the total index length is 1287

  1. The query complies with the best left prefix rule. Only the first field of the combined index is used in the query condition, and the index is valid
EXPLAIN select * from dynamic_info where title = 'notice';
Copy the code

  1. If the first and second columns of the composite index are used as the query criteria, the index is valid
EXPLAIN select * from dynamic_info where title = 'notice' and creator = '5';
Copy the code

4. If the first three fields of the combined index are used as the query criteria, the index is valid

EXPLAIN select * from dynamic_info where title = 'notice' and version_num = 2;
Copy the code

Summary: The length of the index used in the first, second, third, and fourth cases is different due to the combination index matching conditions of the query conditions

The related scenarios of index failure are as follows:

1. If the second and third fields of the combined index are used as query conditions, the indexes are invalid

EXPLAIN select * from dynamic_info where creator = '5' and version_num = 1;
Copy the code

  1. Index failed to evaluate, function, or type on index field
EXPLAIN select * from dynamic_info where LEFT(title,1) = 'pass';
Copy the code

Left (STR,length) function is used to intercept the string, resulting in invalid index

  1. The storage engine cannot use the column to the right of the range condition in the index. The index is partially invalid
EXPLAIN select * from dynamic_info where title = 'notice' and creator = '5';

EXPLAIN select * from dynamic_info where title = 'notice' and creator > '5' and version_num = 2;
Copy the code

Combined with the previous analysis of KEY_LEN, when the index uses one, two, and three fields, KEY_len corresponds to 1023, 1282, and 1287 respectively. Here, only the title and Creator fields are used in the query. Since Creator is a range query, the index on the version_num field of the index is invalid

  1. Try to use overwrite index, select index field query, reduce use select *
EXPLAIN select title,creator,version_num from dynamic_info where title = 'notice' and version_num = 1 and creator = '5';

EXPLAIN select * from dynamic_info where title = 'notice' and creator = '5' and  version_num = 1 ;
Copy the code

Select title,creator,version_num Using overwrite index, index type is ref, performance is better than range, in addition to Extra display Using index, table name only need to find data from the index tree, do not need to look up in the table, While select * may need to query other non-index fields, Extra displays Using WHERE to indicate a lookup in the table. Therefore, select * has lower performance than the SELECT index field

  1. The index field is of VARCHAR type and the query condition is not quoted. Therefore, the index is invalid
EXPLAIN select * from dynamic_info where title = 20210520;
Copy the code

When MySQL executes an SQL statement, it optimizes the SQL statement, converts the number into String, and invalidates the index. To resolve the index invalidates, use the following methods: If the field type is Varchar and the query value is a number, you need to add single quotation marks to make the index valid

  1. The query condition is not equal to (! = or <>), the index is invalid
EXPLAIN select * from dynamic_info where title ! = 'notice';
EXPLAIN select * from dynamic_info where title <> 'notice';
Copy the code

7. If the query condition is NULL or IS not NULL, the index is invalid

EXPLAIN select * from dynamic_info where content is null;
EXPLAIN select * from dynamic_info where content is not null;
Copy the code

8. Start with a wildcard like (%? %), the index is invalid

EXPLAIN select * from dynamic_info where content like '% notice';
EXPLAIN select * from dynamic_info where content like '% % notice';
EXPLAIN select * from dynamic_info where content like 'notice %';
Copy the code

For an existing composite index, use SQL that starts with the like wildcard. You can use overwrite indexes, that is, all select fields are indexed

EXPLAIN select title,creator,version_num from dynamic_info where title like '% % notice';
Copy the code

9. The index is invalid if the or connection condition is used

EXPLAIN select content from dynamic_info where content = 'notice' or content = '5';
Copy the code