演講嘉賓簡介：鄭旦，阿裡雲進階資料庫專家
以下内容根據演講視訊以及PPT整理而成。
本次分享主要圍繞以下三個方面：
一、MySQL基本原理
二、庫表設計規範
三、診斷實踐案例
一、MySQL基本原理
MySQL是非常常見的資料庫，适用于多種場景中的應用，其次MySQL活躍的社群使其流行度非常高。同時，MySQL也是雲上售賣最多的産品之一。
SQl執行流程
SQL是應用和資料庫之間的橋梁，SQL的執行效率對應用來說至關重要。下圖展示了一條SQL的執行流程：
1）首先是用戶端通過MySQL協定與MySQL Server建立連接配接，MySQL Server負責建立連接配接，健全認證和管理連接配接。在阿裡雲的RDS上提供了線程池的能力，一旦打開線程池，使得通過認證的使用者直接可以擷取線程，這适合于大量短連結和高并發的場景。
2）連接配接到MySQL Server後，進入查詢緩存層，如果開啟了緩存或者通過語句設定了緩存開關，此時就需要檢查SQL中是否包含緩存，如果存在緩存，結果直接傳回，如果沒有緩存則進入下一階段。查詢緩存階段需要注意緩存本身和查詢緩存都是非常消耗資源的，如果開啟緩存需要提前對應用做大量評估，密集型的應用請慎重開啟。若必須要開啟，可以參考語句級的緩存設定，自由的控制哪些查詢需要進入緩存。
3）其次進入詞法解析和文法解析階段。SQL會生成一顆解析樹。詞法解析階段會解析關鍵字，文法解析階段判斷MySQL的文法和庫表，以及檢查表名和列名是否都存在。同時MySQL會結合自身的規則，進行SQL的改寫，如關系代數轉換等。
4）之後是進入MySQL最複雜的優化器環節。優化器使用了非常多的優化政策來生成最優的執行計劃，MySQL是基于成本的優化器，是以會預測多種優化政策的成本，選擇成本最低的執行計劃進行執行。
5）明确執行計劃之後，存儲引擎會調用執行計劃，完成最後的SQL執行，并且将執行結果傳回給用戶端。如果此時開啟了查詢緩存，執行結果會同時放在緩存階段。
索引類型
SQL的執行效率提升是至關重要的，SQL提效方面最常用的是索引政策。索引的基本作用主要是将随機IOh轉化為順序IO，減少IO，并且減少記憶體計算，如比較、排序等等。索引是快速定位記錄的一種資料結構的方法，主要類型有B+Tree索引、Hash索引、空間索引（R-Tree）以及全文索引等。B+Tree索引支援等值、範圍檢索；Hash索引支援等值檢索；空間索引（R-Tree）支援地理資料檢索（多元資料）；全文索引支援非結構化資料檢索。
B+Tree索引結構
以B+Tree為例，下圖展示了B+Tree索引結構。左側兩列的第一列是主鍵索引，第二列是非主鍵索引，分别将主鍵索引和非主鍵索引全部插入到B+Tree中。兩個樹的共性都是以page為基本機關，分為根節點、分支節點、葉子節點三層，非葉子節點存放的是葉子節點的索引，葉子節點對應的是資料層，包含完整資料，并且有序，可以在檢索的時候提效。非葉子節點上存放的是葉子+主鍵，也是有序排列，互相指向。B+Tree是一顆平衡樹，任一值搜尋深度相同。檢索深度與IO消耗直接相關。
層高和資料量
下圖建立的table主鍵是int類型，c1是int類型的非主鍵索引，c2是一般列，varchar字段。從下圖可以得到表結構定義的具體資訊，首先是主鍵的key長度（Clustered index key）是4 bytes，非主鍵索引key長度（Secondary index key）也是4 bytes，指針（Key pointer）是8個bytes。假設在平均行長度（Average row length）是200個bytes的情況下，page size 是16K，即16384 bytes。節點填充率（Average node occupancy）為70%的情況下，在主鍵索引中一個page可以存放的資料（Average row per page(Pri Key)）是page sizeAverage node occupancy/Average row length≈50行，非主鍵索引（Average row per page(Sec Key)）是page sizeAverage node occupancy/(Secondary index key+Clustered index key)≈1400行，非葉子節點存放的是索引+指針的資訊（Non-leaf fanout），是以可以存放的資料為page sizeAverage node occupancy/(Secondary index key+Key pointer)≈1000行。下圖表格中給出了不同層高下，主鍵索引和非主鍵索引資料量的情況。主鍵索引下在層高為2時，Non-leaf fanoutAverage row per page(Pri Key)≈50000，非主鍵索引下也同理Non-leaf fanout*Average row per page(Sec Key)≈140w。其它層高也同理。
那如果表行數是1000w，分别在主鍵索引和非主鍵索引，以及全表掃描下IO消耗情況如何。主鍵索引1000W對應的層高是3，帶來了3次随機IO，非主鍵索引1000w對應的層高也是3層，但并不能擷取到全部表的資訊，還需要加上主鍵的IO消耗，此時等于c1的IO消耗c1數量+回到主鍵索引的IO消耗=（3c1數量）+1。若按照c2查詢，上面沒有索引，是以隻能從第一行逐漸查找（全表掃描），1000w行需要消耗20w的IO。這樣的話三種掃描的差距就展現出來了。同時這裡補充一下，MySQL可以支撐的資料量與表結構相關，與具體的SQL相關。
查詢代價
主鍵查詢優先于二級索引查詢，即非主鍵索引。而二級索引查詢優先于全表掃描，單表查詢優先于連接配接查詢，表連接配接數量越少越好，連接配接查詢時的IO消耗等于驅動表的全面掃描*被驅動表的索引消耗，是以可以允許的情況下，盡可能控制join的數量。最後一條是通過使用索引避免排序代價。
二、庫表設計規範
表結構設計
第一條設計規範是降低單條記錄長度，日高緩存使用率。如果長度太長，每個配置下存放的記錄數就會降低，緩存率自然也會跟着降低。可以在業務核心表上降低單表的記錄長度。第二條是将通路頻率低、大字段拆分，用主鍵關聯，提高緩存命中率。第三條是适當備援，不要使用多表join查詢。第四條是在分庫分表場景下，避免資料傾斜。
索引設計
第一條是選擇過濾性高的字段建立索引，即通過distinct（col）和count（*）的比值，判斷過濾性。第二條是在Join查詢中連接配接字段建立索引，避免全表掃描。第三條是盡量使用覆寫索引，将Select item後面的列加到非主鍵索引中，進而避免在非主鍵索引中回表到主鍵索引的操作，無需通路主鍵索引表，避免随機IO。第四條是利用字首索引，将索引長度變短，單個配置下的索引行數變多，提高緩存率。最後一條是盡量避免建重複索引，提高索引使用率。太多的索引會使得寫入性能變差。
SQL書寫
第一條是建議讀寫都采用主鍵索引。盡量利用索引排序，避免産生臨時表，如order by。避免對查詢字段進行計算。避免使用select *，字段少可以配合覆寫索引。避免使用全模糊查詢。對表而言，通路的應用非常多，是以通路的SQL也會很多，在索引的設計上好綜合考慮，保證核心SQL的通路。
三、診斷實踐案例
在MySQL的實踐中有三種案例，一類是SQL優化實踐案例，另一類是主備延遲實踐案例，還有空間優化實踐案例。
SQL優化實踐案例
減少磁盤IO通路
下圖中對表建立了A、B、C三列的符合索引，前兩個案例都使用了A列。第3個和第4個是範圍查詢.在B+Tree中，如第4個中的A的值不确定時，B的值是無法使用到的。第5個A的值是list，是固定的，A和B都可以被使用到。第6個A值沒有，B值也用不到。第7個A值确定，但是B是範圍值，是以C值也用不到。
傳回更少資料
以select 為例，直接全部資料傳回，對select 語句中寫所需的列時，可以帶來以下優點。首先是減少網絡傳輸開銷，隻傳回了所需要的列。其次是減少處理開銷，還減少了用戶端記憶體占用。還可以在字段變更時提前發現問題，減少程式BUG。最後在轉換到Name和ID時，有機會使用到覆寫索引，避免回表資料。
減少互動次數
Col in()可以代替多次col=？，但注意in的範圍不要太多，避免索引失敗。第二個是可以使用batch DML操作，此時需要注意平衡，避免鎖過大問題。第三個和第四個是阿裡雲RDS上特有的文法，select from update 和commit on success/rollback on fail hint文法。在，select from update适用與單行update之後擷取更新後的場景，避免兩次通路的開銷。commit on success/rollback on fail hint文法，在送出時直接成功，若失敗了可以rollback，适合于高吞吐下的優化。以上方法還是不夠的話，可以回到業務邏輯中進行優化。這樣的優化可以減少互動次數的網絡開銷，減少了文法、語義分析，執行計劃生成過程中的開銷。減少了事務送出次數，兩階段送出成本和IO成本。還可以減少鎖持有時間。
減少CPU開銷
下表有A和B列，其中Order by B 不符合符合索引的最左原則。其中最後以個Order by A[ASC/DESC]，B[DESC/ASC]，如果A和B順序不一緻，在8.0之前就不能使用這個索引，8.0之後可以。通過索引的使用可以減少CPU開銷。
主備延遲實踐案例
當發現有主備延遲時，首先要檢查主庫和備庫上的容量，出現的問題一般是主庫和備庫的資源不一緻，備庫無法支撐主庫的發展，就像水龍頭水很大，但是桶不夠大。第二個排查點是主庫和備庫的同步狀态，如果符合同步狀态，可以檢查線程狀态是否有鎖等待。如果上面的排查不能解決主備延遲問題，還需要進一步深入排查，在主庫側做DDL變更排查，是否有超大事務，這會影響到系統的穩定性。最後在庫表設計方面檢查是否有不合理的設計，如無主鍵表，外鍵限制等。
空間優化實踐案例
空間優化主要從三種檔案入手，資料檔案，臨時檔案和日志檔案等。資料檔案優化方案包括庫表結構設計是否合理，檢查主鍵設計是否合理，是否因為delete操作導緻碎片放大。還包括備援索引的檢查，還需要定期的對碎片過多問題進行optimize操作。臨時檔案場景三種優化方案包括通過适當調大sort_buffer_size，避免操作過程中帶來性能慢的問題，其次是建立合适的索引避免排序，最後是統計報表類查詢考慮換存儲。但對資料量大的業務，MySQL并不适合，可以考慮阿裡雲的RDS。最後是日志檔案，首先要檢查日志檔案裡面是否使用了大字段，其次對于沒有使用訂閱增量的資料，可以考慮使用truncase替代delete from，避免bin log中由于有大量的delete from清空表操作帶來的日志檔案。