MySQL全局優化

1 硬件層面優化

1.1 CPU優化

1.2 內存優化

1.3 存儲優化

1.4 網絡優化

2 系統配置優化

2.1 操作系統配置

2.2 MySQL服務配置

3 庫表結構優化

4 SQL及索引優化

mysql可以從四個層面考慮優化，分別是

硬件
系統配置
庫表結構
SQL及索引

從成本和優化效果來看，從以上四方面優化如下

1 硬件層面優化

1.1 CPU優化

選擇高性能多核處理器，可以有效提升高并發處理能力

1.2 內存優化

MySQL InnoDB存儲引擎使用緩沖池緩存數據頁和索引，足夠多的內存可以讓更多的數據緩存，減少磁盤I/O操作
增加物理內存同時需要調整MySQL服務配置innodb_buffer_pool_size，一般設置為物理內存70%~80%

1.3 存儲優化

使用SSD，提供更好地磁盤IO能力
如果不能完全使用SSD替換，可以考慮部分替換
比如將redo日志，undo日志，binlog日志等重要日志存儲路徑指向SSD磁盤

1.4 網絡優化

提高網絡吞吐能力
減少網絡傳輸

2 系統配置優化

2.1 操作系統配置

增加文件描述符限制ulimit -n
tcp參數調優

參數	作用	影響
tcp_window_scaling	允許網絡連接兩端使用比標準創建大小(65535字節)更大的接收窗口	對于跨廣域網或者數據中心間的數據傳輸非常有用可以減少由于網絡延遲造成的傳輸瓶頸
net.ipv4.tcp_fastopen	允許三次握手期間傳輸數據減少了建立新連接的時間	對于頻發短連接場景有利
tcp_keepalive_time	tcp連接多久沒有活動后開始發送保活探測包	適當調整該配置可以幫助更快地檢測到斷開的連接避免長時間占用資源等待無響應的客戶端避免設置太短產生不必要的流程
tcp_tw_reuse	允許將TIME_WAIT狀態的套接字用于新的相同四元組連接	可以更快復用TIME_WAIT狀態端口

選擇合適的文件系統，比如ext4或者xfs
禁用 atime 更新，減少不必要的磁盤寫入

2.2 MySQL服務配置

參數	說明
max_connections	最大連接數連接的創建意味需要分配系統資源，內存和文件描述符等當連接建立時分配內存=線程棧空間 (thread_stack)+基本的連接管理結構（較小且固定）當執行全表掃描時分配讀緩沖區(read_buffer_size) 當執行沒有索引的聯接查詢時分配連接緩沖區（join_buffer_size）當需要排序操作時分配排序緩沖區（sort_buffer_size）當需要使用臨時表時分配臨時表緩沖區（tmp_table_size, max_heap_table_size），如果需要臨時表超過該內存大小時，會使用磁盤存儲臨時表如果系統內存不足時，將會使用磁盤swap內存，導致性能降低因此需要設置合適的連接數
max_user_connections	單個用戶允許的最大連接數
back_log	暫存的連接數，超過最大連接數小于該設置值時不立即失敗，而是等待資源釋放
wait_timeout	jdbc連接空閑一定時間后斷開連接
interactive_timeout	mysql client連接空閑一定時間后斷開
sort_buffer_size	排序緩沖區，可以加速order by或group by 給每個連接分配排序緩沖區
join_buffer_size	表關聯緩沖區，當表關聯不走索引時，使用到該緩沖區，將驅動表一部分數據讀取到該緩沖區，然后與被驅動表進行關聯查詢，查詢完成后，清理緩沖區，繼續將驅動表剩余數據讀取到緩沖區進行關聯查詢給每個連接分配表關聯緩沖區
innodb_thread_concurrency	innodb并發線程數默認值為0，表示不限制通常設置cpu核心數或者核心數的2倍
innodb_buffer_pool_size	innodb緩沖區大小一般為物理內存的70%~80%
innodb_lock_wait_timeout	行鎖鎖定時間默認值50s
innodb_flush_log_at_trx_commit	redo日志落盤時機設置為0：表示每次事務提交時都將redo日志寫入redo日志緩沖區，數據庫宕機時可能會丟失數據設置為1時（默認值），表示每次事務時都會將redo日志持久化到磁盤，數據最安全，不會因為數據庫或者系統宕機導致數據丟失，但是性能差一點設置為2時，表示每次提交事務時都只是將redo日志寫到操作系統緩存（page cache）中，這種情況數據庫宕機不會丟失數據，操作系統宕機的話，如果page cache中的數據沒來的及寫入磁盤文件的話就會丟失數據
sync_binlog	binlog落盤時機為0時（默認），表示每次提交事務只寫到os 緩存page cache中，由操作系統自行判斷什么時候執行fsync寫入磁盤，服務器宕機時有可能丟失數據為1時，表示每次提交事務都會執行fsync寫入磁盤當>1時，表示每次提交事務都寫入到os緩存page cache中，當積累N個事務后調用fsync寫入磁盤，服務其宕機時最多丟失N個事務

如何判斷服務器的內存達到瓶頸？
查看服務器狀態，得到命中innodb緩存的命中率，命中率過小時說明緩沖中的數據被頻繁的交換

 show global status like 'innodb%read%'\G;

參數	說明
nnodb_buffer_pool_reads	從物理磁盤讀取頁的次數
nnodb_buffer_pool_read_ahead	預讀的次數
nnodb_buffer_pool_read_ahead_evicted	預讀的頁但是沒有后續被讀取到緩沖池的頁替換的頁的數量用于判斷預讀的效率
nnodb_buffer_pool_read_requests	從緩沖池中讀取頁的次數
nnodb_buffer_pool_read_requests	總共讀入的字節數
nnodb_data_reads	發起讀取的次數，每次讀取可能讀取多個頁

3 庫表結構優化

選擇合適的字段類型
選擇合適的字段大小
選擇合適的存儲引擎
大表拆小表

4 SQL及索引優化

參見索引優化章節

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/80929.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/80929.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/80929.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！