目錄
- 一、索引的概念
- 二、索引的作用
- 三、索引的副作用
- 四、建立索引的原則依據
- 五、索引的分類和建立
- 5.1、普通索引，最基本的索引類型，沒有唯一性之類的限制
- 直接建立索引
- 修改表方式建立
- 建立表的時候指定索引
- 5.2、唯一索引
- 直接建立
- 修改表方式建立索引
- 5.3、主鍵索引
- 更改字段屬性
- 5.4、組合索引
- 5.5、全文索引
- 修改表方式指定全文索引
- 建立表的時候指定全文索引
- 六、檢視索引
- 5.1、普通索引，最基本的索引類型，沒有唯一性之類的限制
- 七、删除索引
- 7.1、直接删除索引
- 7.2、修改表方式删除索引
- 7.3、删除主鍵索引
- 索引是一個排序的清單，在這個清單中存儲着索引的值和包含這個值得資料所在行的實體位址（類似于C語音的連結清單通過指針指向資料記錄的記憶體位址）
- 使用索引後可以不用掃描全表來定位某行的資料，而是先通過索引表找到該行資料對應的實體位址然後通路響應的資料，是以能加快資料庫的查詢速度
- 索引就好比是一本輸的目錄，可以跟庫目錄中的頁碼快速找到所需的内容
- 索引是表中一列或者若幹列值排序的方法
- 建立索引的目的是加快對表中記錄的查找或排序
- 設定了合适的索引之後，資料庫利用各種快速定位技術，能夠大大加快查詢速度，這是建立索引的最主要原因
- 當表很大或查詢涉及到多個表時，使用索引可以成千上萬倍的提高查詢速度
- 可以降低資料庫的IO成本，并且索引還可以降低資料庫的排序成本
- 通過建立唯一性索引，可以保證資料表中每一行資料的唯一性
- 可以加快表與表之間的連接配接
- 再使用分組和排序時，可以大大減少分組和排序的時間
- 建立索引在搜尋和恢複資料庫中的資料時能顯著提高性能
- 索引需要占用額外的磁盤空間
- 對于mylsam引擎而言，索引檔案和資料檔案時分離的，索引檔案用于儲存資料記錄的位址，而innodb引擎的表資料檔案本身就是索引檔案
- 在插入和修改資料時要花費更多的時間，因為索引也要随之變動
- 索引雖可以提升資料庫查詢的速度，但并不是任何情況下都适合建立索引。因為索引本身會消耗系統資源，在有索引的情況下，資料庫會先進行索引查詢，然後定位到具體的資料行，如果索引使用不當，反而會增加資料庫的負擔
- 表的主鍵、外鍵必須有索引。因為主鍵具有唯一性，外鍵關聯的是子表的主鍵，查詢時可以快速定位
- 記錄數超過300行的表應該有索引。如果沒有索引，需要把表周遊一遍，會嚴重影響資料庫的性能
- 經常與其他表進行連接配接的表，在連接配接字段上應該建立索引
- 唯一性太差的字段不适合建立索引
- 更新太頻繁的字段不适合建立索引
- 經常出現在where語句中的字段，特别是大表的字段，應該建立索引
- 索引應該建在選擇性高的字段上
- 索引應該建立在小字段上，對于打的文本字段甚至超長字段，不要建立索引
create index 索引名 on 表名 （列明[（length）]）；
#（列名（length））：length是可選項，下同。如果忽略length的值，則使用整個列的值作為索引。如果指定，使用列的前length個字元來建立索引，這樣有利于減小索引檔案的大小。在不所示精确性的情況下，長度越短越好
alter table 表名 add index 索引名（列名）；
create table 表名 (字段1 資料類型，字段2 資料類型,index 索引名 (列名));
與普通索引類似，但差別是唯一索引列的每個值都唯一
唯一索引允許有空值（注意和主鍵不同）。如果是用組合索引建立，則列值的組合必須唯一
添加唯一鍵将自動建立唯一索引
create unique index 索引名 on 表名 (列名);
alter table 表名 add unique 索引名 (列名);
create table 表名 (字段1 資料類型，字段2 資料類型,unique 索引名 (列名));
#和第一種方式一樣，隻是變換個索引類型，就不再示範
是一種特殊的唯一索引，必須指定為“PRIMARY KEY”
一個表隻能有一個主鍵，不允許有空值。 添加主鍵将自動建立主鍵索引
如果我們在建立表時沒有設定不為空，可以用以下指令alter table 表名 modify 字段名稱 資料類型 not null;來修改為不為空值
alter table 表名 change 舊列名 新列名 資料類型 primary key;
alter table 表名 add primary key (列名);
create table 表名 (字段1 資料類型，字段2 資料類型,primary key (列名));
可以是單列上建立的索引，也可以是在多列上建立的索引
需要滿足最左原則，因為select語句的 where 條件是依次從左往右執行的，是以在使用 select 語句查詢時 where 條件使用的字段順序必須群組合索引中的排序一緻，否則索引将不會生效
create table 表名 (字段1 資料類型，字段2 資料類型,index 索引名 (列名1，列名2));
select * from 表名 where 列名1='...' and 列名2='...'
适合在進行模糊查詢的時候使用，可用于在一篇文章中檢索文本資訊
在 MySQL5.6 版本以前 fulltext 索引僅可用于 MyISAM 引擎，在 5.6 版本之後 innodb 引擎也支援 fulltext 索引
全文索引可以在 char、varchar 或者 text 類型的列上建立。每個表隻允許有一個全文索引
create fulltext index 索引名 on 表名 (列名);
select * from 表名 where match（列名） against（'查詢内容'）；
alter table 表名 add fulltext 索引名 (列名);
create table 表名 (字段1 資料類型，字段2 資料類型,fulltext 索引名 (列名));
show index from 表名;
show index from 表名\G； 豎向顯示表索引資訊
show keys from 表名;
show keys from 表名\G;
| 字段 | 說明 |
|---|---|
| Table | 表的名稱 |
| Non_unique | 如果索引不能包括重複詞，則為 0；如果可以，則為1 |
| Key_name | 索引的名稱 |
| Seq_in_index | 索引中的列序号，從1開始 |
| Column_name | 列名稱 |
| Collation | 列以什麼方式存儲在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（無分類） |
| Cardinality | 索引中唯一值數目的估計值 |
| Sub_part | 如果列隻是被部分地編入索引，則為被編入索引的字元的數目。如果整列被編入索引，則為 NULL |
| Packed | 訓示關鍵字如何被壓縮。如果沒有被壓縮，則為 NULL |
| Null | 如果列含有 NULL，則含有 YES。如果沒有，則該列含有 NO |
| Index_type | 用過的索引方法（BTREE, FULLTEXT, HASH, RTREE） |
| Comment | 備注 |
drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
alter table 表名 drop primary key;