快捷導(dǎo)航

MySQL回表產(chǎn)生的原因和場(chǎng)景

更新時(shí)間：2023年11月28日 09:43:10 作者：黑夜開發(fā)者

在MySQL數(shù)據(jù)庫中,回表（Look Up）指的是在進(jìn)行索引查詢時(shí),首先通過索引定位到對(duì)應(yīng)頁,然后再根據(jù)行的物理地址找到所需的數(shù)據(jù)行,本文給大家介紹了MySQL回表是什么以及哪些情況下會(huì)回表,需要的朋友可以參考下

一、什么是MySQL的回表？

在MySQL數(shù)據(jù)庫中，回表（Look Up）指的是在進(jìn)行索引查詢時(shí)，首先通過索引定位到對(duì)應(yīng)頁，然后再根據(jù)行的物理地址找到所需的數(shù)據(jù)行。換句話說，回表是指根據(jù)索引查詢到的主鍵值再去訪問主鍵索引，從而獲取完整的數(shù)據(jù)記錄。

二、什么情況下會(huì)觸發(fā)回表？

MySQL的回表操作通常在以下情況下會(huì)發(fā)生：

2.1 索引不Cover所有需要查詢的字段

當(dāng)查詢語句中需要返回的列不在索引列上時(shí)，即使通過索引定位了相關(guān)行，仍然需要回表獲取其他列的值。

2.2 使用了非聚簇索引

非聚簇索引（Secondary Index）只包含了索引列的副本以及指向?qū)?yīng)主鍵的引用，查詢需要通過回表才能獲取完整的行數(shù)據(jù)。

2.3 使用了覆蓋索引但超過了最大索引長(zhǎng)度

在MySQL的InnoDB存儲(chǔ)引擎中，每個(gè)索引項(xiàng)的最大長(zhǎng)度是767字節(jié)，如果查詢需要返回的字段長(zhǎng)度超過了該限制，同樣會(huì)觸發(fā)回表操作。

需要注意的是，回表操作主要發(fā)生在讀取操作（SELECT）中，寫入操作（INSERT、UPDATE、DELETE）一般不會(huì)觸發(fā)回表。

三、哪些情況下不會(huì)觸發(fā)回表？

在某些特殊情況下，MySQL的回表操作可以被避免：

3.1 覆蓋索引

如果查詢的字段都在某個(gè)索引上，并且沒有超過最大索引長(zhǎng)度限制，MySQL可以直接從索引中獲取所需數(shù)據(jù)，而無需回表。

3.2 使用聚簇索引

InnoDB存儲(chǔ)引擎的主鍵索引是聚簇索引，它包含了整個(gè)行的數(shù)據(jù)。當(dāng)查詢條件使用了主鍵或者通過主鍵查詢時(shí)，MySQL可以直接從主鍵索引中獲取所有需要的數(shù)據(jù)，無需回表。

四、回表操作的問題和場(chǎng)景

回表操作雖然提供了更全面的數(shù)據(jù)信息，但也帶來了一些問題和局限性。

4.1 性能問題

回表操作通常需要訪問兩次索引，增加了IO開銷和CPU消耗，對(duì)查詢性能有一定的影響。特別是在高并發(fā)、大數(shù)據(jù)量的情況下，回表可能成為性能瓶頸。

4.2 數(shù)據(jù)一致性

由于回表操作是基于物理地址來獲取數(shù)據(jù)，如果在回表過程中發(fā)生了數(shù)據(jù)修改（如DELETE、UPDATE），則可能會(huì)讀取到不一致或錯(cuò)誤的數(shù)據(jù)。

4.3 是否使用覆蓋索引的判斷

在選擇是否使用覆蓋索引時(shí)，需要綜合考慮查詢的字段以及字段長(zhǎng)度，以及查詢操作的頻率和數(shù)據(jù)量。如果查詢需要返回的字段較多或字段長(zhǎng)度較長(zhǎng)，可能需要權(quán)衡回表帶來的性能損耗和數(shù)據(jù)完整性的需求。

在實(shí)際應(yīng)用中，我們可以根據(jù)具體的場(chǎng)景來決定是否使用回表操作。下面列舉了一些使用回表的典型場(chǎng)景：

需要返回更全面的數(shù)據(jù)：有些查詢場(chǎng)景下，返回的字段可能不僅僅是索引所包含的列，此時(shí)回表可以提供更全面的數(shù)據(jù)信息。
使用非聚簇索引：當(dāng)表中沒有定義主鍵或者查詢條件沒有使用主鍵時(shí)，非聚簇索引成為主要的索引選擇，但回表操作則難以避免。
超過最大索引長(zhǎng)度限制：如果需要返回的字段長(zhǎng)度超過了最大索引長(zhǎng)度限制，即使使用了覆蓋索引也無法避免回表，此時(shí)需要注意回表帶來的性能損耗。