mysql索引的設計和使用詳解

十 . 索引的設計和使用

10.1 索引概述

MyISAM 和InnoDB創建的索引默認都是BTREE索引

MEMARY的默認索引是HASH 但是也支持BTREE

創建索引:

語法

CREATE [UNIQUE|FULLTEXT|SPATIAL(空間索引) ] INDEX index_name

[USING index_type]

ON tablename(index_col_name,…..)

解釋 index_col_name: col_name[(length)] [ASC|DESC]

也可以適應 ALTER TABLE 語法來修改,和create 語法類似

范例

例如,要為 city 表創建瞭 10 個字節的前綴索引,語法是:

mysql> create index cityname on city (city(10));

Query OK, 600 rows affected (0.26 sec)

Records: 600 Duplicates: 0 Warnings: 0

刪除索引

語法

DROP INDEX index_name ON table_name

范例

想要刪除 city 表上的索引 cityname,可以操作如下:

mysql> drop index cityname on city;

Query OK, 600 rows affected (0.23 sec)

Records: 600 Duplicates: 0 Warnings: 0

索引的設計原則

搜尋的索引列

換句話說,最適合索引的列是出現在 WHERE子句中的列,或連接子句中指定的列,而不是出現在 SELECT 關鍵字後的選擇列表中的列。

使用唯一索引

考慮某列中值的分佈,索引列的基數越大 效果越好。

例如,存放出生日期的列具有不同的值,很容易區分各行,而用來記錄性別的列隻有兩個值,則索引沒有什麼用處,不管搜尋那個值都有大約一半的結果

使用短索引:

如對字符串列進行索引時,應該指定一個字符串長度,隻要有可能就應該這樣做。

例如, 有個char(200)的列,如果在前10個或者前20個字符內,多數值是唯一的,那麼就不要對整個列記性索引,對前10個或者前20個字符進行索引能節省大量的索引空間,也可能會是查詢更快,較小的索引涉及的磁盤I/O較小,較短的值比較起來也更快。更重要的是較短的鍵值,索引高速緩存塊中能容納更多的鍵值,因此,MySQL也可以在內存中容納更多的值。這樣就增加瞭找到行而不用讀取索引中較多塊的可能性。

利用最左前綴:

在創建一個n列的索引時,實際是創建瞭MySQL可利用的n個索引,多列索引可以起幾個索引的作用,因為可利用索引中最左邊的列進行匹配,這樣的列集稱為最左前綴

不要過度索引:

占用過多的存儲空間;降低寫的操作性能;MySQL生成執行計劃時要考慮過多的索引,要花費時間,過多的索引也會是MySQL選擇不到最好的索引。

對於InnoDB表 ,記錄默認會按照一定的順序保存。如果明確主鍵,則按照主鍵,沒有主鍵但是有唯一索引,則按照唯一索引的順序保存;如果既沒有主鍵也沒有唯一索引那麼表會自動生成一個內部列。按照主鍵或者內部列的速度最快,所以要盡可能的指定主鍵:

選擇最長訪問的列作為主鍵,主鍵選盡可能短的數據類型。

HASH和BTREE

HASH一些重要的特征

1 隻用於使用=或<=>操作符的等式比較。

2優化器不能使用 HASH 索引來加速 ORDER BY 操作。

3MySQL 不能確定在兩個值之間大約有多少行。如果將一個 MyISAM 表改為 HASH 索引的 MEMORY 表,會影響一些查詢的執行效率。

4隻能使用整個關鍵字來搜尋一行。

BTREE:

使用 >、 < 、 >= 、<= 、 BETWEEN、 != 或者<> 或者LIKE 'abc' (abc 不以通配符開始) 都可以使用相關的列上的索引;

You May Also Like