MySQL資料庫的效能的影響
一. 伺服器的硬體的限制
二. 伺服器所使用的作業系統
三. 伺服器的所配置的引數設定不同
四. 資料庫儲存引擎的選擇
五. 資料庫的引數配置的不同
六. (重點)資料庫的結構的設計和SQL語句
1). 伺服器的配置和設定(cpu和可用的記憶體的大小)
1.網路和I/O資源
2.cpu的主頻和核心的數量的選擇
(對於密集型的應用應該優先考慮主頻高的cpu)
(對於併發量大的應用優先考慮的多核的cpu)
3.磁碟的配置和選擇
(使用傳統的機械硬碟:
特點：讀寫較慢、儲存空間大、最常見、使用最多、價格低;
工作過程：移動磁頭到磁碟表面上的正確位置;
等待磁碟的旋轉，使得所得所需的資料在磁頭之下;
等待磁碟旋轉過去，所有所需的資料都被磁頭讀出
選擇因素：儲存容量、傳輸速度、訪問時間、主軸轉速、物理尺寸)
(使用RAID增強傳統的機器硬碟的效能：
特點：利用小的磁碟組成大的磁碟並提供資料的冗餘保證資料的完整性的技術
資料庫中所使用的RAID的級別：
RAID0級別、RAID1級別、RAID5級別[分散式奇偶校驗磁碟陣列]、RAID10[分片的映象(資料庫最好的方式)]
RAID級別選擇：如下圖)
(使用固態儲存的SSD和PCI-E卡:
特點：相對於機械盤固態磁碟有更好的隨機讀寫效能;
相對於機械固態磁碟能更好的支援併發;
相對於機械固態磁碟更容易損壞
SSD:使用SATA介面可以替換傳統的磁碟而不需要任何的改變[受到介面的速度的限制];
SATA介面的SSD同樣支援RAID技術
PCI-E卡(Fusion-IO卡)：無法使用在SATA介面[需要使用獨特的驅動和配置];
價格貴,使用了cpu的資源和記憶體
使用的場景：適用於存在大量的隨機I/O的場景;
適用於解決單執行緒負載的I/O瓶頸)
(使用網路儲存NAS和SAN：
SAN[光纖接入伺服器]:大量順序讀寫操作、讀寫I/O、快取、I/O合併、隨機讀寫慢（不如本地的RAID）
NAS裝置使用網路連線，基於檔案的協議如NFS或者SMB來訪問
適合場景：資料庫的備份、)
複製程式碼
不同REAID級別的對比：
注意事項：
1.64位資料庫的版本使用32位的伺服器的版本
2.記憶體的主頻的選擇主機板所能支援的最大記憶體的頻率
複製程式碼總結：
對於cpu：
1.64位的cpu一定能夠要工作在64位的系統下
2.對於併發比較高的場景cpu的數量比頻率重要
3.對於cpu密集型的場景和複雜SQL則頻率越高越好
對於記憶體：
1.選擇主機板所能使用的最高頻率的記憶體
2.記憶體的大小對效能很重要，所以儘可能的大
I/O子系統：
1.PCIe -> SSD -> RAID10 -> 磁碟 -> SAN
複製程式碼2). 作業系統對效能的影響
Windows、FreeBSD、Solaris、Linux
centos的引數優化的設定：
(1)核心相關的引數（/etc/sysctl.conf）
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.core.wmem_defaullt = 87380
net.core.wmem_max = 16777216
net.core.rmem_defaullt = 87380
net.core.rmem_max = 16777216
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
kernel.shmmax = 4294967295
vm.swappiness = 0
(2)增加資源限制（/etc/security/limit.conf）
* soft nofile 65535
* hard nofile 65535
* 表示對所有的使用者有效
soft 指的是當前系統的生效的設定
hard 表明系統中所能設定的最大值
nofile 表示所限制的資源是開啟檔案的最大數目
65535 就是限制的數量
(3).磁碟排程策略(/sys/block/devname/queue/scheduler)
noop(電梯式排程策略）、deadline（截止時間排程策略）、anticipatory(預料I/O排程策略)
cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]
echo deadline > /sys/block/sda/queue/scheduler
複製程式碼3).MySQl的資料庫的體系
4).MySQl的資料庫的儲存引擎
(1).Mysql之儲存引擎MyISAM
組成的結構：表為MYD和MYI、frm的檔案組成
特性：併發性和鎖級別
MyISAM表支援索引型別
MyISAM表支援資料的壓縮(命令列：myisampack)
myisampack -b -f myIsam.MYI;
壓縮後的表不能進行寫操作，只能進行讀操作
修復:對資料庫中的表進行檢查並修復:
check table mytable;
repair table mytable;
myisamchk工具,修復時資料庫服務必須停止
限制：使用MySQL5.0之前時預設表的大小4G(儲存大表修改MAX_Rows和AVG_ROW_LENGTH)
使用MySQL5.0之後的版本預設支援256TB
適用的場景:非事務型的應用
只讀類的應用
空間類的應用(GPS的資料)
(2).Mysql之儲存引擎InnoDB
mysql5.5.8之後版本預設使用的儲存引擎
組成結構：通過設定innodb_file_per_table引數儲存的位置不同
ON:獨立表空間：tablename.ibd
OFF:系統表空間:ibdataX
建議：對於mysql中建議使用InnoDB的獨立表空間
特性：事務性儲存引擎
完全支援事務的儲存引擎
Redo log(儲存已經提交的事務)和Undo log(儲存未提交的事務)
InnoDB支援行級別鎖
最大程式的支援併發
行級別的鎖是由儲存引擎層實現的
鎖：共享鎖(讀鎖)、獨佔鎖(寫鎖)
表級鎖、行級鎖
阻塞：確保事務併發的正常的執行
死鎖：兩個或者兩個以上的事務執行過程中相互等待對方的資源而產生的一種異常
InnoDB狀態檢查：
show engine innodb status;
適用場景：InooDB適用於大多數OLTP應用
(3).Mysql之儲存引擎CSV
特點：資料以文字的方式儲存在檔案中
.CSV檔案儲存表的內容
.CSM檔案儲存表的後設資料如表的狀態和資料量
.frm檔案儲存表的結構的資訊
以CSV格式進行資料的儲存
所有的列必須不能為NULL的
不支援索引(不適合大表，不適合線上處理)
可以對資料檔案直接進行編輯
適用的場景：適合作為資料交換的中間表
mysql資料目錄->csv檔案->其他web程式
excel電子表格 -> csv檔案 -> mysql資料目錄
(4).Mysql之儲存引擎Archive
特點：以zlib對錶資料進行壓縮，磁碟I/O更少
資料儲存在ARZ為字尾的檔案中
只支援insert和select操作
只支援在自增的ID列上加索引
適用場景：
日誌和資料採集類的應用
(4).Mysql之儲存引擎Memory
特點：資料只儲存在記憶體中
Memory儲存引擎的I/O效率特別高
支援HASH索引和BTree索引
所有的欄位為固定長度
不支援BLOG和TEXT等大欄位
Memory儲存引擎使用表級鎖
表中儲存資料的最大值由max_heap_table_size引數決定
適用場景：用於查詢或者對映表，例如郵編和地區
用於儲存資料分析產生的中間表
用於快取週期性聚合資料的結果表
複製程式碼5).MySQl的資料庫的伺服器引數
(1).Mysql配置引數作用域
全域性引數
set global 引數名=引數值；
set @@global.引數名：=引數值；
會話引數
set[session] 引數名=引數值；
set @@session.引數名：=引數值；
(2).記憶體配置相關的引數
確定可以使用的記憶體的上限
確定MySQL的每個連線使用的記憶體
sort_buffer_size join_buffer_size
read_buffer_size read_rnd_buffer_size
確定需要為作業系統保留多少記憶體
如何為快取池分配記憶體
Innodb_buffer_pool_size
總記憶體-（每個執行緒鎖需要的記憶體*連線數）- 系統的保留記憶體
key_buffer_size
(3).I/O相關配置引數
InnoDb儲存引擎的I/O引數設定：
Innodb_log_file_size
Innodb_log_file_in_group
Innodb_log_buffer_size
Innodb_flush_log_at_trx_commit
Innodb_flush_method = O_DIRECT
Innodb_file_per_table = 1
Innodb_doublewrite = 1
MySIAM儲存引擎的I/O引數設定：
delay_key_write
OFF:每次操作後重新整理鍵緩衝中的髒塊到磁碟
ON:只對在鍵表時指定了delay_key_write選項的表使用延遲重新整理
ALL:對所有MYSIAM表都使用延遲鍵寫入
(4).安全相關配置引數
expire_logs_days 指定自動清理binlog的天數
max_allowed_packet 控制MySQL可以接受的包的大小（32M）
skip_name_resolve 禁用DNS查詢
sysdate_is_now 確保sysdate()返回確定性的日期
read_only 禁止非super許可權的使用者寫許可權
skip_slave_start 禁止Slave自動恢復
sql_mode 設定MySQL所使用的SQL模式
strict_trans_tables
no_engine_subtitutoion
no_zero_date
no_zero_in_date
only_full_group_by
(5).其他相關配置引數
sync_binlog = 1控制MySQL如何向磁碟重新整理binlog
tmp_table_size和max_heap_table_size 控制記憶體臨時表的大小(設定一致)
max_connections = 2000 控制允許的最大連線數
複製程式碼6).MySQl的資料庫的結構設計和SQL的優化
(1).過分的反正規化化為表的建立太多的列
(2).過分的正規化化造成太多的表關聯
(3).在OLTP環境中使用不恰當的分割槽表
(4).使用外來鍵保證資料的完整性
複製程式碼效能優化的順序
- 資料庫結構設計和SQL語句優化
- 資料庫的儲存引擎的選擇和引數的配置
- 系統的選擇及其優化
- 硬體升級