資料庫優化政策小結

• 資料類型的優化
  • MySQL資料類型
  • 優化政策
• 索引優化
  • 索引類型
  • 高性能索引政策
• 查詢優化
  • 優化資料通路
  • 從好到壞的where條件應用
  • 重構查詢

一、資料類型的優化

（1）MySQL資料類型

• 整數類型：
  • TinyInt，存儲空間8， 位元組長度1；
  • SmallInt， 存儲空間16， 位元組長度2；
  • MediumInt，存儲空間24， 位元組長度3；
  • Int，存儲空間32， 位元組長度4；
  • BigInt，存儲空間64， 位元組長度8；
• 實數類型：
  • Float：位元組長度8，單精度浮點數；
  • Double：位元組長度16，雙精度浮點數；
  • Decimal：未打包的浮點數，計算中會轉化為Double；Decimal 相比于 Float 和 Double 需要額外的空間和計算開銷，是以盡量隻在對小數進行精确計算時才使用，例如存儲财務資料。在資料量比較大的時候，可以考慮使用 BigInt 代替 Decimal，将需要存儲的貨币機關根據小數的位數乘以相應的倍數即可。 　
• 字元串類型：
  • VarChar：存儲可變長字元串，需要一個或兩個額外位元組記錄字元串長度。适用于：字元串列的最大長度比平均長度大很多；列的更新很少（是以碎片不是問題）；使用了UTF-8這樣複雜的字元集，每個字元都使用不同的位元組數進行存儲。
  • Char：定長，根據定義的字元串長度配置設定足夠的空間。Char 适合存儲很短的字元串，或者所有值都接近同一個長度。如：存儲密碼的MD5值（這是一個定長的值）；經常變更的資料（定長的 Char 類型不容易産生碎片）；非常短的列，比如用 char(1) 來存儲隻有Y和N的值，如果采用單位元組字元集隻需要一個位元組，但是 varchar(1) 卻需要兩個位元組（還有一個記錄長度的額外位元組）。
• 時間類型
  • DATETIME：使用8位元組存儲空間，将日期和時間裝到格式為YYYYMMDDHHMMSS的整數中；
  • TIMESTAMP：使用4位元組存儲空間，顯示的值依賴于時區。盡量使用它，因為它的空間效率更好。

（2）優化政策

• 更小的通常更好
  • 更小的通常更快，因為它占用更小的磁盤、記憶體和cpu緩存，且處理時需要的cpu周期更小；
  • 但是要確定沒有低估需要存儲的值的範圍。
• 簡單就好
  • 簡單的資料類型操作需要更少的cpu處理周期；
  • 如：整型比字元串代價更低、MySQL内建類型（date，time，datetime）而非字元串來存儲時間、用整型存儲IP位址。
• 盡量避免使用NULL
  • 通常最好指定列為NOT NULL，除非真的需要存儲NULL值；
  • 因為如果查詢中包含可為 NULL 的列，對 MySQL 來說更難優化，因為可為 NULL 的列使得索引、索引統計和值比較都更複雜；可為 NULL 的列會使用更多的存儲空間，在MySQL裡也需要特殊處理；當可為 NULL 的列被索引時，每個索引記錄需要一個額外的位元組。

二、索引優化

（1）索引類型

• B-Tree索引：
  • 通常意味着所有值按順序存儲，并且每一個葉子葉到根的距離相等；
  • 能加快通路速度，因為存儲引擎不需要全表掃描來捕獲需要的資料，而是從索引的根節點開始進行搜尋；
  • 對索引順序存儲，是以很适合查找範圍資料
• B-Tree索引有效的查詢類型：
  • 全值比對：和索引中所有列進行比對；
  • 比對最左字首：隻使用索引第n列比對；
  • 比對列字首：隻比對某一列值的開頭部分；
  • 比對範圍值：某一列在xx和xxx之間的值；
  • 精确比對某一列，範圍比對另一列：某一列全比對，另一列範圍比對；
  • 隻通路索引的查詢：隻通路索引，不通路資料行。
• B-Tree索引的限制：
  • 如果不是按照索引的最左列查找，則無法使用索引；
  • 不能跳過索引中間的列；
  • 如果查詢中有某個列的範圍查詢，則其右邊所有列都無法使用索引優化查找；（如果範圍查詢有限，建議使用多個等于代替範圍查詢）。
