How do I learn the principles of MySQL indexing? My own summary of the index experience

What is an index? Baidu Encyclopedia is described as follows: An index is a decentralized data result created to speed up the retrieval of data rows in a table, which is created by an index page other than the data page, and the rows in each index page contain logical pointers to accelerate the retrieval of physical data. How the MySQL indexing principle learns, this article will explain in detail.

Summary: just get up and talk about MySQL index. What is an index? Baidu Encyclopedia is described as follows: The index is to accelerate the retrieval of data rows in the table created by a decentralized data results, the hour hand to the table, it is composed of index pages outside the data page, each index page contains a logical pointer, in order to speed up the retrieval of physical data in fact, the concept of the index is very clear, Also know that indexes can improve query efficiency, but most of the children's shoes in how to index, built in which fields have the following common misunderstanding: the new table does not need to build an index, followed by adding the index after the field is indexed simple SQL does not need an index, a federated query requires an index The order of the Federated indexes is where the order of the fields after the field is also newly indexed on fields with small sensitivity, such as status, gender, and so on.

Just get up and talk. mysql index.

What is an index?

Baidu Encyclopedia is described in this way:

An index is a decentralized data result created to speed up the retrieval of data rows in a table, which is created by an index page other than the data page, and the rows in each index page contain logical pointers to speed up the retrieval of physical data

In fact, the concept of the index is very clear, but also know that the index can improve query efficiency, but most of the children's shoes in how to index, built in which fields have the following common misunderstanding:

New table does not need to be indexed, subsequent indexes are added

Fields after the Where condition are indexed

Simple SQL does not need indexes, and federated queries require indexes

The order of the Federated indexes is the order of the fields after the Where condition

New indexes are also created for fields that are small in sensitivity, such as status, gender, and so on.

Index sensitivity

Before we say the above question, let's look at another concept, that is, the degree of differentiation.

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

The degree of differentiation is very important in the new index, and in MySQL, the rules for calculating the degree of differentiation are as follows:

The total number of fields to be weighed and the total number of records for the whole table.

For example:

Select COUNT (Distinct (name))/count (*) from T_base_user;

The results are as follows:

COUNT (Distinct (name))/count (*)
1.0000

Where the maximum degree of differentiation is 1.000, the minimum is 0.0000, the greater the value of the sensitivity, that is, the greater the rate of data repetition, the better the new index effect, the primary key and the unique key above the highest degree of sensitivity, is 1.0000. The sensitivity value above the status, gender, and other fields is minimal. (This depends on the amount of data, if only a few data, then the degree of differentiation is very high, if the amount of data, the degree of discrimination is basically 0.0000.) That is why the effect is poor when you add an index to these fields. )

It is important to note that if there are no records in the table, the result of calculating the sensitivity is null, and in other cases, the sensitivity values are distributed between 0.0000-1.0000.

How to build an index

(a): Degree of Distinction
It is highly recommended that when you build an index, you must first calculate the sensitivity of the field for the following reasons:

1. Single-Column indexing
You can see the sensitivity of the field, depending on the size of the sensitivity, and you can probably know whether the new index on that field is valid and how it works. The greater the degree of differentiation, the more obvious the index effect.

2. Multi-column index (Federated Index)
In fact, there is a sequential problem of a field in a multi-column index, which is generally a high degree of differentiation, so that the federated index is more efficient, for example:

SELECT * from T_base_user where name= "" and Status=1;

Like the above statement, 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);

(ii) leftmost prefix matching principle
MySQL will always match right until it encounters a range query (>, <, between, like) to stop the match, such as

SELECT * from T_base_user where type= "ten" and created_at< "2017-11-03" and Status=1 (the statement as a demonstration only)

In the above statement, the status will not go index, because when encountering <, MySQL has stopped matching, the index is: (TYPE,CREATED_AT), the order is adjustable, and can not go to the status index, at this time need to modify the statement as:

SELECT * from T_base_user where type=10 and Status=1 and created_at< "2017-11-03"

You can go to the status index.

(c) Function operation
Do not perform function operations on the index column, otherwise the index will be invalidated. Because the B + tree has all the values of the fields in the data table, when retrieving it, it is necessary to apply all the elements to the function to compare, obviously the cost is too high.

(iv) Extension of priority
The extension takes precedence, do not create a new index and try to modify it in an existing index. As follows:

SELECT * from T_base_user where name= "Andyqian" and email= "Andytohome"

The Idx_name index already exists in the table T_base_user table, and if you need to add a idx_name_email index, you should modify the Idx_name index instead of creating one.

