The use of order by and limit queries in MySQL for slow resolution

Let's take a look at the test performance

The code is as follows

Copy Code

1. Show row 0-9 (10 totals, query takes 32.4894 seconds) 2.SQL Query: SELECT * 3.FROM tables 4.WHERE m_id 5.IN (50, 49) 6.ORDER by ID DESC 7.LIMIT 10 Show Row 0-9 (10 total, query takes 32.4894 seconds) SQL Query: SELECT * From tables WHERE m_id In (50, 49) ORDER BY ID DESC LIMIT 10 1. Show row 0-9 (10 totals, query takes 0.0497 seconds) 2.SQL Query: SELECT * 3.FROM tables 4.WHERE m_id 5.IN (50, 49) 6.LIMIT 10 Show Row 0-9 (10 total, query takes 0.0497 seconds) SQL Query: SELECT * From tables WHERE m_id In (50, 49) LIMIT 10 1. Show row 0-29 (1,333 totals, query takes 0.0068 seconds) 2.SQL Query: SELECT * 3.FROM tables 4.WHERE m_id 5.IN (50, 49) 6.ORDER by ID DESC 7. Show Row 0-29 (1,333 total, query takes 0.0068 seconds) SQL Query: SELECT * From tables WHERE m_id In (50, 49) ORDER BY ID DESC 1. Show row 0-29 (1,333 totals, query takes 0.12 seconds) 2.SQL Query: SELECT * 3.FROM tables 4.WHERE m_id 5.IN (50, 49) 6.ORDER by m_id, id DESC 7. Show Row 0-29 (1,333 total, query takes 0.12 seconds) SQL Query: SELECT * From tables WHERE m_id In (50, 49) ORDER by m_id, id DESC 1. Show row 0-29 (1,333 totals, query takes 0.0068 seconds) 2.SQL Query: SELECT * 3.FROM tables 4.FORCE Index (M_ID)//Mandatory Index 5.WHERE m_id 6.IN (50, 49) 7.ORDER by ID DESC 8.

The above method if the amount of data on the Tens will also be very slow, it is possible to query to 10 seconds or longer,

Optimize limit and offset

MySQL limit work is to read the first N records, and then discard the top N, read M want, so the greater the N, performance will be worse.

The code is as follows	Copy Code
Optimize before Sql:select * from member ORDER by last_active LIMIT 50,5 After optimization Sql:select * from member INNER JOIN (SELECT member_id from member order by Last_active LIMIT 5) USING (member_id)

The difference is that SQL before optimization requires more I/O waste, because reading the index first, then reading the data, and then discarding the unnecessary rows. and the optimized SQL (subquery

The read-only index (Cover index) is OK, and then read the required columns through member_id.

for MySQL server optimization can also improve performance

1, only to return the required data

Returning data to the client requires at least database extraction data, network transmission data, client receiving data and client processing data, etc.

Back to unwanted data, will increase the server, network and client invalid labor, the harm is obvious, avoid such incidents need attention:

A, landscape, do not write select * statements, but select the fields you need.

B, portrait, a reasonable write WHERE clause, do not write the SQL statement without where.

C, note the WHERE clause in the SELECT INTO, because select into inserts the data into the temporary table, which locks some system tables if this

A WHERE clause returns too much data or is too slow, causing the system table to lock in for a long time, and to plug other processes.

D, for aggregate queries, you can further qualify the returned rows with the HAVING clause.

2, as little as possible to do repetitive work

This point is the same as the previous point, is to minimize the ineffective work, but this focus on the client program, you need to pay attention to the following:

A, control the multiple execution of the same statement, especially some basic data multiple execution is a lot of programmers pay little attention to.

B, reduce the number of data conversion, may require data conversion is the problem of design, but the reduction of times is the programmer can do.

C, to eliminate unnecessary subqueries and join tables, subqueries in the execution plan generally interpreted as an external connection, redundant connection table brings additional overhead.

D, merging multiple updates for the same condition of the same table, such as

The code is as follows	Copy Code
UPDATE EMPLOYEE SET fname= ' haiwer ' WHERE emp_id= ' vpa30890f ' UPDATE EMPLOYEE SET lname= ' yang ' WHERE emp_id= ' vpa30890f '

The two statements should be merged into one of the following statements

The code is as follows	Copy Code
UPDATE EMPLOYEE SET fname= ' haiwer ', lname= ' Yang ' WHERE emp_id= ' vpa30890f '

E, update operations do not split into the delete operation +insert operation form, although the same function, but the performance difference is very large.

F, do not write some meaningless query, such as: SELECT * from EMPLOYEE WHERE 1=2

3, attention to business and locks

The transaction is the database application and the important tool, it has the atomicity, the consistency, the isolation, the persistence these four attributes, many operations we all need to use

Transactions to ensure the correctness of the data. In the use of transactions we need to do to avoid deadlocks, as far as possible to reduce congestion. Particular attention needs to be paid to the following

：

A, the transaction operation process to be as small as possible, can split the transaction to split apart.

B, the transaction operation process should not have interaction, because the interaction is waiting, the transaction does not end, may lock a lot of resources.

C, the transaction operation process to access the object in the same order.

D, increasing the efficiency of each statement in a transaction, using indexes and other methods to improve the efficiency of each statement can effectively reduce the execution time of the entire transaction.

E, try not to specify the lock type and index, SQL Server allows us to specify the lock type and index used by the statement, but in general, SQL

The lock type and index selected by the server optimizer are optimal under the current data volume and query conditions, and we may specify only in the present case more

, but the data volume and data distribution will change in the future.

F, the query can be used at a lower isolation level, especially when the report query, you can choose the lowest isolation level