Shulou(Shulou.com)06/02 Report--

This article mainly shows you "what will lead to index failure in mysql", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what will lead to index failure in mysql" this article.

To explain the following index, let's first create a temporary table test02

CREATE TABLE `sys_ user` (`id`varchar (64) NOT NULL COMMENT '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, and position.

Let's explain the first formula:

1. Full value matching is my favorite.

two。 Best left prefix rule (important)

Full-value matching means that the order and number of simultaneous composite indexes should be the same as the conditional order and number of retrieval.

The best left prefix rule means that if multiple columns are indexed, follow the leftmost prefix rule. The query starts at the leftmost front column of the index and does not skip the columns in the index

Let's create a composite index on 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 or not to use the index by adding the keyword EXLAIN before the retrieval 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 results shown, we can see that we used all three fields in the first composite index, only two fields in the second composite index, and only one field in the third composite index. We use indexes for all three statements, and obviously the first one is the best.

Let's see which situation 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 cases have become full table scans because they violate the leftmost prefix principle, because the leftmost part of the composite index is name. When the search condition name is not in front of the index, the index will fail. The first case satisfies full-value matching, the second satisfies two fields name and age, and the third only uses name because it only satisfies name.

3. Doing nothing on the index column (calculation, function (automatic or manual) type conversion) will invalidate the index and turn it into a full table scan

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

The seventh case of failure is because the index column has done calculations or functional operations, resulting in a full table scan.

4. The storage engine cannot use the column to the right of the scope condition in the index

Maybe you don't know what it means when you look at the above text. Let's execute the query statement to make it clear.

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

From the picture above, we can see that type has become the scope level, that is to say, age

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.