MySQL數據庫的索引有什么特點

本篇內容主要講解“MySQL數據庫的索引有什么特點”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL數據庫的索引有什么特點”吧!

索引是加速表內容訪問的主要手段，特別對涉及多個表的連接的查詢更是如此。這是優化中的一個重要內容，我們要了解為什么需要索引，索引如何工作以及怎樣利用它們來優化查詢。本節中，我們將介紹索引的特點，以及創建和刪除索引的語法。

索引的特點

所有的MySQL列類型能被索引。在相關的列上的使用索引是改進SELECT操作性能的最好方法。

一個表最多可有16個索引。最大索引長度是256個字節，盡管這可以在編譯MySQL時被改變。

對于CHAR和VARCHAR列，你可以索引列的前綴。這更快并且比索引整個列需要較少的磁盤空間。對于BLOB和TEXT列，你必須索引列的前綴，你不能索引列的全部。

MySQL能在多個列上創建索引。一個索引可以由最多15個列組成。（在CHAR和VARCHAR列上，你也可以使用列的前綴作為一個索引的部分）。

雖然隨著 MySQL 的進一步開發創建索引的約束將會越來越少，但現在還是存在一些約束的。下面的表根據索引的特性，給出了 ISAM 表和 MyISAM 表之間的差別：

表2-1  通道信息特征字對照表

索引的特點 ISAM 表 MyISAM 表
NULL 值
BLOB 和 TEXT 列
每個表中的索引數
每個索引中的列數
最大索引行尺寸
不允許
不能索引
16
16
256 字節 允許
只能索引列的前綴
32
16
500 字節

從此表中可以看到，對于 ISAM 表來說，其索引列必須定義為 NOT NULL，并且不能對 BLOB 和 TEXT 列進行索引。MyISAM 表類型去掉了這些限制，而且減緩了其他的一些限制。兩種表類型的索引特性的差異表明，根據所使用的 MySQL 版本的不同，有可能對某些列不能進行索引。例如，如果使用3.23 版以前的版本，則不能對包含 NULL 值的列進行索引。

索引有如下的幾種情況：

INDEX索引：通常意義的索引，某些情況下KEY是它的一個同義詞。索引的列可以包括重復的值。

UNIQUE索引：唯一索引，保證了列不包含重復的值，對于多列唯一索引，它保證值的組合不重復。

PRIMARY KEY索引：也UNIQUE索引非常類似。事實上，PRIMARY KEY索引僅是一個具有PRIMARY名稱的UNIQUE索引。這表示一個表只能包含一個PRIMARY KEY。

用Alter Table語句創建與刪除索引

為了給現有的表增加一個索引，可使用 ALTER TABLE 或 CREATE INDEX 語句。ALTER TABLE 最常用，因為可用它來創建普通索引、UNIQUE 索引或 PRIMARY KEY 索引，如：

