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

一驹尼、索引相關(guān)

1、索引基數(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%”拙绊。

2向图、索引失效原因

對(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)為索引摄悯，MySQL8.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 不會(huì)走伙菜，is not 會(huì)走轩缤，位圖索引 is ，is not 都會(huì)走贩绕；

組合索引遵循最左原則火的。

3、索引的建立

最重要的肯定是根據(jù)業(yè)務(wù)經(jīng)常查詢的語(yǔ)句淑倾；

盡量選擇區(qū)分度高的列作為索引馏鹤，區(qū)分度的公式是 COUNT(DISTINCT col) / COUNT(*)，表示字段不重復(fù)的比率娇哆，比率越大我們掃描的記錄數(shù)就越少湃累；

如果業(yè)務(wù)中唯一特性最好建立唯一鍵勃救，一方面可以保證數(shù)據(jù)的正確性，另一方面索引的效率能大大提高治力。

二蒙秒、EXPLIAN中有用的信息

1、基本用法

desc或者explain加上你的SQL宵统；

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

2痊班、提高性能的特性

索引覆蓋(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ì)顯示具體的合并算法和用到的索引柑晒。

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

4卖毁、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，遍歷全表獲得匹配的行耳舅。

參考地址：

https://blog.csdn.net/DrDanger/article/details/79092808

三碌上、字段類型和編碼

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

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

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

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

3）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箩言。

4）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薇搁。

5）sql yog中初始連接指定編碼類型使用連接配置的初始化命令

四斋扰、SQL語(yǔ)句總結(jié)

1、常用但容易忘的

如果有主鍵或者唯一鍵沖突則不插入：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

2宏娄、鎖相關(guān)(作為了解问裕，很少用)

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

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

3、優(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;

4孵坚、查看狀態(tài)

查看字符集：SHOW VARIABLES LIKE 'character_set%';

查看排序規(guī)則：SHOW VARIABLES LIKE 'collation%';

5粮宛、SQL編寫注意

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列的類型是int且全部是帅刊，則SUM(a)返回的是而不是0；

MySQL判斷相等不能用“a=”漂问，這個(gè)結(jié)果永遠(yuǎn)為UnKnown赖瞒，where和having中，UnKnown永遠(yuǎn)被視為false蚤假，check約束中栏饮，UnKnown就會(huì)視為true來(lái)處理。所以要用“a is ”處理磷仰。

六袍嬉、千萬(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)罩?br>

有時(shí)候如果線上請(qǐng)求超時(shí)瞒爬，應(yīng)該去關(guān)注下慢查詢?nèi)罩竟樵兊姆治龊芎?jiǎn)單，先找到慢查詢?nèi)罩疚募奈恢貌嗟缓罄胢ysqldumpslow去分析矢空。查詢慢查詢?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)單加索引能解決了：

1女轿、業(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ù)的壓力是很大的，而且性能也很低淀零。

2挽绩、統(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ù)。

3、超大分頁(yè)

在慢查詢?nèi)罩局邪l(fā)現(xiàn)了一些超大分頁(yè)的慢查詢?nèi)鏻imit 40000,1000割卖，因?yàn)镸ySQL的分頁(yè)是在server層做的前酿，可以采用延遲關(guān)聯(lián)在減少回表。但是看了相關(guān)的業(yè)務(wù)代碼正常的業(yè)務(wù)邏輯是不會(huì)出現(xiàn)這樣的請(qǐng)求的鹏溯，所以很有可能是有惡意用戶在刷接口罢维，最好在開發(fā)的時(shí)候也對(duì)接口加上校驗(yàn)攔截這些惡意請(qǐng)求。

這篇文章就總結(jié)到這里丙挽，希望能夠?qū)Υ蠹矣兴鶐椭畏酰”疚闹饕偨Y(jié)了工作中一些常用的操作及不合理的操作，在對(duì)慢查詢進(jìn)行優(yōu)化時(shí)收集的一些有用的資料和信息取试，本文適合有MySQL基礎(chǔ)的開發(fā)人員悬槽。

