索引相關(guān)

類(lèi)型隱式轉(zhuǎn)換 大坑

**字段filed1是varchar類(lèi)型，且加了索引，如果 where filed1 = 123; type 可能是all，因?yàn)?23是數(shù)字類(lèi)型，mysql內(nèi)部會(huì)用函數(shù)做隱式轉(zhuǎn)換，用了函數(shù)，索引就失效了。**

大數(shù)據(jù)深度分頁(yè)，用主鍵

select field1,field2 from table limit 100000,10;
select field1,field2 from table where id > 100000 limit 10;

避免使用MySQL函數(shù)

MySQL內(nèi)置了很多函數(shù)，使用函數(shù)可能導(dǎo)致索引失效，盡量讓MySQL只做簡(jiǎn)單的增刪改查。

避免類(lèi)型的隱式轉(zhuǎn)換

varchar等字符串類(lèi)型的字段被加了索引，把這字段當(dāng)做where條件，及時(shí)目標(biāo)值是數(shù)字，也要加引號(hào)，否則類(lèi)型的隱式轉(zhuǎn)換，會(huì)引起索引失效的問(wèn)題。

避免使用函數(shù)或表達(dá)式，盡量只讓數(shù)據(jù)庫(kù)做純粹的增刪改查。

用函數(shù)的前提是知道初始值，然后在操作數(shù)據(jù)，很多函數(shù)都是要傳參的，所以mysql只能全表查，然后每次迭代將數(shù)據(jù)丟給函數(shù)處理。
表達(dá)式也是如此，例如where number + 1 = 10，都會(huì)讓索引失效。

避免使用不等值做排除法

避免使用<>、!=、not in、is not null、這些都會(huì)使索引失效。

避免使用null值

避免字段使用null值，一是影響索引（mysql建索引建的是非null的值，大量的null值影響了正常的B+tree結(jié)構(gòu)），而且造成聚合函數(shù)統(tǒng)計(jì)（如count(該字段)）不準(zhǔn)確的問(wèn)題。

索引無(wú)關(guān)

減少大字段查詢(xún)，避免使用*，不說(shuō)磁盤(pán)io的損耗，連網(wǎng)絡(luò)帶寬都跟著損耗。
如果只select僅需字段，可能會(huì)觸發(fā)覆蓋索引機(jī)制，不用回表，提高性能。

select * from table;
select field1,field2 from table;

查詢(xún)是否存在

select count(*) from table where...;
select field from table where ... limit 1;

冗余優(yōu)化

想要查詢(xún)一篇文章的瀏覽量，不用count(瀏覽記錄)。
新建一個(gè)在文章表中建立一個(gè)瀏覽量的字段，這使得查詢(xún)的時(shí)間復(fù)雜度從O(n)變成O(1)；

避免join，適用于大表關(guān)聯(lián)小表。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-837797.html

如果想要join的兩個(gè)表，一個(gè)很大，一個(gè)很小，應(yīng)盡量避免join。
可將小表數(shù)據(jù)全部取出來(lái)組裝成數(shù)組，放入編程語(yǔ)言的內(nèi)存，用編程語(yǔ)言的內(nèi)存匹配的方式去關(guān)聯(lián)。

哪些場(chǎng)景下索引會(huì)失效

• 使用not in、is not null、<>、!=、這種排除法時(shí)會(huì)導(dǎo)致索引失效，覆蓋索引除外。
• 最左匹配原則，左邊的字段缺少時(shí)會(huì)出現(xiàn)，覆蓋索引除外。
• 最左匹配原則，左邊的字段有區(qū)間查詢(xún)，導(dǎo)致右邊的字段無(wú)法使用索引。
• like左邊或兩邊加百分號(hào)。
• 類(lèi)型的隱式轉(zhuǎn)換，如varchar的字段，使用where varchar_field = 123，包括join表，用on連接的字段。
• where條件有函數(shù)，或表達(dá)式。
• where語(yǔ)句包含or，or中存在非索引列。
• 大數(shù)據(jù)量對(duì)二級(jí)索引字段排序，如果select * 或者其它字段，這個(gè)過(guò)程涉及回表，可能無(wú)法使用索引，因?yàn)閿?shù)據(jù)量大，走索引的每條數(shù)據(jù)都需要回表，代價(jià)會(huì)很大。
• order by字段，如果排序與索引順序不一致，則可能導(dǎo)致索引失效，如果order by的每個(gè)字段，都按照索引的順序，或者反順序，則仍舊會(huì)走索引。

那些查詢(xún)適合創(chuàng)建索引？

• 需要唯一性約束兜底的字段。
• 經(jīng)常被查詢(xún)或者作為where條件的字段，=、>、<、<=、>=、in、between、like 右百分號(hào)。
• 經(jīng)常group by或者order by的字段。
• delete或update被作為where條件的字段。
• distinct的字段。
• join on的連接字段需要加索引，但是需要類(lèi)型一致，因?yàn)镸ySQL內(nèi)部有用函數(shù)做隱式轉(zhuǎn)換，用了函數(shù)就不適用索引。
• 區(qū)分度（不重復(fù)度）高的字段。
• 把搜索最頻繁的列，放在聯(lián)合索引的左側(cè)，（受聯(lián)合索引的最左原則影響）。

那些查詢(xún)不適合創(chuàng)建索引？

• 數(shù)據(jù)量小，一個(gè)表，例如配置表，總類(lèi)別表，可能最多幾十條記錄，創(chuàng)建不創(chuàng)建區(qū)別不大。
• 寫(xiě)多讀少，數(shù)據(jù)的寫(xiě)操作對(duì)索引字段的開(kāi)銷(xiāo)比沒(méi)有索引要大，而且讀操作還少。
• 區(qū)分度低的字段，例如性別狀態(tài)等，這會(huì)導(dǎo)致線(xiàn)性查找，能提升搜索效率，但是不明顯，可加可不加。
• sql語(yǔ)句包含<>、!=、not in、is not null，無(wú)法使用索引，所以專(zhuān)門(mén)用作排除性查找的，不建議創(chuàng)建索引。

到了這里，關(guān)于MySQL查詢(xún)優(yōu)化方案匯總（索引相關(guān)）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！