A case of using union index

Time:2021-2-22

In practice, full table scanning is always avoided, so slow table scanning is avoidedsqlIndex on some fields in.

The significance of index building is that the original operation of scanning and operation in the table is executed in the index to reduce the number of scanning rows in the table, so the query speed is accelerated.

But simply building an index on each field does not necessarily achieve the desired effect. See the followingsql

select avg( score ) from table where group = A;

abovesqlThe purpose is to queryAThe average score of each group. So ingroupscoreTwo indexes are built on the database.

Use it firstgroupIndex to the table to find allgroup=ATo create a temporary table, put the query results into the temporary table, and then calculate in the temporary table. The result is that a large number of rows are still scanned, and the overhead of creating a temporary table is relatively large, in factscoreThe index is meaningless.

So for this articlesqlThe optimization of is failed, and the correct index is builtgroupandscoreThis means that the query does not need to return to the table at all, but directly returns the results in the index.

However, we still need to pay attention to the order of the joint index,groupbefore,scoreAfter that.

This work adoptsCC agreementReprint must indicate the author and the link of this article

focus