Sql效能優化梳理

wolearn發表於2017-09-07
SQL優化

前言

本文主要針對的是關係型資料資料庫MySql。鍵值類資料庫可以參考最簡大資料Redis。先簡單梳理下Mysql的基本概念，然後分建立時和查詢時這兩個階段的優化展開。

1.0 基本概念簡述

1.1 邏輯架構

  • 第一層：客戶端通過連線服務，將要執行的sql指令傳輸過來
  • 第二層：伺服器解析並優化sql，生成最終的執行計劃並執行
  • 第三層：儲存引擎，負責資料的儲存和提取

1.2 鎖

資料庫通過鎖機制來解決併發場景-共享鎖（讀鎖）和排他鎖（寫鎖）。讀鎖是不阻塞的，多個客戶端可以在同一時刻讀取同一個資源。寫鎖是排他的，並且會阻塞其他的讀鎖和寫鎖。簡單提下樂觀鎖和悲觀鎖。

  • 樂觀鎖，通常用於資料競爭不激烈的場景，多讀少寫，通過版本號和時間戳實現。
  • 悲觀鎖，通常用於資料競爭激烈的場景，每次操作都會鎖定資料。

要鎖定資料需要一定的鎖策略來配合。

  • 表鎖，鎖定整張表，開銷最小，但是會加劇鎖競爭。
  • 行鎖，鎖定行級別，開銷最大，但是可以最大程度的支援併發。

但是MySql的儲存引擎的真實實現不是簡單的行級鎖，一般都是實現了多版本併發控制（MVCC）。MVCC是行級鎖的變種，多數情況下避免了加鎖操作，開銷更低。MVCC是通過儲存資料的某個時間點快照實現的。

1.3 事務

事務保證一組原子性的操作，要麼全部成功，要麼全部失敗。一旦失敗，回滾之前的所有操作。MySql採用自動提交，如果不是顯式的開啟一個事務，則每個查詢都作為一個事務。

隔離級別控制了一個事務中的修改，哪些在事務內和事務間是可見的。四種常見的隔離級別：

  • 未提交讀（Read UnCommitted），事務中的修改，即使沒提交對其他事務也是可見的。事務可能讀取未提交的資料，造成髒讀。
  • 提交讀（Read Committed），一個事務開始時，只能看見已提交的事務所做的修改。事務未提交之前，所做的修改對其他事務是不可見的。也叫不可重複讀，同一個事務多次讀取同樣記錄可能不同。
  • 可重複讀（RepeatTable Read），同一個事務中多次讀取同樣的記錄結果時結果相同。
  • 可序列化（Serializable），最高隔離級別，強制事務序列執行。

1.4 儲存引擎

  • InnoDB引擎，最重要，使用最廣泛的儲存引擎。被用來設計處理大量短期事務，具有高效能和自動奔潰恢復的特性。
  • MyISAM引擎，不支援事務和行級鎖，奔潰後無法安全恢復。

2.0 建立時優化

2.1 Schema和資料型別優化

整數

  • TinyInt,SmallInt,MediumInt,Int,BigInt 使用的儲存8,16,24,32,64位儲存空間。使用Unsigned表示不允許負數，可以使正數的上線提高一倍。

實數

  • Float,Double , 支援近似的浮點運算。
  • Decimal，用於儲存精確的小數。

字串

  • VarChar，儲存變長的字串。需要1或2個額外的位元組記錄字串的長度。
  • Char，定長，適合儲存固定長度的字串，如MD5值。
  • Blob，Text 為了儲存很大的資料而設計的。分別採用二進位制和字元的方式。

時間型別

  • DateTime，儲存大範圍的值，佔8個位元組。
  • TimeStamp，推薦，與UNIX時間戳相同，佔4個位元組。

優化建議點

  1. 儘量使用對應的資料型別。比如，不要用字串型別儲存時間，用整型儲存IP。
  2. 選擇更小的資料型別。能用TinyInt不用Int。
  3. 標識列（identifier column），建議使用整型，不推薦字串型別，佔用更多空間，而且計算速度比整型慢。
  4. 不推薦ORM系統自動生成的Schema，通常具有不注重資料型別，使用很大的VarChar型別，索引利用不合理等問題。
  5. 真實場景混用正規化和反正規化。冗餘高查詢效率高，插入更新效率低；冗餘低插入更新效率高，查詢效率低。
  6. 建立完全的獨立的彙總表\快取表，定時生成資料，用於使用者耗時時間長的操作。對於精確度要求高的彙總操作，可以採用 歷史結果+最新記錄的結果 來達到快速查詢的目的。
  7. 資料遷移，表升級的過程中可以使用影子表的方式，通過修改原表的表名，達到儲存歷史資料，同時不影響新表使用的目的。

2.2 索引

索引包含一個或多個列的值。MySql只能高效的利用索引的最左字首列。索引的優勢：

  • 減少查詢掃描的資料量
  • 避免排序和零時表
  • 將隨機IO變為順序IO （順序IO的效率高於隨機IO）

B-Tree

使用最多的索引型別。採用B-Tree資料結構來儲存資料（每個葉子節點都包含指向下一個葉子節點的指標，從而方便葉子節點的遍歷）。B-Tree索引適用於全鍵值，鍵值範圍，鍵字首查詢，支援排序。

