mysql效能優化

PinXiong發表於2020-08-01
MySql優化

MYSQL執行SQL的流程

image.png

SQL的執行過程；

  1. 客戶端傳送一條查詢給伺服器；
  2. 伺服器通過許可權檢查之後,先會檢查查詢快取，如果命中了快取，則立即返回儲存在快取中的結果。否則進入下一階段；
  3. 伺服器端進行SQL解析、預處理，再由優化器根據該SQL所涉及到的資料表的統計資訊進行計算，生成對應的執行計劃；
  4. MySQL根據優化器生成的執行計劃，呼叫儲存引擎的API來執行查詢；
  5. 將結果返回給客戶端。

SQL執行的最大瓶頸在於磁碟的IO，即資料的讀取；不同SQL的寫法，會造成不同的執行計劃的執行，而不同的執行計劃在IO的上面臨完全不一樣的數量級，從而造成效能的差距； 所以,我們說,優化SQL,其實就是讓查詢優化器根據程式猿的計劃選擇匹配的執行計劃,來減少查詢中產生的IO;

schema(表結構)對效能的影響

  1. 冗餘資料的處理； 適當的資料冗餘可以提高系統的整體查詢效能(在P2P中,在userinfo物件中有realname和idnumber); 關聯式資料庫的三正規化: 第一正規化（1NF）是對關係模式的基本要求，不滿足第一正規化（1NF）的資料庫就不是關聯式資料庫，是指資料庫表的每一列都是不可分割的基本資料項，同一列中不能有多個值； 第二正規化（2NF）要求資料庫表中的每個例項或行必須可以被惟一地區分。 第三正規化（3NF）要求一個資料庫表中不包含已在其它表中已包含的非主關鍵字資訊。 (不允許有冗餘資料)

  2. 大表拆小表，有大資料的列單獨拆成小表；

    1. 在一個資料庫中,一般不會設計屬性過多的表;
    2. 在一個資料庫中,一般不會有超過500/1000萬資料的表(拆表,按照邏輯拆分,按照業務拆分);
    3. 有大資料的列單獨拆成小表(富文字編輯器,CKeditor);

  3. 根據需求的展示設定更合理的表結構；

  4. 把常用屬性分離成小表；

    1. 在P2P專案中,我們把logininfo和userinfo和account表拆成了三張表;
    2. 減少查詢常用屬性需要查詢的列;
    3. 便於常用屬性的集中快取;

####索引和索引的優化:
1,索引的原理:把無序的資料變成有序的查詢;

image.png

索引:

  1. 索引的物理結構:

    1. 資料庫檔案儲存的位置:my.ini配置檔案中dataDir對應的資料目錄中;
    2. 每一個資料庫一個資料夾;
      1. MYISAM引擎:每一個表(table_name)--> table_name.MYI:存放的是資料表對應的索引資訊和索引內容; table_name.FRM:存放的是資料表的結構資訊; table_name.MYD:存放的是資料表的內容;
      2. InnoDB引擎:每一個表(table_name)--> table_name.frm:存放的是資料表的結構資訊; 資料檔案和索引檔案都是統一存放在ibdata檔案中;
      3. 索引檔案都是額外存在的,對索引的查詢和維護都是需要消耗IO的;

  2. 索引的結構:

    1. 預設情況下,一旦建立了一個表,這個表設定了主鍵,那麼MYSQL會自動的為這個主鍵建立一個unique的索引;
    2. 索引型別:
      1. Normal:普通的索引;允許一個索引值後面關聯多個行值;
      2. UNIQUE:唯一索引;允許一個索引值後面只能有一個行值;之前對列新增唯一約束其實就是為這列新增了一個unique索引;當我們為一個表新增一個主鍵的時候,其實就是為這個表主鍵列(設定了非空約束),併為主鍵列新增了一個唯一索引;
      3. Fulltext:全文檢索,mysql的全文檢索只能用myisam引擎,並且效能較低,不建議使用;
    3. 索引的方法(規定索引的儲存結構): (資料結構,演算法基礎)
      1. b-tree:是一顆樹(二叉樹,平衡二叉樹,平衡樹(B-TREE)) 使用平衡樹實現索引，是mysql中使用最多的索引型別；在innodb中，存在兩種索引型別，第一種是主鍵索引（primary key），在索引內容中直接儲存資料的地址；第二種是其他索引，在索引內容中儲存的是指向主鍵索引的引用；所以在使用innodb的時候，要儘量的使用主鍵索引，速度非常快； b-tree中儲存的資料都是按照一定順序儲存的資料,是可以允許在範圍之內進行查詢; select * from accountflow where account_id <100;
      2. hash:把索引的值做hash運算，並存放到hash表中，使用較少，一般是memory引擎使用；優點:因為使用hash表儲存，按照常理，hash的效能比B-TREE效率高很多。 hash索引的缺點： 1，hash索引只能適用於精確的值比較，=，in，或者<>；無法使用範圍查詢; 2，無法使用索引排序； 3，組合hash索引無法使用部分索引； 4，如果大量索引hash值相同，效能較低；

