[[333943]]

本文主要是總結(jié)了工作中一些常用的操作，以及不合理的操作，在對(duì)慢查詢(xún)進(jìn)行優(yōu)化時(shí)收集的一些有用的資料和信息，本文適合有一定MySQL基礎(chǔ)的開(kāi)發(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)椴樵?xún)優(yōu)化器發(fā)現(xiàn)某個(gè)值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時(shí)候，它一般會(huì)忽略索引，進(jìn)行全表掃描。慣用的百分比界線是"30%"。

索引失效原因：

對(duì)索引列運(yùn)算，運(yùn)算包括（+、-、*、/、！、<>、%、like'%_'（%放在前面） 類(lèi)型錯(cuò)誤，如字段類(lèi)型為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)為索引，mysql8.0開(kāi)始支持函數(shù)索引，5.7可以通過(guò)虛擬列的方式來(lái)支持，之前只能新建一個(gè)ROUND(t.logicdb_id)列然后去維護(hù)

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

索引的建立

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

二、EXPLIAN中有用的信息

基本用法

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

提高性能的特性

•  索引覆蓋(covering index)：需要查詢(xún)的數(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版本開(kāi)始當(dāng)ICP打開(kāi)時(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顯示的索引類(lèi)型會(huì)顯示index_merge，EXTRA會(huì)顯示具體的合并算法和用到的索引

extra字段

1. using filesort：說(shuō)明MySQL會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。MySQL中無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序” ，其實(shí)不一定是文件排序，內(nèi)部使用的是快排

2. using temporary：  使用了臨時(shí)表保存中間結(jié)果，MySQL在對(duì)查詢(xún)結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序order by和分組查詢(xún)group by

3. using index：表示相應(yīng)的SELECT操作中使用了覆蓋索引（Covering Index），避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)。

6. impossible where：WHERE子句的值總是false，不能用來(lái)獲取任何元組

7. select tables optimized away：在沒(méi)有GROUP BY子句的情況下基于索引優(yōu)化MIN/MAX操作或者對(duì)于MyISAM存儲(chǔ)引擎優(yōu)化COUNT(*)操作， 不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢(xún)執(zhí)行計(jì)劃生成的階段即完成優(yōu)化

8. 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)間roup by就是先排序在分組，如果沒(méi)有排序的需要，可以加上一個(gè)order by NULL來(lái)避免排序，這樣using filesort就會(huì)去除，能提升一點(diǎn)性能。

type字段

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

三、字段類(lèi)型和編碼

mysql返回字符串長(zhǎng)度：CHARACTER_LENGTH方法(CHAR_LENGTH一樣的)返回的是字符數(shù)，LENGTH函數(shù)返回的是字節(jié)數(shù)，一個(gè)漢字三個(gè)字節(jié)

varvhar等字段建立索引長(zhǎng)度計(jì)算語(yǔ)句：select count(distinct left(test,5))/count(*) from table; 越趨近1越好

mysql的utf8最大是3個(gè)字節(jié)不支持emoji表情符號(hào)，必須只用utf8mb4。需要在mysql配置文件中配置客戶端字符集為utf8mb4。jdbc的連接串不支持配置characterEncoding=utf8mb4，最好的辦法是在連接池中指定初始化sql，例如：hikari連接池，其他連接池類(lèi)似spring.datasource.hikari.connection-init-sql=set names utf8mb4。否則需要每次執(zhí)行sql前都先執(zhí)行set names utf8mb4。

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

utf8_genera_ci不區(qū)分大小寫(xiě)，ci為case insensitive的縮寫(xiě)，即大小寫(xiě)不敏感，

utf8_general_cs區(qū)分大小寫(xiě)，cs為case sensitive的縮寫(xiě)，即大小寫(xiě)敏感，但是目前

MySQL版本中已經(jīng)不支持類(lèi)似于***_genera_cs的排序規(guī)則，直接使用utf8_bin替代。

utf8_bin將字符串中的每一個(gè)字符用二進(jìn)制數(shù)據(jù)存儲(chǔ)，區(qū)分大小寫(xiě)。

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

cs為case sensitive的縮寫(xiě)，即大小寫(xiě)敏感；bin的意思是二進(jìn)制，也就是二進(jìn)制編碼比較。

utf8_general_cs排序規(guī)則下，即便是區(qū)分了大小寫(xiě)，但是某些西歐的字符和拉丁字符是不區(qū)分的，比如ä=a，但是有時(shí)并不需要ä=a，所以才有utf8_bin

utf8_bin的特點(diǎn)在于使用字符的二進(jìn)制的編碼進(jìn)行運(yùn)算，任何不同的二進(jìn)制編碼都是不同的，因此在utf8_bin排序規(guī)則下：ä<>a

sql yog中初始連接指定編碼類(lèi)型使用連接配置的初始化命令

四、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
•  從查詢(xún)語(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)(作為了解，很少用)

共享鎖：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í)用到：

強(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)

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

SQL編寫(xiě)注意

•  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è)表

五、踩坑

•  如果有自增列，truncate語(yǔ)句會(huì)把自增列的基數(shù)重置為0，有些場(chǎng)景用自增列作為業(yè)務(wù)上的id需要十分重視
•  聚合函數(shù)會(huì)自動(dòng)濾空，比如a列的類(lèi)型是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 

七、慢查詢(xún)?nèi)罩?/strong>