MySQL設計與優化

kinnylee發表於2018-09-06
MySql優化

前言

  • 怎麼設計優雅的表結構？指導原則是什麼？
  • 索引為什麼那麼快？底層為什麼要用B+樹？
  • 怎麼設計好的索引? 怎麼優化索引？
  • 常用系統引數代表什麼意思？怎麼優化引數？
  • mysql優化手段有哪些？

目錄

基本概念

mysql概述

innodb引擎架構

mysql設計

mysql優化

總結

一. 基本概念

1. 關係模型

  • 一對一
  • 一對多
  • 多對多

MySQL設計與優化

2. 關係型資料庫

依賴關係模型建立的資料庫，用一個二維表格及其關係組成的資料組織，最大的特點是事務的一致性

3. 非關係型資料庫

基於非關係模型的資料庫，非關係模型包括

  • 列模型：Hbase
  • 鍵值對模型：redis
  • 文件型模型：mongodb（聚合型資料庫）

4. 關係VS非關係

比較項 SQL NoSQL
事務一致性 強 弱
擴充套件性 難 易
高併發讀寫效率 低 高
實時性 高 低
資料一致性 高 低
冗餘 低 高

5. 冗餘

同一資訊的重複儲存，叫做冗餘

  • 低階冗餘：欄位的重複
  • 高階冗餘：欄位的派生：比如總額=單價*數量

形成原因

  • 表重複
  • 屬性重複
  • 元組重複

冗餘的壞處

  • 為了保證資料一致性，要維護冗餘欄位的成本高
  • 可能導致資料不一致

6. 正規化

作用：消除或減少冗餘，增進資料一致性。設計出高效優雅的資料庫

分類：

  • 第一正規化（1NF）：要求屬性不可分，具有原子性。下圖的屬性被分開來，關係型資料庫設計不出來這種表

MySQL設計與優化

  • 第二正規化（2NF）：要求記錄具有唯一性
  • 第三正規化（3NF）：要求欄位不能有冗餘，任何欄位不能由其他欄位派生
  • BC正規化（BCNF）：主屬性不依賴於主屬性
  • 第四正規化（4NF）：要求把同一表內的多對多關係刪除
  • 第五正規化（5NF）：從最終結構建立原始結構

最佳實踐（中庸版）

  • 一般，一個資料庫設計符合3NF或BCNF就可以了
  • 過於正規化化甚至會對資料庫的邏輯可讀性和使用效率起到阻礙
  • 適當增加冗餘，達到以空間換時間的目的

最最佳實踐（實踐版）

  • 除非你真的有足夠證據證明按照規範正規化設計資料庫會有效能問題而且這個效能問題無法解決，或者有足夠證據證明你寫入的資料是永遠不會被修改的，否則不要輕易用效能作為藉口反正規化設計。
  • 資料庫對於表連線的處理能力其實非常強大，關聯幾個十幾個表，只要資料庫結構設計合理，其實是非常輕鬆的事情。
  • 在資料量沒達到十萬級別的時候，冗餘根本沒必要。如果資料量和併發數都上來後，會在前面加一個ETL層，其中有Join好的AB（可以是資料庫，也可以是別的快取層）。ETL層和資料庫層之間用MQ打通資料同步機制

7. 事務

1. 概念：

指對系統進行的一個邏輯單元，包括一組操作。會把資料庫從一種一致狀態切換為另一種一致狀態。普通檔案系統是沒有此特性的。

2. 事務需具備的特性（ACID）

  • 原子性（Atomic）：要麼完全執行，要麼完全不執行，允許回滾
  • 一致性（Consistency）：事務開始和結束的中間狀態不能被其他事務看到
  • 隔離性（Isolation）：併發事務直接的影響程度，比如一個事務會不會讀到另一個未提交的事務修改的資料
  • 永續性（Durability）：事務提交後就保證不會丟失

3. 事務併發可能出現的問題

  • 髒讀：事務A修改了資料，但是未提交，事務B讀到了事務A未提交的更新結果，A提交失敗，B就讀到髒資料
  • 不可重複讀：事務B在事務A提交前讀到的結果，和提交後讀到的結果可能不同。比如，事務B在事務A提交前讀到的結果，和提交後讀到的結果可能不同。不可重複讀出現的原因就是事務併發修改記錄
  • 幻讀：在同一個事務中，同一個查詢多次返回的結果不一致。事務A新增了一條記錄，事務B在事務A提交前後各執行了一次查詢操作，發現後一次比前一次多了一條記錄。幻讀是由於併發事務增加記錄導致的

