Mysqlprofileexplainslow_query_log Analysis and Optimization queries often encounter some SQL problems in performance tests. In fact, the most problems encountered during performance tests over the past few years are the database, which is either high IO or high cpu, therefore, Data Optimization plays an important role in performance testing. I will introduce some msyql Performance
Mysql profile explain slow_query_log Analysis and Optimization queries often encounter some SQL problems in performance tests. In fact, the most problems encountered in performance tests over the past few years are the databases, either high I/O or high cpu, so data Optimization plays an important role in performance testing. Below I will introduce some msyql Performance
Mysql profile explain slow_query_log analysis optimization Query
Some SQL problems are often encountered in performance tests. In fact, the most common problems encountered in performance tests over the past few years are the database, which is either high I/O or high cpu, therefore, Data Optimization plays an important role in the performance testing process. Below I will introduce three tools that are frequently used in the performance tuning process of msyql:
1. Slow query (analyze problematic SQL statements)
2. Explain (shows how mysql uses indexes to process select statements and connect tables. Can help you select better indexes and write more optimized query statements)
3. Profile (query the SQL Execution time, and check the CPU/Memory usage, Systemlock, Table lock, and so on .)
First, let's talk about mysql slow query.
1. The configuration is enabled.
Linux:
Add
Log-slow-queries =/var/lib/mysql/slowquery. log (specify the location where the log file is stored, which can be empty and the system will give a default file host_name-slow.log)
Long_query_time = 2 (the time when the record exceeds, the default value is 10 s)
Log-queries-not-using-indexes)
Log-long-format (if it is set, all queries without indexes will be recorded)
Windows:
Add the following statement in [mysqld] Of my. ini:
Log-slow-queries = E: \ web \ mysql \ log \ mysqlslowquery. log
Long_query_time = 2 (other parameters above)
2. Viewing Method
Linux:
Use the mysql built-in command mysqldumpslow to view
Common commands
-S ORDER what to sort by (t, at, l, al, r, aretc), 'at' is default
-T NUM just show the top n queries
-G PATTERN grep: only consider into ts that includethis string
Eg:
S is the order, indicating that the writing is not detailed enough. I used it, including reading the code, mainly including c, t, l, r, ac, at, al, ar, they are sorted by the number of queries, time, lock time, and number of returned records. The time-reverse-t added with a is the meaning of top n, that is, the number of data records in the previous row-g, followed by a RegEx matching pattern, case insensitive
Mysqldumpslow-s c-t 20 host-slow.log
Mysqldumpslow-s r-t 20 host-slow.log
The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.
Mysqldumpslow-t 10-s t-g "left join" host-slow.log returns the first 10 SQL statements containing the left join according to time.
Next is the explain.
Usage:
Execute explain select * FROM res_user ORDER BYmodifiedtime LIMIT to get the following results:
Show result analysis:
Table | type | possible_keys | key | key_len | ref | rows | Extra
Description of the EXPLAIN column:
Table
Which table is the data of this row?
Type
This is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and ALL.
Possible_keys
Displays indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain.
Key
Actually used index. If it is NULL, no index is used. In rare cases, MYSQL selects an optimized index. In this case, you can use index (indexname) in the SELECT statement to force an INDEX or use ignore index (indexname) to force MYSQL to IGNORE the INDEX.
Key_len
The length of the index used. The shorter the length, the better.
Ref
It indicates which column of the index is used. If possible, it is a constant.
Rows
MYSQL considers that the number of rows that must be checked to return the requested data
Extra
Additional information about how MYSQL parses the query. We will discuss it in table 4.3, but here we can see that the bad examples are Using temporary and Using filesort, which means MYSQL cannot use indexes at all, and the result is that the retrieval will be slow.
Meaning of the description returned by the extra column
Distinct
Once MYSQL finds the row that matches the row, it does not search any more.
Not exists
MYSQL optimizes left join. Once it finds a row that matches the left join standard,
No more search
Range checked for each
Record (index map :#)
No ideal index is found. Therefore, for each row combination in the preceding table, MYSQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using indexes.
Using filesort
When you see this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.
Using index
The column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are part of the same index.
Using temporary
When you see this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.
Where used
The WHERE clause is used to limit which rows match the next table or are returned to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this may occur or the query is faulty.
Explanations of different connection types (sorted by efficiency order)
System
The table has only one row: system table. This is a special case of the const connection type.
Const
The maximum value of a record in a table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because MYSQL first reads this value and treats it as a constant.
Eq_ref
During connection, MYSQL reads a record from the Union of each record in the previous table during query, it is used when you query all data that uses the index as the primary key or unique key.
Ref
This connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index-the fewer the better
Range
This connection type uses indexes to return rows in a range, such as> or <查找东西时发生的情况
Index
This connection type performs a full scan of each record in the preceding table (better than ALL, because the index is generally smaller than the table data)
ALL
This connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.
