MySQL DBA基本知識點梳理和查詢最佳化

本文主要是總結了工作中一些常用的操作，以及不合理的操作，在對慢查詢進行最佳化時收集的一些有用的資料和資訊，本文適合有MySQL基礎的開發人員。

一、索引相關

索引基數：基數是資料列所包含的不同值的數量。例如，某個資料列包含值1、3、7、4、7、3，那麼它的基數就是4。索引的基數相對於資料錶行數較高（也就是說，列中包含很多不同的值，重覆的值很少）的時候，它的工作效果最好。如果某資料列含有很多不同的年齡，索引會很快地分辨資料行。如果某個資料列用於記錄性別（只有”M”和”F”兩種值），那麼索引的用處就不大。如果值出現的機率幾乎相等，那麼無論搜尋哪個值都可能得到一半的資料行。在這些情況下，最好根本不要使用索引，因為查詢最佳化器發現某個值出現在表的資料行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。慣用的百分比界線是”30%”。

索引失效原因：

對索引列運算，運算包括（+、-、*、/、！、<>、%、like’%_’（%放在前面） 型別錯誤，如欄位型別為varchar，where條件用number。

對索引應用內部函式，這種情況下應該建立基於函式的索引 如select * from template t where ROUND(t.logicdb_id) = 1

此時應該建ROUND(t.logicdb_id)為索引，mysql8.0開始支援函式索引，5.7可以透過虛擬列的方式來支援，之前只能新建一個ROUND(t.logicdb_id)列然後去維護

• 如果條件有or，即使其中有條件帶索引也不會使用（這也是為什麼建議少使用or的原因），如果想使用or，又想索引有效，只能將or條件中的每個列加上索引
• 如果列型別是字串，那一定要在條件中資料使用引號，否則不使用索引；B-tree索引 is null不會走,is not null會走，點陣圖索引 is null,is not null 都會走組合索引遵循最左原則

索引的建立

最重要的是根據業務經常查詢的陳述句儘量選擇區分度高的列作為索引，區分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示欄位不重覆的比率，比率越大我們掃描的記錄數就越少。如果業務中唯一特性最好建立唯一鍵，一方面可以保證資料的正確性，另一方面索引的效率能大大提高

二、EXPLIAN中有用的資訊

基本用法

• desc 或者 explain 加上sql
• extended explain加上sql，然後透過show warnings可以檢視實際執行的陳述句，這一點也是非常有用的，很多時候不同的寫法經過sql分析之後實際執行的程式碼是一樣的

提高效能的特性

• 索引改寫(covering index)：需要查詢的資料在索引上都可以查到不需要回表EXTRA列顯示using index
• ICP特性(Index Condition Pushdown)：本來index僅僅是data access的一種訪問樣式，存數引擎透過索引回表獲取的資料會傳遞到MySQL server層進行where條件過濾,5.6版本開始當ICP開啟時，如果部分where條件能使用索引的欄位，MySQL server會把這部分下推到引擎層，可以利用index過濾的where條件在儲存引擎層進行資料過濾。EXTRA顯示using index condition。需要瞭解mysql的架構圖分為server和儲存引擎層
• 索引合併(index merge)：對多個索引分別進行條件掃描，然後將它們各自的結果進行合併(intersect/union)。一般用OR會用到，如果是AND條件，考慮建立複合索引。EXPLAIN顯示的索引型別會顯示index_merge，EXTRA會顯示具體的合併演演算法和用到的索引

extra欄位

註：using filesort，using temporary這兩項出現時需要註意下，這兩項是十分耗費效能的，在使用group by的時候，雖然沒有使用order by，如果沒有索引，是可能同時出現using filesort,using temporary的，因為group by就是先排序在分組，如果沒有排序的需要，可以加上一個order by NULL來避免排序，這樣using filesort就會去除，能提升一點效能。

type欄位

