如何在T-SQL中为varchar列生成唯一ID并优化表连接性能？

我来帮你搞定这个性能问题——用整数ID替换varchar关联字段确实是提升连接效率的绝佳方案，尤其是当你的表数据量上去之后，字符串比对的开销会被放大很多。下面一步步给你拆解怎么做：

我们需要一个专门的表来存储所有唯一的地址，以及对应的自增整数ID，这样所有表的关联都通过这个ID来做，速度会快很多：

CREATE TABLE dbo.AddressLookup (
    AddressID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- 自增主键，聚簇索引让查询更快
    Address VARCHAR(255) NOT NULL UNIQUE NONCLUSTERED -- 唯一约束确保一个地址只对应一个ID
);

接下来要把messages里的orig、recip，还有jointable里的address都导入到这个映射表，确保没有重复。用MERGE语句是最高效的方式：

MERGE INTO dbo.AddressLookup AS target
USING (
    -- 合并所有唯一地址
    SELECT DISTINCT orig AS Address FROM dbo.messages
    UNION
    SELECT DISTINCT recip AS Address FROM dbo.messages
    UNION
    SELECT DISTINCT address AS Address FROM dbo.jointable
) AS source ON target.Address = source.Address
WHEN NOT MATCHED THEN -- 只插入映射表里没有的新地址
    INSERT (Address) VALUES (source.Address);

如果数据量不大，用INSERT ... NOT EXISTS也可以，但MERGE在大数据量下更高效。

现在要给messages和jointable加上对应的ID列，然后通过映射表把ID填充进去：

处理messages表 #

-- 先加两个ID列，分别对应orig和recip
ALTER TABLE dbo.messages ADD OrigAddressID INT;
ALTER TABLE dbo.messages ADD RecipAddressID INT;

-- 填充OrigAddressID
UPDATE m
SET OrigAddressID = al.AddressID
FROM dbo.messages m
JOIN dbo.AddressLookup al ON m.orig = al.Address;

-- 填充RecipAddressID
UPDATE m
SET RecipAddressID = al.AddressID
FROM dbo.messages m
JOIN dbo.AddressLookup al ON m.recip = al.Address;

-- 给新列加索引，进一步提升连接速度
CREATE NONCLUSTERED INDEX IX_Messages_OrigAddressID ON dbo.messages(OrigAddressID);
CREATE NONCLUSTERED INDEX IX_Messages_RecipAddressID ON dbo.messages(RecipAddressID);

处理jointable表 #

-- 添加对应address的ID列
ALTER TABLE dbo.jointable ADD AddressID INT;

-- 填充AddressID
UPDATE j
SET AddressID = al.AddressID
FROM dbo.jointable j
JOIN dbo.AddressLookup al ON j.address = al.Address;

-- 给新列加索引
CREATE NONCLUSTERED INDEX IX_JointTable_AddressID ON dbo.jointable(AddressID);

现在就可以用整数ID来替换原来的varchar比对了，速度会有明显提升：

SELECT /* 你的计算逻辑 */
FROM dbo.messages m WITH (NOLOCK)
JOIN dbo.jointable j WITH (NOLOCK)
    ON j.AddressID = m.OrigAddressID 
    OR j.AddressID = m.RecipAddressID;

另外，OR条件有时候会让查询优化器难以生成最优执行计划，你可以考虑把查询拆成两个UNION ALL的分支，性能可能会更好：

SELECT /* 你的计算逻辑 */
FROM dbo.messages m WITH (NOLOCK)
JOIN dbo.jointable j WITH (NOLOCK)
    ON j.AddressID = m.OrigAddressID
UNION ALL
SELECT /* 你的计算逻辑 */
FROM dbo.messages m WITH (NOLOCK)
JOIN dbo.jointable j WITH (NOLOCK)
    ON j.AddressID = m.RecipAddressID;

注意：如果这两个分支会产生重复结果，就把UNION ALL改成UNION，但UNION会做去重，性能比UNION ALL差一些，所以优先用UNION ALL。

为了避免新插入的数据没有对应的ID，你可以给原表创建触发器，自动维护映射表和ID列：

比如给messages表创建插入触发器：

CREATE TRIGGER trg_Messages_InsertAddress
ON dbo.messages
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 先把新的orig地址插入映射表（不存在的话）
    MERGE INTO dbo.AddressLookup AS target
    USING (SELECT DISTINCT orig FROM inserted) AS source
        ON target.Address = source.orig
    WHEN NOT MATCHED THEN
        INSERT (Address) VALUES (source.orig);

    -- 再把新的recip地址插入映射表（不存在的话）
    MERGE INTO dbo.AddressLookup AS target
    USING (SELECT DISTINCT recip FROM inserted) AS source
        ON target.Address = source.recip
    WHEN NOT MATCHED THEN
        INSERT (Address) VALUES (source.recip);

    -- 最后插入数据到messages表，同时填充ID列
    INSERT INTO dbo.messages (orig, recip, OrigAddressID, RecipAddressID /* 其他需要的列 */)
    SELECT 
        i.orig,
        i.recip,
        al_orig.AddressID,
        al_recip.AddressID
        /* 其他列直接从inserted里取 */
    FROM inserted i
    JOIN dbo.AddressLookup al_orig ON i.orig = al_orig.Address
    JOIN dbo.AddressLookup al_recip ON i.recip = al_recip.Address;
END;

同样的，给jointable也创建类似的触发器，这样新插入的数据会自动维护对应的ID，不用手动处理。

• 如果要保证数据一致性，可以给messages的orig、recip和jointable的address添加外键约束到AddressLookup的Address列，不过这会增加一点写入开销，但能避免脏数据。
• 如果你的表数据量特别大，批量更新的时候最好分批进行，比如每次更10000条，避免锁表太久影响业务。

内容的提问来源于stack exchange，提问作者alexithymia