“This is the 16th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

What is index failure

Index failure is the loss of index effect, the underlying principle of index failure is related to B+ tree, this behind alone, here will not expand the explanation, interested friends can also query information.

As we probably know, indexes don’t work all the time, and they may not work in many cases, so what exactly causes indexes to fail? Let’s take a closer look at the following

Index failure classification

1, if there is or in the condition

As most of you probably know, an OR in a SQL statement invalidates an index, even if some of the conditions in the statement are indexed. (This is why you should avoid using or as much as possible.)

Note, however, that this is valid when both the left and right query fields are indexed!

2, like query starts with %

But note that the index is valid when the prefix like does not have % and the suffix %

3, combine index, not use first column index (leftmost principle)

Pay attention to the formula: the lead brother is not allowed to die, the middle brother is not broken!

4. Implicit transformation of data types

Varchar without single quotes may be automatically converted to int, invalidating the index and causing a full table scan.

5, the where clause has mathematical operations on the index column

If math is used in the query column, the DBMS optimizer will first have to deal with math, which also affects query performance. For example: ELECT * FROM TABLE WHERE SUM*2<50

6, use IS NULL or IS NOT NULL operation on index column

Use does not equal (! = OR <>), is null, is not NULL, OR

EXPLAIN SELECT * FROM TABLE WHERE `name` = 'xiaoming' OR `name` = 'xiaohua'';
Copy the code

Use a function on an indexed column in the WHERE clause

Note that functional operations on fields in the WHERE clause cause the engine to abandon the index for a full table scan.

Because SQL resolves local variables only at run time, the optimizer cannot defer the choice of an access plan until run time; It must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is unknown and therefore cannot be used as an input for index selection.

8. When a full table scan is faster than an index

It can be divided into two cases: 1. Queries that use only indexes to return query results, such as the functions count, Max, min in aggregate queries.

2 The number of records in the query result is smaller than a certain proportion of the records in the table. For most databases, this ratio is 10% (Oracle, PostgresQL, etc.), which means the number of results is estimated first, if less than this ratio is indexed, and if more than this ratio is direct table scan.

conclusion

From the index invalidation conditions above, it can be concluded that the following uses cause index invalidation:

  • Computations such as +, -, *, /,! =, <>, is null, is not null, or
  • Functions such as sum(), round(), and so on
  • Manual/automatic type conversion, for example: id = “1010”, which is a number, is written as a string

Thank you

  1. The WHERE clause uses functions on indexed columns
  2. MySQL > alter table select * from ‘not equal’, ‘is NULL’, ‘is not NULL’, ‘OR
  3. Index-based SQL statement optimization
  4. Mysql: index invalid when a column is evaluated by the WHERE clause
  5. Avoid evaluating or using functions on index columns in WHERE conditions, as this will result in the index not being used
  6. Mysql index left-most prefix rule
  7. Do not perform calculations or use functions on index columns in WHERE conditions, as this would result in the index not being used…
  8. Mysql index leftmost matching principle
  9. When is a full table scan faster than an index scan?

The above is today’s share, if there are mistakes, please forgive me, many correction! Thank you ~