第七章-索引

1、第七章,索引,項目知識要點與目標,索引及其應用,索引 —— 是一種提高查找速度的機制,索引用來快速地尋找那些具有特定值的記錄，如果沒有索引，執(zhí)行查詢時MySQL必須從第一個記錄開始掃描整個表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個操作的代價就越高。索引提供指針以指向存儲在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針。數(shù)據(jù)庫使用索引的方式與使用書的目錄很相似：通過搜索索引找到特定的值，然后跟隨指針到達包含

2、該值的行。 如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無需掃描任何記錄即可迅速得到目標記錄所在的位置。如果表有1000個記錄，通過索引查找記錄至少要比順序掃描記錄快100倍。,索引文件,學生表student中建立“學號”索引（升序）示意圖,學生表 student,索引文件,,,,,,索引文件如何影響原表,,沒有索引文件時：,,,,,指針在原表中順序移動,如果要找位于第10000條的學號”20070201”的記錄, 計算機要在表

3、中查找10000次,使用索引查找,有索引文件時：(二分法查找實例) 計算機先在索引文件中學號為”20070201”的記錄,找到相應的記錄號,再到學生表中直接讀取相關記錄.,學生表 student,索引文件,,,索引文件如何加快查找速度,原因： （1）索引后，指針在索引文件中順序移動。 （2）索引文件中記錄是有序的。 （3）有序后，可以用各種方法加快查詢速度， 如折半（二分）查找法，而排序前，只

4、 能順序查找記錄。,B樹索引示意圖,索引的分類,1. 普通索引（INDEX）這是最基本的索引類型，它沒有唯一性之類的限制。創(chuàng)建普通索引的關鍵字是INDEX。2. 唯一性索引（UNIQUE）這種索引和前面的普通索引基本相同，但有一個區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須是唯一的。創(chuàng)建唯一性索引的關鍵字是UNIQUE。3. 主鍵（PRIMARY KEY）主鍵是一種唯一性索引，它必須指定為“PRIMARY KE

5、Y”。主鍵一般在創(chuàng)建表的時候指定，也可以通過修改表的方式加入主鍵。但是每個表只能有一個主鍵。4. 全文索引（FULLTEXT）MySQL支持全文檢索和全文索引。全文索引的索引類型為FULLTEXT。全文索引只能在VARCHAR或TEXT類型的列上創(chuàng)建，并且只能在MyISAM表中創(chuàng)建。,,創(chuàng)建索引,1. 使用CREATE INDEX語句 使用CREATE INDEX語句可以在一個已有表上創(chuàng)建索引，一個表可以創(chuàng)建多個索引。語法格

6、式：CREATE [UNIQUE | FULLTEXT] INDEX 索引名ON 表名(列名[(長度)] [ASC | DESC],...)說明：●索引名：索引的名稱，索引名在一個表中名稱必須是唯一的。● 列名：表示創(chuàng)建索引的列名。 長度：表示使用列的前多少個字符創(chuàng)建索引。使用列的一部分創(chuàng)建索引可以使索引文件大大減小，從而節(jié)省磁盤空間。BLOB或TEXT列必須用前綴索引?！?UNIQUE:UNIQUE表示創(chuàng)建的是唯一性索

7、引● FULLTEXT:FULLTEXT表示創(chuàng)建全文索引；● CREATE INDEX 語句并不能創(chuàng)建主鍵。,創(chuàng)建索引舉例,【例7.1】 根據(jù)Book表的書名列上的前6個字符建立一個升序索引name­_book。 CREATE INDEX name­_book ON Book(書名(6) ASC);可以在一個索引的定義中包含多個列，中間用逗號隔開，但是它們要屬于同一

8、個表。這樣的索引叫做復合索引?！纠?.2】 在Sell表的用戶號列和圖書編號列上建立一個復合索引sfz_bh­_sell。 CREATE INDEX user_bh­_sell ON Sell(用戶號,圖書編號);,ALTER TABLE語句創(chuàng)建索引,使用ALTER TABLE語句使用ALTER TABLE語句修改表，其中也包括向表中添加索引。語法格式如下： A

9、LTER TABLE 表名 ADD INDEX [索引名] (列名,...) /*添加索引*/ | ADD PRIMARY KEY [索引方式] (列名,...) /*添加主鍵*/ | ADD UNIQUE [索引名] (列名,...) /*添加唯一性索引*/ | ADD FULLTEXT [索引名] (列名,...) /*添加全文索引*/,ALTER TA

