SQLServer中沒有函數(shù)索引，在某些場(chǎng)景下查詢的時(shí)候要根據(jù)字段的某一部分做查詢或者經(jīng)過某種計(jì)算之后做查詢，如果使用函數(shù)或者其他方式作用在字段上之后，就會(huì)限制到索引的使用，不過我們可以間接地實(shí)現(xiàn)類似于函數(shù)索引的功能。

另外一個(gè)就是如果查詢字段較大或者字段較多的時(shí)候，所建立的索引就顯得有點(diǎn)笨重，效率也不高，就需要考慮使用一個(gè)較小的"替代性"字段做等價(jià)替換，類似于Hash索引，

本文粗淺地介紹兩種上述兩種問題的解決方式，僅供參考。

1，在計(jì)算列上建索引，實(shí)現(xiàn)“函數(shù)索引”的功能

SQLServer在建表的時(shí)候允許使用計(jì)算列，可以借助這個(gè)計(jì)算列來實(shí)現(xiàn)函數(shù)索引的功能，這里舉例說明一下

Create Table TestFunctionIndex
(
  id int identity(1,1),
  val varchar(50),
  subval as LOWER(SUBSTRING(val,10,4)) persisted --增加一個(gè)持久化計(jì)算列
)
GO

--在持久化計(jì)算列上建立索引
create index idx_subvar on TestFunctionIndex(subval)
GO

--插入10W行測(cè)試數(shù)據(jù)
insert into TestFunctionIndex(val) values (NEWID())
go 100000

在有索引的字段上使用函數(shù)之后，是無法使用索引的

如果直接在計(jì)算列上查詢，就可以正常地使用到索引了

以上通過在計(jì)算列上建立一個(gè)索引，可以根據(jù)計(jì)算列上的索引做查找，避免了直接在字段上使用函數(shù)或者其他操作，造成即便字段上有索引也用不到的情況

補(bǔ)充：

測(cè)試中神奇地發(fā)現(xiàn)，如果計(jì)算列字段上建立了索引，在原始字段上使用函數(shù)與計(jì)算列的函數(shù)一樣的時(shí)候，可以神奇地使用到計(jì)算列上的索引?？梢奡QLServer在我們沒有注意的地方也是下了不少功夫的啊

2，生成較長(zhǎng)字段或者多個(gè)字段的Hash值替代原始字段做查詢或者連接來提升查詢效率

開發(fā)中遇到另外一種常見的情況是經(jīng)常使用到的查詢條件字段較長(zhǎng)，或者是表連接的時(shí)候連接條件字段較多，

即便是字段或者查詢條件上有索引，但是因?yàn)樽侄屋^長(zhǎng)或者條件較多，此時(shí)有可能會(huì)影響到查詢的效率

這種情況就適當(dāng)考慮將原始的較長(zhǎng)的字段生成一個(gè)較小的字段（但是要確保唯一性），或者是講多個(gè)字段生成一個(gè)較短的數(shù)據(jù)類型做替代，以提高查詢的效率

舉個(gè)例子，假如有這么一張表，Name字段是我模擬出來的，Name是一個(gè)比較長(zhǎng)的字段，又要用來做檢索

意思就是查詢字段較長(zhǎng)，索引代價(jià)太大，此時(shí)就需要考慮用一種較小的等價(jià)字段來替代

下面通過某種方式計(jì)算較長(zhǎng)字段的Hash值，來做等價(jià)替換

模擬生成一下測(cè)試數(shù)據(jù)

Create table testHashColumn
(
  id int identity(1,1),
  QueryName nvarchar(100),
  HashName AS CAST( HASHBYTES('MD2',QueryName) AS UNIQUEIDENTIFIER) persisted
)
GO

create index idx_HashName ON testHashColumn(HashName)
GO

--這里模擬生成一個(gè)較長(zhǎng)的名字字段
DECLARE @i int = 0
while @i<10000
begin 
  INSERT INTO testHashColumn (QueryName) VALUES (CONCAT('北京新視點(diǎn)科技文化傳媒有限公司',@i))
  set @i = @i+1
end

我們知道，Name這個(gè)名字是nvarchar(100)的，這個(gè)字段做索引不是不可以，如果情況復(fù)雜，實(shí)際中有可能比這個(gè)字段更大，做索引顯得太寬了，造成索引空間過大，在效率上有一定程度的影響。

這里就可以考慮在Name這個(gè)字段上生成一個(gè)“替代”字段（上述HashName AS CAST( HASHBYTES('MD2',QueryName) AS UNIQUEIDENTIFIER) persisted這個(gè)計(jì)算列），

