當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

20200817-Mysql 底层数据结构及Explain详解

發布時間：2024/9/20 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 20200817-Mysql 底层数据结构及Explain详解小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

Mysql 底層數據結構
- 常見面試題
- 索引
  - 數據結構
    - 二叉樹
    - B-tree
    - B+tree
    - Hash
  - 存儲引擎
    - MyISAM存儲引擎索引實現
      - 非聚集索引
    - InnoDB存儲引擎索引實現
      - 輔助索引
      - 聯合索引
      - 如何支持千萬級數據
- 面試題答案
Explain詳解與索引最佳實踐
- Explain工具介紹
- Explain分析示例
- Explain列說明
  - id
  - select_type
    - simple
    - primary
    - subquery
    - derived
    - union
  - table
  - partitions
  - type
  - possible_keys
  - key
  - key_len
    - 計算規則
  - ref
  - rows
  - filtered
  - extra
    - 覆蓋索引
- 索引最佳實踐
- trace工具用法

Mysql 底層數據結構

常見面試題

為什么建議InnoDB表必須建主鍵，并且推薦使用整型的自增主鍵？
為什么非主鍵索引結構葉子節點存儲的是主鍵值？

索引

幫助MySQL高效獲取數據的排好序的數據結構

數據結構

數據結構演示：

Data Structure Visualization

二叉樹

B-tree

葉節點具有相同的深度，葉節點的指針為空
所有索引元素不重復
節點中的數據索引從左到右遞增排列

B+tree

非葉子節點不存儲data，只存儲索引(冗余)，可以放更多的索引
葉子節點包含所有索引字段
葉子節點用指針連接，提高區間訪問的性能

Hash

對索引的key進行一次hash計算就可以定位出數據存儲的位置
很多時候Hash索引要比B+ 樹索引更高效
僅能滿足 “=”，“IN”，不支持范圍查詢
hash沖突問題

存儲引擎

MyISAM存儲引擎索引實現

非聚集索引

索引文件和數據文件是分離的

InnoDB存儲引擎索引實現

聚集索引
表數據文件本身就是按B+Tree組織的一個索引結構文件
聚集索引-葉節點包含了完整的數據記錄

輔助索引

也叫二級索引：葉子節點中存儲主鍵值，每次查找數據時，根據索引找到葉子節點中的主鍵值。

MYISAM存儲引擎中，主鍵索引和輔助索引是同級別的，沒有主次之分

聯合索引

如何支持千萬級數據

?折疊源碼

1 2	#查看mysql文件頁大小（16K） SHOW?GLOBAL?STATUS?like?'Innodb_page_size';

為什么mysql頁文件默認16K？

假設我們一行數據大小為1K，那么一頁就能存16條數據，也就是一個葉子節點能存16條數據；再看非葉子節點，假設主鍵ID為bigint類型，那么長度為8B，指針大小在Innodb源碼中為6B，一共就是14B，那么一頁里就可以存儲16K/14=1170個(主鍵+指針)。

那么一顆高度為2的B+樹能存儲的數據為：1170*16=18720條，一顆高度為3的B+樹能存儲的數據為：1170*1170*16=21902400（千萬級條）。

面試題答案

為什么建議InnoDB表必須建主鍵，并且推薦使用整型的自增主鍵？

排好序

不用平衡樹結構

為什么非主鍵索引結構葉子節點存儲的是主鍵值？

節省空間

數據一致性

Explain詳解與索引最佳實踐

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列說明

EXPLAIN?SELECT?* FROM?film;

id

id列的編號是 select 的序列號
有幾個 select 就有幾個id
id的順序是按 select 出現的順序增長的。
id列越大執行優先級越高，id相同則從上往下執行，id為NULL最后執行

select_type

simple

簡單查詢。查詢不包含子查詢和union

primary

復雜查詢中最外層的 select

subquery

包含在 select 中的子查詢

derived

包含在 from 子句中的子查詢。

union

在 union 中的第二個和隨后的 select

?折疊源碼

#simple

explain?select?*?from?film?where?id = 2;

#primary、subquery、derived

set?session optimizer_switch='derived_merge=off'; #關閉mysql5.7新特性對衍生表的合并優化

explain?select?(select?1?from?actor?where?id = 1)?from?(select?*?from?film?where?id = 1) der;

set?session optimizer_switch='derived_merge=on';??? #還原默認配置

explain?select?1?union?all?select?1;

table

訪問的表。

當 from 子句中有子查詢時，table列是?格式，表示當前查詢依賴 id=N 的查詢，于是先執行 id=N 的查詢。
當有 union 時，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id。

partitions

如果查詢是基于分區表的話，會顯示查詢將訪問的分區。

type

訪問類型。依次從最優到最差分別為：system > const > eq_ref > ref > range > index > ALL

一般來說，得保證查詢達到range級別，最好達到ref。

NULL：mysql能夠在優化階段分解查詢語句，在執行階段用不著再訪問表或索引。

?折疊源碼

#?null

explain?select?min(id)?from?film;

# system const

explain extended?select?*?from?(select?*?from?film?where?id = 1) tmp;

show warnings;

# eq_ref

explain?select?*?from?film_actor?left?join?film?on?film_actor.film_id = film.id;

# ref

explain?select?*?from?film?where?name?=?'film1';

explain?select?film_id?from?film?left?join?film_actor?on?film.id = film_actor.film_id;

# range

explain?select?*?from?actor?where?id > 1;

#?index

explain?select?*?from?film;

#?all

explain?select?*?from?actor;