B-Tree索引限制：

  • 如果不是按照索引的最左列開始查詢，則無法使用索引。
  • 不能跳過索引中的列。如果使用第一列和第三列索引，則只能使用第一列索引。
  • 如果查詢中有個範圍查詢，則其右邊的所有列都無法使用索引優化查詢。

雜湊索引

只有精確匹配索引的所有列，查詢才有效。儲存引擎會對所有的索引列計算一個雜湊碼，雜湊索引將所有的雜湊碼儲存在索引中，並儲存指向每個資料行的指標。

雜湊索引限制：

  • 無法用於排序
  • 不支援部分匹配
  • 只支援等值查詢如=，IN（），不支援 < >

優化建議點

  1. 注意每種索引的適用範圍和適用限制。
  2. 索引的列如果是表示式的一部分或者是函式的引數，則失效。
  3. 針對特別長的字串，可以使用字首索引，根據索引的選擇性選擇合適的字首長度。
  4. 使用多列索引的時候，可以通過 AND 和 OR 語法連線。
  5. 重複索引沒必要，如（A，B）和（A）重複。
  6. 索引在where條件查詢和group by語法查詢的時候特別有效。
  7. 將範圍查詢放在條件查詢的最後，防止範圍查詢導致的右邊索引失效的問題。
  8. 索引最好不要選擇過長的字串，而且索引列也不宜為null。

3.0 查詢時優化

3.1 查詢質量的三個重要指標

  • 響應時間 （服務時間，排隊時間）
  • 掃描的行
  • 返回的行

3.2 查詢優化點

  1. 避免查詢無關的列，如使用Select * 返回所有的列。
  2. 避免查詢無關的行
  3. 切分查詢。將一個對伺服器壓力較大的任務，分解到一個較長的時間中，並分多次執行。如要刪除一萬條資料，可以分10次執行，每次執行完成後暫停一段時間，再繼續執行。過程中可以釋放伺服器資源給其他任務。
  4. 分解關聯查詢。將多表關聯查詢的一次查詢，分解成對單表的多次查詢。可以減少鎖競爭，查詢本身的查詢效率也比較高。因為MySql的連線和斷開都是輕量級的操作，不會由於查詢拆分為多次，造成效率問題。
  5. 注意count的操作只能統計不為null的列，所以統計總的行數使用count（*）。
  6. group by 按照標識列分組效率高，分組結果不宜出行分組列之外的列。
  7. 關聯查詢延遲關聯，可以根據查詢條件先縮小各自要查詢的範圍，再關聯。
  8. Limit分頁優化。可以根據索引覆蓋掃描，再根據索引列關聯自身查詢其他列。如
    SELECT
 id,
 NAME,
 age
WHERE
 student s1
INNER JOIN (
 SELECT
     id
 FROM
     student
 ORDER BY
     age
 LIMIT 50,5
) AS s2 ON s1.id = s2.id複製程式碼
  9. Union查詢預設去重，如果不是業務必須，建議使用效率更高的Union All

後記

歡迎大家在評論中補充，我會把大家補充的內容持續更新出來。如果有幫助，請幫忙點喜歡。

補充更新日誌

2017.09.08

---->來自大神-小寶
1.條件中的欄位型別和表結構型別不一致，mysql會自動加轉換函式，導致索引作為函式中的引數失效。
2.like查詢前面部分未輸入，以%開頭無法命中索引。
3.補充2個5.7版本的新特性：

  • generated column，就是資料庫中這一列由其他列計算而得

    CREATE TABLE triangle (sidea DOUBLE, sideb DOUBLE, area DOUBLE AS (sidea * sideb / 2));
insert into triangle(sidea, sideb) values(3, 4);
select * from triangle;複製程式碼

    +-------+-------+------+
    | sidea | sideb | area |
    +-------+-------+------+
    | 3 | 4 | 6 |
    +-------+-------+------+

  • 支援JSON格式資料，並提供相關內建函式

    CREATE TABLE json_test (name JSON);
INSERT INTO json_test VALUES('{"name1": "value1", "name2": "value2"}');
SELECT * FROM json_test WHERE JSON_CONTAINS(name, '$.name1');複製程式碼

---->來自JVM專家-達

  1. 關注explain在效能分析中的使用
    EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679"複製程式碼
  • select_type，有幾種值：simple（表示簡單的select，沒有union和子查詢），primary（有子查詢，最外面的select查詢就是primary），union（union中的第二個或隨後的select查詢，不依賴外部查詢結果），dependent union（union中的第二個或隨後的select查詢，依賴外部查詢結果）
  • type，有幾種值：system（表僅有一行（=系統表），這是const連線型別的一個特例），const（常量查詢）, ref(非唯一索引訪問，只有普通索引)，eq_ref（使用唯一索引或元件查詢），all（全表查詢），index（根據索引查詢全表），range（範圍查詢）
  • possible_keys: 表中可能幫助查詢的索引
  • key，選擇使用的索引
  • key_len，使用的索引長度
  • rows，掃描的行數，越大越不好
  • extra，有幾種值：Only index（資訊從索引中檢索出，比掃描錶快），where used（使用where限制），Using filesort （可能在記憶體或磁碟排序），Using temporary（對查詢結果排序時使用臨時表）

相關文章