本文主要總結(jié)了工作中一些常用的操作及不合理的操作，在對慢查詢進行優(yōu)化時收集的一些有用的資料和信息，本文適合有 MySQL 基礎(chǔ)的開發(fā)人員。

索引相關(guān)

索引基數(shù)

基數(shù)是數(shù)據(jù)列所包含的不同值的數(shù)量，例如，某個數(shù)據(jù)列包含值 1、3、7、4、7、3，那么它的基數(shù)就是 4。

索引的基數(shù)相對于數(shù)據(jù)表行數(shù)較高(也就是說，列中包含很多不同的值，重復(fù)的值很少)的時候，它的工作效果***。

如果某數(shù)據(jù)列含有很多不同的年齡，索引會很快地分辨數(shù)據(jù)行;如果某個數(shù)據(jù)列用于記錄性別(只有“M”和“F”兩種值)，那么索引的用處就不大;如果值出現(xiàn)的幾率幾乎相等，那么無論搜索哪個值都可能得到一半的數(shù)據(jù)行。

在這些情況下，***根本不要使用索引，因為查詢優(yōu)化器發(fā)現(xiàn)某個值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。慣用的百分比界線是“30%”。

索引失效原因

索引失效的原因有如下幾點：

• 對索引列運算，運算包括(+、-、*、/、!、<>、%、like'%_'(% 放在前面)。
• 類型錯誤，如字段類型為 varchar，where 條件用 number。
• 對索引應(yīng)用內(nèi)部函數(shù)，這種情況下應(yīng)該要建立基于函數(shù)的索引。例如 select * from template t where ROUND (t.logicdb_id) = 1，此時應(yīng)該建 ROUND (t.logicdb_id) 為索引。

MySQL 8.0 開始支持函數(shù)索引，5.7 可以通過虛擬列的方式來支持，之前只能新建一個 ROUND (t.logicdb_id) 列然后去維護。

• 如果條件有 or，即使其中有條件帶索引也不會使用(這也是為什么建議少使用 or 的原因)，如果想使用 or，又想索引有效，只能將 or 條件中的每個列加上索引。
• 如果列類型是字符串，那一定要在條件中數(shù)據(jù)使用引號，否則不使用索引。
• B-tree 索引 is null 不會走，is not null 會走，位圖索引 is null，is not null 都會走。
• 組合索引遵循最左原則。

索引的建立

索引的建立需要注意以下幾點：

• 最重要的肯定是根據(jù)業(yè)務(wù)經(jīng)常查詢的語句。
• 盡量選擇區(qū)分度高的列作為索引，區(qū)分度的公式是 COUNT(DISTINCT col) / COUNT(*)，表示字段不重復(fù)的比率，比率越大我們掃描的記錄數(shù)就越少。
• 如果業(yè)務(wù)中唯一特性***建立唯一鍵，一方面可以保證數(shù)據(jù)的正確性，另一方面索引的效率能大大提高。

EXPLIAN 中有用的信息

基本用法

EXPLIAN 基本用法如下：

• desc 或者 explain 加上你的 SQL。
• extended explain 加上你的 SQL，然后通過 show warnings 可以查看實際執(zhí)行的語句，這一點也是非常有用的，很多時候不同的寫法經(jīng) SQL 分析后，實際執(zhí)行的代碼是一樣的。

提高性能的特性

EXPLIAN 提高性能的特性如下：

• 索引覆蓋(covering index)：需要查詢的數(shù)據(jù)在索引上都可以查到不需要回表 EXTRA 列顯示 using index。
• ICP特性(Index Condition Pushdown)：本來 index 僅僅是 data access 的一種訪問模式，存數(shù)引擎通過索引回表獲取的數(shù)據(jù)會傳遞到 MySQL Server 層進行 where 條件過濾。

5.6 版本開始當 ICP 打開時，如果部分 where 條件能使用索引的字段，MySQL Server 會把這部分下推到引擎層，可以利用 index 過濾的 where 條件在存儲引擎層進行數(shù)據(jù)過濾。

EXTRA 顯示 using index condition。需要了解 MySQL 的架構(gòu)圖分為 Server 和存儲引擎層。

• 索引合并(index merge)：對多個索引分別進行條件掃描，然后將它們各自的結(jié)果進行合并(intersect/union)。

一般用 or 會用到，如果是 AND 條件，考慮建立復(fù)合索引。EXPLAIN 顯示的索引類型會顯示 index_merge，EXTRA 會顯示具體的合并算法和用到的索引。

Extra 字段

Extra 字段使用：

• using filesort：說明 MySQL 會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取。

MySQL 中無法利用索引完成的排序操作稱為“文件排序”，其實不一定是文件排序，內(nèi)部使用的是快排。

• using temporary：使用了臨時表保存中間結(jié)果，MySQL 在對查詢結(jié)果排序時使用臨時表。常見于排序 order by 和分組查詢 group by。
• using index：表示相應(yīng)的 SELECT 操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數(shù)據(jù)行，效率不錯。
• impossible where：where 子句的值總是 false，不能用來獲取任何元組。
• select tables optimized away：在沒有 group by 子句的情況下基于索引優(yōu)化 MIN/MAX 操作或者對于 MyISAM 存儲引擎優(yōu)化 COUNT(*) 操作，不必等到執(zhí)行階段再進行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化。
• distinct：優(yōu)化 distinct 操作，在找到***匹配的元組后即停止找同樣值的操作。

using filesort、using temporary 這兩項出現(xiàn)時需要注意下，這兩項是十分耗費性能的。

在使用 group by 的時候，雖然沒有使用 order by，如果沒有索引，是可能同時出現(xiàn) using filesort，using temporary 的。

因為 group by 就是先排序在分組，如果沒有排序的需要，可以加上一個 order by NULL 來避免排序，這樣 using filesort 就會去除，能提升一點性能。

type 字段

type 字段使用：

• system：表只有一行記錄(等于系統(tǒng)表)，這是 const 類型的特例，平時不會出現(xiàn)。
• const：如果通過索引依次就找到了，const 用于比較主鍵索引或者 unique 索引。因為只能匹配一行數(shù)據(jù)，所以很快。如果將主鍵置于 where 列表中，MySQL 就能將該查詢轉(zhuǎn)換為一個常量。
• eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。
• ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質(zhì)上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而它可能會找到多個符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體。
• range：只檢索給定范圍的行，使用一個索引來選擇行。key 列顯示使用了哪個索引，一般就是在你的 where 語句中出現(xiàn) between、<、>、in 等的查詢。
• 這種范圍掃描索引比全表掃描要好，因為只需要開始于縮印的某一點，而結(jié)束于另一點，不用掃描全部索引。
• index：Full Index Scan ，index 與 ALL 的區(qū)別為 index 類型只遍歷索引樹，這通常比 ALL 快，因為索引文件通常比數(shù)據(jù)文件小。
• 也就是說雖然 ALL 和 index 都是讀全表，但 index 是從索引中讀取的，而 ALL 是從硬盤讀取的。
• all：Full Table Scan，遍歷全表獲得匹配的行。

字段類型和編碼

MySQL 返回字符串長度

CHARACTER_LENGTH(同CHAR_LENGTH)方法返回的是字符數(shù)，LENGTH 函數(shù)返回的是字節(jié)數(shù)，一個漢字三個字節(jié)。

varchar 等字段建立索引長度計算語句

select count(distinct left(test,5))/count(*) from table;越趨近 1 越好。

MySQL 的 utf8

MySQL 的 utf8 ***是 3 個字節(jié)不支持 emoji 表情符號，必須只用 utf8mb4。需要在 MySQL 配置文件中配置客戶端字符集為 utf8mb4。

JDBC 的連接串不支持配置 characterEncoding=utf8mb4，***的辦法是在連接池中指定初始化 SQL。

例如：hikari 連接池，其他連接池類似 spring . datasource . hikari . connection - init - sql =set names utf8mb4。否則需要每次執(zhí)行 SQL 前都先執(zhí)行 set names utf8mb4。

MySQL 排序規(guī)則

一般使用 _bin 和 _genera_ci：

• utf8_genera_ci 不區(qū)分大小寫，ci 為 case insensitive 的縮寫，即大小寫不敏感。
• utf8_general_cs 區(qū)分大小寫，cs 為 case sensitive 的縮寫，即大小寫敏感，但是目前 MySQL 版本中已經(jīng)不支持類似于 ***_genera_cs 的排序規(guī)則，直接使用 utf8_bin 替代。
• utf8_bin 將字符串中的每一個字符用二進制數(shù)據(jù)存儲，區(qū)分大小寫。

那么，同樣是區(qū)分大小寫，utf8_general_cs 和 utf8_bin 有什么區(qū)別?

• cs 為 case sensitive 的縮寫，即大小寫敏感;bin 的意思是二進制，也就是二進制編碼比較。
• utf8_general_cs 排序規(guī)則下，即便是區(qū)分了大小寫，但是某些西歐的字符和拉丁字符是不區(qū)分的，比如 ä=a，但是有時并不需要 ä=a，所以才有 utf8_bin。
• utf8_bin 的特點在于使用字符的二進制的編碼進行運算，任何不同的二進制編碼都是不同的，因此在 utf8_bin 排序規(guī)則下：ä<>a。

初始化命令

SQLyog 中初始連接指定編碼類型使用連接配置的初始化命令，如下圖：

 

SQL 語句總結(jié)

常用但容易忘的

SQL 語句常用但容易忘的總結(jié)如下：

• 如果有主鍵或者唯一鍵沖突則不插入：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。
• 復(fù)制表結(jié)構(gòu)：CREATE TABLE user_v2 LIKE user。
• 從查詢語句中導(dǎo)入：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。

鎖相關(guān)

鎖相關(guān)(作為了解，很少用)：

• 共享鎖：select id from tb_test where id = 1 lock in share mode。
• 排它鎖：select id from tb_test where id = 1 for update。

優(yōu)化時用到

優(yōu)化時用到：

• 強制使用某個索引：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。

查看狀態(tài)

查看狀態(tài)：

• 查看字符集：SHOW VARIABLES LIKE 'character_set%'。
• 查看排序規(guī)則：SHOW VARIABLES LIKE 'collation%'。

SQL 編寫注意

SQL 編寫請注意：

• where 語句的解析順序是從右到左，條件盡量放 where 不要放 having。
• 采用延遲關(guān)聯(lián)(deferred join)技術(shù)優(yōu)化超多分頁場景，比如 limit 10000,10,延遲關(guān)聯(lián)可以避免回表。
• distinct 語句非常損耗性能，可以通過 group by 來優(yōu)化。
• 連表盡量不要超過三個表。

踩坑

踩坑總結(jié)如下：

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

千萬大表在線修改

MySQL 在表數(shù)據(jù)量很大的時候，如果修改表結(jié)構(gòu)會導(dǎo)致鎖表，業(yè)務(wù)請求被阻塞。

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 

慢查詢?nèi)罩?/span>

