Mysql的優化的相關知識

優化的幾個方面

SQL語句優化:使用EXPLAIN來理解SQL語句本身的執行過程,執行計劃;

索引優化:

資料庫設計優化

InnoDB表優化;

MyISAM表優化;

理解查詢執行計劃;

緩衝和快取;

• 將經常緩衝的資料快取在記憶體中,快取的資料例如:表資訊[表名], 表結構資訊;

鎖優化;

• 需要指定合適的鎖策略,並且使用粒度較小的鎖,來使用行鎖;

Mysql伺服器優化

• 可以用於快取結果,Mysql自己也包括查詢快取;

效能評估;

Mysql優化內幕;

Mysql優化需要在三個不同層次上面協調進行:Mysql級別,OS級別和硬體級別,Mysql級別的優化包括表級別優化,查詢級別優化和Mysql服務配置優化,最終Mysql的各種資料結構,直接作用於OS甚至是硬體裝置;因此還需要了解每種結構對OS級別的資源的需求,通常還涉及對於CPU以及IO操作的優化,用來提升效能;

資料庫層面的優化著眼點

1.是否正確設計了表結構的相關資訊,尤其是每個欄位型別是否為最佳,同時為特定樂行的工作組織使用了合適的表以及表欄位,例如,對於頻繁更新的表,應該使用較多的表,較少的欄位,對於複雜資料查詢,應該使用較少的表,較多的欄位,例如對於字元型來說,VARCHAR可以顯著的節省空間,但是對於CHAR可以顯著的提升效能;

2.是否為了高效的查詢而建立了合適的索引;常見的索引包括HASH索引[不適用於範圍查詢]和B+樹索引;

3.是否為每張表建立了合適的儲存引擎,並且有效利用率儲存引擎本身的有事和特性;

4.是否基於儲存引擎為表選取了合適的行格式,例如壓縮表在讀寫操作中會降低IO操作需求並且佔用較少的磁碟空間,但是MyISAM僅僅在讀環境中支援壓縮表;

5.是否使用了合適的鎖策略,如在併發操作使用使用共享鎖,同時還應該考慮儲存引擎支援的鎖型別;

6.是否為InnoDB的緩衝池,MyISAM的鍵快取以及Mysql的查詢快取設定了合適大小的記憶體空間,用於儲存頻繁訪問的資料,而又不會引起頁面換出;

作業系統和硬體級別又換的著眼點

1.是否為實際工作選取了合適的CPU,如對於CPU密集型,需要選取更快速度的以及更多數量的CPU,為查詢場景較多的情況下,選擇更多的CPU,甚至採用基於多核甚至是超執行緒技術,通常來說,CPU效能提升的的目標包括:低延遲和高吞吐量;對於mysql來說,一個查詢語句只能夠執行在一個CPU上面;

2.是否包含由合適的實體記憶體.並且通過合適的配置平衡記憶體和磁碟管理,減低甚至避免磁碟IO,程式設計通常具有區域性性原理,為了這個原理通過指定合適的快取策略,可以實現延遲寫入,優化寫入;

3.是否選擇了合適的網路裝置,網路裝置導致的延遲和頻寬,以及丟包等問題,如果存在量少但是資料請求大的連線,就應該提升網路卡效能,對於連線請求多,資料量少,因為啟用連線重用;

4.是否選取了合適的檔案系統,因為Mysql對於資料安全的要求,應該儘量選擇帶有日誌功能的檔案系統;

5.Mysql為了響應每一個使用者的請求,通常維護了一個單獨的執行緒,並且還需要建立內部使用的執行緒,特殊目的使用的執行緒以及儲存引擎建立的執行緒,Mysql需要對大量的執行緒進行管理;

使用InnoDB儲存引擎最佳實踐

1.基於Mysql查詢語句中最常用的欄位或者欄位組合建立主鍵,如果沒有適合的主鍵建議使用AUTO_INCREMENT型別的某欄位為主鍵;

• 一般是主索引(聚集索引),輔助索引,基於輔助索引的查詢仍然會使用到主索引;
• 索引是需要裝入記憶體的;
• 聚集索引:對於表中的資料只能夠按照一種方式進行聚集索引;
• 非聚集索引:
• 主索引:主索引
• 輔助索引:
• 稠密索引:每一個變化值都是對應一個匹配的索引條目;
• 稀疏索引:不是每一個變化值都對應一個匹配的索引條目;
• 多級索引:
• B+樹索引:是一種多集索引,從根到每一個資料節點的路徑是等長的,成為平衡樹索引;雖然查詢速度顯著提升,但是導致資料的更新速度變慢;
  • 適用於全鍵值,鍵值範圍,適合於最左左前查詢,例如Li%開始的範圍查詢,例如select name like lixu%;但是不適合於select name like %u%,這種查詢;
    • 只適合於左前查詢;
    • 不能夠跳過索引中的列:如果建立的索引是name,age,salary,在進行查詢時,查詢的是name, salary這個過程是不能夠跳過age這一列的;但是支援name,age這種查詢方式,也就是說可以跳過最後一個值,但是不能夠跳過中間的索引;
    • 儲存引擎不能夠優化訪問任何在第一個範圍條件右邊的列;對於建立的索引name, age,salary,如果不使用name欄位,那麼索引就是沒有使用的,並且如果select語句是select name like 'chen%' and salary > 3000,那麼儲存引擎是不能夠對salary欄位進行優化的;
