歡迎來到愛書不愛輸?shù)某绦蛟车牟┛? 本博客致力于知識(shí)分享，與更多的人進(jìn)行學(xué)習(xí)交流

本文收錄于SQL應(yīng)知應(yīng)會(huì)專欄,本專欄主要用于記錄對(duì)于數(shù)據(jù)庫(kù)的一些學(xué)習(xí)，有基礎(chǔ)也有進(jìn)階，有MySQL也有Oracle

前言

?今天繼續(xù)SQL的索引的第 3 篇文章，主要講到了聚簇索引與非聚簇索引、查看索引與刪除索引、索引方法，大家拭目以待吧！
??文章中提供了代碼和很具體的截圖，代碼是為了減輕大家學(xué)習(xí)的難度，同時(shí)用截圖可以更形象的讓大家去理解知識(shí)點(diǎn)想要表達(dá)的意思，希望大家跟著一起學(xué)起來
??希望文章的內(nèi)容對(duì)大家有所幫助，如果有什么不足的地方，大家可以在評(píng)論區(qū)或者私信我，感謝大家的支持
??那么，快拿出你的電腦，跟著文章一起學(xué)習(xí)起來吧

一、索引

1.簡(jiǎn)介

• 在索引（一）中描述了索引的優(yōu)點(diǎn)與缺點(diǎn)，可以幫助我們更好的理解為什么使用索引以及學(xué)習(xí)索引應(yīng)該思考哪些問題。

2.索引類型之邏輯分類

• 在索引（一）中主要講了索引按邏輯分類后，普通索引和復(fù)合索引的創(chuàng)建與應(yīng)用，其中復(fù)合索引的創(chuàng)建是重中之重，重點(diǎn)講述了六大應(yīng)用，對(duì)于“最左前綴”進(jìn)行了三種情況的詳細(xì)分析；
• 在索引（二）重點(diǎn)補(bǔ)充了邏輯分類中的唯一索引、主鍵索引以及全文索引，并針對(duì)唯一索引與復(fù)合索引、主鍵索引與復(fù)合索引進(jìn)行了對(duì)比分析，加強(qiáng)對(duì)各類索引的理解和應(yīng)用。

3.索引類型之物理分類

• 分為聚簇索引(主鍵索引和唯一索引)和非聚簇索引（有時(shí)也稱輔助索引或二級(jí)索引）
• 聚簇是為了提高某個(gè)屬性(或?qū)傩越M)的查詢速度，把這個(gè)或這些屬性(稱為聚簇碼)上具有相同值的元組集中存放在連續(xù)的物理塊。

3.1 聚簇索引 (clustered index)

• 不是單獨(dú)的一種索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。
• 這種存儲(chǔ)方式是依靠B+樹來實(shí)現(xiàn)的，根據(jù)表的主鍵構(gòu)造一棵B+樹且B+樹葉子節(jié)點(diǎn)存放的都是表的行記錄數(shù)據(jù)時(shí)，方可稱該主鍵索引為聚簇索引。
• 聚簇索引也可理解為將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，找到索引也就找到了數(shù)據(jù)。

3.2 非聚簇索引

• 數(shù)據(jù)和索引是分開的，B+樹葉子節(jié)點(diǎn)存放的不是數(shù)據(jù)表的行記錄。
• 雖然InnoDB(支持事務(wù))和MyISAM存儲(chǔ)引擎(不支持事務(wù))都默認(rèn)使用B+樹結(jié)構(gòu)存儲(chǔ)索引，但是只有InnoDB的主鍵索引才是聚簇索引，InnoDB中的輔助索引以及MyISAM使用的都是非聚簇索引。
• 每張表最多只能擁有一個(gè)聚簇索引(一個(gè)表只能有一個(gè)主鍵)

3.3 索引方法

3.3.1 B-TREE

• B-樹就是B樹，多路搜索樹，樹高一層意味著多一次的磁盤I/O，下圖是3階B樹

• 特征：
  • 關(guān)鍵字集合分布在整顆樹中；
  • 任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)結(jié)點(diǎn)中；
  • 搜索有可能在非葉子結(jié)點(diǎn)結(jié)束；
  • 其搜索性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找；
  • 自動(dòng)層次控制

3.3.2 B+TREE

• B+樹是B-樹的變體，也是一種多路搜索樹，見圖：

• 特征：
  • 所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中（稠密索引），且鏈表中的關(guān)鍵字恰好是有序的；
  • 不可能在非葉子結(jié)點(diǎn)命中；
  • 非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引（稀疏索引），葉子結(jié)點(diǎn)相當(dāng)于是存儲(chǔ)（關(guān)鍵字）數(shù)據(jù)的數(shù)據(jù)層；
  • 每一個(gè)葉子節(jié)點(diǎn)都包含指向下一個(gè)葉子節(jié)點(diǎn)的指針，從而方便葉子節(jié)點(diǎn)的范圍遍歷
  • 更適合文件索引系統(tǒng)

