程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MYSQL性能調優概述

MYSQL性能調優概述

編輯：關於MYSQL數據庫

優化思路          ==>架構(服務分離,負載均衡)
            ==>單一服務器優化(系統,服務)
               ==>庫結構(表合理,索引)
                 ==>DEBUG工具查詢需要優化的地方 1 硬件優化
a>內存--大內存,大內存位寬,盡量不要用SWAP ？
b>硬盤--15000RPM,RAID5,RAID10
c> CPU--64位,高主頻,高緩存,高並行處理能力
d>網絡--標配的千兆網卡,盡可能在同一局域網,盡量避免諸如防火牆側羅等不必要的開銷
2 架構上的優化
a>縱向拆分
     eg:一台服務器同時負責web,中間件,數據庫多個角色.         若仍然無法滿足需求,可以考慮在數據庫和應用服務器之間加memcached b>橫向拆分
     eg:主從同步,讀寫分離,負載均衡,高可用性集群.
        當單一MySQL數據庫無法滿足日益增長需求時,可以考慮在數據庫這個邏輯層面          增加多台服務器,以達到穩定,高效的目的.
3 操作系統級別的優化
a>64位系統可以分給單個進程更多的內存,服務調優.
    禁用不必要啟動的服務.修改文件描述符限制,留給更多的資源給MySQL
b>文件系統調優:給數據倉庫一個單獨文件系統,推薦使用XFS,一般效率更高,更可靠
c>可以考慮在掛載分區時啟用notime選項
4 數據庫服務的優化
a>使用Linux/bsd操作系統進行編譯安裝,對編譯參數進行性能優化,精簡不必要啟用的功能
b>合適的應用程序接口
c>標尺每個表都不要太大,對大表做橫切和縱切
    eg:比如我要取得某ID的lastlogin,完全可以做一張只有"ID"和"lastlogin"的小表,        因為對一個有1000萬條記錄的表做更新比對10個100萬的表做更新一般來的要慢
d>myisam引擎,表級鎖,但所開銷小,但影響范圍大,適合讀多寫少的表,不支持失誤日志;
e>innodb引擎,行級鎖,鎖定行的開銷要比鎖定全表要大,但影響小,適合寫操作比較頻繁
5 my.cnf內參數的優化
優化總原則:給MySQL的資源太小,則MySQL施展不開
             給MySQL的資源太多,可能會拖累整個OS
a>總體資源占用的優化
    open_file_limit————MySQLd可以打開的文件數量
    max_connections————允許並行客戶端連接數
    max_connect_errors————允許主機錯誤連接數
    table_cache————每個連接允許打開表的數量
    max_allowed_packet————從服務器接收包的大小
    thread_cache_size————緩存多少個待用線程
b>具體buffer的優化
    sort_buffer_size————每個線程可以分配的緩沖區的大小
    join_buffer_size————不走索引的join操作可分配的緩沖區的大小
    query_cache_size————為查詢分配的緩存
    query_cache_limit————不緩存大於該限制的查詢結果
    query_cache_min_res_unit————不緩存小於限制的查詢結果
    tmp_table_size————內存內的臨時性表超過該限制值,則寫入硬盤
    binlog_cache_size————二進制日志文件的緩存
    key_buffer_size————myisam引擎的索引塊公用緩沖區
    read_buffer_size————為從數據表順序讀取數據的讀操作保留的緩沖區的長度
    innodb_additional_mem_pool_size————InnoDB用來存儲數據目錄信息&     innodb_buffer_pool_size————InnoDB用來緩存它的數據和索引的內存緩沖區的大小.     innodb_data_file_path————innodb表空間的制定以及大小,初始表空間大一些可以減少     innodb_thread_concurrency————在InnoDB核心內的允許線程數量
    innodb_log_buffer_size————InnoDB用來往磁盤上的日志文件寫操作的緩沖區的大小.
      當日志大小超過該限制時,日志會被寫入磁盤,比寫入內存的I/O開銷大
    innodb_log_file_size————每個日志文件的大小
    max_allowed_packet————包服務所能處理的請求包的最大大小以及服務所能處理的最大 6 查詢優化
   a>建表時表結構要合理,每個表不宜過大.在任何情況下均應使用最精確的類型.
      eg:如果ID列用Int是一個好主意,而用text類型則是個蠢辦法
         TIME列酌情使用DATE或DATETIME
b>索引,所有的查詢都是走科學的索引,單個索引命中率低時使用聯合索引
c>查詢時盡量減少邏輯運算(與運算,或運算.大於小於某值的運算)
d>減少不當的查詢語句,不要查詢應用中不需要的列.
      eg:select * from 等操作
e>減少事務包的大小
f>將多個小的查詢適當合並成一個大的查詢,減少每次建立/關閉查詢時的開銷
g>將某些國語復雜的查詢拆分成多個小查詢(與上一條恰好相反)
h>建立和優化存儲過程來代替大量的外部程序交互
7 DEBUG工具
a>vmstat————vmstat命令報告關於內核線程,虛擬內存,磁盤,陷阱和CPU活動的統計信息
b>iostat————iOStat命令報告CPU,硬盤燈設備的輸入輸出情況,可能需要安裝sysstat rpm包
c>top————動態顯示當前系統的資源占用,與iOStat比,top更側重於進程
d>free————顯示內存和SWAP占用情況
e>show processlist————顯示當前運行或等待的線程,判斷哪些查詢語句總是處於等待狀態
f>EXPLAIN————“EXPLAIN + SQL語句"查看索引使用情況
g>show create table + "table_name" ————查看指定表的表結構
h>select count(distinct "row_name") from "table_name"; 查看列內數據的唯一性,確定給哪一列創建索引
i>create index 創建索引,並用show processlist,top 觀察創建索引後的效果.