Skip to main content

UsingIndex

索引好比書的目錄​

  • 如果沒有建立索引,會從第一筆資料開始搜尋到最後一筆,執行效率會很差
  • 使用Where,Order By, Group by的欄位可以考慮建立索引,搜尋可以加速
  • 如果該欄位值得變化不大(0/1)可以不建立索引,因為效過不大
  • 索引的欄位盡量不要有null的內容
  • Where後有Or就必須為每個Or建立索引,否則無效
  • 如果將索引欄位放在函式內就會失去索引效果
  • 建立索引會佔空間,所以不要建立過多索引

列出索引​

################# 語法如下:
SHOW INDEX FROM tablename;
EXPLAIN select語句;     #評估有無所引的效能差別

################# 範例如下:
SHOW INDEX from book;

EXPLAIN select * from book where isbn='1233123123'; #有索引的效率
EXPLAIN select * from book where bookname='java';   #無索引的效率

主索引​

  • 建立主鍵時就會自動建立主索引
  • 該欄位不可重複不可為NULL
################# 語法如下:
ALTER TABLE tablename ADD PRIMARY KEY (columnname);
SHOW INDEX from tablename;
DROP INDEX indexname ON tablename;

################# 範例如下:
ALTER TABLE book ADD PRIMARY KEY (isbn);
SHOW INDEX FROM book;
DROP INDEX ID ON book;

唯一索引​

  • 很類似主索引,唯一不同是值可為NULL
################# 語法如下:
CREATE INDEX indexname ON tablename(columnname);
ALTER TABLE tablename ADD UNIQUE indexname(columnname);

################# 範例如下:
CREATE UNIQUE uniq_bname ON book(bookname);
ALTER TABLE book ADD UNIQUE uniq_bname(bookname);

非唯一索引​

  • 值可以重複也可為NULL
  • 用在查詢條件欄位上,可以加速查詢速度
################# 語法如下:
CREATE INDEX indexname ON tablename(columnname);
ALTER TABLE tablename ADD INDEX indexname(columnname);

################# 範例如下:
CREATE INDEX idx_bname ON book(bookname);
ALTER TABLE book ADD INDEX idx_bname(bookname);

複合索引​

  • 將多個欄位一起加入索引,可以套用在任何類型索引上
  • 查詢條件可能有多個欄位,如果都建立單一索引,mySQL就會使用他認為最佳的索引來搜尋資料,如果條件欄位有多個且索引有使用上的優先序,就可以建立複合索引,並且將優先序高的索引欄位列在左
################# 語法如下:
CREATE INDEX indexname ON tablename(columnlist);
ALTER TABLE tablename ADD INDEX indexname(columnlist);

################# 範例如下:
CREATE INDEX idx_bname_author ON book(bookname, author);
ALTER TABLE book ADD INDEX idx_bname_author(bookname,author);