天天看點
傳回

MySQL進階篇(02)：索引體系劃分，B-Tree結構說明

本文源碼：GitHub·點這裡 || GitEE·點這裡

一、索引簡介

1、基本概念

首先要明确索引是什麼：索引是一種資料結構，資料結構是計算機存儲、組織資料的方式，是指互相之間存在一種或多種特定關系的資料元素的集合，例如：連結清單，堆棧，隊列，二叉樹等等。

其次要清楚索引的作用：索引可以使存儲引擎快速找到資料記錄，這是最基本的作用，索引是對查詢速度最關鍵的影響，良好的索引設計可以使查詢的效率有質的飛越。

索引的使用：如果查詢語句使用所有，MySQL會在索引的資料結構上查詢，如果查詢到，就傳回包含該索引的資料行。

2、索引的優點

  • 唯一或者主鍵索引,保證列資料的唯一性
  • 減少資料掃描量，快速查詢資料；
  • 資料有序的索引，可以将随機IO變成順序IO；
  • 有效的索引查詢，可以避免排序和臨時表；

3、索引分類

索引的種類非常多，如何分類取決多個場景和不同的角度，常見的劃分如下：

  • 産生作用：主鍵索引，普通索引，非空索引，全文索引；
  • 覆寫字段：單列索引，組合索引；
  • 資料結構：B-Tree索引，哈希索引，R-Tree索引；

注意：索引的實作是在存儲引擎層面，相同的索引在不同的存儲引擎中，其實作方式可能都是不一樣的。

二、索引用法詳解

1、不同索引特點

普通索引

基本的索引，沒有任何使用限制，主要用來加速資料查詢。适合經常出現在查詢條件或排序條件中的資料列。

主鍵索引

特殊的唯一索引，不允許有空值，在建表的時候指定主鍵，就會建立主鍵索引，MySQL中最核心的索引，大量的業務資料都是基于主鍵查詢。

唯一索引

普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須是唯一性的。

全文索引

用于全文搜尋，通過建立全文索引，基于分詞的查詢模式，可以極大的提升檢索效率。

組合索引

建立的索引覆寫兩個或者兩個以上的列，适應組合查詢的場景，也常用于要素驗證的業務，例如判斷使用者身份ID，手機号，郵箱，是否為同一個使用者。

2、管理索引文法

基礎使用者表

