MySQL中索引的定義以及操作新手教學

2022-08-25 14:04:17

索引的定義

資料庫中的索引就像一本書的目錄，可以據此快速定位資料庫中相關資料的所在位置。

在資料庫中，索引被定義為一種特殊的資料結構，由資料庫中的一列或多列組合而成，可以用來快速查詢資料表中某一特定值的記錄。

索引是在表的欄位的基礎上建立的一種資料庫物件，它由DBA或者表的擁有者建立或復原，他是建立表與表之間關聯關係的基礎。

索引的型別

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

普通索引（INDEX）

普通索引是MySQL中的基本索引型別，允許在定義索引的列中插入空值和重複值。

建立普通索引的幾種方式：

# 1.直接建立索引
create index index_name on table(column(length));

# 2. 以修改表結構建立
alter table table_name add index index_name on column(length);

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

唯一索引（UNIQUE INDEX）

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

建立唯一索引的幾種方式：

# 1.直接建立索引
create unique index index_name on table(column(length));

# 2. 以修改表結構建立
alter table table_name add unique index index_name on column(length);

# 3. 建立表的同時建立索引
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，表示在定義索引的列上支援值的全文查詢，允許插入重複值或空值。

全文索引主要用來查詢文字中的關鍵字，而不是直接與索引中的值相比較。

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

全文索引可以在進行建立表、更新表和建立索引時使用，目前只支援CHAR、VARCHAR、TEXT列建立全文索引。

單列索引和組合索引

單列索引

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

組合索引

組合索引是指組合表的多個欄位建立的索引，，只有在查詢條件中使用了建立索引時的第一個欄位，索引才會被使用。遵循最左字首匹配原則

聚集索引和非聚集索引

聚集索引

聚集索引是索引結構和資料一起存放的索引，索引結構的葉子節點上包含著該行的所有資訊，找到索引也就找到了資料。

非聚集索引

非聚集索引是索引結構和資料分開存放的索引，索引結構的葉子節點指向了資料的對應行，當需要存取資料時，在記憶體中先搜尋索引，然後通過索引找到磁碟相應資料。

聚集索引	非聚集索引
一個表只能有一個	一個表可以有多個
物理連續	邏輯連續，物理不連續
查詢快，插入慢（需要移動物理位置）	回表查詢
索引的葉節點就是資料節點	索引的葉節點仍然是索引節點，通過一個指標指向對應的資料塊。

索引的建立原則

1. 建立索引由專人完成

索引由DBA或表的擁有者負責建立和復原，其他使用者不能隨意操作。
索引由系統自動選擇，或由使用者開啟，使用者可執行重建索引操作。

2.是否建立索引取決於表的資料量

基本表中記錄的數量越多，記錄越長，越有必要建立索引。建立索引後，查詢速度的提升效果會很明顯。要避免對經常更新的表建立過多的索引，索引中的列也要儘可能少。
資料量小的表最好不要使用索引。由於資料較少，查詢花費的時間可能比遍歷索引的時間還要短，因此，建立索引可能不會產生優化效果。對經常用於查詢的欄位應該建立索引，但要避免新增不必要的欄位。
索引要根據資料查詢或資料處理的要求確定是否建立。對於查詢頻度高、實時性要求高的資料一定要建立索引。

3.索引數量要適度

索引檔案佔用檔案目錄和儲存空間，因此索引過多會加重系統負擔。
索引需要自身維護。當基本表的資料增加、刪除或修改時，索引也會進行調整和更新，索引檔案也要隨之變化，以保持與基本表一致。
索引過多會影響資料增、刪、改的速度。索引並非越多越好，-張表中如果有大量的索引，不僅佔用磁碟空間，而且還會影響INSERT、DELETE、UPDATE等操作的效能。

4.避免使用索引的情形

(1)包含太多重複值的欄位。

(2)查詢中很少被參照的欄位。

(3)值特別長的欄位。

(4)查詢返回率很高的欄位。

(5)具有很多NULL值的欄位。

(6)需要經常增、刪、改的欄位。

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

(8)需要頻繁、大批次進行資料更新的基本表。

索引操作

建立索引

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.通過修改表結構刪除

alter table <table_name> drop index <index_name>

總結

到此這篇關於MySQL中索引的定義以及操作的文章就介紹到這了,更多相關MySQL索引定義及操作內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！