MySQL 索引

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

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

MySQL 索引的建立對(duì)于 MySQL 的高效運(yùn)行是很重要的，索引可以大大提高 MySQL 的檢索速度。

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

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

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

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

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

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

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

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

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

---

普通索引

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

創(chuàng)建索引

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

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

CREATE INDEX 的語(yǔ)法：

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

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

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

CREATE INDEX idx_name ON students (name);

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

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

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

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

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

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

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: 指定要?jiǎng)?chuàng)建的索引的名稱。索引名稱在表中必須是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一個(gè)或多個(gè)列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

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

ALTER TABLE employees
ADD INDEX idx_age (age);

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

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

我們可以在創(chuàng)建表的時(shí)候，你可以在 CREATE TABLE 語(yǔ)句中直接指定索引，以創(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: 指定要?jiǎng)?chuàng)建的表的名稱。
• (column1, column2, ...): 定義表的列名和數(shù)據(jù)類型。你可以指定一個(gè)或多個(gè)列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• INDEX: 用于創(chuàng)建普通索引的關(guān)鍵字。
• index_name: 指定要?jiǎng)?chuàng)建的索引的名稱。索引名稱在表中必須是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一個(gè)或多個(gè)列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

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

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

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

刪除索引的語(yǔ)法

我們可以使用 DROP INDEX 語(yǔ)句來(lái)刪除索引。

DROP INDEX 的語(yǔ)法：

DROP INDEX index_name ON table_name;

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

使用 ALTER TABLE 語(yǔ)句刪除索引的語(yǔ)法如下：

ALTER TABLE table_name
DROP INDEX index_name;

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

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

DROP INDEX idx_age ON employees;

或使用 ALTER TABLE 語(yǔ)句：

ALTER TABLE employees
DROP INDEX idx_age;

這兩個(gè)命令都會(huì)從 employees 表中刪除名為 idx_age 的索引。

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

---

唯一索引

在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 語(yǔ)句來(lái)創(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: 指定要?jiǎng)?chuàng)建的唯一索引的名稱。索引名稱在表中必須是唯一的。
• table_name: 指定要在哪個(gè)表上創(chuàng)建唯一索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一個(gè)或多個(gè)列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

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

CREATE UNIQUE INDEX idx_email ON employees (email);

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

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

我們可以使用 ALTER TABLE 命令來(lái)創(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: 指定要?jiǎng)?chuàng)建的唯一索引的名稱。約束名稱在表中必須是唯一的。
• UNIQUE (column1, column2, ...): 指定要索引的表列名。你可以指定一個(gè)或多個(gè)列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。
• ASC和DESC（可選）: 用于指定索引的排序順序。默認(rèn)情況下，索引以升序（ASC）排序。

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

ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

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

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

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

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

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

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

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

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

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

在這個(gè)例子中，email 列被定義為唯一索引，因?yàn)樵谒暮竺婕由狭?UNIQUE 關(guān)鍵字。

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

---

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

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

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語(yǔ)句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為NULL。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語(yǔ)句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會(huì)出現(xiàn)多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出現(xiàn)多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語(yǔ)句指定了索引為 FULLTEXT ，用于全文索引。

以下實(shí)例為在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你還可以在 ALTER 命令中使用 DROP 子句來(lái)刪除索引。嘗試以下實(shí)例刪除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

---

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

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

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;

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

---

顯示索引信息

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

可以通過(guò)添加 \G 來(lái)格式化輸出信息。

SHOW INDEX 語(yǔ)句：:

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

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

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

網(wǎng)頁(yè)標(biāo)題：創(chuàng)新互聯(lián)MYSQL教程MySQL 索引
文章地址：http://m.jiaoqi3.com/article/dppogcs.html