03mysql突擊-學習14-六星教育14索引基礎

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