關(guān)于索引使用與優(yōu)化時(shí)需要了解的知識(shí)
無(wú)法使用索引的語(yǔ)句
-
查詢(xún)的是語(yǔ)句中索引使用的邊界不明確時(shí)
A测蘑、B兩個(gè)字段組成一個(gè)索引馋嗜，這時(shí)SELECT * fROM T WHERE B = 'xxx',那么這個(gè)索引將不會(huì)生效狰住，但是如果是SELECT B fROM T WHERE B = 'xxx'厢蒜，這個(gè)時(shí)候就會(huì)使用索引查詢(xún)理疙。
-
當(dāng)索引的查詢(xún)字段中存在null值時(shí)
A字段上建立索引晕城，SELECT COUNT(A) FROM T,這個(gè)時(shí)候因?yàn)锳字段中存在空值，那么不可能使用索引查詢(xún)沪斟，除非在包含子查詢(xún)語(yǔ)句對(duì)A數(shù)值上做限制广辰，SELECT COUNT(A) FROM T WHERE A NOT NULL,有或者在設(shè)計(jì)表時(shí)，將A設(shè)置成not null屬性也可以主之。
-
當(dāng)語(yǔ)句中索引字段使用函數(shù)數(shù)據(jù)時(shí)
A字段上建立索引择吊，SELECT * FROM T WHERE FUN(A) = 'xxx',這個(gè)時(shí)候?qū)嶋H上不是使用字段A的數(shù)據(jù)，而是A字段計(jì)算之后的數(shù)據(jù)槽奕，如果希望這個(gè)語(yǔ)句使用索引查詢(xún)几睛，那么索引的建立應(yīng)該建立在函數(shù)上，CREATE INDEX IDX FUNT ON T(FUN(A));
-
當(dāng)有語(yǔ)句中有隱式轉(zhuǎn)換時(shí)
其實(shí)類(lèi)似于<u>第三點(diǎn)</u>,部分字段當(dāng)類(lèi)型是字符串粤攒，但是給出的條件是數(shù)字時(shí)所森，會(huì)被優(yōu)化器優(yōu)化，隱式的使用函數(shù)夯接，將字符串轉(zhuǎn)換成數(shù)字焕济，要么重新添加基于函數(shù)的索引，要么就是修改sql語(yǔ)句中子查詢(xún)語(yǔ)句中比較參數(shù)的類(lèi)型盔几，讓查詢(xún)字段數(shù)據(jù)類(lèi)型與比較參數(shù)數(shù)據(jù)類(lèi)型一致晴弃。
-
數(shù)據(jù)量太小時(shí)
當(dāng)一次表中的數(shù)據(jù)太小，以至于最小的數(shù)據(jù)塊即可包含大部分?jǐn)?shù)據(jù)時(shí)，將不會(huì)使用索引查詢(xún)上鞠。
!=或者<>(不等于)
not in和not exist
-
通配符傳詢(xún)字符串际邻，通配符在第一位時(shí)
因?yàn)槭孜蝗我馄ヅ洌灾荒苋聿樵?xún)
-
表字段的屬性設(shè)置不當(dāng)導(dǎo)致聚簇因子過(guò)大時(shí)
因?yàn)樽址蛿?shù)字類(lèi)型在數(shù)據(jù)庫(kù)中存儲(chǔ)的方式是不一樣的芍阎，所以計(jì)算索引的方式有區(qū)別世曾，當(dāng)索引字段設(shè)置不當(dāng)，導(dǎo)致聚簇因子谴咸，數(shù)據(jù)庫(kù)會(huì)放棄使用索引而直接全表查詢(xún)轮听。
監(jiān)控索引的使用情況
監(jiān)控Oracle索引使用情況
-
開(kāi)啟指定索引使用監(jiān)控
ALTER INDEX <index_name> MONITORING USAGE; -
創(chuàng)建索引
CREATE INDEX <indexName> ON <tableName>(<columnName>); -
刪除索引
DROP INDEX <indexName> ON <tableName>; -
查詢(xún)相關(guān)表指定表中的索引使用情況
SELECT * FROM V$OBJECT_USAGE WHERE TABLE_NAME = '<TABLE_NAME>';
oracle可以直觀(guān)的看到表中的索引是否使用過(guò)，網(wǎng)上也有一些腳本寿冕，可以統(tǒng)計(jì)一段時(shí)間內(nèi)沒(méi)有使用的索引蕊程，相對(duì)于mysql來(lái)講比較容易優(yōu)化索引的使用情況。
監(jiān)控mysql索引使用情況
-
創(chuàng)建索引
CREATE INDEX <indexName> ON <tableName>(<columnName>(length)); -
添加索引
ALTER TABLE <tableName> ADD INDEX <indexName>(<columnName>) -
刪除索引
DROP INDEX <indexName> ON <tableName>; -
查詢(xún)索引使用情況
show status like 'Handler_read%';
mysql中沒(méi)有類(lèi)似Oracle的針對(duì)單個(gè)索引監(jiān)控的功能驼唱，只能粗放性的查詢(xún)到全局中索引使用的結(jié)果統(tǒng)計(jì)
參考資料：
sql中索引不會(huì)被用到的幾種情況
如何監(jiān)控ORACLE索引使用與否
mysql使用率監(jiān)控
MySQL 關(guān)于索引以及使用效率對(duì)比藻茂，附測(cè)試數(shù)據(jù)代碼
Oracle 建立索引及利用索引的SQL語(yǔ)句優(yōu)化
