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

This article mainly explains the "optimization principles of MySQL sentences". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the optimization principles of MySQL sentences".

1. Use the index to traverse the table faster.

The index built by default is a non-clustered index, but sometimes it is not optimal. Under a non-clustered index, the data is physically randomly stored on the data page. Reasonable index design should be based on the analysis and prediction of various queries. Generally speaking:

a. There are a large number of duplicate values and there are often range queries (>

=, < =) and order by, group by occurred columns, you can consider the establishment of a cluster index

b. Multiple columns are often accessed at the same time, and each column contains duplicate values. Consider establishing a combined index.

c. The composite index should try to make the key query form index coverage, and its leading column must be the most frequently used column. Although indexes help to improve performance, it is not better to have as many indexes as possible. On the contrary, too many indexes can lead to system inefficiency. Each time the user adds an index to the table, it is necessary to update the index collection to maintain it.

2. Use format conversion as little as possible in massive queries.

3. ORDER BY and GROPU BY use ORDER BY and GROUP BY phrases, and any index helps to improve the performance of SELECT.

4. Any operation on the column will result in a table scan, which includes functions, evaluation expressions, and so on. When querying, move the operation to the right of the equal sign as much as possible.

5. IN and OR clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the clause apart. The index should be included in the split clause.

6. As long as you can meet your needs, use smaller data types as much as possible: for example, use MEDIUMINT instead of INT

7. Try to set all the columns to NOT NULL. If you want to save NULL, set it manually instead of setting it as the default value.

8. Use VARCHAR, TEXT and BLOB types as little as possible

9. If you have only a small number of data that you know. It is best to use the ENUM type

10. Build an index, as graymice said.

The following is an experiment I have done to find that indexes can greatly improve the efficiency of queries:

I have a member information form users, which contains 37365 user records:

Query without indexing:

Sql statement A:

Copy the contents to the clipboard

Code:

Select * from users where username like%%

The duration of 8 queries in Mysql-Front is 1.40, 0.54, 0.54, 0.54, 0.54, 0.53, 0.53, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, respectively.

Sql statement B:

Copy the contents to the clipboard

Code:

Select * from users where username like allowed%

The duration of 8 queries in Mysql-Front is: 0.53, 0.53, 0.53, 0.53, 0.54, 0.54, 0.53, 0.53, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, 0.54, respectively.

Sql statement C:

Copy the contents to the clipboard

Code:

Select * from users where username like%

The duration of 8 queries in Mysql-Front is: 0.51, 0.51, 0.52, 0.52, 0.52, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, 0.51, respectively.

Add an index to the username column:

Copy the contents to the clipboard

Code:

Create index usernameindex on users (username (6))

Query again:

Sql statement A:

Copy the contents to the clipboard

Code:

Select * from users where username like%%

The duration of 8 queries in Mysql-Front is: 0.35, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34, 0.34

Sql statement B:

Copy the contents to the clipboard

Code:

Select * from users where username like allowed%

The duration of 8 queries in Mysql-Front is: 0.06, 0.07, 0.07, 0.07, 0.07, 0.07, 0.07, 0.07, 0.07, 0.06, 0.06, a total of 836 records were found.

Sql statement C:

Copy the contents to the clipboard

Code:

Select * from users where username like%

The duration of 8 queries in Mysql-Front is: 0.32, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31, 0.31 and 0.31.

In the course of the experiment, I did not open any other program, the above data show that in the single table query, the establishment of the index can greatly improve the query speed.

Another thing to say is that if an index is established, the speed improvement is the most obvious for like allowed% type queries. Therefore, we try our best to query in this way when writing sql statements.

Our optimization principles for multi-table queries are:

Try to index on the fields involved in the conditional statement of: left join on/right join on. + condition.

Multi-table query can better reflect the advantages of index than single-table query.

11. The principles of establishing the index:

If there are few prefix duplicates in a column of data, it is best to index only that prefix. Mysql supports this kind of index. The indexing method I used above is to index the leftmost six characters of the username. The shorter the index, the less disk space it takes and the less time it takes to retrieve it. This method can index up to 255 characters to the left.

In many cases, we can index multi-column data.

The index should be based on the fields that are compared in the query conditions, not on the fields that we want to find and display

12. Affectionate question: worksheets are often used in IN and OR clauses to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the clause apart. The index should be included in the split clause.

How to solve this sentence? please give an example.

Examples are as follows:

If an index is built on both fields1 and fields2, fields1 is the primary index

The following sql will use the index

Copy the contents to the clipboard

Code:

Select * from tablename1 where fields1=value1 and fields2=value2

Indexes are not used in the following sql

Copy the contents to the clipboard

Code:

Select * from tablename1 where fields1=value1 or fields2=value2

13. Indexes greatly improve the speed of queries, but indexes also take up extra hard disk space (of course, hard disk space is not a problem now), and it takes some time for the index to update when inserting new records into the table.

Some tables do not need to be indexed if they are often insert with less select. Otherwise, it takes time to rewrite the index every time the data is written; this depends on the actual situation, and the index is usually necessary.

14. When I have doubts about query efficiency, I usually use Mysql's Explain directly to track queries.

You use Mysql-Front to compare by the length of time. I think it is more accurate to compare the number of times the fields are scanned when querying.

Thank you for your reading, the above is the content of "the optimization principles of MySQL sentences", after the study of this article, I believe you have a deeper understanding of the optimization principles of MySQL sentences, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.