Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the principle of MySQL index is. It is very detailed and has certain reference value. Friends who are interested must finish it.

Summary: let's talk about MySQL indexing. What is an index? Baidu encyclopedia is described like this: an index is a decentralized data result created to speed up the retrieval of rows of data in a table. It is created by clockwise tables. It is composed of index pages other than data pages. The rows in each index page contain logical pointers to speed up the retrieval of physical data. In fact, we all know the concept of index, and we also know that index can improve query efficiency. But most of the children's shoes in how to build the index, which fields have the following common misunderstandings: new table does not need to build an index, after adding the index where condition fields are indexed simple SQL does not need an index, joint query needs index joint index order is the order of fields after the where condition also create a new index, such as status, gender and other fields.

Let's talk about MySQL index.

What is an index?

Baidu encyclopedia is described as follows:

An index is a decentralized data result created to speed up the retrieval of rows in a table, clockwise to the table. It consists of index pages other than data pages, each of which contains logical pointers to speed up the retrieval of physical data.

In fact, we all know the concept of index, and we also know that index can improve query efficiency, but most children's shoes have the following common misunderstandings about how to build an index and which fields to build:

You don't need to build an index when you create a new table. Add an index later.

All fields after where condition are indexed.

Simple SQL does not need an index, but federated queries need an index

The order of federated indexes is the order of fields after the where condition.

New indexes are also created on fields with low discrimination, such as status, gender, and so on.

Index differentiation degree

Before we talk about the above problems, let's take a look at another concept, that is, the degree of differentiation.

Discrimination: refers to the non-repetition ratio of a field in the database

The degree of differentiation has a very important reference value when creating a new index. In MySQL, the rules for calculating the degree of differentiation are as follows:

The total number of deduplicated fields is the quotient of the total number of records in the table.

For example:

Select count (distinct (name)) / count (*) from t_base_user

The results are as follows:

Count (distinct (name)) / count (*) 1.0000

The maximum value of discrimination is 1.000 and the minimum is 0.0000. The greater the value of discrimination, that is, the greater the data non-repetition rate, the better the effect of the new index, and the highest discrimination on the primary key and unique key is 1.0000. The distinction value above the fields such as status, gender and so on is the lowest. This depends on the amount of data. If there are only a few pieces of data, the degree of discrimination is quite high. If there is a large amount of data, the degree of discrimination is basically 0.0000. That's why it doesn't work well after adding indexes to these fields.)

It is worth noting that if there are no records in the table, the result of calculating the differentiation is a null value, and in other cases, the discrimination value is distributed between 0.0000 and 1.0000.

How to build an index

(1): degree of differentiation

It is strongly recommended that when building an index, the discrimination of this field must be calculated first for the following reasons:

1. Single column index

You can see the degree of differentiation of this field, and depending on the degree of differentiation, you can also roughly know whether the new index on the field is valid and how effective it is. The greater the degree of differentiation, the more obvious the indexing effect.

two。 Multi-column index (federated index)

In fact, there is also a problem with the order of fields in multi-column indexes. Generally, the ones with high differentiation are put in front, so that the federated index is more effective, for example:

Select * from t_base_user where name= "" and status=1 "

Like the statement above, if you build a federated index, it should be:

Alter table t_base_user add index idx_name_status (name,status)

Instead of:

Alter table t_base_user add index idx_status_name (status,name)

(2) the principle of leftmost prefix matching

MySQL matches to the right until it encounters a range query (>,

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.