MySQL之索引基礎
- 2019 年 11 月 6 日
- 筆記
innodb存儲引擎支持B+樹索引、全文索引以及哈希索引等常見的幾種索引。需要注意的是，Innodb存儲引擎支持的哈希索引是自適應的，Innodb存儲引擎會根據表的使用情況自動為表生成哈希索引。B+樹索引就是傳統意義上的索引，它的構造類似於二叉樹，根據key value鍵值對快速找到數據。
01
索引的優缺點
索引的優點很多，這裡簡單列舉幾條：
(1)使用唯一索引可以保證數據庫表中的每行數據的唯一性；
(2)可以大大加快數據的查詢速度；
(3)在實現數據的參考完整性方面，可以加速表和表之間的關聯；
(4)在使用分組和排序自居進行數據查找時，可以顯著減少查詢中分組和排序的時間
過多的索引還會帶來以下缺點：
(1)創建索引和維護索引需要耗費時間，並且隨着數據量的增加所耗費的時間也會增加；
(2)索引需要佔用磁盤空間，除了數據表佔用磁盤空間之外，每一個索引還要佔用一定的物理空間，如果有大量的索引，索引文件可能比數據文件還大;
(3)當對表中的數據進行增加、刪除和修改的時候，索引也要動態維護，降低了數據的維護速度。
02
索引的簡單分類
索引的分類可以簡單分為以下幾種：
(1)普通索引和唯一索引
普通索引是MySQL中基本索引類型，允許在定義索引的列中插入重複值和空值;
CREATE TABLE book ( bookid INT NOT NULL, bookname VARCHAR() NOT NULL, authors VARCHAR() NOT NULL, info VARCHAR() NULL, comment VARCHAR() NULL, year_publication YEAR NOT NULL, INDEX(year_publication) );
唯一索引指的是索引列的值必須唯一，但是允許有控制，如果是組合索引，則列值的組合必須為宜；
CREATE TABLE t1 ( id INT NOT NULL, name CHAR() NOT NULL, UNIQUE INDEX UniqIdx(id) );
主鍵索引是一種特殊的唯一索引，不允許有空值；
(2)單列索引和組合索引
單列索引指的是一個索引只包含一個列，一個表可以有多個單列索引；
CREATE TABLE t2 ( id INT NOT NULL, name CHAR() NULL, INDEX SingleIdx(name()) );
組合索引值得是在多個字段上組合創建索引，只有在查詢條件中使用了這些字段左邊的字段時，索引才會被使用。
CREATE TABLE t3 ( id INT NOT NULL, name CHAR() NOT NULL, age INT NOT NULL, info VARCHAR(), INDEX MultiIdx(id, name, age()) );
(3)全文索引
全文索引在定義索引的列上支持值的全文查找，允許在這些索引列中插入重複值和空值，全文索引可以在char，varchar，text類型的列上創建。MyISAM存儲引擎支持全文索引；
CREATE TABLE t4 ( id INT NOT NULL, name CHAR() NOT NULL, age INT NOT NULL, info VARCHAR(), FULLTEXT INDEX FullTxtIdx(info) ) ENGINE=MyISAM;
(4)聚集索引與非聚集索引
聚集（clustered）索引，也叫聚簇索引，類似字典中的拼音查詢，它的定義：數據行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引。MySQL里主鍵就是聚集索引。它的特點是非空，唯一。
非聚集（unclustered）索引，類似字典中的偏旁部首查詢。它的定義：該索引中索引的邏輯順序與磁盤上行的物理存儲順序不同，一個表中可以擁有多個非聚集索引。
關於這兩種索引，後續會再進行分析。
(5)空間索引是對空間數據類型的字段建立的索引，這個不經常使用，這裡不做贅述。
03
索引的設計原則
索引設計不合理或者缺少索引都會對數據庫的性能造成障礙，高效的索引對於獲得良好的性能十分重要，設計索引時考慮的準則大概有以下幾種：
(1)索引不是越多越好，過多的索引維護起來不方便，而且佔用磁盤空間；
(2)避免對經常更新的表進行過多的索引，而對於經常查詢的字段，需要創建索引，但是要避免添加不必要的字段；
(3)數據量小的表最好不用索引，由於數據量小，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果；
(4)在不同值較多的列上建立索引，如果一個列是性別屬性，那麼最好不要建立索引，因為該字段只有兩個不同值；
(5)當唯一性是某種數據本身的特徵時，指定唯一索引，使用唯一索引能夠確保定義的列的數據的完整性，從而提高查詢速度。
(6)在頻繁進行排序或者分組的列上簡歷索引，如果待排序的列有多個，可以在這些列上建立組合索引。
