偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

面試官:什么是回表,什么是索引下推?

數(shù)據(jù)庫 其他數(shù)據(jù)庫
使用?MySQL?時(shí),我們經(jīng)常會(huì)聽到“回表”、“索引下推”這樣的概念,今天就來聊一聊什么是回表,什么是索引下推。

大家好,我是君哥。

使用 MySQL 時(shí),我們經(jīng)常會(huì)聽到“回表”、“索引下推”這樣的概念,今天就來聊一聊什么是回表,什么是索引下推。

一、回表

1.1 概念

我們看下面這個(gè) SQL:

CREATE TABLE`test_temp` (
`id`INT(11) NOTNULLDEFAULT'0',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(10) DEFAULTNULL,
  PRIMARY KEY (`id`),
KEY(`b`)
) ENGINE=INNODBDEFAULTCHARSET=utf8

我們創(chuàng)建一個(gè) test_temp 表,主鍵是 id,給字段 b 加了一個(gè)索引。插入 4 條數(shù)據(jù),SQL 如下:

INSERT INTO test_temp(100, 10, 50);
INSERT INTO test_temp(200, 20, 40);
INSERT INTO test_temp(300, 30, 30);
INSERT INTO test_temp(400, 40, 10);

test_temp 表會(huì)構(gòu)建 2 個(gè)索引,一個(gè)是主鍵索引,一個(gè)是字段 b 的普通索引。

一般主鍵索引被稱為聚集索引,普通索引被稱為非聚集索引。

我們執(zhí)行下面查詢 SQL:

select * from test_temp where b in(10, 20, 30 ,40);

這個(gè) SQL 語句的查詢過程如下圖:

圖片圖片

1.從索引 b 上查詢 10,查到主鍵 id 的值是 400,再用 400 這個(gè) id 去主鍵索引上取出 row4;

2.從索引 b 上查詢 20,沒有查到記錄,繼續(xù)下一條;

3.從索引 b 上查詢 30,查到主鍵 id 的值是 300,再用 300 這個(gè) id 去主鍵索引上取出 row3;

4.從索引 b 上查詢 40,查到主鍵 id 的值是 200,再用 200 這個(gè) id 去主鍵索引上取出 row2;

5.給客戶端返回結(jié)果集。

上面 1、3、5 回到主鍵索引搜索數(shù)據(jù)的過程,就叫回表。上面查詢回表 3 次。

1.2 缺點(diǎn)

回表有什么問題嗎?回表次數(shù)多了,可能會(huì)嚴(yán)重影響查詢效率。

1.導(dǎo)致磁盤 I/O 增加:每次回表讀取數(shù)據(jù)行,這些數(shù)據(jù)分散在磁盤各個(gè)地方,導(dǎo)致大量的磁盤 I/O。

2.導(dǎo)致緩存失效:回表的數(shù)據(jù)如果不在緩存行中,就需要從磁盤加載,新的數(shù)據(jù)可能會(huì)覆蓋已有的緩存,影響其他查詢。

1.3 措施

那有什么方法可以避免回表嗎?下面兩個(gè)方法可以避免:

1.覆蓋索引

上面的查詢中,如果 SQL 改成:

select b, id from test_temp where b in(10, 20, 30 ,40);

這樣就不用回表查詢了。如果需要查詢 b、a 兩個(gè)字段,可以創(chuàng)建 b、a 的覆蓋索引,這樣就可以從 b、a 這個(gè)覆蓋索引上查詢出結(jié)果。

2.只查詢必要字段

修改查詢范圍,不用的字段不查詢。如果查詢的字段不多,可以把查詢語句改成只查聯(lián)合索引包含的字段。如果查詢頻率高,又沒有覆蓋索引,可以加一個(gè)包含查詢字段的聯(lián)合索引。

二、索引下推

首先回顧一下 MySQL 的邏輯架構(gòu):

圖片圖片

Server 層是 MySQL 的核心服務(wù)層,這一次包括查詢解析、分析、優(yōu)化、緩存、以及所有內(nèi)置函數(shù)(例如,日期、時(shí)間、數(shù)學(xué)和加密函數(shù)),所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn),包括:存儲(chǔ)過程、觸發(fā)器、視圖等。

存儲(chǔ)引擎層負(fù)責(zé) MySQL 中數(shù)據(jù)的存儲(chǔ)和提取。

首先,我們創(chuàng)建一張表:

CREATE TABLE`test_temp` (
`id`INT(11) NOTNULLDEFAULT'0',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(10) DEFAULTNULL,
`c`VARCHAR(10) DEFAULTNULL,
`d`VARCHAR(10) DEFAULTNULL,
  PRIMARY KEY (`id`),
KEY`a_b`(`a`,`b`)
) ENGINE=INNODBDEFAULTCHARSET=utf8

插入一批數(shù)據(jù):

