索引的定義

數(shù)據(jù)庫中的索引就像一本書的目錄，可以據(jù)此快速定位數(shù)據(jù)庫中相關(guān)數(shù)據(jù)的所在位置。

在數(shù)據(jù)庫中，索引被定義為一種特殊的數(shù)據(jù)結(jié)構(gòu)，由數(shù)據(jù)庫中的一列或多列組合而成，可以用來快速查詢數(shù)據(jù)表中某一特定值的記錄。

索引是在表的字段的基礎(chǔ)上建立的一種數(shù)據(jù)庫對象，它由DBA或者表的擁有者創(chuàng)建或撤銷，他是創(chuàng)建表與表之間關(guān)聯(lián)關(guān)系的基礎(chǔ)。

索引的類型

普通、唯一、主鍵和全文索引

普通索引（INDEX）

普通索引是MySQL中的基本索引類型，允許在定義索引的列中插入空值和重復(fù)值。

創(chuàng)建普通索引的幾種方式：

# 1.直接創(chuàng)建索引
create index index_name on table(column(length));

# 2. 以修改表結(jié)構(gòu)創(chuàng)建
alter table table_name add index index_name on column(length);

# 3. 創(chuàng)建表的同時創(chuàng)建索引
create table user(
	id CHAR(6) not null,
	name CHAR(255) not null,
	primary key(id),
	index user_name(name(length));
)

唯一索引（UNIQUE INDEX）

唯一索引指索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

創(chuàng)建唯一索引的幾種方式：

# 1.直接創(chuàng)建索引
create unique index index_name on table(column(length));

# 2. 以修改表結(jié)構(gòu)創(chuàng)建
alter table table_name add unique index index_name on column(length);

# 3. 創(chuàng)建表的同時創(chuàng)建索引
create table user(
	id CHAR(6) not null,
	name CHAR(255) not null,
	primary key(id),
	unique index user_name(name(length));
)

主鍵索引（PRIMARY KEY）

主鍵索引是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。

全文索引（FULLTEXT）

全文索引的類型為FULLTEXT，表示在定義索引的列上支持值的全文查找，允許插入重復(fù)值或空值。

全文索引主要用來查找文本中的關(guān)鍵字，而不是直接與索引中的值相比較。

MySQL中只有MyISAM存儲引擎支持全文索引。

全文索引可以在進(jìn)行創(chuàng)建表、更新表和創(chuàng)建索引時使用，目前只支持CHAR、VARCHAR、TEXT列創(chuàng)建全文索引。

單列索引和組合索引

• 單列索引

單列索引即一個索引只包含單個列，一個表可以有多個單列索引。

• 組合索引

組合索引是指組合表的多個字段創(chuàng)建的索引，，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用。遵循最左前綴匹配原則

聚集索引和非聚集索引

• 聚集索引

聚集索引是索引結(jié)構(gòu)和數(shù)據(jù)一起存放的索引，索引結(jié)構(gòu)的葉子節(jié)點上包含著該行的所有信息，找到索引也就找到了數(shù)據(jù)。

• 非聚集索引

非聚集索引是索引結(jié)構(gòu)和數(shù)據(jù)分開存放的索引，索引結(jié)構(gòu)的葉子節(jié)點指向了數(shù)據(jù)的對應(yīng)行，當(dāng)需要訪問數(shù)據(jù)時，在內(nèi)存中先搜索索引，然后通過索引找到磁盤相應(yīng)數(shù)據(jù)。

索引的創(chuàng)建原則

1. 創(chuàng)建索引由專人完成

• 索引由DBA或表的擁有者負(fù)責(zé)創(chuàng)建和撤銷，其他用戶不能隨意操作。
• 索引由系統(tǒng)自動選擇，或由用戶打開，用戶可執(zhí)行重建索引操作。

2.是否創(chuàng)建索引取決于表的數(shù)據(jù)量

• 基本表中記錄的數(shù)量越多，記錄越長，越有必要創(chuàng)建索引。創(chuàng)建索引后，查詢速度的提升效果會很明顯。要避免對經(jīng)常更新的表創(chuàng)建過多的索引，索引中的列也要盡可能少。
• 數(shù)據(jù)量小的表最好不要使用索引。由于數(shù)據(jù)較少，查詢花費的時間可能比遍歷索引的時間還要短，因此，創(chuàng)建索引可能不會產(chǎn)生優(yōu)化效果。對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引，但要避免添加不必要的字段。
• 索引要根據(jù)數(shù)據(jù)查詢或數(shù)據(jù)處理的要求確定是否創(chuàng)建。對于查詢頻度高、實時性要求高的數(shù)據(jù)一定要建立索引。

3.索引數(shù)量要適度

• 索引文件占用文件目錄和存儲空間，因此索引過多會加重系統(tǒng)負(fù)擔(dān)。
• 索引需要自身維護(hù)。當(dāng)基本表的數(shù)據(jù)增加、刪除或修改時，索引也會進(jìn)行調(diào)整和更新，索引文件也要隨之變化，以保持與基本表一致。
• 索引過多會影響數(shù)據(jù)增、刪、改的速度。索引并非越多越好，-張表中如果有大量的索引，不僅占用磁盤空間，而且還會影響INSERT、DELETE、UPDATE等操作的性能。

4.避免使用索引的情形

(1)包含太多重復(fù)值的字段。

(2)查詢中很少被引用的字段。

(3)值特別長的字段。

(4)查詢返回率很高的字段。

(5)具有很多NULL值的字段。

(6)需要經(jīng)常增、刪、改的字段。

(7)記錄較少的基本表。

(8)需要頻繁、大批量進(jìn)行數(shù)據(jù)更新的基本表。

索引操作

創(chuàng)建索引

create table <table_name> [<fields_name> type]
[UNIQUE|FULLTEXT][INDEX|KEY] 
<index_name> (<column_name > [length]) [ASC|DESC]

查看索引

show index from <table_name> [from <database_name>]

刪除索引

1.刪除索引

drop index <index_name> from <table_name>

2.通過修改表結(jié)構(gòu)刪除

alter table <table_name> drop index <index_name>

總結(jié)

到此這篇關(guān)于MySQL中索引的定義以及操作的文章就介紹到這了,更多相關(guān)MySQL索引定義及操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論