4. 事務的隔離級別（由低到高）

  • RR（read uncommitted）：最低的隔離級別，什麼都不需要做。所有的併發事務問題都會發生。
  • RC(read committed)：只有在事務提交後，其更新結果才會被其他事務看見。可以解決髒讀問題。
  • RR(repeated read)：同一事務中，對同一份資料的讀取結果總是相同的。可以解決髒讀、不可重複讀。mysql預設級別，在此基礎上做了優化
  • Serialization：序列化。隔離級別最高，犧牲併發性。可以解決併發事務的所有問題

5. 各種資料庫對事務的支援情況

事務的定義極其嚴格，必須同時滿足四個特性，但是資料庫廠商處於各種目的，比如效能，並沒有嚴格滿足ACID的要求

  • mysql的NDB cluster引擎，不滿足D（永續性）
  • Oracle資料庫預設隔離級別為RC，不滿足I（隔離性）
  • mysql的InnoDB引擎，完全遵守ACID特性

二. MySQL概述

1. 特點

  • 關係型資料庫
  • 外掛式儲存引擎

2. 架構

2.1 特點

單程式多執行緒模型

2.2 儲存引擎

  • InnoDB

    • 支援事務
    • 實現sql標準的4種隔離級別，預設為RR
    • 支援行鎖
    • mysql5.5.8之後預設的儲存引擎（windows除外）

  • MyISAM：

    • 不支援事務
    • 支援全文索引
    • 只快取索引檔案，不快取資料檔案
    • mysql5.5.8之前預設的儲存引擎

  • NDB

    • 叢集儲存引擎

  • Archive

    • 支援高比例壓縮儲存

  • Memory（heap）

    • 資料全部放在記憶體中

    最新的mysql版本（8.0.12）支援的儲存引擎

    mysql儲存引擎

mysql->show engines; //檢視所有支援的儲存引擎和預設儲存引擎
複製程式碼

MySQL設計與優化

三. InnoDB引擎架構

1. 架構和邏輯儲存結構

MySQL設計與優化

  • 記憶體池的職責
    • 維護所有程式和執行緒需要訪問的資料結構
    • 快取磁碟資料
    • redo log緩衝
  • 後臺執行緒的職責
    • 負責重新整理記憶體池中的資料，保證緩衝池中的記憶體快取的是最近的資料
    • 負責將已修改的資料檔案重新整理到磁碟檔案
    • 保證資料庫異常時能恢復到正常執行狀態

2. 後臺執行緒

  • master thread：核心後臺執行緒，主要負責將緩衝池中的資料非同步重新整理到磁碟，保證資料一致性
  • io thread：負責io請求的處理
  • purge thread：回收事務提交後已經使用的undo頁
  • page clean thread: 負責髒頁的重新整理

3. 快取池

概述：一塊記憶體區域，按頁存放，每頁預設16k，通過checkpoint機制重新整理會磁碟，包含多種頁型別

快取的頁型別：

  • 索引頁
  • 資料頁
  • undo頁
  • 鎖資訊
  • ...

3. 快取如何管理

  • LRU list：改進的快取資料列表
  • free List: 空閒列表
  • flush list：髒頁列表

4. 事務

4.1 InnoDB引擎對事務的支援

  • 事務的隔離性：預設隔離級別為RR，並使用Next-Key-Lock鎖來避免幻讀的產生
  • 事務的原子性和永續性：redo log（重做日誌）來保證
  • 事務的一致性：undo log來保證（回滾操作）
   mysql->select @@tx_isolation\G; //檢視隔離級別
   mysql->select @@global.tx_isolation|G; //檢視全域性的事務隔離級別
複製程式碼

MySQL設計與優化

4.2 redo

  • 在事務提交時，記錄事務的行為到檔案系統緩衝
  • 之後呼叫fsync（重新整理頻率可調），將重做日誌寫入磁碟

