大數據學習的MYSQL進階

278

大數據學習的MYSQL進階

免費學習推薦：mysql視頻教程

文章目錄

  • 1 影響性能的幾個方面
    • 1.1 硬件方面
    • 1.2 服務器系統
    • 1.3 數據庫存儲引擎的選擇
    • 1.4 數據庫參數配置
    • 1.5 數據庫結構設計和SQL語句（重點）
  • 2 硬件方面
    • 2.1 CPU資源和可用內存大小
      • 2.1.1 如何選擇CPU
      • 2.1.2 內存
        • 2.1.2.1 常用MySQL存儲引擎
        • 2.1.2.2 提示
        • 2.1.2.3 如何選擇內存
    • 2.2 磁盤的配置和選擇
      • 2.2.1 使用傳統機器硬盤
      • 2.2.2 使用RAID增強傳統機器硬盤的性能
        • 2.2.2.1 什么是RAID
        • 2.2.2.2 RAID級別
          • 2.2.2.2.1 RAID 0
          • 2.2.2.2.2 RAID 1
          • 2.2.2.2.3 RAID 5 —— 常見的RAID組別
          • 2.2.2.2.4 RAID 10 —— 常用的RAID組別
        • 2.2.2.3 RAID級別的選擇
      • 2.2.3 使用固態存儲SSD和PCIe卡
      • 2.2.4 使用網絡存儲NAS和SAN
        • 2.2.4.1 網絡存儲使用的場景
        • 2.2.4.2 網絡性能的限制
        • 2.2.4.3 網絡對性能的影響
    • 2.3 總結
  • 3 操作系統對性能的影響
    • 3.1 centos系統參數優化
  • 4 文件系統對性能的影響
  • 5 MySQL體系結構

1 影響性能的幾個方面

1.1 硬件方面

通常個人計算機速度慢，我們都會說是因為電腦硬件的問題，通常是CPU，內存，磁盤IO等因素，因此在服務器上也會出現這個問題。

1.2 服務器系統

一般個人電腦的操作系統都是windows，不同版本的windows系統的性能都不相同，或者配置了某一些參數導致性能的不同。這對于服務器系統也是一樣，參數的設置也會影響服務器性能。

1.3 數據庫存儲引擎的選擇

MySQL具有插件式存儲引擎，可以根據不同的業務需求選擇不同的存儲引擎。
而不同的存儲引擎也有不同的特點：

  • MyISAM：不支持事務，表級鎖。
  • InnoDB：事務級存儲引擎，完美支持行級鎖，事務ACID特性。

1.4 數據庫參數配置

對于不同存儲引擎，它的參數配置都不盡相同，有些參數對存儲引擎的影響是微乎其微，但有些參數卻對性能起著決定性作用。因此我們會根據所選的存儲引擎和不同的業務需求，對參數的優化也是很重要的。

1.5 數據庫結構設計和SQL語句（重點）

我們在進行數據庫結構設計的時候應該考慮到今后我們要在數據庫上執行怎樣的sql語句，來對表結構進行查詢和更新，只有這樣才能設計出符合要求的表結構。
對于慢查詢，是導致性能低下的罪魁禍首，而它就是由于我們對數據庫表結構設計不合理而產生的。而對于這類sql來說，也是最難優化的，因為項目一旦上線，就很難對數據庫表結構進行修改。

因此我們優化數據庫性能的重點在于：

  • 數據庫表結構設計

  • SQL語句的編寫和優化

下面具體對每一個方面進行詳細的說明。

2 硬件方面

2.1 CPU資源和可用內存大小

2.1.1 如何選擇CPU

通常在選擇CPU的時候，我們都希望CPU的頻率和核心數量兩者都盡量高，但由于成本或各種因素，往往只能迫使我們選擇其中的一種。那我們應該怎樣選擇最優的方案？因此，在購買CPU時我們需要注意幾點問題：

  • 我們的應用時CPU密集型的嗎？
    • 如果我們的應用為CPU密集型的話，要加快sql的處理速度，顯然我們需要更好的CPU，而不是更多的CPU。
    • 對于當前的MySQL而言，還不支持duoCPU對同一SQL并發處理。
  • 我們系統的并發量如何？
    • 如果我們系統需要更多吞吐量，那么我們的CPU則是越多越好。假設我們有40個CPU，那我們是不是可以同時處理40個SQL了呢。
    • 衡量數據庫處理能力的指標：QPS，指的是同時處理SQL的數量。但這個指標是在1s中處理SQL的數量，但上一點闡述的同時處理是在納秒級的維度上。
    • MySQL通常是使用在web應用上的，往往并發量比較大，這時CPU數量比CPU頻率更為重要。
  • 我們所使用的MySQL的版本
    • 在5.0版本之前，MySQL對多核的CPU支持是并不好，對系統的限制是很嚴重的，在現在5.6，5.7版本上，對多核CPU的支持已經有了很大的改善。因此推薦使用最新版的MySQL版本，以達到更好的性能。
  • 選擇32位還是64位的CPU？
    • 目前服務器的CPU默認都是64位架構的，但是要注意，要檢查好系統是否在64位上裝了32位的服務器版本，這會嚴重影響服務器性能。

