程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MYSQL入門知識 >> mysql索引種類和操作

mysql索引種類和操作

編輯：MYSQL入門知識

部分來源：

mysql索引總結----mysql 索引類型以及創建該來源，部分命令運行失敗，故在本篇文章中進行了修改。所有命令，都已經進行過運行測試。
mysql 添加索引 mysql 如何創建索引

索引的概念

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表裡所有記錄的引用指針。更通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度。
假設要從10萬條查詢中查詢某個數據，在沒有索引的情況下，數據庫會遍歷全部10萬條數據後選擇符合條件的；而有了相應的索引之後，數據庫會直接在索引中查找符合條件的選項。

索引分類及命令

以數據庫data_index，表格test_index，字段name為例

普通索引
最基本的索引，它沒有任何限制，索引類型為Normal,索引方法為BTREE。

直接創建普通索引
CREATE INDEX index_name ON test_index(name);
修改表結構的方式添加普通索引
ALTER TABLE test_index ADD INDEX index_name(name);
創建表的時候同時創建普通索引
CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title)
);
刪除索引
DROP INDEX index_name ON table_name;

唯一索引
與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。
如果是組合索引，則列值的組合必須唯一，創建方法和普通索引類似,只是添加關鍵字UNIQUE。
索引類型為Unique,索引方法為BTREE。

直接創建唯一索引 
CREATE UNIQUE INDEX index_name ON test_index(name);
修改表結構的方式添加唯一索引 
ALTER TABLE test_index ADD UNIQUE INDEX index_name(name);
創建表的時候同時創建唯一索引
CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE INDEX index_name (title)
);
刪除索引
DROP INDEX index_name ON table_name;

全文索引（FULLTEXT）
MySQL從3.23.23版開始支持全文索引和全文檢索，FULLTEXT索引僅可用於 MyISAM 表；
他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創建，或是隨後使用ALTER TABLE 或CREATE INDEX被添加。
對於較大的數據集，將你的資料輸入一個沒有FULLTEXT索引的表中，然後創建索引，其速度比把資料輸入現有FULLTEXT索引的速度更為快。
不過切記對於大容量的數據表，生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法。
索引類型為Full Text,索引方法為空。
```
直接創建全文索引
CREATE FULLTEXT INDEX index_name ON table_name_full(title);
修改表結構的方式添加全文索引 
ALTER TABLE table_name_full ADD FULLTEXT INDEX index_name(title);
創建表的時候同時創建全文索引
CREATE TABLE `table_name_full` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`),
FULLTEXT INDEX index_name (title)
);
刪除索引
DROP INDEX index_name ON table_name;
```
單列索引、多列索引
多個單列索引與單個多列索引的查詢效果不同
因為執行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。

組合索引（最左前綴）
平時用的SQL查詢語句一般都有比較多的限制條件，所以為了進一步搾取MySQL的效率，就要考慮建立組合索引。
現在對表格test_index刪除所有索引，並且添加組合索引

ALTER TABLE test_index ADD INDEX index_name_desp(name,desp);

建立這樣的組合索引，其實是相當於分別建立了下面兩組組合索引：
name,desp
name
為什麼沒有desp這樣的組合索引呢？這是因為MySQL組合索引“最左前綴”的結果。簡單的理解就是只從最左面的開始組合。並不是只要包含這兩列的查詢都會用到該組合索引。
示例如下：

mysql語句：
select * from test_index where name = "cuiyf1465";
分析結果：
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test_index      ref index_name_desp index_name_desp 768 const   1   100 
其中，key值為index_name_desp 表示使用到了索引

mysql語句：
select * from test_index where desp = "handsome092";
分析結果：
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test_index      ALL                 96903   10  Using where
其中，key值為空 表示未使用到索引

mysql語句：
select * from test_index where name = "cuiyf034" and desp = "handsome092";
分析結果：
id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test_index      const   index_name_desp index_name_desp 1536    const,const 1   100 
其中，key值為index_name_desp 表示使用到了索引

注意事項：

索引不會包含有NULL值的列
只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那麼這一列對於此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL。
like語句操作
一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。
不要在列上進行運算
例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，
因此我們可以改成：select * from users where adddate<’2007-01-01′。