4.3 undo

  • 事務的回滾操作，將資料回滾到修改之前的樣子
  • 存放在資料庫內部的特殊段中（segment），稱為undo segment
  • 恢復的是邏輯日誌，不是物理日誌：因為一頁裡面可能有很多併發的事務，不能回滾整個頁，影響別的事務
  • unduo還實現了MVCC：當使用者讀取一行時，若該記錄被其他事務佔用，當前事務科通過undo讀取之前的行版本資訊，實現非鎖定讀
  • undo log的同事會產生redo log

4.4 redo VS binlog

對比項 redo undo
操作者 InnoDB引擎層 資料庫層
生效範圍 InnoDb引擎 所有引擎
日誌內容 物理格式日誌，記錄對於每個頁的修改 邏輯日誌，記錄SQL語句
大致格式 page(2,3) offset 32, value 1,2 insert into..
寫入時機 事務提交後一次寫入 事務進行中不斷寫入
一個事務對應記錄 1條 多條，併發還會導致亂序
恢復速度 快 慢

4.5 事務的提交

1. 顯示提交

  • begin/start transaction：開啟事務，start是儲存過程專用的
  • commit：提交
  • rollback：回滾
  • savepoint：建立事務儲存點
  • settransaction 手動設定隔離級別

2. 隱式提交

以下語句提交有隱式的commit操作

  • 修改或刪除相關：alter, create, drop, rename，set password，add user，grant...
  • 管理相關：analyze, cache, check, load index, optimize, repair

4.6 innodb支援的鎖

  • 共享鎖（S Lock）:允許多個事務讀一行資料
  • 排它鎖（X Lock）:允許一個事務刪除或更新一行資料
  • 行鎖和表鎖

5. 邏輯儲存結構

5.1 架構圖

MySQL設計與優化

  • 所有資料放在tablespace中

  • tablespace由segment（段），extent（區），page（頁，block）組成

  • innodb引擎預設有一個共享表空間ibdata1

  • segment包括資料段（Leaf node segment），索引段(Non-Lefa)，回滾段(Rollback)等

  • 區由連續的頁組成，每個區大小任何時候都為1MB，1MB=64頁*每頁16KB，每頁大小可調整（需為2的倍數）

  • 頁是Innodb管理的最小單元

  • 資料按行存放，每頁最多允許16kb/2 -200 = 7992行記錄

  • 行的儲存格式有：Compact（預設），Redundant（相容舊的格式）

    頁結構

MySQL設計與優化
檢視本機行儲存格式
MySQL設計與優化
Compact格式
MySQL設計與優化

5.2 檔案結構

  • .ibd 表結構檔案
  • .frm 表資料檔案

四. 資料庫設計

1. 表關係設計

  • 1對1：在任意一張表中新增外建指向另一張表的主鍵
  • 1對多：“多”中新增一個外來鍵，指向“1”的主鍵
  • 多對多：新增一張關係表，兩個外建分別指向兩張表的主鍵

2. 表欄位設計

2.1 表欄位說明 參考

  • 數值型別的選擇

    • tinyint(num) : 長度只是一個最大顯示寬度，寬度不足前面補0，跟資料儲存範圍無關
    • char（num）: num是字元的最大長度，不是位元組。
    • 範圍是怎麼算出來的：1byte=8bit，1bit有0和1兩種可能，所以1byte可表示無符號2^8=256個數字，但是如果有符號，需要1bit儲存符號，所以只能儲存正負各2^7=128個數字，0包含在正數範圍內，所以正數最大是127
    型別 大小 範圍（有符號） 範圍（無符號） 用途
    tinyint 1位元組 (-128, 127) (0, 255) 小整數值
    smallint 2位元組 (-32768, 32767) (0, 65535) 大整數值
    mediumint 3位元組 (-2^(3x7), 2^(3x7) -1 ) (0, 2^(3*8) - 1) 大整數值
    int/integer 4位元組 .. .. 大整數值
    bigint 8位元組 .. .. 極大整數值
    float 4位元組 .. .. 單精度浮點數
    double 8位元組 .. .. 雙精度浮點數
    decimal decimal(M,D),若M>D，為M+2,否則D+2 小數

  • 字串的選擇

    型別 大小 用途
    char 0-255位元組 定長字串
    varchar 0-255位元組 變長字串
    tinyblob 0-255位元組 不超過255個字元的二進位制字串
    tinytext 0-255位元組 短文字字串
    blob 0-65535位元組 二進位制長文字資料
    text 0-65535位元組 長文字資料
    mediumblob 16M 中等長二進位制文字資料
    mediumtext 16M 中等長文字資料
    longblob 4G 極大的二進位制文字資料
    longtext 4G 極大文字資料

  • 時間型別的選擇

    型別 大小 範圍 格式 用途
    date 3位元組 1000-01-01 9999-12-31 YYYY-MM-DD 日期值
    time 3位元組 '-838:59:59' '838:59:59' HH:MM:SS 時間值
    year 1位元組 1901 2155 YYYY 年分值
    timestamp 4位元組 1970-01-01 2037年 YYYYMMDD HHMMSS 時間戳（空值或null會自動填充當前時間）,儲存數會隨時區變化而變化
    datetime 8位元組 1000-01-01 00:00:00 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 日期和時間,儲存數不會隨時區變化而變化

  • 複合型別（技術上都是字串型別）

    型別 說明
    enum 只允許從一個集合中取得一個值
    set 允許從一個集合中取得任意多個值