這個(gè)字段首選是要跟實(shí)際值一一對(duì)應(yīng)的，另外就是要求“替代”的字段類型要求相對(duì)較小，當(dāng)然方法也有多種，比如生成利用checksum函數(shù)生成一個(gè)校驗(yàn)值，但是據(jù)實(shí)際觀察checksum生成的校驗(yàn)值是有可能重復(fù)的，也就是說兩個(gè)不同的字符串，生成同一個(gè)校驗(yàn)值

比如這樣，很容易驗(yàn)證出來這個(gè)問題，可以認(rèn)為是對(duì)于不同的字符串，計(jì)算之后得到同一個(gè)校驗(yàn)和

因此在生成“替代”字段的時(shí)候，需要考慮計(jì)算值的唯一性

這里使用的是HASHBYTES加密函數(shù)，對(duì)字符串加密，然后對(duì)加密之后的數(shù)據(jù)生成一個(gè)UNIQUEIDENTIFIER，重復(fù)的概率就小的多的多了

演示這里通過CAST( HASHBYTES('MD2','北京新視點(diǎn)科技文化傳媒有限公司999') AS UNIQUEIDENTIFIER)的方式，就可以給這個(gè)較長(zhǎng)的字段生成一個(gè)UNIQUEIDENTIFIER類型的字段，

當(dāng)然也不一定只有這一種方法，甚至可以做的跟復(fù)雜，只要能保證一個(gè)唯一的長(zhǎng)字段生成的較短的字段也是唯一的就可以達(dá)到目的了

參考如下查詢，就可以使用HashName計(jì)算出來的值與計(jì)算列做比較，在一定程度上可以減少檢索字段索引的大小，又能達(dá)到目的的效果

如截圖，就可以使用HashName字段上的索引了，同時(shí)也避免了在原始的QueryName這個(gè)較長(zhǎng)的字段上建索引，節(jié)約了空間并提高了查詢效率

3， 邏輯主鍵為多個(gè)字段的時(shí)候，在多了字段上生成一個(gè)“替代”性的唯一字段

某些情況下業(yè)務(wù)需求或者設(shè)計(jì)也好(比如沒有達(dá)到第三范式，BC范式，第四范式，甚至是第五范式)，在表連接的時(shí)候往往會(huì)有多個(gè)字段

比如這種樣子：

SELECT *
FROM TableNameA a
INNER JOIN TableNameB b
  ON a.key=b.key
    AND a.Type = b.Type
    AND a.Status = b.Staus
    AND a.CreationTime = b.CreationTime
    AND a.***=b.***
where ***

在表關(guān)聯(lián)的時(shí)候，連接條件很多，如果是這樣子，最好的情況就是建立一個(gè)較寬的復(fù)合索引，但是這樣的話，索引的寬度和體積就變得很大，使用的時(shí)候效率也有一定的影響。這種情況就可以考慮在TableNameA 和 TableNameB 上，利用多個(gè)連接的字段（Key+Type +Status +CreationTime+***）做了類似于示例2中的一個(gè)計(jì)算列，在計(jì)算列上建立一個(gè)索引，然后再表連接的時(shí)候就可以用如下的方式替代

SELECT *
FROM TableNameA a
INNER JOIN TableNameB b
  ON a.HashValue=b.HashValue
WHERE ***

總是，這是一種以空間換時(shí)間的思路（冗余存儲(chǔ)一個(gè)類似于標(biāo)識(shí)符的字段，提高查詢效率），在生成“替代”字段的思想有兩點(diǎn)，第一要足夠的小，第二要原始值生成替代字段的唯一性

總結(jié)：SQLServer 中沒有函數(shù)索引和Hash索引，而某些業(yè)務(wù)需求或者說是為了性能考慮，又需要類似的功能，通過類似于空間換時(shí)間的方法來實(shí)現(xiàn)，可以變通地來實(shí)現(xiàn)類似于函數(shù)索引或者Hash索引的功能，已達(dá)到其他數(shù)據(jù)庫(kù)中函數(shù)索引和Hash索引的效果（雖然原理可能不一樣）。需要注意的就是在生成計(jì)算列或者說Hash值替代的時(shí)候要注意計(jì)算方式，確保生成之后的Key值的唯一性。當(dāng)然實(shí)現(xiàn)方式就可以根據(jù)需要自行選擇了，條條大路通羅馬。

以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，同時(shí)也希望多多支持腳本之家！

最新評(píng)論