MySQL performance optimization----SQL statement optimization, index optimization, database structure optimization, System configuration optimization, server hardware optimization

Source: Internet
Author: User
Tags create index

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.