SQL業務審核與優化，SQL業務審核優化

SQL業務審核與優化，SQL業務審核優化

審核

---

  什麼是業務審核

• 類似與code review
• 評審業務Schema和SQL設計
• 偏重關注性能
• 是業務優化的主要入口之一

          審核提前發現問題，進行優化           上線後通過監控或巡檢發現問題，進行優化   Schema設計審核

• 表和字段命名是否合規
• 字段類型，長度設計是否適當
• 表關聯關系是否合理
• 主鍵，更新時間保留字段等是否符合要求
• 約束，默認值等配置是否恰當
• 了解業務，表數據量，增長模式
• 數據訪問模式，均衡度
• 根據業務需求，表是否需要分區，是否有數據什麼周期

  SQL語句審核

• SQL語句的執行頻率
• 表上是否有合適的索引
• 單次執行的成本
• 執行模式，鎖情況分析
• 關注事務上下文

  什麼時候需要審核

• 業務開發階段，上線前
• 業務版本變更，線上更新前
• • 新表和SQL上線
  • SQL查詢條件變化
  • SQL查詢頻率變化
  • 業務邏輯導致現有表數據量規模變化

  業務發布流程

• SQL審核需要開發與應用運維支持
• 充分溝通，做好必要性說明和教育工作
• 指定業務發布流程，嵌入DBA審核環節
• 積累經驗，不斷完善評審方法

  慢查詢

---

  查詢優化，索引優化，庫表結構優化需要齊頭並進。   慢查詢兩個步驟分析：

• 確認應用程序是否向數據庫請求了大量超過需要的數據
• 確認mysql服務器層是否在處理大量超過需要的數據記錄

  是否向數據庫請求了不需要的數據 典型案例：

• 查詢不需要的記錄
• 多表關聯時返回全部列
• 總是取出全部列
• 重復查詢相同的數據

  mysql是否在掃描額外的記錄 在確定查詢只返回需要的數據後，接下來應該看看查詢為了返回結果是否掃描了過多的數據。   mysql查詢開銷的三個指標：

• 響應時間
• 掃描的行數
• 返回的行數

  這三個指標都會記錄到mysql的慢日志中，索引檢查慢日志記錄是找出掃描行數過多的查詢的好辦。   響應時間：執行時間和等待時間； 判斷一個響應時間是否是合理的值，可以使用"快速上限估計"。   掃描的行數和返回的行數 分析查詢時，查看該查詢掃描的行數是非常有幫助的。它一定程度上說明該查詢找到需要的數據的效率高不高。   如果發現查詢需要掃描大量的數據但只返回少數的行，優化方法：

• 使用索引覆蓋掃描，把所有需要用的列都放到索引中。
• 改變庫表結構。例如使用單獨的匯總表
• 重寫這個復雜的查詢，讓mysql優化器能夠以更優化的方式執行這個查詢。

  有的時候將大查詢分解為多個小查詢是有必要的。   查詢執行的基礎

---

  mysql查詢執行路徑  

 

      mysql客戶端/服務器通信協議 mysql客戶端和服務器之間的通信協議是"半雙工"。任何時候只能一方發；不能同時發送；   mysql連接時線程狀態  

mysql> show  full processlist;
+----+------+-----------+--------+---------+------+-------+------------------------+
| Id | User | Host      | db     | Command | Time | State | Info                   |
+----+------+-----------+--------+---------+------+-------+------------------------+
| 39 | root | localhost | sakila | Sleep   |    4 |       | NULL                   |
| 40 | root | localhost | sakila | Query   |    0 | NULL  | show  full processlist |
+----+------+-----------+--------+---------+------+-------+------------------------+
2 rows in set (0.00 sec)

 

  查詢優化器

---

  一條查詢可以有很多種執行方式，最後都返回相同的結果。 優化器的作用就是找到這其中最好的執行計劃。   mysql使用基於成本的優化器，它將嘗試預測一個查詢使用某種執行計劃時的成本，並選擇其中成本最小的一個。   通過查詢當前會話的last_query_cost的值來得知Mysql計算的當前查詢的成本。

mysql> select count(*) from film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+
1 row in set (0.00 sec)
 
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1040.599000 |
+-----------------+-------------+

 

