中國移動MySQL資料庫最佳化最佳實務

標籤：

原創 2016-08-12 章穎 DBAplus社群

 

本文根據DBAplus社群第69期線上分享整理而成，文末還有書送哦~

 

講師介紹 章穎

資料研發工程師

 

• 現任中國移動杭州研發中心資料研發工程師，擅長MySQL故障診斷，效能調優，MySQL高可用技術，曾任中國電信綜合平台開發運營中心DBA

 

開來源資料庫MySQL比較容易碰到效能瓶頸，為此經常需要對MySQL資料庫進行最佳化，而MySQL資料庫最佳化需要營運DBA與相關開發共同參與，其中MySQL參數及伺服器配置最佳化主要由營運DBA完成，開發則需要從資料類型最佳化，索引最佳化，SQL最佳化三個角度考慮MySQL資料庫最佳化問題，本次分享將從開發角度，看如何?MySQL資料庫最佳化。

 

本次分享大綱：

• 一個例子

• 資料類型最佳化

• 索引最佳化

• SQL最佳化

 

一、一個例子

 

 

 

資料庫需要處理的行數： 189444*1877*13482~~~479億 

 

如果在關聯欄位上加上合適的索引：

 

 

 

資料庫需要處理的行數：368006*1*3*1~~~110萬

 

MySQL通常是一個請求對應一個線程，其thread_handling是one-thread-per-connection，因此一條sql請求只能利用一個CPU

 

 

 

通過加索引，資料庫需要處理的行數下降了4個數量級，第一種情況下等待半小時不一定能跑出結果，但第二種情況可以在秒級範圍內拿到需要的結果。從該例子可以看出，MySQL資料庫最佳化非常重要，一條不合理的SQL就可能導致服務異常。

 

開發需要掌握查看MySQL執行計畫及profile工具：

1. EXPLAIN  SELECT ……

2. EXPLAIN EXTENDED SELECT ……

3. profile工具

SET profiling = 1;

show profiles;

-- 顯示最近發送的mysql服務的sql語句

show profile;

-- 顯示最近的單個SQL語句的詳細過程資訊

show profile all for query 61;

-- 顯示所有相關資訊

 

二、資料類型最佳化

 

選擇資料類型的步驟：

• Step1：確定合適的大類型，如數字、字串、時間等；

• Step2：選擇具體類型，相同大類型的不同子類型資料的儲存長度，範圍，允許的精度不同，有時候也有一些特殊的行為和屬性。

 

普遍適用的原則：

• 使用小而簡單的合適的資料類型； 

• 對於可變長字串VARCHAR，只分配真正需要的空間； 

• 小心使用ENUM; 

• 盡量使用整型定義識別欄位； 

• 使用相同資料類型儲存相似或者相關的值，尤其是關聯條件中使用的列。 

 

核心原則：具體問題具體分析。一些特定的業務情境並不適合套用普遍使用的原則。

 

>>>>

使用小而簡單的合適的資料類型：

• Case1：如果只需要存0-200，tinyint unsigned更好。

  因為更小的資料類型所需的磁碟，記憶體和CPU緩衝更少，處理時需要的CPU周期也更少。

• Case2：用INT代替varchar(15)來儲存IP地址。

  因為字元集和校對規則（定序）使字元比較比整型比較更複雜。

• Case3：使用MySQL內建的類型（date, time, datetime等）而不是字串來儲存日期和時間。

• Case4：用char儲存密碼的MD5值，因為密碼的MD5是一個定長的值。

 

>>>>

對於可變長字串VARCHAR，只分配真正需要的空間：

使用VARCHAR(4)和VARCHAR(200)儲存‘ZYHY’的空間開銷是一樣的，但使用更短的列VARCHAR(4)有如下優勢：

 

因為MySQL通常會分配固定大小的記憶體塊來儲存內部值，所以更長的列會消耗更多的記憶體，在使用記憶體暫存資料表進行排序或者操作時會特別糟糕，利用磁碟暫存資料表進行排序時也同樣糟糕。

 

所以，建議只分配真正需要的空間。

 

>>> >

小心使用ENUM

 

MySQL在儲存ENUM枚舉時非常緊湊，會根據列表值的數量壓縮到一個或者兩個位元組中。MySQL在內部會將每個值在列表中的位置儲存為整數，並且在表的.frm檔案中儲存“數字-字串”映射關係的“尋找表”。枚舉欄位是按照內部儲存的整數而不是定義的字串進行排序。

 

 

 

