補12.關於mysql的索引回顧

標籤：mysql   索引   index   

一、mysql中的索引是什嗎？

索引在mysql中也叫做“鍵”，是mysql儲存引擎用於快速找到記錄的一種標記，索引對於提高查詢速度起到了非常關鍵的作用，當表中的資料量足夠大時，索引的作用愈發重要。

建立和維護索引，需要耗費很大的硬碟空間以及時間，但是可以大大的提高查詢速度。

二、在使用索引之前，你需要注意的。

1. 儘可能的去使用一些簡單的資料類型，整形的資料類型比起字串，處理開銷會小很多，因為字串的比較更複雜。在MySQL中，應該用內建的日期和時間資料類型，而不是用字串來儲存時間；以及用整數資料型別儲存IP地址。

2. 盡量避免空值（NULL）推薦給每個欄位都指定為not null（除非你想儲存NULL）在MySQL中，含有空值的列很難進行查詢最佳化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值。

3. 不要過度使用索引。

4. 查詢語句後面的where後面比較頻繁的條件，比較適合用索引。

5. 值過於集中的欄位，不推薦添加索引，即使加了也不會提高多少效率（比如性別：男/女）

  6. 切記不要在索引欄位進行運算，例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′。這一個單引號可能會造成mysql近百倍的效能損失。

三、mysql中常用的索引類型。

1. 普通索引：最基本的索引類型，沒有任何的限制，僅僅加快查詢速度。（比較常用）

    MyIASM中預設的BTREE類型的索引，也是我們大多數情況下用到的索引。

    1.1直接建立一個索引：

    CREATE INDEX 索引名稱 ON 表名（欄位名）

   1.2 在表結構上直接建立索引：

    alter table 表名 add index/unique/fulltext[索引名]（欄位名）

   1.3  建立主鍵索引：

    alter table 表名 add primary  key （欄位名）

   1.4 刪除一個索引：

    刪除一個非主鍵索引：

    alter table 表名 drop index 索引名；

    刪除主鍵索引：

    alter table 表名 drop primary key;

2.唯一索引（unique）：欄位內的值不可以重複。

與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是複合式索引，則列值的組合必須唯一，建立方法和普通索引類似。

 3. 主鍵索引：欄位內的值不能重複，雖然主鍵索引的欄位不能有重複，但是唯一索引不一定是主鍵。

在一張表上只能有一個主鍵，但是可以有多個唯一索引。

 4. 全文索引（full text）：

FULLTEXT索引僅可用於 MyISAM 表；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被建立，或是隨後使用ALTER TABLE 或CREATE INDEX被添加。

將你的資料輸入一個沒有FULLTEXT索引的表中，然後建立索引，其速度比把資料輸入現有FULLTEXT索引的速度更為快。不過切記對於大容量的資料表，產生全文索引是一個非常消耗時間非常消耗硬碟空間的做法。

–建立表的適合添加全文索引

CREATE TABLE `table` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

`time` int(10) NULL DEFAULT NULL ,

PRIMARY KEY (`id`),

FULLTEXT (content)

);

–修改表結構添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

–直接建立索引

CREATE FULLTEXT INDEX index_content ON article(content)

5.多列索引：

多個單列索引與單個多列索引的查詢效果不同，因為執行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。（個人認為，多列索引沒有單列索引查詢效率高）。

本文出自 “reBiRTH” 部落格，請務必保留此出處http://suhaozhi.blog.51cto.com/7272298/1933258

補12.關於mysql的索引回顧