High performance mysql--query performance optimization

In database design, if the query is written badly, even if the library table structure design is reasonable, the index can not achieve high performance.

1. Optimize data access

The most basic reason for poor query performance is that there is too much data to access. For inefficient queries, analysis is always effective through the following two steps:

1. Verify that the application is retrieving a large amount of data than needed. This usually means accessing too many rows or too many columns;
2. Verify that the MySQL server layer is analyzing a large number of data rows that are more than needed.

Whether unwanted data was requested from the database

Some queries request more data than is actually needed and are discarded by the application, which increases the additional burden on the MySQL server and increases network overhead.

Typical cases:

1. Query for records that are not required. For example: Querying all the data in a table, showing only the first 10 bars. The simplest and most effective way--add limit after such query;
2. Returns all columns when multiple table associations are associated. For example: Setect * from actor INNER JOIN film_actor USING (actor_id) ...; Improvement: Selcet actor.* from actor INNER JOIN film_actor USING (actor_id) ...; Only the required columns are taken out;
3. Always remove all columns. Removing all of the columns will cause the optimizer to fail the optimizer to complete an index overlay scan such optimizations, and also to bring additional I/O, CPU, and memory consumption to the server. So some DBAs even strictly forbid the writing of select *;
4. Query the same data repeatedly.

Does MySQL scan for additional records

For MySQL, the simplest measure of query overhead is three metrics: response time, number of rows scanned, and number of rows returned.

General MySQL can be used in three ways to apply the Where condition, favorably to the inferior order:

1. Use the Where condition in the index to filter for mismatched records, which is done at the storage engine level;
2. Use an index overlay scan to return records, filter unwanted records directly from the index, and return hit results. This is done at the MySQL server layer, but does not require a back table query;
3. Returns data from the data table and then filters records that do not meet the criteria. This is done at the MySQL server layer, and you need to read all the records from the data table and then filter.

If you find that a query needs to scan large amounts of data, but only a small amount of data is returned, try the following to optimize it:

1. Use the index overlay scan to put all the required columns into the index, so that the storage engine does not need to back the table to get the corresponding row data;
2. Change the structure of the library table. For example: Use a separate summary table;
3. Rewrite the complex query so that the MySQL optimizer can execute it in a more optimal way.

2. How to Refactor queries

1. A complex query or multiple simple queries. When designing queries, it is important to consider whether a complex query needs to be divided into several simple queries.
2. Slice query-Splits a large query into smaller queries, each of which returns only a subset of the results at a time. For example: To delete 1000w data at a time, if cut into each delete 1w, divided by multiple deletion, this will greatly reduce the burden on the MySQL server. DELETE from messages WHERE ...;
3. Decomposing associated queries--refactoring queries with the decomposition of associated queries has the following advantages:
   • Make the cache more efficient. Many reference programs can easily be used to query the corresponding result object;
   • After the query is decomposed, executing a single query can reduce the contention of the lock;
   • In the application layer to do the correlation, it is easier to split the database, more easily to achieve high performance and scalability;
   • The efficiency of the query itself may also be improved. For example, using in () instead of an associative query allows MySQL to query in the order of IDs, which may be more efficient than a random association;
   • Queries that can reduce redundant records.

3, the basis of query execution (slightly) 4, MySQL query optimizer limitations associated subqueries

For example: SELECT * from film where film_id in (SELECT film_id from film_actor where actor_id = 1); After the MySQL optimizer is optimized: SELECT * from film where EXISTS (select film_id from film_actor where actor_id = 1 and film_actor.film_id = fil M.FILM_ID);

As can be seen from the optimizer optimization results, MySQL will press the relevant outer table into the subquery, when the subquery needs to be based on "film_id" to correlate the external table film, because the "film_id" field is required, MySQL does not think that the subquery can be executed, so the film table performs a full table scan, So the performance is very low.

Optimization scenario: Overwrites the associated subquery with the associated query. For example: SELECT film.* from film INNER JOIN film_actor USING (film_id) WHERE actor_id = 1;

Limitations of Union

Sometimes MySQL cannot move the constraints from the outer to the inner layer. For example, if you want each clause of a union to take only a subset of the result set based on limit, or if you want to sort and then merge the result set, you need to use these clauses separately in the various clauses of union.

such as: (select First_Name, last_name from actor) UNION (select First_Name, last_name from Customer) ORDER by last_name LIMIT 2 0;

Optimization scheme: (select First_Name, last_name from actor ORDER by Last_Name LIMIT) UNION (select First_Name, last_name from custom Er ORDER by last_name limit 20;

Loose index Scanning

For historical reasons, MySQL does not support loose index scans and cannot scan an index in a discontinuous fashion.

For example: There is an index key (a, b); Execute query select * FROM table WHERE B between 2 and 5; Because the leading field of the index is column A, but only B is specified in the query, Gu MySQL cannot use the index;

Optimization scenario: Add a possible constant value to the leading column. SELECT * FROM Table WHERE A in (2, 3, 5, ...) and b between 2 and 5;

Querying and updating on the same table

MySQL does not allow querying and updating the same table at the same time. You can bypass this restriction by generating a temporary table.

For example: UPDATE tbl as outer_tbl SET cnt = (SELECT count (*) from TBL as inner_tbl WHERE inner_tbl.type = outer_tbl.type);

Optimization scheme: UPDATE tbl INNER JOIN (SELECT type, COUNT (*) as CNT from TBL GROUP by type) SET tbl.cnt = der.cnt;

5. Tips for query optimizer

If you are dissatisfied with the execution plan selected by the optimizer, you can use several hints (hint) provided by the optimizer to control the final execution plan. Refer to the official MySQL manual for details.

6. Optimization of specific types of query optimization count () query

count () functions : Count () is a special aggregation function that has two very different usages: counting the number of values in a column, and counting the number of rows. When a column value is counted, the column value is required to be non-empty (no statistics null). If the expression of a column or a column is in parentheses, the count is the number of results that the expression has a value.

High performance mysql--query performance optimization