mysql資料庫SQL最佳化

1.瞭解sql的執行頻率
show global status like 'Com_%';
show global status like 'Innodb_%';
show global status like 'Connections';
show global status like 'Uptime';
show global status like 'Slow_queries';
2.定位低效的sql語句
慢查詢
show processlist；
3.透過explain分析低效sql執行計劃
mysql索引中包含null，oracle索引不包含null
（1）select_type:
        simple：簡單表，不使用表連線或者子查詢
        primary：主查詢，即外層的查詢
        union：union連線中的第二個或者後面的查詢語句
        subquery：子查詢中的第一個select
type：
        all：
        index：索引全掃描
        range：索引範圍掃描
        ref：非唯一索引等值查詢
        eq_ref:唯一索引等值查詢
extra:
       using index :透過索引就能拿到資料
       using where：表示最佳化器需要透過索引返回表查詢資料
       using filesort: 做了排序操作，（透過索引排序不算在內）
測試樣例wget /> mysql資料版本
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.1.73 |
+---------------+--------+

explain select * from film where rating>9;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL |  949 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
explain select title from film ;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_title | 767     | NULL |  949 | Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
explain select * from payment where customer_id>=300 and customer_id<=350;
+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys      | key                | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-------------+
|  1 | SIMPLE      | payment | range | idx_fk_customer_id | idx_fk_customer_id | 2       | NULL | 1349 | Using where |
+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-------------+
explain select * from payment where customer_id=350;
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows | Extra |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+
|  1 | SIMPLE      | payment | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   23 |       |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+
explain select * from film a,film_text b where a.film_id=b.film_id;
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | a     | ALL    | PRIMARY       | NULL    | NULL    | NULL             |  949 |             |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.a.film_id |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
explain select * from (select * from customer where email='AARON.SELBY@sakilacustomer.org') a;
+----+-------------+------------+--------+---------------+----------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key      | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+----------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL     | NULL    | NULL |    1 |       |
|  2 | DERIVED     | customer   | const  | uk_email      | uk_email | 153     |      |    1 |       |
+----+-------------+------------+--------+---------------+----------+---------+------+------+-------+
type：（還有一些其他值）
     ref_or_null：與ref類似，區別在於條件中包含對null的查詢
     index_merge:索引合併最佳化
     unique_subquery:in後面是一個查詢主鍵欄位的子查詢
     index_subquery:in後面是查詢非唯一索引欄位的子查詢

（2）.explain extended詳解

explain select sum(amount) from customer c,payment b where 1=1 and c.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org';
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | c     | const | PRIMARY,uk_email   | uk_email           | 153     | const |    1 | Using index |
|  1 | SIMPLE      | b     | ref   | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   28 |             |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
 explain extended select sum(amount) from customer c,payment b where 1=1 and c.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org';
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | c     | const | PRIMARY,uk_email   | uk_email           | 153     | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | b     | ref   | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   28 |   100.00 |             |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `c` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = '77') and ('JANE.BENNETT@sakilacustomer.org' = 'JANE.BENNETT@sakilacustomer.org'))
     
（3）explain partitions 顯示分割槽使用資訊

4.透過show profile分析sql
select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
set profiling=1;
show profiles；
show profile for query 8；
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000062 |
| Opening tables     | 0.000044 |
| System lock        | 0.000004 |
| Table lock         | 0.000007 |
| init               | 0.000012 |
| optimizing         | 0.000007 |
| statistics         | 0.000013 |
| preparing          | 0.000007 |
| executing          | 0.000004 |
| Sending data       | 0.003732 |
| end                | 0.000079 |
| query end          | 0.000007 |
| freeing items      | 0.000025 |
| logging slow query | 0.000003 |
| cleaning up        | 0.000002 |
+--------------------+----------+
15 rows in set (0.00 sec)
注：Sending data狀態表示MYSQL執行緒開始訪問資料並把結果返回給客戶端（所以包含io時間）；
select state,sum(duration) as total_r,
             ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION)
                                      FROM INFORMATION_SCHEMA.PROFILING
                                      WHERE QUERY_ID=@query_id
                                      ),2) AS Pct_R,
             count(*) as calls,
             sum(duration)/count(*) as "r/call"
from information_schema.profiling
where query_id=@query_id
group by state
order by total_r desc;

show profile cpu for query 8;

MySQL5.6可以透過trace分析最佳化器如何選擇執行計劃
set optimizer_trace="enabled=on",END_MARKERS_IN_JSON=on;
set optimizer_trace_max_mem_size=1000000;
select * from table_name;
select * from information_schema.optimizer_trace\G