sql索引的原理

1. sql的索引是什麼意思用於什麼情況下

索引主要用來提升數據檢索速度,在數據量很大的時候很有用. 索引相當於圖書館的圖書目錄,你要找本書可以在圖書目錄上找到這本書在哪個書架第幾本,這樣明顯比到書架去找書要快得多,索引就是這個道理. 索引在資料庫優化當中起著非常重要的作用,像我的網站(http://www.99sc.cn)裡面的 表基本上都建有索引, 這樣可以使得我的網站數據在百萬千萬條數據的時候也不會很慢(只是現在數據很少,呵呵),如果沒有索引估計會很慘. 以下是網上的介紹,看看吧:

索引簡介

索引是表示數據的另一種方式，它提供的數據順序不同於數據在磁碟上的物理存儲順序。索引的特殊作用是在表內重新排列記錄的物理位置。索引可建立在數據表的一列上，或建立在表的幾列的組合上。

回想一下圖書館中，存放著一架一架的圖書。假設要找一本書，圖書管理員視必要一架一架的尋找，直到找到想要的那本書為止。這樣的查找肯定費時費力，那麼管理員可以將所有的圖書標題按字母分類建立索引卡片，按字母順序放在不同的辦公抽屜中，這樣一架一架查找圖書的過程就變成了在辦公桌前抽屜中查詢索引卡片了，很容易得到有關這本書的信息。

再進一步設想，圖書管理員很勤快，他不僅按照圖書標題字母分類，而且還按作者姓名和出版社分別建立了另外的索引卡片，這個過程將給借閱者在檢索圖書信息時以更大的靈活性。因此在一個數據表中是可以建立多個索引的。

在資料庫中，對無索引的表進行查詢一般稱為全表掃描。全表掃描是資料庫伺服器用來搜尋表的每一條記錄的過程，直到所有符合給定條件的記錄返回為止。這個操作可以比作在圖書館中查書，從第一個書架的第一本書開始，瀏覽每一本書，直到發現所要的書為止。為了進行高效查詢，可以在數據表上針對某一欄位建立索引，由於該索引包括了一個指向數據的指針，資料庫伺服器則只沿著索引排列的順序對僅有一列數據的索引進行讀取（只建立一個索引）直至索引指針指向相應的記錄上為止。由於索引只是按照一個欄位進行查找，而沒有對整表進行遍歷，因此一般說來索引查找比全表掃描的速度快。

那麼，是不是使用索引查詢一定比全表掃描的速度快呢？答案是否定的。如果查詢小型數據表（記錄很少）或是查詢大型數據表（記錄很多）的絕大部分數據，全表掃描更為實用。例如，查詢「性別」欄位，其值只能是「男或女」，在其上建立索引的意義就不大，甚至不允許在布爾型、大二進制型（備注型、圖像型等）上建立索引。

我的qq:505022441

2. MySQL索引機制(詳細+原理+解析)

MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

集一個索引包含多個列（最左前綴匹配原則）

索引列的值必須唯一，但允許有空值

全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值，全文索引可以在CHAR,VARCHAR,TEXT類型列上創建

設定主鍵後數據會自動建立索引，InnoDB為聚簇索引

即一個索引只包含單個列，一個表可以有多個單列索引

覆蓋索引是指一個查詢語句的執行只用從所有就能夠得到，不必從數據表中讀取，覆蓋索引不是索引樹，是一個結果，當一條查詢語句符合覆蓋索引條件時候，MySQL只需要通過索引就可以返回查詢所需要的數據，這樣避免了查到索引後的回表操作，減少了I/O效率

查看索引

列名解析：

刪除索引

查看：

刪除前:

刪除後：

普通的索引，沒有什麼介紹

查看:（注意和前綴索引Sub_part的區別）

當索引的列是unique的時候，會生成唯一索引，唯一索引關於null有下列兩種情況

SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個空值

MYSQL下的唯一索引的列，允許null值，並且允許多個空值

查看:

會建立兩個索引，一個非聚簇索引，一個是唯一索引

結果:

可以插入兩個空值（明人不說暗話，我喜歡MySQL）

一方面，它不會索引所有欄位所有字元，會減小索引樹的大小.

另外一方面，索引只是為了區別出值，對於某些列，可能前幾位區別很大，我們就可以使用前綴索引。

一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對於BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。

查看：

查看：

復合索引的最左前綴匹配原則 ：

對於復合索引，查詢在一定條件才會使用該索引

減少開銷。 建一個聯合索引(col1,col2,col3)，實際相當於建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁碟空間的開銷。對於大量數據的表，使用聯合索引會大大的減少開銷！

覆蓋索引。 對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那麼MySQL可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。

效率高。 索引列越多，通過索引篩選出的數據越少。有1000W條數據的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的數據，如果只有單值索引，那麼通過該索引能篩選出1000W10%=100w條數據，然後再回表從100w條數據中找到符合col2=2 and col3= 3的數據，然後再排序，再分頁；如果是聯合索引，通過索引篩選出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一個欄位，可以參考這篇博文
: https://zhuanlan.hu.com/p/88275060

我們先進行下面一個實驗看看InnoDB下的主鍵索引的一個現象。

查看:

我們插入進去的時候，數據的id都是亂序的，為什麼這里最後select查詢出來的結果都是進行了排序？

這是因為InnoDB索引底層實現的是B+tree，B+tree具有下列的特點：

所以上面的排序是為了使用B+tree的結構 ，B+tree為了范圍搜索，將主鍵按照從小到大排序後，拆分成節點。後續還有新的節點進入的時候，和B-tree相同的操作，會進行分裂。

一般來說，聚簇索引的B+tree都是三層

InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。

為什麼這里輔助索引葉子結點不直接存儲數據呢？

MYISAM只有非聚簇索引，索引最終指向的都是物理地址。

Q:既然有回表的存在，那麼聚簇索引的優勢在哪裡？

Q:主鍵索引作為聚簇索引需要注意什麼

在查詢語句中使用LIke關鍵字進行查詢時，如果匹配字元串的第一個字元為"%"，索引不會使用。如果「%」不是在第一位，索引就會使用

多列索引是在表的多個欄位上創建的索引，滿足最左前綴匹配原則，索引才會被使用

查詢語句只有Or關鍵字時候，如果OR前後的兩個條件都是索引，這這次查詢將會使用索引，否則Or前後有一個條件的列不是索引，那麼查詢中將不使用索引

3. SqlServer：索引是什麼，以及為什麼使用索引

收藏
問題反饋
索引
索引，使用索引可快速訪問資料庫表中的特定信息。索引是對資料庫表中一列或多列的值進行排序的一種結構。 在關系資料庫中，索引是一種與表有關的資料庫結構，它可以使對應於表的SQL語句執行得更快。索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。當表中有大量記錄時，若要對表進行查詢，第一種搜索信息方式是全表搜索，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作；第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過保存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。 索引是一個單獨的、物理的資料庫結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。 索引提供指向存儲在表的指定列中的數據值的指針，然後根據您指定的排序順序對這些指針排序。資料庫使用索引的方式與您使用書籍中的索引的方式很相似：它搜索索引以找到特定值，然後順指針找到包含該值的行。 在資料庫關系圖中，可以在選定表的「索引/鍵」屬性頁中創建、編輯或刪除每個索引類型。當保存索引所附加到的表，或保存該表所在的關系圖時，索引將保存在資料庫中。