03mysql突擊-學(xué)習(xí)14-六星教育14索引基礎(chǔ)

0..0..2SQLObteehshPage/ ?CopyrighMonday,Jun0,209,:33PMbyB+tree的結(jié)在nnoDB引擎中，每一個(gè)索引都對(duì)應(yīng)一棵B+ree，nnoDB的索引主要分為主索引和輔助索引keykey就是主索引，也就是主鍵，也被稱為聚簇索引。因?yàn)闊o(wú)法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)索引。在nnoDB中，主索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)，這也意味著nnoDB中的表一定要有一個(gè)主索引；輔助索引：某個(gè)key指定的順序與文件記錄的物理順序不同，這個(gè)key就是輔助索引。nnoDB中的輔助索引在葉子節(jié)點(diǎn)中并不實(shí)際的數(shù)據(jù)，只會(huì)包含nnoDB的表中要求必須有一個(gè)主鍵，那么可能有人會(huì)將號(hào)這種唯一性的標(biāo)識(shí)作為主索引，這樣就大錯(cuò)特錯(cuò)了。剛剛說(shuō)到主鍵也被稱為聚簇索引，它是要 號(hào)作為主鍵，不能保證每次插入的數(shù)據(jù)都是按照號(hào)的順序進(jìn)行排列的，這就使得每次主鍵的插入都變得完全隨機(jī)，可能導(dǎo)致每次插入一條數(shù)據(jù)都會(huì)引起頁(yè)的問(wèn)題（這個(gè)話題在后面會(huì)講到）。所以在表結(jié)構(gòu)定義的時(shí)候，應(yīng)該使用一個(gè)具有性的key作為主鍵，如果真的沒(méi)有的話，可以使用一個(gè)AUONCREMEN鍵作為主索引，這樣可以保證數(shù)據(jù)行是順序?qū)懭氲?。如果你真的完全沒(méi)有定義主鍵，會(huì)選擇一個(gè)唯一的非空索引代替，但是如果沒(méi)有這樣的索引，InnoDB會(huì)隱式定義一個(gè)主鍵來(lái)作為索引1a12a1314a05j1聯(lián)合索引在查詢的時(shí)候，比如要找Ace，2這條記錄wereame="alice"adage=MySQL會(huì)先根據(jù)name查找到了兩條數(shù)據(jù)，然后再根據(jù)age找到d2的這條數(shù)據(jù)結(jié)合B+ree的特點(diǎn)，自增主鍵是連續(xù)的，在插入過(guò)程中盡量減少頁(yè)，即使要進(jìn)行頁(yè)，也只會(huì)很少一部分。并且能減少數(shù)據(jù)的移動(dòng)，每次插入都是插3.2回但是因?yàn)間ende對(duì)應(yīng)的b+te的記錄中只含有g(shù)ende這個(gè)字段，而在查詢中需要monhsaaygende所記錄的數(shù)據(jù)。這時(shí)需要把從上一步中獲取到的每一條記錄的d字段都到聚簇索引對(duì)應(yīng)的B+索引gender會(huì)先根據(jù)gender的值進(jìn)行排序，所以值為0的記錄在磁盤(pán)中的是相連的，集中分布在一個(gè)或幾個(gè)數(shù)據(jù)頁(yè)中，我們可以很快的把這些連著的記錄 方式我們也可以稱為順序/O完整的用戶記錄可能分布在不同的數(shù)據(jù)頁(yè)中，這樣完整的用戶記錄可能要的數(shù)據(jù)頁(yè)，這種方式我們也可以稱為隨機(jī)/O。一般情況下，順序/O比隨機(jī)/O的性能高很多，所以步驟1的執(zhí)行可能很快，而步驟2就慢一些。所以這個(gè)使用索引gender的查詢有這么兩個(gè)特點(diǎn)：會(huì)使用到兩個(gè)B+樹(shù)索引，一個(gè)二級(jí)索引，一個(gè)聚簇索引。二級(jí)索引使用順序/O，聚簇索引使用隨機(jī)/Ohash索 (usngndex)。不過(guò)內(nèi)存中的行的速度很快，索引Tablee CTablee Clmellnaiiy l Y A A1 全文索引(full全文索引，在MySQL5.6之前僅有mysam引擎支持，而在5.6及以上的版本中開(kāi)始nnodb支持全文索引。所謂全文索引，是一種通過(guò)建立倒排索引，快速eenngramngram就是一段文字里面連續(xù)的n個(gè)字的序列。ngram全文解析器能夠?qū)ξ谋具M(jìn)行分詞，每個(gè)單詞是連續(xù)的n個(gè)字的序列。例如，用ngram:s0Y As1 e Clmellnaiiy l e 下除了customers.frm和customers.bd文件外還有如上的文FS0000000000000031000000000000005cNDEX1~6.bd這6個(gè)文件用于倒排索引，的是分詞和位置以及 進(jìn)行分區(qū)，映射到不同的文件中；文件名規(guī)則為FS{ABLED{NDEXDINDEX{N.bdFS0000000000000031BENGDELEED.bd包含已經(jīng)被刪除的DOCDFS0000000000000031DELEEDCACHE.bd是前者的內(nèi)存緩存（但是搜索了下代碼，只有當(dāng)ftscachet::deleteddocids被使用時(shí)，才會(huì)在sync時(shí)轉(zhuǎn)儲(chǔ)到該表中，但并沒(méi)有發(fā)現(xiàn)任何地方使用這個(gè)對(duì)象）FS0000000000000031BENGDELEEDCACHE.bdFS0000000000000031BENGDELEED.bd，包含了已經(jīng)被刪除索引記錄并且正在從全文索引中移除的DOCD，前者是后者的內(nèi)存版本，這兩個(gè)表主要用于輔助進(jìn)行OPMZEABLE時(shí)將DELEED/DELEEDCACHED表中的記錄轉(zhuǎn)儲(chǔ)到其中。FS0000000000000031CONFG.bd，包含全文索引的內(nèi)部信息，最重要的是FSSYNCEDDOCD，表示已經(jīng)解析并刷到磁盤(pán)的docd，在恢復(fù)普通DML Bed0199806v內(nèi)0198411f0193007ed0199806v內(nèi)0198411f0193007v內(nèi) 198411f 193007r 192304WORDWORDDODATDD D N1101 e索引弊端：當(dāng)創(chuàng)建了一個(gè)索引之后，比如tabe創(chuàng)建了一個(gè)dxcty的索引，這個(gè)時(shí)候，當(dāng)數(shù)據(jù)表tabe內(nèi)容一旦發(fā)生了改變之后，MySQL就會(huì)重新調(diào)整索引的結(jié)挑選索引-索引應(yīng)該刨建在搜索、排序、歸組等操作所涉及的數(shù)據(jù)列上,只在輸出報(bào)告里出現(xiàn)的數(shù)據(jù)列不是好的候選。換句話說(shuō),WHERE子句、關(guān)聯(lián)檢索中的FROM子句、ORDERBY或GROUPBY子句中出現(xiàn)過(guò)的數(shù)據(jù)列最適合用來(lái)創(chuàng)建索引。只在SELEC關(guān)鍵字后面的輸出列里出現(xiàn)過(guò)的數(shù)據(jù)列并不是好的候推推 同的數(shù)據(jù)行很好地區(qū)分開(kāi);如果某個(gè)數(shù)據(jù)列里存放的是用來(lái)表示的M和F兩種值,建立在其上的索引恐怕就幫不上多大的忙了如果數(shù)據(jù)值的分布比較均勻,那么,不管使用哪個(gè)值(M或F)進(jìn)行搜索都會(huì)匹配到大約50%的數(shù)據(jù)行。在這種情況下MySQL selectcou(*),v(tsalary)rocuomrweregeder=0;就可以創(chuàng)建一個(gè)dxgendermonthsaay的索引還是有很多情況是不會(huì)使用到的比如之前題目的第二題查詢同城市的客戶數(shù)量和平均月薪setprofiling=>)m111rowsinset(42.70sec)>rDn )m11rowinset,1rig(0.00sec)> s g m g tgtmp Sendingdata Cratigsort query g query cleaning 20rowsinset,1wig(0.00會(huì)發(fā)現(xiàn)查詢時(shí)間特別長(zhǎng)通過(guò)explain>)m1 select_type partitionstypepossible_keys key_len E 1 NULLNULL 100.00igtmr;iggym>showindexrmt; e Clumellnaiiy e1 Y1A 1 y1A 1 y2yA 但是我們可以通過(guò)一個(gè)技巧給這個(gè)操作使用上ix_gender_motsaary這個(gè)索引改寫(xiě)一下>)m111rowsinset(24.14sec)>)m1 select_type partitionstype key_len E cs1NULL ddrmsaryNULLNULL 100.00gw;igtemprry;igfilesortg這個(gè)案例顯示了一個(gè)基本原則考慮表上所有的選項(xiàng)。當(dāng)設(shè)計(jì)索引時(shí)不要只為現(xiàn)有的査詢考慮需要哪些索引需要?jiǎng)?chuàng)建新索引但是這個(gè)索引又會(huì)降低另一些查詢的效率那么應(yīng)該想一下是否能優(yōu)化原來(lái)的查詢。應(yīng)該同時(shí)優(yōu)化查詢和索引以找到最佳的平衡altertabler1dropindex;1;ys>explainselectatslr)omct1groupbycity; select_typetable partitionstype key_lenref E cs1 indexy y 100.00igindex;igtmprry;sgg>)m111rowsinset(1.01sec)s>selectutatala)mr1wheregender ) s>explainselecttatala)mmr1wheregender select_typetable partitionstypepossible_keys key_lenref E 1 y y 100.00igg>)mcount(*)gotsa) Page/ ?CopyrighMonday,Jun0,209,:33PMbynn 存在內(nèi)存里的索引多,索引緩存區(qū)中率也就越高。(當(dāng)然,你也得有點(diǎn)。如果只對(duì)數(shù)據(jù)列的第一個(gè)字符進(jìn)行索引,它的用處就可能沒(méi)有這么大,因?yàn)槟蹷在讀記錄的變長(zhǎng)字段長(zhǎng)度列表時(shí)先查看表結(jié)構(gòu)，如果某個(gè)變長(zhǎng)字段允許的最大字節(jié)數(shù)大于時(shí)，該怎