1、電子課件數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）第9章第9章MySQL索引數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）CONTENTS索引1索引的定義和管理2設(shè)計(jì)原則和注意事項(xiàng)3小結(jié)4索引19.1.1 索引概述索引指向數(shù)據(jù)庫中具體數(shù)據(jù)所在位置，對(duì)相關(guān)列使用索引可提高select操作性能不同的存儲(chǔ)引擎定義了每一個(gè)表的最大索引數(shù)量和最大索引長(zhǎng)度，所有存儲(chǔ)引擎對(duì)每個(gè)表至少支持16個(gè)索引，總索引長(zhǎng)度至少為256字節(jié)。在MySQL中，索引其實(shí)與書的目錄非常的相似，由數(shù)據(jù)表中一列或多列組合而成，創(chuàng)建索引的目的是為了優(yōu)化數(shù)據(jù)庫的查詢速度。索引有兩種存儲(chǔ)類型：B

2、型樹（BTREE）索引哈希（HARSH）索引其中B型樹為系統(tǒng)默認(rèn)索引存儲(chǔ)類型。InnoDB和MyISAM存儲(chǔ)引擎支持B類型索引，MEMORY存儲(chǔ)引擎支持HASH類型索引。9.1.2 索引的作用1.索引優(yōu)點(diǎn)所謂索引其實(shí)就是對(duì)數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。9.1.2 索引的作用索引有兩個(gè)特征，即唯一性索引和復(fù)合索引。 3.索引的特征 復(fù)合索引就是一個(gè)索引創(chuàng)建在兩個(gè)列或者多個(gè)列上。唯一性索引保證在索引列中的全部數(shù)據(jù)是唯一的，不會(huì)包含冗余數(shù)據(jù)。9.1.3 索引的分類1.普通索引在創(chuàng)建普通索引時(shí)，不附加任何限制條件。MySQL的索引包括普通索引、唯一

3、性索引、全文索引、單列索引、多列索引和空間索引等。2.唯一性索引使用UNIQUE參數(shù)可以設(shè)置索引為唯一性索引。在創(chuàng)建唯一性索引時(shí)，限制該索引的值必須是唯一的。主鍵就是一種特殊唯一性索引。3.全文索引使用FULLTEXT參數(shù)可以設(shè)置索引為全文索引。全文索引只能創(chuàng)建在CHAR、VARCHAR或TEXT類型的字段上。9.1.3 索引的分類4.單列索引在表中的單個(gè)字段上創(chuàng)建索引。單列索引只根據(jù)該字段進(jìn)行索引。單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引。只要保證該索引只對(duì)應(yīng)一個(gè)字段即可。MySQL的索引包括普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等。5.多列索引多列

4、索引是在表的多個(gè)字段上創(chuàng)建一個(gè)索引。該索引指向創(chuàng)建時(shí)對(duì)應(yīng)的多個(gè)字段，可以通過這幾個(gè)字段進(jìn)行查詢。但是，只有查詢條件中使用了這些字段中第一個(gè)字段時(shí)，索引才會(huì)被使用。6.空間索引使用SPATIAL參數(shù)可以設(shè)置索引為空間索引?？臻g索引只能建立在空間數(shù)據(jù)類型上，這樣可以提高系統(tǒng)獲取空間數(shù)據(jù)的效率。索引的定義和管理29.2.1 創(chuàng)建索引創(chuàng)建索引是指在某個(gè)表的一列或多列上建立一個(gè)索引。創(chuàng)建索引方法主要有：直接創(chuàng)建索引，有以下三種方式，create table table_name(屬性名，數(shù)據(jù)類型 完整性約束,屬性名，數(shù)據(jù)類型 完整性約束,. . .屬性名，數(shù)據(jù)類型 完整性約束,index | key 索

5、引名 ( 屬性名 ( 長(zhǎng)度 ) asc | desc );1)在創(chuàng)建表的時(shí)候創(chuàng)建索引。9.2.1 創(chuàng)建索引創(chuàng)建索引是指在某個(gè)表的一列或多列上建立一個(gè)索引。創(chuàng)建索引方法主要有：直接創(chuàng)建索引，有以下三種方式，create index 索引名 ON 表名 (屬性名 ( 長(zhǎng)度) asc | desc );2)在已存在的表上創(chuàng)建索引alter table table_nameadd index | key 索引名 ( 屬性名 ( 長(zhǎng)度 ) asc | desc ) 3）使用alter table語句來創(chuàng)建索引間接創(chuàng)建索引，例如在表中定義主鍵約束或者唯一性鍵約束時(shí)，同時(shí)也創(chuàng)建了索引。9.2.1 創(chuàng)建索引1