這個結果表示mysql優化器認為大概需要做1040個數據頁的隨機查找才能完成上面的查詢。這是根據一系列的統計信息計算得來的：每個表或者索引的頁面個數，索引的基數(索引中不同值的數量)，索引和數據行的長度，索引分布情況。   優化器在評估成本的時候並不考慮任何層面的緩存，它假設讀取任何數據都需要一次磁盤I/O。   mysql優化器選錯執行計劃的原因：

• 統計信息不准確
• 執行計劃中的成本估算不等同於實際執行的成本。
• • 有的計劃雖然要讀取更多頁，但是這些頁在緩存中。
• mysql的最有可能和你想的最優不一樣。
• • 比如你希望執行時間盡可能的短，而mysql只是基於成本模型選擇的最優執行計劃。
• mysql從不考慮其他並發執行的查詢，這可能會影響到當前查詢速度。
• mysql不會考慮不受其控制的操作的成本。
• • 如執行存儲過程或者用戶自定義函數的成本

    優化策略：

• 靜態優化
• • 直接對解析樹進行分析，並完成優化。優化器通過一些簡單的代數變換將where條件轉換成另一種等價形式。靜態優化在第一次完成後一直有效。可以認為這是一種"編譯時優化"
• 動態優化
• • 動態優化和查詢的上下文有關。也和其他很多因素有關，例如where中的取值，索引中條目，等等。每次查詢的時候都重新評估，可以認為這是一種"運行時優化"

  mysql能夠處理的優化類型

• 重新定義關聯表的順序。
• 將外聯結轉成內連接
• 使用等價變化規則
• • 合並和減少一些比較，移除一些恆成立和一些恆不成立的判斷
  • 優化count(),min(),max()，min()就直接拿BTree樹最左端數據行
• 預估並轉換為常數表達式
• 覆蓋索引掃描
• 子查詢優化
• 提前終止查詢
• 等值傳播

  在查詢中添加hint,提示優化器，   優化器的局限性

---

  1 關聯子查詢 mysql的子查詢實現得非常糟糕；最糟糕的一類查詢是where條件中包含IN()的子查詢語句。 例如，我們希望找到sakila數據庫中，演員actor_id為1，參演過的所有影片信息。很自然的，我們會按照下面的方式  

mysql> select * from film where film_id in ( select film_id from film_actor where actor_id =1) \G;

 

我們一般認為，mysql會首先將子查詢的actor_id=1的所有film_id都找到，然後再去做外部查詢，如  

select * from film where film_id in （1,23,25,106,140）；

 

然而，mysql不是這樣做的。 mysql會將相關的外層表壓到子查詢中，它認為這樣可以更高效率地查找數據行。   當然我們可以使用連接替代子查詢重寫這個SQL，來優化；  

mysql> explain select * from film f  inner join film_actor  fa where f.film_id=fa.film_id and actor_id =1;
+----+-------------+-------+--------+------------------------+---------+---------+-------------------+------+-------+
| id | select_type | table | type   | possible_keys          | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+--------+------------------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | fa    | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | const             |   19 |       |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.fa.film_id |    1 |       |
+----+-------------+-------+--------+------------------------+---------+---------+-------------------+------+-------+
2 rows in set (0.00 sec)

 

