first, SQL statement optimization1-1.mysql Slow Log
1). Slow log on mode and storage format
How do I find the problematic SQL? Use MySQL slow logs to monitor efficiency issues with SQL
Pre-preparation
Mysql> Show variables like '%log_queri% '; +-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF |+-------------------------------+-------+# record queries that do not use an index mysql> set global log_queries_not_using_indexes= On Query OK, 0 rows affected (0.03 sec)
Mysql> Show variables like ' slow_query_log '; +----------------+-------+| Variable_name | Value |+----------------+-------+| Slow_query_log | OFF |+----------------+-------+# turn on slow query log mysql> set global slow_query_log=on; Query OK, 0 rows affected (0.32 sec)
# log queries larger than 10 milliseconds into the logs mysql> show variables like ' long_query_time '; +-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+
mysql> use sakila;database changedmysql> show tables;23 rows in Set (0.00 sec) # View slow log in what text mysql> show variables Like ' slow_query_log_file% '; +---------------------+--------------------------------+| variable_name | Value |+---------------------+--------------------------------+| Slow_query_log_file | /var/lib/mysql/ubuntu-slow.log |+---------------------+--------------------------------+mysql> select * FROM Store limit 10;+----------+------------------+------------+---------------------+| store_id | manager_staff_id | address_id | Last_update |+----------+------------------+------------+---------------------+| 1 | 1 | 1 | 2006-02-15 04:57:12 | | 2 | 2 | 2 | 2006-02-15 04:57:12 |+----------+------------------+------------+---------------------+<pre name= "code" class= " SQL ">[email protected]:~$ sudo tail-50/var/lib/mysql/ubuntu-slow.log/usr/sbin/mysqld, version:5.6.30-0ubuntu0.15.10.1 ((Ubuntu)). Started with:tcp port:3306 Unix socket:/var/run/mysqld/mysqld.socktime Id Command argument/usr/sbin/ Mysqld, version:5.6.30-0ubuntu0.15.10.1 (Ubuntu). Started with:tcp port:3306 Unix socket:/var/run/mysqld/mysqld.socktime Id Command argument# time:16 0908 0:47:01# [email protected]: root[root] @ localhost [] id:2# query_time:0.019114 lock_time:0.000065 Row S_sent:2 Rows_examined:2use Sakila; SET Timestamp=1473320821;select * from store limit 10;
Storage format for slow-scan logs
2). Mysqldumpslow of the slow-scan log analysis tool
[Email protected]:~$ mysqldumpslow--help
[Email protected]:~$ sudo mysqldumpslow-t 3/var/lib/mysql/ubuntu-slow.log | More
3). Pt-query-digest of the slow-scan log analysis tool
4). Query and analyze the SQL execution plan through explain
Mysql> explain select Customer_id,first_name,last_name from customer;+----+-------------+----------+------+----- ----------+------+---------+------+------+-------+| ID | Select_type | Table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+------+-------+| 1 | Simple | customer | All | NULL | NULL | NULL | NULL | 599 | NULL |+----+-------------+----------+------+---------------+------+---------+------+------+-------+1 row in Set (0.94 sec)
5). Optimization of Count () and Max ()
Query last payment time-optimize max () function
You can see that there are more than 10,000 rows of returned request data, not very good, optimized as follows
Mysql> CREATE index Idx_paydate on payment (payment_date);mysql> explain select Max (payment_date) from payment \g*** 1. Row *************************** id:1 select_type:simple table:null Type:NULLpossible_keys:NULL key:null key_len:null ref:null rows:null extra:select tables optimized away1 row in set (0.03 SE C
Mysql> CREATE TABLE t (id int); Query OK, 0 rows affected (0.62 sec) mysql> insert into T values (1), (2), (null); Query OK, 3 rows affected (0.20 sec) records:3 duplicates:0 warnings:0<pre name= "code" class= "SQL" >mysql& Gt Select COUNT (*), COUNT (ID), COUNT (id= ' 2 '), Count (id= ' 2 ' or null) from t;+----------+-----------+---------------+------ -----------------+| COUNT (*) | Count (ID) | Count (id= ' 2 ') | Count (id= ' 2 ' or null) |+----------+-----------+---------------+-----------------------+| 3 | 2 | 2 | 1 |+----------+-----------+---------------+-----------------------+1 row in Set (0.00 sec)
Mysql> Select count (release_year= ' 2006 ' or null) as ' 2006 ', Count (release_year= ' or null) as ' from film;+--- ---+------+| 2006 | |+------+------+| 540 | |+------+------+
6). Optimization of sub-queries
mysql> CREATE table T1 (tid int); Query OK, 0 rows affected (0.21 sec) mysql> insert into T1 values (1), (1); Query OK, 1 row affected (0.11 sec)--add distinct to mysql> select T.id from t join T1 on t.id = t1.tid;+------+| ID |+------+| 1 | | 1 |+------+2 rows in Set (0.00 sec) mysql> select * from T where t.id in (select T1.tid from t1); +------+| ID |+------+| 1 |+------+1 row in Set (0.25 sec)
7). Group BY optimization
Mysql> Explain select Actor.first_name, Actor.last_name, COUNT (*) from Sakila.film_actor INNER join Sakila.actor USING (actor_id) GROUP by film_actor.actor_id \g*************************** 1. Row *************************** id:1 select_type:simple table:actor Type:allpossible_keys: PRIMARY key:null key_len:null ref:null rows:200 extra:using temporary ; Using filesort*************************** 2. Row *************************** id:1 select_type:simple table:film_actor type:refpossible_ keys:primary,idx_fk_film_id key:primary key_len:2 ref:sakila.actor.actor_id rows:13 Extra:using index2 rows in Set (0.01 sec)
The above SQL query can see that the use of temporary tables and file sorting, so bad, optimized as follows
Mysql> Explain select Actor.first_name, Actor.last_name, c.cnt from Sakila.actor inner join (select actor_id, cou NT (*) as CNT from Sakila.film_actor Group by actor_id) as C USING (actor_id) \g*************************** 1. Row *************************** id:1 select_type:primary table:actor Type:allpossible_keys: PRIMARY key:null key_len:null ref:null rows:200 extra:null********************* 2. Row *************************** id:1 select_type:primary table: <derived2> type:refposs Ible_keys: <auto_key0> key: <auto_key0> key_len:2 ref:sakila.actor.actor_id R Ows:27 extra:null*************************** 3. Row *************************** id:2 select_type:derived table:film_actor type:indexpossible _keys:primary,idx_fk_film_id key:primary Key_len:4 Ref:null rows:5462 extra:using index3 rows in Set (0.00 sec)
8). Optimization of limit Queries
Mysql> explain select film_id,description from Sakila.film order by title limit 50,5;+----+-------------+-------+---- --+---------------+------+---------+------+------+----------------+| ID | Select_type | Table | Type | Possible_keys | Key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| 1 | Simple | film | All | NULL | NULL | NULL | NULL | 1000 | Using filesort |+----+-------------+-------+------+---------------+------+---------+------+------+-------------- --+
Optimization Step 1:use an indexed column or primary key for an order by Operation
Mysql> explain select film_id,description from Sakila.film order by film_id limit 50,5;+----+-------------+-------+-- -----+---------------+---------+---------+------+------+-------+| ID | Select_type | Table | Type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+| 1 | Simple | film | index | NULL | PRIMARY | 2 | NULL | 55 | NULL |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+1 row in Set (0.00 sec)
If the above is 500,5 then rows is 505, and if the number is too many, it will also affect performance, optimized as follows:
Optimization Step 2: record the primary key returned once and use primary key filtering at the next query
Mysql> explain select Film_id,description from Sakila.film where film_id>55 and film_id<=60 order by film_id Limi T 1,5;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| ID | Select_type | Table | Type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | Simple | film | range | PRIMARY | PRIMARY | 2 | NULL | 5 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+------------- +
The primary key above must be ordered.
MySQL performance optimization----SQL statement optimization, index optimization, database structure optimization, System configuration optimization, server hardware optimization