丨索引如何提高查詢的速度

如果你去過館，應(yīng)該會(huì)知道館的檢索系統(tǒng)。館為準(zhǔn)備了檢索包括L中的索，就相當(dāng)于館的檢索 ，它是幫助QL一種結(jié)構(gòu)。我們可以在索引中按照查詢條件，檢索索引字段的值，然后快速定位數(shù)據(jù)記錄的位置，這樣就不需要遍歷整個(gè)數(shù)據(jù)表了。而且，數(shù)據(jù)表中的字段越多，表中數(shù)據(jù)記錄越多，速度提升越是明顯。我來舉個(gè)例子，進(jìn)一步解釋下索引的作用。這里要用到銷售流水表（demo.tran| | |YES|MUL| | |YES | | | |YES | | |datetime|YES|MUL| | | |YES | | | |YES | |cashiernumber| |YES|MUL| | | |YES|MUL| |transuniqueid| |YES | 14 159rowsinset(0.02某個(gè)門店的銷售流水表有400100代代12345678mysql>->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=99+|++++ty|price|transdate+++|1.000|220.00|2020-12-1219:45:36|1.000|220.00|2020-12-1208:56:37++++2rowsinset(808可以看到，結(jié)果總共有28關(guān)聯(lián)，這只是單表的查詢，而且只是一個(gè)門店幾個(gè)月的數(shù)據(jù)而已。而總部是把所有門店的數(shù)據(jù)都匯總到一起，查詢速度更慢，這樣的查詢效率，我們肯定是不能接受的。MySQL支持單字段索引和組合索引，而單字段索引比較常用，我們先來學(xué)習(xí)下創(chuàng)建單字段創(chuàng)建單字段索引，一般有3CREATE多代代1CREATEINDEX索引名ONTABLE表名(字段代代3456CREATETABLE(字段數(shù)據(jù)類型{INDEX|KEY}索引名(字段)代代1ALTERTABLE表名ADD{INDEX|KEY}索引名(字段這里有個(gè)小問題要提醒你一下，給表設(shè)定主鍵約束或者唯一性約束的時(shí)候，L創(chuàng)建主鍵索引或唯一性索引。這也是我建議你在創(chuàng)建表的時(shí)候，一定要定義主鍵的原因之舉個(gè)小例子，我們可以給表demo.trans代代123456789mysql>CREATEINDEXindex_transONdemo.trans(transdate(10));QueryOK,0rowsaffected(1min8.71sec)Records:0Duplicates:0Warnings:mysql>->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=+|++++ty|price|transdate+++|1.000|220.00|2020-12-1219:45:36|1.000|220.00|2020-12-1208:56:37 2rowsinset(0.300.320要知道索引是怎么起作用的，我們需要借助MySQL中的 IN這個(gè)關(guān)鍵字 INSQL代代123456789mysql>IN->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=+++++++|id|select_type|table|partitions|type|possible_keys|key|+++++++|1|SIMPLE|trans|NULL|range|index_trans|index_trans|6|NULL|+++++++1rowinset,1warning(0.00type=range：表示使用索引查詢特定范圍的數(shù)據(jù)記錄。rows=5411：表示需要的記錄數(shù)。possible_keys=index_trans：表示可以選index_transkey=index_trans：表示實(shí)際選擇的索引是extra=Usingindexcondition;Usingwhere;UsingMRR：這里面的信息對(duì)SQL語句的執(zhí)行細(xì)節(jié)做了進(jìn)一步的解釋，包含了3層含義：第一個(gè)是執(zhí)行時(shí)使用了索引，第二個(gè)是執(zhí)行時(shí)通過WHERE條件進(jìn)行了篩選，第三個(gè)是使用了順序磁盤的策略。通過這個(gè)小例子，我們可以發(fā)現(xiàn)，有了索引之后，MySQLSQL在剛剛的查詢中，我們是選擇transdate（時(shí)間）字段來當(dāng)索引字段，你可能會(huì)問，為啥不選別的字段呢？這是因?yàn)椋瑫r(shí)間是查詢條件。MySQL可以按照時(shí)間的限定“20201212不過，你有沒有想過，itemnumberitemnumber代代mysql>CREATEINDEXindex_trans_itemnumberONdemo.transQueryOK,0rowsaffected(43.88Records:0Duplicates:0Warnings:代代123456789mysql>->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=-- +|+++--對(duì)商品編號(hào)的篩選，可以在itemnumber+ty|price|transdate+++|1.000|220.00|2020-12-1219:45:36|1.000|220.00|2020-12-1208:56:37++++2rowsinset(0.38itemnumber這是為啥呢？我們來看看MySQL代代123456789mysql>IN->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=--對(duì)itemnumber++++++|id|select_type|table|partitions|type|possible_keys|key|++++++|1|SIMPLE|trans|NULL|ref|index_trans,index_trans_itemnumber|++++++1rowinset,1warning(0.01我們發(fā)現(xiàn)，“possible_keys=index_trans,index_trans_itemnumber”，就是說MySQL認(rèn)為可以選擇的索引確實(shí)有2個(gè)，一個(gè)是用transdate字段創(chuàng)建的索引index_trans，itemnumberindex_trans_itemnumber。key=index_trans_itemnumber，說明MySQL實(shí)際選擇使用的索引是itemnumber字段創(chuàng)建的索引index_trans_itemnumber。而rows=1192，就表示實(shí)際的數(shù)據(jù)記錄數(shù)只有1192個(gè)，比用transdate創(chuàng)建的索引index_trans的實(shí)際記錄數(shù)要少，這就是MySQL選擇使用itemnumber索引的原因。所以，我建議你在選擇索引字段的時(shí)候，要選擇那些經(jīng)常被用做篩選條件的字段能發(fā)揮索引的作用，提升檢索的效率。在實(shí)際工作中，有時(shí)會(huì)遇到比較復(fù)雜的數(shù)據(jù)表，這種表包括的字段比較多，經(jīng)常需要通過不同的字段篩選數(shù)據(jù)，特別是數(shù)據(jù)表中包含多個(gè)層級(jí)信息。比如我們的銷售流水表就包含了門店信息、收款機(jī)信息和商品信息這3個(gè)收款機(jī)對(duì)應(yīng)多個(gè)從這臺(tái)收款機(jī)銷售出去的商品。我們經(jīng)常要把這些層次信息作為篩選條件，來進(jìn)行查詢。這個(gè)時(shí)候單字段的索引往往不容易發(fā)揮出索引的最大功效，可以使用組branchnumbercashiernumber代代1234567mysql>CREATEINDEXindex_trans_branchnumberONdemo.trans(branchnumber);QueryOK,0rowsaffected(41.49sec)Records:0Duplicates:0Warnings:mysql>CREATEINDEXindex_trans_cashiernumberONdemo.trans(cashiernumber);QueryOK,0rowsaffected(41.95sec)Records:0Duplicates:0Warnings:11|100|1.000|220.00|2020-07-1109:18:3512|100|1.000|220.00|2020-09-0621:21:5813|100|1.000|220.00|2020-11-1015:00:11|100|1.000|220.00|2020-12-2514:28:06|100|1.000|220.00|2021-01-0920:21:44|100|1.000|220.00|2021-02-0810:45:0517 186rowsinset(0.3160.31代代123mysql>IN->->4456789->->->branchnumber=11ANDcashiernumber=->ANDitemnumber=++++++|id|select_type|table|partitions|type|possible_keys|key|++++++|1|SIMPLE|trans|NULL|ref|++++++1rowinset,1warning(0.01MySQL有3個(gè)索引可以用，分別是用branchnumber創(chuàng)建的index_trans_branchnumbercashiernumberindex_trans_cashiernumber和用itemnumber創(chuàng)建的index_trans_itemnumber。最后，MySQLindex_trans_itemnumber1192，花費(fèi)了0.31秒。MySQL3個(gè)字段的索引。MySQL最多支持由16個(gè)字段組成的組合索引。代代1CREATEINDEX索引名ONTABLE表名(字段1，字段代代CREATETABLE(字段數(shù)據(jù)類型{INDEX|KEY}索引名(字段1，字段)代代1ALTERTABLE表名ADD{INDEX|KEY}索引名(字段1，字段3branchnumber、cashiernumber、itemnumber組成的組合索引，如下所示：代代mysql>CREATEINDEXIndex_branchnumber_cashiernumber_itemnumberONQueryOK,0rowsaffected(59.26Records:0Duplicates:0Warnings:代代123456789mysql>->->->->->branchnumber=11ANDcashiernumber=->ANDitemnumber=+++++|itemnumberty|price|transdate+++++|100|1.000|220.00|2020-07-1109:18:35|||||+100|1.000|220.00|2020-09-0621:21:58100|1.000|220.00|2020-11-1015:00:11100|1.000|220.00|2020-12-2514:28:06100|1.000|220.00|2021-01-0920:21:44100|1.000|220.00|2021-02-0810:45:05++++6rowsinset(0.00幾乎是瞬間就完成了，不超過10毫秒。我們看看MySQL代代123456789mysql>IN->->->->--->branchnumber=11ANDcashiernumber=->ANDitemnumber=++++++|id|select_type|table|partitions|type|possible_keys|key|++++++|1|SIMPLE|trans|NULL|ref|++++++1rowinset,1warning(0.01這個(gè)查詢，MySQL4index_trans_itemnumber；MySQL選擇了組合索引，篩選后的記錄只有6條。組合索引被充分利用，篩選更加組合索引的多個(gè)字段是有序的，遵循左對(duì)齊的原則branchnbrrmber和nmber。因此，篩選的條件也要遵循從左向右的原則，如果中斷，那么，斷點(diǎn)后面的條件就沒有辦法利用索引了。比如說我們剛才的條件，branchnumber=11ANDcashiernumber=1ANDitemnumber100，包含了從左到右的所有字段，所以可以最大限度使用全部組合索假如把條件換成“cashiernumber=1ANDitemnumber=100”，由于我們的組合索引是按照branchnumber、cashiernumber和itemnumber的順序建立的，最左邊的字段branchnumber沒有包含到條件當(dāng)中，中斷了，所以這個(gè)條件完全不能使用組合索引。如“branchnumber>10ANDcashiernumber=1ANDitemnumber=100”這個(gè)條件，只能用到組合索引中branchnumber>10的部分，后面的索引就都用不上了。我們來看看MySQL的運(yùn)行計(jì)劃：代代123456789mysql>IN->->->->->branchnumber>10A