How to learn MySQL index principles? My own summary of indexing experience-Mysql Tutorial-php.cn

What is index? Baidu Encyclopedia describes it this way: An index is a scattered data result created to speed up the retrieval of data rows in a table. It is built for the table. It is composed of index pages other than the data page. Each Rows in the index page contain logical pointers to speed up retrieval of physical data. How to learn MySQL index principles, this article will explain in detail.

Summary: Let’s talk about MySQL indexes. What is an index? Baidu Encyclopedia describes it this way: An index is a scattered data result created to speed up the retrieval of data rows in a table. It is built for the table. It is composed of index pages other than the data page. Each The rows in the index page contain logical pointers to speed up the retrieval of physical data. In fact, everyone is very clear about the concept of indexing, and we also know that indexing can improve query efficiency. However, most children's shoes have the following common questions about how to build an index and which fields to build it on. Misunderstanding: There is no need to create an index when creating a new table, and indexes will be added later. All fields after the where condition are indexed. Simple SQL does not require an index, only joint queries require indexes. The order of the joint index is the order of the fields after the where condition, which is less differentiated. New indexes are also created on fields, such as status, gender and other fields.

Let’s talk about MySQL indexes together.

What is an index?

Baidu Encyclopedia describes it this way:

The index is a scattered data result created to speed up the retrieval of data rows in the table. It is created for the table. Yes, 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, everyone is very clear about the concept of index, and We know that indexes can improve query efficiency, but most children's shoes have the following common misunderstandings about how to build indexes and which fields to build on:

There is no need to create an index when creating a new table, and the index will be added later

The fields after the where condition are all indexed

Simple SQL does not require an index, only the joint query requires an index

The order of the joint index is the order of the fields after the where condition

Also create new indexes for fields with small distinction, such as status, gender and other fields.

Index Distinction

Before talking about the above issues, let’s take a look at another concept, which is discrimination.

Distinction: refers to the non-duplicate ratio of fields in the database

Distinction has a very important reference value when creating a new index. In MySQL, the difference The calculation rules are as follows:

The quotient of the total number of fields after deduplication and the total number of records in the entire table.

For example:

select count(distinct(name))/count(*) from t_base_user;

The results are as follows:

##1.0000

The maximum value of the distinction is 1.000, and the minimum value is 0.0000. The greater the value of the distinction, that is, the greater the data non-duplication rate, the better the new index effect. The distinction on the primary key and the unique key is the highest. , is 1.0000. The distinction value on fields such as status and gender is the smallest. (This depends on the amount of data. If there are only a few pieces of data, the discrimination is quite high. If there is a lot of data, the discrimination is basically 0.0000. That is to say, after adding indexes to these fields, the effect will not be good. Reason.)

It is worth noting that: If there are no records in the table, the result of calculating the discrimination is a null value. In other cases, the discrimination values are distributed between 0.0000-1.0000.

How to build an index

(1): Distinction
I strongly recommend that when building an index, you must first calculate the distinction of the field for the following reasons:

1. Single-column index
You can check the distinction of the field. Based on the degree of distinction, you can also roughly know whether the new index on the field is effective and how effective it is. The greater the discrimination, the more obvious the indexing effect.

2. Multi-column index (joint index)
In fact, there is also a problem of the order of fields in the multi-column index. Generally, the ones with higher distinction are placed first, so that the joint index is more effective. , for example:

select * from t_base_user where name="" and status=1;

Like the above statement, if a joint index is built, 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) Leftmost prefix matching principle
MySQL will keep matching to the right until it encounters a range query (>, <, between, like) and stops matching, such as

select * from t_base_user where type="10" and created_at<"2017-11-03" and status=1, (This statement is only for demonstration)

In the above statement, status will not go to the index, because MySQL has stopped matching when < is encountered. The index used at this time is: (type, created_at). The order can be adjusted, but status cannot be reached. Index, at this time the statement needs to be modified as:

select * from t_base_user where type=10 and status=1 and created_at<"2017-11-03"

That is Status index can be walked.

(3) Function operation
Do not perform function operations on the index column, otherwise the index will be invalid. Because the b-tree stores all field values in the data table, but when retrieving, you need to apply functions to all elements to compare, which is obviously too costly.