一、索引相關(guān)

1瞬浓、索引基數(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%”火欧。

2棋电、索引失效原因

對(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)為索引嘿期，MySQL8.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 不會(huì)走棠笑，is not 會(huì)走，位圖索引 is 禽绪，is not 都會(huì)走蓖救；

組合索引遵循最左原則。

3印屁、索引的建立

最重要的肯定是根據(jù)業(yè)務(wù)經(jīng)常查詢的語(yǔ)句循捺；

盡量選擇區(qū)分度高的列作為索引，區(qū)分度的公式是 COUNT(DISTINCT col) / COUNT(*)雄人，表示字段不重復(fù)的比率从橘，比率越大我們掃描的記錄數(shù)就越少念赶；

如果業(yè)務(wù)中唯一特性最好建立唯一鍵，一方面可以保證數(shù)據(jù)的正確性恰力，另一方面索引的效率能大大提高叉谜。

二、EXPLIAN中有用的信息

1踩萎、基本用法

desc或者explain加上你的SQL停局；

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

2锭碳、提高性能的特性

索引覆蓋(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ì)顯示具體的合并算法和用到的索引。

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

4烈拒、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膀懈，遍歷全表獲得匹配的行顿锰。

參考地址：

https://blog.csdn.net/DrDanger/article/details/79092808

三、字段類型和編碼

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

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

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

select count(distinct left(test,5))/count(*) from table狐血；越趨近1越好

3）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。

4）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拌消。

5）sql yog中初始連接指定編碼類型使用連接配置的初始化命令

四挑豌、SQL語(yǔ)句總結(jié)

1、常用但容易忘的

如果有主鍵或者唯一鍵沖突則不插入：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

2鹦筹、鎖相關(guān)(作為了解铝阐，很少用)

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

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

3、優(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;

4铐拐、查看狀態(tài)

查看字符集：SHOW VARIABLES LIKE 'character_set%';

查看排序規(guī)則：SHOW VARIABLES LIKE 'collation%';

5徘键、SQL編寫注意

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列的類型是int且全部是，則SUM(a)返回的是而不是0达传；

MySQL判斷相等不能用“a=”篙耗，這個(gè)結(jié)果永遠(yuǎn)為UnKnown，where和having中宪赶，UnKnown永遠(yuǎn)被視為false宗弯，check約束中，UnKnown就會(huì)視為true來(lái)處理搂妻。所以要用“a is ”處理蒙保。

六、千萬(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)罩?/p>

有時(shí)候如果線上請(qǐng)求超時(shí)昧互，應(yīng)該去關(guān)注下慢查詢?nèi)罩荆樵兊姆治龊芎?jiǎn)單伟桅，先找到慢查詢?nèi)罩疚募奈恢贸ň颍缓罄胢ysqldumpslow去分析。查詢慢查詢?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)單加索引能解決了：

1、業(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ù)的壓力是很大的，而且性能也很低溜宽。

2吉拳、統(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ù)。

3、超大分頁(yè)

在慢查詢?nèi)罩局邪l(fā)現(xiàn)了一些超大分頁(yè)的慢查詢?nèi)鏻imit 40000,1000杰标，因?yàn)镸ySQL的分頁(yè)是在server層做的兵怯，可以采用延遲關(guān)聯(lián)在減少回表。但是看了相關(guān)的業(yè)務(wù)代碼正常的業(yè)務(wù)邏輯是不會(huì)出現(xiàn)這樣的請(qǐng)求的腔剂，所以很有可能是有惡意用戶在刷接口媒区，最好在開發(fā)的時(shí)候也對(duì)接口加上校驗(yàn)攔截這些惡意請(qǐng)求。

這篇文章就總結(jié)到這里掸犬，希望能夠?qū)Υ蠹矣兴鶐椭?/p>