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

How to use the index in MySQL, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

I. Preface

When doing SQL optimization in MySQL, there is often some confusion about whether MySQL can take advantage of indexes in some cases.

For example:

1. MySQL stops matching when it encounters a range query condition, so what are the range conditions?

2. How does MySQL make use of the index when LIKE performs fuzzy matching?

3. Under what circumstances can MySQL use the index for sorting?

Today, I will use a model to answer all these questions so that you are no longer afraid of using MySQL indexes.

II. Knowledge supplement

Key_len

In the EXPLAIN execution plan, a column key_len is used to indicate how many bytes of the selected index length are in this query, which is usually used to determine how many columns of the federated index have been selected.

Here, the key_len size is calculated as follows:

Generally, key_len is equal to the byte length of the index column type, for example, the int type is 4 bytes,bigint and 8 bytes

If it is a string type, you also need to consider the character set factors, for example: CHAR (30) UTF8, then key_len is at least 90 bytes

If NULL is allowed when the column type is defined, its key_len needs to be added by 1 bytes

If the column type is a variable length type, such as VARCHAR (TEXT\ BLOB does not allow the creation of an entire column index, and partial index creation is also regarded as a dynamic column type), its key_len needs to be added by 2 bytes.

3. What conditions can be used for indexing

First of all, thank you very much for Dengbo, which gave me a good inspiration. I made this picture through his article and then combined with my own understanding.

At first glance, is it very dizzy, not in a hurry? let's take our time.

The picture is divided into three parts:

1. Index Key: MySQL is used to determine the range of scanned data, which is actually the index part of MySQL that can be used, which is reflected in Key Length.

2. Index Filter:MySQL is used to determine which data can be filtered by the index. After ICP is enabled, the part of the index can be used.

3. Table Filter:MySQL cannot be filtered by index. After the row data is retrieved from the table, the data is filtered at the server layer.

We unfold it in detail.

Index Key

Index Key is used to determine the scanning range of MySQL, which is divided into upper boundary and lower boundary.

MySQL uses =, > =, > to determine the lower boundary (first key), and uses the leftmost principle to first determine whether * index key values exist in the where condition. If so, determine the comparison symbol. If it is one of (=, > =), add the definition of the lower boundary, and then continue to determine the next index key. If it exists and is (>), the key value is added to the definition of the lower boundary and stops matching the next index key. If it does not exist, stop the lower boundary matching directly.

Exp: idx_c1_c2_c3 (C1 ~ c2 ~ c3) where C1 > = 1 and c2 > 2 and c3 ~ 1-- > first key (C1 ~ c2)-- > C1 is'> =', add the lower boundary, continue to match the next-- > c2 is'>', add the lower boundary and stop matching.

The upper boundary (last key) is similar to the lower boundary (first key). First determine whether it is (=, c3 is'= 'and also contains' =', then the index key can be used and can continue to match the subsequent index key values; if there is no'=', that is,'>',

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.