(4) Expansion first
Expansion first, do not create a new index, try to modify the 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, if you need to add idx_name_email The index should be to modify the idx_name index instead of creating a new index.

Correction of misunderstandings

As mentioned above, how to create a new index, now we can answer the misunderstandings in the first step.

Misunderstanding 1: There is no need to create an index when creating a new table, and the index will be added later

Answer: A good data table design should consider the creation of indexes at the beginning, rather than waiting until later When something goes wrong and affects business use, a new index is created to save the situation, and the cost of subsequent index creation is relatively high. (This is to leave opportunities for production accidents to take root and sprout)

Misunderstanding 2: The fields after the where condition are all indexed

Answer: This misunderstanding is relatively common, but the fields after the where condition are indexed It is not necessary to create all indexes. Too many indexes will also lead to a sharp increase in index files and will not achieve the desired effect. For details, please refer to the section on creating an index above.

Misunderstanding 3: Simple SQL does not need to create an index, and joint query requires the creation of an index

Answer: This misunderstanding needs to be explained carefully. Nowadays, Internet companies, especially under the B/S architecture, the business The logic is stripped off at the code logic layer. At the final SQL level, it is actually some simple SQL, with only some connection queries and more single-table operations. (There are many logics written at the SQL level in the C/S architecture) , you said these statements are not simple.

Misunderstanding 4: The order of the joint index is the order of the fields after the where condition

Answer: As we just said, the order of the joint index is based on the leftmost prefix principle and the degree of distinction. The distinction has nothing to do with the order of fields after the where condition.

Misunderstanding 5: Create an index for a field with a small degree of differentiation

Answer: Creating a new index for a field with a small degree of differentiation is basically ineffective and will also add a large number of index files. You said Isn’t it worth the loss?

Is the index important?

The above introduces the concept of MySQL index and some techniques when creating a new index. Such a theoretical thing, for children's shoes that are not used or used relatively rarely, the importance of indexing may not be so intuitive at this time. So, let me talk about the losses and pitfalls I have suffered in indexing! It is also a common problem of not building an index!

0. Causes slow query
This problem is often caused by unindexed, (there are also many details here, such as: implicit type conversion, etc.)

1. Causes service timeout

Scenario:
When going online at a certain time, as a service provider, provide services to business parties. At first I thought I was just providing a simple service, and the test has been completed. I am still secretly happy that I can finally go home early today!

Description:
As soon as it was actually online, when the business party requested a call in the production environment, each request timed out and the data had been landed. At this time, I could only review the code. Finally, I found that there was something wrong in production. As a result of a slow query, it took more than 10 seconds. You would never imagine how simple this statement is. It is actually a single-table WHERE conditional query statement. You say that this kind of reason causes the service to be unavailable. Are you right or wrong? Are you angry? (This is why I say that for a good data table design, new indexes must be considered from the beginning).

2. Database server CPU 100%

On SQL with relatively high query frequency, if the query is slow due to no index being built, it will cause the database server CPU to be 100%, affecting But the whole system.

Summary
There are several types of problems mentioned above. Problems caused by not establishing an index can cause slow queries and affect the system efficiency. At worst, they can cause CPU 100% and affect the use of the entire system. See At this point, do you think indexing is important?

Finally

As mentioned above briefly, what is an index? What is its use, as well as some tips when building an index, and also emphasizes the importance of indexing. So indexing is so important, how to avoid it in daily coding? The following are my personal suggestions:

1. When building a table, you should consider adding indexes, such as foreign key fields, etc.

2. After writing the SQL, be sure to check the execution plan. Try to avoid full table scans.

3. If you add an index to an existing table, you must first calculate the distinction of the field.

4. Joint index, put the greatest distinction in the front.

5. Follow the MySQL left column prefix priority 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. 2008. Serializable isolation for snapshot databases. In SIGMOD '08: Proceedings of the 2008 ACM SIGMOD international conference on Management of data, pages 729–738, New York, NY, USA. ACM.[ 4]Michael James Cahill. 2009. 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 39(2), pages 492–528, June 2005.

mysql index--(mysql learning 2)_MySQL

Mysql-index learning (1)_MySQL