2.2 表欄位設計原則參考

  • 主鍵一般使用自增長欄位
  • 欄位選擇合理範圍內最小的，大大減少磁碟IO讀寫開銷，記憶體和cpu佔用率
  • 選擇相對簡單的資料型別
  • 不要使用NULL。因為MYSQL對NULL欄位索引優化不佳，增加更多的計算難度，同時在儲存與處理NULL類形時，也會做更多的工作，所以從效率上來說，不建議用過多的NULL。有些值他確實有可能沒有值，怎麼辦呢？解決方法是數值弄用整數0，字串用空來定義預設值即可
  • 在不能確定欄位需要多少字元時使用 VARCHAR 型別可以大大地節約磁碟空間、提高儲存效率。但如果確切知道字串長度,比如就在50~55之間,那就用 CHAR 因為 CHAR 型別由於本身定長的特性使其效能要高於 VARCHAR。如uuid，MD5
  • 複合型別我們一般用tinyint,更快的時間更省的空間以及更容易擴充套件

3. 索引設計

3.1 什麼是索引

  • 儲存引擎用於加快查詢速度（排好序）的一種資料結構
  • 索引會被儲存到磁碟上

3.2 索引優點

  • 能輕易將查詢效能提升幾個數量級
  • 唯一索引保證資料唯一性
  • 減少分組和排序時間

3.3 索引缺點

  • 佔用磁碟空間，大量索引可能導致比檔案還大
  • 損耗效能，增刪改查都要維護索引

3.4 索引資料結構

資料庫 索引使用的資料結構
mysql B+樹
mongodb B樹（B-樹）

B+樹的特點

  • 多叉樹，高度較低
  • 每個節點可儲存多個key
  • 非葉子節點儲存key，葉子節點儲存key和data
  • 葉子節點兩兩相連

MySQL設計與優化

為什麼是B+樹？

普通平衡樹的缺點
  • 資料量不大時，普通平衡樹（AVL樹，紅黑樹）效能極好。但是資料量巨大時，記憶體不夠用，無法將資料全部載入到記憶體中，只能放到磁碟
  • 樹的高度為LogN，導致磁碟IO次數過多影響效率
  • 調整樹的平衡是通過旋轉實現，如果不把全部資料載入進記憶體是無法完成旋轉的
B-樹的缺點
  • 非葉子節點也儲存資料，每次磁碟io資料量是固定的，每一層索引範圍小
  • 資料分散在每個節點中，不支援範圍查詢
B+樹的特有性質
  • 非葉子節點只儲存key，每一層能索引的資料更多。每次io能看到更多資料
  • 樹高度低（一般為3層左右），io次數少
  • 葉子節點兩兩相連，符合磁碟預讀特性，減少io次數
  • 範圍查詢支援良好。真正資料只儲存在葉子節點，範圍查詢只需遍歷葉子節點
  • 每個節點的大小設定為磁碟IO一次的大小（稱為頁，根據作業系統不同而定，如16k）