• 哈希索引：
  • 基于哈希表實作，隻有精确比對索引所有列的查詢才有效；
  • 對于每一行資料，存儲引擎都會對所有索引列計算一個哈希碼，哈希索引将所有哈希碼存儲在索引中，同時在哈希表中儲存指向每個資料行的指針；
• 哈希索引的限制：
  • 隻包含哈希值和行指針，不存儲字段，是以不能使用索引中的值避免讀取行；
  • 不是按索引值順序存儲，是以不能排序；
  • 不支援部分索引列比對查找；
  • 通路哈希索引的速度非常快，除非出現很多哈希沖突，出現很多哈希沖突的話，一些索引維護的代價非常大；
• 哈希索引的應用：
  • InnoDB“自适應哈希索引”：某些索引值被引用的非常頻繁，他會在記憶體中基于B-Tree索引的基礎上建立一個哈希索引。

（2）高性能索引政策：

• 索引的優點：
  • 索引可以大大減少資料庫表的掃描量
  • 索引可以幫助伺服器避免排序和臨時表
  • 索引可以将随機I/O變成順序I/O
• 索引選擇：
  • 字首索引：使得索引更小，更快（比如要索引很長的字元串），但是無法做GROUP BY和ORDER BY操作，也無法覆寫掃描；
  • 索引列順序：經驗法則是将選擇性最高的放在最前面；
• 聚簇索引：實際上是一種資料的存儲方式
  • 資料航存放在索引的葉子結點，且資料行和相鄰的鍵值緊湊地存放在一起
  • 優點：
    • 可以将相關資料儲存在一起，減少磁盤I/O
    • 索引和資料儲存在一個B-Tree，資料通路更快
    • 使用覆寫索引的掃描時可以直接使用主鍵
  • 缺點：
    • 插入速度依賴于插入順序，最好是按照主鍵順序插入
    • 更新列代價很高
    • 插入行可能導緻頁分裂

三、查詢優化

（1）優化資料通路

• 避免查詢不需要的記錄：使用limit；
• 避免多表查詢查詢所有列：不要随意使用select * ；
• 重複查詢相同資料：采用緩存；

（2）從好到壞的where條件應用

• 最佳：存儲引擎層在索引中使用where過濾不比對的記錄
• 次佳：使用索引覆寫掃描，直接從索引中過濾不需要的記錄并傳回，在伺服器層完成
• 最次：先從資料表中傳回資料，然後過濾，需要回表查詢

（3）重構查詢

• 一個複雜查詢改為多個簡單查詢
• 切分查詢：
  • 對大查詢“分而治之”，減少鎖持有的時間
  • 例如删除過期記錄，每次LIMIT 10000，否則可能一次鎖住很多資料，占滿整個事務日志，耗盡系統資源，阻塞很多小但是重要的查詢；
• 分解關聯查詢：
• 讓緩存效率更高；
• 減少鎖的競争；
• 應用層關聯便于表的拆分，更容易做到高性能和可擴充；
• 減少備援記錄查詢；

要解決資料量大的問題，是避不開資料庫優化的，下面就來介紹一些常見的資料庫優化政策

 1，表結構優化

     表結構優化是資料庫優化中最重要的，需要結合實際情況來看怎麼設計更加的優化合理

2，sql語句優化

    *sql文法優化，寫出更加便捷的sql語句

     *處理邏輯優化，如配合索引和緩存的使用

      一個常見的做法是，将涉及到大資料量的sql語句記錄下來，觀察日志，有側重點的優化

3，分庫分表

    分區是指将一張表的資料按照一定的規則分到不同的區來儲存。若一張表中有幾種類型，可以考慮分表

    舉一個例子，分區按照月份來分，将不同類型的字段分表，這麼做的好處就是增删改查資料的時候範圍已經大大縮小了

4，索引優化

  索引的原理是在進行增删改的時候就預先按照指定的字段順序排列後儲存了，在查找的時候就可以從索引找到對應的指針找到資料

  優點：查詢效率很高 缺點:每次增删改要更新索引，降低增删改的速度

5，分離活躍資料

   将活躍資料單獨存放起來

   比如登入網站，可以将活躍度高的使用者單獨存放（依據最近登入時間或者機關時間内登入次數等），查詢時先從活躍資料查找，沒有再去不活躍處查找

6，讀寫分離

   讀寫分離的本質是對資料庫進行叢集，在高并發的情況下降低單台伺服器的壓力。

  一般将寫的伺服器叫主伺服器，寫入後同步到讀伺服器（即從伺服器），并将讀請求配置設定到多個伺服器上

參考文獻：《高性能MySQL》