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

This article mainly introduces how to optimize the sentences in the database, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

The main principle of how to speed up the query speed and optimize the query efficiency in MySQL database is to avoid full table scanning as far as possible and to consider establishing indexes on the columns involved in where and orderby.

Indexing is not about building as many indexes as possible. The principle is:

First: the more indexes in the table, the better.

The more indexes a table has, the better, and there is no specific number. According to past experience, a table cannot have more than 6 indexes at most, because the more indexes it has, it will also have an impact on the performance of update and insert operations, involving index creation and reconstruction operations.

Second, the methodology of indexing is:

Columns commonly used by most queries

Columns with few modifications

The index needs to be based on columns with large data differences.

Using the above basis, let's discuss how to optimize sql.

Composite index (such as an index in the form of (xQuery yjinuid))

Let's first look at such a statement like this: select*fromuserswherearea='beijing'andage=22

If we create indexes on area and age respectively, since mysql queries can only use one index at a time, although this has improved the efficiency of full table scans compared to those without indexing, it will be more efficient if we create composite indexes on two columns of area,age.

When using an index field as a condition, if the index is a composite index, the first field in the index must be used as a condition to ensure that the system uses the index, otherwise the index will not be used. and the order of the fields should be consistent with the order of the index as far as possible.

For example, if we create such an index (area,age,salary), it is tantamount to creating (area,age,salary), (area,age), (area) three indexes, which is called the best left prefix feature.

Guidance on structure optimization of MySQY statement model

Index Optimization of a.ORDERBY+LIMIT combination

If a SQL statement looks like: select [column1], [column2], … . FROM[TABLE] ORDERBY[sort] LIMIT [offset], [LIMIT]

This SQL statement is relatively simple to optimize, just create an index on the [sort] field.

Index Optimization of b.WHERE+ORDERBY+LIMIT combination

If a SQL statement looks like: select [column1], [column2], … .from [TABLE] WHERE [columnX] = [VALUE] Orderby [sort] LIMIT [offset], [LIMIT]

This statement, if you still use the method of indexing in the first example, although the index can be used, but it is not efficient. A more efficient approach is to create a federated index (columnX,sort)

C.WHERE+ORDERBY multiple fields + LIMIT

If a SQL statement looks like: SELECT* From [table] WHEREuid=1ORDERx,yLIMIT0,10

For this statement, you may want to add an index like this: (xquotiy rep uid). But actually the better effect is (uid,x,y). This is caused by the mechanism by which MySQL handles sorting.

Thank you for reading this article carefully. I hope the article "how to optimize the sentences of the database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.