Shulou(Shulou.com)05/31 Report--

This article introduces what are the rules of MySQL index, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

First, the best left prefix rule

1. Define

In the case of a multi-column index, the query starts with the leftmost front column of the index and cannot skip the columns in the index.

The best left prefix rule means that if multiple indexes are created, they should be used in the order in which they were created, and cannot be missing or skipped, of course, if only the leftmost index column is used, that is, the first index is OK.

two。 Environmental preparation

DROP TABLE IF EXISTS `tb_ emp`; CREATE TABLE `tb_ emp` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (20) NOT NULL, `age` int (11) NOT NULL, gender varchar (10) NOT NULL, email varchar (20), PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `tb_ emp` (name,age,gender,email) VALUES ('Tom',' 22) INSERT INTO `tb_ emp` (name,age,gender,email) VALUES ('Mary',' 21)); INSERT INTO `tb_ emp` (name,age,gender,email) VALUES ('Jack',' 27)); INSERT INTO `tb_ emp` (name,age,gender,email) VALUES ('Rose',' 23)

3. Create a composite index

Create index idx_all on tb_emp (name,age,gender); show index from tb_emp

Here the locomotive represents name, the carriage represents age, and the rear represents gender.

4. Only locomotives.

Description:

The order of index creation is name,age,gender

Directly use name (locomotive) as a condition, you can see type=ref,key_len=82,ref=const, the effect is OK.

5. Only carriages.

Description: do not use the locomotive (name), directly use the car, resulting in full table scan (type=ALL)

6. Locomotive plus car, locomotive plus rear

Description:

Locomotive plus car, locomotive plus rear, although they are all type=ref, but observing key_len and ref, and comparing the results in only the locomotive, it can be concluded that when using the locomotive (name) and the rear (gender), only part of the index is used, that is, the index of the locomotive (name).

Popular understanding: there is no problem for the locomotive to run alone, and it is no problem for the locomotive to run with the directly connected carriages, but if there is no car in the middle, the locomotive can only run on its own.

7. Locomotive plus car and rear

Explanation: the locomotive plus the car plus the rear of the train, the three in series, it becomes a running train. Type=ref,key_len=128,ref=const,const,const .

2. Index columns are not calculated

Any operation on the index column (evaluation, function, (automatic or manual) type conversion) will cause the index to fail and turn to a full table scan.

1. Function calculation

Description: the function calculation, type=ALL, is used here, which leads to the invalidation of the index.

two。 Implicit type conversion

Description: where '123' is a string and 123 is a number, an implicit type conversion occurred, resulting in a full table scan (type=ALL)

III. All index columns on the right side of the range are invalid

The storage engine cannot use the column to the right of the range in the index, which means that the index column to the right of the range will be invalidated.

Analyze the above 4 SQL:

When the condition uses name alone, type=ref,key_len=82,ref=const.

When conditional plus age (using constant equivalents), type=ref,key_len=86,ref=const,const.

When the full value matches, type=ref,key_len=128,ref=const,const,const. It shows that the index is all used, as can be seen from key_len and ref.

When using the range (age > 27), type=range,key_len=86,ref=Null, you can see that only part of the index is used, but the gender index is not used.

Conclusion: the index column to the right of the range is invalid.

Fourth, try to use the overlay index

1. Override index definition

If an index contains (or overrides) the values of all the fields that need to be queried, it is called an override index. That is, you only need to scan the index without returning to the table.

The advantage of scanning only the index without returning to the table:

Index entries are usually much smaller than the data row size, and if you only need to read the index, mysql will greatly reduce data access.

Because indexes are stored in the order of column values, there is much less IO for IO-intensive range lookups than for randomly reading each row of data from disk.

Some storage engines, such as myisam, only cache indexes in memory, while data is cached by the operating system, so a system call is needed to access the data.

Innodb's clustered index, override index is particularly useful for innodb tables. (innodb's secondary index holds the primary key value of the row in the leaf node, so if the secondary primary key can override the query, you can avoid a secondary query on the primary key index.)

Override indexes must store the values of index columns, while hash indexes, spatial indexes, and full-text indexes do not store the values of index columns, so mysql can only use B-tree indexes as override indexes.

When you initiate an index override query, you can see the information of using index in the extra column of explain

two。 Compare the benefits of using an overlay index

Try to use overlay indexes (query columns and index columns are consistent as far as possible, generally speaking, indexes are created on An and B columns, and then An and B columns are also used in the query) to reduce the use of select *.

Mysql > explain select * from tb_emp where name='Jack' and age=27 and gender='male'; mysql > explain select name,age,gender from tb_emp where name='Jack' and age=27 and gender='male'

Note: comparing the two sql, the first one uses select * and the second uses an overlay index (the query column corresponds to the condition column). You can see that the Extra has changed from Null to Using index, which improves the retrieval efficiency.

On the MySQL index which rules to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.