SQL業(yè)務(wù)審核與優(yōu)化
轉(zhuǎn)自http://www.cnblogs.com/Aiapple/p/5697229.html
審核
什么是業(yè)務(wù)審核
-
類似與code review
-
評審業(yè)務(wù)Schema和SQL設(shè)計
-
偏重關(guān)注性能
-
是業(yè)務(wù)優(yōu)化的主要入口之一
審核提前發(fā)現(xiàn)問題，進行優(yōu)化
上線后通過監(jiān)控或巡檢發(fā)現(xiàn)問題，進行優(yōu)化
Schema設(shè)計審核
-
表和字段命名是否合規(guī)
-
字段類型，長度設(shè)計是否適當(dāng)
-
表關(guān)聯(lián)關(guān)系是否合理
-
主鍵，更新時間保留字段等是否符合要求
-
約束，默認(rèn)值等配置是否恰當(dāng)
-
了解業(yè)務(wù)，表數(shù)據(jù)量，增長模式
-
數(shù)據(jù)訪問模式，均衡度
-
根據(jù)業(yè)務(wù)需求，表是否需要分區(qū)，是否有數(shù)據(jù)什么周期
SQL語句審核
-
SQL語句的執(zhí)行頻率
-
表上是否有合適的索引
-
單次執(zhí)行的成本
-
執(zhí)行模式，鎖情況分析
-
關(guān)注事務(wù)上下文
什么時候需要審核
-
業(yè)務(wù)開發(fā)階段，上線前
-
業(yè)務(wù)版本變更，線上更新前
-
-
新表和SQL上線
-
SQL查詢條件變化
-
SQL查詢頻率變化
-
業(yè)務(wù)邏輯導(dǎo)致現(xiàn)有表數(shù)據(jù)量規(guī)模變化
業(yè)務(wù)發(fā)布流程
-
SQL審核需要開發(fā)與應(yīng)用運維支持
-
充分溝通，做好必要性說明和教育工作
-
指定業(yè)務(wù)發(fā)布流程，嵌入DBA審核環(huán)節(jié)
-
積累經(jīng)驗，不斷完善評審方法
慢查詢
查詢優(yōu)化，索引優(yōu)化，庫表結(jié)構(gòu)優(yōu)化需要齊頭并進。
慢查詢兩個步驟分析：
-
確認(rèn)應(yīng)用程序是否向數(shù)據(jù)庫請求了大量超過需要的數(shù)據(jù)
-
確認(rèn)mysql服務(wù)器層是否在處理大量超過需要的數(shù)據(jù)記錄
是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)
典型案例：
-
查詢不需要的記錄
-
多表關(guān)聯(lián)時返回全部列
-
總是取出全部列
-
重復(fù)查詢相同的數(shù)據(jù)
mysql是否在掃描額外的記錄
在確定查詢只返回需要的數(shù)據(jù)后，接下來應(yīng)該看看查詢?yōu)榱朔祷亟Y(jié)果是否掃描了過多的數(shù)據(jù)。
mysql查詢開銷的三個指標(biāo)：
-
響應(yīng)時間
-
掃描的行數(shù)
-
返回的行數(shù)
這三個指標(biāo)都會記錄到mysql的慢日志中，索引檢查慢日志記錄是找出掃描行數(shù)過多的查詢的好辦。
響應(yīng)時間：執(zhí)行時間和等待時間；
判斷一個響應(yīng)時間是否是合理的值，可以使用"快速上限估計"。
掃描的行數(shù)和返回的行數(shù)
分析查詢時，查看該查詢掃描的行數(shù)是非常有幫助的。它一定程度上說明該查詢找到需要的數(shù)據(jù)的效率高不高。
如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)但只返回少數(shù)的行，優(yōu)化方法：
-
使用索引覆蓋掃描，把所有需要用的列都放到索引中。
-
改變庫表結(jié)構(gòu)。例如使用單獨的匯總表
-
重寫這個復(fù)雜的查詢，讓mysql優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個查詢。
有的時候?qū)⒋蟛樵兎纸鉃槎鄠€小查詢是有必要的。
查詢執(zhí)行的基礎(chǔ)
mysql查詢執(zhí)行路徑
-
客服端發(fā)送一條查詢給服務(wù)器
-
服務(wù)器先檢查緩存。如果命中緩存，則立刻返回結(jié)果。否則進入下一階段。
-
服務(wù)器端進行SQL解析，預(yù)處理，再由優(yōu)化器生成對應(yīng)的執(zhí)行計劃。
-
mysql根據(jù)優(yōu)化器生成的執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢。
-
將結(jié)果返回給客戶端
mysql客戶端/服務(wù)器通信協(xié)議
mysql客戶端和服務(wù)器之間的通信協(xié)議是"半雙工"。任何時候只能一方發(fā)；不能同時發(fā)送；
mysql連接時線程狀態(tài)
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)
查詢優(yōu)化器
一條查詢可以有很多種執(zhí)行方式，最后都返回相同的結(jié)果。
優(yōu)化器的作用就是找到這其中最好的執(zhí)行計劃。
mysql使用基于成本的優(yōu)化器，它將嘗試預(yù)測一個查詢使用某種執(zhí)行計劃時的成本，并選擇其中成本最小的一個。
通過查詢當(dāng)前會話的last_query_cost的值來得知Mysql計算的當(dāng)前查詢的成本。
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 | +-----------------+-------------+
這個結(jié)果表示mysql優(yōu)化器認(rèn)為大概需要做1040個數(shù)據(jù)頁的隨機查找才能完成上面的查詢。這是根據(jù)一系列的統(tǒng)計信息計算得來的：每個表或者索引的頁面?zhèn)€數(shù)，索引的基數(shù)(索引中不同值的數(shù)量)，索引和數(shù)據(jù)行的長度，索引分布情況。
優(yōu)化器在評估成本的時候并不考慮任何層面的緩存，它假設(shè)讀取任何數(shù)據(jù)都需要一次磁盤I/O。
mysql優(yōu)化器選錯執(zhí)行計劃的原因：
-
統(tǒng)計信息不準(zhǔn)確
-
執(zhí)行計劃中的成本估算不等同于實際執(zhí)行的成本。
-
-
mysql的最有可能和你想的最優(yōu)不一樣。
-
-
比如你希望執(zhí)行時間盡可能的短，而mysql只是基于成本模型選擇的最優(yōu)執(zhí)行計劃。
-
mysql從不考慮其他并發(fā)執(zhí)行的查詢，這可能會影響到當(dāng)前查詢速度。
-
mysql不會考慮不受其控制的操作的成本。
-
-
如執(zhí)行存儲過程或者用戶自定義函數(shù)的成本
優(yōu)化策略：
-
靜態(tài)優(yōu)化
-
-
直接對解析樹進行分析，并完成優(yōu)化。優(yōu)化器通過一些簡單的代數(shù)變換將where條件轉(zhuǎn)換成另一種等價形式。靜態(tài)優(yōu)化在第一次完成后一直有效?？梢哉J(rèn)為這是一種"編譯時優(yōu)化"
-
動態(tài)優(yōu)化
-
-
動態(tài)優(yōu)化和查詢的上下文有關(guān)。也和其他很多因素有關(guān)，例如where中的取值，索引中條目，等等。每次查詢的時候都重新評估，可以認(rèn)為這是一種"運行時優(yōu)化"
mysql能夠處理的優(yōu)化類型
-
重新定義關(guān)聯(lián)表的順序。
-
將外聯(lián)結(jié)轉(zhuǎn)成內(nèi)連接
-
使用等價變化規(guī)則
-
-
合并和減少一些比較，移除一些恒成立和一些恒不成立的判斷
-
優(yōu)化count(),min(),max()，min()就直接拿BTree樹最左端數(shù)據(jù)行
-
預(yù)估并轉(zhuǎn)換為常數(shù)表達式
-
覆蓋索引掃描
-
子查詢優(yōu)化
-
提前終止查詢
-
等值傳播
在查詢中添加hint,提示優(yōu)化器，
優(yōu)化器的局限性
1 關(guān)聯(lián)子查詢
mysql的子查詢實現(xiàn)得非常糟糕；最糟糕的一類查詢是where條件中包含IN()的子查詢語句。
例如，我們希望找到sakila數(shù)據(jù)庫中，演員actor_id為1，參演過的所有影片信息。很自然的，我們會按照下面的方式
mysql> select * from film where film_id in ( select film_id from film_actor where actor_id =1) \G;
我們一般認(rèn)為，mysql會首先將子查詢的actor_id=1的所有film_id都找到，然后再去做外部查詢，如
select * from film where film_id in （1,23,25,106,140）；
然而，mysql不是這樣做的。
mysql會將相關(guān)的外層表壓到子查詢中，它認(rèn)為這樣可以更高效率地查找數(shù)據(jù)行。
當(dāng)然我們可以使用連接替代子查詢重寫這個SQL，來優(yōu)化；
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)
如何用好關(guān)聯(lián)子查詢，很多時候，關(guān)聯(lián)子查詢也是一種非常合理，自然，甚至是性能最好的寫法。
where in(）肯定是不行的，但是 where exists（）有時是可以的；
2 union的限制
有時，mysql無法將限制條件從外層"下推"到內(nèi)層，這使得原本能夠限制部分返回結(jié)果的條件無法應(yīng)用到內(nèi)層查詢的優(yōu)化上。
如果希望union的各個子句能夠根據(jù)limit只取部分結(jié)果集，或者希望能夠先拍下再合并結(jié)果集的話，就需要在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)