• Hash索引:鍵值對,鍵是hash碼,值是資料的值,value是某一張所在的位置,對於主索引不能夠使Hash索引,可以用於等值查詢,但是不適用於範圍查詢,因為頻繁的計算hash碼;memory支援顯示的hash索引,InnoDB儲存引擎支援自適應hash索引,會自動建立hash索引;
  • 適用於等值條件比較,例如= IN() <=>;查詢速度快;
  • 索引只包含了hash碼和行指標,所以無法進行有效的索引排序;
  • 不支援部分匹配,例如不支援like '%%',這種匹配方式;
• 空間索引:
• 全文索引:
• 覆蓋索引:資料查詢是索引建立裡面的一部分,例如建立的索引是name,age,在進行查詢時,查詢age,就屬於覆蓋索引;

2.如果需要使用多表查詢,將這些表建立外來鍵約束關係;

3.關閉autocommit;

4.使用事務START TRANSACTION語句組合相關的修改操作或者一個整體的工作單元,但是事務不應該過於大;

5.停止使用LOCK TABLES語句,InnoDB儲存引擎可以搞笑的處理來自於多個會話的併發讀寫請求,如果需要在一系列的行上面獲取獨佔訪問許可權,建議使用SELECT .... FROM UPDATE鎖定僅需要更新的行;

6.啟用Innodb_file_pre_table選項,將各張表的資料和索引分別存放;

7.評估資料和訪問模式是否能夠從InnoDB的表壓縮功能中收益,決定是否在建立表時,使用FROM_FORMAT=COMPRESSED選項;

MyISAM常用的幾個調優引數

key_buffer_size:用於調整鍵緩衝大小,用於儲存索引的記憶體大小的,預設是8MB,最大為4GB,主要適用於加速查詢操作;

concurrent_insert:表示是否支援併發插入,一次執行多個插入操作,用於提高寫入效能;

• 0:表示禁止併發插入;
• 1:預設值,表示資料之間存在不存在空隙的話,允許併發插入;
• 2:表示如果資料之間存在空隙,那麼允許併發插入;
• 資料之間的空隙是因為某些行被刪除,而之間的空隙違背填充;

delay_key_write:用於延遲鍵寫入操作,資料更新,索引就需要更新,索引更新就會觸發快取衝的索引失效,之後就需要重新讀取索引,這個表示的含義是資料更新,並不立即更新索引操作;

max_write_lock_count:

preload_buffer_size:為了鍵緩衝預先準備的記憶體大小;

InnoDB儲存引擎優化

InnoDB_data_file_path:表示InnoDB表空間的路徑;

InnoDB_data_home_dir:表示資料檔案的目錄;

InnoDB_file_per_table:表示每表一個表空間檔案;

innodb_buffer_pool_size:用於定義InnoDB的緩衝池的大小,這個適用於快取索引和資料的快取區域,對於這段空間建議還是用大記憶體頁;

innodb_flush_log_at_trx_commit:表示在事物提交時,是否重新整理log檔案,為了事務的安全性,這個是建議開啟的;

• 0:這個不建議使用
• 1:表示事務提交,並且每隔一段時間就會flush;
• 2:僅僅在事物提交時,才進行flush操作;
• 使用這個選項的前提是關係auto_commit;

innodb_log_file_size:表示的是事務日誌的大小,通常這個值建議調大;

查詢快取:

• 鍵是select語句的hash碼,值是語句的查詢結果;對於select語句來說,如果使用的語句存在大小寫交叉的情況,會導致select語句在計算快取時得到的hash碼不統一,無法有效利用查詢快取;
• query_alloc_block_size:表示預設的查詢快取大小;
• query_cache_size:數值為0,表示禁用查詢快取,否則就是啟用,並且制定了一個大小,對於非決定性以及不具有時效性的時間都是會進行快取的;
• query_cache_limit:用於設定查詢的結果大於某個值,就不在進行快取;
• query_cache_min_limit:查詢結果如果小於這個值,不進行快取;
• query_cache_type:用於定義查詢快取的型別;
  • OFF:表示不進行快取;
  • ON:表示進行快取,這個是預設的選項,儘量快取能夠快取的語句;
  • DEMAND:表示按照需要進行快取,需要顯示的提示進行快取,才會進行快取,例如select name from student where age = 30 SQL chachep[要求進行快取];

EXPLAIN語句解析:

id:select語句的識別符號,一般是數字,表示對應的SELECT語句中的原始語句中的位置,沒有子查詢或者聯合的整個查詢只有一個SELECT語句,因此其ID通常為1,在聯合或者子查詢語句中,內層的SELECT語句通常接在原始語句中的次序進行編號,但UNION操作通常最後會有一個id為NULL的行,因此UNION的結果通常儲存在臨時表中,而Mysql需要到次臨時表中取得結果;

架構師---->自動化運維---->DBA(Mysql--->Oracle)