MySQL 索引

MySQL 索引是一種數(shù)據(jù)結(jié)構(gòu)，用于加快數(shù)據(jù)庫查詢的速度和性能。

成都創(chuàng)新互聯(lián)公司主營梅列網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,手機APP定制開發(fā),梅列h5成都小程序開發(fā)搭建,梅列網(wǎng)站營銷推廣歡迎梅列等地區(qū)企業(yè)咨詢

MySQL 索引的建立對于 MySQL 的高效運行是很重要的，索引可以大大提高 MySQL 的檢索速度。

MySQL 索引類似于書籍的索引，通過存儲指向數(shù)據(jù)行的指針，可以快速定位和訪問表中的特定數(shù)據(jù)。

打個比方，如果合理的設(shè)計且使用索引的 MySQL 是一輛蘭博基尼的話，那么沒有設(shè)計和使用索引的 MySQL 就是一個人力三輪車。

拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查找到需要的字。

索引分單列索引和組合索引：

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

創(chuàng)建索引時，你需要確保該索引是應(yīng)用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。

實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。

索引雖然能夠提高查詢性能，但也需要注意以下幾點：

• 索引需要占用額外的存儲空間。
• 對表進行插入、更新和刪除操作時，索引需要維護，可能會影響性能。
• 過多或不合理的索引可能會導(dǎo)致性能下降，因此需要謹(jǐn)慎選擇和規(guī)劃索引。

---

普通索引

索引能夠顯著提高查詢的速度，尤其是在大型表中進行搜索時。通過使用索引，MySQL 可以直接定位到滿足查詢條件的數(shù)據(jù)行，而無需逐行掃描整個表。

創(chuàng)建索引

使用 CREATE INDEX 語句可以創(chuàng)建普通索引。

普通索引是最常見的索引類型，用于加速對表中數(shù)據(jù)的查詢。

CREATE INDEX 的語法：

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE INDEX: 用于創(chuàng)建普通索引的關(guān)鍵字。
• index_name: 指定要創(chuàng)建的索引的名稱。索引名稱在表中必須是唯一的。
• table_name: 指定要在哪個表上創(chuàng)建索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

以下實例假設(shè)我們有一個名為 students 的表，包含 id、name 和 age 列，我們將在 name 列上創(chuàng)建一個普通索引。

CREATE INDEX idx_name ON students (name);

上述語句將在 students 表的 name 列上創(chuàng)建一個名為 idx_name 的普通索引，這將有助于提高通過姓名進行搜索的查詢性能。

需要注意的是，如果表中的數(shù)據(jù)量較大，索引的創(chuàng)建可能會花費一些時間，但一旦創(chuàng)建完成，查詢性能將會顯著提高。

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

我們可以使用 ALTER TABLE 命令可以在已有的表中創(chuàng)建索引。

ALTER TABLE 允許你修改表的結(jié)構(gòu)，包括添加、修改或刪除索引。

ALTER TABLE 創(chuàng)建索引的語法：

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• ALTER TABLE: 用于修改表結(jié)構(gòu)的關(guān)鍵字。
• table_name: 指定要修改的表的名稱。
• ADD INDEX: 添加索引的子句。ADD INDEX用于創(chuàng)建普通索引。
• index_name: 指定要創(chuàng)建的索引的名稱。索引名稱在表中必須是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

下面是一個實例，我們將在已存在的名為 employees 的表上創(chuàng)建一個普通索引:

ALTER TABLE employees
ADD INDEX idx_age (age);

上述語句將在 employees 表的 age 列上創(chuàng)建一個名為 idx_age 的普通索引。

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

我們可以在創(chuàng)建表的時候，你可以在 CREATE TABLE 語句中直接指定索引，以創(chuàng)建表和索引的組合。

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

• CREATE TABLE: 用于創(chuàng)建新表的關(guān)鍵字。
• table_name: 指定要創(chuàng)建的表的名稱。
• (column1, column2, ...): 定義表的列名和數(shù)據(jù)類型。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• INDEX: 用于創(chuàng)建普通索引的關(guān)鍵字。
• index_name: 指定要創(chuàng)建的索引的名稱。索引名稱在表中必須是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

下面是一個實例，我們要創(chuàng)建一個名為 students 的表，并在 age 列上創(chuàng)建一個普通索引。

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

在上述實例中，我們在 students 表的 age 列上創(chuàng)建了一個名為 idx_age 的普通索引。

刪除索引的語法

我們可以使用 DROP INDEX 語句來刪除索引。

DROP INDEX 的語法：

DROP INDEX index_name ON table_name;

• DROP INDEX: 用于刪除索引的關(guān)鍵字。
• index_name: 指定要刪除的索引的名稱。
• ON table_name: 指定要在哪個表上刪除索引。

使用 ALTER TABLE 語句刪除索引的語法如下：

ALTER TABLE table_name
DROP INDEX index_name;

• ALTER TABLE: 用于修改表結(jié)構(gòu)的關(guān)鍵字。
• table_name: 指定要修改的表的名稱。
• DROP INDEX: 用于刪除索引的子句。
• index_name: 指定要刪除的索引的名稱。

