数据库都支持自动创建索引吗?Do any databases support automatic Index Creation?

- 此内容更新于:2014-12-30
主题:

数据库自动索引表为什么不基于查询的频率?任何工具存在分析数据库和查询接收,并自动创建、或者至少表明索引创建哪一个? 我MySQL特别感兴趣,但是我很好奇其他数据库。

原文:

Why don't databases automatically index tables based on query frequency? Do any tools exist to analyze a database and the queries it is receiving, and automatically create, or at least suggest which indexes to create?

I'm specifically interested in MySQL, but I'd be curious for other databases as well.

解决方案:
原文:

There are database optimizers that can be enabled or attached to databases to suggest (and in some cases perform) indexes that might help things out.

However, it's not actually a trivial problem, and when these aids first came out users sometimes found it actually slowed their databases down due to inferior optimizations.

Lastly, there's a LOT of money in the industry for database architects, and they prefer the status quo.

Still, databases are becoming more intelligent. If you use SQL server profiler with Microsoft SQL server you'll find ways to speed your server up. Other databases have similar profilers, and there are third party utilities to do this work.

But if you're the one writing the queries, hopefully you know enough about what you're doing to index the right fields. If not then having the right indexes is likely the least of your problems...

什么愚蠢的声明,“数据库架构师喜欢quo"状态。是的我们# 39;再保险大型卡特尔,南瓜每次数据库切割。喜欢简单的设备添加到你的车去100英里/加仑的石油公司躲避我们。