2.1.2 內存

內存的大小直接影響數據庫的性能。目前內存的效率要遠遠高于磁盤。因此把數據緩存到內存中，可以大大提高服務器性能。

2.1.2.1 常用MySQL存儲引擎

有兩種常用的存儲引擎：MyISAM和InnoDB。
MyISAM：
索引存儲在內存中，數據保存在硬盤中。
大數據學習的MYSQL進階
InnoDB：
索引和數據都保存在內存中，從而提高數據庫的運行效率。
大數據學習的MYSQL進階

2.1.2.2 提示

  1. 雖然內存的數量是越多越好，但是對系統的性能影響是有限的。
    假如我們數據庫的數據有100G，那么內存選擇在128G左右就可以達到最大的性能了，這時如果所有的數據都是熱數據，那么都會緩存在內存當中，沒有必要上256G的內存，但是選擇更大的內存，對于操作系統等其他服務的性能也會有相應的提高，并且在短期內不用考慮升級內存的問題。
  2. 對于內存緩存的寫操作時，可以進行延緩寫入，減少數據庫的壓力。
    內存在讀操作上已經有了很好的支持，在寫操作上也可以在內存上完成，我們最后都需要將數據寫入到磁盤中，雖然不能避免寫入磁盤的操作，但是我們可以對寫入操作進行延緩，將多次寫入合并成一次寫入，減輕數據庫的壓力。數據庫提供了類似的功能，可以在緩存池中將多次的寫操作合并成一次，最終寫入磁盤中。

2.1.2.3 如何選擇內存

  1. 盡量使用主板能夠支持最大頻率的內存

  2. 組成購買升級，每個通道的內存盡量相同品牌、顆粒、頻率、電壓、校驗技術和型號。
  3. 根據數據庫大小選擇內存。

2.2 磁盤的配置和選擇

雖然內存對數據庫性能起到很大的作用，但是我們不能忽略IO子系統對性能的影響。目前我們常用的磁盤選擇有以下4種：

2.2.1 使用傳統機器硬盤

特點：存儲空間大，價格低，使用最多，最常見，讀、寫較慢

  • 如何選擇傳統機器硬盤？
  1. 存儲容量
  2. 傳輸速度
  3. 訪問時間
  4. 主軸轉速
  5. 物理尺寸

2.2.2 使用RAID增強傳統機器硬盤的性能

2.2.2.1 什么是RAID

RAID是磁盤冗余隊列的簡稱（Redundant Arrays of Independent Disks），簡單來說RAID的作用就是把多個容量較小的磁盤組成一組容量更大的磁盤，并提供數據冗余來保證數據完整性的技術。

2.2.2.2 RAID級別

2.2.2.2.1 RAID 0

RAID 0 是最早出現的RAID模式，也稱之為數據條帶。是組件磁盤陣列中最簡單的一種形式，只需要2塊以上的硬盤即可，成本低，可以提高整個磁盤的性能和吞吐量。RAID 0沒有提供冗余或錯誤修復能力，但是實現成本是最低的。但在考慮到數據恢復和可靠性因素，RAID 0成為了成本最高的配置，因為RAID 0中沒有冗余，并且數據在損壞的概率在當個磁盤中的還要高。因為數據在任意一個磁盤中損壞都會造成數據的丟失。比如由3塊磁盤組成的RAID 0，其損壞的幾率是單個硬盤的3倍。
因此RAID 0適用于不會單一丟失數據的情況，比如：可以隨時可以從其他數據庫克隆的備庫或者某些只需一次性使用的數據庫。
大數據學習的MYSQL進階
簡單來說，RAID 0就是將硬盤串聯在一起，形成更大的磁盤，比如：
大數據學習的MYSQL進階
并且在并發的過程中，可以達到相當于單個硬盤3倍的性能。

2.2.2.2.2 RAID 1