3.5 索引設計原則

  • 索引並不是越多越好，過多索引不僅增加磁碟空間，而且更新插入資料都要動態維護索引，影響效率
  • 經常作為where條件欄位需要建立索引
  • 資料量很少的表不要建索引，全表查詢效率比遍歷索引可能還快
  • 將使用頻率高，區分度大的列放在索引前面。範圍查詢或不等於查詢的列放在最後
  • 不同值較多的列上建立索引，在不同值較少的列上不要建立索引，比如性別欄位只有男和女，就沒必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度
  • 當唯一性是某種資料本身的特徵時，指定唯一索引。使用唯一索引需能確保定義的列的資料完整性，以提高查詢速度
  • 在頻繁排序或分組（即group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引
  • 沒有必要為同一欄位建立重疊索引
  • 選擇較短的資料型別，可以有效的減少索引的磁碟佔用，提高索引的快取效果
  • join多個表時，為join的欄位建立索引，mysql內部會優化sql語句。且join的欄位型別必須是相同的，字串的字符集也必須相同

五. 資料庫優化

1. 索引優化

1.1 索引使用原則

  • 查詢語句中必須使用獨立的列，不能含有表示式，否則不走索引

  • 符合索引由左到右生效，遇到範圍查詢就不走索引

    index(a, b, c)

    查詢語句 索引是否生效
    where a=3 是，使用了a
    where a=3 and b=5 是，使用了a, b
    where c=4 and a=3 and b=5 是，使用了a,b,c.與查詢順序無關
    where b=3 否
    where a=3 and c=4 是，只使用了a
    where a>4 and b=5 and c=3 是，只使用了a

  • 資料型別出現隱式轉換不會走索引

1.2 索引分析

explain分析

MySQL設計與優化

select_type： 查詢型別,效能由高到低
  • simple 此查詢不包含union或子查詢（最常見）
  • primary 最外層查詢
  • union union的第一個以外的查詢
  • subquery 子查詢的第一個select
  • derived 派生表的select（from字句的子查詢）
table：查詢涉及的表名（別名）
type：判斷是全表掃描還是索引掃描（很重要的欄位）
  • const/system 根據主鍵或者唯一索引查詢到，只讀取一次，速度非常快
  • eq_ref 等值引用。多表join，前表的每個結果，只能匹配到後表的一行結果，比較通常是=，查詢效率較高
  • ref 多表join，非唯一索引，或者使用了最左字首規則索引的查詢
  • range 使用索引範圍查詢，此型別下ref欄位為NULL
  • index 全索引掃描，比all稍快
  • all 全表掃描，查詢效能最差，對資料庫是災難
possible_keys：查詢可能使用的索引,設定過多的索引會影響效能
key：查詢真正使用的索引，一般一個查詢只使用一個索引
ken_len：表示where查詢使用了索引的位元組數(不包括order by/group by，也不是索引本身的長度)

該欄位可以評估組合索引是被完全使用，還是隻有最左部分被使用，越小表示索引佔用磁碟空間越小，索引更高效

  • 字串
    • char(n): n位元組長度
    • varchar(n): 如果是utf8編碼，則是3n+2, 如果是utf8mb4編碼，則是4n+2
  • 數值型別
    • tinyint: 1位元組
    • smallint：2位元組
    • mediumint：3位元組
    • int：4位元組
    • bigint：8位元組
  • 時間型別
    • date：3位元組
    • timestamp：4位元組
    • datetime：8位元組
  • 欄位屬性
    • NULL：多加1位元組
    • NOT NULL： 不用多加1位元組
ref：使用哪個列或常熟與key一起從表中選擇行
rows：掃描的行數（重要）
Extra：執行情況和描述
  • Using index 表示在索引樹種就可以查詢到所需資料，不用掃描資料檔案，說明效能不錯
  • Using where 使用了where條件限制哪些行匹配下一張表
  • Using temporary 使用臨時表儲存結果，通常發生在對不同的列進行order by，效率不高，需優化
  • Using Filesort 需要額外的排序操作，不能通過索引順序排序。查詢時cpu資源消耗大，需優化

2. 引數優化

