Detailed explanation of MySQL combined index method

For any DBMS, indexes are the most important factor for optimization. For a small amount of data, not having a suitable index does not have a big impact, but as the amount of data increases, performance will drop sharply.

If you index multiple columns (composite index), the order of the columns is very important. MySQL can only effectively search for a prefix of the leftmost column of the index. For example:

Assume that there is a composite index (c1, c2), the query select * from t1 where c1=1 and c2=2 can use this index. The query select * from t1 where c1=1 can also use this index. However, the query statement select * from t1 where c2=2 cannot use this index because there is no leading column for the composite index. That is, in order to use the c2 column for search, c1 must be equal to a certain value.

For example:
Create two tables book (book table) and bookclass (book classification table)

select b.ISBN FROM book b where b.CATEGORY_ID = 1; 

Execution time: 0.053s

Use explain to analyze the SQL:

type = ALL Extra=Using where, the full table query does not use the index.

EXPLAIN shows how MySQL uses indexes to process select statements and join tables. It can help choose better indexes and write more optimized query statements.

ALL For each combination of rows from the previous tables, a full table scan is performed. This is usually bad if the table is the first table not marked const, and is usually very bad in other cases. Often you can add more indexes instead of using ALL so that rows can be retrieved based on constant values ​​or column values ​​in the previous table.

Create a composite index:
create index index_isbn on book (CATEGORY_ID,ISBN);

Execute the SQL again and find that the time is shortened to 0.009s

Use explain to analyze the SQL:

type = ref, Extra = Using index Index query is used.

ref For each combination of rows from the previous tables, all rows with matching index values ​​will be read from this table. If the join uses only a leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot select a single row based on the key), then ref is used. This join type is good if the keys used match only a small number of rows.

This is the end of this article about the detailed explanation of the combined index method of MySQL. For more relevant MySQL combined index content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!