資料查詢最佳化之mysql索引

標籤：order by   sel   xxx   text   覆蓋   har   方式   表數   需要   

為什麼要建立索引：

索引用於快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記錄開始然後讀完整個表直到找出相關的行，還需要考慮每次讀入資料頁的IO開銷。而如果採取索引，則可以根據索引指向的頁以及記錄在頁中的位置，迅速地讀取目標頁進而擷取目標記錄。

使用索引需要注意：

⑴只對WHERE和ORDER BY需要查詢的欄位設定索引，避免無意義的硬碟開銷；

⑵複合式索引支援首碼索引；

⑶更新表的時候，如增刪記錄，MySQL會自動更新索引，保持樹的平衡；因此更多的索引意味著更多的維護成本

 索引的欄位類型問題

• text類型，也可建索引（需指定長度）
• myisam儲存引擎索引鍵長度綜合不能超過1000位元組
• 用來篩選的值盡量保持和索引列同樣的資料類型

索引分四類：

index ----普通的索引,資料可以重複

fulltext----全文索引，用來對大表的文本域(char，varchar，text)進行索引。文法和普通索引一樣。 

unique ----唯一索引,唯一索引,要求所有記錄都唯一

primary key ----主鍵索引,也就是在唯一索引的基礎上相應的列必須為主鍵

like 不能用索引？

• 盡量減少like，但不是絕對不可用，”xxxx%” 是可以用到索引的，

想象一下，你在看一本成語詞典，目錄是按成語拼音順序建立，查詢需求是，你想找以 “一”字開頭的成語（”一%“），和你想找包含一字的成語（“%一%”）

• 除了like，以下操作符也可用到索引：

<，<=，=，>，>=，BETWEEN，IN

<>，not in ，！=則不行

原則

1. 單表資料太少，索引反而會影響速度；更新非常頻繁的資料不適宜建索引
2. where後的條件，order by ,group by 等這樣過濾時，後面的欄位最好加上索引。根據實際情況，選擇PRIMARY KEY、UNIQUE、INDEX等索引，但是不是越多越好，要適度
3. 聯集查詢，子查詢等多表操作時關連欄位要加索引

ps：資料量特別大的時候，最好不要用聯集查詢，即使你做了索引(??)

索引原則

1.索引越少越好
   原因：主要在修改資料時，第個索引都要進行更新，降低寫速度。
2.最窄的欄位放在鍵的左邊
3.避免file sort排序，暫存資料表和表掃描.哪些常見情況不能用索引?

• like “%xxx”
• not in ， ！=
• 對列進行函數運算的情況（如 where md5(password) = “xxxx”）
• WHERE index=1 OR A=10
• 存了數值的字串類型欄位（如手機號），查詢時記得不要丟掉值的引號，否則無法用到該欄位相關索引，反之則沒關係

覆蓋索引(Covering Indexes)擁有更高效率

索引包含了所需的全部值的話，就只select 他們，換言之，只select 需要用到的欄位，如無必要，可盡量避免select *

NULL 的問題

NULL會導致索引形同虛設，所以在設計表結構時應避免NULL 的存在（用其他方式表達你想表達的NULL，比如 -1？）

如何查看索引資訊，如何分析是否正確用到索引?

show index from tablename;
explain select ……;

資料查詢最佳化之mysql索引