提的最多的資料庫“索引”，先來簡單瞭解一下

Code綜藝圈發表於2022-03-25
資料庫索引

前言

現在的專案對於資料庫操作基本上都是使用封裝好的ORM框架，這樣開發效率相對來說有所提高。但由於框架的封裝，會自動生成SQL語句，這讓一些小夥伴對SQL產生了一種陌生感(基本不寫SQL)，導致排查業務執行緩慢問題時比較盲目；其實本質還是SQL，而對於SQL的優化，索引是否使用上是一個關鍵的點，所以這先來了解一下平時見過的那些索引分類，後續再來好好說說索引的使用。

正文

1. 索引概述

索引是輔助高效獲取資料的資料結構，目的就是為了提高查詢效率。

索引本身也會存在磁碟上，從儲存和表資料操作效率來說，一個表建立過多的索引也不是個好事。

2. 索引分類

2.1 按邏輯使用分

  • 主鍵索引：主鍵索引也是一種唯一索引，不能有空值，一個表只能有一個主鍵。

    建立索引

    建立表時建立

    CREATE TABLE tableName(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	PRIMARY KEY (ID)   
);

    修改表的形式新增

    ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);

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

    建立索引

    建立表時建立

    CREATE TABLE tableName(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	UNIQUE indexName (username)   
);

    修改表的形式新增

    ALTER table tableName ADD UNIQUE indexName(columnName)

    有表時直接建立

    CREATE UNIQUE INDEX indexName ON tableName(columnName)

  • 普通索引：基本的索引型別，沒有唯一性限制，允許有空值，一個表可以有多個普通索引；

    建立索引

    建立表時建立

    CREATE TABLE tableName(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	INDEX indexName (username)  
);

    修改表的形式新增

    ALTER table tableName ADD INDEX indexName(columnName)

    有表時直接建立

    CREATE INDEX indexName ON tableName (column_name)

  • 複合索引：一個索引可包含多列，一個表可以有多個複合索引，目的就是針對組合條件查詢的場景。

    建立索引的方式和普通索引基本一樣，只是可以指定多列。

    ALTER TABLE tableName ADD INDEX indexName(column_name1,column_name2,column_name3);

  • 全文索引：FULLTEXT索引，可以在varchar、char、text型別上建立，用作關鍵詞查詢等場景，但一般在關係型資料庫中使用的不多，都會使用類似於ES的搜尋引擎。

    建立索引

    建立表時建立

    CREATE TABLE tableName(
    id INT(10) PRIMARY KEY,
    username VARCHAR(10) NOT NULL,
    user_desc TEXT,
    FULLTEXT(user_desc)
)

    修改表的形式新增

    ALTER TABLE tableName ADD FULLTEXT INDEX indexName(column_name);

    有表時直接建立

    CREATE FULLTEXT INDEX indexName ON tableName (column_name)

    如果是中文，在建立全文索引時，需要指明解析外掛WITH PARSER ngram，否則查詢不出對應結果，如下：

    CREATE FULLTEXT INDEX indexName ON tableName (column_name) WITH PARSER ngram;

    建立之後就可以針對對應的欄位進行關鍵詞搜尋了，如下：

    # 針對column_name，如果匹配到有‘工作’兩字的資料都查出來
SELECT * FROM tableName WHERE MATCH(column_name) AGAINST('工作');
2.2 按儲存分

索引其實是一種資料結構，可以不同的形式進行儲存，所以可以將其進行如下分類：

  • Hash索引：採用Hash的形式進行儲存，針對於等值條件的查詢，效率很高，但比較耗記憶體，而在實際應用場景中，範圍條件查詢的場景比較多，所以Hash索引使用的不多。
  • BTree索引和B+ Tree索引：BTree和B+ Tree都是為了提升IO讀效率，目的是減少IO讀的次數，從而可以大大提升資料查詢效率，B+ Tree其實是對BTree的擴充套件，B+ Tree能儲存更多的資料，對葉子節點資料的儲存增加關聯關係，提升資料遍歷效率。所以在InnoDB建立的索引預設都是B+ Tree索引。
  • R-Tree索引：空間索引，R樹就是一棵用來儲存高維資料的平衡樹，可以用作地理資料儲存。比如檢視附近的共享單車位置資訊這種場景，但對於資料量大點的場景，效率不高，都會使用其他方案代替，比如Redis。

具體的儲存細節，暫時就不在這展開，關於資料結構和演算法系列的文章，之前也分享過一部分，後續還會持續更新，說到具體內容時，再來詳細說說如何在對應資料結構中運算元據。

2.3 聚簇索引和非聚簇索引

  • 聚簇索引(又稱聚類索引、簇集索引)：索引的順序和表資料儲存的物理順序一致，因為一個表的資料順序只有一種，所以一個表中只有一個聚簇索引。

    聚簇索引儲存的形式是索引與資料資訊存在一起，找到聚簇索引其實就找到了資料。

  • 非聚簇索引(又稱非聚類索引、非聚集索引)：索引的順序和儲存表資料的順序無關；

    非聚簇索引儲存的形式是索引和資料分開儲存，先是根據索引找到對應資料的實體地址，然後根據實體地址再去定位對應的資料資訊。

總結

關於索引先聊這麼多，雖然ORM幫我們省去了寫SQL的時間，但控制ORM生成高效的SQL語句是我們必須要做的，所以小夥伴們趕緊捲起來吧~~~，後面的文章還會繼續說說索引在實際場景中的應用、SQL如何才能匹配到索引、如何避免索引失效等，關注“Code綜藝圈”，和我一起學習吧。

相關文章