(原文:What a silly statement, "database architects prefer the status quo". Yep we're a large cartel that squashes every attempt to make databases self-indexing. Like the simple device you add to your car to get 100mpg that the oil companies are hiding from us.)

Mitch Wheat的回复:@Adam戴维斯:“但是如果你# 39;重新编写查询,希望你足够了解你# 39;重新做索引正确的字段。如果不那么正确的索引可能至少problems"——没有正确的索引描述好比例的所有数据库……

(原文:@Adam Davis: "But if you're the one writing the queries, hopefully you know enough about what you're doing to index the right fields. If not then having the right indexes is likely the least of your problems" - not having the right indexes describes a good proportion of all databases out there...)

Chris Moschini的回复:简单的SQL脚本转储的SQL服务器# 39;年代的内部指标列表的索引和受益于创建它们,估计在2005年,2008年和2012年:blogs.msdn.com/b/bartd/archive/2007/07/19/…

(原文:The simple SQL script here dumps SQL Server's own internal metrics with a list of indexes and estimated benefit from creating them - works in 2005, 2008 and 2012: blogs.msdn.com/b/bartd/archive/2007/07/19/…)

解决方案:
原文:

That is a best question I have seen on stackoverflow. Unfortunately I don't have an answer. Google's bigtable does automatially index the right columns, but BigTable doesn't allow arbitrary joins so the problem space is much smaller.

The only answer I can give is this:

One day someone asked, "Why can't the computer just analyze my code and and compile & statically type the pieces of code that run most often?"

People are solving this problem today (e.g. Tamarin in FF3.1), and I think "auto-indexing" relational databases is the same class of problem, but it isn't as much a priority. A decade from now, manually adding indexes to a database will be considered a waste of time. For now, we are stuck with monitoring slow queries and running optimizers.

如果有一个正确的答案数据库做了。还有# 39;总是权衡。你可以100 & # 39;索引和查询的年代总是跑得快但插入和更新将阻力。哪个更好?因为你经常运行查询并# 39;t意味着这# 39;对你最重要的工作。

(原文:If there were one right answer the database would do it already. There's always a trade off. You could have 100's of indexes and queries would always run fast but inserts and updates would drag. Which is better? Because your query runs frequently doesn't mean it's the most important job to you.)

Mitch Wheat的回复:@Mark布雷迪:位置:# 39;实质性的年代总是权衡。

(原文:@Mark Brady: spot on: it's always a trade off.)

解决方案:
MS SQL 2005还维护一个内部参考建议的索引创建基于使用数据。不完整或准确优化顾问,但这是自动的。研究dm_db_missing_index_groups获得更多信息。
原文:

MS SQL 2005 also maintains an internal reference of suggested indexes to create based on usage data. It's not as complete or accurate as the Tuning Advisor, but it is automatic. Research dm_db_missing_index_groups for more information.

解决方案:
我认为有一个脚本的MS SQL博客在SQL脚本显示索引2005但是我现在找不到确切的脚本!只是我记得的事情描述。这里有一个链接到一些更多信息:http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx PS为SQL Server 2005 +
原文:

There is a script on I think an MS SQL blog with a script for suggesting indexes in SQL 2005 but I can't find the exact script right now! Its just the thing from the description as I recall. Here's a link to some more info http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

PS just for SQL Server 2005 +

解决方案:
原文:

Yes, some engines DO support automatic indexing. One such example for mysql is Infobright, their engine does not support "conventional" indexes and instead implicitly indexes everything - this is a column-based storage engine.

The behaviour of such engines tends to be very different from what developers (And yes, you need ot be a DEVELOPER to even be thinking about using Infobright; it is not a plug-in replacement for a standard engine) expect.

解决方案:
有工具。 对于MS SQL,使用SQL分析器(对数据库记录活动)和数据库引擎优化顾问(SQL 2005)或索引优化向导(SQL 2000)分析活动和推荐索引或其他改进。
原文:

There are tools out there for this.

For MS SQL, use the SQL Profiler (to record activity against the database), and the Database Engine Tuning Advisor (SQL 2005) or the Index Tuning Wizard (SQL 2000) to analyze the activities and recommend indexes or other improvements.

解决方案:
原文:

Part of the reason may be that indexes don't just give a small speedup. If you don't have a suitable index on a large table queries can run so slowly that the application is entirely unusable, and possibly if it is interacting with other software it simply won't work. So you really need the indexes to be right before you start trying to use the application.

Also, rather than building an index in the background, and slowing things down further while it's being built, it is better to have the index defined before you start adding significant amounts of data.

I'm sure we'll get more tools that take sample queries and work out what indexes are necessary; also probably we will eventually get databases that do as you suggest and monitor performance and add indexes they think are necessary, but I don't think they will be a replacement for starting off with the right indexes.

解决方案:
原文:

Amazon's SimpleDB has automatic indexing on all columns based on your usage:

http://aws.amazon.com/simpledb/

It has other limitations though:

  • It's a key-value store, not an RDB. Obviously that means slow joins (and no built-in join support).
  • It has a 10gb limit on table size. There are libraries that will handle partitioning big data for you although this locks you into that library's way of doing things, which can have its own problems.
  • It stores all values as strings, even numbers, which makes sorting a column with a 1,9, and 10 come out like 1,10,9 unless you use a library which hacks this by 0 padding. This also impacts negative numbers.

The 10gb limit is bigger than many might assume, so you could proceed with this for a simple site that you plan on rewriting if it ever hits big.

It's unfortunate this kind of automatic indexing didn't make it into DynamoDb, which appears to have replaced it - they don't even mention SimpleDb in their Product list anymore, you have to find it through old links to it.

解决方案:
Google App Engine(见索引。yaml文件)。
原文:

Google App Engine does that (see the index.yaml file).

解决方案:
我同意什么亚当·戴维斯在他的评论中说。我将添加,如果存在这样的一种机制,自动创建索引,最常见的反应这一特性,“那很好啊…我怎么把它关掉吗?”
原文:

I agree with what Adam Davis says in his comment. I'll add that if such a mechanism existed to create indexes automatically, the most common reaction to this feature would be, "That's nice... How do I turn it off?"

解决方案:
看来,MySQL没有一个用户友好的分析器。也许你想尝试这样的东西,一个php类基于MySQL分析器。
原文:

Seems that MySQL doesn't have a user-friendly profiler. Maybe you want to try something like this, a php class based in MySQL profiler.