MySQL 索引 總結

標籤：

1.索引的種類（六種）

　　普通索引，唯一索引，全文索引，單列索引，多列索引，空間索引

2.優缺點及注意事項

優點：有了索引，對於記錄數量很多的表，可以提高查詢速度。

缺點：索引是佔用空間的，索引會影響update insert delete速度。

以下列出幾點使用mysql索引的注意事項。

　　a、索引要建立在where和join用到的欄位上。

　　b、以下操作符號可以應用索引(：<，<=，=，>，>=，BETWEEN，IN，LIKE不以%_開頭)

  　　　以下需要注意不使用索引(<>, NOT IN, LIKE %_開頭)

　　　　<> 可以用 a>1 or a<3 代替,NOT IN 可以用NOT exists代替

　　c、在使用max() min()時最好加上索引。

　　d、單索引要建立在確實需要的地方。多列索引有最佳左首碼特性，所以盡量左邊的欄位是最常用的。

　　e、索引不會包括有NULL值，就是有NULL索引就失效。

　　f、使用短索引，一個欄位字太多，可以建立部分索引，只取前十個位元組約空間。

　　g、不要在列上運算。例如:where MD5("password") = "myz"。

　　h、explain select * from myz 來測試一下語句的效率...

3.儲存引擎和索引儲存類型

　　索引儲存類型包含兩種：B樹型索引和HASH型索引

　　MyISAM和InnoDB儲存引擎支援B樹型索引；MEMORY支援HASH型索引和B樹型索引，預設是前者。

4.索引的設計原則

　　a.選擇唯一性索引

　　b.為經常需要排序，分組和聯合操作的列建立索引

　　c.為常需要作為查詢條件的欄位建立索引

　　d.限制索引的數目

　　e.盡量使用資料量少的欄位作為索引

　　f.盡量使用首碼作為索引

　　g.刪除不常用的或者很少用的索引

5.建立，修改索引的SQL語句

　　

CREATE TABLE 表名(屬性名稱 資料類型 [完整性條件約束條件],                 屬性名稱 資料類型 [完整性條件約束條件],                 ......                 [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY[別名](屬性名稱 [(長度)] [ASC|DESC])                 );

 

 

執行個體

普通索引建立CREATE TABLE index1(id INT,name VARCHAR(20),sex BOOLEAN,INDEX(id));普通索引修改CREATE INDEX index1_id ON index1(id);或ALTER TABLE index1 ADD INDEX index1_id(id);

 

唯一性索引建立CREATE TABLE index2(id INT UNIQUE,name VARCHAR(20),UNIQUE INDEX index2_id(id ASC));唯一性索引修改CREATE UNIQUE index2_id ON index2(id);或ALTER TABLE index2 ADD UNIQUE INDEX index2_id(id);

全文索引建立CREATE TABLE index3(id INT,info VARCHAR(20),FULLTEXT INDEX index3_info(info))ENGINE=MyISAM;全文索引修改CREATE FULLTEXT INDEX index3_info ON index3(info);或ALTER TABLE index3 ADD FULLTEXT INDEX index3_info(info);

單列索引建立CREATE TABLE index4(id INT,subject VARCHAR(20),INDEX index4_st(subject(10)));單列索引修改CREATE INDEX index4_name ON index4(name);或ALTER TABLE index4 ADD INDEX index4_name(name);

 

多列索引建立CREATE TABLE index5(id INT,name VARCHAR(20),sex CHAR(4),INDEX index5_ns(name,sex));多列索引修改CREATE INDEX index5_ns ON index5(name,sex);或ALTER TABLE index5 ADD INDEX index_ns(name,sex);

空間索引建立CREATE TABLE index6(id INT,space GEOMETRY NOT NULL,SPATIAL INDEX index_sp(space))ENGINE=MyISAM;空間索引修改CREATE SPATIAL INDEX index6_sp ON index6(space);或ALTER TABLE index6 ADD SPATIAL INDEX index6_sp(space);

 

 

6.刪除索引

　　DROP INDEX 索引名 ON 表名;

 

 

 

 

 

 

 

 

 

 

　　

MySQL 索引 總結