Optimized sentence optimization for MySQL database

Database optimization purposes

Avoid page access errors

1, because the database link timeout generated page 5xxx wrong I

2. The page cannot be loaded due to slow query

3. Data cannot be submitted due to blocking

How to find the problematic SQL

Use MySQL slow log to monitor efficiency issues with SQL

Show variables like '%slow% ';

Set global log_queries_not_using_indexes=on;

Represents a record that does not use an index after the SQL is optimized to record open without using an index

The Long_query_time unit is set to 100ms in seconds, which is 0.1 seconds.

If slow log time is short, there will be a large number of logs, disk space may be stained with the first MySQL official Mysqldumpslow
Analyze slow-scan tool to install MySQL's own tool, mysqldumpslow-t 3 slow-Scan log path | More view

But the results are not very comprehensive.

So use Pt-query-digest.

--limit=a parameter-review expain execution plan such as scanning a lot of rows, sending to the client very little, representing the index is not very good

The first part is the header shows the basic information, the time range of SQL number second table statistics Part III SQL information

As an example, this SQL executes once, but the execution time takes up 7,300, and the SQL focuses on the number of executions and the percentage of total elapsed time. Database main bottleneck in IO This, the main concern is the number of scan lines, if the number of rows scanned, the greater the consumption of his IO.

The first focus on the number of executions and execution time ratio, the second focus on scanning the number of examine, indicating that his IO consumption will be greater, the third by scanning the number of rows and the number of rows sent, if the number of scanned rows examine, far greater than the number of send sent lines, indicating that the SQL index hit rate is not high

How to optimize after a slow statement is found

The first is to use Explaim this clause, you can display the SQL execution plan, the principle of SQL in the database, the execution plan analysis, and then the specific implementation of SQL, the implementation of the plan side of the response to the SQL execution efficiency,

Table is the table that the data for this row is about.

Type this column of const performance Best Performance worst is all

The const description is a constant lookup, usually the primary key. A unique index to find,

Eq-reg is a range of lookups, usually a unique index. The range lookup for the primary key,

Ref is more common in connected queries, where a table is based on a lookup of an index,

Range is the scope lookup for the index,

Index is a scan of the indexes,

All is a table scan
There is no where condition so the index is not used

The shorter the length of the Key-len index, the better, because in the query process in MySQL, the smaller the index length of the better, MySQL every read is in page units, if the number of indexes stored in a page, the higher the query efficiency will be.

Rows is actually the number of rows scanned by the table,

Extension Columns one is

Filesort query results using file sorting method to optimize, file sorting is mainly in the order procedure is more common,

Temporary this is used in temporary tables, whether filesort or temporpary use external files or temporary tables for data storage, this SQL generally appears in the order by the GROUP BY clause, such SQL is also to focus on. The above is the use of explain clauses and some values returned, execution plan

Optimized sentence optimization for MySQL database