有時候如果線上請求超時，應(yīng)該去關(guān)注下慢查詢?nèi)罩?，慢查詢的分析很簡單，先找到慢查詢?nèi)罩疚募奈恢?，然后利?mysqldumpslow 去分析。

查詢慢查詢?nèi)罩拘畔⒖梢灾苯油ㄟ^執(zhí)行 SQL 命令查看相關(guān)變量，常用的 SQL 如下：

 

mysqldumpslow 的工具十分簡單，我主要用到的參數(shù)如下：

• -t：限制輸出的行數(shù)，我一般取前十條就夠了。
• -s：根據(jù)什么來排序默認是平均查詢時間 at，我還經(jīng)常用到 c 查詢次數(shù)，因為查詢次數(shù)很頻繁但是時間不高也是有必要優(yōu)化的，還有 t 查詢時間，查看那個語句特別卡。
• -v：輸出詳細信息。

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

查看 SQL 進程和殺死進程

如果你執(zhí)行了一個 SQL 的操作，但是遲遲沒有返回，你可以通過查詢進程列表看看它的實際執(zhí)行狀況。

如果該 SQL 十分耗時，為了避免影響線上可以用 kill 命令殺死進程，通過查看進程列表也能直觀的看下當前 SQL 的執(zhí)行狀態(tài);如果當前數(shù)據(jù)庫負載很高，在進程列表可能會出現(xiàn)，大量的進程夯住，執(zhí)行時間很長。

