1. MySQL有多中訪問表的行，其中最常用的是順序訪問和索引訪問。順序訪問就是：一行一行的浏覽一個表，即全表掃描。缺點:非常費時，效率很低。索引訪問是：只讀取那些表現了所需特性的行，其中包含了至少一個索引；與書上的目錄相似。
www.2cto.com
索引：就是一個列表，包含索引字段的值和相應的頁碼定位。
索引關鍵字進行了排序。查詢時可以通過索引，提供查詢速度.對於索引的每一項，MySQL在內部為它保存一個數據文件中實際記錄所在位置的指針。
索引的優點：提供查詢速度。
缺點：占磁盤空間 ; 會降低寫的速度。（插入，更新，刪除）
什麼時候鍵索引：如果表主要是提供查詢，那麼在經常查詢的字段上建立索引。
索引弊端：
1）首先，索引是以文件的形式存儲的，索引文件要占用磁盤空間。如果有大量的索引，索引文件可能會比數據文件更快地達到最大的文件尺寸。
2）其次，在更新表中索引列上的數據時，對索引也需要更新，這可能需要重新組織一個索引，如果表中的索引很多，這是很浪費時間的。也就是說，這樣就降低了添加、刪除、修改和其他寫入操作的效率。表中的索引越多，則更新表的時間就越長。
但是這些弊端並不妨礙索引的應用，因為索引帶來的好處已經基本掩蓋了它的缺陷，在表中有很多行數據的時候，索引通常是不可缺少的。
www.2cto.com
在數據庫中建立索引主要有以下好處:
1).快速讀取數據；
2).保證數據記錄的唯一性；
3).實現表與表之間的參照完整性；
4)在使用group by、order by字句進行數據檢索時，利用索引可以減少排序和分組的時間
2. 索引的分類
目前大部分MySQL索引都是以B-樹（BTREE）方式存儲的。btree方式構建為包含了多個節點的一棵樹。頂部的節點構成了索引的開始點，叫做根。每個節點中含有索引列的幾個值，節點中的每個值又都指向另一個節點或者指向表中的一行，一個節點中的值必須是有序排列的。指向一行的節點叫做葉子頁。葉子頁本身也是相互連接的，一個葉子頁有一個指針指向下一組。這樣，表中的每一行都會在索引中有一個對應值。查詢的時候就可以根據索引值直接找到所在的行。
索引中的節點是存儲在文件中的，所以索引也是要占用物理空間的，MySQL將一個表的索引都保存在同一個索引文件中。
如果更新表中的一個值或者向表中添加或刪除一行，MySQL會自動地更新索引，因此索引樹總是和表的內容保持一致。
索引是對象，刪除對象用drop，刪除記錄用delete。
3.按BTREE形式存儲的主要索引類型介紹如下：
1）. 普通索引（index）
這是最基本的索引類型，它沒有唯一性之類的限制。創建普通索引的關鍵字是INDEX。
www.2cto.com
create index 索引名 on 表名(字段名，。。。)；
例如：create indexindex_name on xs(姓名);
2）. 唯一性索引（unique）
這種索引和前面的普通索引基本相同，但有一個區別：索引列的所有值都只能出現一次，即必須是唯一的。創建唯一性索引的關鍵字是UNIQUE。
在一個表上可以創建多個，索引值不能重復，允許有空值
create unique index 索引名 on 表名(字段名，。。。)
3）. 主鍵（primary key）
主鍵是一種唯一性索引，它必須指定為“PRIMARY KEY”。主鍵一般在創建表的時候指定，也可以通過修改表的方式加入主鍵。但是每個表只能有一個主鍵。
在一個表上只能創建一個主鍵，主鍵字段不能出現重復值，不允許為空。
4）. 全文索引（fulltext）
MySQL支持全文檢索和全文索引。在MySQL中，全文索引的索引類型為FULLTEXT。全文索引只能在VARCHAR或TEXT類型的列上創建，並且只能在MyISAM表中創建。它可以通過CREATE TABLE命令創建，也可以通過ALTER TABLE或CREATE INDEX命令創建。對於大規模的數據集，通過ALTER TABLE（或CREATE INDEX）命令創建全文索引要比把記錄插入帶有全文索引的空表更快。
另外，當表類型為memory或heap時，除了BTREE索引，MySQL還支持哈希索引（HASH）。使用哈希索引，不需要建立樹結構，但是所有的值都保存在一個列表中，這個列表指向相關頁和行。當根據一個值獲取一個特定的行時，哈希索引非常快。
create fulltext index 索引名 on 表名(字段名，。。。)；
4. 使用create index語句
使用CREATE INDEX語句可以在一個已有表上創建索引，一個表可以創建多個索引。語法格式：
create [unique | fulltext |spatial] index index_name
[using index_type] www.2cto.com
on tbl_name (index_col_name,...)
其中，index_col_name格式為：col_name [(length)] [ASC | DESC]
說明：
● index_name：索引的名稱，索引在一個表中名稱必須是唯一的。
● USING index_type：部分存儲引擎允許在創建索引時指定索引的類型。index_type為存儲引擎支持的索引類型的名稱，MySQL支持的索引類型有BTREE和HASH。如果不指定USING子句，MySQL自動創建一個BTREE索引。
● index_col_name：col_name表示創建索引的列名。length表示使用列的前length個字符創建索引。使用列的一部分創建索引可以使索引文件大大減小，從而節省磁盤空間。在某些情況下，只能對列的前綴進行索引。例如，索引列的長度有一個最大上限，因此，如果索引列的長度超過了這個上限，那麼就可能需要利用前綴進行索引。BLOB或TEXT列必須用前綴索引。前綴最長為255字節，但對於MyISAM和InnoDB表，前綴最長為1000字節。
另外還可以規定索引按升序（ASC）還是降序（DESC）排列，默認為ASC。如果一條SELECT語句中的某列按照降序排列，那麼在該列上定義一個降序索引可以加快處理速度。
● UNIQUE | FULLTEXT | SPATIAL：UNIQUE表示創建的是唯一性索引；FULLTEXT表示創建全文索引；SPATIAL表示為空間索引，可以用來索引幾何數據類型的列。本書不討論SPATIAL索引。
可以看出，CREATEINDEX語句並不能創建主鍵。
例：根據XS表的學號列上的前5個字符建立一個升序索引XH_XS。
CREATE INDEX XH_XS ON XS(學號(5) ASC);
可以在一個索引的定義中包含多個列，中間用逗號隔開，但是它們要屬於同一個表。這樣的索引叫做復合索引。
例： 在XS_KC表的學號列和課程號列上建立一個復合索引XSKC_IN。
CREATE INDEX XSKC_IN ON XS_KC(學號,課程號);
5. 使用ALTER TABLE語句
前面介紹了如何使用ALTER TABLE語句修改表，其中也包括向表中添加索引。 語法格式如下：
ALTER [IGNORE] TABLEtbl_name
add index [索引名] [索引類型] (索引列,...) /*添加索引*/
| add [constraint [symbol]] primary key [索引類型] (索引列,...) /*添加主鍵*/ www.2cto.com
| add [CONSTRAINT [symbol]]UNIQUE [index_name][index_type] (index_col_name,...) /*添加唯一性索引*/
| add [FULLTEXT | SPATIAL] [索引名] (索引列,...) /*添加全文索引*/
| add [CONSTRAINT [symbol]] foreign key [索引名] (索引列,...)[reference_definition] /*添加外鍵*/
| disable keys
| enable keys
說明：
● index_type：語法格式為USING{BTREE | HASH}。
當定義索引時默認索引名，則一個主鍵的索引叫做“PRIMARY”，其他索引使用索引的第一個列名作為索引名。如果存在多個索引的名字以某一個列的名字開頭，就在列名後面放置一個順序號碼。
● constraint [symbol]：為主鍵、UNIQUE鍵、外鍵定義一個名字。以下也有用到。這個將在命名完整性約束一節中介紹。
● disable keys| enable keys：只在MyISAM表中有用，使用ALTER TABLE...DISABLE KEYS可以讓MySQL在更新表時停止更新MyISAM表中的非唯一索引，然後使用ALTER TABLE ... ENABLE KEYS重新創建丟失的索引，這樣可以大大地加快查詢的速度。
例： 在XS表的姓名列上創建一個非唯一的索引。
ALTER TABLE XS ADD INDEX XS_XM USING BTREE (姓名);
例： 以XS表為例（假設XS表中主鍵未定），創建這樣的索引，以加速表的檢索速度：
ALTER TABLE XS ADD PRIMARY KEY(學號),
ADD INDEX mark(出生日期,性別);
這個例子中，既包括PRIMARY KEY，也包括復合索引，說明MySQL可以同時創建多個索引。記住：使用PRIMARY KEY的列，必須是一個具有NOT NULL屬性的列。 www.2cto.com
如果想要查看表中創建的索引的情況，可以使用show index from索引名 語句，例如：SHOW INDEX FROM XS;
6. 在創建表時創建索引
在前面兩種情況下，索引都是在表創建之後創建的。索引也可以在創建表時一起創建。在創建表的CREATE TABLE語句中可以包含索引的定義。
語法格式：
Create [temporary] table [IF NOT EXISTS] 表名
[ ( [字段名] , ... |[index_definition] ) ]
[table_option] [select_statement];
其中，index_definition為索引項：
[constraint [symbol]]PRIMARY KEY [索引類型] (索引列,...) /*主鍵*/
| {INDEX | KEY}[索引名] [索引類型] (索引列,...) /*索引*/
| [constraint [symbol]] UNIQUE [INDEX] [索引名] [索引類型] (索引列，...) /*唯一性索引*/
| [FULLTEXT|SPATIAL][INDEX] [索引類型] (索引列,...) /*全文索引*/ www.2cto.com
| [constraint [symbol]] foreign KEY [索引類型] (索引列,...) [reference_definition] /*外鍵*/
說明：KEY通常是INDEX的同義詞。在定義列選項的時候，也可以將某列定義為PRIMARYKEY，但是當主鍵是由多個列組成的多列索引時，定義列時無法定義此主鍵，必須在語句最後加上一個PRIMARY KEY（col_name，…）子句。
例：創建XS_KC表的語句如下，XS_KC表帶有學號和課程號的聯合主鍵，並在成績列上創建索引。
CREATE TABLE XS_KC(
學號 CHAR(6) NOT NULL,
課程號 CHAR(3) NOT NULL,
成績 TINYINT(1),
學分 TINYINT(1),
PRIMARY KEY(學號,課程號),INDEX CJ(成績));
7. 1）. 使用DROP INDEX語句刪除索引
語法格式：DROP INDEX index_name ON tbl_name
這個語句語法非常簡單，index_name為要刪除的索引名，tb1_name為索引所在的表。
例： 刪除XS表上的XS_XH索引。
DROP INDEX XS_XH ON XS;
2）. 使用ALTER TABLE語句刪除索引
語法格式：
ALTER [ignore] TABLE tbl_name
| DROP PRIMARY KEY /*刪除主鍵*/
| DROP INDEX index_name /*刪除索引*/
| DROP FOREIGN KEY fk_symbol /*刪除外鍵*/
其中，DROP INDEX子句可以刪除各種類型的索引。使用DROP PRIMARY KEY子句時不需要提供索引名稱，因為一個表中只有一個主鍵。
www.2cto.com
例： 刪除XS表上的主鍵和mark索引。
ALTER TABLE XS DROP PRIMARY KEY, DROP INDEX mark;
如果從表中刪除了列，則索引可能會受到影響。如果所刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。
當執行涉及多個表的連接查詢時，索引將更有價值。
8. 索引的三種創建方法總結：
1.表已經建好了：
1） create index 索引名 on 表名(字段名，。。);
2) alter table 表名 add index 索引名(字段名，。。。);
2.在建表的同時創建索引
1) 直接指出主鍵：
create table abc
(id int primary key,
name varchar(10),
index ind_name(name));
2). 後面再指出主鍵：
create table abc1
www.2cto.com
(id int ,
name varchar(10),
primary key(id),
index ind_name(name));
作者 tianyazaiheruan