訪問類型	說明
null	mysql能夠在優化階段分解查詢語句，在執行階段用不著再訪問表或索引
eq_ref	primary key 或 unique key 索引的所有部分被連接使用，最多只會返回一條符合條件的記錄
system	system是const的特例，表里只有一條元組匹配時為system
ref	不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行。
all	全表掃描
const	對查詢的某部分進行優化并將其轉化成一個常量，用于 primary key 或 unique key 的所有列與常數比較時，所以表最多有一個匹配行，讀取1次，速度比較快。
index	掃描全表索引
range	范圍掃描通常出現在 in(), between ,> ,<, >= 等操作中。使用一個索引來檢索給定范圍的行。

possible_keys

可能使用索引。

possible_keys 有值，而 key 顯示 NULL 的情況，這種情況是因為表中數據不多，mysql認為索引對此查詢幫助不大，選擇了全表查詢。
如果該列是NULL，則沒有相關的索引。

key

實際采用索引。如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index、ignore index。

key_len

索引里使用的字節數。

?折疊源碼

1	explain?select??from?film_actor?where*?film_id = 2;

計算規則

類型	說明
char(n)	n字節長度
varchar(n)	utf8mb4=4,utf8=3,gbk=2,latin1=1。key_len=(表字符集長度) * 列長度 + 1(null) + 2(變長列)例：utf-8、不為null，則長度 3n + 2
tinyint	1
smallint	2
int	4
bigint	8
date	3
timestamp	4
datetime	8

索引最大長度是768字節

ref

key列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），字段名。

rows

估計要讀取并檢測的行數.

filtered

半分比的值，rows * filtered/100 可以估算出將要和 explain 中前一個表進行連接的行數

extra

?折疊源碼

# Using?index

explain?select?film_id?from?film_actor?where?film_id = 1;

# Using?where

explain?select?*?from?actor?where?name?=?'a';

# Using?index?condition

explain?select?*?from?film_actor?where?film_id > 1;

# Using?temporary

explain?select?distinct?name?from?actor;

explain?select?distinct?name?from?film; # film.name建立了idx_name索引，此時查詢時extra是using?index,沒有用臨時表

# Using filesort

explain?select?*?from?actor?order?by?name;

explain?select?*?from?film?order?by?name; # film.name建立了idx_name索引

#?Select?tables optimized away

explain?select?min(id)?from?film;

值	說明
Using index	使用覆蓋索引
Using where	使用 where 語句來處理結果，并且查詢的列未被索引覆蓋
Using index condition	詢的列不完全被索引覆蓋，where條件中是一個前導列的范圍
Using temporary	需要創建一張臨時表來處理查詢。出現這種情況一般是要進行優化的，首先是想到用索引來優化
Using filesort	將用外部排序而不是索引排序，數據較小時從內存排序，否則需要在磁盤完成排序。這種情況下一般也是要考慮使用索引來優化的。
Select tables optimized away	使用某些聚合函數（比如 max、min）來訪問存在索引的某個字段

覆蓋索引

select后面查詢的字段都可以從這個索引的樹中獲取，一般針對的是輔助索引，整個查詢結果只通過輔助索引就能拿到結果，不需要通過輔助索引樹找到主鍵，再通過主鍵去主鍵索引樹里獲取其它字段值。

索引最佳實踐

工作中遇到的99%SQL優化，這里都能給你解決方案 - 云+社區 - 騰訊云

?折疊源碼

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=0?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());

全值匹配

最左前綴法則

不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換）

存儲引擎不能使用索引中范圍條件右邊的列

盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少select?*語句

使用不等于（！=或者<>）的時候無法使用索引會導致全表掃描

is null,is not null 一般情況下也無法使用索引

like以通配符開頭（'$abc...'）mysql索引失效會變成全表掃描操作

字符串不加單引號索引失效

少用or或in

用它查詢時，mysql不一定使用索引，mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引，詳見范圍查詢優化

范圍查詢優化

沒走索引原因：mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。比如這個例子，可能是由于單次數據量查詢過大導致優化器最終選擇不走索引

優化方法：可以將大的范圍拆分成多個小范圍

?折疊源碼

# 全值匹配

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';

# 最左匹配

EXPLAIN?SELECT?*?FROM?employees?WHERE?age = 22?AND?position ='manager';

EXPLAIN?SELECT?*?FROM?employees?WHERE?position =?'manager';

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'LiLei';

# 索引列上不做操作

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'LiLei';

EXPLAIN?SELECT?*?FROM?employees?WHERE?left(name,3) =?'LiLei';

# 存儲引擎不能使用索引中范圍條件右邊的列

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';

# 使用覆蓋索引

EXPLAIN?SELECT?name,age?FROM?employees ;

EXPLAIN?SELECT?*?FROM?employees ;

# 使用不等于（！=或者<>）的時候無法使用索引

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?!=?'LiLei';

#?is?null,is?not?null?一般情況下也無法使用索引

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?is?null;

#?like以通配符開頭（'$abc...'）mysql索引失效

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?like?'%Lei';

EXPLAIN?SELECT?name,age,position?FROM?employees?WHERE?name?like?'%Lei%'; # 使用覆蓋索引

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?like?'Lei%';

# 字符串不加單引號

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'1000';

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?= 1000;

# 少用?in?or

EXPLAIN?SELECT?*?FROM?employees?WHERE?name?=?'LiLei'?or?name?=?'HanMeimei';

# 范圍查詢優化

ALTER?TABLE?`employees`?ADD?INDEX?`idx_age` (`age`) USING BTREE ;

explain?select?*?from?employees?where?age >=1?and?age <=2000;

explain?select?*?from?employees?where?age >=1?and?age <=1000;

explain?select?*?from?employees?where?age >=1001?and?age <=2000;

trace工具用法

trace工具用法.md

總結

以上是生活随笔為你收集整理的20200817-Mysql 底层数据结构及Explain详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：怎么买股票
下一篇：三种编程命名规则：驼峰命名法（壹）