RAID 1 又稱磁盤鏡像，原理是把一個磁盤的數據鏡像到另一個磁盤上，也就是說數據在寫入一塊磁盤的同時，會在另一塊限制的磁盤上生成鏡像文件，在不影響性能情況下最大限度的保證系統的可靠性和可修復性。
大數據學習的MYSQL進階
它與RAID 0不同的地方在，中間的地方畫上了一個等于號。兩個磁盤的數據都是一樣的，具備良好的冗余能力，但成本會相應的提高，當出現磁盤故障的情況下也可以正常運行，但需要即使更換故障的磁盤，否則系統也會崩潰。
在更換新的磁盤后，數據的同步需要消耗很多時間，雖然不會對數據的訪問造成影響，但系統的性能是會有所下降的。
RAID 1在很多情況下，可以提供很好的讀性能，并且在不同磁盤間冗余數據，因此數據冗余性很好。RAID 1在讀上比RAID 0 要好，因此比較適合在存放日志或類似的工作。

2.2.2.2.3 RAID 5 —— 常見的RAID組別

RAID 5 又稱之為分布式奇偶校驗磁盤陣列。通過分布式奇偶校驗塊把數據分散到多個磁盤上，這樣如果任何一個盤數據失效，都可以從奇偶校驗塊中重建。但是如果兩塊磁盤失效，則整個卷的數據都無法恢復。
大數據學習的MYSQL進階
可見，每個磁盤中分別有Dp，Cp，Bp，Ap，如果其中一塊磁盤出現問題，則可以通過其他三塊磁盤的數據和奇偶校驗值重新計算出磁盤的數據。
對于RAID 0和RAID 1而言，這是最經濟的冗余配置，因為整個陣列配置只需要1塊磁盤的容量就可以了。
在RAID 5上寫速度較慢，因為每次寫都要在磁盤之間進行2次讀和2次寫，以計算存儲校驗位的數值，但是，隨機讀和順序讀都很快，因為在讀取的時候不需要計算奇偶校驗位，因此RAID 5 更適合以讀為主的數據庫業務。
RAID 5發生的最大問題是在磁盤失效的時候，因為數據需要重新分配到其他磁盤上，這將會嚴重影響磁盤的性能，所以使用RAID 5最好使用在重讀的情況下。

2.2.2.2.4 RAID 10 —— 常用的RAID組別

RAID 10又稱分片的鏡像。它是對磁盤先做RAID 1之后對兩組RAID 1的磁盤再做RAID 0，所以對讀寫都有良好的性能，相對于RAID 5重建起來更簡單，速度也更快。
大數據學習的MYSQL進階
在RAID 10上，如果損壞了一個硬盤，那么對性能會造成嚴重的影響，因為在讀寫過程中，本來可以在兩塊相鄰的磁盤中同時讀取，如果損壞了一塊，那么只能從單臺磁盤進行讀取，因此最壞的情況下，我們的性能會降低50%。

2.2.2.3 RAID級別的選擇

等級 特點 是否冗余 盤數 讀 寫
RAID 0 便宜，快速，危險 否 N 快 快
RAID 1 高速讀，簡單，安全 有 2 快 慢
RAID 5 安全，成本折中 有 N+1 快 取決于最慢的盤
RAID 10 貴，高速，安全 有 2N 快 快

2.2.3 使用固態存儲SSD和PCIe卡

固態存儲又稱為閃存。
特點：

  • 相比機械磁盤固態磁盤有更好的隨機讀寫性能
  • 相比機械磁盤固態磁盤有更好的支持并發
  • 相比機械磁盤固態磁盤更容易損壞

SSD特點：

  • 使用SATA接口，可以替換傳統磁盤而不需任何改變
  • SATA接口的SSD同樣支持RAID技術

固態存儲PCIe卡特點：

  • 無法使用SATA接口，需要獨特的驅動和配置
  • 價格相對于SSD更貴，但是性能比SSD更好

固態存儲的使用場景

  • 適用于存在大量隨機I/O的場景
  • 使用解決單線程負載的I/O瓶頸

2.2.4 使用網絡存儲NAS和SAN

SAN(Strorage Area Network) 和 NAS(Network-Attached Storage) 是兩種外部文件存儲設備加載到服務器上的方法。

SAN：
SAN設備通過光纖連接到服務器，設備通過塊接口訪問，服務器可以將其當作硬盤使用。

大數據學習的MYSQL進階
SAN的特點：
大數據學習的MYSQL進階
NAS：
NAS設備使用網絡連接，通過基于文件的協議如NFS或SMB來訪問。

2.2.4.1 網絡存儲使用的場景

適合使用在數據庫備份。

2.2.4.2 網絡性能的限制

網絡性能的限制主要是延遲和帶寬。

