全文索引搜索具有大量页面读取 [英] Fulltext index search has large number of page reads

问题描述

我在包含这样的数据的表的列上有一个全文索引:

I have a full-text index on a column in a table that contains data like this:

searchColumn
90210 Brooks Diana Miami FL [e-mail] 5612233395

该列是邮政编码，姓氏，名字，城市，州，电子邮件和电话号码的汇总.

The column is an aggregate of Zip, last name, first name, city, state, e-mail and phone number.

我使用此列根据这些可能的信息中的任何一个来搜索客户.

I use this column to search for a customer based on any of this possible information.

我担心的问题是在此列上执行查询时出现大量读取.我正在使用的查询是:

The issue I am worried about is with the high number of reads that occurs when doing a query on this column. The query I am using is:

declare @searchTerm varchar(100) = ' "FL" AND "90210*" AND "Diana*" AND "Brooks*" '

select *
from CustomerInformation c
where contains(c.searchColumn, @searchTerm)

现在，运行Profiler时，我可以看到该搜索具有大约50.000页读操作以返回单行，这与使用常规索引和多个变量的不同方法(如 @firstName 分解)相反.code>， @LastName ，如下所示:

Now, when running Profiler I can see that this search has about 50.000 page reads to return a single row, as opposed to when using a different approach using regular indexes and multiple variables, broken down like @firstName, @LastName, like below:

WHERE C.FirstName like coalesce(@FirstName + '%' , C.FirstName)
    AND C.LastName like coalesce(@LastName + '%' , C.LastName)
    etc.

使用这种方法，我只能读取约140页.我知道方法完全不同，但是我试图理解为什么全文本具有更多的读取量，并且是否有任何办法可以将其降低到更接近使用常规索引时得到的数字.

Using this approach I get only around 140 page reads. I know the approaches are quite different, but I'm trying to understand why the full-text version has so much more reads and if there is any way I can bring that down to something closer to the numbers I get when using regular indexes.

推荐答案

对此我有几点想法.首先，Select *将产生大量的页面读取，因为它必须提取所有可能会或可能不会建立索引的列.当您拉出每一列时，很可能不会利用那里的最佳索引计划.

I have a couple of thoughts on this. First the Select * will generate a great number of page reads because it has to pull all columns which may or may not be indexed. When you pull every column it most likely will not make use of the best Index plan out there.

关于Where子句，当使用@searchTerm和"FL" AND"90210 *" AND"Diana *" AND"Brooks *"的值时，每次运行时必须多次检查数据页.想一想如果必须要如何查找此信息.您查看一张上面有信息的纸，然后查看搜索列中是否包含FL.现在它包含FL和90210 *.现在，它是否同时包含这两个元素以及Diana ...等.

As to your Where clauses, when using the @searchTerm and the value of "FL" AND "90210*" AND "Diana*" AND "Brooks*" it has to check the datapages multiple times each time it is run. Think of how you would look up this information if you had to do it. You look at a piece of paper with the info on it and see if the search column contains FL. Now does it contain FL and 90210*. Now does it contain both of those plus Diana...etc.

您会看到为什么它不得不继续返回页面以一遍又一遍地阅读.第二个查询只需要查看狭窄定义的2列即可.

You can see why it would keep having to go back to the page to read over and over again. The second query only has to look at 2 columns narrowly defined.

如果您想了解更多有关此的信息，我建议Brent Ozar开设一门现在免费的课.如何像SQL Server引擎一样思考

If you want more information on this, I would suggest a class by Brent Ozar that is free right now. How to think like the SQL Server Engine

我希望能帮上忙.

这篇关于全文索引搜索具有大量页面读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！