從中的select e + 0 from enum_test;的結果可以看出，MySQL在內部會將每個值在列表中的位置儲存為整數，可以與整數進行算術運算。

 

從中的select e from enum_test order by e;的結果可以看出，排序結果與建表時的順序一致，如果需要按字元創的字母順序排序，則需要通過額外的方法來處理，比如：

 

1. 按照需要的順序來定義枚舉列； 

2. 在查詢中使用FIELD()函數顯示地指定排序次序，但這會導致MySQL無法利用索引消除排序。 

 

 

 

與VARCHAR相比，ENUM優勢與劣勢：

1. 優勢：資料緊湊，儲存的是整數，佔用空間小，作為關聯欄位時，效率比varchar類型高很多；

2. 劣勢：字串列表是固定的，添加或者刪除字串必須使用ALTER TABLE,如果添加的字串不在列表末尾，則需要重建整個表完成修改。由於ENUM儲存為整數，必須進行尋找才能轉換為字串，在需要轉換為字串時有一些開銷。在一些特定情況下，把varchar列和枚舉列進行關聯可能比varchar自關聯更慢。

 

>>>>

盡量使用整型定義識別欄位

• 因為整形資料的執行計算和比較都很快；

• 不建議使用UUID等隨機字串作為識別欄位，因為隨機字串會任意分布在很大的空間，導致INSERT和SELECT語句變得很慢。

 

>>>>

使用相同資料類型儲存相似或者相關的值，尤其是關聯條件中使用的列

• 因為混用不同的資料類型可能導致效能問題，在關聯條件中會有資料類型轉換的資源消耗；

• 在比較操作時隱形類型轉換可能導致很難發現的錯誤。

 

>>>>

關於整數類型指定寬度的一個解釋

MySQL可以為整數類型指定寬度，如INT(11),但對大多數應用來說，這並沒有什麼意義：它不會限制值的合法範圍，只是規定了MySQL的一些互動工具（例如MySQL命令列用戶端）用來顯示字元的個數。對於儲存和計算來說，INT(1) 和INT(20)是相同的。

 

>>>>

關於實數類型

1. MySQL既支援精確類型（decimal, numeric），也支援不精確類型(float, double)。 

2. 可以使用DECIMAL儲存比BIGINT還大的整數。 

3. CPU不支援對DECIMAL的直接計算，而是MySQL伺服器自身對DECIMAL進行高精度計算。而CPU直接支援原生浮點運算，所以，浮點運算明顯更快。 

4. 可以考慮使用BIGINT代替DECIMAL，將需要儲存的值根據小數的位元乘以相應的倍數即可，如精確到0.01，則把所有值乘以100儲存到BIGINT中，這樣可以同時避免浮點儲存計算不精確和DECIMAL精確計算代價高的問題。 

 

>>>>

關於NULL的定義:

a missing unknown value, means “not having a value.”

 

與NULL的任何數學運算的結果還是NULL

 

 

 

判斷值是否等於NULL，不能簡單用=，而要用IS NULL/ IS NOT NULL

 

 

 

0和Null 字元串都不是NULL:

 

 

 

NULL與Null 字元串的區別 

 

 

 

中分別insert了一個NULL和一個Null 字元創，其表達的意義不一樣：

• INSERT a NULL:不知道這個人有沒有電話號碼；

• INSERT a ‘’: 確定這個人沒有電話號碼；

•  COUNT(table.column), MIN(), and SUM() 會忽略NULL ，count(*)會計算包含NULL的所有行

 

三、索引最佳化

 

>>>>

索引類型

 

按資料存放區方式分類：

• 聚簇索引：資料行實際上存放在索引的葉子（leaf page）頁中。即資料行和相鄰的索引值緊湊地儲存在一起。 

• 二級索引（非聚簇索引）：二級索引的葉子節點包含了引用行的主鍵列（它不指向行的物理位置，而是行的主索引值）。二級索引需要兩次索引尋找，而不是一次。（對於InnoDb，自適應雜湊索引能夠減少這樣的重複工作） 

 

按索引的資料結構分類：

• B-TREE索引 

• 雜湊索引 

• 空間資料索引（R-TREE) 

• 全文索引 

 

InnoDB主鍵索引結構：

在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的分葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。

 

InnoDB非主鍵索引：

InnoDB的輔助索引data域儲存相應的記錄值及該記錄對應的主鍵的值而不是地址。

 

>>>>

索引策略

• 經常與其他表進行關聯的表，在關聯欄位上應該建立索引；  

• 經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引； 

