SQL statement optimization steps
Preface
This article mainly shares with you the general steps of SQL statement Optimization for your reference and learning. I will not talk much about it below. Let's take a look at the detailed introduction.
1. Use the show status command to learn the frequency of running various SQL statements.
After the mysql client is successfully connectedshow [session|global] status The command can provide server status information or be used on the operating system.mysqladmin extend-statusCommand to obtain these messages.
show statusYou can add the option session (default) or global in the middle of the 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 _ % ';
We usually care about the following statistical parameters:
- Com_select: the number of select operations performed. Only 1 is added for one query.
- Com_insert: the number of insert operations. insert operations in batches are accumulated only once.
- Com_update: the number of times the update operation is performed.
- Com_delete: the number of delete operations.
The preceding parameters are used to accumulate table operations for all storage engines. The following parameters are only for innodb, and the accumulative algorithms are slightly different:
- Innodb_rows_read: number of rows returned by the select statement.
- Innodb_rows_inserted: number of rows inserted by the insert operation.
- Innodb_rows_updated: number of rows updated by the update operation.
- Innodb_rows_deleted: number of rows deleted by the delete operation.
With the above parameters, you can easily understand whether the current database application is dominated by insert and update, query operations, and the approximate execution ratio of various types of SQL statements. The Count of update operations is the count of the number of executions. Both the commit and rollback operations are accumulated.
For transactional applicationsCom_commitAndCom_rollback You can understand the transaction commit and rollback situations. For databases with frequent rollback operations, it may mean that there is a problem in application writing.
In addition, the following parameters help you understand the basic information of the database:
- Connections: number of attempts to connect to the mysql server.
- Uptime: the server's working time.
- Slow_queries: the number of slow queries.
Ii. Define SQL statements with low execution efficiency
1. Locate SQL statements with low execution efficiency using the slow query log, and use--log-slow-queries[=file_name]Mysqld writes a log file containing all SQL statements whose execution time exceeds long_query_time.
2. the slow query log is recorded only after the query is completed. Therefore, when the application reports an execution efficiency error, the slow query log cannot be located, you can use the show processlist command to view the current mysql thread, including the thread status and whether to lock the table. You can view the SQL Execution status in real time, optimize some lock table operations.
Iii. explain the execution plan of inefficient SQL statements
Test Database address: https://downloads.mysql.com/docs/sakila-db.zip (local download)
To count the total amount paid by an email for copying a rental movie, you need to associate the customer table with the payment table payment, and perform the sum operation on the payment amount field, the 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='JANE.BENNETT@sakilacustomer.org'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: ALLpossible_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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 26 filtered: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)
- Select_type: select type. common values include:
Simple: simple table, and does 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 the subquery.
- Table: The table of the output result set.
- Type: indicates the method in which mysql finds the required rows in the table, or the access type. The performance of common types ranges from all, index, range, ref, eq_ref, and const, system, null:
1.type=ALL, Full table scan, mysql traverses the full table to find matching rows:
mysql> explain select * from film where rating > 9 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 33.33 Extra: Using where1 row in set, 1 warning (0.01 sec)
2.type=index, Full index scan, mysql traverses the entire index to query matched rows
mysql> explain select title form film\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 1000 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)
3.type=range, Index range scanning, common in <, <=,>,> =, between, and other operations:
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: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 1350 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.07 sec)
4.type=refUse a non-unique index scan or a unique index prefix scan to return record rows that match 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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 23 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.01 sec)
Indexidx_fk_customer_idNon-unique index. The query condition is equivalent.customer_id = 350Therefore, the index scan type is ref. Ref is often used 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: ALLpossible_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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.b.customer_id rows: 26 filtered: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)
5.type=eq_ref, Similar to ref, the difference is that the unique index is used when the index is used. For the key value of each index, only one record matches in the Table. Simply put, it is used in multi-table join.primary key Orunique index As the join 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: ALLpossible_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_refpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.b.film_id rows: 1 filtered: 100.00 Extra: Using where2 rows in set, 1 warning (0.03 sec)
6.type=const/systemA single table can have at most one matching row, which is very fast to query. Therefore, the values of other columns in this matching row can be processed by the optimizer as constants in the current query. For exampleprimary keyOr a unique index.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: constpossible_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)
7.type=nullMysql can get the result directly without accessing tables or indexes:
mysql> explain select 1 from dual where 1 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used1 row in set, 1 warning (0.00 sec)
Type has other values, suchref_or_null (Similar to ref, the difference is that the condition contains null queries), index_merge (index merge optimization), and unique_subquery (in is followed by a subquery that queries the primary key field) index_subquery (similar to unique_subquery, the difference is that in is followed by subqueries that query non-unique index fields.
- Possible_keys: indicates the index that may be used during query.
- Key: actually used Index
- Key-len: the length of the index field.
- Rows: number of rows scanned
- Extra: Description and description of execution, including additional information that is not suitable for displaying in other columns but is important to the execution plan.
Show warnings command
Execute explain and then executeshow warnings, You can see which SQL statements are rewritten by the optimizer before the SQL statement is 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 = 'JANE.BENNETT@sakilacustomer.org'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: ALLpossible_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: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 26 filtered: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)MySQL [sakila]> show warnings;+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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` = 'JANE.BENNETT@sakilacustomer.org')) |+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
From the message Field of warning, we can see that the optimizer automatically removes the 1 = 1 constant standing condition, that is, the optimizer automatically removes the constant standing condition when rewriting SQL.
The explain command also supports partitions.
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: 0MySQL [sakila]> explain select * from customer_part where customer_id=130\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer_part partitions: p2 type: constpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warnings (0.00 sec)
The partition accessed by SQL is p2.
4. Analyze SQL Performance through performance_schema
The earlier version of mysql can use profiles to analyze SQL Performance. I use version 5.7.18 and do not allow profiles anymore. We recommend that you use
Performance_schema analysis SQL.
5. Use the trace analysis optimizer to select an execution plan.
Mysql5.6 provides A trace of SQL to further understand why optimizer chooses Execution Plan A instead of execution plan B, which helps us better understand the behavior of the optimizer.
Usage: first, enable trace, set the format to json, and set the maximum memory size that can be used by trace. This avoids incomplete display because the default memory size is too small during 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)
Next, execute the SQL statement that you want to execute trace. For example, if you want to learn about copying a movie whose inventory number inventory_id is 4466 in rental table marshal_date is 4:00:00 ~ Rental records between 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: 01 row in set (0.00 sec)
6. Identify the problem and take corresponding optimization measures
After the above steps, you can confirm the cause of the problem. At this time, you can take appropriate measures based on the situation to optimize the execution efficiency.
Summary
The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.