久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

MYSQL進階怎么學

162次閱讀
沒有評論

共計 8120 個字符,預計需要花費 21 分鐘才能閱讀完成。

自動寫代碼機器人,免費開通

這篇文章主要介紹 MYSQL 進階怎么學,文中介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們一定要看完!

文章目錄

1 影響性能的幾個方面

1.1 硬件方面

1.2 服務器系統

1.3 數據庫存儲引擎的選擇

1.4 數據庫參數配置

1.5 數據庫結構設計和 SQL 語句(重點)

2 硬件方面

2.2.1 使用傳統機器硬盤

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

2.2.3 使用固態存儲 SSD 和 PCIe 卡

2.2.4 使用網絡存儲 NAS 和 SAN

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.1 什么是 RAID

2.2.2.2 RAID 級別

2.2.2.3 RAID 級別的選擇

2.2.4.1 網絡存儲使用的場景

2.2.4.2 網絡性能的限制

2.2.4.3 網絡對性能的影響

2.1.1 如何選擇 CPU

2.1.2 內存

2.1.2.1 常用 MySQL 存儲引擎

2.1.2.2 提示

2.1.2.3 如何選擇內存

2.1 CPU 資源和可用內存大小

2.2 磁盤的配置和選擇

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 提示

雖然內存的數量是越多越好,但是對系統的性能影響是有限的。
假如我們數據庫的數據有 100G,那么內存選擇在 128G 左右就可以達到最大的性能了,這時如果所有的數據都是熱數據,那么都會緩存在內存當中,沒有必要上 256G 的內存,但是選擇更大的內存,對于操作系統等其他服務的性能也會有相應的提高,并且在短期內不用考慮升級內存的問題。

對于內存緩存的寫操作時,可以進行延緩寫入,減少數據庫的壓力。
內存在讀操作上已經有了很好的支持,在寫操作上也可以在內存上完成,我們最后都需要將數據寫入到磁盤中,雖然不能避免寫入磁盤的操作,但是我們可以對寫入操作進行延緩,將多次寫入合并成一次寫入,減輕數據庫的壓力。數據庫提供了類似的功能,可以在緩存池中將多次的寫操作合并成一次,最終寫入磁盤中。

2.1.2.3 如何選擇內存

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

組成購買升級,每個通道的內存盡量相同品牌、顆粒、頻率、電壓、校驗技術和型號。

根據數據庫大小選擇內存。

2.2 磁盤的配置和選擇

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

2.2.1 使用傳統機器硬盤

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

如何選擇傳統機器硬盤?

存儲容量

傳輸速度

訪問時間

主軸轉速

物理尺寸

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)

net.core.somaxconn = 65535
對于處于一個監聽狀態的端口,都有一個自己的監聽隊列,這個參數決定了每個端口的監聽隊列的最大長度。這個參數的默認值可能會比較小,對于很大的服務器來說是不夠的,一般會修改成 2048 或更大的值。

net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535
其中 backlog 這個參數決定了在每個網絡接口接收數據包的速率比內核處理機處理快的時候,允許被發送到隊列中的數據包的最大的數目,而另一個參數了是決定了這些還未獲得對方連接的這種請求可保存在隊中的最大數目。對于超過這個值大小的連接可能會被拋棄,所以要同時調大一些。

net.ipv4.tcp_fin_timeout = 10
這個參數是用于控制 tcp 連接處理的等待狀態的超時時間。對于連接比較頻繁的系統,通常由大量的連接數處于等待狀態,這個參數的設置就是減少連接超時的時間,加快 tcp 的回收速度。同樣有對 tcp 連接有影響的參數有以下兩個:
net.ipv4.tcp_tw_reuse = 1、net.ipv4.tcp_tw_recycle = 1
這三個參數都是主要加快 tcp 的回收,在高負載的系統下,如果 tcp 連接被占滿的話,就會出現連接數據庫 500 的錯誤,因此這三個參數的作用是很大的。

net.core.wmem_default = 87380、net.core.wmem_max = 16777216、net.core.r0mem_default = 87380、net.core.rmem_max = 16777216
以上 4 個參數決定了 tcp 連接接收和發送緩沖區大小的默認值和最大值。對于數據庫來說,應該把這幾個參數的值調整的稍微大一些。

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 探測消息。這三個參數的默認值對于一個平常系統來說稍微有點大了,所以這里分別對它們改為了小了一些。

kernel.shmmax = 4294967295
這個參數是 Linux 內核參數中最重要的參數之一,用于定義單個共享內存段的最大值。
注意:

這個參數應該設置的足夠大,以便能在一個共享內存段下容納下整個的 Innodb 緩沖池的大小。

這個值的大小對于 64 為 Linux 系統,可取的最大值為物理內存值 – 1 byte,建議值為大于物理內存段的一半,一般取直大于 Innodb 緩沖池的大小即可,可以取物理內存 – 1 byte。

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 進階怎么學”這篇文章的所有內容,感謝各位的閱讀!希望分享的內容對大家有幫助,更多相關知識,歡迎關注丸趣 TV 行業資訊頻道!

向 AI 問一下細節

正文完
 
丸趣
版權聲明:本站原創文章,由 丸趣 2023-12-04發表,共計8120字。
轉載說明:除特殊說明外本站除技術相關以外文章皆由網絡搜集發布,轉載請注明出處。
評論(沒有評論)
主站蜘蛛池模板: 锡林浩特市| 浦县| 土默特左旗| 亚东县| 乌苏市| 郸城县| 含山县| 游戏| 观塘区| 望江县| 怀柔区| 绥江县| 若羌县| 兴宁市| 云霄县| 明溪县| 灵宝市| 怀远县| 闽清县| 连平县| 连州市| 绥德县| 余庆县| 皮山县| 漯河市| 崇阳县| 呼伦贝尔市| 德安县| 黄浦区| 宜川县| 琼中| 个旧市| 新泰市| 绥芬河市| 洱源县| 梁河县| 犍为县| 湛江市| 临海市| 曲阜市| 高青县|