sql資料庫語句最佳化分析和最佳化技巧總結（sql最佳化工具）

通常sql資料庫需要進行最佳化分析，並且還有一定的技巧，sql最佳化的幾種方法這裡就不做詳細介紹了，本文將會sql語句最佳化進行總結，後面還附了最佳化工具SQL Tuning Expert for Oracle及使用方法，首先我們要遵隨資料庫最佳化的幾個原則：

1.盡量避免在列上做運算，這樣會導致索引失敗；

2.使用join是應該用小結果集驅動大結果集，同時把複雜的join查詢拆分成多個query。不然join的越多表，就會導致越多的鎖定和堵塞。

3.注意like模糊查詢的使用，避免使用%%，例如select * from a where name like '%de%';

代替語句：select * from a where name >= 'de' and name < 'df';

4.僅列出需要查詢的欄位，不要使用select * from ...，節省記憶體；

5.使用批量插入語句，節省互動；

insert into a (id ,name)values(2,'a'),(3,'s');

6.limit基數比較大時，使用between ... and ...

7.不要使用rand函數隨機擷取記錄；

8.避免使用null ，這就需要在建表時，盡量設定為not null，提升查詢效能；

9，不要使用count(id),而應該是count(*)

10.不要做無謂的排序，儘可能在索引中完成排序；

我們先來看一個sql：

 select                    ii.product_id,                     p.product_name,                     count(distinct pim.pallet_id) count_pallet_id,                     if(round(sum(itg.quantity),2) > -1 && round(sum(itg.quantity),2) < 0.005, 0, round(sum(itg.quantity),2)) quantity,                    round(ifnull(sum(itag.locked_quantity), 0.00000),2) locked_quantity,                    pc.container_unit_code_name,                    if(round(sum(itg.qoh),2) > -1 && round(sum(itg.qoh),2) < 0.005, 0, round(sum(itg.qoh),2)) qoh,                    round(ifnull(sum(itag.locked_qoh), 0.00000),2) locked_qoh,                    p.unit_code,                    p.unit_code_name                from (select                         it.inventory_item_id item_id,                         sum(it.quantity) quantity,                         sum(it.real_quantity) qoh                     from                         ws_inventory_transaction it                    where                         it.enabled = 1                     group by                         it.inventory_item_id                      ) itg                     left join (select                                     ita.inventory_item_id item_id,                                     sum(ita.quantity) locked_quantity,                                     sum(ita.real_quantity) locked_qoh                                from                                     ws_inventory_transaction_action ita                               where                                     1=1 and ita.type in ('locked', 'release')                                group by                                     ita.inventory_item_id                                )itag on itg.item_id = itag.item_id                    inner join ws_inventory_item ii on itg.item_id = ii.inventory_item_id                     inner join ws_pallet_item_mapping pim on ii.inventory_item_id = pim.inventory_item_id                      inner join ws_product p on ii.product_id = p.product_id and p.status = 'OK'                    left join ws_product_container pc on ii.container_id = pc.container_id//總起來說關聯太多表，設計表時可以多一些冗餘欄位，減少表之間的關聯查詢；                where                     ii.inventory_type = 'raw_material' and                     ii.inventory_status = 'in_stock' and                     ii.facility_id = '25' and                     datediff(now(),ii.last_updated_time) < 3  //違反了第一個原則                     and p.product_type = 'goods'                     and p.product_name like '%果%'   // 違反原則3                group by                     ii.product_id                having                     qoh < 0.005                order by                     qoh desc

上面的sql我們在from 中使用了子查詢，這樣對查詢是非常不利的；

更好的一種做法是下面的語句：

select                  t.facility_id,                f.facility_name,                t.inventory_status,                wis.inventory_status_name,                t.inventory_type,                t.product_type,                t.product_id,                 p.product_name,                t.container_id,                 t.unit_quantity,                 p.unit_code,                p.unit_code_name,                pc.container_unit_code_name,                t.secret_key,                sum(t.quantity) quantity,                sum(t.real_quantity) real_quantity,                sum(t.locked_quantity) locked_quantity,                sum(t.locked_real_quantity) locked_real_quantity            from ( select                         ii.facility_id,                        ii.inventory_status,                        ii.inventory_type,                        ii.product_type,                        ii.product_id,                         ii.container_id,                         ii.unit_quantity,                         ita.secret_key,                        ii.quantity quantity,                        ii.real_quantity real_quantity,                        sum(ita.quantity) locked_quantity,                        sum(ita.real_quantity) locked_real_quantity                    from                         ws_inventory_item ii                         inner join ws_inventory_transaction_action ita on ii.inventory_item_id = ita.inventory_item_id                    where                         ii.facility_id = '{$facility_id}' and                         ii.inventory_status = '{$inventory_status}' and                         ii.product_type = '{$product_type}' and                         ii.inventory_type = '{$inventory_type}' and                        ii.locked_real_quantity > 0 and                         ita.type in ('locked', 'release')                     group by                         ii.product_id, ita.secret_key, ii.container_id, ita.inventory_item_id                    having                         locked_real_quantity > 0             ) as t                inner join ws_product p on t.product_id = p.product_id                 left join ws_facility f on t.facility_id = f.facility_id                left join ws_inventory_status wis on wis.inventory_status = t.inventory_status                left join ws_product_container pc on pc.container_id = t.container_id                        group by                 t.product_id, t.secret_key, t.container_id

注意：

1、from 語句中一定不要使用子查詢；

2、使用更多的where加以限制，縮小尋找範圍；

3、合理利用索引；

4、通過explain查看sql效能；

使用工具 SQL Tuning Expert for Oracle 最佳化SQL語句

對於SQL開發人員和DBA來說，根據業務需求寫出一條正確的SQL很容易。但是SQL的執行效能怎麼樣呢？能最佳化一下跑得更快嗎？如果不是資深
DBA,估計很多人都沒有信心。

幸運的是，自動化最佳化工具可以協助我們解決這個難題。這就是今天要介紹的 Tosska SQL Tuning Expert for Oracle 工具。

下載 https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/

本工具發明人Richard To, Dell的前首席工程師, 擁有超過20年的SQL最佳化經驗.

1、建立資料庫連接，也可以稍後建立。填好串連資訊，點擊 “Connect” 按鈕。

如果您已經安裝Oracle用戶端，並且在Oracle用戶端配置了TNS，可以在本視窗選擇“TNS”作為”Connection Mode”，然後在”Database Alias”中選擇配置好的TNS作為資料庫別名。

如果您沒有安裝Oracle用戶端或者不想安裝Oracle用戶端， 可以選擇“Basic Type”作為”Connection Mode”，只需資料庫伺服器IP, 連接埠和服務名即可。

2、輸入有效能問題的SQL

3、點擊Tune按鈕，自動產生大量的等價SQL並且開始執行。雖然測試還沒有完成，我們已經可以看到 SQL 20 的效能提升了100%。

讓我們仔細看一下SQL 20, 它使用了兩個Hints, 以最快的執行速度脫穎而出。原來的SQL要0.99秒，最佳化後的SQL執行時間接近0秒。

由於這條SQL每天要在資料庫中執行上萬次，最佳化後可節省大約 165秒的資料庫執行時間。

最後，用等價的SQL 20 替換 應用程式原始碼中有效能問題的SQL。重新編譯應用程式，效能得到了提高。

調優任務順利完成！

相關文章：

Sql效能最佳化總結與sql語句最佳化篇

SQL語句最佳化原則，sql語句最佳化

相關視頻：

MySQL最佳化視頻教程—布爾教育