SQL索引和性能改进 [英] SQL Indexes and performance improvement

问题描述

我对SQL索引以及它们如何提高性能有一些疑问。希望你们能回答他们！ ：D

I have some questions about SQL indexes and how they improve performance. Hope you guys can answer them! :D

• 创建整个表的索引以及我的表和几列的索引有什么区别？那些只指定了几列的索引会更快吗？

推荐答案

您没有指定哪个数据库系统你'重新使用 - 这些东西总是非常特定于供应商。

You didn't specify what database system you're using - those kinds of things are always very vendor specific.

这是适用于SQL Server的我的专有技术：

Here's my know-how that applies to SQL Server:

首先，SQL Server中的索引在其索引条目中最多只能有900个字节。仅这一点就不可能有一个包含所有列的索引。

first of all, an index in SQL Server can only have at most 900 bytes in its index entry. That alone makes it impossible to have an index with all columns.

最重要的是：这样的索引完全没有意义。你想要实现什么？

Most of all: such an index makes no sense at all. What are you trying to achieve??

考虑一下：如果你有的索引（LastName，FirstName，Street，City），该指数将不能够用于加速查询

Consider this: if you have an index on (LastName, FirstName, Street, City), that index will not be able to be used to speed up queries on

• FirstName 单独


• 城市


• 街道

• FirstName alone
• City
• Street

该指数对于

• （LastName），或


• （LastName，FirstName），或


• （LastName，FirstName，Street），或


• （姓氏，名字，街道，城市）

• (LastName), or
• (LastName, FirstName), or
• (LastName, FirstName, Street), or
• (LastName, FirstName, Street, City)

但实际上没有别的 - 当然不是只搜索街道或只是城市！

but really nothing else - certainly not if you search for just Street or just City!

索引中列的顺序有很大不同，查询优化器不能只使用索引中间某处的任何列进行查找。

The order of the columns in your index makes quite a difference, and the query optimizer can't just use any column somewhere in the middle of an index for lookups.

考虑一下你的电话簿：它的订单可能是LastName，FirstName，也许是Street。那么索引是否可以帮助您找到您所在城市的所有Joe's？所有人都住在主街？不 - 您可以先通过LastName查找 - 然后在该组数据中获得更具体的信息。只对所有内容编制索引并没有帮助加快搜索所有>列。

Consider your phone book: it's order probably by LastName, FirstName, maybe Street. So does that indexing help you find all "Joe's" in your city? All people living on "Main Street" ?? No - you can lookup by LastName first - then you get more specific inside that set of data. Just having an index over everything doesn't help speed up searching for all columns at all.

如果您希望能够搜索到街道 - 你需要在（街道）上添加一个单独的索引（可能还有另外一两列有意义） 。

If you want to be able to search by Street - you need to add a separate index on (Street) (and possibly another column or two that make sense).

如果你想通过占领或其他任何东西进行搜索 - 你需要另一个特定的指数。

If you want to be able to search by Occupation or whatever else - you need another specific index for that.

仅仅因为您的列存在于索引中并不意味着会加快该列的所有搜索速度！

Just because your column exists in an index doesn't mean that'll speed up all searches for that column!

主要规则是：使用尽可能少的索引 - 对于系统来说，太多的索引可能比没有索引更糟糕了......构建系统，监视其性能，并找到那些成本的查询最多 - 然后优化这些，例如通过添加索引。

The main rule is: use as few indices as possible - too many indices can be even worse for a system than having no indices at all.... build your system, monitor its performance, and find those queries that cost the most - then optimize these, e.g. by adding indices.

不要只是盲目地索引每一列只是因为你可以 - 这是糟糕的系统性能的保证 - 任何索引也需要维护和维护，所以索引越多你你的INSERT，UPDATE和DELETE操作会越多（变得越来越慢），因为所有这些索引都需要更新。

Don't just blindly index every column just because you can - this is a guarantee for lousy system performance - any index also requires maintenance and upkeep, so the more indices you have, the more your INSERT, UPDATE and DELETE operations will suffer (get slower) since all those indices need to be updated.

这篇关于SQL索引和性能改进的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！