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

This article mainly introduces why the mysql index will fail, has a certain reference value, friends in need can refer to. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Mysql index invalidation of several cases: 1, there is or in the condition, even if there is a conditional with index will not be used; 2, for multi-column index, do not use the first part of the index will not be used; 3, like query to start with%, the index is invalid; 4, when the field type is a string, the data in the condition is not referenced in quotation marks.

An index does not always take effect. For example, the following situations will invalidate the index:

1. If there is an or in the condition, it will not be used even if there is a conditional index (which is why or is used as little as possible)

Note: if you want to use or and want the index to take effect, you can only index every column in the or condition.

two。 For multi-column indexes, which are not the first part of the use, the index will not be used

The 3.like query starts with% and the index is invalid; when the like prefix does not have% and the suffix has%, the index is valid.

4. If the column type is a string, be sure to quote the data in quotation marks in the condition, otherwise the index is not used

5. If mysql estimates that using a full table scan is faster than using an index, do not use an index

In addition, check the usage of the index

Show status like 'Handler_read%'

You can pay attention to:

Handler_read_key: the higher the value, the better. A higher value indicates the number of times to query using the index.

Handler_read_rnd_next: the higher the value, the less efficient the query.

1) there are no query conditions, or the query conditions are not indexed

2) Boot columns are not used on query conditions

3) the number of queries is most of the large tables, which should be more than 30%.

4) the index itself is invalid

5) query conditions use functions on index columns, or operate on index columns, including (+, -, *, /,! Etc.) wrong example: select * from test where id-1=9; correct example: select * from test where id=10

6) query the small table

7) prompt not to use index

8) the statistics are not true

9) CBO calculates that it costs too much to walk the index. In fact, it also includes the above situation, which means that the block occupied by the table is smaller than that of the index.

10) implicit conversion results in index invalidation. This point should be paid attention to. It is also a common mistake in development. Because the field tu_mdn of the table is defined as varchar2 (20), but when querying, the field is passed to Oracle as a number type under where condition, which will lead to index invalidation. Wrong example: select * from test where tu_mdn=13333333333; correct example: select * from test where tu_mdn='13333333333'

12) 1, 2, separate >

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.