章穎
資料研發工程師
現任中國移動杭州研發中心資料研發工程師，擅長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工具：
explain select ……
explain extended select ……
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;的結果可以看出，排序結果與建表時的順序一緻，如果需要按字元創的字母順序排序，則需要通過額外的方法來處理，比如：
按照需要的順序來定義枚舉列；
在查詢中使用field()函數顯示地指定排序順序，但這會導緻mysql無法利用索引消除排序。
與varchar相比，enum優勢與劣勢：
優勢：資料緊湊，存儲的是整數，占用空間小，作為關聯字段時，效率比varchar類型高很多；
劣勢：字元串清單是固定的，添加或者删除字元串必須使用alter table,如果添加的字元串不在清單末尾，則需要重建整個表完成修改。由于enum儲存為整數，必須進行查找才能轉換為字元串，在需要轉換為字元串時有一些開銷。在一些特定情況下，把varchar列和枚舉列進行關聯可能比varchar自關聯更慢。
盡量使用整型定義辨別列
因為整形資料的執行計算和比較都很快；
不建議使用uuid等随機字元串作為辨別列，因為随機字元串會任意分布在很大的空間，導緻insert和select語句變得很慢。
>>>>
使用相同資料類型存儲相似或者相關的值，尤其是關聯條件中使用的列
因為混用不同的資料類型可能導緻性能問題，在關聯條件中會有資料類型轉換的資源消耗；
在比較操作時隐形類型轉換可能導緻很難發現的錯誤。
關于整數類型指定寬度的一個解釋
mysql可以為整數類型指定寬度，如int(11),但對大多數應用來說，這并沒有什麼意義：它不會限制值的合法範圍，隻是規定了mysql的一些互動工具（例如mysql指令行用戶端）用來顯示字元的個數。對于存儲和計算來說，int(1) 和int(20)是相同的。
關于實數類型
mysql既支援精确類型（decimal, numeric），也支援不精确類型(float, double)。
可以使用decimal存儲比bigint還大的整數。
cpu不支援對decimal的直接計算，而是mysql伺服器自身對decimal進行高精度計算。而cpu直接支援原生浮點運算，是以，浮點運算明顯更快。
可以考慮使用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與空字元串的差別
上圖中分别insert了一個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時條件字段類型不一緻時；
在組合索引裡使用非第一個索引時；
使用!=以及<>不等于時；
索引列不獨立時。
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，因為要保證資料完整性。
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-08-12</b>