Misunderstanding correction

It says, how to create a new index, now we can answer, in the first step there is a misunderstanding.

Misunderstanding one: You do not need to index when creating a new table, followed by indexes

A: A good data table design, in the beginning to consider the creation of the index, rather than wait until the subsequent problems, affecting the business use, the new index to rescue, and the subsequent creation of the index is relatively expensive. (This is the opportunity to leave a root for the production accident)

Misunderstanding two: Fields after the Where condition are indexed

A: This misunderstanding is more common, but the field after the where condition does not need to be indexed all, too many indexes, will also cause the index file to soar, also can not achieve the desired effect. For more information, refer to the section on the new index above.

Misunderstanding three: Simple SQL does not need to be indexed, federated query Mining to build an index

A: This misunderstanding must be well said, now the Internet company, especially the B/s structure, business logic is stripped in the code logic layer, to the final SQL level, is actually some simple SQL, only a little connection query, more or single table operation, (c/S architecture There are many in the SQL level of write logic), You say these statements are simple.

Myth Four: The Order of the Federated Index is the order of the field after the Where condition

A: As we have just said, the order of the federated indexes is differentiated according to the leftmost prefix principle, as well as the degree of differentiation, regardless of the order of the fields after the where condition.

Myth Five: Create a new index for a field with less sensitivity

A: The new index on the less sensitive field, basically invalid, also will add a lot of index file, you say is not worth the candle.

Does index weight matter?

The concept of MySQL indexing is described above, with some tips for creating new indexes. So the theory of things, for normal use or less use of children's shoes, at this time on the importance of the index may not be so intuitive, then, I would like to say I eat on the index of the loss, stepped on the pit! Also the non-built index FAQ!

0. Causes slow Query
This problem is not indexed by the regulars Oh, (there are also many details, such as: implicit type conversion, etc.)

1. Cause the service to time out

Scene:
When on-line, as a service provider, to provide services to the business party use. At first thought to provide a simple service, has been tested to complete, the heart is still in the theft of joy, today finally can go home early!

Describe:
Actual one on-line, in the production environment cause the business party request call, and each request is timed out, the data has landed, at this time can only review code, and finally found that there is a slow query in production, the cost of more than 10 seconds alive, the statement how simple it is, you absolutely unexpected, is actually a single-table where condition query statement. You say this cause service is not available, you say injustice not injustice, gas not angry! (That's why I said that a good data sheet design, from the outset, will consider the new index).

2. Database Server CPU 100%

In the query frequency is relatively high on the SQL, if due to the non-indexed, resulting in slow query, it will cause the database server CPU 100%, the impact of the entire system oh.

Summary
Above said several kinds, because does not establish the index causes the problem, the light causes the slow query, affects the system efficiency, the heavy, causes the CPU 100%, affects the entire system the use, sees here, you say index weight unimportant?

At last

What is the index, simply stated above? What is the use of, as well as some techniques for indexing, also emphasizes the importance of indexing. So the index is so important, how to avoid it when coding in peacetime? Here are my personal suggestions:

1. When building a table, you should consider adding indexes, such as: Foreign key fields, and so on.

2. After you have finished writing SQL, be sure to review the execution plan. Try to avoid a full table scan.

3. If you are adding an index to an existing table, be sure to calculate the sensitivity of the field first.

4. Combine the indexes to put the distinction in front of the big.

5. Follow the MySQL left column prefix precedence principle \

[2] H. Berenson, P. Bernstein, J. Gray, J.melton, E. o ' Neil,and P. O ' Neil. A Critique of ANSI SQL isolation levels. Inproceedings of the Sigmod International Conference on Management of Data, Pages1–10, May 1995.

[3] Michael J. Cahill, Uwe Röhm, and Alan d.fekete. Serializable isolation for snapshot databases. In Sigmod ' 08:proceedings of the $ ACM SIGMOD International Conference on Management of data, pages 729–738, New York, NY, USA. Acm. [4] Michael James Cahill. Serializable isolation for Snapshot Databases. Sydney Digital theses. University of Sydney, School of information technologies[5] A. Fekete, D. Liarokapis, E. O ' Neil, P.O ' Neil, andd. Shasha. Making Snapshot Isolation serializable. Www.codexueyuan.com in ACM Transactions on Database Systems, Volume (2), pages 492–528, June 2005.

Related articles:

MySQL index--(MySQL Learning II) _mysql

mysql-Index Learning (i) _mysql

Related videos:

Index simple introduction-six days to take you to play MySQL video tutorial