Mysql效能最佳化----SQL語句最佳化、索引最佳化、資料庫結構最佳化、系統配置最佳化、伺服器硬體最佳化

標籤：

一、SQL語句最佳化1-1.MySQL慢日誌

1).慢日誌開啟方式和儲存格式
  如何發現有問題的SQL? 使用Mysql慢日誌對有效率問題的SQL進行監控

前期準備

mysql> show variables like '%log_queri%';+-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF   |+-------------------------------+-------+# 記錄未使用索引的查詢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   |+----------------+-------+# 開啟慢查詢日誌mysql> set global slow_query_log=on;Query OK, 0 rows affected (0.32 sec)

# 把大於10毫秒的查詢記錄到日誌裡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)# 查看慢查日誌在什麼地文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: 160908  0:47:01# [email protected]: root[root] @ localhost []  Id:     2# Query_time: 0.019114  Lock_time: 0.000065 Rows_sent: 2  Rows_examined: 2use sakila;SET timestamp=1473320821;select * from store limit 10;

慢查日誌的儲存格式


2).慢查日誌分析工具之mysqldumpslow
[email protected]:~$ mysqldumpslow --help
[email protected]:~$ sudo mysqldumpslow -t 3 /var/lib/mysql/ubuntu-slow.log | more

3).慢查日誌分析工具之pt-query-digest

4).通過explain查詢和分析SQL的執行計畫

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).Count()和Max()的最佳化
  查詢最後支付時間 -- 最佳化max()函數

可以看到返回請求資料的行數有一萬多條，不是很好，最佳化如下

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 sec)

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> 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=‘2007‘ or null) as ‘2007‘ from film;+------+------+| 2006 | 2007 |+------+------+| 540 | 160 |+------+------+

6).子查詢的最佳化

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)-- 加distinct即可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的最佳化

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)

上面那個sql查詢可以看到，使用了暫存資料表和檔案排序，這樣不好，最佳化如下

mysql> explain select actor.first_name, actor.last_name, c.cnt  from sakila.actor      inner join (select actor_id, count(*) 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: refpossible_keys: <auto_key0>          key: <auto_key0>      key_len: 2          ref: sakila.actor.actor_id         rows: 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).Limit查詢的最佳化

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 |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

最佳化步驟1：使用有索引的列或主鍵進行order by操作

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)

如果上面為500，5則rows是505，如果數太多，也會影響效能，最佳化如下：

最佳化步驟2：記錄一次返回的主鍵，在下次查詢時使用主鍵過濾

mysql> explain select film_id,description from sakila.film where film_id>55 and film_id<=60 order by film_id limit 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 |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

上面主鍵一定要是順序排序的

Mysql效能最佳化----SQL語句最佳化、索引最佳化、資料庫結構最佳化、系統配置最佳化、伺服器硬體最佳化