Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the phenomena of mysql index failure?". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Index failure: 1, the like query starts with "%"; 2, the index is not used at the same time before and after the or statement; 3, the first column index is not used in the combined index; 4, the "IS NULL" or "IS NOT NULL" operation is used on the index column; 5, use "not", "! =" on the index field, and so on.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Index is a special database structure, which is composed of one or more columns in a data table, which can be used to quickly query records with a specific value in the data table. This section explains in detail the meaning, functions, advantages and disadvantages of the index.

Through the index, when querying data, you do not have to read all the information of the record, but only query the index column. Otherwise, the database system will read all the information from each record to match.

The index can be compared to the sequence table of Xinhua Dictionary. For example, to look up the word "library", if you don't use phonetic order, you need to look it up page by page from 400 pages of the dictionary. However, if you extract pinyin to form a sequence table, you only need to find it directly from the sequence table of more than 10 pages. This can save a lot of time.

Therefore, the use of index can greatly improve the query speed of the database, but also effectively improve the performance of the database system.

There are several situations in which index queries fail:

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

2. Indexes are not used simultaneously before and after the or statement.

When only one query field around or is an index, the index is invalid. It will take effect only if all query fields around or are indexes.

3. Composite index, instead of using the first column index, the index is invalid.

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

The data type is implicitly converted. If varchar does not add single quotation marks, it may be automatically converted to int, invalidating the index and resulting in a full table scan.

5. Use IS NULL or IS NOT NULL operations on index columns.

The index does not index null values, so this operation can not use the index, can be dealt with in other ways, such as: numeric type, determine greater than 0, string type set a default value, determine whether it is equal to the default value. (this is wrong! )

Explain the above errors:

Here I will recreate an emp table

Create a new index

View Index

Execute SQL statement

From this it can be found that the index is used.

Summary: if you use IS NULL or IS NOT NULL operations on index columns, the index is not necessarily invalid!

6. Use notpr è re on the index field.

Does not mean that the operator will never use an index, so processing it will only result in a full table scan. Optimization method: key0 is changed to key > 0 or key

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.