MySQL系列：索引基本操作（4）

libingql發表於2017-02-28
MySql索引

1. 索引簡介

　　索引是一種特殊的資料庫結構，可以用來快速查詢資料中的特定記錄。

　　MySQL中索引包括：普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等。

1.1 索引定義

　　索引由資料庫表中一列或多列組合而成，作用是提高對錶中資料的查詢速度。

　　索引是建立在表是的，對資料表中一列或多列的值進行排序的一種結構。

　　MySQL索引包含兩種儲存型別：BTREE索引和HASH索引。InnoDB和MyISAM儲存引擎支援BTREE索引，MEMORY儲存引擎支援HASH索引和BTREE索引，預設為HASH索引。

　　索引的優點：可以提高檢索資料的速度。

　　索引的缺點：建立和維護索引需要耗費時間，耗費的時間隨資料量增加而增加；

　　　　　　　　索引需要佔用物理空間，每一個索引要佔一定的物理空間；

　　　　　　　　增加、刪除和修改資料時，要動態維護索引，降低資料維護速度。

　　索引可以提高查詢的速度，但會影響插入記錄的速度。向有索引的表中插入記錄時，資料庫系統會按照索引進行排序。

　　插入大量記錄時，最好的辦法是先刪除表中的索引，再插入資料，插入完成後，再建立索引。

1.2 索引分類

　　MySQL中索引包括：普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等。

　　1>. 普通索引

　　在建立普通索引時，不附加任何限制條件。

　　2>. 唯一性索引

　　使用UNIQUE設定唯一性索引。在建立唯一性索引時，限制該索引的值必須是唯一的。

　　唯一性索引，可以更快地確定某條記錄。

　　主鍵是一種特殊的唯一性索引。

　　3>. 全文索引

　　使用FULLTEXT設定索引為全文索引。全文索引只能建立在CHAR、VARCHAR或TEXT型別的欄位上。

　　查詢資料量較大的字串型別的欄位時，使用全文索引可以提高查詢速度。

　　只有MyISAM儲存引擎支援全文索引。

　　4>. 單列索引

　　

1.3 索引設計原則

　　（1）索引並非越多越好，一個表中如有大量的索引，佔用磁碟空間，影響INSERT、UPDATE、DELETE等語句效能。

　　（2）避免對經常更新的表進行過多的索引，索引中的列儘可能少。

　　（3）資料量小的表最好不要使用索引。

　　（4）在條件表示式中經常用到的不同值較多的列上建立索引，在不同值較少的列上不要建立索引。如：性別欄位只有“男”和“女”兩個不同值，無須建立索引。

　　（5）當唯一性是某種資料本身的特徵時，指定唯一索引。

　　（6）在頻繁排序或分組的列上建立索引，如果待排序的列有多個，可以建立組合索引。

2. 建立索引

2.1 建立表的時候建立索引

　　建立表時建立索引的基本語法格式：

CREATE TABLE table_name [col_name data_type]
[UNIQUE] [INDEX | KEY] [index_name] (col_name[length]) [ASC | DESC]

　　其中，INDEX與KEY為同義詞，兩者作用相同，用來指定建立索引。

　　（1）建立普通索引

mysql> CREATE TABLE product
    -> (
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> product_no VARCHAR(255) NOT NULL DEFAULT '',
    -> product_name VARCHAR(255) NOT NULL DEFAULT '',
    -> KEY idx_product_no(product_no)
    -> );
mysql> SHOW CREATE TABLE product;
+---------+----------------------------------------------------+
| Table   | Create Table                                       |
+---------+----------------------------------------------------+
| product | CREATE TABLE `product` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_no` varchar(255) NOT NULL DEFAULT '',
  `product_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_product_no` (`product_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------+

　　（2）建立唯一索引

mysql> CREATE TABLE product
    -> (
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> product_no VARCHAR(255) NOT NULL DEFAULT '',
    -> product_name VARCHAR(255) NOT NULL DEFAULT '',
    -> UNIQUE KEY idx_product_no(product_no)
    -> );

　　（3）建立組合索引

mysql> CREATE TABLE product
    -> (
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> product_no VARCHAR(255) NOT NULL DEFAULT '',
    -> product_name VARCHAR(255) NOT NULL DEFAULT '',
    -> KEY idx_product_no_name(product_no, product_name)
    -> );

　　組合索引遵從最左字首：從索引最左邊的列進行匹配。

2.2 檢視錶上的索引

　　語法格式：

SHOW INDEX FROM table_name
mysql> SHOW INDEX FROM product;

　　其中，主要引數：

　　　　Table：建立索引的表

　　　　Non_unique：索引非唯一，1表示非唯一索引，0表示唯一索引

　　　　Key_name：索引名稱

　　　　Seq_in_index：該欄位在索引中的位置，單列索引該值為1，組合索引為每個欄位在索引定義中的順序

　　　　Column_name：定義索引的列欄位

2.3 在已存在的表上建立索引

　　（1）使用ALTER TABLE建立索引

ALTER TABLE table_name ADD [UNIQUE] [INDEX | KEY] [index_name] (col_name[length]) [ASC | DESC]
mysql> ALTER TABLE product ADD INDEX idx_product_no(product_no ASC);

　　（2）使用CREATE INDEX建立索引

CREATE [UNIQUE] [INDEX | KEY] [index_name] ON table_name(col_name[length]) [ASC | DESC]

2.4 刪除索引

　　（1）使用ALTER TABLE刪除索引

ALTER TABLE table_name DROP INDEX index_name;
mysql> ALTER TABLE product DROP INDEX idx_product_no;

　　（2）使用DROP INDEX語句刪除索引

mysql> DROP INDEX index_name on table_name;
mysql> DROP INDEX idx_product_no on product;

相關文章