如何用好關聯子查詢，很多時候，關聯子查詢也是一種非常合理，自然，甚至是性能最好的寫法。 where in(）肯定是不行的，但是 where exists（）有時是可以的；   2 union的限制   有時，mysql無法將限制條件從外層"下推"到內層，這使得原本能夠限制部分返回結果的條件無法應用到內層查詢的優化上。   如果希望union的各個子句能夠根據limit只取部分結果集，或者希望能夠先拍下再合並結果集的話，就需要在union的各個子句中分別使用這些子句。   如：  

（select first_name,last_name
  from sakila.actor
  order by last_name）
 union all
（select first_name,last_name
  from sakila.customer
  order by last_name）
 limit 20;

會將actor中200條記錄和customer中599條記錄放在一個臨時表中，然後在從臨時表中取出前20條；   而

（select first_name,last_name
  from sakila.actor
  order by last_name
  limit 20）
 union all
（select first_name,last_name
  from sakila.customer
  order by last_name
  limit 20）
 limit 20;

現在中間的臨時表中只會包含40條記錄。

  3  最大值和最小值優化 對於min()和max()查詢，mysql的優化做得並不好。  

mysql> explain select min(actor_id) from actor where first_name='PENELOPE';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

 

因為在first_name字段上沒有索引，因此mysql將會進行一次全表掃描。 如果mysql能夠進行主鍵掃描，那麼理論上，mysql讀到第一個滿足條件的記錄的時候，就是我們需要找的最小值了，因為主鍵時嚴格按照actor_id字段的大小順序排序的。但這僅僅是如果，mysql這時只會做全表掃描。   優化min()，使用limit重寫SQL：  

mysql> explain select actor_id from actor USE INDEX(PRIMARY) where first_name='PENELOPE' LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

看著實驗結果，似乎沒有使用 主鍵索引，不知道是什麼原因導致.歡迎交流。   4  在同一個表上查詢和更新 mysql不允許，對同一張表進行查詢和更新：

mysql> update tbl AS outer_tbl
          set cnt = (
               select count(*) from tbl AS inner_tbl
               where inner_tbl.type = outer_tbl.type
          );
error:you can't specify target table 'outer_tbl' for update in from clause

可以使用內連接來繞過這個限制。實際上，這執行了兩個查詢：一個是子查詢中的select語句，另一個是多表關聯update，只是關聯的表是一個臨時表。  

mysql> update tbl
          inner join (
               select type,count(*) AS cnt
               from tbl
               group by type
          )AS der using(type)
       set tbl.cnt = der.cnt;
 

  優化器的提示（hint）

---

  如果對優化器選擇的執行計劃不滿意，可以使用優化器提供的幾個提示（hint）來控制最終的執行計劃。  

• HIGH_PRIORITY,LOW_PRIORITY
• • 這個提示告訴mysql，當多個語句同時訪問某一個表的時候，哪些語句的優先級相對高些，哪些語句的優先級相對低些。
  • 只對使用表鎖的存儲引擎有效，不要在innodb或者其他有細粒度鎖機制和並發控制的引擎中使用。
• DELAYED
• • 這個提示對insert，replace有效。mysql會將使用該提示的語句立即返回給客戶端，並將插入的行數據放入到緩沖區，然後在表空間時批量將數據寫入。
  • 日志系統使用這樣的提示非常有效，或者是其他需要寫入大量數據但是客戶端卻不需要等待單條語句完成I/O的應用。這個用法有一些限制：並不是所有的存儲引擎都支持這樣的做法；並且該提示會導致函數LAST_INSERT_ID無法正常工作。
• USE INDEX,IGNORE INDEX ,FORCE INDEX

  慢查詢分析

---

  1 show status 了解各SQL的執行頻率   默認使用參數為，session；可以使用global；

mysql> show status like 'com%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Com_admin_commands        | 0     |
| Com_assign_to_keycache    | 0     |
| Com_alter_db              | 0     |
| Com_alter_procedure       | 0     |
| Com_alter_server          | 0     |
| Com_alter_table           | 0     |

com_xxx表示每個xxx語句執行的次數： com_select: 執行select操作的次數，一次查詢只累加一次； com_insert: 執行insert操作的次數，對於批量插入的insert操作，只累加一次； com_update: 執行update操作的次數 com_delete:  執行delete操作的次數   上面這些參數對於所有存儲引擎的表操作都會進行累計。下面幾個參數只是針對innodb存儲引擎，累加算法也略有不同。   innodb_rows_read: select查詢返回的行數 innodb_rows_inserted: 執行insert操作插入的行數 innodb_rows_updated: 執行update操作更新的行數 innodb_rows_deleted:  執行delete操作刪除的行數   通過以上參數，很容易了解當前數據庫的應用是以插入更新為主還是以查詢操作為主，大致的讀寫比例是多少；   可以通過com_commit 和 com_rollback 可以知道，事務回滾的比例； 如果比例過高則說明應用編寫存在問題；   connections:  試圖連接mysql服務器的次數 uptime:  服務器工作時間 slow_queries: 慢查詢的次數；   2 定位低效 SQL  

• 慢查詢日志，定位低效SQL；long_query_time，慢查詢的標准時間；
• 慢查詢是，查詢結束之後才記錄；因此他不是實時的；show processlist 查看mysql在進行的線程，查看線程的一些狀態，可以實時地查看SQL的執行情況；

  3 explain分析低效查詢SQL的執行計劃  

mysql> explain select b from t where a =1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | a             | a    | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

 

當然explain也可以來查詢使用了什麼索引；  

• select_type
• • simple:簡單表，即不使用表鏈接或者子查詢
  • Primary：主查詢，即外層的查詢
  • union：union中的第二個或者後面的查詢語句
  • subquery: 子查詢中的第一個select
• table：輸出結果集的表
• type：訪問類型
• • all，全表掃描
  • index，索引全掃描
  • range，索引范圍掃描，常見於< , >,between
  • ref,使用非唯一索引掃描或唯一索引的前綴掃描，返回匹配某個單獨值的記錄行
  • eq_ref,類似ref,區別在於使用了唯一索引；
  • const/system，表中最多有一個匹配行；Primary key 或 unique index;
  • null,不用訪問表或者索引就可以得到結果
• possible_keys：表示查詢時可能使用的索引
• key: 表示實際使用的索引
• key_len: 使用到索引字段的長度
• rows：掃描行的數量
• extra：執行情況的說明和描述；

  使用explain extended，可以得到更清晰易讀的SQL，多出了warning，可以進一步分析SQL；   當然如果表 有分區，那麼使用explain partition 可以找到select到底是在哪個分區查詢的；   4 show profile 分析SQL 查看mysql是否支持profile；

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

  查看profiling是否開啟，默認關閉：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)
 

 

