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));