mysql优化 - mysql聚合索引的问题

Question

关于mysql聚合索引，这是有段话，一定不是很理解。

If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Answer 1

The question asked by the questioner may be translated incorrectly. It should be called Multiple-Column Indexesmulti-column index or composite indexescompound index. Aggregated indexes should be called clustered indexes, that is another type.

I am used to using multi-column indexes. This sentence means that when building a multi-column index, the order of each column must be considered.
Just take the official document to explain: Assuming that the index is built on (last_name, first_name), then

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';

In all of the above situations, this multi-column index can be used to speed up queries. This is what the second half of the sentence means.
However, the (last_name, first_name) index cannot be used in the following two situations, because the multi-column index must be based on lvalue matching and the order of the fields in which the index is built. This is the meaning of the first half of the sentence

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';