開啟profiling：

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

通過profile，我們能夠更清楚地了解SQL執行的過程。 如何使用：  

mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.02 sec)

 

通過show profiles，找到對應SQL的 query id;

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.01064275 | select count(*) from payment |
|        2 | 0.00048225 | show databases               |
|        3 | 0.00015000 | show DATABASE()              |
|        4 | 0.00039975 | show tables                  |
+----------+------------+------------------------------+

通過show profile for query id ，分析具體的SQL； 能夠看到執行過程中線程的每個狀態和消耗的時間；

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000058 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000050 |
| System lock          | 0.000008 |
| init                 | 0.000012 |
| optimizing           | 0.000005 |
| statistics           | 0.000012 |
| preparing            | 0.000010 |
| executing            | 0.000007 |
| checking permissions | 0.000132 |
| Sending data         | 0.000042 |
| end                  | 0.000007 |
| query end            | 0.000007 |
| closing tables       | 0.000005 |
| removing tmp table   | 0.000009 |
| closing tables       | 0.000006 |
| freeing items        | 0.000015 |
| logging slow query   | 0.000005 |
| cleaning up          | 0.000006 |
+----------------------+----------+
19 rows in set (0.00 sec)

在獲取到最消耗時間的線程狀態後，mysql支持進一步選擇all,cpu,block io ,context switch,page faults等明細類型來查看mysql在使用什麼資源上耗費了過高的時間。   例如選擇查看cup的消耗時間：

mysql> show profile cpu for query 4;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000058 | 0.000000 |   0.000000 |
| checking permissions | 0.000009 | 0.000000 |   0.000000 |
| Opening tables       | 0.000050 | 0.000000 |   0.000000 |
| System lock          | 0.000008 | 0.000000 |   0.000000 |
| init                 | 0.000012 | 0.000000 |   0.000000 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |
| statistics           | 0.000012 | 0.000000 |   0.000000 |
| preparing            | 0.000010 | 0.000000 |   0.000000 |
| executing            | 0.000007 | 0.000000 |   0.000000 |
| checking permissions | 0.000132 | 0.000000 |   0.000000 |
| Sending data         | 0.000042 | 0.000000 |   0.000000 |
| end                  | 0.000007 | 0.000000 |   0.000000 |
| query end            | 0.000007 | 0.000000 |   0.000000 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |
| removing tmp table   | 0.000009 | 0.000000 |   0.000000 |
| closing tables       | 0.000006 | 0.000000 |   0.000000 |
| freeing items        | 0.000015 | 0.000000 |   0.000000 |
| logging slow query   | 0.000005 | 0.000000 |   0.000000 |
| cleaning up          | 0.000006 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------

show profile 能夠在做SQL優化時幫助我們了解時間都耗費到哪裡去了；   而mysql5.6則通過trace文件進一步向我們展示了優化器是如何選擇執行計劃的。   5 通過trace 分析優化器如何選擇執行計劃   提供了對SQL的跟蹤trace，通過trace文件能夠進一步了解為什麼優化器選擇A執行計劃而不選擇B執行計劃，幫助我們更好地理解優化器的行為。   使用方式： 首先打開trace，設置格式為json，設置trace最大能夠使用的內存大小，避免解析過程中因為默認內存過小而不能完整顯示。   然後執行select；   最後在，information_schema.optimizer_trace中查看跟蹤文件；    索引問題