2.2.4.3 網絡對性能的影響

  • 網絡帶寬對性能的影響
  • 網絡質量對性能的影響
    建議：
    • 采用高性能和高帶寬的網絡接口設備和交換機
    • 對多個網卡進行綁定，增強可用性和帶寬
    • 盡可能的進行網絡隔離

2.3 總結

CPU：

  • 64位的CPU一定要工作在64位的系統下
  • 對于并發比較高的場景CPU的數量比頻率重要
  • 對于CPu密集型場景和復雜SQL則頻率越高越好

內存：

  • 選擇主板所能使用的最高頻率的內存
  • 內存的大小對性能很重要，所以盡可能的大

I/O子系統：

  • PCIe -> SSD -> RAID10 -> 磁盤 -> SAN

3 操作系統對性能的影響

MySQL適合的操作系統：Windows，FreeBSD，Solaris，linux

3.1 CentOS系統參數優化

內核相關參數（/etc/sysctl.conf）

  1. net.core.somaxconn = 65535
    對于處于一個監聽狀態的端口，都有一個自己的監聽隊列，這個參數決定了每個端口的監聽隊列的最大長度。這個參數的默認值可能會比較小，對于很大的服務器來說是不夠的，一般會修改成2048或更大的值。
  2. net.core.netdev_max_backlog=65535
    net.ipv4.tcp_max_syn_backlog=65535
    其中backlog這個參數決定了在每個網絡接口接收數據包的速率比內核處理機處理快的時候，允許被發送到隊列中的數據包的最大的數目，而另一個參數了是決定了這些還未獲得對方連接的這種請求可保存在隊中的最大數目。對于超過這個值大小的連接可能會被拋棄，所以要同時調大一些。
  3. net.ipv4.tcp_fin_timeout = 10
    這個參數是用于控制tcp連接處理的等待狀態的超時時間。對于連接比較頻繁的系統，通常由大量的連接數處于等待狀態，這個參數的設置就是減少連接超時的時間，加快tcp的回收速度。同樣有對tcp連接有影響的參數有以下兩個：
    net.ipv4.tcp_tw_reuse = 1、net.ipv4.tcp_tw_recycle = 1
    這三個參數都是主要加快tcp的回收，在高負載的系統下，如果tcp連接被占滿的話，就會出現連接數據庫500的錯誤，因此這三個參數的作用是很大的。
  4. net.core.wmem_default = 87380、net.core.wmem_max = 16777216、net.core.r0mem_default = 87380、net.core.rmem_max = 16777216
    以上4個參數決定了tcp連接接收和發送緩沖區大小的默認值和最大值。對于數據庫來說，應該把這幾個參數的值調整的稍微大一些。
  5. net.ipv4.tcp_keepalive_time = 120、net.ipv4.tcp_keepalive_intvl = 30、net.ipv4.tcp_keepalive_probes = 3
    以上三個參數用于減少失效連接所占用的tcp系統資源的數量，加快資源回收的效率，net.ipv4.tcp_keepalive_time是表示tcp發送tcp_keepalive探測消息的時間的間隔，單位為秒， 用于確認tcp連接是否有效。net.ipv4.tcp_keepalive_intvl用于當探測這個tcp連接沒有反應后，重新發送探測消息的時間間隔，單位為秒，net.ipv4.tcp_keepalive_probes表示在認定tcp連接失效之前，需要發送多少個tcp_keepalive探測消息。這三個參數的默認值對于一個平常系統來說稍微有點大了，所以這里分別對它們改為了小了一些。
  6. kernel.shmmax = 4294967295
    這個參數是Linux內核參數中最重要的參數之一，用于定義單個共享內存段的最大值。
    注意：
    • 這個參數應該設置的足夠大，以便能在一個共享內存段下容納下整個的Innodb緩沖池的大小。
    • 這個值的大小對于64為Linux系統，可取的最大值為物理內存值 – 1 byte，建議值為大于物理內存段的一半，一般取直大于Innodb緩沖池的大小即可，可以取物理內存 – 1 byte。
  7. vm.swappiness = 0
    這個參數當內存不足時會對性能產生比較明顯的影響。這個參數就是告訴Linux系統內核除非虛擬內存完全滿了，否則不要使用交換區。
    Linux系統內存交換分區：
    在Linux系統安裝時都會有一個特殊的磁盤分區，稱之為系統交換分區。如果我們使用free -m在系統中查看可以看到類似下面的內容，其中swap就是交換分區。當操作系統因為沒有足夠的內存時就會將一些虛擬內存寫到磁盤的交換區中這樣就會發生內存交換。
    在MySQL服務所在的Linux系統上完全禁用交換分區，會帶來以下兩點風險：
    • 降低操作系統的性能
    • 容易造成內存溢出，崩潰，或都被操作系統Kill掉

