Shulou(Shulou.com)06/02 Report--

This article mainly introduces the conditions for the entry into force of the joint index and the conditions for the failure of the index in mysql. What is introduced in this article is very detailed and has a certain reference value. Interested friends must read it!

1. Conditions for failure of federated index

A federated index is also called a composite index. An index on two or more columns is called a composite index.

For composite indexes: Mysql uses fields in the index from left to right, and a query can use only a portion of the index, but only the leftmost part. For example, the index is key index (a _ r _ b _ r _ c). It can be searched with three combinations of a | aPermian b | aPermian bPermec, but not bPermie c. The index is very effective when the leftmost field is a constant reference.

With additional columns in the index, you can narrow your search, but using an index with two columns is different from using two separate indexes. The structure of the composite index is similar to that of the phone book, where the first name consists of a last name and a first name. The phone book first sorts pairs by last name, and then sorts people with the same surname by first name. The phone book will be very useful if you know the last name; if you know the first name and first name, the phone book will be more useful, but if you only know your first name, the phone book will be useless.

So when creating a composite index, you should carefully consider the order of the columns. A composite index is useful when performing a search on all columns in an index or only on the first few columns; a composite index is not useful when searching only any subsequent column.

Such as: to establish a compound index of name, age and gender.

Create table myTest (an int, b int, c int, KEY a)

(1) select * from myTest where axiom 3 and baggage 5 and cantilever 4;-abc order

All three indexes of abc are used in where conditions, and all of them play a role.

(2) select * from myTest where cantilever 4 and baccalaureate 6 and astat3

The order of conditions in where is automatically optimized by mysql before the query, and the effect is the same as the previous sentence.

(3) select * from myTest where axiom 3 and cantilever 7

A uses the index, but b is useless, so c does not use the index effect

(4) select * from myTest where axiom 3 and b > 7 and canti3;-b range values, breakpoints, blocking the index of c

An is used, b is also used, c is not used, where b is a range value, which is also a breakpoint, but the index itself is used.

(5) select * from myTest where bread3 and cantilever 4;-federated indexes must be used sequentially and all should be used

Because the an index is not used, bc does not use the index effect here

(6) select * from myTest where a > 4 and Beng7 and Cruise 9

A used b did not use, c did not use

(7) select * from myTest where axi3 order by b

A uses the index, and b also uses the effect of the index in the sorting of results. The b of any paragraph below an is sorted.

(8) select * from myTest where axi3 order by c

A uses the index, but this place c does not have a sorting effect, because there are intermediate breakpoints, and you can see the filesort using explain

(9) select * from mytable where bust 3 order by a

B does not use the index, and a does not have the effect of index in sorting.

two。 Conditions for index failure

Not doing anything on the index column (calculation, function, (automatic or manual) type conversion) will cause the index to fail and turn to a full table scan

The storage engine cannot use the column to the right of the index range condition

Try to use overlay indexes (queries that only access the index (index columns are consistent with query columns) and reduce select *

Mysql in use is not equal to (! Being unable to use an index when = or) will cause a full table scan

Is null,is not null also can't use the index-there is a doubt here, it does work after testing, ref and const levels, not all.

Like begins with a wildcard character ('% abc …' Mysql index invalidation becomes a full table scan operation Question: how to solve the problem that the index is not used when the like'% string%'?

Index invalidation of string without single quotation marks

SELECT * from staffs where name='2000';-because mysql implicitly converts it to SELECT * from staffs where name=2000; at the bottom-no index is used

General suggestion

For single-key indexes, try to choose an index that is more filtered for the current query.

When selecting a combined index, the most filtered field in the current Query is in the order of the index fields, and the higher the position, the better.

When choosing a combined index, try to select an index that can contain more fields in the where clause of the current query

As far as possible, the purpose of selecting the appropriate index can be achieved by analyzing the statistical information and adjusting the writing of query.

These are all the contents of the article "what are the conditions for the entry into force of the federated index and what are the conditions for the failure of the index in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.