<< High Performance mysql>> Note 2

Reprint Please specify: theviper http://www.cnblogs.com/TheViper

This article next MySQL query statement optimization

• Did you request data that you don't want?

Typical case: Querying for unwanted records, returning all columns when multiple tables are associated, always fetching all columns, and querying the same data repeatedly.

• Whether to scan for additional records

The simplest metric for measuring query overhead.

1. Response data
2. Number of rows scanned
3. Number of rows returned

• type of Access

When evaluating query overhead, consider the cost of finding a row of data from a table, and there are several ways in which MySQL can find and return a row of results. Some access methods may require many rows to be scanned to return a row of results, and some may return results without scanning.

The type column in the explain statement responds to the access types. There are many types of access, from full table scan to index Scan, range Scan, unique index query, constant reference, and so on. These are listed here, the speed is from slow to fast, the number of rows scanned is also small to large.

So try to avoid having each SQL do a full table scan.

If the query is not able to find the right type of access, the best way to solve it is usually to add an appropriate index, as mentioned in the previous article. The index allows MySQL to find the required records in the most efficient way, with the fewest number of rows scanned.

General MySQL has three ways to apply the Where condition. From good to bad in turn

• Use the Where condition in the index to filter for mismatched records, which is done in the storage engine layer.
• Use an index overlay scan (using index in the extra column) to return records, filter unwanted records directly from the index, and return hit results. This is done at the MySQL service layer, but it is not necessary to return the table query records.
• Returning data from a table and filtering records that do not meet the criteria (where in the extra column) is done at the MySQL service layer, and MySQL needs to first read the records from the data table and then filter.

If you find that a large amount of data is scanned in a query, only a small number of rows are returned. You can try the following method optimizations.

• Use the index overlay scan to put all the required columns into the index so that the storage engine can return the results without getting the corresponding rows back to the table.
• Change the structure of a table, such as using a separate summary table
• Rewrite this complex query to let the MySQL optimizer execute the query in a more optimized way

• Refactoring Query methods

A complex query or a number of simple queries?

In the traditional implementation, always emphasize that the database layer to complete as much work as possible, the logic of this is always believed that network communication, query parsing, optimization is a very expensive thing.

But the idea for MySQL does not work, MySQL from the design to make the connection and disconnection is very light, in return to a small query results very efficient. In addition, today's network speed is much faster than before, whether it is broadband or delay. On some versions of MySQL, even on a common server, you can run more than 100,000 queries per second. Even a gigabit NIC can easily accommodate queries more than 2000 times per second.

Slice Query

The so-called Divide and conquer, the big query cut into small queries, each query function exactly the same, each time only a small number of results.

Deleting the old data is a good example of a regular cleanup of large amounts of data, if done one time with a large statement, it may lock up a lot of data at once, fill the entire transaction log, exhaust system resources, blocking many small but important queries.

So you can

Explode an associated query

Simply put, a single-table query is made for each table, and then the results are associated in the application. For example

It can be decomposed into the following query to replace

At first glance, this does no good. In fact, there are the following advantages

1. Make caching more efficient. Many applications can easily query the corresponding result object for a table of ease.
2. After the query is decomposed, executing a single query can reduce the competition for locks.
3. The application layer makes it easier to split the database, making it easier to perform high-performance and extensible.
4. The efficiency of the query itself is also improved. In this example, using in instead of the associated query allows MySQL to query in the order of IDs, which may be more efficient than a random association.
5. Queries that can reduce redundant records. When you make an associated query, you may need to revisit a subset of the data. From this point of view, such refactoring can also reduce the consumption of network and memory.
6. A hash association is implemented instead of a nested loop association using MySQL. In some scenarios, hash associations are much more efficient.

• How MySQL executes the associated query

The term "relevance" in MySQL has a broader meaning than general understanding. In general, MySQL considers any query to be an "association", and not just a query that requires up to two table matches to be called associations. So, in MySQL, each query, each fragment (including subqueries, even select based on a single table), can be associated.

Below is a look at how MySQL executes the associated query.

See union query first. MySQL first puts a series of individual query results into a temporary table, and then re-reads the temporary table data to complete the union query. In the MySQL concept, each query is associated once, so reading the resulting temporary table is also an association.

MySQL executes a nested loop association policy on any association, that is, MySQL first loops through a single table of data, then nesting loops into the next table to look for matching rows, and then down until all the tables match the behavior. The columns that are required in the query are then returned based on the rows that match each table.

You can see that the query starts from the actor table, which is the choice that the MySQL association query optimizer automatically makes. Now use the Straight_join keyword to not allow MySQL to automatically optimize the association.

This time the correlation order is reversed, you can see that the first associated table after inversion only needs to scan a very small number of rows. And the second, the Third Association table is based on the index query, the speed is very fast.

• Sorting optimization

Anyway, sorting is a very expensive operation. So from a performance perspective, you should avoid sorting as much as possible or avoid sorting large amounts of data.

The previous article talked about how to sort by index. When a sort result cannot be generated using an index, MySQL needs to sort itself, and if the amount of data is small, it is in memory, and the amount of data is large, then a disk is used. MySQL unification calls this process a file sort (filesort).

If you need to sort the query when it is associated with it, MySQL handles the sorting of the files in two different cases.

1. If all the columns in the ORDER BY clause are from the first associated table, MySQL sorts the files as they are associated with the first table. If so, the extra field in the explain result has a using filesort.

2. In all other cases, MySQL first stores the associated results in a temporary table and then sorts the files after all the associations have finished. If so, the extra field in the explain result will have a using temporary; Using Filesort. If there is a limit in the query, the limit is also applied after the sort. So even if you need to return fewer rows, the temporary table and the amount of data that needs to be sorted are still very large.

mysql5.6 has made a number of important improvements here. When you only need to return partial sorting results, for example, using the limit clause, MySQL will not sort all the results, but instead choose to discard the results that do not meet the criteria, and then sort them.

• Correlated Subqueries

MySQL's sub-query implementation is very bad, the worst kind of query is a subquery statement that contains in in the Where condition.

MySQL has a special optimization strategy for the options in the In () list, and it is generally assumed that MySQL performs subqueries first. Unfortunately, however, MySQL will first defer the related outer table to the subquery. For example

MySQL will change the query to this

As you can see, MySQL performs a full table scan of film first, and then executes the subquery one by one, based on the returned film_id. If the outer table is a very large table, the performance of this query can be very bad. Of course it is easy to rewrite this query, directly with the association on it.

Another optimization method is to use the function group_concat () to construct a comma-delimited list in ().

In addition, it is generally recommended to overwrite the in () subquery with the EXISTS () equivalent.

<< High Performance mysql>> Note 2