為什么要用索引
假設有一張表，表中有100萬條數(shù)據(jù)，這100萬條數(shù)據(jù)在硬盤上是存儲在數(shù)據(jù)頁上的，一頁數(shù)據(jù)大小為16k。存儲100萬條數(shù)據(jù)那么就需要數(shù)據(jù)頁，假設其中有一條數(shù)據(jù)是“id為7900”的，那么如果要查詢這條數(shù)據(jù)，其中SQL是SELECT * FROM 表名 WHERE id = 7900。在執(zhí)行這條SQL語句的時候，MySQL需要掃描全表來查詢id = 7900的記錄。
全表掃描就是從“數(shù)據(jù)頁1”開始，向后逐頁查詢。對于少量數(shù)據(jù)來說，查詢的速度會很快，但是，當隨著數(shù)據(jù)量的增加，性能會急劇下降。100萬條數(shù)據(jù)進行逐頁查詢的時間是無法被用戶所接受的。
索引是什么
索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結構。
是排好序的快速查找的數(shù)據(jù)結構。
索引就是類似于書的目錄。
數(shù)據(jù)庫在存儲數(shù)據(jù)本身以外，還有維護著一個滿足特定查找算法的數(shù)據(jù)結構，這些數(shù)據(jù)結構就以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結構的基礎上實現(xiàn)高級查找算法，這種數(shù)據(jù)結構就是索引。
索引的原理
索引的目的在于提高查詢效率，與我們查詢圖書所用的目錄是一個道理：先定位到章，然后定位到該章下的一個小節(jié)，然后找到頁數(shù)。相似的例子還有：查字典、查火車車次，飛機航班等。
本質上都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們就可以總是用同一種查找方式來鎖定數(shù)據(jù)。
索引類似于書的目錄，在一本書前面加上目錄，查找內容時就不必逐頁翻閱就能夠快速地找到所要查找的內容。借助索引，執(zhí)行查詢時就不必掃描整個表就能夠快速地找到所需要的數(shù)據(jù)。
優(yōu)點
索引提高了數(shù)據(jù)檢索的效率，降低了數(shù)據(jù)庫IO成本。
通過索引列對數(shù)據(jù)進行排序，降低了數(shù)據(jù)排序的成本，降低了CPU的消耗。
缺點
實際上，索引本質上也是一張表，這個表保存了主鍵與索引之間的字段，并指向實體表的記錄，所以索引列也是要占用磁盤的空間。
雖然索引大大提高了插敘的速度，但同時也會降低更新表數(shù)據(jù)的速度。例如：INSERT、UPDATE和DELETE，因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一次索引文件，每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息。
創(chuàng)建索引的原則
索引雖好，但也不能亂用。
什么情況下需要索引
- 主鍵自動建立唯一索引。
- 頻繁作為查詢條件的字段應該創(chuàng)建索引（WHERE 后面的語句）。
- 查詢中與其它表關聯(lián)的字段，外鍵關系建立索引。
- 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度分組中的字段。
什么情況下不需要索引
- 表記錄過少
- 經(jīng)常性的增刪改的表。雖然有提高了查詢的速度，但同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE，因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引的文件。（所以在遇到這種情況下，就會分表，分表就是將讀、寫的數(shù)據(jù)進行分離）
- WHERE條件里用不到的字段不會創(chuàng)建索引
- 數(shù)據(jù)重復且分布平均的表字段，因此應該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列表建立索引，某個數(shù)據(jù)列包含許多重復的內容，建立索引沒有太大的實際效果。
索引的分類
主鍵索引
就是設定主鍵后數(shù)據(jù)庫會自動建立索引，一個表只能有一個主鍵。
ALTER TABLE 表名 ADD PRIMARY KEY 表名（列名）；
刪除主鍵索引：
ALTER TABLE 表名 DROP PRIMARY KEY；
單值索引
就是一個索引只包含單個列，一個表可以有多個單列索引。
加這個索引肯定是比不加索引要快的。
創(chuàng)建單值索引：
CREATE INDEX 索引名 ON 表名（列名）；
刪除索引：
DROP INDEX 索引名；
唯一索引
索引列的值必須唯一，允許為null；
CREATE UNIQUE INDEX 索引名 ON 表名 （列名）;
刪除索引：
DROP INDEX 索引名 ON 表名;
組合索引（復合索引）
即一個索引包含多個列，在數(shù)據(jù)庫操作期間，復合索引比單值索引所需要的開銷是更小的（對于相同的多個列鍵索引）。
當表的行數(shù)遠大于索引列的數(shù)目時可以使用復合索引。
創(chuàng)建復合索引
CREATE INDEX 索引名 ON 表名（列1，列2，…）；
刪除索引
DROP INDEX 索引名 ON 表名;
組合索引最左前綴原則
例如表中有a，b，c 3列，為a，b兩列創(chuàng)建組合索引，那么在使用時需要滿足最左側索引原則。即在使用組合索引的列為條件的時候，是必須要出現(xiàn)最左側列作為條件，否則索引是不生效的。
例如：
SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; #索引生效SELECT * FROM 表名 WHERE b = ' ' AND b = ' ' ; #索引生效SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; # 索引生效SELECT * FORM 表名 WHERE b = ' ' AND c = ' ' ; # 索引不生效
全文索引(僅在MySQL8之后有)
是需要模糊查詢的，一般索引是無效的，這個時候就可以使用全文索引。
比如： WHERE name LIKE %J%，這樣查詢的時候，即使name列添加了索引，但也會索引失效的。所以是不建議使用的，在MySQL8當中就建議使用全文索引。
創(chuàng)建全文索引列：
CREATE FULLTEXT INDEX 索引名 ON 表名（列名） WITH TARSER ngram;
使用全文索引：
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST(‘輸入的參數(shù)’);
全文索引的插敘效率是比使用LIKE來查詢要快的，所以在MySQL8之后是建議使用全文索引的。
查找索引：
SHOW INDEX FROM 表名；
索引的數(shù)據(jù)結構
在MySQL數(shù)據(jù)庫中的InnoDB引擎是使用B+樹來進行數(shù)據(jù)存儲的。
B+樹由二叉查找樹、平衡二叉樹（AVLTree）和平衡多落查找樹（B-Tree）逐步優(yōu)化而來的。使其更適合實現(xiàn)外存索引結構。
B+數(shù)的特點：
- 它是排好序的，一個節(jié)點可以存儲多個數(shù)據(jù)。
- 非葉子節(jié)點是不存儲數(shù)據(jù)的，只存儲索引，是可以放更多的索引。
- 數(shù)據(jù)都是存放在在葉子節(jié)點中的。
- 所有的葉子節(jié)點之間都是有一個鏈指針的。
就是使用B+樹使得樹是橫向擴展的，讓樹的高度降低了。在葉子節(jié)點也可以存儲多個數(shù)據(jù)。
MySQL所以使用B+樹，因為索引是用來加快查詢的，而B+數(shù)通過對數(shù)據(jù)進行排序所以是可以提高查詢速度，然后通過一個節(jié)點可以存儲多個元素，從而可以使得B+樹的高度不會太高。并且葉子節(jié)點之間存在指針，可以很好的支持全表掃描，范圍查找等SQL語句。
聚簇索引和非聚簇索引
聚簇索引
就是找到了索引就找到了需要的數(shù)據(jù)，那么這個索引就是聚簇索引，所以主鍵就是聚簇索引。
非聚簇索引
索引的存儲和數(shù)據(jù)的存儲時分離的，也就是意味著找到了索引但沒有找到數(shù)據(jù)，需要根據(jù)索引上的值（主鍵）再次回表查詢，非聚簇索引也叫做輔助索引。
舉例：
先創(chuàng)建一個表，用來說明說明情況下是聚簇索引和非聚簇索引：
CREATE TABLE student (
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
`name` VARCHAR (50) COMMENT '學生姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT '學生年齡',
KEY `idx_name` (`name`)
)
創(chuàng)建的數(shù)據(jù)庫表中，主鍵id是一個索引，給age添加了索引。
① 直接根據(jù)主鍵查詢獲取所有字段數(shù)據(jù)，此時主鍵就是聚簇索引。因為主鍵對應的索引葉子節(jié)點存儲的就是id的所有字段值。
SELECT * FROM student WHERE id = 1;
② 根據(jù)name查詢所有信息，name本身就是一個唯一索引，但查詢的數(shù)據(jù)包括了所有的數(shù)據(jù)，所以當命中name索引的時候，該索引的節(jié)點的數(shù)據(jù)存儲的就是主鍵的ID，然后需要ID再查詢一次。
這就是非聚簇索引。
SELECT * FROM student WHERE name = “張三”;
索引就是再單獨的創(chuàng)建一張表，在這個表中查找數(shù)據(jù)，然后拿到ID在返回所有數(shù)據(jù)的表中查詢所有的數(shù)據(jù)。
③ 根據(jù)name查詢的時候，我們只查詢name的值，不查詢其他的信息。這種查詢就是命中name索引，直接返回name的值，因為所需要的數(shù)據(jù)就是索引，此時不需要會表查詢。這種場景就是非聚簇索引。
SELECT name FROM student WHERE name = “張三”；
拓展
在MySQL中InnoDB引擎的索引和文件是存放在一起的，找到索引就可以找到數(shù)據(jù)，是聚簇式設計。（默認是聚簇設計的，當你加上其他的索引，根據(jù)變化就成為了非聚簇式）
而MyISAM引擎采用的是非聚簇式設計，索引文件和數(shù)據(jù)文件不在同一個文件中。（不管怎么創(chuàng)建，它本質上就是非聚簇的設計）文章來源：http://www.zghlxwxcb.cn/news/detail-431134.html
因為在MyISAM當中，它是在MYD中存儲文件的數(shù)據(jù)；在MYI中存儲文件的索引；在sdi中存儲表的結構。
文章來源地址http://www.zghlxwxcb.cn/news/detail-431134.html
到了這里，關于MySQL數(shù)據(jù)庫中的索引（含SQL語句）的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！