---

  索引是數據庫優化中最常用也是最重要的手段之一，通過索引通常可以幫助用戶解決大多數的SQL性能問題。    索引的存儲分類 索引是在mysql的存儲引擎層中實現的，而不是在服務器層實現的。

• B-Tree 索引：大部分引擎都支持B-Tree索引
• HASH索引：只有memory引擎支持，使用場景簡單。
• R-Tree索引：空間索引，Myisam引擎的一個特殊索引類型，主要用於地理空間數據類型
• Full-text：全文索引

  前綴索引，大大縮小索引文件的大小，但是在order by 和 group by 操作的時候無法使用前綴索引。    查看所有使用情況

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 6     |
| Handler_read_last     | 0     |
| Handler_read_next     | 16050 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 297   |
+-----------------------+-------+
7 rows in set (0.00 sec)

• Handler_read_key :值高，證明索引正在工作；值低，說明增加索引得到的性能改善不高，因為索引不經常使用
• Handler_read_rnd_next：值高，意味著查詢效率低，應該建立索引補救；

  優化方法

---

  定期分析表和檢查表   分析表：

mysql> analyze table store;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| sakila.store | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.00 sec)

本語句用於分析和存儲表的關鍵字分布，分析的結果將可以使得系統得到准確的統計信息，是的SQL能夠生成正確的執行計劃。 如果用戶感覺實際執行計劃並不是預期的執行計劃，執行一次分析表可能會解決問題。   檢查表：

mysql> check table store;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.store | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)
 

檢查表的作用是檢查一個或多個表是否有錯誤。 check table 也可以檢查視圖是否有錯誤，比如在視圖定義中被引用的表已經不存在；    定期優化表   優化表:

mysql> optimize table store;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| sakila.store | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sakila.store | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

將表中的空間碎片進行合並，並且可以消除由於刪除或者更新造成的空間浪費； 適用於：已經刪除了表的一大部分，或者已經對含有可變長度行的表（含有varchar，blob，text）進行了很多更改，此時表中的空間會產生大量的空間碎片；   另外innodb表在刪除大量數據後： 可以使用alter table 但是不修改引擎的方式來回收不用的空間：

mysql> alter table payment engine=innodb;
Query OK, 16049 rows affected (0.62 sec)
Records: 16049  Duplicates: 0  Warnings: 0

 

常用SQL的優化

---

  1 大批量插入數據-load   各個引擎的優化方式是不一樣的： MyIsam 引擎，使用load 導入大批數據時:   導入前，使索引失效，導入完成之後，在啟用索引；   innodb引擎：   2 優化insert語句

• 使用多個值表的insert比單個insert語句快，因為多值表一起插入，縮減了客戶端與數據庫之間的連接，關閉等消耗；
• • insert into test values(1,2),(2,3),(3,4).....
• 將索引文件和數據文件分在不同的磁盤上存放（利用建表中的選項）
• 當從一個文本裝載一個表時，使用load data infile ，這通常比使用很多insert 語句快20倍；
• 如果從不同客戶插入很多行，insert delayed 語句得到更高的速度；將數據都放入內存中，然後合並一起insert，減少客戶端與數據庫的交互

  3 優化order by 語句 mysql中有兩種排序方式

• 第一種通過有序索引順序掃描直接返回有序數據，這種方式在使用explain 分析查詢的時候顯示為useing index，不需要額外的排序，操作效率較高。
• 第二種是通過返回數據進行排序，也就是filesort排序，所有不是通過索引直接返回排序結果的排序都叫filesort排序。

  1.索引排序 例子：

 
