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

This article mainly explains "the query optimization method of MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the query optimization method of MySQL.

1. Introduction

A good web application, the most important thing is to have excellent access performance. Database MySQL is not only a part of web application, but also an important part of its performance. So it is very important to improve the performance of MySQL.

The improvement of MySQL performance can be divided into three parts, including hardware, network and software. Among them, the hardware and network depend on the financial resources of the company, and they need silver taels for nothing, not to mention here. The software is subdivided into many kinds, and here we improve the performance through query optimization of MySQL.

Recently, I have read some books on query optimization, and also read some articles written by my predecessors on the Internet.

Here are some summaries about query optimization that I have sorted out and used for reference:

Back to the top.

2. Intercept SQL statements

1. Comprehensive query log

2. Slow query log

3. Binary log

4. Process list

SHOW FULL PROCESSLIST

. . .

Back to the top.

3. Query optimization basic analysis command

1. EXPLAIN {PARTITIONS | EXTENDED}

2 、 SHOW CREATE TABLE tab

3 、 SHOW INDEXS FROM tab

4. SHOW TABLE STATUS LIKE 'tab'

5. SHOW [GLOBAL | SESSION] STATUS LIKE''

6 、 SHOW VARIABLES

.

Ps: I feel like there's nothing nutritious on it. Here is the real practical information.

Back to the top.

4. Query optimization in several directions

1. Try to avoid full-text scanning, add an index to the corresponding fields, and apply the index to query.

2. Delete indexes that are not used or duplicated

3. Query rewriting, equivalent transformation (predicate, subquery, join query)

4. Delete unnecessary statements with repeated contents and simplify the statements.

5. Integrate repeated statements

6. Cache query results

Back to the top.

5. Index optimization

Back to the top.

5.1. Index advantages:

1. Maintain the integrity of the data

2. Improve the query performance of data.

3. Improve the join operation of the table (jion)

4. Sort the query results. If there is no index, the internal file sorting algorithm will be used for sorting, which is less efficient.

5. Simplify the operation of aggregated data

Back to the top.

5.2. Index shortcomings

1. The index needs to occupy a certain amount of storage space.

2. Data insertion, update and deletion will be affected by the index, and the performance will be degraded. Because the data change index also needs to be updated.

3. Multiple indexes, if the optimizer needs to be time-consuming, you will have a good choice.

Back to the top.

5.3. Index selection

1. It is used when there is a large amount of data.

2. When the data is highly duplicated, do not use

3. If the query fetches more than 20% of the data, full-text scanning will be used instead of indexing.

Back to the top.

5.4. Index study

Information Enquiry:

InnoDB and MyISAM in MySQL are all B-Tree type indexes.

B-Tree contains: PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT

B-Tree type indexing is not supported (that is, the index will not be used when the field uses the following symbols):

>, =, 10) as T2 where t1.age > 10 and t2.age10 and t2.age10

Specific steps:

1. Merge from and from to modify the corresponding parameters

2. Merge where with where and connect with and

3. Modify the corresponding predicate (in =)

Back to the top.

7. Equivalent predicate rewriting:

1. BETWEEEN AND is rewritten to > =, = 'abc' and nameb and b > 10 to a > b and a > 10 and b > 10 to optimize significantly.

Back to the top.

9. External connection optimization

Change the external connection to the internal connection

Advantages:

1. Optimizing the processor to deal with external connections is more and time-consuming than internal connections.

2. after the external join is eliminated, the optimizer chooses the multi-table join order with more choices, which can be selected.

3. The table with the strictest filter criteria can be used as the appearance (the outer loop layer of the multi-layer loop body is at the top of the join order).

It can reduce the unnecessary Istroke O overhead and speed up the execution of the algorithm.

The difference between on a.id=b.id and where a.id=b.id, on joins tables, and where compares data.

Note: the premise must be that the result is NULL reject (that is, conditional restrictions do not NULL data rows, semantically inner joins)

Optimization principles:

Simplified query, join elimination, equivalent conversion, removal of redundant table object join

For example, if the primary key / unique key is used as the join condition, and the intermediate table column is only used as the equivalent condition, the intermediate table join can be removed.

Back to the top.

10. Other query optimization

1. The following will cause the index query to be abandoned and full-text scanning will be used

1.1.Use the! = or operator in the where clause Note: primary key support. Non-primary keys are not supported

1.2. avoid using or

After testing, it is not necessary to use the index if you use or. In most cases, the index is not used, but there are still a few cases that are used, so the specific situation is analyzed.

Similar optimizations:

Select * from tab name='aa' or name='bb'

= >

Select * from tab name='aa'

Union all

Select * from tab name='bb'

Measured:

1, 100,000 data test, without any index, the above query rate is twice as fast as the following query rate.

2. 300000 data test, when both aa and bb are indexed separately, the query rate below is a little faster than or.

1.3. avoid using not in

Indexes are generally not available in not in; primary key fields can

1.4.The use of null judgment should be avoided in where.

1.5. like cannot be preceded by the percent sign like'% .com'.

Resolve:

1. If you must use% prefix, and the length of the data is not large, such as URL, you can flip the data into the database and check it again. LIKE REVERSE'%.com'

2. Use overlay index

1.6. When using an index field as a condition, if it is a composite index, you should use the field name of the leftmost prefix of the index

2. Replace in with exists

Select num from a where num in (select num from b)

Select num from a where exists (select 1 from b where num=a.num)

1 million items of data, and it took 6.65 s and 4.18 s to screen 59417 items of data. There are no other optimizations, just replacing exists with in.

3. The field definition is a string, the query is not marked with quotation marks, the index will not be used, and a full-text scan will be performed.

At this point, I believe that everyone on the "MySQL query optimization methods" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.