10、BLE創(chuàng)建索引舉例,【例7.3】 在Book表的書名列上創(chuàng)建一個普通索引。 ALTER TABLE Book ADD INDEX sm_book (書名);【例7.4】假設Book表中主鍵未設定，為Book表創(chuàng)建以圖書編號為主鍵索引，出版社和出版時間為復合索引，以加速表的檢索速度。 ALTER TABLE BookADD PRIMARY KEY(圖書編號),ADD INDEX mark(出版社,

11、出版時間); 這個例子中，既包括PRIMARY KEY，也包括復合索引，說明MySQL可以同時創(chuàng)建多個索引。記住，使用PRIMARY KEY的列，必須是一個具有NOT NULL屬性的列。如果想要查看表中創(chuàng)建的索引的情況，可以使用SHOW INDEX FROM tbl_name語句，例如：SHOW INDEX FROM book;,創(chuàng)建表時創(chuàng)建索引,在前面兩種情況下，索引都是在表創(chuàng)建之后創(chuàng)建的。索引也可以在創(chuàng)建表時一起創(chuàng)建

12、。在創(chuàng)建表的CREATE TABLE語句中可以包含索引的定義。語法格式： CREATE TABLE 表名 ( 列名, ... | [索引項])其中，索引項語法格式如下： PRIMARY KEY (列名,...) /*主鍵*/ | {INDEX | KEY} [索引名] (列名,...) /*索引*/ | UNIQUE [INDEX] [索引名

13、] (列名,...) /*唯一性索引*/ | [FULLTEXT] [INDEX] [索引名] (列名,...) /*全文索引*/說明：KEY通常是INDEX的同義詞。在定義列選項的時候，也可以將某列定義為PRIMARY KEY，但是當主鍵是由多個列組成的多列索引時，定義列時無法定義此主鍵，必須在語句最后加上一個PRIMARY KEY列名，…）子句。,【例7.5】 創(chuàng)建sell_copy表的語句如下，sell_c

14、opy表帶有身份證號和圖書編號的聯(lián)合主鍵，并在訂購冊數(shù)列上創(chuàng)建索引。 CREATE TABLE sell_copy (身份證號 CHAR(18) NOT NULL,圖書編號 CHAR(20) NOT NULL,訂購冊數(shù) INT(5),訂購時間 DATETIME,PRIMARY KEY(身份證號, 圖書編號),INDEX dgcs(訂購冊數(shù)) );,創(chuàng)建表時創(chuàng)建索引舉例,刪除索引

15、,1. 使用DROP INDEX語句刪除索引語法格式： DROP INDEX索引名 ON 表名【例7.6】 刪除Book表上的sm_book索引。 DROP INDEX sm_book ON Book;,ALTER TABLE刪除索引,2. 使用ALTER TABLE語句刪除索引 語法格式： ALTER [IGNORE] TABLE 表名 |

16、DROP PRIMARY KEY /*刪除主鍵*/ | DROP INDEX 索引名 /*刪除索引*/【例7.7】 刪除Book表上的主鍵和mark索引。 ALTER TABLE BookDROP PRIMARY KEY,DROP INDEX mark; 如果從表中刪除了列，則索引可能會受到影響。如果所刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所

17、有列都被刪除，則整個索引將被刪除。,索引對查詢的影響,目前本書實例中所涉及的表最多只有幾十行的數(shù)據(jù)，所以有沒有建立索引，還體會不到查詢速度上的差異，可是當一個表里有成千上萬行數(shù)據(jù)的時候，差異就非常明顯了?，F(xiàn)在假設有一個表，表里只有一列，由數(shù)值1～1000的1000行組成，現(xiàn)在要想查找到數(shù)字1000所在的行。如果沒有索引，要從第一行開始匹配，若數(shù)值不是1000，則轉到下一行進行匹配，這樣直到第1000行的時候才能找到數(shù)字1000所在行，也

18、就是說服務器進行了1000次的運算。而當在該列上創(chuàng)建一個索引后，則可以直接在索引值中找到1000的位置，然后找到1000所指向的行，在速度上比全表掃描至少快了100倍。當執(zhí)行涉及多個表的連接查詢時，索引將更有價值。,索引的弊端,首先，索引是以文件的形式存儲的，索引文件要占用磁盤空間。如果有大量的索引，索引文件可能會比數(shù)據(jù)文件更快地達到最大的文件尺寸。其次，在更新表中索引列上的數(shù)據(jù)時，對索引也需要更新，這可能需要重新組織一個索引，如果