Mysql tuning three steps (slow query and explain profile)

Mysql tuning three steps (slow query and explain profile) often encounter some SQL problems in performance tests. In fact, the most problems encountered in performance tests over the past few years are databases, either high I/O or high cpu, Data Optimization plays an important role in performance testing, below I will introduce some of the three tools that are frequently used in the performance tuning process of msyql: 1. Slow query (analyze the problematic SQL statements) 2. Explain (shows how mysql uses indexes to process select statements and connect tables. It can help to select better indexes and write more optimized query statements. 3. Profile (how long will the SQL statement be executed? We can see the CPU/Memory usage and Systemlock during execution, how long does Table lock take .) first, let's talk about mysql slow query 1. Enable the configuration of Linux: In the mysql configuration file my. add log-slow-queries =/var/lib/mysql/slowquery in cnf. log (specify the location where the log file is stored, which can be null, the system will give a default file host_name-slow.log) long_query_time = 2 (record time exceeded, default is 10 s) log-queries-not-using-indexes, all queries without indexes will also be recorded.) Windows: In my. add the following statement to [mysqld] of ini: log-slow-queries = E: \ web \ mysql \ log \ mysqlslowquery. log long_query_time = 2 (other parameters are listed above) 2. View Details in 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 stmts that includethis string

 

For example: s, it is the order, which indicates that the writing is not detailed enough. I used it, including reading the code, mainly including c, t, l, r, ac, at, al, ar is 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.logmysqldumpslow -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, explain usage: Execute explain select * FROM res_user ORDER BYmodifiedtime LIMIT to get the following results: show the result analysis: table | type | possible_keys | key | key_len | ref | rows | explanation of the Extra EXPLAIN column: The table displays the data of this row about which table type is an important column, shows the connection type. The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL possible_keys. The indexes that may be applied to this table are displayed. If it is null, there is no possible index. You can select an index suitable for the statement key from the WHERE statement for the related domain. 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 length used by INDEX key_len. Without compromising accuracy, the shorter the length, the better. ref indicates which column of the index is used. If possible, it is a constant that rows MYSQL considers to be required to check the number of rows used to return request data Extra 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, the result is that the search will be slow. The description returned by the extra column indicates the meaning of Distinct. Once MYSQL finds the row that matches with the row, it no longer searches for Not exists MYSQL to optimize left join, once it finds a row that matches the left join standard, it no longer searches for Range checked for each Record (index map: #) and does not find the ideal index, 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. When you see this in Using filesort, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts data in the Using index column of all rows based on the connection type and the row pointer of all rows that store the sort key value and match the condition, instead of actually reading the information in the index., this occurs when all the request columns of the table are the same index, and Using temporary sees 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 performed on different column sets, instead of using the Where clause on group by, WHERE used uses the Where clause to restrict which rows match the next table or which rows 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 will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order) the system table has only one row: system table. This is a special case of the const connection type. The maximum value of a record in the const 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 to treat eq_ref in the connection. when MYSQL queries, from the previous table, the Union of each record reads a record from the table, it uses ref when the query uses the index as the primary key or unique key. This connection type is only used when the query uses a key that is not the only or primary key or a part of these types (for example, occurs with 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 smaller the better the range. This connection type uses the index to return rows in a range, for example, if you use> or <when something is found, the index 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) the ALL connection type performs a full scan for each of the preceding records. This is generally worse. We should try to avoid using profile first.

Mysql> SELECT @ profiling; + ------------- + | @ profiling | + ------------- + | 0 | + --------------- + 1 row in set (0.00 sec) to check whether profile is enabled, if the profilng value is 0, you can use mysql> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @ profiling; + ------------- + | @ profiling | + ------------- + | 1 | + ------------- + 1 row in set (0.00 sec)

 

. After profiling is enabled, we execute a query statement, for example:

SELECT * FROM res_user order by modifiedtimeLIMIT 0,1000 mysql> show profiles; + ---------- + ------------ + response + | Query_ID | Duration | Query | + ---------- + Duration + | 1 | 0.00012200 | SELECT @ profiling | 2 | 1.54582000 | SELECT res_id FROM res_user order by modifiedtime LIMIT 0.00 | + ---------- + ------------ + ----------------------------------------------------------- + 2 rows in set (sec) note: Query_ID indicates the execution of the query statement mysql> show profile for query 2; + running + ---------- + | Status | Duration | + -------------------------------- + ---------- + | starting | 0.000013 | checking query cache for query | 0.000035 | Opening tables | 0.000009 | System lock | 0.000002 | Table lock | 0.000015 | init | 0.000011 | optimizing | 0.000003 | statistics | 0.000006 | preparing | 0.000006 | executing | 0.000001 | Sorting result | 1.545565 | Sending data | 0.000038 | end | 0.000003 | query end | 0.000003 | freeing items | 0.000069 | storing result in query cache | 0.000004 | logging slow query | 0.000001 | logging slow query | 0.000033 | cleaning up | 0.000003 | + -------------------------------- + ---------- + 19 rows in set (0.00 sec)

 

Conclusion: The Execution Process and execution time of the query statement are displayed. The total execution time is about 1.545 s. At this time, we will execute it again.

Mysql> SELECT res_id FROM res_user ORDERBY modifiedtime LIMIT 1000305; + --------- + | res_id | + --------- + | 1000322 | 1000323 | 0.00 | + --------- + 3 rows in set (sec) mysql> show profiles; + ---------- + ------------ + response + | Query_ID | Duration | Query | + ---------- + Duration + | 1 | 0.00012200 | SELECT @ profiling | 2 | 1.54582000 | SELECT res_id FROM res_userORDER BY modifiedtime LIMIT 0.00006500 | 3 | 0.00 | SELECT res_id FROMres_user order by modifiedtime LIMIT | + ---------- + ------------ + rows + 3 rows in set (sec) mysql> show profile for query 3; + running + ---------- + | Status | Duration | + -------------------------------- + ---------- + | starting | 0.000013 | checking query cache for query | 0.000005 | checking privileges on cached | 0.000003 | sending cached result to clien | 0.000040 | logging slow query | 0.000002 | cleaning up | 0.000002 | + -------------------------------- + ---------- + 6 rows in set (0.00 sec) (note the place marked in red)

 

Conclusion: The second query generated a cache because of the previous query. Therefore, this query directly reads data from the cache instead of reading data from the database file, the result query time is much faster than the first time (the first query takes less than 1.5 seconds and the current query takes less than 5 milliseconds ).