CREATE TABLE user_base (
	id INT (11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
	user_name VARCHAR (20) NOT NULL COMMENT '使用者名',
	phone VARCHAR (20) NOT NULL COMMENT '手機号',
	email VARCHAR (32) DEFAULT NULL COMMENT '郵箱',
	card_id VARCHAR (32) DEFAULT NULL COMMENT '身份編号',
	create_time datetime DEFAULT NULL COMMENT '建立時間',
	state INT (1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '使用者基礎表';

建立單列索引

CREATE INDEX card_id_index ON user_base(card_id);

修改添加索引

ALTER TABLE user_base ADD INDEX state_index(state) ;

建立組合索引

CREATE INDEX bind_index ON user_base(phone,card_id);

删除索引

DROP INDEX card_id_index ON user_base ;

修改索引

MySQL不支援真正修改索引的文法規範，可以通過删除舊索引，添加新索引的方式進行操作。

3、查詢索引

分析MySQL查詢，多數情況下用來分析執行語句的SQL中是否使用索引，是否産生臨時表等性能相關問題。

基礎用法

EXPLAIN SELECT * FROM user_base WHERE id='1';

參數說明

  • id：相同，按table列由上至下順序執行，不同，如果是子查詢，id的序号會遞增，id的值越大優先級越高，越先被執行；
  • select_type：表示查詢的類型，主要是用于差別普通查詢、聯合查詢、子查詢等的複雜查詢； 
simple：簡單select查詢，查詢中不包含子查詢或者
primary：查詢中若包含複雜的子部分，最外層查詢則被标記為primary
subquery：select或where中包含子查詢
derived：from中包含的子查詢被标記為derived衍生，mysql會遞歸執行這些子查詢，且生成臨時表
union：第二個select出現在union後，标記為union
union-result：從union表擷取結果的select
  • table：指目前執行計劃中的資料表；
  • type：說明的是查詢使用了哪種類型，下面從好到差排序； 
system-const：對查詢的某部分進行優化并轉換成一個常量時，會使用該類型
eq_ref：常見于主鍵或唯一索引掃描，表中隻有一條記錄與之比對
ref：非唯一性索引掃描，傳回比對某個單獨值的所有行
index：周遊索引結構，索引檔案通常比資料檔案小
all：周遊全表進行查詢
  • possible_keys：在查詢中可能使用到的索引；
  • key：在查詢中實際使用到的索引；
  • key_len：查詢中索引字段的最大可能長度，在不損失精确性的情況下，長度越短越好；
  • ref：表示本行被操作的對象的參照對象，可能是一個常量用const表示，也可能是其他表的key指向的對象；
  • rows：預估找到符合要求的記錄所需要掃描的行數，掃描越少越好；
  • extra：執行計劃中，一些十分重要的資訊； 
Using-Filesort：查詢使用檔案排序，最差的執行計劃
Using-Temporary：臨時表儲存中間結果，比檔案排序稍微強點
Using-Index：查詢操作中使用了覆寫索引
Using-Where：表明使用了where過濾條件
Using-Join-Buffer:表明使用了連接配接緩存
Impossible-Where：表示where條件false,不能過濾元素
Distinct：優化distinct找到第一比對的資料後即停止找同樣值的動作
Select-Tables-Optimized-Away：不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化

三、B-Tree索引結構

1、B-Tree索引簡介

MySQL官方比較推薦的索引結構類型，在實際的資料庫開發中，基于MySQL中的表結構，大部分使用的都是B-Three索引結構，即二叉樹的結構。可以加快資料的通路速度，存儲引擎不再需要進行全表掃描來擷取資料，資料分布在各個索引節點上，B-Tree索引結構如圖：

MySQL進階篇(02)：索引體系劃分，B-Tree結構說明

該結構是典型的二叉樹結構，特點：資料值按照順序存儲的，每個葉子節點到根部的距離是相同的，注意這裡描述的是索引結構圖。

實際存儲結構上，資料順序存儲，每個節點包含索引值，索引指向的資料行的值，指向子頁的指針，指向葉子頁的指針，這樣才能把索引和資料結構組織起來，結構如圖：

MySQL進階篇(02)：索引體系劃分，B-Tree結構說明

這樣完整描述B-Tree索引的資料特點，基于樹搜尋提升效率，減少掃描資料，資料被順序的組織起來，按照索引值順序排列。

2、搜尋規則

索引的根本作用，減少掃描的資料量，提升查詢效率，基于B-Tree索引的結構的查詢規則基本如下：

  • 查詢從索引的根節點開始，逐漸搜尋；
  • 根節點的槽中存放指向子節點的指針，指向下層；
  • 根據節點頁的值和查詢值比較，判斷是否符合條件；
  • 不斷執行上述邏輯，直到查詢完成；

注意：必須要強調一點，查詢必須是在執行索引的基礎上，才是該邏輯，正常的開發中多分析一下查詢語句，有時候可能隻是自己感覺查詢索引是執行的，實際可能是失效的。

3、索引查詢失效

好的索引設計十分重要，但是查詢的時候很可能因為觸發各種索引失效機制，導緻SQL語句不執行索引搜尋，嚴重損失性能，是以基于業務下資料查詢特點，設計相對好用的索引結構，是十分關鍵的，這裡涉及很多場景問題，後續再詳細記錄。

四、索引導緻的問題

索引有時候并不是最好的解決方式，當資料量龐大的時候，索引也會占據龐大的存儲空間，這裡提供一個業務測試場景，僅供參數：單表三個字元類型字段，兩個字段使用索引結構，存儲資料在700W量級，在A和B兩個資料庫，A資料庫有索引結構，B資料庫沒有索引，A庫占用的空間是B庫的1.6倍，寫入千萬資料的速度也比B資料庫慢9分鐘。

這裡隻想說明一點：索引雖然好，使用妥當才能發揮作用。

五、源代碼位址 

GitHub·位址
https://github.com/cicadasmile/mysql-data-base
GitEE·位址
https://gitee.com/cicadasmile/mysql-data-base
MySQL進階篇(02)：索引體系劃分，B-Tree結構說明

推薦閱讀：MySQL系列

序号 文章标題
01 MySQL基礎：經典實用查詢案例，總結整理
02 MySQL基礎：從五個次元出發，審視表結構設計
03 MySQL基礎：系統和自定義函數總結，觸發器使用詳解
04 MySQL基礎：存儲過程和視圖，用法和特性詳解
05 MySQL基礎：邏輯架構圖解和InnoDB存儲引擎詳解
06 MySQL基礎：事務管理，鎖機制案例詳解
07 MySQL基礎：使用者和權限管理，日志體系簡介
08 MySQL進階：基于多個次元，分析伺服器性能
B-Tree索引 索引 mysql
上一篇: MySQL進階篇(01)：基于多個次元，分析伺服器性能
下一篇: MySQL基礎篇(07)：使用者和權限管理，日志體系簡介

繼續閱讀