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

索引相關(guān)

索引基數(shù)

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

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

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

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

索引失效原因

索引失效的原因有如下幾點(diǎn)：

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

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

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

索引的建立

索引的建立需要注意以下幾點(diǎn)：

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

EXPLIAN 中有用的信息

基本用法

EXPLIAN 基本用法如下：

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

提高性能的特性

EXPLIAN 提高性能的特性如下：

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

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

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

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

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

Extra 字段

Extra 字段使用：

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

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

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

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

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

因?yàn)?group by 就是先排序在分組，如果沒(méi)有排序的需要，可以加上一個(gè) order by NULL 來(lái)避免排序，這樣 using filesort 就會(huì)去除，能提升一點(diǎn)性能。

type 字段

type 字段使用：

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

字段類型和編碼

MySQL 返回字符串長(zhǎng)度

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

varchar 等字段建立索引長(zhǎng)度計(jì)算語(yǔ)句

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

MySQL 的 utf8

MySQL 的 utf8 ***是 3 個(gè)字節(jié)不支持 emoji 表情符號(hào)，必須只用 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 將字符串中的每一個(gè)字符用二進(jìn)制數(shù)據(jù)存儲(chǔ)，區(qū)分大小寫。

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

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

初始化命令

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

 

SQL 語(yǔ)句總結(jié)

常用但容易忘的

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

• 如果有主鍵或者唯一鍵沖突則不插入：insert ignore into。
• 如果有主鍵或者唯一鍵沖突則更新，注意這個(gè)會(huì)影響自增的增量：INSERT INTO room_remarks(room_id,room_remarks)VALUE(1,"sdf") ON DUPLICATE KEY UPDATE room_remarks = "234"。
• 如果有就用新的替代，values 如果不包含自增列，自增列的值會(huì)變化：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。
• 從查詢語(yǔ)句中導(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)化時(shí)用到

優(yōu)化時(shí)用到：

• 強(qiáng)制使用某個(gè)索引：select * from table force index(idx_user) limit 2。
• 禁止使用某個(gè)索引：select * from table ignore index(idx_user) limit 2。
• 禁用緩存(在測(cè)試時(shí)去除緩存的影響)：select SQL_NO_CACHE from table limit 2。

查看狀態(tài)

查看狀態(tài)：

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

SQL 編寫注意

SQL 編寫請(qǐng)注意：

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

踩坑

踩坑總結(jié)如下：

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

千萬(wàn)大表在線修改

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

MySQL 在 5.6 之后引入了在線更新，但是在某些情況下還是會(huì)鎖表，所以一般都采用 PT 工具( Percona Toolkit)。

如對(duì)表添加索引：

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>

有時(shí)候如果線上請(qǐng)求超時(shí)，應(yīng)該去關(guān)注下慢查詢?nèi)罩荆樵兊姆治龊芎?jiǎn)單，先找到慢查詢?nèi)罩疚募奈恢茫缓罄?mysqldumpslow 去分析。

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

 

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

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

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

查看 SQL 進(jìn)程和殺死進(jìn)程

如果你執(zhí)行了一個(gè) SQL 的操作，但是遲遲沒(méi)有返回，你可以通過(guò)查詢進(jìn)程列表看看它的實(shí)際執(zhí)行狀況。

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

命令如下：

--查看進(jìn)程列表 
SHOW PROCESSLIST; 
--殺死某個(gè)進(jìn)程 
kill 183665 

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

在進(jìn)程列表頁(yè)面可以右鍵殺死進(jìn)程，如下所示：

 

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

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

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

考慮這樣一個(gè)場(chǎng)景，獲取用戶粉絲列表信息，加入分頁(yè)是十個(gè)，其實(shí)像這樣的 SQL 是十分簡(jiǎn)單的，通過(guò)連表查詢性能也很高。

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

統(tǒng)計(jì) SQL

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

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

超大分頁(yè)

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

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