• system：表只有一行記錄（等於系統表），這是const型別的特例，平時不會出現
• const：如果透過索引依次就找到了，const用於比較主鍵索引或者unique索引。 因為只能匹配一行資料，所以很快。如果將主鍵置於where串列中，MySQL就能將該查詢轉換為一個常量
• eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描
• ref：非唯一性索引掃描，傳回匹配某個單獨值的所有行。本質上也是一種索引訪問，它傳回所有匹配 某個單獨值的行，然而它可能會找到多個符合條件的行，所以它應該屬於查詢和掃描的混合體
• range：只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是在你的where陳述句中出現between、、in等的查詢，這種範圍掃描索引比全表掃描要好，因為只需要開始於縮印的某一點，而結束於另一點，不用掃描全部索引
• index：Full Index Scan ，index與ALL的區別為index型別只遍歷索引樹，這通常比ALL快，因為索引檔案通常比資料檔案小。 （也就是說雖然ALL和index都是讀全表， 但index是從索引中讀取的，而ALL是從硬碟讀取的）
• all：Full Table Scan，遍歷全表獲得匹配的行

三、欄位型別和編碼

mysql傳回字串長度：CHARACTER_LENGTH方法(CHAR_LENGTH一樣的)傳回的是字元數，LENGTH函式傳回的是位元組數，一個漢字三個位元組

varvhar等欄位建立索引長度計算陳述句：select count(distinct left(test,5))/count(*) from table; 越趨近1越好

mysql的utf8最大是3個位元組不支援emoji表情符號，必須只用utf8mb4。需要在mysql配置檔案中配置客戶端字符集為utf8mb4。jdbc的連線串不支援配置characterEncoding=utf8mb4，最好的辦法是在連線池中指定初始化sql，例如：hikari連線池，其他連線池類似spring.datasource.hikari.connection-init-sql=set names utf8mb4。否則需要每次執行sql前都先執行set names utf8mb4。

msyql排序規則(一般使用_bin和_genera_ci)：

utf8_genera_ci不區分大小寫，ci為case insensitive的縮寫，即大小寫不敏感，

utf8_general_cs區分大小寫，cs為case sensitive的縮寫，即大小寫敏感，但是目前

MySQL版本中已經不支援類似於***_genera_cs的排序規則，直接使用utf8_bin替代。

utf8_bin將字串中的每一個字元用二進位制資料儲存，區分大小寫。

那麼，同樣是區分大小寫，utf8_general_cs和utf8_bin有什麼區別？

cs為case sensitive的縮寫，即大小寫敏感；bin的意思是二進位制，也就是二進位制編碼比較。

utf8_general_cs排序規則下，即便是區分了大小寫，但是某些西歐的字元和拉丁字元是不區分的，比如ä=a，但是有時並不需要ä=a，所以才有utf8_bin

utf8_bin的特點在於使用字元的二進位制的編碼進行運算，任何不同的二進位制編碼都是不同的，因此在utf8_bin排序規則下：ä<>a

sql yog中初始連線指定編碼型別使用連線配置的初始化命令

四、SQL陳述句總結

常用的但容易忘的：

• 如果有主鍵或者唯一鍵衝突則不插入：insert ignore into

• 如果有主鍵或者唯一鍵衝突則更新,註意這個會影響自增的增量：INSERT INTO room_remarks(room_id,room_remarks) VALUE(1,”sdf”) ON DUPLICATE KEY UPDATE room_remarks=”234″

• 如果有就用新的替代，values如果不包含自增列，自增列的值會變化： REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,”sdf”)

• 備份表：CREATE TABLE user_info SELECT * FROM user_info

• 複製表結構：CREATE TABLE user_v2 LIKE user

• 從查詢陳述句中匯入：INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user

• 連表更新：UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id

• 連表刪除：DELETE user FROM user,black WHERE user.id=black.id

鎖相關(作為瞭解，很少用)

共享鎖： select id from tb_test where id = 1 lock in share mode;

排它鎖： select id from tb_test where id = 1 for update

最佳化時用到：

強制使用某個索引： select * from table force index(idx_user) limit 2;

禁止使用某個索引： select * from table ignore index(idx_user) limit 2;

禁用快取(在測試時去除快取的影響)： select SQL_NO_CACHE from table limit 2;

檢視狀態

• 檢視字符集 SHOW VARIABLES LIKE ‘character_set%’;

• 檢視排序規則 SHOW VARIABLES LIKE ‘collation%’;

SQL編寫註意

• where陳述句的解析順序是從右到左，條件儘量放where不要放having
• 採用延遲關聯(deferred join)技術最佳化超多分頁場景，比如limit 10000,10,延遲關聯可以避免回表
• distinct陳述句非常損耗效能，可以透過group by來最佳化
• 連表儘量不要超過三個表

五、踩坑

