資料庫調優教程（十） 【精華章節】解決like ’%str’ 時索引不被使用的4種方法，調優%str

資料庫調優教程（十） 【精華章節】解決like ’%str’ 時索引不被使用的4種方法，調優%str
三、           索引

7.      解決like’%str’不使用模糊查詢的4種方法

上一講最後說了，只要模糊查詢的模糊值在字串前面，則不會使用索引，‘%aaa’和‘_aaa’都不會！

如下

應該說這是Mysql給程式員們開的一個玩笑。要是我的表資料量很大，而且又需要使用like’%%’這樣的模糊查詢來檢索時，該怎麼辦？？

接下來，筆者將會給大家分享解決這個問題的四種方法！

1)    Select主鍵

只要Select的欄位剛好是主鍵，那麼就會使用到索引(只對innodb資料庫有效)

比如下面的

select idfrom emp where ename like '%haha%'\G

就使用了索引

select * from empwhere ename like '%haha%'\G

則不使用索引

除了主鍵，其他欄位必須設定為覆蓋索引才能使索引生效，不能單獨設定索引

比如下面這種是不會使用索引的

可以採用分步查詢的方法，先select主鍵再利用主鍵去找其他欄位。不過好像比較麻煩！別怕！接下來會講一種最優方法——覆蓋索引法！

2)    覆蓋索引法

覆蓋索引是一種特殊的多列索引，當多列索引指向一個查詢語句中所有的欄位時，該多列索引就被稱為覆蓋索引。

使用覆蓋索引可以解決問題！

建立覆蓋索引

當然，如果你想要select很多欄位甚至是select*，那你可以建立一個多列索引指向所有欄位（innodb可以不指向主鍵）

注意：

筆者發現，當覆蓋索引指向的欄位是varchar(380)及380以上的欄位時，覆蓋索引會失效！

 

3)    全文索引法

此方法有較大局限。

全文索引，只對MyISAM引擎有用。主要是針對對檔案，文本的檢索, 比如文章或者段落,.

它會把某個資料表的某個資料列出現過的所有單詞產生一份清單

少於3個字元的單詞不會被包含在全文索引裡，可以通過修改my.cnf修改選項

ft_min_word_len=3

但是！

全文索引不完全等同於模糊查詢

比如title欄位有這麼個資料’abcd20088ccaa’,使用模糊查詢select * from articles wheretitle like’%2008%’可以尋找到，而使用全文檢索索引select * from articles where match(title) against(‘2008’);是檢索不到的，因為2008不是一個單詞！

 

4)    使用全文檢索索引引擎工具包

採用lucene、Sphinx、solr等專門的全文檢索索引開源工具可以檢索某段字串。

本章結束，下一章將迴歸資料庫的開發源頭——設計好一張表。