2.1 引數說明

  • innodb_buffer_size 緩衝區大小
  • innodb_buffer_pool_instance 緩衝池例項個數
  • innodb_old_blocks_pct 讀取的頁放入緩衝區LRU的位置，預設37%
  • innodb_old_blocks_time 讀取的頁等待多久才放入LRU
  • innodb_log_buffer_size undo日誌緩衝區大小，預設8M
  • innodb_page_size 每一頁的大小
  • max_connections 最大連線數
  • key_buffer_size
  • innodb_thread_concurrency 最大併發執行緒數
  • thread_cache_size 快取的最大執行緒數
  • tmp_table_size 超過該值的用硬碟臨時表，低於改值的直接放記憶體
  • query_cache_limit 超過此大小的查詢將不快取
  • query_cache_min_res_unit 快取塊的最小大小
  • query_cache_size 查詢快取大小
  • innodb_log_buffer_size 日誌緩衝大小
  • slow_query_log = ON 開啟慢查詢
  • long_query_time = 3 超過3s的為慢查詢
  • innodb_flush_log_at_trx_commit重做日誌從緩衝重新整理到磁碟的策略：0表示不記錄redo日誌

3. 主從優化

3.1 概念

通過配置主庫和從庫，主庫負責讀取刪改，從庫負責只讀，做到讀寫分離，並根據讀寫要求的不同配置不同的系統引數

3.2 資料庫主從原理

  • 主庫開啟binlog配置，對主庫每次操作都會記錄在binlog中
  • 從庫通過io執行緒從主庫讀取binlog，傳輸到從庫
  • 從庫sql執行緒讀取binlog，並應用到從庫

3.3 主從配置（確保版本一致）

  • 主從伺服器分別新增binlog配置
  • 重啟服務
  • 檢視主庫當前記錄的日誌位置
  • 從庫配置從主庫讀取到的位置，並開啟同步

3.4 使用xtrabackup備份資料

該工具可在不停服的情況下，實現資料同步。

4. 分庫分表

1. 垂直拆分

1.1 概念：列拆分，把列比較多的表拆分為多張表

1.2 原則：

  • 把不常用的欄位單獨放在一張表
  • 把text，blob等大欄位拆分出來放在附表中
  • 經常組合查詢的列放在一張表中

2. 水平拆分

2.1 概念：行拆分，將一張表的資料拆分為多張表存放

2.2 原則：

  • 通常根據id取模存放資料
  • 部分業務邏輯可言通過地區，年份等欄位歸檔拆分（介面上限定住不讓跨年查詢）

2.3 難點及問題：樂視網分庫分表

  • 擴充套件表時，舊的資料將全部失效，無法再擴充套件
  • 跨節點join， order by group by的問題，分散式事務等
  • 如何保證id的唯一性

2.4 常用的開源元件

  • mycat
  • sharding-sphere
  • tsharing

總結

1. 本文內容串起來如下：

  • 介紹了關關係型資料庫和非關係型資料庫
  • 知道關係型資料庫最重要的特性是事務的一致性，然後介紹了事務的相關特性
  • 如何保證資料一致性：mysql底層做到RR級別事務隔離
  • 使用者設計資料庫時如何提高一致性：遵守正規化，以減少冗餘
  • 資料庫查詢效率怎麼提升：設計好的表結構和索引
  • 資料庫查詢慢的時候怎麼優化：介紹了幾種優化手段

2. 和前言中我們提到的幾個問題，簡短總結一下

怎麼設計優雅的表結構？指導原則是什麼？

大的前提是遵守正規化以減少冗餘，其次才綜合業務量設定冗餘。合理選擇欄位和建立索引

索引為什麼那麼快？底層為什麼要用B+樹？

索引是排好序的資料結構，B+樹是為了適應磁碟資料的儲存設計的結構，包括多叉結構降低高度較少io次數，非葉節點不存資料增加索引量，葉節點相連便於範圍查詢，節點大小設定為頁大小充分利用io呼叫。

怎麼設計好的索引? 怎麼優化索引？

根據查詢條件設定合適的組合索引，時常用explain分析並調整索引

常用系統引數代表什麼意思？怎麼優化引數？

略

mysql優化手段有哪些？

索引優化，引數優化，主從優化，分庫分表等等

參考文獻

  • 《mysql技術內幕（innodb儲存引擎）》
  • 《高效能mysql》

相關文章