DB要如何調效能呢？

peter109 2016-06-14 17:24:45 ‧ 14833 瀏覽

分享至

因為以前我公司

DB效能不夠

通常是CPU或RAM的問題

只要改善這兩個問題就沒了。

最近看到說可以調資料庫效能

請問是如何調呢？
以MS SQL來說
謝謝

窮嘶發發發 iT邦高手 1 級 ‧ 2016-06-15 13:31:28 檢舉

雷大說的是 SQL 語法的改進，但如果用的是套裝軟體，SQL 語法寫死的
那麼就沒得改的，建議從儲存裝置開始改善吧，例如換 SSD，
這樣對基本的資料查詢有很大的幫助，還有加記憶體，但這部分能增加的效益基本不多
因為軟體本身會控制每次查詢的筆數才對，他都是有上限的
因此除非使用者故意查詢大量資料，否則，記憶體很難不夠用
因此，儲存裝置的速度是優先考量，我們公司之前改過一次
從 HDD 改成 SSD ，基本上幾乎完全改善 DB 存取速度的問題
當然如果要大量運算的話，還是要看 CPU 的速度，不過，至少存取資料的速度改善了
就能縮短很多的時間

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

Ray

iT邦大神 1 級 ‧ 2016-06-14 23:27:01

最佳解答

加 CPU 和 RAM 是最後手段, 除非是當初對硬體效能估算錯誤, 否則不應該發生要加 CPU 和 RAM 的結果. 如果一直用這個手段, 等於告訴全世界: 「我不會估算和調整SQL效能, 所以只要效能不足, 就是加硬體解決」

這裡有一個案例, 說明如何調整 SQL 語法, 來增進效能, 原文出自:
張瀚文

=====我是分隔線=======

今天遇到的狀況，有在 MySQL 用索引的可以注意一下。

有兩組複合鍵：

第一組：索引名稱 D_A 索引順序 (D,A)

第二組：索引名稱 A_B_C 索引順序 (A,B,C)

Query：

SELECT count(*) FROM Table WHERE D < 日期 AND A = 1 ;

// 發現自從加了第二組索引後，竟然變慢了，於是查了語法怎麼做的

Query：

EXPLAIN SELECT count(*) FROM Table WHERE D < 日期 AND A = 1 ;

Possible_Keys：D_A, A_B_C

Key：A_B_C (竟然是另一組索引)

Extra：Using Where

這邊後來是用指定索引的方式，才使用到預期索引。

Query：

SELECT count(*) FROM Table USE INDEX (D_A) WHERE D < 日期 AND A = 1
;

指定索引後，再看語法執行情況：

Query：

EXPLAIN SELECT count(*) FROM Table USE INDEX (D_A) WHERE D < 日期 AND A = 1 ;

Possible_Keys：D_A

Key：D_A

Extra：Using Where, Using Index

總算使用了正確的索引。

因為加了一組新的索引，導致發生非預期的結果，看語法 Log 才發現，小地方要注意。

=========我是分隔線===========

以上的案例, 如果你沒有去查 SQL語法, 根本不知道它用錯索引, 只是盲目的加 CPU/RAM, 結果根源的問題沒有被解決, 以後還是會不斷的發生效能問題, 你能無限制地往上加硬體嗎?

回應 2
分享
檢舉

Ray iT邦大神 1 級 ‧ 2016-06-14 23:33:37 檢舉

再看一個案例:
原文出自Query Tuning By Example: Throw Away That Execution Plan

======未調校前的 SQL 語法====

SELECT DISTINCT
  o.order_id,
  o.order_date
FROM
  orders o,
  order_items ol
WHERE
  LOWER(o.order_state) = : order_state AND
  ol.order_id = o.order_id AND
  ol.item_id IN (
    SELECT item_id
    FROM items
    WHERE item_state = : item_state
  )
ORDER BY o.order_date;

====== 調校過後的 SQL 語法=====

SELECT
  order_id,
  order_date
FROM orders o
WHERE order_state = : order_state
AND EXISTS (
  SELECT *
  FROM order_items ol
  WHERE order_id = o.order_id
  AND EXISTS (
    SELECT *
    FROM items
    WHERE item_id = ol.item_id
    AND item_state = : item_state
  )
)
ORDER BY order_date;

前一個跑完要花費幾分鐘, 但後面這個跑完, 只需要不到 1 秒鐘. 假設前面是 2 分鐘好了 (120秒), 那麼改成後面這個, 等於速度提升了 120倍!!

你能用 CPU+RAM 提升 120倍的 SQL 速度嗎?