MySQL索引（索引失效）

標籤：desc   fulltext   mem   多個   語句   bsp   dex   drop   esc   

索引

索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄。

 

myisam儲存引擎，資料檔案、索引檔案、表結構檔案分開儲存

innodb儲存引擎，資料和索引儲存在一個檔案中

B+tree索引

hash索引

 

hash索引（只有memory儲存引擎支援），尋找一條記錄的速度非常快

B+tree索引（Innodb和MyISAM支援），更適合排序等操作

 

適合作索引的資料類型

（1）較小的資料類型，需要更少的空間

（2）簡單的資料類型，整型比字串開銷小

（3）避免使用null，null會導致索引失效

 

主鍵索引

唯一索引

普通索引

全文索引

複合式索引

 

顯示當前表的所有索引資訊

show index from tableName\G

 

1.普通索引

create index idx_name on tableName（colName(length)）

如果是char或者varchar length可以小於實際的長度，如果是blob和text 必須指定length

alter table tableName add index idx_name(colName)

drop index idx_name on tableName

一張表可以有多個普通索引

2. 唯一索引

列的值必須唯一，允許用null值

如果是複合式索引，列的值的組合必須唯一

create union index idx_name on tableName(colName)

alter table table_name add unique idx_name(colName)

一張表可以有多個唯一索引

3.主鍵索引

主鍵的資料類型最好為整型

主鍵索引只能有一個

主鍵列的值必須唯一併且不能是null

自動成長的列一定是主鍵

主鍵列不一定是自動成長的

alter table tableName add primary key(col_name)

4.全文索引

innodb不支援

alter table tableName add fulltext idx_name(col_name)

 

 

 

索引失效

1.最佳左首碼原則

複合式索引，不按索引定義時制定的順序 的最左列開始

2.like ‘%Z’ 

like模糊查詢時， 以%開頭，導致索引失效

3.範圍之後全失效

4.遇到null值，索引失效

5.索引列上的顯式或者隱式運算，導致索引失效

6.order by

由於查詢只使用一個索引，因此，如果where語句使用了索引，order by語句不會使用

7.不在一個方向的order by導致索引失效

全asc 或 全desc

8.複合式索引，中間跳過了某一列，後面的列全失效

 

MySQL索引（索引失效）