以下實例假設(shè)我們有一個名為 employees 的表，并在 age 列上有一個名為 idx_age 的索引，現(xiàn)在我們要刪除這個索引：

DROP INDEX idx_age ON employees;

或使用 ALTER TABLE 語句：

ALTER TABLE employees
DROP INDEX idx_age;

這兩個命令都會從 employees 表中刪除名為 idx_age 的索引。

如果該索引不存在，執(zhí)行命令時會產(chǎn)生錯誤。因此，在刪除索引之前最好確認(rèn)該索引是否存在，或者使用錯誤處理機制來處理可能的錯誤情況。

---

唯一索引

在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 語句來創(chuàng)建唯一索引。

唯一索引確保索引中的值是唯一的，不允許有重復(fù)值。

創(chuàng)建索引

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE UNIQUE INDEX: 用于創(chuàng)建唯一索引的關(guān)鍵字組合。
• index_name: 指定要創(chuàng)建的唯一索引的名稱。索引名稱在表中必須是唯一的。
• table_name: 指定要在哪個表上創(chuàng)建唯一索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

以下是一個創(chuàng)建唯一索引的實例： 假設(shè)我們有一個名為 employees的 表，包含 id 和 email 列，現(xiàn)在我們想在email列上創(chuàng)建一個唯一索引，以確保每個員工的電子郵件地址都是唯一的。

CREATE UNIQUE INDEX idx_email ON employees (email);

以上實例將在 employees 表的 email 列上創(chuàng)建一個名為 idx_email 的唯一索引。

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

我們可以使用 ALTER TABLE 命令來創(chuàng)建唯一索引。

ALTER TABLE命令允許你修改已經(jīng)存在的表結(jié)構(gòu)，包括添加新的索引。

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

• ALTER TABLE: 用于修改表結(jié)構(gòu)的關(guān)鍵字。
• table_name: 指定要修改的表的名稱。
• ADD CONSTRAINT: 這是用于添加約束（包括唯一索引）的關(guān)鍵字。
• index_name: 指定要創(chuàng)建的唯一索引的名稱。約束名稱在表中必須是唯一的。
• UNIQUE (column1, column2, ...): 指定要索引的表列名。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

以下是一個使用 ALTER TABLE 命令創(chuàng)建唯一索引的實例：假設(shè)我們有一個名為 employees 的表，包含 id 和 email 列，現(xiàn)在我們想在 email 列上創(chuàng)建一個唯一索引，以確保每個員工的電子郵件地址都是唯一的。

ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

以上實例將在 employees 表的 email 列上創(chuàng)建一個名為 idx_email 的唯一索引。

請注意，如果表中已經(jīng)有重復(fù)的 email 值，那么添加唯一索引將會失敗。在創(chuàng)建唯一索引之前，你可能需要確保表中的 email 列沒有重復(fù)的值。

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

我們也可以在創(chuàng)建表的同時，你可以在 CREATE TABLE 語句中使用 UNIQUE 關(guān)鍵字來創(chuàng)建唯一索引。

這將在表創(chuàng)建時同時定義唯一索引約束。

CREATE TABLE 語句中創(chuàng)建唯一索引的語法：

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

• CREATE TABLE: 用于創(chuàng)建新表的關(guān)鍵字。
• table_name: 指定要創(chuàng)建的表的名稱。
• (column1, column2, ...): 定義表的列名和數(shù)據(jù)類型。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• CONSTRAINT: 用于添加約束的關(guān)鍵字。
• index_name: 指定要創(chuàng)建的唯一索引的名稱。約束名稱在表中必須是唯一的。
• UNIQUE (column1, column2, ...): 指定要索引的表列名。

以下是一個在創(chuàng)建表時創(chuàng)建唯一索引的實例：假設(shè)我們要創(chuàng)建一個名為 employees 的表，其中包含 id、name 和 email 列，我們希望 email 列的值是唯一的，因此我們要在創(chuàng)建表時定義唯一索引。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

在這個例子中，email 列被定義為唯一索引，因為在它的后面加上了 UNIQUE 關(guān)鍵字。

請注意，使用 UNIQUE 關(guān)鍵字后，索引名稱將自動生成，你也可以根據(jù)需要指定索引名稱。

---

使用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 ，用于全文索引。

以下實例為在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

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

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

---

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

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

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

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

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

---

顯示索引信息

你可以使用 SHOW INDEX 命令來列出表中的相關(guān)的索引信息。

可以通過添加 \G 來格式化輸出信息。

SHOW INDEX 語句：:

mysql> SHOW INDEX FROM table_name\G
........

• SHOW INDEX: 用于顯示索引信息的關(guān)鍵字。
• FROM table_name: 指定要查看索引信息的表的名稱。
• \G: 格式化輸出信息。

執(zhí)行上述命令后，將會顯示指定表中所有索引的詳細信息，包括索引名稱（Key_name）、索引列（Column_name）、是否是唯一索引（Non_unique）、排序方式（Collation）、索引的基數(shù)（Cardinality）等。

標(biāo)題名稱：創(chuàng)新互聯(lián)MYSQL教程MySQL 索引
轉(zhuǎn)載來于：http://www.fisionsoft.com.cn/article/dppogcs.html