INSERT INTO test_temp VALUES(100, 10, 20, 2, 1);
INSERT INTO test_temp VALUES(200, 10, 40, 4, 2);
INSERT INTO test_temp VALUES(300, 10, 30, 3, 3);
INSERT INTO test_temp VALUES(400, 40, 10, 1, 4);

這時(shí)我們看一下下面這條 SQL 的執(zhí)行計(jì)劃:

EXPLAIN SELECT * FROM test_temp WHERE a > '10' AND b < '50';

我們看一下執(zhí)行計(jì)劃:

圖片圖片

上圖中的 Using index condition 就是使用了索引下推。

如果不使用索引下推,比如只對(duì) a 這個(gè)字段加了索引,那就會(huì)對(duì) a 這個(gè)字段篩選出來的 id,依次做回表查詢,查到結(jié)果后再對(duì) b 字段進(jìn)行過濾。

而使用了索引下推,SQL 執(zhí)行過程如下:

1.Server 層向存儲(chǔ)引擎查詢數(shù)據(jù);

2.存儲(chǔ)引擎根據(jù) a_b 聯(lián)合索引首先找到所有 a > '10' 的數(shù)據(jù),根據(jù)聯(lián)合索引中已經(jīng)存在的 b 字段對(duì)數(shù)據(jù)做過濾,找出符合條件 b < '50' 的數(shù)據(jù);

3.存儲(chǔ)引擎根據(jù) a_b 聯(lián)合索引找到所有符合條件的數(shù)據(jù)后,回表查詢,給 Server 層返回結(jié)果集。

可以看到,索引下推最大的優(yōu)勢(shì)就是在存儲(chǔ)引擎層,利用聯(lián)合索引的優(yōu)勢(shì)對(duì)查詢條件進(jìn)行了過濾,這樣可以減少回表查詢次數(shù),從而大大減少 I/O 次數(shù),提升查詢性能。


索引下推是在 MySQL 5.6 版本中才引入的,MySQL 5.6 以前版本沒有這個(gè)功能。

當(dāng)然使用索引下推也有一定限制:

1.索引下推主要適用于 eq_ref、range、ref、ref_or_null 這幾個(gè)場(chǎng)景;

2.InnoDB 和 MyISAM 存儲(chǔ)引擎都支持索引下推,MySQL 分區(qū)表也支持;

3.對(duì) InnoDB 存儲(chǔ)引擎來說,索引下推只適用于二級(jí)索引,主鍵索引(聚集索引)不支持,因?yàn)橹麈I索引存儲(chǔ)了數(shù)據(jù),不存在回表這一說;

4.語句中子查詢的條件不支持索引下推;

5.使用了存儲(chǔ)函數(shù)的 SQL,存儲(chǔ)函數(shù)中的條件不支持索引下推,因?yàn)榇鎯?chǔ)引擎無法調(diào)用存儲(chǔ)函數(shù)。

我們?cè)倏聪旅孢@個(gè)查詢語句(把條件 b 改成條件 c):

EXPLAIN SELECT * FROM test_temp WHERE a > '10' AND c < '50';

這個(gè)語句其實(shí)并不能使用聯(lián)合索引第二個(gè)字段在存儲(chǔ)引擎層做過濾,還是需要對(duì)每一條索引 a_b 上查詢到的 id 做回表查詢,但是執(zhí)行計(jì)劃里面卻有索引下推,這也是需要注意的一點(diǎn)。

圖片圖片

總結(jié)

本文介紹了 MySQL 的回表和索引下推,這兩個(gè)概念在 MySQL 中非常重要,希望對(duì)你的學(xué)習(xí)和面試有所幫助。


責(zé)任編輯:武曉燕 來源: 君哥聊技術(shù)
相關(guān)推薦

2024-05-24 09:28:22

2022-09-29 07:30:57

數(shù)據(jù)庫索引字段

2021-09-07 10:44:33

Java 注解開發(fā)

2024-03-25 13:02:00

MySQL索引主鍵

2024-02-22 15:36:23

Java內(nèi)存模型線程

2021-12-08 06:53:29

面試動(dòng)態(tài)代理

2023-12-06 09:10:28

JWT微服務(wù)

2021-02-19 10:02:57

HTTPSJava安全

2024-12-24 14:11:57

2023-12-20 14:35:37

Java虛擬線程

2021-04-19 18:56:58

大數(shù)字符串運(yùn)算

2025-04-28 07:10:46

聚簇非聚簇索引

2024-04-15 00:01:00

STWJava垃圾

2025-03-10 07:05:07

2021-05-12 08:20:53

開發(fā)

2022-01-13 14:31:56

MySQL數(shù)據(jù)庫回表

2022-01-05 09:55:26

asynawait前端

2021-08-24 08:05:41

泛型類型擦除Class

2024-01-11 08:12:20

重量級(jí)監(jiān)視器

2020-07-22 08:05:44

中間人攻擊
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)