主页 / dba / 问题 / 235102
Accepted
mustaccio
Asked: 2019-04-18 14:51:37 +0800 CST

如果索引中包含 VARCHAR(MAX) 列，则整个值是否始终存储在索引页中？

  • 772

我是出于好奇而问这个问题，受到这个问题的启发。

我们知道超过VARCHAR(MAX)8000 字节的值不会存储在行中，而是存储在单独的 LOB 页中。随后检索具有该值的行需要两个或多个逻辑 IO 操作（本质上，比理论上需要多一个）。

我们可以将一VARCHAR(MAX)列作为INCLUDEd 添加到唯一索引，如链接问题中所示。如果此列的值长度超过 8000 字节，这些值是否仍会“内联”存储在索引叶页中，或者它们是否也会被移动到 LOB 页？

sql-server varchar

1 个回答

  1. Best Answer

    超过 8000 字节的值不能“内联”存储。它们存储在 LOB 页面上。您可以通过sys.dm_db_index_physical_stats看到这一点。从一个简单的表格开始：

    USE tempdb;

DROP TABLE IF EXISTS #LOB_FOR_ME;

CREATE TABLE #LOB_FOR_ME (
ID BIGINT,
MAX_VERNON_WAS_HERE VARCHAR(MAX) 
);

CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);

    现在插入一些具有 8000 字节值的行，VARCHAR(MAX)并检查 DMF：

    USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 1, REPLICATE('Z', 8000)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');

    索引中没有 LOB 页：

    ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2540 ║         2540 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2540 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

    但是，如果我添加具有 8001 字节值的行：

    USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');

    现在，我刚刚插入的每一行的索引中都有 1 个 LOB 页：

    ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2556 ║         5080 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2556 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
║           0 ║ NONCLUSTERED INDEX ║ LOB_DATA             ║       2540 ║         2540 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

    您还可以使用SET STATISTICS IO ON;正确的查询来查看此内容。考虑以下仅查看 8000 字节行的查询：

    SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 1;

    执行结果：

    扫描计数 1，逻辑读取 2560，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    如果我改为查询 8001 字节的行：

    SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 2;

    现在我看到 lob 读取：

    扫描计数 1，逻辑读取 20，物理读取 0，预读读取 0，lob 逻辑读取 5080，lob 物理读取 0，lob 预读读取 0。

    • 21

相关问题