命令如下：

--查看進程列表 
SHOW PROCESSLIST; 
--殺死某個進程 
kill 183665 

如果你使用的 SQLyog，那么也有圖形化的頁面，在菜單欄→工具→顯示→進程列表。

在進程列表頁面可以右鍵殺死進程，如下所示：

 

一些數(shù)據(jù)庫性能的思考

在對公司慢查詢?nèi)罩咀鰞?yōu)化的時候，很多時候可能是忘了建索引，像這種問題很容易解決，加個索引就行了。但是有幾種情況就不是簡單加索引能解決了：

業(yè)務(wù)代碼循環(huán)讀數(shù)據(jù)庫

考慮這樣一個場景，獲取用戶粉絲列表信息，加入分頁是十個，其實像這樣的 SQL 是十分簡單的，通過連表查詢性能也很高。

但是有時候，很多開發(fā)采用了取出一串 ID，然后循環(huán)讀每個 ID 的信息，這樣如果 ID 很多對數(shù)據(jù)庫的壓力是很大的，而且性能也很低。

統(tǒng)計 SQL

很多時候，業(yè)務(wù)上都會有排行榜這種，發(fā)現(xiàn)公司有很多地方直接采用數(shù)據(jù)庫做計算，在對一些大表做聚合運算的時候，經(jīng)常超過五秒，這些 SQL 一般很長而且很難優(yōu)化。

像這種場景，如果業(yè)務(wù)允許(比如一致性要求不高或者是隔一段時間才統(tǒng)計的)，可以專門在從庫里面做統(tǒng)計。另外我建議還是采用 Redis 緩存來處理這種業(yè)務(wù)。

超大分頁

在慢查詢?nèi)罩局邪l(fā)現(xiàn)了一些超大分頁的慢查詢?nèi)?Limit 40000，1000，因為 MySQL 的分頁是在 Server 層做的，可以采用延遲關(guān)聯(lián)在減少回表。

但是看了相關(guān)的業(yè)務(wù)代碼正常的業(yè)務(wù)邏輯是不會出現(xiàn)這樣的請求的，所以很有可能是有惡意用戶在刷接口，***在開發(fā)的時候也對接口加上校驗攔截這些惡意請求。