What are the rules for the use of composite indexes in Mysql 03/29 Update SLTechnology News&Howtos

What are the rules for the use of composite indexes in Mysql

2025-03-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the rules for the use of the composite index in Mysql, which can be used for reference by interested friends. I hope you will gain a lot after reading this article.

Joint index verification: working from left to right

Index: (C1, c2, c2, c3, c4): on the basis of finding C1, you can find c2, and on the basis of c3, you can find c4.

A:select * from t where c1, and c2, and c3, and c4, x

B:select * from t where c1roomx and c2roomx and c4 > x and c3roomx; c1+c2+c3+c4 is used

C:select * from t where c1indexes x and c2posts x and c4posts x order by c3; C1+C2 uses index lookup, C3 only plays the role of sorting, C3 does not need (order by c3: work, sorting does not need to be done), the index of C4 is not used, 4 boards are broken in the middle, and then they are not used.

D:select * from t where c1, and c4, group by c3, c2

E:select * from t where c1, and c5, order by c2, c3

F:select * from t where c1, and c2, and c5? Order by c2,c3

Create table t (C1 char (10), c2 char (10), c3 char (10), C4 char (10), c5 char (10))

Insert into t values ('b1','b1','b2','b3','b4','b5'), ('b1','b2','b3','b5')

Create index idx_t_c1234 on t (C1, c2, c3, c4)

Create index idx_t_c1 on t (C1)

Create index idx_t_c2 on t (c2)

Create index idx_t_c3 on t (c3)

Create index idx_t_c4 on t (c4)

Alter table t drop index idx_t_c1234

Explain select * from t where c1 / A1 'and c2 / b2' and c3 / a3 'and c4 / 3 / 2 / 3 / 3 / 3 / 3 / 3 / 2 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / and / c4 /

+- -+

+- -+

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 44 | const,const,const,const | 1 | Using where; Using index |

+- -+

Key_len: 44 / / CHAR (10) * 4 + 4 * NULL: it means that all three indexes are used, and all of them are equivalent query indexes: C1 Magi c2 Magi c3 Magi c4

After deleting the composite index, it is found that only C1 index is used, but c2menthc3memc4 index is not used at all.

+- -+

+- -+

| | 1 | SIMPLE | t | ref | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c1 | 11 | const | 1 | Using index condition; Using where |

+- -+

After deleting the composite index: it is found that only one index c4 is used, but no C1 index is used. This is because the optimizer found that none of the c4rooma1' entries were found, and using this index to query is the fastest.

Explain select * from t where c1 / A1 'and c2 / b2' and c3 / a3 'and c4 / A1'

+- -+

+- -+

| | 1 | SIMPLE | t | ref | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c4 | 11 | const | 1 | Using index condition; Using where |

+- -+

