共計 10557 個字符,預計需要花費 27 分鐘才能閱讀完成。
本篇文章為大家展示了 MySQL 索引底層數據結構是什么,內容簡明扼要并且容易理解,絕對能使你眼前一亮,通過這篇文章的詳細介紹希望你能有所收獲。
案例:
CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT COMMENT 姓名 , `age` int(11) NOT NULL DEFAULT 0 COMMENT 年齡 , `position` varchar(20) NOT NULL DEFAULT COMMENT 職位 , `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入職時間 , PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT= 員工記錄表 INSERT INTO employees(name,age,position,hire_time) VALUES(LiLei ,22, manager ,NOW());INSERT INTO employees(name,age,position,hire_time) VALUES(HanMeimei , 23, dev ,NOW());INSERT INTO employees(name,age,position,hire_time) VALUES(Lucy ,23, dev ,NOW());
分析以下幾條 sql 的索引使用情況:
SELECT * FROM employees WHERE name= LiLei SELECT * FROM employees WHERE name= LiLei AND age = 22 AND position = manager SELECT * FROM employees WHERE age = 22 AND position = manager SELECT * FROM employees WHERE name= LiLei AND age 22 AND position = manager SELECT * FROM employees WHERE name != LiLei
Mysql 的索引分析
MySQL 官方對索引的定義為:索引 (Index) 是幫助 MySQL 高效獲取數據的數據結構。索引的本質:索引是數據結構,而且是實現了高級查找算法的數據結構 索引一般以文件形式存儲在磁盤上,索引檢索需要磁盤 I / O 操作。
磁盤存取原理
尋道時間(速度慢,費時)。
旋轉時間(速度較快) 預讀:長度為頁的整倍數(主存和磁盤以頁為單位交換數據,一頁 4K)。
索引的結構
二叉樹;
紅黑樹;
HASH;
BTREE。
索引底層數據結構與算法
Hash 索引
如果是等值查詢,哈希索引明顯有絕對優勢,前提:鍵值唯一。
哈希索引沒辦法完成范圍查詢檢索 哈希索引也沒辦法利用索引完成排序,以及 like‘xxx%’這樣的部分模糊查詢 哈希索引也不支持多列聯合索引的 在有大量重復鍵值情況下,哈希索引的效率也最左前綴原則是極低的,因為存在哈希碰撞問題。
B-Tree
度(Degree)- 節點的數據存儲個數;
葉子節點具有相同的深度;
葉子節點的指針為空;
節點中的數據 key 從左到右遞增排列。
B+Tree
非葉子節點不存儲 data,只存儲 key,可以增大度;
葉子節點不存儲指針;
順序訪問指針,提高區間訪問的性能。
MyISAM 索引實現(非聚集)
MyISAM 索引文件和數據文件是分離的。
InnoDB 索引實現(聚集)
數據文件本身就是索引文件;
表數據文件本身就是按 B +Tree 組成的一個索引結構文件;
聚集索引 - 葉節點包含了完整的數據記錄;
為什么 InnoDB 表要求有主鍵,并且推薦使用整型的自增主鍵;
為什么非主鍵索引結構葉子節點存儲的是主鍵值?(一致性和節省存儲空間)。
聯合索引數據結構
EXPLAIN 執行計劃
使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 語句,從而知道 MySQL 是 如何處理你的 SQL 語句的,分析你的查詢語句或者表結構的性能瓶頸。
語法:Explain + SQL 語句
在 select 語句之前增加 explain 關鍵字,MySQL 會在查詢上設置一個標記,執行查詢時,會返回執行計劃的信息,而不是執行這條 SQL(如果 from 中包含子查詢,仍會執行該子查詢,將結果放入臨時表中)。
執行計劃作用
表的讀取順序;
數據讀取操作的操作類型;
哪些索引可以使用;
哪些索引被實際使用;
表之間的引用;
每張表有多少行被優化器查詢。
explain 案例
DROP TABLE IF EXISTS `actor`;CREATE TABLE `actor` (`id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1, a , 2017-12-22 15:27:18), (2, b , 2017-12-22 15:27:18), (3, c , 2017-12-22 15:27:18 DROP TABLE IF EXISTS `film`;CREATE TABLE `film` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film` (`id`, `name`) VALUES (3, film0),(1, film1),(2, film2 DROP TABLE IF EXISTS `film_actor`;CREATE TABLE `film_actor` ( `id` int(11) NOT NULL, `film_id` int(11) NOT NULL, `actor_id` int(11) NOT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_film_actor_id` (`film_id`,`actor_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
explain select * from actor;
在查詢中的每個表會輸出一行,如果有兩個表通過 join 連接查詢,那么會輸出兩行。表的意義相當廣泛:可以是子查詢、一個 union 結果等。
explain 兩個變種
explain extended
會在 explain 的基礎上額外提供一些查詢優化的信息。緊隨其后通過 show warnings 命令可以 得到優化后的查詢語句,從而看出優化器優化了什么。額外還有 filtered 列,是一個半分比的值,rows * filtered/100 可以估算出將要和 explain 中前一個表進行連接的行數(前一個表指 explain 中的 id 值比當前表 id 值小的表)。
explain extended select * from film where id = 1;
show warnings;
explain partitions
相比 explain 多了個 partitions 字段,如果查詢是基于分區表的話,會顯示查詢將訪問的分區。
explain 中的列
接下來我們將展示 explain 中每個列的信息。
1. id
id 列的編號是 select 的序列號,有幾個 select 就有幾個 id,并且 id 的順序是按 select 出現的順序增長的。MySQL 將 select 查詢分為簡單查詢 (SIMPLE) 和復雜查詢(PRIMARY)。復雜查詢分為三類:簡單子查詢、派生表(from 語句中的子查詢)、union 查詢。id 列越大執行優先級越高,id 相同則從上往下執行,id 為 NULL 最后執行。
1)簡單子查詢
explain select (select 1 from actor limit 1) from film;
2)from 子句中的子查詢
explain select id from (select id from film) as der;
這個查詢執行時有個臨時表別名為 der,外部 select 查詢引用了這個臨時表。
3)union 查詢
explain select 1 union all select 1;
union 結果總是放在一個匿名臨時表中,臨時表不在 SQL 中出現,因此它的 id 是 NULL。
2. select_type 列
select_type 表示對應行是簡單還是復雜的查詢,如果是復雜的查詢,又是上述三種復雜查詢中的哪一種。1)simple:簡單查詢。查詢不包含子查詢和 union。
explain select * from film where id = 2;
2)primary:復雜查詢中最外層的 select 3)subquery:包含在 select 中的子查詢(不在 from 子句中) 4)derived:包含在 from 子句中的子查詢。MySQL 會將結果存放在一個臨時表中,也稱為派生表(derived 的英文含義) 用這個例子來了解 primary、subquery 和 derived 類型。
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
5)union:在 union 中的第二個和隨后的 select 6)union result:從 union 臨時表檢索結果的 select 用這個例子來了解 union 和 union result 類型:
explain select 1 union all select 1;
3. table 列
這一列表示 explain 的一行正在訪問哪個表。當 from 子句中有子查詢時,table 列是 格式,表示當前查詢依賴 id=N 的查詢,于是先執行 id=N 的查詢。當有 union 時,UNION RESULT 的 table 列的值為,1 和 2 表示參與 union 的 select 行 id。
4. type 列
這一列表示關聯類型或訪問類型,即 MySQL 決定如何查找表中的行,查找數據行記錄的大概范圍。
完整的結果值從最優到最差分別為:system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
需要記憶的:system const eq_ref ref range index ALL 一般來說,得保證查詢達到 range 級別,最好達到 refNULL:mysql 能夠在優化階段分解查詢語句,在執行階段用不著再訪問表或索引。例如:在索引列中選取最小值,可以單獨查找索引來完成,不需要在執行時訪問表。
explain select min(id) from film;
const, system:mysql 能對查詢的某部分進行優化并將其轉化成一個常量(可以看 show warnings 的結果)。用于 primary key 或 unique key 的所有列與常數比較時,所以表最多有一個匹配行,讀取 1 次,速度比較快。system 是 const 的特例,表里只有一條元組匹配時為 system。
explain extended select * from (select * from film where id = 1) tmp;
show warnings;
eq_ref:primary key 或 unique key 索引的所有部分被連接使用,最多只會返回一條符合條件的記錄。這可能是在 const 之外最好的聯接類型了,簡單的 select 查詢不會出現這種 type。
explain select * from film_actor left join film on film_actor.film_id = film.id;
ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前綴,索引要和某個值相比較,可能會找到多個符合條件的行。
簡單 select 查詢,name 是普通索引(非唯一索引)
explain select * from film where name = film1
關聯表查詢,idxfilmactorid 是 filmid 和 actorid 的聯合索引,這里使用到了 filmactor 的左邊前綴 film_id 部分。
explain select film_id from film left join film_actor on film.id = film_actor.film_id;
range:范圍掃描通常出現在 in(), between , , , = 等操作中。使用一個索引來檢索給定范圍的行。
explain select * from actor where id
index:掃描全表索引,這通常比 ALL 快一些。
explain select * from film;
ALL:即全表掃描,意味著 mysql 需要從頭到尾去查找所需要的行。通常情況下這需要增加索引來進行優化了。
explain select * from actor;
5. possible_keys 列
這一列顯示查詢可能使用哪些索引來查找。explain 時可能出現 possible_keys 有列,而 key 顯示 NULL 的情況,這種情況是因為表中數據不多,mysql 認為索引對此查詢幫助不大,選擇了全表查詢。如果該列是 NULL,則沒有相關的索引。在這種情況下,可以通過檢查 where 子句看是否可以創造一個適當的索引來提高查詢性能,然后用 explain 查看效果。
6. key 列
這一列顯示 mysql 實際采用哪個索引來優化對該表的訪問。如果沒有使用索引,則該列是 NULL。如果想強制 mysql 使用或忽視 possible_keys 列中的索引,在查詢中使用 force index、ignore index。
explain select * from film ignore index(idx_name);
7. key_len 列
這一列顯示了 mysql 在索引里使用的字節數,通過這個值可以算出具體使用了索引中的哪些列。舉例來說,filmactor 的聯合索引 idxfilmactorid 由 filmid 和 actorid 兩個 int 列組成,并且每個 int 是 4 字節。通過結果中的 keylen= 4 可推斷出查詢使用了第一個列:filmid 列來執行索引查找。
mysql explain select * from film_actor where film_id = 2;
key_len 計算規則如下:
字符串;
char(n):n 字節長度;
varchar(n):2 字節存儲字符串長度,如果是 utf-8,則長度 3n + 2;
數值類型;
tinyint:1 字節;
smallint:2 字節;
int:4 字節;
bigint:8 字節;
時間類型;
date:3 字節;
timestamp:4 字節;
datetime:8 字節;
如果字段允許為 NULL,需要 1 字節記錄是否為 NULL。
索引最大長度是 768 字節,當字符串過長時,mysql 會做一個類似左前綴索引的處理,將前半部分的字符提取出來做索引。
8. ref 列
這一列顯示了在 key 列記錄的索引中,表查找值所用到的列或常量,常見的有:const(常量),字段名(例:film.id)。
9. rows 列
這一列是 mysql 估計要讀取并檢測的行數,注意這個不是結果集里的行數。
10. Extra 列
這一列展示的是額外信息。常見的重要值如下:Using index:查詢的列被索引覆蓋,并且 where 篩選條件是索引的前導列,是性能高的表現。一般是使用了覆蓋索引(索引包含了所有查詢的字段)。對于 innodb 來說,如果是輔助索引性能會有不少提高。
explain select film_id from film_actor where film_id = 1;
Using where:查詢的列未被索引覆蓋,where 篩選條件非索引的前導列。
explain select * from actor where name = a
Using where Using index:查詢的列被索引覆蓋,并且 where 篩選條件是索引列之一但是不是索引的前導列,意味著無法直接通過索引查找來查詢到符合條件的數據。
explain select film_id from film_actor where actor_id = 1;
NULL:查詢的列未被索引覆蓋,并且 where 篩選條件是索引的前導列,意味著用到了索引,但是部分字段未被索引覆蓋,必須通過“回表”來實現,不是純粹地用到了索引,也不是完全沒用到索引。
explain select * from film_actor where film_id = 1;
Using index condition:與 Using where 類似,查詢的列不完全被索引覆蓋,where 條件中是一個前導列的范圍;
explain select * from film_actor where film_id
Using temporary:mysql 需要創建一張臨時表來處理查詢。出現這種情況一般是要進行優化的,首先是想到用索引來優化。1. actor.name 沒有索引,此時創建了張臨時表來 distinct。
explain select distinct name from actor;
film.name 建立了 idx_name 索引,此時查詢時 extra 是 using index, 沒有用臨時表
explain select distinct name from film;
Using filesort:mysql 會對結果使用一個外部索引排序,而不是按索引次序從表里讀取行。此時 mysql 會根據聯接類型瀏覽所有符合條件的記錄,并保存排序關鍵字和行指針,然后排序關鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優化的。
1. actor.name 未創建索引,會瀏覽 actor 整個表,保存排序關鍵字 name 和對應的 id,然后排序 name 并檢索行記錄。
explain select * from actor order by name;
2. film.name 建立了 idx_name 索引, 此時查詢時 extra 是 using index
explain select * from film order by name;
索引最佳實踐
使用的表
CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT COMMENT 姓名 , `age` int(11) NOT NULL DEFAULT 0 COMMENT 年齡 , `position` varchar(20) NOT NULL DEFAULT COMMENT 職位 , `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入職時間 , PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT= 員工記錄表 INSERT INTO employees(name,age,position,hire_time) VALUES(LiLei ,22, manager ,NOW());INSERT INTO employees(name,age,position,hire_time) VALUES(HanMeimei , 23, dev ,NOW());INSERT INTO employees(name,age,position,hire_time) VALUES(Lucy ,23, dev ,NOW());
最佳實踐
1. 全值匹配
EXPLAIN SELECT * FROM employees WHERE name= LiLei
EXPLAIN SELECT * FROM employees WHERE name= LiLei AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= LiLei AND age = 22 AND position = manager
2. 最左前綴法則
如果索引了多列,要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
EXPLAIN SELECT * FROM employees WHERE age = 22 AND position = manager
EXPLAIN SELECT * FROM employees WHERE position = manager
EXPLAIN SELECT * FROM employees WHERE name = LiLei
3. 不要在索引列上做任何操作 (計算、函數、(自動 or 手動) 類型轉換),會導致索引失效而轉向全表掃描
EXPLAIN SELECT * FROM employees WHERE name = LiLei
EXPLAIN SELECT * FROM employees WHERE left(name,3) = LiLei
4. 存儲引擎不能使用索引中范圍條件右邊的列
EXPLAIN SELECT * FROM employees WHERE name= LiLei AND age = 22 AND position = manager
EXPLAIN SELECT * FROM employees WHERE name= LiLei AND age 22 AND position = manager
5. 盡量使用覆蓋索引(只訪問索引的查詢(索引列包含查詢列)),減少 select * 語句
EXPLAIN SELECT name,age FROM employees WHERE name= LiLei AND age = 23 AND position = manager
EXPLAIN SELECT * FROM employees WHERE name= LiLei AND age = 23 AND position = manager
6.mysql 在使用不等于 (!= 或者) 的時候無法使用索引會導致全表掃描
EXPLAIN SELECT * FROM employees WHERE name != LiLei
7.is null,is not null 也無法使用索引
EXPLAIN SELECT * FROM employees WHERE name is null;
8.like 以通配符開頭($abc…)mysql 索引失效會變成全表掃描操作
EXPLAIN SELECT * FROM employees WHERE name like %Lei
EXPLAIN SELECT * FROM employees WHERE name like Lei%
問題:解決 like % 字符串 % 索引不被使用的方法?a)使用覆蓋索引,查詢字段必須是建立覆蓋索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like %Lei%
b)當覆蓋索引指向的字段是 varchar(380)及 380 以上的字段時,覆蓋索引會失效!
9. 字符串不加單引號索引失效
EXPLAIN SELECT * FROM employees WHERE name = 1000
EXPLAIN SELECT * FROM employees WHERE name = 1000;
10. 少用 or, 用它連接時很多情況下索引會失效
EXPLAIN SELECT * FROM employees WHERE name = LiLei or name = HanMeimei
11.in 和 exsits 優化
原則:小表驅動大表,即小的數據集驅動大的數據集
in:當 B 表的數據集必須小于 A 表的數據集時,in 優于 exists
select * from A where id in (select id from B)
explainselect*fromfilmwhereidin(selectfilm_idfromfilm_actor);
exists:當 A 表的數據集小于 B 表的數據集時,exists 優于 in 將主查詢 A 的數據,放到子查詢 B 中做條件驗證,根據驗證結果 (true 或 false) 來決定主查詢的數據是否保留 select * from A where exists (select 1 from B where B.id = A.id) #A 表與 B 表的 ID 字段應建立索引
explain select * from film where exists (select 1 from film_actor where film_actor.film_id = film.id)
EXISTS (subquery)只返回 TRUE 或 FALSE, 因此子查詢中的 SELECT * 也可以是 SELECT 1 或 select X, 官方說法是實際執行時會忽略 SELECT 清單, 因此沒有區別。EXISTS 子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比。EXISTS 子查詢往往也可以用 JOIN 來代替,何種最優需要具體問題具體分析。
總結:
①MySQL 支持兩種方式的排序 filesort 和 index,Using index 是指 MySQL 掃描索引本身完成排序。index 效率高,filesort 效率低。
②order by 滿足兩種情況會使用 Using index。
order by 語句使用索引最左前列。
使用 where 子句與 order by 子句條件列組合滿足索引最左前列。
③盡量在索引列上完成排序,遵循索引建立 (索引創建的順序) 時的最左前綴法則。
④如果 order by 的條件不在索引列上,就會產生 Using filesort。⑤group by 與 order by 很類似,其實質是先排序后分組,遵照索引創建順序的最左前綴法則。注意 where 高于 having,能寫在 where 中的限定條件就不要去 having 限定了。
上述內容就是 MySQL 索引底層數據結構是什么,你們學到知識或技能了嗎?如果還想學到更多技能或者豐富自己的知識儲備,歡迎關注丸趣 TV 行業資訊頻道。