丨查詢(xún)有點(diǎn)慢語(yǔ)句該如何寫(xiě)

雖然MySQL的查詢(xún)分析語(yǔ)句并不能直接優(yōu)化查詢(xún)，但是卻可以幫助你了解SQL語(yǔ)句的執(zhí)行計(jì)劃，有助于你分析查詢(xún)效率低下的原因，進(jìn)而有針對(duì)性地進(jìn)行優(yōu)化。查詢(xún)分析語(yǔ)句的語(yǔ)法結(jié)構(gòu)是：1{ IN|DESCRIBE|DESC}查詢(xún)語(yǔ)句假設(shè)有一個(gè)銷(xiāo)售流水表（demo.trans），里面有400萬(wàn)條數(shù)據(jù)，如下所現(xiàn)在，我要查詢(xún)一下商品編號(hào)是1商品2020618上午9到12之間mysql>SELECTitemnumber,->FROM->WHERE->ANDtransdate>'2020-06-18->ANDtransdate<'2020-06-186 7|itemnumber ty|price|transdate8 9|1|0.276|70.00|2020-06-1811:04:0010|1|1.404|70.00|2020-06-1811:10:5711|1|0.554|70.00|2020-06-1811:18:1212|1|0.431|70.00|2020-06-1811:27:3913|1|0.446|70.00|2020-06-1811:42:0814|1|0.510|70.00|2020-06-1811:56:4315 166rowsinset(6.54結(jié)果顯示，有6記錄符合條件。這個(gè)簡(jiǎn)單的查詢(xún)一共花去了6.54，這個(gè)速度顯然太慢123456789mysql>INSELECTty,price,transdate--分析查詢(xún)執(zhí)行->FROM->WHERE--通過(guò)商品編號(hào)篩->ANDtransdate>'2020-06-1809:00:00'--通->ANDtransdate<'2020-06-18++++++++|id|select_type|table|partitions|type|possible_keys|key++++++++|1|SIMPLE|trans|NULL|ALL|NULL|NULL|NULL|NULL|++++++++1rowinset,1warning(0.00這個(gè)結(jié)果集中包含了很多個(gè)字段，理解這些字段的意思，是我們優(yōu)化查詢(xún)的關(guān)id：是一個(gè)查詢(xún)序列號(hào)table：表示與查詢(xún)結(jié)果相關(guān)的表的名partition：表示查詢(xún)的分區(qū)key：表示優(yōu)化器最終決定使用的索引是什rows：表示為了得到查詢(xún)結(jié)果，必須掃描多少行記filtered：表示查詢(xún)篩選出的記錄占全部表記錄數(shù)的百分possible_key：表示MySQL可以通過(guò)哪些索引找到查詢(xún)的結(jié)果記錄。如果這里的值是空，就說(shuō)明沒(méi)有合適的索引可用。你可以通過(guò)查看WHERE條件語(yǔ)句中使用的字段，來(lái)Extra：表示MySQL查詢(xún)?cè)敿?xì)信息type：表示表是如何連接的。至于具體的內(nèi)容，你可以參考下除了剛剛這些字段，還有1比較重要，那就是select_type。它表示查詢(xún)的類(lèi)型，主要有4種取值。 代123456789mysql>SELECT->FROMdemo.goodsmaster->WHEREitemnumber代123456789mysql>SELECT->FROMdemo.goodsmaster->WHEREitemnumber->->->FROMdemo.goodsmaster->WHEREb.goodsname='書(shū)->->->FROMdemo.goodsmaster->WHEREc.goodsname='筆->+++++++|itemnumber|barcode|goodsname|specification|unit|salesprice|+++++++|1|0001|書(shū)|16開(kāi)|本|89.00|31.00|2|0002|筆|NULL|包|5.00|2.87+++++++19192rowsinset(0.00對(duì)這個(gè)語(yǔ)句的執(zhí)行進(jìn)行分析，得到如下的123456789mysql>INSELECT->FROMdemo.goodsmaster->WHEREa.itemnumber->->->FROMdemo.goodsmaster->WHEREb.goodsname='書(shū)->->->FROMdemo.goodsmaster->WHEREc.goodsname='筆->+++++++|id|select_type|table|partitions|type|possible_keys|key|+++++++|1|PRIMARY|a|NULL|ALL|NULL|NULL|NULL|NULL|2|100.00||2|DEPENDENTSUBQUERY|b|NULL|eq_ref|PRIMARY|PRIMARY|4|func|3|DEPENDENTUNION|c|NULL|eq_ref|PRIMARY|PRIMARY|4|func|1|NULL|UNIONRESULT|<union2,3>|NULL|ALL|NULL|NULL|NULL|NULL+++++++4rowsinset,1warning(0.00MySQL在執(zhí)行的時(shí)候，會(huì)把這個(gè)語(yǔ)句進(jìn)行優(yōu)化，重新寫(xiě)成下面的語(yǔ)SELECTFROMdemo.goodsmasterWHERE(SELECTFROMdemo.goodsmasterWHEREb.goodsname='書(shū)'SELECTFROMdemo.goodsmasterWHEREc.goodsname='筆'AND在這里，子查詢(xún)中的聯(lián)合查詢(xún)SELECTFROMdemo.goodsmasterWHEREc.goodsname='筆'AND這個(gè)查詢(xún)就用到了與外部查詢(xún)相關(guān)的條件a.itemnumber=c.itemnumber，因此，查詢(xún)類(lèi)別就變成了“UNIONDEPENDENT”。我們發(fā)現(xiàn)，這個(gè)查詢(xún)是一個(gè)簡(jiǎn)單查詢(xún)，涉及的表是emo.rans，沒(méi)有分區(qū)，連接類(lèi)型是掃描全表，沒(méi)有索引，一共要掃描的記錄數(shù)是 。因此，查詢(xún)速度慢的主要原因是沒(méi)有索引，導(dǎo)致必須要對(duì)全表進(jìn)行掃描才能完成查詢(xún)。所以，針對(duì)這個(gè)問(wèn)題，可以通過(guò)創(chuàng)建索引的辦法，來(lái)提高查詢(xún)的速度。下面，我們用條件語(yǔ)句中的篩選字段itemnumber和transdate分別創(chuàng)建索引代代1234567mysql>CREATEINDEXitemnumber_transONQueryOK,0rowsaffected(59.86Records:Duplicates:Warnings:mysql>CREATEINDEXtransdate_transONQueryOK,0rowsaffected(56.75Records:Duplicates:Warnings:然后我們?cè)俅芜\(yùn)行剛才的查詢(xún)，看看優(yōu)化有沒(méi)有起作123456mysql>SELECT->FROM->WHERE->ANDtransdate>'2020-06-18->ANDtransdate<'2020-06-18+++++9 1| |70.00|2020-06-1811:04:0010 1| |70.00|2020-06-1811:10:5711 1| |70.00|2020-06-1811:18:1212 1| |70.00|2020-06-1811:27:3913 1| |70.00|2020-06-1811:42:0814 1| |70.00|2020-06-1811:56:4315 166rowsinset(0.09結(jié)果顯示，查詢(xún)只運(yùn)行了0.09，跟之前的6.54相比，快了很多。這說(shuō)明我們的優(yōu)化mysql> INSELECT ->FROMWHERE --->ANDtransdate>'2020-06-1809:00:00'--按照時(shí)間篩->ANDtransdate<'2020-06-186 7|id|select_type|table|partitions|type|8 9|1| |trans| |range|10 111rowinset,1warning(0.01結(jié)果顯示，這一次的查詢(xún)執(zhí)行計(jì)劃有了改變。系統(tǒng)發(fā)現(xiàn)，有2個(gè)索引itemnumber_trans和transdate_trans可以使用，并且最終選擇了使用時(shí)間字段創(chuàng)建的索引transdate_trans行查詢(xún)。掃描的方式也不再是全表掃描，而是改為了區(qū)域掃描，實(shí)際的掃描記錄數(shù)也減少到了552個(gè)。這樣一來(lái)，查詢(xún)更加精準(zhǔn)，查詢(xún)的速度自然也就大幅提至此，我給你介紹了查詢(xún)分析語(yǔ)句，并且演示了如何通過(guò)使用查詢(xún)分析語(yǔ)句對(duì)慢查詢(xún)的執(zhí)行計(jì)劃進(jìn)行分析，并且利用分析的結(jié)果對(duì)慢查詢(xún)進(jìn)行優(yōu)化。接下來(lái)，我再給你介紹2化查詢(xún)的方法。優(yōu)化查詢(xún)最有效的方法就是創(chuàng)建索引。關(guān)于如何創(chuàng)建索引，我已經(jīng) 過(guò)了，這里就不多說(shuō)了。下面我來(lái)講講怎么在包含關(guān)鍵字“LIKE”和“OR”的條件語(yǔ)句中，利用索引提高查詢(xún)效率。使用關(guān)鍵字“LIKE”經(jīng)常被用在查詢(xún)的限定條件中，通過(guò)通配符“%”來(lái)篩選符合條件的記錄。比WHERE字段LIKE‘%aa’，表示篩選出所有以字段以“aa”結(jié)尾的記錄WHERE字段LIKE‘a(chǎn)a%’，表示篩選出所有以“aa”開(kāi)始的記錄WHERE字段LIKE‘%aa%’，表示所有字段中包含“aa”的記錄LIKE‘%aa’和WHERE字段LIKE‘%aa%’都不能使用索引，但是通配符在后面的篩選條代代mysql>CREATEINDEXtrans_barcodeONQueryOK,0rowsaffected(1min20.78Records:0Duplicates:0Warnings:我們來(lái)看看如果把通配符放面，能不能用到索引12345678mysql>INSELECT*FROM->WHEREbarcodeLIKE++++++++|id|select_type|table|partitions|type|possible_keys|key++++++++|1|SIMPLE|trans|NULL|ALL|NULL|NULL|NULL|NULL|++++++++1rowinset,1warning(0.00查詢(xún)分析的結(jié)果顯示，type的值是“ALL”，key是空，表示需要進(jìn)行全表掃描，沒(méi)有索引可用，rows的值是 再看看通配符在后面的情況12345678mysql>INSELECT*FROM->WHEREbarcodeLIKE+++++++|id|select_type|table|partitions|type|possible_keys|key|+++++++|1|SIMPLE|trans|NULL|range|trans_barcode|trans_barcode|803|+++++++1rowinset,1warning(0.00type值是“range”，意思是使用索引檢索一個(gè)給定范圍的記錄。rows值是563，也就是只需要掃描563條記錄就行了，這樣效率就高多了。使用關(guān)鍵字關(guān)鍵字“OR”表示“或”的關(guān)系，“WHERE達(dá)式1OR達(dá)式2”，就表示表達(dá)式或者表達(dá)式2只要有一個(gè)成立WHERE件就是成立的需要注意的是，只有當(dāng)條件語(yǔ)句中只有關(guān)鍵字“OR”，并且“OR”前后的表達(dá)式中的字段都建有索引的時(shí)候，查詢(xún)才能用到索引。號(hào)“itemnumber”創(chuàng)建一個(gè)索引mysql>CREATEINDEXtrans_itemnumberONQueryOK,0rowsaffected(20.24Records:0Duplicates:0Warnings:我們先看一下關(guān)鍵字“OR”前后的表達(dá)式中的字段都創(chuàng)建了索引的情123456789mysql>INSELECT*FROM->WHEREbarcodeLIKE->ORitemnumber=++++++|id|select_type|table|partitions|type|possible_keys|key|++++++|1|SIMPLE|trans|NULL|index_merge|trans_barcode,trans_itemnumber|++++++1rowinset,1warning(0.01查詢(xún)分析結(jié)果顯示，有2個(gè)索引可以使用是“ransbarode”和“trasiemumer”。ey=ine_merge，就說(shuō)明優(yōu)化器選擇了合并索引的方式。因此，這個(gè)關(guān)鍵字“OR”前后的表達(dá)式中的字段都創(chuàng)建了索引的查詢(xún)，是可以用到索引的。在下面的例子中，表達(dá)式goodsnameLIKE海鮮菇%'中的字段goodsname有創(chuàng)建123456789mysql>INSELECT*FROM->WHEREbarcodeLIKE->ORgoodsnameLIKE'%海鮮菇++++++++|id|select_type|table|partitions|type|possible_keys|key++++++++|1|SIMPLE|trans|NULL|ALL|trans_barcode|NULL|NULL|NULL|++++++++1rowinset,1warning(0.00查詢(xún)分析的結(jié)果顯示，type=ALL，是全表掃描，不能用索引11{ IN|DESCRIBE|DESC}查詢(xún)語(yǔ)句代特別需要提醒你注意的是，在使用“LIKE”關(guān)鍵字的條件語(yǔ)句中，通配符“%”面的篩選條件不能使用索引，通配符“%”在后面的篩選條件可以使用索引。在使用“OR”關(guān)鍵字的條件語(yǔ)句中，只有關(guān)鍵字“OR”前后的表達(dá)式中的字段都創(chuàng)建了索引，條件語(yǔ)句才能使用索引。關(guān)于優(yōu)化查詢(xún)，還有一個(gè)值得關(guān)注的點(diǎn)，就是子查詢(xún)。這是MySL的一項(xiàng)重要的功能，可以幫助我們通過(guò)一個(gè)SL語(yǔ)句實(shí)現(xiàn)比較復(fù)雜的查詢(xún)。但是，子查詢(xún)的執(zhí)行效率不高為MySQL會(huì)用臨時(shí)表把子查詢(xún)的結(jié)果保存起來(lái)，然后再使用臨時(shí)表