What are the skills of using indexing in mysql database

This article mainly introduces the use of indexing skills in the mysql database, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

Take 1 minute to learn about clustered indexes, nonclustered indexes, federated indexes, index overrides.

For example, business scenarios, user tables, and table structures are as follows:

T_user (uid primary key,login_name unique,passwd,login_time,age, …)

Clustered index (clustered index): a clustered index determines the physical sorting of data on disk. A table can only have one clustered index, which is generally constrained by primary key.

For example: the index on uid in a t_user scenario.

Nonclustered index (non-clustered index): it does not determine the physical sort of data on disk, the index contains only the data being indexed, and a row locator row-locator, which can be understood as a pointer to the physical sorting of a clustered index, through which row data can be found.

For example, find the business needs of a young MM:

Select uid from t_user where age > 18 and age < 26

The index built on age is a nonclustered index.

Federated index: an index built on multiple fields that can speed up the retrieval of review query conditions

For example, log in to business requirements:

Select uid, login_time from t_user where login_name=? And passwd=?

A federated index can be created (login_name, passwd).

The federated index can meet the leftmost query requirements, such as the joint index of (a, b, c) three columns, and can accelerate the query requirements of three sets of a | (a, b) | (a, b, c).

This is why there is no federated index (passwd, login_name). There are almost no single-conditional query requirements of passwd in business, but there are many single-conditional query requirements of login_name.

Questions:

Select uid, login_time from t_user wherepasswd=? And login_name=?

Can you hit (login_name, passwd) this federated index?

Answer: yes, the leftmost query requirement does not mean that the writing of the SQL statement must meet the order of the index (this is the misunderstanding of many friends)

Index coverage: for the column being queried, the data can be obtained from the index instead of going to the row through the row locator row-locator, that is, "the queried column will be overwritten by the index created", which can speed up the query.

For example, log in to business requirements:

Select uid, login_time from t_user wherelogin_name=? And passwd=?

You can build a federated index (login_name, passwd, login_time). Because login_time is already in the index, the queried uid and login_time do not have to go to row to get data, thus speeding up the query.

Finally, log in to this business scenario, login_name is unique, just build this single-column index.

Assignment:

Suppose the order has three states: 0 has been placed, 1 has been paid, and 2 has been completed

Which SQL is faster to query outstanding orders for business requirements?

Select * from order where statusindexing skills 2 select * from order where status=0 or status=1select * from order where status IN (0Magne1) select * from order where status=0unionselect * from order where stauts=1 Thank you for reading this article carefully. I hope the article "what indexing techniques are used in mysql database" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!