SQL执行计划是基于架构还是数据或两者？ [英] Are SQL Execution Plans based on Schema or Data or both?

问题描述

我希望这个问题不是太明显......我已经找到了很多关于解释执行计划的好信息，但有一个问题我还没有找到答案。

I hope this question is not too obvious...I have already found lots of good information on interpreting execution plans but there is one question I haven't found the answer to.

计划（更具体地说是相对CPU成本）是仅基于架构，还是基于数据库中当前的实际数据？

Is the plan (and more specifically the relative CPU cost) based on the schema only, or also the actual data currently in the database?

我尝试对产品数据库中需要索引的位置进行一些分析，但我正在使用自己的测试系统，该系统没有接近数据量该领域的产品将有。我看到一些奇怪的事情，比如在添加索引后估计的CPU成本实际上略微上升，我想知道这是因为我的数据集是如此之小。

I am try to do some analysis of where indexes are needed in my product's database, but am working with my own test system which does not have close to the amount of data a product in the field would have. I am seeing some odd things like the estimated CPU cost actually going slightly UP after adding an index, and am wondering if this is because my data set is so small.

I我正在使用SQL Server 2005和Management Studio来执行计划

I am using SQL Server 2005 and Management Studio to do the plans

推荐答案

它将基于Schema和Data。 Schema告诉它哪些索引可用，Data告诉它哪个更好。

It will be based on both Schema and Data. The Schema tells it what indexes are available, the Data tells it which is better.

答案可以在很小的程度上改变，具体取决于你使用的DBMS（你没有虽然），但他们都保持对索引的统计数据，以了解索引是否会有所帮助。如果索引将1000行分成900个不同的值，则它是一个很好的索引。如果索引只为1000行产生3个不同的值，那么它实际上不是 selective 所以它不是很有用。

The answer can change in small degrees depending on the DBMS you are using (you have not stated), but they all maintain statistics against indexes to know whether an index will help. If an index breaks 1000 rows into 900 distinct values, it is a good index to use. If an index only results in 3 different values for 1000 rows, it is not really selective so it is not very useful.

这篇关于SQL执行计划是基于架构还是数据或两者？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！