image.png

image.png

索引的利弊:

  1. 索引的好處： 1，提高表資料的檢索效率； 2，如果排序的列是索引列(如果查詢的列==排序的列[並且在這列上做了索引])，大大降低排序成本； 3，在分組操作中如果分組條件是索引列，也會提高效率；
  2. 索引的問題： 索引需要額外的維護成本；因為索引檔案是單獨存在的檔案,對資料的增加,修改,刪除,都會產生額外的對索引檔案的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執行效率;
  3. 怎麼建立索引?
    1. 較頻繁的作為查詢條件的欄位應該建立索引；
    2. 唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件； 作為索引的列,如果不能有效的區分資料,那麼這個列就不適合作為索引列;比如(性別,狀態不多的狀態列) 舉例:SELECT sum(amount) FROM accountflow WHERE accountType = 0; 假如把accountType作為索引列,因為accountType只有14種,所以,如果根據accountType來建立索引,最多隻能按照1/14的比例過濾掉資料;但是,如果可能出現,只按照該條件查詢,那我們就要考慮到其他的提升效能的方式了; 第一種方案:單獨建立一個系統摘要表;在這個裡面有一個列叫做系統總充值金額;每次充值成功,增加這個列的值;以後要查詢系統總充值金額,只需要從這個系統摘要表中查詢;(缺陷:如果充值頻率過快,會導致表的鎖定問題;) 第二種方案:流水一旦發生了,是不會隨著時間改變的;針對這種資訊,我們就可以使用增量查詢(結算+增量查詢); 1,建立一張日充值表;記錄每一天的充值總金額(beginDate,endDate,totalAmount),每天使用定時器對當前的充值記錄進行結算;日充值報表裡面記錄只能記錄截止昨天的資料; 2,建立一張月充值表;記錄每一個月的充值總金額(beginDate,endDate,totalAmount),每月最後一天使用定時器對當月的充值記錄進行結算(資料來源從日充值報表來); 3,要查詢系統總充值,從月報表中彙總(當前月之前的總充值金額),再從日充值報表中查詢當天之前的日報表資料彙總;再從流水中查詢當前截止查詢時間的流水;使用另外一張當天流水錶記錄當天的流水;再把三個資料累加;
    3. 更新非常頻繁的欄位不適合建立索引；原因,索引有維護成本;
    4. 不會出現在WHERE 子句中的欄位不該建立索引；
    5. 索引不是越多越好;(只為必要的列建立索引) 1,不管你有多少個索引,一次查詢至多采用一個索引;(索引和索引之間是獨立的) 2,因為索引和索引之間是獨立的,所以說每一個索引都應該是單獨維護的;資料的增/改/刪,會導致所有的索引都要單獨維護;

索引的使用限制:

  1. BLOB 和TEXT 型別的列只能建立字首索引
  2. MySQL 目前不支援函式索引(在MYSQL中,索引只能是一個列的原始值,不能把列通過計算的值作為索引); 例項:請查詢1981年入職的員工: SELECT * FROM emp WHERE year(hire_date)='1981'; 問題:查詢的列是在過濾之前經過了函式運算;所以,就算hire_date作為索引,year(hire_date)也不會使用索引; 解決方案: 1,SELECT * FROM emp WHERE hire_date BETWEEN '1981-01-01' AND '1981-12-31'; 2,在建立一列,這列的值是year(hire_date),然後把這列的值作為索引; 3. 使用不等於（!= 或者<>）的時候MySQL 無法使用索引
  3. 過濾欄位使用了函式運算後（如abs(column)），MySQL 無法使用索引]
  4. Join 語句中Join 條件欄位型別不一致的時候MySQL 無法使用索引
  5. 使用LIKE 操作的時候如果條件以萬用字元開始（ '%abc...'）MySQL 無法使用索引 1,字串是可以用來作為索引的; 2,字串建立的索引按照字母順序排序; 3,如果使用LIKE,例項:SELECT * FROM userinfo WHERE realName LIKE '吳%';這種情況是可以使用索引的; 但是LIKE '_嘉' 或者LIKE '%嘉'都是不能使用索引的;
  6. 使用非等值查詢的時候MySQL 無法使用Hash 索引

