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

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 '王%'

insert image description here

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 '%王' 

insert image description here

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 '%王'


insert image description here

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!

You may also be interested in:
  • Detailed explanation of MySQL database indexes and failure scenarios
  • Common scenarios and avoidance methods for index failure in MySQL
  • MySQL index failure principle
  • MySQL joint index effective conditions and index invalid conditions
  • Share 15 scenarios where MySQL indexes fail

<<:  Steps of an excellent registration process

>>:  Example of using UserMap in IMG

Recommend

Detailed process of upgrading gcc (version 10.2.0) under CentOS7 environment

Table of contents Short Introduction 1. Check the...

What are the differences between var let const in JavaScript

Table of contents 1. Repeated declaration 1.1 var...

Nexus uses nginx proxy to support HTTPS protocol

background All company websites need to support t...

Let's talk about what JavaScript's URL object is

Table of contents Overview Hash Properties Host p...

Vue project code splitting solution

Table of contents background Purpose Before split...

9 ways to show and hide CSS elements

In web page production, displaying and hiding ele...

How to prevent users from copying web page content using pure CSS

Preface When I was typing my own personal blog, I...

Vue uses WebSocket to simulate the chat function

The effect shows that two browsers simulate each ...

Use of hasOwnProperty method of js attribute object

Object's hasOwnProperty() method returns a Bo...

Detailed tutorial on installing Docker on Windows

Since my local MySQL version is relatively low, I...

Example of using nested html pages (frameset usage)

Copy code The code is as follows: <!DOCTYPE ht...

Detailed explanation of the process of installing MySQL on Ubuntu 18.04.4

Let's take a look at the process of installin...