mysql> alter table customer  add index idx_email_storeid (email,store_id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> explain select store_id,email, customer_id from customer order by email\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
         type: index
possible_keys: NULL
          key: idx_email_storeid
      key_len: 154
          ref: NULL
         rows: 652
        Extra: Using index
1 row in set (0.00 sec)

 

可以看到，此時只使用了索引順序，沒有使用filesort；   如果將索引改成：

mysql> alter table customer  drop index idx_email_storeid;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table customer add index idx_storeid_email (store_id,email);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> explain select store_id,email, customer_id from customer order by email\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
         type: index
possible_keys: NULL
          key: idx_storeid_email
      key_len: 154
          ref: NULL
         rows: 652
        Extra: Using index; Using filesort
1 row in set (0.00 sec)

可以看到，此時使用到了filesort 這種額外排序，這顯然會增加開銷； 可見，索引最左端原則，order by email，那麼email 這一列在索引中應該在最左端，這樣才能夠只使用索引排序，而不用使用filesort；   優化目標： 盡量減少額外的排序，通過索引直接返回有序數據。 到達這目的要求：where 條件和 order by 使用相同的索引，並且order by 的順序和索引順序相同，並且order by的字段都是升序或者降序。否則肯定需要額外的排序操作，這樣就會出現filesort；   總結： 下列SQL可以使用索引：

• select * from tabname order by key_part1,key_part2,......;
• select * from tabname where key_part1=1 order by key_part1 DESC, key_part2 DESC;
• select * from tabname order by key_part1 DESC, key_part2 DESC;

  下列不可以使用索引：

• select * from tabname order by key_part1 desc ,key_part2 asc;
• • order by 的字段混合 ASC,DESC
• select * from tabname  where key2 = 2 order by key1;
• • 用於查詢行的關鍵字與order by 中使用的不相同
• select * from tabname order by key1，key2;

  2.filesort的優化 通過創建合適的索引能夠減少filesort出現，但是在某些情況下，條件限制不能讓filesort消失，那就需要想辦法加快filesort的操作。   mysql有兩種排序算法：

• 兩次掃描算法：首先根據條件取出排序字段和行指針信息，之後在排序區sort buffer中排序。優點是排序的時候內存開銷較小，但排序效率低；
• 一次掃描算法：一次性去除滿足條件的行的所有字段，然後在排序區sort buffer中排序後直接輸出結果。優點排序效率比兩次掃描高，但內存開銷大；

  mysql通過max_length_for_sort_data的大小和query語句取出的字段總大小來判斷使用哪種排序算法； 如果max_length_for_sort_data 大 則使用一次掃描算法，如果小則使用 兩次掃描算法；   4  優化group by 語句   默認情況下，group by col1,col2, 的字段進行排序。這當然會造成額外消耗；要消除這種不必要的排序，可以使用 order by null 來禁止排序；  

mysql> explain select payment_date,sum(amount) from payment group by payment_date \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16451
        Extra: Using temporary; Using filesort
1 row in set (0.01 sec)

使用order by null優化group by

mysql> explain select payment_date,sum(amount) from payment group by payment_date ORDER BY NULL \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16451
        Extra: Using temporary
1 row in set (0.00 sec)

 

  5 優化嵌套查詢 子查詢可以被更有效率的連接（join）替代；   連接（join）之所以更有效率一些，因為mysql不需要在內存中創建臨時表來完成這個邏輯上需要兩個步驟的查詢工作；     6 mysql 如何優化or條件     對於含有or的查詢子句，如果要利用索引，則or之間的每個條件列都必須用到索引；如果沒有索引，則應該考慮增加索引；   mysql在處理含有or子句的查詢時，實際是對or的各個字段分別查詢後的結果進行了union 操作；     但是在建有符合索引的列 company_id 和moneys上面做or操作時，卻不能用到索引；     7 優化分頁查詢 延遲關聯，它讓mysql掃描盡可能少的頁面，索取需要訪問的記錄後再根據關聯列回原表查詢需要的所有列。   考慮下面的查詢：  

mysql> explain select film_id,description from 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 | 1134 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

使用"延遲關聯":  

mysql> explain select film_id,description from film inner join ( select film_id from film  order by title limit 50,5) AS lim using(film_id);
+----+-------------+------------+--------+---------------+-----------+---------+-------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key       | key_len | ref         | rows | Extra       |
+----+-------------+------------+--------+---------------+-----------+---------+-------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL      | NULL    | NULL        |    5 |             |
|  1 | PRIMARY     | film       | eq_ref | PRIMARY       | PRIMARY   | 2       | lim.film_id |    1 |             |
|  2 | DERIVED     | film       | index  | NULL          | idx_title | 767     | NULL        |   55 | Using index |
+----+-------------+------------+--------+---------------+-----------+---------+-------------+------+-------------+
3 rows in set (0.00 sec)

首先，讓film_id使用索引，找到對應film_id，然後再回表找到對應description的數據列。這樣，是延遲了列的訪問，所以叫延遲關聯；其實是分別找出對應列的數據行；   使用書簽記錄   offset，它會導致mysql掃描大量不需要的行然後再拋棄掉。 可以使用書簽記錄上次數據的位置，那麼下次就可以直接從書簽記錄的位置開始掃描，這樣就可以避免使用offset；   首先獲得第一組結果:  

mysql> select * from rental order by rental_id limit 5;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
|         4 | 2005-05-24 23:04:41 |         2452 |         333 | 2005-06-03 01:43:41 |        2 | 2006-02-15 21:30:53 |
|         5 | 2005-05-24 23:05:21 |         2079 |         222 | 2005-06-02 04:33:21 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
 

之後從書簽開始再找：

mysql> select * from rental where rental_id > 5 order by rental_id limit 5;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         6 | 2005-05-24 23:08:07 |         2792 |         549 | 2005-05-27 01:32:07 |        1 | 2006-02-15 21:30:53 |
|         7 | 2005-05-24 23:11:53 |         3995 |         269 | 2005-05-29 20:34:53 |        2 | 2006-02-15 21:30:53 |
|         8 | 2005-05-24 23:31:46 |         2346 |         239 | 2005-05-27 23:33:46 |        2 | 2006-02-15 21:30:53 |
|         9 | 2005-05-25 00:00:40 |         2580 |         126 | 2005-05-28 00:22:40 |        1 | 2006-02-15 21:30:53 |
|        10 | 2005-05-25 00:02:21 |         1824 |         399 | 2005-05-31 22:44:21 |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
5 rows in set (0.00 sec)

   總結

---

 

•  mysql客戶端和服務器間：半雙工   
•  show processlist，實時查看SQL執行情況;
•  查詢優化器：
• • 統計信息：
  • • 每個表/索引的頁面個數，
    • 索引的基數，索引和數據行的長度，索引分布情況；
  • 不考慮任何緩存假設數據讀取需要一次IO；
  • 優化策略：
  • • 靜態優化，“編譯時優化”
    • 動態優化“運行時優化”
•  查詢優化器的局限：
• • 關聯子查詢，使用連接替代（5.6之後優化器已經自動優化了）；
  • union限制，需每個子句都limit 20控制臨時表數量；
  • 最大值和最小值優化，不能自動更據主鍵ID 選擇；
  • 不允許在同一表上更新和查詢，可以使用內連接跳過限制；
•  hint：
• • use index
  • ignore index
  • force index
• 慢查詢分析：
• • show status like 'com%':
  • • 了解讀寫比例；
    • 事務回滾比例；
    • 視圖連接mysql服務器的次數；
    • 慢查詢的次數；
  • 定位低效SQL：慢查詢日志
  • explain分析低效SQL：
  • • explain extended 可以得到更清晰易讀的SQL，多出來warning；
    • explain partition 找到select到底是在哪個分區查詢；
  • show profile 分析SQL：
  • • show profile 能夠在做SQL優化時幫助我們了解時間都耗費到哪裡去了；
    • 通過show profiles 找出query id，
    • 通過show profile for query id 分析具體的SQL；能夠看到執行過程中線程的每個狀態和消耗的時間；也能根據cpu,io，等具體參數；
  • trace：5.6之後可以使用，通過trace文件能夠進一步了解為什麼優化器選擇A執行計劃而不選擇B執行計劃，幫助我們更好地理解優化器的行為
• 索引使用情況：
• • show status like 'Handler_read%'
  • Handler_read_key:值高，證明索引正在工作；值低，說明增加索引得到的性能改善不高，因為索引不經常使用；
  • Handler_read_rnd_next:值高，意味著查詢效率低，應該建立索引補救；
• 定期分析表和檢查表：使系統得到准確的統計信息，使優化器更好工作；
• 常用SQL優化：
• • load:
  • • myisam，導入前，使索引失效，導入後，開啟索引；
    • innodb，關閉唯一性校驗
  • insert:多值表插入，
  • order by:
  • • 索引排序：
    • • where條件和order by使用相同索引
      • order by 的順序和索引順序相同
      • order by 字段都是升序或降序，
    • filesort：兩次掃描算法，一次掃描算法；
• group by:group by 默認對字段排序，使用order by null 來禁止排序；
• 子查詢可以使用連接代替
• or條件使用索引需要左右都要有索引段；
• 分頁查詢
• • “延遲關聯”，
  • “首先獲得第一組，然後使用書簽方式”
• 將大查詢分解為多個小查詢