ALTER TABLE tbl_name ADD INDEX index_name  (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name  (column_list)
ALTER TABLE tbl_name ADD PRIMARY KEY index_name  (column_list)

其中 tbl_name 是要增加索引的表名，而 column_list 指出對哪些列進行索引。一個(col1,col2,...)形式的列表創造一個多列索引。索引值有給定列的值串聯而成。如果索引由不止一列組成，各列名之間用逗號分隔。索引名 index_name 是可選的，因此可以不寫它，MySQL 將根據第一個索引列賦給它一個名稱。ALTER TABLE 允許在單個語句中指定多個表的更改，因此可以在同時創建多個索引。

同樣，也可以用ALTER TABLE語句產出列的索引：

ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP PRIMARY KEY

注意上面第一條語句可以用來刪除各種類型的索引，而第三條語句只在刪除 PRIMARY KEY 索引時使用；在此情形中，不需要索引名，因為一個表只可能具有一個這樣的索引。如果沒有明確地創建作為 PRIMARY KEY 的索引，但該表具有一個或多個 UNIQUE 索引，則 MySQL 將刪除這些 UNIQUE 索引中的第一個。

如果從表中刪除了列，則索引可能會受到影響。如果所刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。

例如，對于上面所使用的student為例，你可能想為之創建這樣的索引，以加速表的檢索速度：

> ALTER TABLE student
-> ADD PRIMARY KEY(id),
-> ADD INDEX mark(english,Chinese,history);

這個例子，既包括PRIMARY索引，也包括多列索引。記住，使用PRIMARY索引的列，必須是一個具有NOT NULL屬性的列，如果你愿意產看創建的索引的情況，可以使用SHOW INDEX語句：

mysql> SHOW INDEX FROM student;

其結果為：

+---------+------------+----------+--------------+-------------+-

| Table   | Non_unique | Key_name | Seq_in_index | Column_name |

+---------+------------+----------+--------------+-------------+-

| student |          0 | PRIMARY  |            1 | id          |

| student |          1 | mark     |            1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

+---------+------------+----------+--------------+-------------+-

由于列數太多，上表并沒有包括所有的輸出，讀者可以試著自己查看。

再使用ALTER TABLE語句刪除索引，刪除索引需要知道索引的名字，你可以通過SHOW INDEX語句得到：

mysql> ALTER TABLE student DROP PRIMARY KEY,
    -> DROP INDEX mark;

再產看表中的索引，其語句和輸出為：

mysql> SHOW INDEX FROM student;
Empty set (0.01 sec)

用CREATEDROP INDEX創建索引

還可以用CREATE INDEX語句來創建索引.CREATE INDEX 是在 MySQL 3.23版中引入的，但如果使用3.23 版以前的版本，可利用 ALTER TABLE 語句創建索引（MySQL 通常在內部將 CREATE INDEX 映射到 ALTER TABLE）。該語句創建索引的語法如下：

CREATE UNIQUE INDEX index_name ON tbl_name (column_list)
CREATE INDEX index_name ON tbl_name (column_list)

tbl_name、index_name 和 column_list 具有與 ALTER TABLE 語句中相同的含義。這里索引名不可選。很明顯，CREATE INDEX 可對表增加普通索引或 UNIQUE 索引，不能用 CREATE INDEX 語句創建 PRIMARY KEY 索引。

可利用 DROP INDEX語句來刪除索引。類似于 CREATE INDEX 語句，DROP INDEX 通常在內部作為一條 ALTER TABLE 語句處理，并且DROP INDEX是在 MySQL 3.22 中引入的。

刪除索引語句的語法如下：

DROP INDEX index_name ON tbl_name

還是上一節的例子，由于CREATE INDEX不能創建PRIMARY索引，所以這里我們值創建一個多列索引：

mysql> CREATE INDEX mark ON student(english,chinese,history);

同樣的檢查student表，可知：

mysql> SHOW INDEX FROM student;

+---------+------------+----------+--------------+-------------+

| Table   | Non_unique | Key_name | Seq_in_index | Column_name |

+---------+------------+----------+--------------+-------------+

| student |          1 | mark     |            1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

+---------+------------+----------+--------------+-------------+

然后使用下面的語句刪除索引：

mysql> DROP INDEX mark ON student;

在創建表時指定索引

要想在發布 CREATE TABLE 語句時為新表創建索引，所使用的語法類似于 ALTER TABLE 語句的語法，但是應該在您定義表列的語句部分指定索引創建子句，如下所示：

CREATE TABLE tbl_name
(
…
INDEX index_name (column_list),
KEY index_name (column_list),
UNIQUE index_name (column_list),
PRIMARY KEY index_name (column_list),
…
)

與ALTER TABLE 一樣，索引名對于 INDEX 和 UNIQUE 都是可選的，如果未給出，MySQL 將為其選一個。另外，這里KEY時INDEX的一個別名，具有相同的意義。

有一種特殊情形：可在列定義之后增加 PRIMARY KEY 創建一個單列的PRIMARY KEY 索引，如下所示：

CREATE TABLE tbl_name
(
  i INT NO

到此，相信大家對“MySQL數據庫的索引有什么特點”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！