6、.普通索引創(chuàng)建一個(gè)普通索引時(shí)，不需要加任何unique、fulltext或者sparial參數(shù)?！纠縿?chuàng)建一個(gè)新表newTable，包含int 型的id字段、varchar(20)類型的name字段和int型的age字段。在表的name字段的前10個(gè)字符上建立普通索引CREATE TABLE newTable (id INT NOT NULL PRIMARY KEY,NAME VARCHAR (20),age INT,INDEX name_index (NAME(10);9.2.1 創(chuàng)建索引創(chuàng)建完成后我們可以通過EXPLAIN語句輸出在表中查找name=”abc”的記錄時(shí)，檢查name_ind

7、ex索引是否被使用。9.2.1 創(chuàng)建索引【例】用create index命令在剛才新創(chuàng)建的newTable中添加age索引CREATE INDEX age_index ON newTable (age);用SHOW CREATE TABLE newTable查看newTable的表結(jié)構(gòu)9.2.1 創(chuàng)建索引多了一個(gè)age_index索引。9.2.1 創(chuàng)建索引【例】用alter table命令在name字段的前5個(gè)字節(jié)上創(chuàng)建降序排序ALTER TABLE newTable ADD INDEX name_index5 (NAME(5) DESC);用SHOW CREATE TABLE newTabl

8、e查看newTable的表結(jié)構(gòu)9.2.1 創(chuàng)建索引現(xiàn)在在newTable中的name字段上有兩個(gè)索引，區(qū)別只是索引名稱和索引長(zhǎng)度的不同。那么查找name=”abcdefg”時(shí)將使用哪一個(gè)索引，可以通過如下命令檢查。由顯示結(jié)果可以看出，使用的索引有name_index 和name_index5兩個(gè)索引。但數(shù)據(jù)庫使用了name_index索引。9.2.1 創(chuàng)建索引【例】指定使用name_index5索引用于name查詢SELECT * FROM newTable USE INDEX FOR JOIN (name_index5) WHERE NAME = abcdefg;用EXPLAIN查看輸出信息

9、,可知使用的name_index5而不是name_index9.2.1 創(chuàng)建索引1.唯一索引(unique index)創(chuàng)建唯一性索引時(shí)，需要使用unique參數(shù)進(jìn)行約束【例】創(chuàng)建新表newTable1，在表的id字段上建立名為id_index的唯一索引，以升序排列。CREATE TABLE newTable1 (id INT UNIQUE,NAME CHAR (20),age INT,UNIQUE INDEX id_index (id ASC);9.2.1 創(chuàng)建索引1.唯一索引(unique index)創(chuàng)建唯一性索引時(shí)，需要使用unique參數(shù)進(jìn)行約束【例】使用create index命令

10、在表newTable1的name字段上創(chuàng)建唯一索引CREATE UNIQUE INDEX name_index ON newTable1 (NAME);9.2.1 創(chuàng)建索引1.唯一索引(unique index)創(chuàng)建唯一性索引時(shí)，需要使用unique參數(shù)進(jìn)行約束【例】使用alter table命令在表newTable1的age上創(chuàng)建唯一性索引ALTER TABLE newTable1 ADD UNIQUE INDEX (age);9.2.1 創(chuàng)建索引2.全文索引(fulltext index)全文索引只能創(chuàng)建在char、varchar或者text類型的字段上?！纠縿?chuàng)建表newTable2，并

11、指定char(20)字段類型的字段Info為全文索引CREATE TABLE newTable2 (id INT NOT NULL PRIMARY KEY auto_increment,info CHAR (20),FULLTEXT INDEX info_index (info);9.2.1 創(chuàng)建索引2.全文索引(fulltext index)全文索引只能創(chuàng)建在char、varchar或者text類型的字段上。用SHOW CREATE TABLE newTable2命令查看表的結(jié)構(gòu)如果MySQL的版本低于5.6，就必須指明表的存儲(chǔ)引擎為MyISAM，否則會(huì)報(bào)錯(cuò)。9.2.1 創(chuàng)建索引3.多列索引

12、多列索引是在多個(gè)字段上創(chuàng)建一個(gè)索引【例】創(chuàng)建表newTable3，在類型char(20)的name字段上和int類型的age字段上創(chuàng)建多列索引CREATE TABLE newTable3 (id INT NOT NULL PRIMARY KEY,NAME CHAR (20),age INT,INDEX name_age_index (NAME, age);9.2.1 創(chuàng)建索引3.多列索引多列索引是在多個(gè)字段上創(chuàng)建一個(gè)索引用explain可以查看下面兩種查詢對(duì)索引的使用情況。9.2.2 查看索引在實(shí)際使用索引的過程中，有時(shí)需要對(duì)表的索引信息進(jìn)行查詢，了解在表中曾經(jīng)建立的索引SHOW INDEX

13、FROM table_name FROM db_name SHOW INDEX FROM mytable FROM mydb; SHOW INDEX FROM mydb.mytable; 【例】查看newTable中索引的詳細(xì)信息SHOW INDEX FROM newTable;9.2.2 查看索引show index會(huì)返回表索引信息，包含以下字段：Table表的名稱。 Non_unique索引能不能包括重復(fù)詞，不能則為0。如果可以，則為1。Key_name索引的名稱。 Seq_in_index索引中的列序列號(hào)，從1開始。 Column_name列名稱。Collation列以什么方式存儲(chǔ)在索引

14、中。在MySQL中，有值A(chǔ)（升序）或NULL（無分類）。9.2.3 刪除索引在用戶不需要該表的索引時(shí)，可以手動(dòng)刪除指定索引。DROP INDEX index_name ON table_name ; ALTER TABLE table_name DROP INDEX index_name ; ALTER TABLE table_name DROP PRIMARY KEY ; 【例】下面刪除newTable中的索引的name_index。DROP INDEX name_index ON newTable; 9.2.3 刪除索引在用戶不需要該表的索引時(shí)，可以手動(dòng)刪除指定索引。DROP INDEX index_name ON table_name ; ALTER TABLE table_name DROP INDEX index_name ; ALTER TABLE table_name DROP PRIMARY KEY ; 【例】刪除newTable3上的primary key索引ALTER TABLE newTable3 DROP PRIMARY KEY ; 注意：如果從表中刪除某列，則索引會(huì)受影響。對(duì)于多列組合的索引，如果刪除其中的某列，則該列也會(huì)從索引中刪除。如果刪除組成索引的所有列，則整個(gè)索引將被刪除。9.3 設(shè)計(jì)原則和注意