数据库都支持自动创建索引吗?Do any databases support automatic Index Creation?
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...
(原文：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.
(原文：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:
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?"
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.
- 数据库都支持自动创建索引吗?Do any databases support automatic Index Creation?
- 如何加入另一个MySQL表在使用主/明细在Netbeans示例表单吗?How to join another MySQL table when using Master/Detail Sample Form in Netbeans?
- MySQL条件语句MySQL conditional statement
- 我怎么能有MySQL写输出文件作为不同的用户? - How can I have MySQL write outfiles as a different user?
- SQL查询字段不包含$ x的地方SQL Query Where Field DOES NOT Contain $x
- MySQL更新错误当使用特殊字符MySQL update error when special characters are used