1. Run the show status command to learn about SQL executionfrequency
After the success of the mysql client connection, through the show [session | global] status command can provide server status information, also can use mysqladmin on the operating system the extend – status command to get the messages. You can add session (default) or global in the middle of the show status command:
- Session (current connection)
- Global (since data was last started)
# Com_xxx indicates the number of times each XXX statement is executed.
mysql> show status like 'Com_%';Copy the codeWe are usually concerned with the following statistical parameters:
- Com_select: indicates the number of times that the select operation is performed. Only 1 is added to a query.
- Com_insert: indicates the number of insert operations. For batch insert operations, only one is added.
- Com_update: indicates the number of times the update operation is performed.
- Com_delete: indicates the number of delete operations.
These parameters are accumulated for all storage engine table operations. The following parameters are only for InnoDB, and the cumulative algorithm is slightly different:
- Innodb_rows_read: select the number of rows returned by the query.
- Innodb_rows_inserted: Number of rows inserted by an INSERT operation.
- Innodb_rows_updated: Number of rows updated by the update operation.
- Innodb_rows_deleted: indicates the number of rows deleted by the DELETE operation.
With these parameters, it is easy to know whether the current database application is mainly for insert updates or query operations, as well as the approximate percentage of execution of the various types of SQL. The count of the update operation is the count of the number of times it was performed, and both commit and rollback are accumulated. For transactional applications, Com_commit and Com_rollback can be used to learn about transaction commit and rollback. For databases with frequent rollback operations, this may indicate application writing problems. In addition, the following parameters help users understand the basic information about the database:
- Connections: Number of attempts to connect to the mysql server.
- Uptime: indicates the server working time.
- Slow_queries: indicates the number of slow queries.
2. Define SQL statements that execute inefficiently
1. Use slow query logs to locate SQL statements that are inefficient in execution. When you start mysqld with –log-slow-queries[=file_name], mysqld writes a log file containing all SQL statements whose execution time exceeds long_query_time seconds.
2. Slow query logs are recorded after the query is complete. If the application displays a problem with the execution efficiency, the slow query logs cannot locate the problem. You can run the show processList command to check the current threads in mysql, including the thread status and table lock. You can view the execution of SQL in real time and optimize some lock table operations.
Analyze the execution plan of inefficient SQL through Explain
Test database address: downloads.mysql.com/d…
To calculate the total amount paid by an email for renting movie copies, it is necessary to associate customer table with payment table and perform sum operation on the amount field. The corresponding execution plan is as follows:
mysql> explain select sum(amount) from customer a . payment b where a.customer_id= b.customer_id and a.email='[email protected]'\G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 10.00
Extra: Using where
* * * * * * * * * * * * * * * * * * * * * * * * * * * 2. row * * * * * * * * * * * * * * * * * * * * * * * * * * *
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
Copy the code-
Select_type: indicates the select type. Common values are as follows:
- Simple: simple table, and do not use table join or subquery
- Primary: primary query, that is, outer query
- Union: The second or subsequent query statement in union
- Subquery: The first select in a subquery
- Table: output result set table
- Type: indicates the way mysql finds rows in a table, or the access type. The common types in order of performance are: all, index, range, ref, eq_ref, const, system, null:
-
Mysql > select * from table where type=ALL;
mysql> explain select * from film where rating > 9 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 33.33 Extra: Using where 1 row in set, 1 warning (0.01 sec) Copy the code -
Type =index, index full scan, mysql traverses the entire index to find matching rows
mysql> explain select title form film\G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: film partitions: NULL type: index possible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 1000 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) Copy the code -
Type =range, index range scan, common in <, <=, >, >=, between, etc:
mysql> explain select * from payment where customer_id > = 300 and customer_id < = 350 \G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: payment partitions: NULL type: range possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 1350 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.07 sec) Copy the code -
Type =ref, using a non-unique index scan or a prefix scan with a unique index, returns a row that matches a single value, for example:
mysql> explain select * from payment where customer_id = 350 \G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: payment partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 23 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.01 sec) Copy the codeIndex idx_fk_customer_id is a non-unique index. The search condition is equivalent value customer_id = 350. Therefore, the scan index type is ref. Ref also often appears in join operations:
mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: b partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 filtered: 100.00 Extra: NULL * * * * * * * * * * * * * * * * * * * * * * * * * * * 2. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: a partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.b.customer_id rows: 26 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec) Copy the code -
Type =eq_ref; type=eq_ref; Use a primary key or unique index as an association condition.
mysql> explain select * from film a . film_text b where a.film_id = b.film_id \G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: b partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 100.00 Extra: NULL * * * * * * * * * * * * * * * * * * * * * * * * * * * 2. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: a partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.b.film_id rows: 1 filtered: 100.00 Extra: Using where 2 rows in set, 1 warning (0.03 sec) Copy the code -
Type =const/system. There is at most one matching row in a single table, which is very fast to look up, so the values of other columns in this matching row can be treated as constants by the optimizer in the current query, for example, based on the primary key or unique index.
mysql> create table test_const ( -> test_id int, -> test_context varchar(10), -> primary key (`test_id`), -> ); insert into test_const values(1,'hello'); explain select * from ( select * from test_const where test_id=1 ) a \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_const partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)Copy the code -
Mysql > select * from table where type=null;
mysql> explain select 1 from dual where 1 \G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used 1 row in set, 1 warning (0.00 sec) Copy the code
Type type also has other values, such as ref_OR_NULL (similar to ref, The difference is that the criteria include null queries), index_merge(index merge optimization), unique_subquery (in is followed by a subquery that queries primary key fields), index_subquery(similar to unique_subquery, SQL > select * from in;
- Possible_keys: possible index in query.
- Key: indicates the actual index
- Key-len: indicates the length used in the index field.
- “Rows” : indicates the number of scanned rows
- Extra: A description and description of the performance, containing additional information that is important to the execution plan but not suitable for display in other columns.
Show warnings command
Execute Explain followed by show Warnings to see what SQL overwrites the optimizer did before the SQL was actually executed:
MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = '[email protected]'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 10.00
Extra: Using where* * * * * * * * * * * * * * * * * * * * * * * * * * *2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set.1 warning (0.00 sec)
MySQL [sakila]> showwarnings; +-------+------+-------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -----------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -----------------------------+ | Note | 1003 |/* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = '[email protected]')) | +-------+------+-------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -----------------------------+1 row in set (0.00 sec)
Copy the codeAs you can see from the warning message field, the optimizer automatically removes the condition that 1=1 is constant. That is, the optimizer automatically removes the condition when rewriting the SQL.
The Explain command also has support for partitioning.
MySQL [sakila]> CREATE TABLE `customer_part` (
-> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-> `store_id` tinyint(3) unsigned NOT NULL- >`first_name` varchar(45) NOT NULL- >`last_name` varchar(45) NOT NULL- >`email` varchar(50) DEFAULT NULL- >`address_id` smallint(5) unsigned NOT NULL- >`active` tinyint(1) NOT NULL DEFAULT '1'- >`create_date` datetime NOT NULL- >`last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`customer_id`) - > - >)partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)
MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0
MySQL [sakila]> explain select * from customer_part where customer_id=130\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer_part
partitions: p2
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set.1 warnings (0.00 sec)
Copy the codeYou can see that the partition accessed by SQL is P2.
Performance_schema Is used to analyze SQL performance
In older versions of mysql, profiles can be used to analyze SQL performance. I used version 5.7.18 and profiles are no longer allowed. Performance_schema is recommended for SQL analysis.
Analyze how the optimizer selects an execution plan through trace.
Mysql5.6 provides A trace of SQL to further understand why the optimizer chooses plan A over plan B, helping us understand optimizer behavior.
Usage: First open trace, set the format to JSON, and set the maximum memory size that trace can use to avoid incomplete display because the default memory is too small in the process of parsing.
MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)
MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)
Copy the codeNext, execute the SQL statement that you want to trace, For example, to know the rental record of the movie copy with inventory number inventory_id 4466 in the rental table, rental_date is 2005-05-25 4:00:00 ~ 5:00:00:
mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
39 | | + -- -- -- -- -- -- -- -- -- -- - +
1 row in set (0.06 sec)
MySQL [sakila]> select * from information_schema.optimizer_trace\G
* * * * * * * * * * * * * * * * * * * * * * * * * * *1. row ***************************
QUERY: select * from infomation_schema.optimizer_trace
TRACE: {
"steps": [
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
Copy the codeSix, determine the problem and take the corresponding optimization measures
After the above steps, you can basically confirm the cause of the problem. At this time, appropriate measures can be taken according to the situation to improve the efficiency of execution.