A brief discussion on what situations in MySQL will cause index failure

Here are some tips from training institutions and my own summary:
In order to explain the following index content, we first create a temporary table test02

CREATE TABLE `sys_user` (
  `id` varchar(64) NOT NULL COMMENT 'Primary key',
  `name` varchar(64) DEFAULT NULL COMMENT 'name',
  `age` int(64) DEFAULT NULL COMMENT 'Age',
  `pos` varchar(64) DEFAULT NULL COMMENT 'Position',
  PRIMARY KEY (`id`),
  KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User table';

This table has four fields: primary key, name, age, position

Let's explain the first formula:
1. Full value match my favorite
2. Best Left Prefix Rule (Important)
Full value matching means that the order and number of the combined composite indexes must be the same as the order and number of the search conditions.
The best left prefix rule means that if multiple columns are indexed, the leftmost prefix rule must be followed. It means that the query starts from the leftmost column of the index and does not skip columns in the index <br /> Next, we create a composite index for this table

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);

The following is our search statement:

SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';

We can know whether the index is used by adding the keyword EXLAIN in front of the search statement.

(1) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
(2) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22;
(3) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND pos = 'java';

From the displayed results, we can see that we used all three fields of the first composite index, only two fields of the second composite index, and only one field of the third composite index. We use indexes in all three statements, and the first one is obviously the best.

Let's see what will fail:

(4) EXPLAIN SELECT * FROM sys_user WHERE age = 22;
(5) EXPLAIN SELECT * FROM sys_user WHERE pos = 'java';
(6) EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos = 'java';

The above three situations all become full table scans because they violate the leftmost prefix principle. Since the leftmost column of the composite index is name, the index will be invalid when the search condition name is not in the front. The first situation satisfies the full value match, the second satisfies the two fields name and age, and the third situation satisfies only name, so the index only uses name.

3. Do not perform any operations (calculations, function (automatic or manual) type conversion) on the index column, which will invalidate the index and convert it to a full table scan.

(7) EXPLAIN SELECT * FROM sys_user WHERE LEFT(name,1)='Xiaoming';

The seventh case fails because the index column performs calculations or function operations, resulting in a full table scan.

4. The storage engine cannot use the columns on the right side of the range condition in the index <br /> You may not understand what the above text means. Let's execute the query statement below to understand it.

(8) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age < 22 AND pos = 'java';

From the above figure, we can see that type has become a range level, which means that the index of the pos field after age<22 is invalid.

5. Try to use covering indexes (queries that only access the index (index columns and query columns are the same), and reduce the use of select * <br /> This is the literal meaning. Querying specific fields is more efficient than querying *. Let's compare it.

(9) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
(10) EXPLAIN SELECT name, age, pos FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';

6. MySQL cannot use indexes when using unequal (!= or <>), which will result in a full table scan
(!= or <>) usually matches a large amount of data. When the cost of using the index is greater than a full table scan, MySQL will give up using the index and choose a full table scan instead.

(11) EXPLAIN SELECT * FROM sys_user WHERE name != 'Xiaoming'

The results show that the index failure caused a full table scan

7. Is null, is not null, and indexes cannot be used
is null, is not null usually matches a large amount of data. When the cost of using the index is greater than a full table scan, MySQL will give up using the index and choose a full table scan instead.

(12) EXPLAIN SELECT * FROM sys_user WHERE name is not null

8. If like starts with a wildcard ('%abc...'), the MySQL index will become invalid and the operation will be a full table scan. (Writing % on the right can avoid index invalidation. If the business really needs '%abc...%', you can use a covering index to avoid index invalidation.)

(13) EXPLAIN SELECT * FROM sys_user WHERE name like '%明%'
(14) EXPLAIN SELECT * FROM sys_user WHERE name like '明%'
(15) EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

From the above results, the first index fails. The second one can avoid index failure by just writing the % on the right. The third one can solve the problem of index failure by using a covering index if the business really needs a SQL like '%abc...%'.

9. String indexing will fail if it is not enclosed in single quotes

(16) EXPLAIN SELECT * FROM sys_user WHERE name=222;

Because the search string must be enclosed in single quotes, the 222 used above is of int type. When searching, MySQL will determine that name is of varchar type and convert 222 to '222' for retrieval. The index column undergoes type conversion, so the index becomes invalid.

10. Use or less, as it will invalidate the index when used to connect

(16) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' or age = 22; 

This is the end of this article about the situations in which MySQL indexes will be invalid. For more information about MySQL index invalidation, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!