大四了，疫情原因忘記了很多東西，從今往后三個月實訓時間開始知識拾遺

索引

• 什么是索引

數(shù)據(jù)庫索引（index），是數(shù)據(jù)庫管理系統(tǒng)中的一個排序的數(shù)據(jù)結(jié)構(gòu)，用于協(xié)助快速查詢、更新數(shù)據(jù)庫表中的數(shù)據(jù)。除了實際需要使用的數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)庫中實際需要使用的數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。

• 優(yōu)缺點

數(shù)據(jù)庫索引的主要目的就是提高數(shù)據(jù)的查詢效率，那么索引是如何提高查詢效率的呢？
通過使用索引，數(shù)據(jù)庫能夠快速找出索引列中特定值的那一行；而如果不使用索引，數(shù)據(jù)庫將不得不從第一條記錄開始查找相關(guān)列的特定值，直到找到對應的那一行為止。在這個過程中，表越大，則可能花費時間就越多。簡單說，如果待查詢的列有索引，則數(shù)據(jù)庫就能快速定位到該列的某一個值的位置（并關(guān)聯(lián)出相關(guān)記錄），而省去了查找無用記錄的過程，由此提高了數(shù)據(jù)查詢效率。需要注意的是，為數(shù)據(jù)庫表添加索引也是有代價的：一是索引會增加數(shù)據(jù)庫的存儲空間（創(chuàng)建索引時會在另外的表空間，如 mysql 中的 innodb 表空間，以一個類似目錄的結(jié)構(gòu)存儲索引信息），二是在插入和修改數(shù)據(jù)時會花費更多的時間（因為索引也要隨之變動）。

• 創(chuàng)建索引
  1.直接創(chuàng)建索引

普通索引

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length。

修改表結(jié)構(gòu)(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

創(chuàng)建表的時候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  

刪除索引的語法

DROP INDEX [indexName] ON table; 

使用“CREATE INDEX”語句或者使用創(chuàng)建索引向?qū)韯?chuàng)建索引，是最基本的索引創(chuàng)建方式。這種方式具有柔性，可以定制創(chuàng)建符合自己需要的索引。在使用這種方式創(chuàng)建索引時，可以使用很多選項，例如指定數(shù)據(jù)頁的充滿度、進行排序、整理統(tǒng)計信息等，通過這些選項可以優(yōu)化索引。同時，使用這種方法創(chuàng)建索引，也可以指定索引的類型、唯一性和復合性，即既可以創(chuàng)建聚簇索引，也可以創(chuàng)建非聚簇索引；既可以在一個列上創(chuàng)建索引，也可以在兩個或者兩個以上的列上創(chuàng)建索引。

唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：

創(chuàng)建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表結(jié)構(gòu)

ALTER table mytable ADD UNIQUE [indexName] (username(length))

創(chuàng)建表的時候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

使用ALTER 命令添加和刪除索引

有四種方式來添加數(shù)據(jù)表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會出現(xiàn)多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出現(xiàn)多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定了索引為 FULLTEXT ，用于全文索引。
  以下實例為在表中添加索引

ALTER TABLE testalter_tbl ADD INDEX (c);

你還可以在 ALTER 命令中使用 DROP 子句來刪除索引。嘗試以下實例刪除索引:

 ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和刪除主鍵

主鍵作用于列上（可以一個列或多個列聯(lián)合主鍵），添加主鍵索引時，你需要確保該主鍵默認不為空（NOT NULL）。實例如下：

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令刪除主鍵：

 ALTER TABLE testalter_tbl DROP PRIMARY KEY;

刪除主鍵時只需指定PRIMARY KEY，但在刪除索引時，你必須知道索引名。

顯示索引信息

你可以使用 SHOW INDEX 命令來列出表中的相關(guān)的索引信息?？梢酝ㄟ^添加 \G 來格式化輸出信息。
嘗試以下實例:

 SHOW INDEX FROM table_name; \G
........

• 間接創(chuàng)建索引

在表中定義主鍵（primary key）約束或者唯一鍵（unique key）約束時，同時也會自動創(chuàng)建索引。
主鍵約束是一種保持數(shù)據(jù)完整性的邏輯，它禁止表中的記錄出現(xiàn)相同的主鍵記錄。在創(chuàng)建主鍵約束時，系統(tǒng)會自動創(chuàng)建一個唯一性的聚簇索引。在邏輯結(jié)構(gòu)上，主鍵約束是一種重要的結(jié)構(gòu)，同時，在物理結(jié)構(gòu)上，與主鍵約束相對應的結(jié)構(gòu)是唯一性的聚簇索引。同樣，在創(chuàng)建唯一鍵約束時，也同時創(chuàng)建了索引，這種索引則是唯一性的非聚簇索引。
因此，當使用約束間接創(chuàng)建索引時，索引的類型和特征基本上都已經(jīng)確定了，用戶基本上是不可以定制的。
說明：
當在表上定義主鍵或者唯一性鍵約束時，如果表中已經(jīng)有了使用“CREATE INDEX”語句創(chuàng)建的標準索引時，那么主鍵約束或者唯一鍵約束創(chuàng)建的索引會覆蓋以前創(chuàng)建的標準索引。即，主鍵約束或者唯一性鍵約束創(chuàng)建的索引的優(yōu)先級高于使用“CREATE INDEX”語句直接創(chuàng)建的索引；
在聚簇索引中，表中行的物理順序與鍵值的邏輯順序相同（一個表只能包含一個聚集索引）。相反，如果某索引不是聚集索引，則表中行的物理順序與鍵值的邏輯順序是不匹配的。與非聚集索引相比，聚集索引通常提供更快的數(shù)據(jù)訪問速度。

鍵（key）與索引

• key 與 index 是不同的層面上的概念

key 是數(shù)據(jù)庫關(guān)系模型理論中的一部份，比如有主鍵（Primary Key)、唯一鍵（Unique Key）、外鍵（Foreign Key）等，用于數(shù)據(jù)完整性檢查與唯一性約束等作用。
index 則處于物理實現(xiàn)層面，如果對表個的任意列建立索引，那么當建立索引的列處于 SQL 語句中的 where 條件中時，就可以進行快速的數(shù)據(jù)定位，從而實現(xiàn)快速檢索。至于唯一索引（Unique Index），只是屬于 index 的一種而已，創(chuàng)建了唯一索引表示此列數(shù)據(jù)不可重復。
所以，在設(shè)計表的時候，key 是要處于模型層面的，而當需要進行查詢優(yōu)化，就需要對相關(guān)列建立索引了。

本文部分轉(zhuǎn)載于數(shù)據(jù)庫索引（index）簡介