[MySQL performance Optimization series] Aggregate index

1. How to use the index of ordinary youth

Suppose we have a user table Tb_user, which reads:

Age

name		Sex
Jack	22	Man
Rose	21st	Woman
Tom	20	Man
...	...	...

Execute SQL statement:

SELECT  from WHERE = ;

By default, MySQL needs to traverse the entire table to find records that match the criteria. If an index is established on the age field, then MySQL can quickly find all the qualifying records (the index itself is implemented by the B + tree and is fast to look up.) For the sake of simplicity, imagine the difference between a binary lookup and a traversal lookup. ）

2. How to play the young arts 2.1 accelerating queries with redundant federated indexes

Then the above example, we assume that the Tb_user table has 1 million rows, usually, "where Age = 20" Such a statement, will return tens of thousands of rows of data, the actual test found that the speed is not fast enough.

The reason is that when MySQL queries a qualifying record based on the index, it also needs to find these records in the Tablespace one by one (in fact, the physical line number of the age field and associated record is recorded in the index), which means that MySQL must read the table space up to tens of thousands of times to return the final result.

Smart you may have thought that if the Age field index has the value of the Name field, MySQL will no longer have to bother to access the table space.

Final Solution: Create a federated index that allows MySQL to remove the value of the Name field directly from the index

KEY ' age_with_name ' (' Age ', ' name ')

Note that the order here must be the First age name and vice versa (unless you are checking age by name).

2.2 Accelerating sorting with redundant federated indexes

is still the previous table, assuming that you want to do such a query:

SELECT *  from ORDER  by age;

Because we have an index on age, the sort is fast (the essence of the index is to sort the physical line numbers of the table records by a specific rule)

In the actual project, SQL may be more complex than this, such as:

SELECT *  from WHERE Sex=' man 'ORDER by age;

At this point, the index on the age field will not be useful. Because the age index is for the entire table, the filtered table and the age index are not on the right.

Solution: Still a federated index!

KEY ' age_with_name ' (' Sex ', ' age ')

This federated index, which records both sex and age, and the rules for sorting, is to sort by sex and sex in the same time. So, by "WHERE sex= '", MySQL first filters the index, and then the rest of the index is exactly the same as the age sort. As a result, the overall SQL is still sort fast.

[MySQL performance Optimization series] Aggregate index