增加資源限制（/etc/security/limit.conf）
limit.conf這個文件實際上時Linx PAM也就是插入式認證模塊的配置文件。
其中比較重要的參數配置就是打開文件數的限制。
大數據學習的MYSQL進階
結論：把可打開的文件數量增加到了65535個以保證可以打開足夠多的文件句柄。
注意：這個文件的修改需要重啟服務器后生效。

磁盤調度策略（/sys/block/devname/queue/scheduler）
可以使用命令cat /sys/block/sda/queue/scheduler查看當前磁盤所使用的調度策略。下面的noop anticipatory deadline [cfq]為系統默認的cfq調度策略。
在MySQL數據庫服務下，cfq并不合適，是由于在MySQL工作過程中，cfq會在隊列中插入一些不必要的請求，導致很差的響應時間。
大數據學習的MYSQL進階
除了cfq調度策略，還有以下幾種策略：
noop（電梯式調度策略）：
大數據學習的MYSQL進階
deadline（截止時間調度策略）：
大數據學習的MYSQL進階
anticipatory（預料I/O調度策略）：
大數據學習的MYSQL進階
我們可以輸入以下命令來改變磁盤的調度策略：
echo schedulerName > /sys/block/sda/queue/scheduler
如：echo deadline > /sys/block/sda/queue/scheduler

4 文件系統對性能的影響

推薦使用XFS文件系統，在EXT3和EXT4下需要配置以下參數：
大數據學習的MYSQL進階
EXT3/4系統的掛載參數（/etc/fstab）：

  • data=writeback | ordered | journal
    這個參數一共有三個可選擇的值，writeback表示只有元數據寫入到日志，元數據寫入和數據寫入并不是同步的。這是一種最快的配置，因為InnoDB原本有自己的事務日志，所以通常是InnoDB最好的選擇。ordered只會記錄元數據，但提供了一些一致性的保證，在寫元數據之前，會先寫數據，使它們保持一致，這個選項比writeback稍微慢一點，但出現崩潰會更加安全。journal提供了原子日志的行為，在數據寫入到最終的日志之前，將記錄到日志中。這個選項對于InnoDB顯然是沒有必要的，也是三種中最慢的一種。
  • noatime、nodiratime
    這兩個選項用于記錄文件的訪問時間和讀取目錄的時間。設置了這兩個參數可以減少一些寫的操作。系統在讀取文件和目錄時也不必寫操作來記錄以上兩個時間。
    下面是文件/dev/sda1/ext4中的一些配置：
    noatime,nodiratime,data=writeback 1 1

5 MySQL體系結構

體系結構在最上層的叫做客戶端，這一層代表了可以通過mysql連接協議連接到mysql的客戶端，比如說php，Java，C API，.Net以及ODBC，JDBC等，從這里可以看出，這一層并不是mysql體系結構所特有。大多數CS架構的服務都是采用了這一種體系結構。這一層主要是完成了連接處理，授權認證和安全等一些功能。每個連接到mysql的客戶端都在服務器的進程中擁有一個線程，這個連接的查詢只會在這個線程中進行執行，也就是我們前面說到的，每個連接的查詢只用到一個CPU的核心。
那么這個體系的第二層，大多數的mysql核心服務都在這一層中，如下圖所示。
大數據學習的MYSQL進階
我們常用的DDL或者DML語句都是在這一層上定義的。但是我們只要記住一點就可以了，所有跨存儲引擎的功能都是在這一層中實現的，因為這一層也被稱之為服務層。
我們的結構體系的第三層是存儲引擎層，mysql是一款非常優秀的開源數據庫，其中定義了一系列了存儲引擎的接口，只要符合存儲引擎的要求，我們就可以對mysql開發出一款完全符合自己需要的存儲引擎，比如我們常用的InnoDB，目前mysql支持的存儲引擎有很多，如下圖所示：
大數據學習的MYSQL進階
注意：存儲引擎是針對于表的而不是針對于庫的（一個庫中的不同表可以使用不同的存儲引擎）
下面我們選一些比較常用的存儲引擎進行簡單的說明，mysql所使用的存儲引擎會對數據庫的性能產生直接的影響，還希望各位能仔細的了解存儲引擎的一些特點，完了之后才使用存儲引擎。

更多相關免費學習推薦：mysql教程(視頻)

? 版權聲明
THE END
數據庫
# linux# 數據庫# php# mysql# windows# sql# centos# 接口# 并發# Java# 架構# 線程# 分布式# echo# 結構體
喜歡就支持一下吧
點贊8 分享