• 頻繁進行資料操作的表，不要建立太多的索引，資料的插入，更新和刪除會對索引產生影響，太多的索引會導致插入更新刪除操作緩慢；  

• 索引應該建在選擇性高的欄位上Cardinality/rows儘可能等於1。Show index命令查看Cardinality（索引列去重後的行數）。 

• 索引應該建在小欄位上，整數欄位尤其適合，對於大的文字欄位甚至超長欄位，不要建索引，或者建立首碼索引， 如create index 索引名 on 表名(列名1 (指定長度)，……) 

• 刪除無用的索引，如重複索引，不必要的冗餘索引；  

• 針對複合式索引，設計合理的索引列順序 

 

下面介紹一些與索引相關的概念。

 

首碼索引：索引開始的部分字元，以節約索引空間，提高索引效率。

風險：會降低索引的選擇性。

對於BLOB，text或者很長的varchar類型的列，必須使用首碼索引。

 

否則會報錯：

[Err] 1170 - BLOB/TEXT column ‘blobtext‘ used in key specification without a key length

 

 

 

首碼索引的長度有一個權衡點：選擇足夠長的首碼以保證較高 的選擇性，同時又不能太長。 

 

那麼如何計算不同前置長度的選擇性：

 

 

 

查詢顯示當前置長度到達7的時候，再增加前置長度，選擇性提升的幅度已經很小。

 

重複索引：指在相同列上按照相同順序建立相同類型的索引。 （SQL摘抄自《高效能MySQL》）

 

 

 

相當於建了三個重複索引。

 

MySQL需要單獨維護重複索引，最佳化器在最佳化查詢的時候也需要逐個進行考慮，因此 重複索引會影響效能。

 

冗餘索引：

• Case1: 如建立了索引（A,B），再建立索引（A），則產生了冗餘索引，因為索引（A）只是索引（A,B）的首碼索引。

• Case2: 索引（A），再建立索引（A,ID），其中ID是主鍵，對於InnoDB來說主鍵列已經包含在二級索引中了，所以這也是冗餘。

 

什麼時候需要冗餘索引？

 

當擴充已有的索引會導致其變得太大，從而影響其他使用該索引的查詢效能。

 

比如，在整數列上有一個索引，現在需要增多一個VARCHAR列來擴充該索引，此時，如果使用整數列與varchar列的複合式索引比單獨使用整數列的索引的效率要慢很多，因此，此時可以考慮冗餘索引，以滿足不同情境下的query需求。

 

索引列順序：

在多列B-tree索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，…

 

建議將選擇性最高的列放在索引最左列。

 

如何確定選擇性更高的欄位： （SQL摘抄自《高效能MySQL》）

 

 

 

發現customer_id的選擇性更高。

 

索引列順序建議為(customer_id, staff_id)。

 

覆蓋索引 

 

索引包含（或者說覆蓋）所有需要查詢欄位的值。

 

優勢：

• 只需要讀取索引，就可以訪問到資料 

• 索引按照列值順序儲存，順序查詢比隨機io要快。 

 

案例：

 

 

當發起一個被索引覆蓋的查詢時，在explain的extra列可以看到“Using index”的資訊。

 

 

 

不能使用索引的情境

 

在一些情境下，索引不能生效，比如：

• 使用LIKE或者REGEXP時，以%開頭，即“%***”時； 

• 在欄位使用函數時； 

• 在join時條件欄位類型不一致時； 

• 在複合式索引裡使用非第一個索引時； 

• 使用!=以及<>不等於時；

• 索引列不獨立時。

 

四、SQL最佳化

 

Where子句中使用獨立的列：

 

查詢中列如果不是獨立的，則不會使用索引。

 

 

關聯查詢最佳化： 

• 確保ON或者USING子句的列上有索引。一般只需要在關聯順序中的第二個表的相應列上建立索引。 

• 關聯欄位類型保持一致。 

 

LIKE匹配最佳化：

如果 LIKE 的參數是非通配字元開始的固定字串，MySQL在做LIKE比較時也可能用到索引。

 

select * from customer where last_name like ‘MA%‘;

  

 

Extra資訊中顯示使用了索引。

 

like後面使用萬用字元開始的字串則不會使用索引

 

select * from customer where last_name like ‘%MA%‘;

 

 

 

rows列顯示599行，也就是customer表的總行數，因此沒利用到索引。

 

避免SQL中出現不必要的類型轉換：

 

select * from charge_record where phone=13990055761;

 

 

 

select * from charge_record where phone=‘13990055761’;

 

 

 

