MySQL數(shù)據(jù)庫的索引有什么特點(diǎn)

本篇內(nèi)容主要講解“MySQL數(shù)據(jù)庫的索引有什么特點(diǎn)”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL數(shù)據(jù)庫的索引有什么特點(diǎn)”吧!

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

索引的特點(diǎn)

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

一個(gè)表最多可有16個(gè)索引。最大索引長度是256個(gè)字節(jié)，盡管這可以在編譯MySQL時(shí)被改變。

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

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

雖然隨著 MySQL 的進(jìn)一步開發(fā)創(chuàng)建索引的約束將會(huì)越來越少，但現(xiàn)在還是存在一些約束的。下面的表根據(jù)索引的特性，給出了 ISAM 表和 MyISAM 表之間的差別：

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

索引的特點(diǎn) ISAM 表 MyISAM 表
NULL 值
BLOB 和 TEXT 列
每個(gè)表中的索引數(shù)
每個(gè)索引中的列數(shù)
最大索引行尺寸
不允許
不能索引
16
16
256 字節(jié) 允許
只能索引列的前綴
32
16
500 字節(jié)

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

索引有如下的幾種情況：

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

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

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

用Alter Table語句創(chuàng)建與刪除索引

為了給現(xiàn)有的表增加一個(gè)索引，可使用 ALTER TABLE 或 CREATE INDEX 語句。ALTER TABLE 最常用，因?yàn)榭捎盟鼇韯?chuàng)建普通索引、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 指出對哪些列進(jìn)行索引。一個(gè)(col1,col2,...)形式的列表創(chuàng)造一個(gè)多列索引。索引值有給定列的值串聯(lián)而成。如果索引由不止一列組成，各列名之間用逗號(hào)分隔。索引名 index_name 是可選的，因此可以不寫它，MySQL 將根據(jù)第一個(gè)索引列賦給它一個(gè)名稱。ALTER TABLE 允許在單個(gè)語句中指定多個(gè)表的更改，因此可以在同時(shí)創(chuàng)建多個(gè)索引。

同樣，也可以用ALTER TABLE語句產(chǎn)出列的索引：

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

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

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

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

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

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

mysql> SHOW INDEX FROM student;

其結(jié)果為：

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

| 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     |

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

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

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

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

再產(chǎn)看表中的索引，其語句和輸出為：

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

用CREATEDROP INDEX創(chuàng)建索引

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

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 語句創(chuàng)建 PRIMARY KEY 索引。

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

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

DROP INDEX index_name ON tbl_name

還是上一節(jié)的例子，由于CREATE INDEX不能創(chuàng)建PRIMARY索引，所以這里我們值創(chuàng)建一個(gè)多列索引：

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;

在創(chuàng)建表時(shí)指定索引

要想在發(fā)布 CREATE TABLE 語句時(shí)為新表創(chuàng)建索引，所使用的語法類似于 ALTER TABLE 語句的語法，但是應(yīng)該在您定義表列的語句部分指定索引創(chuàng)建子句，如下所示：

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 將為其選一個(gè)。另外，這里KEY時(shí)INDEX的一個(gè)別名，具有相同的意義。

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

CREATE TABLE tbl_name
(
  i INT NO

到此，相信大家對“MySQL數(shù)據(jù)庫的索引有什么特點(diǎn)”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！