Optimized query statements in the MySQL database.

Optimized query statements in the MySQL database.

Most of the time, the system performance bottleneck for websites built based on php + MySQL is often found in MySQL, and the most frequently used statements in MySQL are query statements. Therefore, optimization of MySQL database query statements is crucial! This article makes a detailed analysis of this problem as follows:

1. Determine whether unnecessary data is requested from the MySQL database, in the following cases:

(1) query unnecessary data. For example, if you need 10 data records, you have selected 100 data records and added the limit by limit.
(2) When multiple tables are joined, all columns are returned.
(3) always retrieve all columns select *...... retrieving all columns will make it impossible for the optimizer to complete index overwrite scanning. This will also cause additional I/O, memory, and cpu consumption on the server.
(4) Repeat the query of the same data. For example, if you need to query the URL of the user's profile picture in the user's comments, you can cache the data when making multiple comments, retrieve from the cache when necessary, so the performance will be better.

2. Is mysql scanning additional records?

The following three indicators are used to measure query Overhead: response time, number of scanned rows, and number of returned rows.

Response time: Service time and queue time. Service time refers to the actual time spent by the database to process this query. The queue time refers to the query that the server does not actually execute because it waits for some resources.

Number of scanned rowsAndNumber of returned rows: Ideally, the number of scanned rows is the same as the number of returned rows.

In general, MYSQL can use the following three methods to apply the where condition record, from good to bad:

(1) Use the where condition in the index to filter unmatched records, which is completed at the storage index layer.

(2) Use index overwrite scanning to return records, filter unwanted records directly from the index, and return hit results, which are completed at the mysql server layer, however, you do not need to query records in the returned table.

(3) return data from the data table, and then filter records that do not meet the conditions. At the mysql server layer, read the records from the data table and then filter them.

If you find that a query needs to scan a large amount of data but returns a few rows, you can usually try the following techniques:

(1) Use index overwrite scanning to put all required columns into the index, so that the storage engine can return results without returning the table to obtain the corresponding row.

(2) Change the database table structure and use a separate summary table.

(3) rewrite this complex Query

3. Rebuild the query method

(1 ),One complex query or multiple simple queries:

Mysql can scan millions of data records in the memory per second. In contrast, it is much slower for mysql to respond to data from the client. When other conditions are the same, it is better to use as few queries as possible, however, it is necessary to break a large Query into multiple small queries.

(2 ),Split Query:

The deletion of old data is a good example. When a large number of data is regularly cleared, if a large statement is used once, a lot of data may be locked at a time to occupy the entire transaction log. System resources are exhausted and many small but important queries are blocked.

Mysql>deletefrommessageswherecreated<DATE_SUB(NOW(),INTERVAL3MONTH);

Rewrite:

Rows_affected=0;Do{Rows_affected=do_query(“deletefrommessageswherecreated<DATE_SUB(NOW(),INTERVAL3MONTH)”;)}

(3 ),Decomposition association query:

This improves the cache efficiency and allows you to conveniently cache a single piece of data in applications.
After the query is decomposed, executing a single query can reduce the lock competition.
Association at the application layer makes it easier to split databases and achieve high performance and high scalability.
The query itself is more efficient.
This can reduce the query of redundant data and perform associated queries at the application layer. This means that only one query is required for a data application, but a query in the database may require repeated access to some data.

Applicable scenarios:

① When the application can conveniently cache a single query result;
② When data can be distributed to different mysql servers;
③ When the IN () method can be used instead of the associated query;
④ When a data table is used in the query.

How to optimize mysql query statements can increase the query speed, as if my database has an index, how can I use it?

Increase query speed:
1. When writing SQL statements, if you do not need all columns, try not to use select * from and write the columns you want to query.
2. When writing SQL statements, try to use the where clause to filter out some records.
After an index is added to a field in the database, you can create, maintain, and use indexes on your own. You do not need to pay attention to these indexes in SQL statements.

Mysql DataBase Query Optimization

For query statement optimization, you can read a copy of "Database System Performance Tuning" written by danish shasha, which describes some statement optimization methods. For your statement, we recommend that you change this statement to the UNION of the two subquery results, that is:
Select p. id from project p, comment c where c.tar get_id = p. id and c.tar get_type = 500 and c. uid = 10001
Union
Select p. id from project p, bookmark B where B. project_id = p. id and B. uid = 10001;
You will find that your original statement has unnecessary Cartesian set join operations, so it is slow because the first subquery has nothing to do with bookmark, and the second subquery has nothing to do with comment, however, according to your statement, when the database generates an execution plan, the operations on the Cartesian set must be performed on both or conditions, which may cause great overhead.