一：前言
什么是索引仲器？
索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)煤率。索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的，所以每種存儲(chǔ)引擎中的索引都不一樣乏冀。如MYISAM和InnoDB存儲(chǔ)引擎只支持BTree索引蝶糯；MEMORY儲(chǔ)存引擎可以支持HASH和BTREE索引。
首先辆沦，一些注意點(diǎn):
mysql每次只使用一個(gè)索引
mysql只有在查詢中量數(shù)據(jù)時(shí)才會(huì)使用索引,查詢絕大部分?jǐn)?shù)據(jù)會(huì)拒絕使用索引,從而進(jìn)行全表掃描昼捍，對(duì)于極少量的數(shù)據(jù)，mysql也會(huì)優(yōu)化為不使用索引
對(duì)于聯(lián)合索引“a b c”,在B+樹(shù)中單獨(dú)看b是無(wú)序的,在a等值匹配下,看b部分是有序的
索引不會(huì)包含null值的列
每次對(duì)數(shù)據(jù)進(jìn)行操作,數(shù)據(jù)庫(kù)也會(huì)對(duì)索引進(jìn)行相應(yīng)的操作
索引優(yōu)化肢扯，注意回表問(wèn)題６什纭！Ｎ党俊乍钻！回表問(wèn)題，請(qǐng)移步：http://www.reibang.com/p/edafae5becdb
并不是所有索引對(duì)查詢都有效铭腕，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢優(yōu)化的银择，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引累舷，如一表中有字段 sex浩考，male、female幾乎各一半被盈，那么即使在sex上建了索引也對(duì)查詢效率起不了作用析孽。
索引并不是越多越好搭伤，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率袜瞬，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引怜俐，所以怎樣建索引需要慎重考慮，視具體情況而定吞滞。一個(gè)表的索引數(shù)較好不要超過(guò)6個(gè)佑菩，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要盾沫。
二裁赠、優(yōu)化相關(guān)（ 僅針對(duì)InnoDB存儲(chǔ)引擎所支持的BTree索引）
1.索引的設(shè)計(jì)原則
-
選擇唯一性索引
- 唯一性索引的值是唯一的，可以更快速的通過(guò)該索引來(lái)確定某條記錄赴精。例如佩捞，學(xué)生表中學(xué)號(hào)是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個(gè)學(xué)生的信息蕾哟。如果使用姓名的話一忱，可能存在同名現(xiàn)象，從而降低查詢速度谭确。
-
為經(jīng)常需要排序帘营、分組和聯(lián)合操作的字段建立索引
- 經(jīng)常需要ORDER BY、GROUP BY逐哈、DISTINCT和UNION等操作的字段芬迄，排序操作會(huì)浪費(fèi)很多時(shí)間。如果為其建立索引昂秃，可以有效地避免排序操作禀梳。
-
為常作為查詢條件的字段建立索引
- 如果某個(gè)字段經(jīng)常用來(lái)做查詢條件，那么該字段的查詢速度會(huì)影響整個(gè)表的查詢速度肠骆。因此算途，為這樣的字段建立索引，可以提高整個(gè)表的查詢速度蚀腿。
-
限制索引的數(shù)目
- 索引的數(shù)目不是越多越好嘴瓤。每個(gè)索引都需要占用磁盤空間，索引越多莉钙，需要的磁盤空間就越大纱注。修改表時(shí)，對(duì)索引的重構(gòu)和更新很麻煩胆胰。越多的索引狞贱，會(huì)使更新表變得很浪費(fèi)時(shí)間。
-
盡量使用數(shù)據(jù)量少的索引
- 如果索引的值很長(zhǎng)蜀涨，那么查詢的速度會(huì)受到影響瞎嬉。例如蝎毡，對(duì)一個(gè)CHAR（100）類型的字段進(jìn)行全文檢索需要的時(shí)間肯定要比對(duì)CHAR（10）類型的字段需要的時(shí)間要多。a
-
盡量使用前綴來(lái)索引
- 如果索引字段的值很長(zhǎng)氧枣，最好使用值的前綴來(lái)索引沐兵。例如，TEXT和BLOG類型的字段便监，進(jìn)行全文檢索會(huì)很浪費(fèi)時(shí)間扎谎。如果只檢索字段的前面的若干個(gè)字符，這樣可以提高檢索速度烧董。
-
刪除不再使用或者很少使用的索引
- 表中的數(shù)據(jù)被大量更新毁靶，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要逊移。數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)定期找出這些索引预吆，將它們刪除，從而減少索引對(duì)更新操作的影響胳泉。
-
選取不經(jīng)常修改的列
- 對(duì)索引列的修改在索引文件中也會(huì)進(jìn)行操作
-
聯(lián)合索引中拐叉，最好將分辨度大的字段放在前面
計(jì)算分辨度公式：select distinct(name)/count(name) from table_name
值越大說(shuō)明分辨度越大，則應(yīng)該放在聯(lián)合索引的前面
注意：選擇索引的最終目的是為了使查詢的速度變快扇商。上面給出的原則是最基本的準(zhǔn)則凤瘦，但不能拘泥于上面的準(zhǔn)則。需要根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷案铺，選擇最合適的索引方式蔬芥。
2.sql不使用索引的情況
參與算術(shù)運(yùn)算的索引
參與函數(shù)運(yùn)算的索引
like中“%aaa%”類型索引,而“aaa%”類型使用索引
類型顯式隱式轉(zhuǎn)換
如果mysql估計(jì)全表掃描比使用索引快時(shí),也不會(huì)使用索引
不滿足最左匹配原則
用or分割開(kāi)的條件，or前條件有索引红且，or后的列沒(méi)有索引
order by在select 中查詢的列坝茎，包含索引沒(méi)有包含的列，也會(huì)不使用索引
not in
is null \ is not null ： 用其它相同功能的操作運(yùn)算代替暇番，如：a is not null 改為 a>0 或a>’’等嗤放。不允許字段為空，而用一個(gè)缺省值代替空值壁酬，如申請(qǐng)中狀態(tài)字段不允許為空次酌，缺省為申請(qǐng)。
記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中.
3.sql優(yōu)化
-
盡量減少訪問(wèn)數(shù)據(jù)庫(kù)次數(shù)舆乔，將一些邏輯放在后臺(tái)代碼中處理
在不影響業(yè)務(wù)的情況下岳服，整合簡(jiǎn)單,無(wú)關(guān)聯(lián)和有關(guān)聯(lián)的數(shù)據(jù)庫(kù)訪問(wèn)。
數(shù)據(jù)庫(kù)在內(nèi)部執(zhí)行了許多工作: 解析SQL語(yǔ)句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等希俩。沒(méi)訪問(wèn)一次數(shù)據(jù)庫(kù)便會(huì)消耗這部分資源吊宋。(從緩存讀取數(shù)據(jù)情況除外)
-
in和exists的不同使用狀況
如果子查詢得出的結(jié)果集記錄較少，主查詢中的表較大且又有索引時(shí)應(yīng)該用in, 反之如果外層的主查詢記錄較少颜武，子查詢中的表大璃搜，又有索引時(shí)使用exists拖吼。
其實(shí)我們區(qū)分in和exists主要是造成了驅(qū)動(dòng)順序的改變(這是性能變化的關(guān)鍵)，如果是exists这吻，那么以外層表為驅(qū)動(dòng)表吊档，先被訪問(wèn)，如果是in唾糯，那么先執(zhí)行子查詢怠硼，所以我們會(huì)以驅(qū)動(dòng)表的快速返回為目標(biāo)，那么就會(huì)考慮到索引及結(jié)果集的關(guān)系了 移怯，另外in時(shí)不對(duì)NULL進(jìn)行處理香璃。
in 是把外表和內(nèi)表作hash 連接，而exists是對(duì)外表作loop循環(huán)芋酌，每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢增显。
-
union（去重復(fù)）與union all （不去重復(fù)）
- 所以u(píng)nion all比union效率高雁佳，都滿足的情況下盡量使用union all脐帝。
-
where語(yǔ)句后面的條件順序
- 這一個(gè)網(wǎng)絡(luò)上很多說(shuō)有用，但我感覺(jué)沒(méi)有作用糖权，因?yàn)閿?shù)據(jù)庫(kù)都會(huì)自動(dòng)優(yōu)化查詢堵腹，如果連where后面的條件順序都不能優(yōu)化的話也太差勁了，并且通過(guò)我個(gè)人的測(cè)試星澳，在千萬(wàn)數(shù)量級(jí)的表中疚顷，順序并沒(méi)有影響，不知道在網(wǎng)上為什么那么多說(shuō)有影響的禁偎，以前的版本不可以腿堤？有人測(cè)試過(guò)這方面，有不同的看法如暖，歡迎在評(píng)論區(qū)討論
-
select 避免使用“*：
因?yàn)樵趍ysql中笆檀，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會(huì)很多，由多個(gè)數(shù)據(jù)包組成盒至。但是當(dāng)服務(wù)器響應(yīng)客戶端請(qǐng)求時(shí)酗洒，客戶端必須完整的接收整個(gè)返回結(jié)果，而不能簡(jiǎn)單的只取前面幾條結(jié)果枷遂，然后讓服務(wù)器停止發(fā)送樱衷。因而在實(shí)際開(kāi)發(fā)中，盡量保持查詢簡(jiǎn)單且只返回必需的數(shù)據(jù)酒唉，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個(gè)非常好的習(xí)慣矩桂。
使用*可能會(huì)導(dǎo)致order by不適用索引。
-
用truncate替代delete
Truncate是一個(gè)能夠快速清空資料表內(nèi)所有資料的SQL語(yǔ)法痪伦。并且能針對(duì)具有自動(dòng)遞增值的字段侄榴，做計(jì)數(shù)重置歸零重新計(jì)算的作用阔籽。
另外，當(dāng)你不再需要該表時(shí)牲蜀， 用 drop笆制；當(dāng)你仍要保留該表，但要?jiǎng)h除所有記錄時(shí)涣达， 用 truncate在辆；當(dāng)你要?jiǎng)h除部分記錄時(shí)（always with a WHERE clause), 用 delete.
having只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過(guò)WHERE子句限制記錄的數(shù)目,那就能減少這方面的開(kāi)銷。
-
用>=替代> ：
兩者的區(qū)別在于, 前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄度苔。
如一個(gè)表有100萬(wàn)記錄匆篓，一個(gè)數(shù)值型字段A，30萬(wàn)記錄的A=0寇窑，30萬(wàn)記錄的A=1鸦概，39萬(wàn)記錄的A=2，1萬(wàn)記錄的A=3甩骏。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了窗市，因?yàn)锳>2時(shí)ORACLE會(huì)先找出為2的記錄索引再進(jìn)行比較，而A>=3時(shí)ORACLE則直接找到=3的記錄索引饮笛。
-
join 代替 子查詢
- MySQL從4.1版開(kāi)始支持子查詢（一個(gè)查詢的結(jié)果作為另一個(gè)select子句的條件）咨察，子查詢雖然靈活但執(zhí)行效率不高，因?yàn)槭褂米硬樵儠r(shí)福青，MySQL需要為內(nèi)層查詢語(yǔ)句的查詢結(jié)果建立一個(gè)臨時(shí)表摄狱，然后外層查詢語(yǔ)句從臨時(shí)表中查詢記錄，查詢完畢后 再撤銷這些臨時(shí)表无午，因此子查詢的速度會(huì)相應(yīng)的受到影響媒役。而連接查詢不需要建立臨時(shí)表其查詢速度快于子查詢！
-
使用表的別名(Alias)
- 當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來(lái),就可以減少解析的時(shí)間并減少那些由Column歧義引起的語(yǔ)法錯(cuò)誤宪迟。
-
用UNION替換OR (適用于索引列)
- 通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR可能造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒(méi)有被索引, 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低.
-
盡量使用數(shù)字型字段
- 若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型酣衷，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷踩验。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符鸥诽，而對(duì)于數(shù)字型而言只需要比較一次就夠了。
-
盡量避免使用游標(biāo)
- 因?yàn)橛螛?biāo)的效率較差箕憾，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行牡借，那么就應(yīng)該考慮改寫。
盡量避免向客戶端返回大數(shù)據(jù)量袭异，若數(shù)據(jù)量過(guò)大钠龙，應(yīng)該考慮相應(yīng)需求是否合理。
盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力碴里。
不要用存儲(chǔ)過(guò)程了沈矿，缺點(diǎn)太多了，完全可以由緩存或者其他方式代替
另外：在優(yōu)化sql的同時(shí)咬腋，著重優(yōu)化系統(tǒng)中的慢查詢sql（sql本身層面優(yōu)化羹膳、業(yè)務(wù)優(yōu)化后的sql優(yōu)化等），慢查詢sql才是系統(tǒng)的瓶頸所在根竿。
三：其他
1：order by 優(yōu)化 (來(lái)自網(wǎng)絡(luò)）
mysql支持兩種方式的排序陵像，F(xiàn)ileSort（不使用索引）和Index（使用索引）。
Index效率比FileSort高寇壳，它指MySQL掃描索引本身完成排序醒颖。所以對(duì)于order by子句，盡量使用Index方式排序壳炎，避免使用FileSort方式排序泞歉。
-
order by 滿足三種情況，會(huì)使用Index方式排序
order by 語(yǔ)句使用索引最左前列
使用where子句與order by子句條件列組合滿足索引最左前列
select 查詢的列需要全部屬于使用索引的索引所包含的列
- image
2:最左前綴原則
-
通俗的說(shuō)
最左匹配原則針對(duì)的是聯(lián)合索引(name,age,phoneNum) 匿辩，B+樹(shù)是按照從左到右的順序來(lái)建立搜索樹(shù)的腰耙。如（'張三',18,'18668247652'）來(lái)檢索數(shù)據(jù)的時(shí)候，B+樹(shù)會(huì)優(yōu)先匹配name來(lái)確定搜索方向撒汉，name匹配成功再依次匹配age沟优、phoneNum涕滋，最后檢索到最終的數(shù)據(jù)睬辐。
也就是說(shuō)這種情況下是有三級(jí)索引，當(dāng)name相同宾肺，查找age溯饵，age也相同時(shí)，去比較phoneNum锨用；但是如果拿 （18,'18668247652'）來(lái)檢索時(shí)丰刊，B+樹(shù)沒(méi)有拿到一級(jí)索引，根本就無(wú)法確定下一步的搜索方向增拥。（'張三','18668247652'）這種場(chǎng)景也是一樣啄巧，當(dāng)name匹配成功后，沒(méi)有age這個(gè)二級(jí)索引掌栅，只能在name相同的情況下秩仆，去遍歷所有的phoneNum。
B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)決定了在使用聯(lián)合索引的時(shí)候必須遵守最左前綴原則猾封，在創(chuàng)建聯(lián)合索引的時(shí)候澄耍，盡量將經(jīng)常參與查詢的字段放在聯(lián)合索引的最左邊。
原則測(cè)試：
1.測(cè)試用表
2.索引
3.測(cè)試sql與解釋
explain select * from testIndex where bid = 2 and cid = 3 and did = 4 ;
最左匹配原則，沒(méi)有使用索引
EXPLAIN select * from testIndex where bid = 1 and aid = 1 and did = 1; -- aid,bid,did 只有aid,bid使用索引齐莲，did不適用
ref中只有兩個(gè)const,表明只有兩個(gè)字段使用了索引
EXPLAIN select * from testIndex where bid = 1 and aid = 1 and cid = 1; -- aid,bid,did 都使用索引
測(cè)試是否正確：mysql會(huì)按照聯(lián)合索引從左往右進(jìn)行匹配痢站，直到遇到范圍查詢，如：>,<,between,like等就停止匹配选酗，a = 1 and b =2 and c > 3 and d = 4阵难，如果建立（a,b,c,d）順序的索引，d是不會(huì)使用索引的芒填。但如果聯(lián)合索引是（a,b,d,c）的話多望，則a b d c都可以使用到索引，只是最終c是一個(gè)范圍值氢烘。
explain select * from testIndex where aid = 1 and bid = 2 and did = 4 and cid = 3 ;
通過(guò)key_len判斷4個(gè)字段都使用了索引
explain select * from testIndex where aid = 1 and bid = 2 and did > 4 and cid = 3 ;
通過(guò)key_len為20判斷都是用了索引怀偷，因?yàn)閙ysql會(huì)優(yōu)化sql語(yǔ)句，將did與cid的順序進(jìn)行了排序后為：where aid = 1 and bid = 2 and cid = 3 and did > 4等同于下一個(gè)實(shí)例播玖，所以四個(gè)字段都是用索引
explain select * from testIndex where aid = 1 and bid = 2 and cid = 3 and did > 4 ;
explain select * from testIndex where aid = 1 and bid = 2 and cid > 3 and did = 4 ;
只有前三個(gè)字段使用了索引椎工，所以測(cè)試的那句話是正確的！
如果感覺(jué)這篇文章對(duì)您有所幫助蜀踏，請(qǐng)點(diǎn)擊一下喜歡或者關(guān)注博主维蒙，您的喜歡和關(guān)注將是我前進(jìn)的最大動(dòng)力！
博主所有文章首發(fā)公眾號(hào)：【Coder技術(shù)棧】
【Coder技術(shù)椆玻】公眾號(hào)分享工作中涉及到的技術(shù)知識(shí)颅痊，主要分享數(shù)據(jù)庫(kù)相關(guān)和Java技術(shù)干貨（JVM+并發(fā)+全鏈路優(yōu)化）；涉及計(jì)算機(jī)網(wǎng)絡(luò)局待、數(shù)據(jù)結(jié)構(gòu)與算法斑响、linux等編程知識(shí)；
