mysql 普通索引 唯一索引的建立與效率比較

unique(唯一索引)

alter table `table_name` add unique (`column`)

index(普通索引)
mysql教程>alter table `table_name` add index index_name ( `column` )

fulltext(全文索引)
alter table `table_name` add fulltext ( `column` )

 

1、普通索引

　　普通索引（由關鍵字key或index定義的索引）的唯一任務是加快對資料的訪問速度。因此，應該只為那些最經常出現在查詢條件（wherecolumn=）或排序條件（orderbycolumn）中的資料列建立索引。只要有可能，就應該選擇一個資料最整齊、最緊湊的資料列（如一個整數類型的資料列）來建立索引。

一個文章庫，裡面有兩個表：category和article。category裡面有10條分類資料。article裡面有20萬條。article裡面有一個"article_category"欄位是與category裡的"category_id"欄位相對應的。article表裡面已經把 article_category字義為了索引。資料庫教程大小為1.3g。

問題描述：

執行一個很普通的查詢： select * from `article` where article_category=11 order by article_id desc limit 5 。執行時間大約要5秒左右

解決方案：

建一個索引：create index idx_u on article (article_category,article_id);

select * from `article` where article_category=11 order by article_id desc limit 5 減少到0.0027秒

2、唯一索引

　　普通索引允許被索引的資料列包含重複的值。比如說，因為人有可能同名，所以同一個姓名在同一個“員工設定檔”資料表裡可能出現兩次或更多次。

如果能確定某個資料列將只包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字unique把它定義為一個唯一索引。這麼做的好處：一是簡化了mysql對這個索引的管理工作，這個索引也因此而變得更有效率；二是mysql會在有新記錄插入資料表時，自動檢查新記錄的這個欄位的值是否已經在某個記錄的這個欄位裡出現過了；如果是，mysql將拒絕插入那條新記錄。也就是說，唯一索引可以保證資料記錄的唯一性。事實上，在許多場合，人們建立唯一索引的目的往往不是為了提高訪問速度，而只是為了避免資料出現重複。

create table persons
(
id_p int not null,
lastname varchar(255) not null,
firstname varchar(255),
address varchar(255),
city varchar(255),
constraint uc_personid unique (id_p,lastname)
)

如需撤銷 unique 約束，請使用下面的 sql：

mysql:
alter table persons
drop index uc_personid

 

唯一索引。
它與前面的"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是複合式索引，則列值的組合必須唯一。它有以下幾種建立方式：
（1）建立索引：create unique index indexname on tablename(tablecolumns(length))
（2）修改表結構：alter tablename add unique [indexname] on (tablecolumns(length))
（3）建立表的時候直接指定：create table tablename ( [...], unique [indexname] (tablecolumns(length));