After deleting the composite index: it is found that only one index C1 is used, and no other indexes are used. This is because the optimizer did not find which condition has the least value record (c2memc3recoverc4indexes' equivalent join also matches multiple entries), so choose the leftmost column index.

Explain select * from t where c1 / A1 'and c2 / b2' and c3 / A3 'and c4 / A4'

+- -+

+- -+

| | 1 | SIMPLE | t | ref | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c1 | 11 | const | 18 | Using index condition; Using where |

+- -+

Explain select * from t where c4 / A1 'and c2 / b2' and c3 / A3 'and c1 / A1'

+- -+

+- -+

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 44 | const,const,const,const | 1 | Using where; Using index |

+- -+

The order after the where condition is independent

Explain select * from t where c1 / A1 'and c2 / b2' and c4 >'a 'and c3 / A3'

+-+-

+-+-

+-+-

Key_len: 44 / / CHAR (10) * 4 + 4 * NULL: all 4 indexes are used, and all of them are equivalent query indexes: C1, Magi, c2, c3, and c4, all through

As a new feature of Using index condition:5.6, Where conditional filtering can be done at the innodb engine layer, so that innodb sends much less to the server layer. If this feature is not enabled, the data will be sent to the server layer for where filtering after the data is accessed through the index.

Explain select * from t where C1 / A1 'and c2 / b2' and c3 / and c4 >'a'

+-+-

+-+-

+-+-

Range: on behalf of c4, the index is used and range lookup is used.

Explain select * from t where c1 / a1 'and c2 / b2' and c4 / b4 'order by c3

+- -+

+- -+

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 22 | const,const | 1 | Using index condition; Using where |

+- -+

Key_len: 22 / / CHAR (10) * 2 + 2 * NULL: it means that all the indexes of C1 Magi c2 are used, and all of them are equivalent query indexes: C1 Magi c2.

Using where: indicates that c4 performs where filtering at the server layer

C3: index sorting is used

The column name or const that ref needs to compare with the index (constant, where id = 1 is const)

After deleting the composite index: only C1 index is used, that is, only one index is used, no other indexes are used, and sorting is not used.

Explain select * from t where c1 / a1 'and c2 / b2' and c4 / b4 'order by c3

+- -- +

+- -- +

| | 1 | SIMPLE | t | ref | idx_t_c1,idx_t_c2,idx_t_c4 | idx_t_c1 | 11 | const | 2 | Using index condition; Using where; Using filesort |

+- -- +

Explain select * from t where c1, and c4, group by c3, c2

+- -- +

+- -- +

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 11 | const | 1 | Using index condition; Using where; Using temporary; Using filesort |

Key_len: 11 / / CHAR (10) * 1 + 1 * NULL: C1 index is all used, and all are equivalent query indexes: C1

Using temporary:DISTINCT, or use different ORDER BY and GROUP BY columns, and do not use the index, will use temporary table to sort, the temporary table is memory temporary table, not the worst, the biggest fear is Using disk temporary

Using filesort: when we try to sort a field without an index, it is filesoft

Due to the discontiguous index with (c1magentin c2menc3pyrc4) index sorting cannot be used in c3memery c2.

After deleting the composite index: only C1 index is used, that is, only one index is used, other indexes are not used, and group by is not used.

Explain select * from t where c1, and c4, group by c3, c2

+- -- +

+- -- +

| | 1 | SIMPLE | t | ref | idx_t_c1,idx_t_c4 | idx_t_c4 | 11 | const | 1 | Using index condition; Using where; Using temporary; Using filesort |

+- -- +

Explain select * from t where c1, and c4, group by c2, c3

+-+-

+-+-

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 11 | const | 1 | Using index condition; Using where |

+-+-

C2Magee c3 uses (C1 Magi c2 Magi c3 Magi c4) index sort, connected to C1

Explain select * from t where c1 recording a3 'and c5 recording a5' order by c2 sparing c3

+-+-

+-+-

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 11 | const | 1 | Using index condition; Using where |

+-+-

Key_len: 11 / / CHAR (10) * 1 + 1 * NULL: C1 index is all used, and all are equivalent query indexes: C1

Explain select * from t where c1, and c2, and c5, order by c2, c3

+- -+

+- -+

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 22 | const,const | 1 | Using index condition; Using where |

+- -+

Key_len: 11 / / CHAR (10) * 2 + 2 * NULL: C1 index is all used, and all of them are equivalent query indexes: C1 and c2

The principle of avoiding sorting through indexes in group by:

Explain select * from t where c1, and c4, group by c3, c2

Explain select * from t where c1 / a1 'and c2 / b2' and c4 / b4 'order by c3

The where condition is only concerned about the data. In the process of filtering, if c3meme c2 has an index, it can be used directly.

In the process of searching, you can get the data of c3 together. At this time, you can sum,avg and so on. There is no need to sort.

After deleting the composite index: only C1 index is used, that is, only one index is used, other indexes are not used, and order by is not used.

Explain select * from t where c1, and c2, and c5, order by c2, c3

+- -- +

+- -- +

| | 1 | SIMPLE | t | ref | idx_t_c1,idx_t_c2 | idx_t_c1 | 11 | const | 2 | Using index condition; Using where; Using filesort |

+- -- +

Explain select * from t where c3%'

+-- +

+-- +

+-- +

Full table scan, no composite index idx_t_c1234 is used, unless the Where condition is followed by C1 and c2

Explain select * from t where C1%%'

+-+-

+-+-

| | 1 | SIMPLE | t | ref | idx_t_c1234 | idx_t_c1234 | 11 | const | 1 | Using index condition |

+-+-

The composite index idx_t_c1234 is used

Thank you for reading this article carefully. I hope the article "what are the rules for the use of composite indexes in Mysql" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.