單列索引和複合索引:

  1. 因為一個查詢一次至多隻能使用一個索引,所以,如果都使用單值索引(一個列一個索引),在資料量較大的情況下,不能很好的區分資料;
  2. 所以,MYSQL引入了多值索引(複合索引); 複合索引就是由多列的值組成的索引;並且(注意),多列的索引是有順序的!!!!
  3. 複合索引的原理:就是類似orderby(orderby後面可以跟多個排序條件order by hire_date,username desc); 就是在排序和分組(建立倒排表的時候),按照多個列進行排序和合並; SELECT * FROM accountflow WHERE actionTime < 'xxxxx' AND account_id = 5 可以使用actionTime+account_id的複合索引; SELECT * FROM accountflow WHERE actionTime < 'xxxxx' 可以使用actionTime+account_id的複合索引; SELECT * FROM accountflow WHERE account_id = 5 不可以使用actionTime+account_id的複合索引; SELECT * FROM accountflow WHERE account_id = 5 AND actionTime < 'xxxxx' 不可以使用actionTime+account_id的複合索引;
  4. 複合索引,在查詢的時候,遵守向左原則;只要在查詢的時候,是按照複合索引從左到右的順序依次查詢,不管查詢條件是否完全滿足所有的符合索引的列,都可以使用部分的符合索引;
  5. 在實際應用中,基本上都使用複合索引;

檢視MYSQL的執行計劃和執行明細狀態(explain+profiling)

  1. Explain:可以讓我們檢視MYSQL執行一條SQL所選擇的執行計劃；
  2. Profiling:可以用來準確定位一條SQL的效能瓶頸；

EXPLAIN:

  1. 使用方式： explain SQL;
  2. 返回結果：
    1. ID：執行查詢的序列號；
    2. select_type：使用的查詢型別
      1. DEPENDENT SUBQUERY：子查詢中內層的第一個SELECT，依賴於外部查詢的結果集；
      2. DEPENDENT UNION：子查詢中的UNION，且為UNION 中從第二個SELECT 開始的後面所有SELECT，同樣依賴於外部查詢的結果集；
      3. PRIMARY：子查詢中的最外層查詢，注意並不是主鍵查詢；
      4. SIMPLE：除子查詢或者UNION 之外的其他查詢；
      5. SUBQUERY：子查詢內層查詢的第一個SELECT，結果不依賴於外部查詢結果集；
      6. UNCACHEABLE SUBQUERY：結果集無法快取的子查詢；
      7. UNION：UNION 語句中第二個SELECT 開始的後面所有SELECT，第一個SELECT 為PRIMARY 8，UNION RESULT：UNION 中的合併結果；
    3. table：這次查詢訪問的資料表；
    4. type：對錶所使用的訪問方式：
      1. all：全表掃描
      2. const：讀常量，且最多隻會有一條記錄匹配，由於是常量，所以實際上只需要讀一次；
      3. eq_ref：最多隻會有一條匹配結果，一般是通過主鍵或者唯一鍵索引來訪問；
      4. fulltext：全文檢索，針對full text索引列；
      5. index：全索引掃描；
      6. index_merge：查詢中同時使用兩個（或更多）索引，然後對索引結果進行merge 之後再讀取表資料；
      7. index_subquery：子查詢中的返回結果欄位組合是一個索引（或索引組合），但不是一個主鍵或者唯一索引；
      8. rang：索引範圍掃描；
      9. ref：Join 語句中被驅動表索引引用查詢；
      10. ref_or_null：與ref 的唯一區別就是在使用索引引用查詢之外再增加一個空值的查詢；
      11. system：系統表，表中只有一行資料；
      12. unique_subquery：子查詢中的返回結果欄位組合是主鍵或者唯一約束；
    5. possible_keys：可選的索引；如果沒有使用索引，為null；
    6. key：最終選擇的索引；
    7. key_len：被選擇的索引長度；
    8. ref：過濾的方式，比如const（常量），column（join），func（某個函式）；
    9. rows：查詢優化器通過收集到的統計資訊估算出的查詢條數；
    10. Extra：查詢中每一步實現的額外細節資訊
      1. Distinct：查詢distinct 值，所以當mysql 找到了第一條匹配的結果後，將停止該值的查詢而轉為後面其他值的查詢；
      2. Full scan on NULL key：子查詢中的一種優化方式，主要在遇到無法通過索引訪問null值的使用使用；
      3. Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通過收集到的統計資訊判斷出不可能存在結果；
      4. No tables：Query 語句中使用FROM DUAL 或者不包含任何FROM 子句；
      5. Not exists：在某些左連線中MySQL Query Optimizer 所通過改變原有Query 的組成而使用的優化方法，可以部分減少資料訪問次數；
      6. Select tables optimized away：當我們使用某些聚合函式來訪問存在索引的某個欄位的時候，MySQL Query Optimizer 會通過索引而直接一次定位到所需的資料行完成整個查詢。當然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的時候；
      7. Using filesort：當我們的Query 中包含ORDER BY 操作，而且無法利用索引完成排序操作的時候，MySQL Query Optimizer 不得不選擇相應的排序演算法來實現。
      8. Using index：所需要的資料只需要在Index 即可全部獲得而不需要再到表中取資料；
      9. Using index for group-by：資料訪問和Using index 一樣，所需資料只需要讀取索引即可，而當Query 中使用了GROUP BY 或者DISTINCT 子句的時候，如果分組欄位也在索引中，Extra 中的資訊就會是Using index for group-by；
      10. Using temporary：當MySQL 在某些操作中必須使用臨時表的時候，在Extra 資訊中就會出現Using temporary 。主要常見於GROUP BY 和ORDER BY 等操作中。
      11. Using where：如果我們不是讀取表的所有資料，或者不是僅僅通過索引就可以獲取所有需要的資料，則會出現Using where 資訊；
      12. Using where with pushed condition：這是一個僅僅在NDBCluster 儲存引擎中才會出現的資訊，而且還需要通過開啟Condition Pushdown 優化功能才可能會被使用。控制引數為engine_condition_pushdown 。

