SQL業務審核與優化
轉自http://www.cnblogs.com/Aiapple/p/5697229.html
審核
什么是業務審核
-
類似與code review
-
評審業務Schema和SQL設計
-
偏重關注性能
-
是業務優化的主要入口之一
審核提前發現問題，進行優化
上線后通過監控或巡檢發現問題，進行優化
Schema設計審核
-
表和字段命名是否合規
-
字段類型，長度設計是否適當
-
表關聯關系是否合理
-
主鍵，更新時間保留字段等是否符合要求
-
約束，默認值等配置是否恰當
-
了解業務，表數據量，增長模式
-
數據訪問模式，均衡度
-
根據業務需求，表是否需要分區，是否有數據什么周期
SQL語句審核
-
SQL語句的執行頻率
-
表上是否有合適的索引
-
單次執行的成本
-
執行模式，鎖情況分析
-
關注事務上下文
什么時候需要審核
-
業務開發階段，上線前
-
業務版本變更，線上更新前
-
-
新表和SQL上線
-
SQL查詢條件變化
-
SQL查詢頻率變化
-
業務邏輯導致現有表數據量規模變化
業務發布流程
-
SQL審核需要開發與應用運維支持
-
充分溝通，做好必要性說明和教育工作
-
指定業務發布流程，嵌入DBA審核環節
-
積累經驗，不斷完善評審方法
慢查詢
查詢優化，索引優化，庫表結構優化需要齊頭并進。
慢查詢兩個步驟分析：
-
確認應用程序是否向數據庫請求了大量超過需要的數據
-
確認mysql服務器層是否在處理大量超過需要的數據記錄
是否向數據庫請求了不需要的數據
典型案例：
-
查詢不需要的記錄
-
多表關聯時返回全部列
-
總是取出全部列
-
重復查詢相同的數據
mysql是否在掃描額外的記錄
在確定查詢只返回需要的數據后，接下來應該看看查詢為了返回結果是否掃描了過多的數據。
mysql查詢開銷的三個指標：
這三個指標都會記錄到mysql的慢日志中，索引檢查慢日志記錄是找出掃描行數過多的查詢的好辦。
響應時間：執行時間和等待時間；
判斷一個響應時間是否是合理的值，可以使用"快速上限估計"。
掃描的行數和返回的行數
分析查詢時，查看該查詢掃描的行數是非常有幫助的。它一定程度上說明該查詢找到需要的數據的效率高不高。
如果發現查詢需要掃描大量的數據但只返回少數的行，優化方法：
-
使用索引覆蓋掃描，把所有需要用的列都放到索引中。
-
改變庫表結構。例如使用單獨的匯總表
-
重寫這個復雜的查詢，讓mysql優化器能夠以更優化的方式執行這個查詢。
有的時候將大查詢分解為多個小查詢是有必要的。
查詢執行的基礎
mysql查詢執行路徑
-
客服端發送一條查詢給服務器
-
服務器先檢查緩存。如果命中緩存，則立刻返回結果。否則進入下一階段。
-
服務器端進行SQL解析，預處理，再由優化器生成對應的執行計劃。
-
mysql根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢。
-
將結果返回給客戶端
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>selectcount(*) from film_actor; +----------+|count(*) |+----------+|5462|+----------+1 row inset (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 innerjoin 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 inset (0.00 sec)
如何用好關聯子查詢，很多時候，關聯子查詢也是一種非常合理，自然，甚至是性能最好的寫法。
where in(）肯定是不行的，但是 where exists（）有時是可以的；
2 union的限制
有時，mysql無法將限制條件從外層"下推"到內層，這使得原本能夠限制部分返回結果的條件無法應用到內層查詢的優化上。
如果希望union的各個子句能夠根據limit只取部分結果集，或者希望能夠先拍下再合并結果集的話，就需要在union的各個子句中分別使用這些子句。
如：
（select first_name,last_name from sakila.actor orderby last_name） unionall （select first_name,last_name from sakila.customer orderby last_name）
limit 20;
會將actor中200條記錄和customer中599條記錄放在一個臨時表中，然后在從臨時表中取出前20條；
而
（select first_name,last_name from sakila.actor orderby last_name
.png)
