Correct understanding of column indexes and multi-column indexes in Mysql

The Mysql database provides two types of indexes. If they are not correctly set, the index utilization efficiency will be greatly reduced, but the problem is completely unknown.
Copy codeThe Code is as follows:
Create table test (
Id int not null,
Last_name CHAR (30) not null,
First_name CHAR (30) not null,
Primary key (id ),
INDEX name (last_name, first_name)
);

The above is actually a multi-column index. The code for creating a column index is as follows:
Copy codeThe Code is as follows:
Create table test (
Id int not null,
Last_name CHAR (30) not null,
First_name CHAR (30) not null,
Primary key (id ),
INDEX name (last_name ),
INDEX_2 name (first_name)
);

A multi-column index can be considered as a sort array containing the values created by the concatenate index column values. When the query statement conditions include last_name and first_name, for example:
Copy codeThe Code is as follows:
SELECT * FROM test WHERE last_name = 'kun' AND first_name = 'lil ';

SQL first filters out records whose last_name meets the condition, and then filters records whose first_name meets the condition. If we create two column indexes on last_name and first_name respectively, the mysql processing method will be different. It will select the strictest index for retrieval, it can be understood that the index with the strongest retrieval capability is used for retrieval, and the other one cannot be used, so the effect is not as good as that of Multi-column indexes.

However, the use of multi-column indexes is also required. The following query statements can use the above multi-column indexes:
Copy codeThe Code is as follows:
SELECT * FROM test WHERE last_name = 'widenius ';
SELECT * FROM test WHERE last_name = 'widenius 'AND first_name = 'Michael ';
SELECT * FROM test WHERE last_name = 'widenius 'AND (first_name = 'Michael' OR first_name = 'monty ');
SELECT * FROM test WHERE last_name = 'widenius 'AND first_name> = 'M' AND first_name <'n ';

The following query statements do not use multiple column indexes:
Copy codeThe Code is as follows:
SELECT * FROM test WHERE first_name = 'Michael ';
SELECT * FROM test WHERE last_name = 'widenius 'OR first_name = 'Michael ';

Index creation by multiple columns is more advantageous than index creation by each column, because the more index creation, the more disk space occupied, the slower the data update speed.
In addition, when you create multiple index columns, you need to pay attention to the order. You should place the strict index first, so that the filtering intensity will be greater and the efficiency will be higher.