Several methods to solve the problem of MySQL fuzzy query index failure

When we use the like % wildcard, we often encounter index failure issues.
Here, we discuss several cases where like uses %:

The following examples use the index (VC_STUDENT_NAME)

1. Like 'xx%'

EXPLAIN select * from t_student where VC_STUDENT_NAME like '王%'

We found that the index is valid when the % is not placed at the beginning

2. Like '%xx'

EXPLAIN select * from t_student where VC_STUDENT_NAME like '%王' 

We found that when % is placed at the beginning, the index becomes invalid.

The above two examples show that when using wildcards, placing % at the beginning will cause the index to fail.
But in real problems, it is often necessary to put % at the beginning for fuzzy matching. We can solve the problem of index failure by covering the index, that is, index covering query.

EXPLAIN select * from t_student where VC_STUDENT_NAME like '%王'

By replacing the query column with the column corresponding to the index, the index invalidation problem can be solved.

Replenish:

1. Indexes do not store null values

More precisely, single-column indexes do not store null values, and composite indexes do not store values ​​that are all null. The index cannot store Null, so when the is null condition is used for this column, the index is not valid.

Without Null values, the index cannot be used and the entire table can only be scanned.

Why can't the index column store Null values?

Building a tree of index column values ​​necessarily involves many comparison operations. The special feature of the Null value is that most of the operations involved take the value of null.

In this case, the null value cannot actually participate in the indexing process. That is, null values ​​do not appear in the leaf nodes of the index tree like other values.

2. Not suitable for columns with fewer key values ​​(columns with more duplicate data)

If the index column TYPE has 5 key values ​​and there are 10,000 records, then WHERE TYPE = 1 will access 2,000 data blocks in the table.

In addition to accessing the index blocks, a total of more than 200 data blocks need to be accessed.

If the full table is scanned, assuming that 10 data items are in one data block, then only 1000 data blocks need to be accessed.

If there are fewer, the index will definitely not be used.

3. Leading fuzzy query cannot use index (like '%XX' or like '%XX%')

If there is a column with code values ​​of 'AAA', 'AAB', 'BAA', 'BAB', if the condition where code like '%AB' is true,

It is fuzzy, so the order of the index cannot be used. You must search one by one to see if the conditions are met. This will result in a full index scan or a full table scan

Description. If the condition is where code like 'A % ', you can search for the position of the code starting with A in the code, and when you encounter the code starting with B

When there is no data, you can stop searching, because the subsequent data will definitely not meet the requirements. This way you can take advantage of the index.

4. Several situations where index failure occurs

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

If you want to use or and make the index effective, you can only add an index to each column in the or condition.

2. For a multi-column index, the index will not be used unless it is the first part to be used

3.Like query starts with %

4. If the column type is a string, the data must be quoted in the condition, otherwise the index will not be used.

5. If MySQL estimates that a full table scan is faster than an index, it does not use the index.

5.MySQL mainly provides two types of indexes: B-Tree index and Hash index

B-tree indexes have the ability to perform range and prefix searches. For a B-tree with N nodes, the complexity of retrieving a record is O(LogN). Equivalent to binary search.

Hash indexes can only be used for equality searches, but no matter how large the hash table is, the search complexity is O(1).

Obviously, if the values ​​are very different and the main search is for equal values ​​(=, <, >, in), the hash index is a more efficient choice, with a search complexity of O(1).

If the difference between values ​​is relatively poor and range searches are the main focus, B-tree is a better choice because it supports range searches.

This concludes this article on several methods to solve the problem of MySQL fuzzy query index failure. For more relevant content on MySQL fuzzy query index failure, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!