First.
Optimization Overview
MySQL database is a common two bottlenecks are the bottleneck of CPU and I/O. When the CPU is saturated, it generally occurs when data is loaded into memory or read from disk. The disk I/O bottleneck occurs when the loaded data is much larger than the memory capacity. If the application is distributed on the network, then the query bottleneck will appear on the network when the query volume is quite large. We can use mpstat, iostat, sar and vmstat to view the performance status of the system.
In addition to the performance bottleneck of the server hardware, for the MySQL system itself, we can use tools to optimize the performance of the database. There are usually three types: using indexes, using EXPLAIN to analyze queries, and adjusting MySQL's internal configuration.
Second, query and index optimization analysis
When optimizing MySQL, it is usually necessary to analyze the database. Common analysis methods include slow query log, EXPLAIN analysis query, profiling analysis, and show command to query system status and system variables. By locating and analyzing performance bottlenecks, the database can be better optimized. The performance of the system.
show command
We can view the MySQL status and variables through the show command to find the bottleneck of the system:
Mysql> show status-display status information (extended show status like ‘XXX’)
Mysql> show variables-show system variables (extended show variables like ‘XXX’) show variables like "slow%"
Mysql> show innodb status-displays the status of the InnoDB storage engine
Mysql> show processlist-view the current SQL execution, including the execution status, whether to lock the table, etc.
Shell> mysqladmin variables -u username -p password-display system variables
Shell> mysqladmin extended-status -u username -p password-display status information
Slow query log
Slow query can record statements with slow execution time and statements that do not use indexes
1. MySQL database has several configuration options to help us capture inefficient SQL statements in time
1, slow_query_log This parameter is set to ON, you can capture SQL statements whose execution time exceeds a certain value.
2. long_query_time When the execution time of the SQL statement exceeds this value, it will be recorded in the log. It is recommended to set it to 1 or shorter (in seconds).
3. Slow_query_log_file records the file name of the log.
4, log_queries_not_using_indexes This parameter is set to ON, you can capture all unused index SQL statements, although this SQL statement may be executed quickly.
explain analysis query
Using the EXPLAIN keyword can simulate the optimizer executing SQL query statements, thereby knowing how MySQL processes your SQL statements. This can help you analyze the performance bottleneck of your query or table structure. You can get through the explain command:
– Reading order of the table
– Operation type of data reading operation
– Which indexes can be used
– Which indexes are actually used
– References between tables
– How many rows in each table are queried by the optimizer
SQL
mysql> explain select * from user;
+----+-------------+-------+------------+------+-- -------------+------+---------+------+------+----- -----+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |
+----+-------------+-------+------------+------+-- -------------+------+---------+------+------+----- -----+-------+
1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 85 | 100.00 |
+----+-------------+-------+------------+------+-- -------------+------+---------+------+------+----- -----+-------+
1 row in set, 1 warning (0.01 sec)
EXPLAIN field:
id: The query sequence number is the order in which the sql statement is executed
select_type
select type, it has the following values
2.1 simple It means simple selection, no union and subquery
2.2 The outermost select of the primary, in the statement with the subquery, the outermost select query is the primary, as shown in the above figure
2.3 The second union union statement or the latter one. Now execute a statement, explain
Table: Shows which table this row of data refers to
possible_keys: Shows the indexes that may be applied in this table. If it is empty, there is no possible index. You can choose a suitable statement from the WHERE statement for the relevant domain
key: the actual index used. If NULL, no index is used. MYSQL rarely selects indexes that are under-optimized. In this case, you can use USE INDEX(index) in the SELECT statement to force the use of an index or IGNORE INDEX(index) to force the index to be ignored.
key_len: the length of the index used. Without losing accuracy, the shorter the better
ref: shows which column of the index is used, if possible, it is a constant
rows: the number of rows that MySQL thinks must be retrieved to return the requested data
type: This is one of the most important fields and shows what type the query uses. The connection types from best to worst are system, const, eq_reg, ref, range, index, and ALL
Extra: Additional information about how MYSQL parses queries, mainly the following
Extra: parameter description
using index: Only the index is used to avoid access to the table.
using where: use where to consider the data. Not all where clauses need to show using where. Such as accessing the index by =.
using tmporary: Use temporary table
using filesort: additional sorting is used. (When order by v1 is used, and no index is used, additional sorting is used)
range checked for eache record(index map:N): There is no good index.
type: parameter description
system, const: You can convert the query variable to a constant. For example, id=1; id is the primary key or unique key.
eq_ref: access the index and return the data of a single row. (usually appears when joining, the index used by the query is the primary key or the only key)
ref: Access the index and return data of a certain value. (Can return multiple rows) Usually occurs when =
range: This connection type uses an index to return rows in a range, such as using> or <to find something, and what happens when an index is built on this field (Note: not necessarily better than index)
index: Perform a full table scan in the order of the index. The advantage is that it does not need to be sorted.
ALL: full table scan should be avoided
profiling analysis query
Through slow log query, you can know which SQL statements are executed inefficiently. Through explain, we can know the specific execution of SQL statements, index usage, etc., and can also view the execution status in combination with the show command.
If you feel that the explain information is not detailed enough, you can use the profiling command to get more accurate information about SQL execution consuming system resources.
Profiling is turned off by default. You can view select @@profiling by the following statement;
Open the function: mysql>set profiling=1; execute the sql statement to be tested:
After opening the sql statement, you can use the show profiles; command to view the execution time
SQL
mysql> show profiles;
+----------+------------+------------------------- --+
| Query_ID | Duration | Query |
+----------+------------+------------------------- --+
| 1 | 0.00417000 | select * from user |
| 2 | 0.00214700 | select count(*) from user |
+----------+------------+------------------------- --+
2 rows in set, 1 warning (0.00 sec)
mysql> show profiles\G; You can get the time and ID of the executed SQL statement
mysql>show profile for query 1; Get the detailed information of the corresponding SQL statement execution
Bash
