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

Blog    

Recommend

Sample code using vue-router in html

Introducing vue and vue-router <script src=&qu...

Example of how to achieve ceiling effect using WeChat applet

Table of contents 1. Implementation 2. Problems 3...

In-depth explanation of Vue multi-select list component

A Multi-Select is a UI element that lists all opt...

Summary of ten Linux command aliases that can improve efficiency

Preface Engineers working in the Linux environmen...

MySQL 5.7 deployment and remote access configuration under Linux

Preface: Recently I am going to team up with my p...

MySQL master-slave configuration study notes

● I was planning to buy some cloud data to provid...

Solve the problem of setting Chinese language pack for Docker container

If you use docker search centos in Docker Use doc...

A brief analysis of Vue's asynchronous update of DOM

Table of contents The principle of Vue asynchrono...

Text pop-up effects implemented with CSS3

Achieve resultsImplementation Code html <div&g...

Comprehensive explanation of CocosCreator hot update

Table of contents Preface What is Hot Change Coco...

Tutorial on installing mysql under centos7

Recently, I plan to deploy a cloud disk on my hom...

Monitor changes in MySQL table content and enable MySQL binlog

Preface binlog is a binary log file, which record...

Detailed steps to install python3.7 on CentOS6.5

1. Download Python 3 wget https://www.python.org/...

IDEA configuration process of Docker

IDEA is the most commonly used development tool f...