profiling:

Query Profiler是MYSQL5.1之後提供的一個很方便的用於診斷Query執行的工具，能夠準確的獲取一條查詢執行過程中的CPU，IO等情況； 1. 開啟profiling：set profiling=1; 2. 執行QUERY，在profiling過程中所有的query都可以記錄下來； 3. 檢視記錄的query：show profiles； 4. 選擇要檢視的profile：show profile cpu, block io for query 6

status是執行SQL的詳細過程； Duration：執行的具體時間； CPU_user：使用者CPU時間； CPU_system：系統CPU時間； Block_ops_in：IO輸入次數； Block_ops_out：IO輸出次數； profiling只對本次會話有效；

JOIN:

1,JOIN的原理: 在mysql中使用Nested Loop Join來實現join； A JOIN B：通過A表的結果集作為迴圈基礎，一條一條的通過結果集中的資料作為過濾條件到下一個表中查詢資料，然後合併結果； 2,JOIN的優化原則: 1，儘可能減少Join 語句中的Nested Loop 的迴圈總次數，用小結果集驅動大結果集； 2，優先優化Nested Loop 的內層迴圈； 3，保證Join 語句中被驅動表上Join 條件欄位已經被索引； 4，擴大join buffer的大小；

image.png

SQL優化原則:

[原則一:選擇需要優化的SQL]

  1. 選擇需要優化的SQL:不是所有的SQL都需要優化,在優化的過程中,首選更需要優化的SQL; 怎麼選擇? 優先選擇優化高併發低消耗的SQL；
    1. 1小時請求1W次，1次10個IO；
    2. 1小時請求10次，1次1W個IO； 考慮:
    • 從單位時間產生的IO總數來說,相同的;
    • 針對一個SQL,如果我能把10個IO變成7個IO,一小時減少3W個IO; 針對第二個SQL,如果能把1W個IO變成7K個IO,一小時減少3W個IO;
    • 從優化難度上講,1W->7K難的多;
    • 從整體效能上來說,第一個SQL的優化能夠極大的提升系統整體的效能;第二個SQL慢一點,無非也就是10個連線查詢慢一點;
  2. 定位效能瓶頸；
    1. SQL執行較慢有兩個影響原因，IO和CPU，明確效能瓶頸所在；
    2. 明確優化目標；

[原則二:從Explain和Profile入手]

  1. 任何SQL的優化，都從Explain語句開始；Explain語句能夠得到資料庫執行該SQL選擇的執行計劃；
  2. 首先明確需要的執行計劃，再使用Explain檢查；
  3. 使用profile明確SQL的問題和優化的結果；

[原則三:永遠用小結果集驅動大的結果集]

[原則四:在索引中完成排序]

[原則五:使用最小Columns]

  1. 減少網路傳輸資料量；
  2. 特別是需要使用column排序的時候.為什麼?MYSQL排序原理，是把所有的column資料全部取出，在排序快取區排序，再返回結果；如果column資料量大，排序區容量不夠的時候，就會使用先column排序，再取資料，再返回的多次請求方式；

[原則六:使用最有效的過濾條件]

  1. 過多的WHERE條件不一定能夠提高訪問效能；
  2. 一定要讓where條件使用自己預期的執行計劃；

[原則七:避免複雜的JOIN和子查詢]

  1. 複雜的JOIN和子查詢，需要鎖定過多的資源，MYSQL在大量併發情況下處理鎖定效能下降較快；
  2. 不要過多依賴SQL的功能，把複雜的SQL拆分為簡單的SQL；
  3. MySQL子查詢效能較低，應儘量避免使用；

相關文章