3.3.3 HASH

• 哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時(shí)不需要類似B+樹那樣從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)逐級(jí)查找，只需一次哈希算法即可立刻定位到相應(yīng)的位置，速度非常快。

• Hash索引僅僅能滿足=,IN和<=>查詢，不能使用范圍查詢,也不支持任何范圍查詢,例如WHERE price > 100

• 由于Hash索引比較的是進(jìn)行Hash運(yùn)算之后的Hash值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因?yàn)榻?jīng)過相應(yīng)的Hash算法處理之后的Hash值的大小關(guān)系，并不能保證和Hash運(yùn)算前完全一樣。
  

4.查看索引(SHOW INDEX)

4.1 查看索引的語(yǔ)法格式

SHOW INDEX FROM <表名> [ FROM <數(shù)據(jù)庫(kù)名>]

• <表名>：指定需要查看索引的數(shù)據(jù)表名
• <數(shù)據(jù)庫(kù)名>：指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫(kù)

4.2 示范

• emp表的索引情況
  
• 使用SQL查看

show index from emp;

• table : 表名
• Non_unique : 表示該索引是否是唯一索引
  • 若不是唯一索引，則該列的值為 1；
  • 若是唯一索引，則該列的值為 0
• Key_name : 索引的名稱
• Seq_in_index : 表示該列在索引中的位置
  • 如果索引是單列的，則該列的值為 1；
  • 如果索引是組合索引，則該列的值為每列在索引定義中的順序
• Column_name : 定義索引的列字段
• Collation ：表示列以何種順序存儲(chǔ)在索引中
  • 在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無分類
• Cardinality : 索引中唯一值數(shù)目的估計(jì)值
  • 基數(shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)， MySQL 使用該索引的機(jī)會(huì)就越大
• Sub_part : 表示列中被編入索引的字符的數(shù)量
  • 若列只是部分被編入索引，則該列的值為被編入索引的字符的數(shù)目；
  • 若整列被編入索引，則該列的值為 NULL
• Packed : 指示關(guān)鍵字如何被壓縮。若沒有被壓縮，值為 NULL
• Null : 顯示索引列中是否包含 NULL
• Index_type : 顯示索引使用的類型和方法（ BTREE、 FULLTEXT、 HASH、 RTREE）
• Comment : 顯示注釋

5. 刪除索引

5.1 刪除索引的語(yǔ)法格式

• 使用 DROP INDEX 語(yǔ)句

DROP INDEX <索引名> ON <表名>

<索引名>：要?jiǎng)h除的索引名
<表名>：指定該索引所在的表名

• 使用 ALTER TABLE 語(yǔ)句

ALTER TABLE <> + [句1/句2/句3]

句1：DROP PRIMARY KEY：表示刪除表中的主鍵索引
句2：DROP INDEX index_name：表示刪除名稱為 index_name 的索引
句3：DROP FOREIGN KEY fk_symbol：表示刪除外鍵

如果刪除的列是索引的組成部分，那么在刪除該列時(shí)，也會(huì)將該列從索引中刪除；如果組成索引的所有列都被刪除，那么整個(gè)索引將被刪除

5.2 示范

drop index index_empno on emp; # 索引名稱為index_empno
# 前面4.2中的示范里，索引的名稱是2，使用這條SQL語(yǔ)句刪除時(shí)，會(huì)報(bào)錯(cuò)，正常情況下，索引名稱都不會(huì)這樣取得，所以無需糾結(jié)這種情況

alter table emp drop index index_empno;

??感謝大家耐心的看完這篇文章，這篇文章是MySQL索引的第3篇文章
?如果大家覺著內(nèi)容還算可以，那么就關(guān)注一下愛書不愛輸?shù)某绦蛟?/strong>吧
??也可以加入我的社區(qū)一起學(xué)習(xí)呀
??各種專欄，精彩不斷

• SQL應(yīng)知應(yīng)會(huì)專欄，對(duì)于數(shù)據(jù)庫(kù)的一些學(xué)習(xí)，有基礎(chǔ)也有進(jìn)階，有MySQL也有Oracle
• UML應(yīng)知應(yīng)會(huì)專欄，對(duì)于UML的一些講解，應(yīng)有盡有
• … … 還有java的專欄、算法與數(shù)據(jù)結(jié)構(gòu)的專欄等其他專欄，快去我的主頁(yè)關(guān)注我吧