一、索引的概念
- 索引是一個排序的串列，在這個串列中存盤著索引的值和包含這個值的資料所在行的物理地址（類似于C語言的鏈表通過指標指向資料記錄的記憶體地址），
- 使用索引后可以不用掃描全表來定位某行的資料，而是先通過索引表找到該行資料對應的物理地址然后訪問相應的資料，因此能加快資料庫的查詢速度，
- 索引就好比是一本書的目錄，可以根據目錄中的頁碼快速找到所需的內容，
- 索引是表中一列或者若干列值排序的方法，
- 建立索引的目的是加快對表中記錄的查找或排序，
二、索引的作用
- 設定了合適的索引之后，資料庫利用各種快速定位技術，能夠大大加快查詢速度，這是創建索引的最主要的原因
- 當表很大或查詢涉及到多個表時，使用索引可以成千上萬倍地提高查詢速度，
- 可以降低資料庫的IO成本，并且索引還可以降低資料庫的排序成本，
- 通過創建唯一性索引，可以保證資料表中每一行資料的唯一性，
- 可以加快表與表之間的連接，
- 在使用分組和排序時，可大大減少分組和排序的時間，
三、索引的缺點
- 索引需要占用額外的磁盤空間， 對于 MyISAM 引擎而言，索引檔案和資料檔案是分離的，索引檔案用于保存資料記錄的地址， 而 InnoDB 引擎的表資料檔案本身就是索引檔案，
- 在插入和修改資料時要花費更多的時間，因為索引也要隨之變動，
四、創建索引的原則依據
索引隨可以提升資料庫查詢的速度，但并不是任何情況下都適合創建索引，因為索引本身會消耗系統資源，在有索引的情況下，資料庫會先進行索引查詢，然后定位到具體的資料行，如果索引使用不當，反而會增加資料庫的負擔，
- 表的主鍵、外鍵必須有索引，因為主鍵具有唯一性，外鍵關聯的是子表的主鍵，查詢時可以快速定位，
- 記錄數超過300行的表應該有索引，如果沒有索引，需要把表遍歷一遍，會嚴重影響資料庫的性能，
- 經常與其他表進行連接的表，在連接欄位上應該建立索引，
- 唯一性太差的欄位不適合建立索引，
- 更新太頻繁地欄位不適合創建索引，
- 經常出現在 where 子句中的欄位，特別是大表的欄位，應該建立索引，
- 在經常進行 GROUP BY、OPDER BY 的欄位上建立索引
- 索引應該建在選擇性高的欄位上，
- 索引應該建在小欄位上，對于大的文本欄位甚至超長欄位，不要建索引，
五、索引的分類和創建
5.1普通索引
最基本的索引型別，沒有唯一性之類的限制
（1）直接創建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
(列名(length))：length是可選項，下同，如果忽略 length 的值，則使用整個列的值作為索引，如果指定使用列前的 length 個字符來創建索引，這樣有利于減小索引檔案的大小，
索引名建議以“_index”結尾，
MySQL表中key的含義
- 如果鍵是PRI，則列是主鍵或多列主鍵中的列之一，
- 如果鍵是UNI，則該列是唯一索引的第一列，(唯一索引允許多個空值，但可以通過檢查Null欄位來判斷該列是否允許空，)
- 如果鍵為MUL，則該列是非唯一索引的第一列，其中允許在列中多次出現給定值，
（2）修改表方式創建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
（3）創建表的時候指定索引
CREATE TABLE 表名 ( 欄位1 資料型別,欄位2 資料型別[,...],INDEX 索引名 (列名));
5.2唯一索引
與普通索引類似，但區別是唯一索引列的每個值都唯一，唯一索引允許有空值（注意和主鍵不同），如果是用組合索引創建，則列值的組合必須唯一，添加唯一鍵將自動創建唯一索引
（1）直接創建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
（2）修改表方式創建
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
（3）創建表的時候指定
CREATE TABLE 表名 (欄位1 資料型別,欄位2 資料型別[,...],UNIQUE 索引名 (列名));
5.3主鍵索引
是一種特殊的唯一索引，必須指定為“PRIMARY KEY”，一個表只能有一個主鍵，不允許有空值， 添加主鍵將自動創建主鍵索引，
（1）創建表的時候指定
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));
（2）修改表方式創建
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
5.4組合索引（單列索引與多列索引）
可以是單列上創建的索引，也可以是在多列上創建的索引，需要滿足最左原則，因為 select 陳述句的 where 條件是依次從左往右執行的，所以在使用 select 陳述句查詢時 where 條件使用的欄位順序必須和組合索引中的排序一致，否則索引將不會生效，
CREATE TABLE 表名 (列名1 資料型別,列名2 資料型別,列名3 資料型別,INDEX 索引名 (列名1,列名2,列名3));
select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';
5.5全文索引（FULLTEXT）
適合在進行模糊查詢的時候使用，可用于在一篇文章中檢索文本資訊，
在 MySQL5.6 版本以前，FULLTEXT 索引僅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引，
全文索引可以在 CHAR、VARCHAR 或者 TEXT 型別的列上創建，每個表只允許有一個全文索引，
（1）直接創建索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
（2）修改表方式創建
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
（3）創建表的時候指定索引
CREATE TABLE 表名 (欄位1 資料型別[,...],FULLTEXT 索引名 (列名));
資料型別可以為 CHAR、VARCHAR 或者 TEXT
（4）使用全文索引查詢
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查詢內容');
六、查看索引
show index from 表名;
show keys from 表名;
各欄位的含義如下：
- 欄位 含義
- 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： 備注
七、洗掉索引
7.1直接洗掉索引
DROP INDEX 索引名 ON 表名;
7.2修改表方式洗掉索引
ALTER TABLE 表名 DROP INDEX 索引名;
7.3洗掉主鍵索引
ALTER TABLE 表名 DROP PRIMARY KEY;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/538238.html
標籤：其他
上一篇：處理器基礎知識