MySQL Query optimization

First, why the query slow

Network, CPU calculation, generated statistics, execution technology, lock wait, io wait, etc.

Second, slow query optimization access to inefficient query processing methods:

1. Confirm that you have accessed too many rows, or too many columns
2. Have you analyzed a large amount of data

Problems and Solutions

1. Querying for unwanted records: using limit
2. Avoid select *, which affects index overlay scanning
3. Avoid querying the same data repeatedly

Three ways to use the WHERE statement

1. The storage engine uses where to filter for invalid fields directly
2. Filter invalid fields in index using index Scan (extra appears with using index)
3. Returns all data then where filter (the using where is present in extra)

Querying large amounts of data to return a small number of rows is a technique to consider

1. Using the Overwrite index
2. Modifying a library table structure such as adding a summary table
3. overriding complex queries

Iii. refactoring query methods consider using a complex query or multiple simple queries Slice Query

1. Spread the pressure of the server to a time period

Explode an associated query

1. Make the cache more efficient
2. Performing a single query can reduce the resulting contention problem
3. Easier to split data tables for high performance and scalability
4. It might be possible for MySQL to query sequentially by ID, rather than with the authorities
5. Queries that can reduce redundant data
6. Equivalent to implementing a hash association in an application rather than using a nested loop association of MySQL

Iv. query execution basic query execution process

1. The server receives a query command
2. If there is a query cache that returns results directly, otherwise go to the next stage
3. SQL parsing, preprocessing
4. The optimizer generates the corresponding execution plan
5. Call the storage Engine API according to the execution plan to complete the query
6. Returns the result

Query cache

Use case-sensitive hash lookup implementations, as long as the query statement has a little change, it will not be hit. The user's permissions are also confirmed again during this period. The query statement is not parsed at this time, so it explains why the where a>1+1 will not be hit by the cache after the a>2 execution is done.

MySQL Execution Association policy

MySQL performs a nested association operation on any association.

• MySQL first loops out a single piece of data in a table
• Then nest loops into the next table looking for matching rows
• Go down until you find the matching behavior in all tables.
• The columns that are required in the query are then returned based on the rows that match each table
• MySQL optimizes the order of associations based on the corresponding optimization strategy, which makes traversing multiple tables achieve better results.

View the refactoring query

Execute on the query

explain select * from t1;

After execution

show warning;

Sorting optimization   

1. Old: Reads the row pointer and the field that needs to be sorted, sorts it, and then reads the required rows based on the sorted result
2. NEW: Reads all the columns required by the query, then sorts them according to the given column, and returns the sort results directly

3. Comparison:

   • Pros: Only one sequential IO is required, which has a great advantage over two Io (with one random io)
   • Cons: Introduce a lot of useless columns, occupy the memory in vain, have no influence on the sorting

4. File Sort:

   • Each sort record is assigned a fixed-length space long enough to hold, and sometimes the sort takes up more space than the data store occupies.
   • For an associative query to count all of the columns in the ORDER BY clause from the first table, MySQL sorts the files when associating the first table (using Filesort is visible in extra)
   • Otherwise, the associated results are placed in the staging table, and then the file is sorted after all the tables have been associated, and the limit can be executed in advance after version 5.6, avoiding the need to sort large amounts of data to the last few awkward situations

MySQL Query optimization