Select指定列來代替select *：

• 在某些情況下 select *  要比select 指定列 需要浪費更多的資源 

• 如果某些列中含有text等類型，select 指定列可以減少網路傳輸緩衝區的使用 

• 如果SQL中含有order by ,並且排序不能利用上已用的索引那麼，額外的欄位會佔用更多的sort_buffer_size . 

• Select指定列可以方便使用覆蓋索引。 

 

比如下面這個例子，使用到了覆蓋索引。

 

 

子查詢最佳化：

 

• MySQL5.6前，子查詢大多時候會先遍曆outer table，對於其返回的每一條記錄都執行一次subquery，而且子查詢沒有任何索引，導致子查詢相較於關聯查詢要慢很多（解決方案：表串連代替子查詢）；

• MySQL5.6 後，對子查詢進行了大幅度的最佳化，將子查詢結果存入暫存資料表，使得子查詢只執行一次，而且最佳化器還會給子查詢產生的派生表添加索引，使得子查詢效能得到了強勁的最佳化。

 

曾經的“絕對真理”：子查詢比關聯查詢慢很多。——不再成立。

 

通過子查詢最佳化可以減少多個查詢多次對資料進行訪問。

 

但也有時候，子查詢可能比關聯查詢還要快。

 

>>>>

GROUP BY最佳化：

 

表的識別欄位分組比其他列分組的效率高。

 

SELECT actor.first_name, actor.last_name, count(*) FROM film_actor INNER JOIN actor USING (actor_id) GROUP BY actor.first_name, actor.last_name;

 

最佳化後：

SELECT actor.first_name, actor.last_name,count(*) FROM film_actor

INNER JOIN actor USING (actor_id) GROUP BY actor.actor_id ;

 

 

 

因為actor.actor_id是主鍵，分組效率會提升。

 

使用GROUP BY子句時，結果集會自動按照分組的欄位進行排序，GROUP BY子句中可以直接使用DESC或者ASC關鍵字，使得分組的結果集按需要的方向排序。

 

So：如果沒有排序需求，可以加ORDER BY NULL,讓MySQL不再進行檔案排序，從而提高查詢效率。

 

>>>>

UNION最佳化：

 

除非需要消除重複的行，否則一定要使用union all，因為沒有ALL關鍵字，MySQL會給暫存資料表加上DISTINCT選項，使得對整個暫存資料表做代價很高的唯一性檢查。

 

由於union產生的暫存資料表無法使用最佳化器的最佳化策略，所以可以直接將WHERE, ORDER BY, LIMIT等子句冗餘的寫一份到各個子查詢中。

 

案例：

 

  

 

如果把ORDER BY, LIMIT等子句冗餘寫一份到各個子查詢中。

 

 

則排序的基數會有效得到降低，從而提高效率。

 

參考文獻：《高效能MySQL》

 

Q&A

Q1：這個是乘積嗎？那不是笛卡爾積了嗎？

 

A1：這個是乘積，但不是笛卡爾積。笛卡爾積是表的總行數的乘積，這個乘積是嵌套乘積。

 

Q2：在索引以最佳化的前提下，MySQL 單表超過多大就要考慮分表了或者說達到其效能瓶頸了？

A2：MySQL單表過億差不多就達到效能瓶頸了，還可以藉助NoSQL的查詢高效，把熱點資料放在NoSQL裡，減輕MySQL壓力。

 

Q3：線上庫上有幾條select ，執行時間達到上千甚至上萬秒，但我串連資料庫執行只有1秒多，show processlist顯示為 waut to net   max_net_package我已經設定為1個G  伺服器端網路沒問題，請問這個問題該怎麼排查？

A3：1、檢查max_allowed_packet 這個參數是否足夠大且生效；2、線上是否有其他請求會堵塞那幾條select；3、監控mysql服務的cpu io memorybandwidth等。

 

Q4：MySQL中flush table 的運行機制是怎麼樣的？（加鎖還是？）之前因為MySQLdump的備份線上上出現了一個問題導致資料庫宕機： 線上有條執行很長的SQL 這是我在MySQLldump指令碼備份導致後來的SQL一直處於wait to flush table 導致大量的等待 追加一個問題~ 除了備份時有flush table隱士命令，還有什麼操作會有隱式的flushtable，再有就是好想知道 fluh table的實現原理，這個我查了很多資料都沒找到。

A4：flush會加共用鎖定，備份一般都有flush table，因為要保證資料完整性。

中國移動MySQL資料庫最佳化最佳實務