• 如果有自增列，truncate陳述句會把自增列的基數重置為0，有些場景用自增列作為業務上的id需要十分重視
• 聚合函式會自動濾空，比如a列的型別是int且全部是NULL，則SUM(a)傳回的是NULL而不是0
• mysql判斷null相等不能用“a=null”,這個結果永遠為UnKnown，where和having中,UnKnown永遠被視為false，check約束中，UnKnown就會視為true來處理。所以要用“a is null”處理

六、千萬大表線上修改

mysql在表資料量很大的時候，如果修改表結構會導致鎖表，業務請求被阻塞。mysql在5.6之後引入了線上更新，但是在某些情況下還是會鎖表，所以一般都採用pt工具( Percona Toolkit)

如對錶新增索引：

pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_user_id(room_id,create_time)" D=fission_show_room_v2,t=room_favorite_info --execute

七、慢查詢日誌

有時候如果線上請求超時，應該去關註下慢查詢日誌，慢查詢的分析很簡單，先找到慢查詢日誌檔案的位置，然後利用mysqldumpslow去分析。查詢慢查詢日誌資訊可以直接透過執行sql命令檢視相關變數，常用的sql如下：

— 檢視慢查詢配置

— slow_query_log  慢查詢日誌是否開啟

— slow_query_log_file 的值是記錄的慢查詢日誌到檔案中

— long_query_time 指定了慢查詢的閾值

— log_queries_not_using_indexes 是否記錄所有沒有利用索引的查詢

SHOW VARIABLES LIKE ‘%quer%’;

 

— 檢視慢查詢是日誌還是表的形式

SHOW VARIABLES LIKE ‘log_output’

 

— 檢視慢查詢的數量

SHOW GLOBAL STATUS LIKE ‘slow_queries’;

mysqldumpslow的工具十分簡單，我主要用到的是引數如下：

• -t:限制輸出的行數，我一般取前十條就夠了
• -s:根據什麼來排序預設是平均查詢時間at，我還經常用到c查詢次數，因為查詢次數很頻繁但是時間不高也是有必要最佳化的，還有t查詢時間，檢視那個陳述句特別卡。
• -v:輸出詳細資訊

例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500

八、檢視sql行程和殺死行程

如果你執行了一個sql的操作，但是遲遲沒有傳回，你可以透過查詢行程串列看看他的實際執行狀況，如果該sql十分耗時，為了避免影響線上可以用kill命令殺死行程，透過檢視行程串列也能直觀的看下當前sql的執行狀態，如果當前資料庫負載很高，在行程串列可能會出現，大量的行程夯住，執行時間很長。命令如下：

–檢視行程串列

SHOW PROCESSLIST;

–殺死某個行程

kill 183665

如果你使用的sqlyog，那麼也有圖形化的頁面，在選單欄-工具-顯示-行程串列。在行程串列頁面可以右鍵殺死行程。如下所示：

檢視行程串列

殺死行程

九、一些資料庫效能的思考

在對公司慢查詢日誌做最佳化的時候，很多時候可能是忘了建索引，像這種問題很容易解決，加個索引就行了。但是有兩種情況就不是簡單能加索引能解決了：

• 業務程式碼迴圈讀資料庫： 考慮這樣一個場景，獲取使用者粉絲串列資訊 加入分頁是十個 其實像這樣的sql是十分簡單的，透過連表查詢效能也很高，但是有時候，很多開發採用了取出一串id，然後迴圈讀每個id的資訊，這樣如果id很多對資料庫的壓力是很大的，而且效能也很低
• 統計sql：很多時候，業務上都會有排行榜這種，發現公司有很多地方直接採用資料庫做計算，在對一些大表的做聚合運算的時候，經常超過五秒，這些sql一般很長而且很難最佳化， 像這種場景，如果業務允許（比如一致性要求不高或者是隔一段時間才統計的），可以專門在從庫裡面做統計。另外我建議還是採用redis快取來處理這種業務
• 超大分頁: 在慢查詢日誌中發現了一些超大分頁的慢查詢如 limit 40000,1000，因為mysql的分頁是在server層做的，可以採用延遲關聯在減少回表。但是看了相關的業務程式碼正常的業務邏輯是不會出現這樣的請求的，所以很有可能是有惡意使用者在刷介面，所以最好在開發的時候也對介面加上校驗攔截這些惡意請求。

陳芳志

贊賞

朋友會在“發現-看一看”看到你“在看”的內容

確定