您是否将索引置于源代码管理中？
如何使它们在测试和生产环境之间保持同步？
当谈到数据库表上的索引时，我的理念是它们是编写查询数据库的任何代码不可或缺的一部分。 如果不分析对索引的影响，就无法引入新查询或更改查询。
因此，我尽最大努力使我的索引在所有环境之间保持同步，但说实话，我在自动化方面做得不太好。 这是一种随意的手动过程。
我定期检查索引统计信息并删除不必要的索引。 我通常通过创建一个删除脚本来完成此操作，然后将其复制回其他环境。
但有时索引是在正常流程之外创建和删除的，很难看出差异在哪里。
我发现真正有用的一件事是使用简单的数字索引名称，例如
idx_t_01
idx_t_02
其中 t 是表的简短缩写。 当我试图巧妙地处理涉及的所有列时，我发现索引维护是不可能的，就像，
idx_c1_c2_c5_c9_c3_c11_5
很难区分这样的索引。
有人有真正好的方法将索引维护集成到源代码控制和开发生命周期中吗？
And how do you keep them in synch between test and production environments?
When it comes to indexes on database tables, my philosophy is that they are an integral part of writing any code that queries the database. You can't introduce new queries or change a query without analyzing the impact to the indexes.
So I do my best to keep my indexes in synch betweeen all of my environments, but to be honest, I'm not doing very well at automating this. It's a sort of haphazard, manual process.
I periodocally review index stats and delete unnecessary indexes. I usually do this by creating a delete script that I then copy back to the other environments.
But here and there indexes get created and deleted outside of the normal process and it's really tough to see where the differences are.
I've found one thing that really helps is to go with simple, numeric index names, like
idx_t_01
idx_t_02
where t is a short abbreviation for a table. I find index maintenance impossible when I try to get clever with all the columns involved, like,
idx_c1_c2_c5_c9_c3_c11_5
It's too hard to differentiate indexes like that.
Does anybody have a really good way to integrate index maintenance into source control and the development lifecycle?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
使用 grails 应用程序时，索引默认存储在源代码管理中，因为您是在表示域对象的文件内定义索引定义。 只是提供“Grails”的观点作为参考。
Using a grails app the indexes are stored in source control by default since you are defining the index definition inside of a file that represents your domain object. Just offering the 'Grails' perspective as an FYI.
索引是数据库模式的一部分，因此应该与其他所有内容一起进行源代码控制。 任何人都不应在不经过正常的质量检查和发布流程（尤其是性能测试）的情况下在生产中创建索引。
关于模式版本控制还有许多其他主题。
Indexes are a part of the database schema and hence should be source controlled along with everything else. Nobody should go around creating indexes on production without going through the normal QA and release process- particularly performance testing.
There have been numerous other threads on schema versioning.
数据库的完整架构应该位于代码旁边的源代码管理中。 当我说“完整模式”时，我指的是表定义、查询、存储过程、索引等等。
进行全新安装时，您将执行以下操作：
- 查看产品的 X 版本。
- 从结账的“数据库”目录中，运行数据库脚本来创建数据库。
- 使用结账时的代码库与数据库进行交互。
当您进行开发时，每个开发人员都应该针对自己的私有数据库实例进行工作。 当他们进行架构更改时，他们会签入一组新的架构定义文件，这些文件适用于修改后的代码库。
使用这种方法，您永远不会遇到代码库-数据库同步问题。
The full schema for your database should be in source control right beside your code. When I say "full schema" I mean table definitions, queries, stored procedures, indexes, the whole lot.
When doing a fresh installation, then you do:
- check out version X of the product.
- from the "database" directory of your checkout, run the database script(s) to create your database.
- use the codebase from your checkout to interact with the database.
When you're developing, every developer should be working against their own private database instance. When they make schema changes they checkin a new set of schema definition files that work against their revised codebase.
With this approach you never have codebase-database sync issues.
是的，任何 DML 或 DDL 更改都会编写脚本并签入源代码管理，主要是通过 Rails 中的 ActiveRecord 迁移。 我讨厌不断地吹响 Rails 的号角，但在构建基于数据库的系统的多年经验中，我发现迁移路线比我使用或构建的任何本土系统都要好得多。
不过，我确实命名了所有索引（不要让 DBMS 随意选择它所选择的疯狂名称）。 不要为它们添加前缀，这很愚蠢（因为您在 sysobjects 或您拥有的任何数据库中都有类型元数据），但我确实包含了表名和列，例如 tablename_col1_col2。
这样，如果我正在浏览 sysobjects，我可以轻松地看到特定表的索引（这也是一种习惯的力量，在我使用的某些 DBMS 上，索引名称在整个数据库中是唯一的，所以唯一的方法以确保使用唯一的名称）。
Yes, any DML or DDL changes are scripted and checked in to source control, mostly thru activerecord migrations in rails. I hate to continually toot rails' horn, but in many years of building DB-based systems I find the migration route to be so much better than any home-grown system I've used or built.
However, I do name all my indexes (don't let the DBMS come up with whatever crazy name it picks). Don't prefix them, that's silly (because you have type metadata in sysobjects, or in whatever db you have), but I do include the table name and columns, e.g. tablename_col1_col2.
That way if I'm browsing sysobjects I can easily see the indexes for a particular table (also it's a force of habit, wayyyy back in the day on some dBMS I used, index names were unique across the whole DB, so the only way to ensure that is to use unique names).
我认为这里有两个问题：索引命名约定，以及将数据库更改添加到源代码管理/生命周期。 我将解决后一个问题。
我已经成为 Java 程序员很长时间了，但最近接触到了一个使用 Ruby on Rails 进行部分系统数据库访问的系统。 我喜欢 RoR 的一件事是“迁移”的概念。 基本上，您有一个充满类似 001_add_foo_table.rb、002_add_bar_table.rb、003_add_blah_column_to_foo.rb 等文件的目录。这些 Ruby 源文件扩展了父类，重写了名为“up”和“down”的方法。 “up”方法包含将数据库模式的先前版本更改为当前版本所需的一组数据库更改。 类似地，“down”方法将更改恢复到以前的版本。 当您想要为特定版本设置架构时，Rails 迁移脚本会检查数据库以查看当前版本是什么，然后找到 .rb 文件，使您从那里向上（或向下）到达所需的版本。
为了使这成为您的开发过程的一部分，您可以将它们检查到源代码控制中，并进行调味。
Rails 并没有什么特别之处，只是这是我第一次看到这种技术被广泛使用。 您也可以使用成对的 SQL DDL 文件，例如 001_UP_add_foo_table.sql 和 001_DOWN_remove_foo_table.sql。 剩下的就是 shell 脚本编写的小问题，留给读者的练习。
I think there are two issues here: the index naming convention, and adding database changes to your source control/lifecycle. I'll tackle the latter issue.
I've been a Java programmer for a long time now, but have recently been introduced to a system that uses Ruby on Rails for database access for part of the system. One thing that I like about RoR is the notion of "migrations". Basically, you have a directory full of files that look like 001_add_foo_table.rb, 002_add_bar_table.rb, 003_add_blah_column_to_foo.rb, etc. These Ruby source files extend a parent class, overriding methods called "up" and "down". The "up" method contains the set of database changes that need to be made to bring the previous version of the database schema to the current version. Similarly, the "down" method reverts the change back to the previous version. When you want to set the schema for a specific version, the Rails migration scripts check the database to see what the current version is, then finds the .rb files that get you from there up (or down) to the desired revision.
To make this part of your development process, you can check these into source control, and season to taste.
There's nothing specific or special about Rails here, just that it's the first time I've seen this technique widely used. You can probably use pairs of SQL DDL files, too, like 001_UP_add_foo_table.sql and 001_DOWN_remove_foo_table.sql. The rest is a small matter of shell scripting, an exercise left to the reader.
我总是对 SQL（DDL、DML 等）进行源代码控制。 它的代码与其他代码一样。 这是很好的做法。
I always source-control SQL (DDL, DML, etc). Its code like any other. Its good practice.
我不确定索引在不同环境中是否应该相同，因为它们具有不同的数据大小。 除非您的测试和生产环境具有完全相同的数据，否则索引将会不同。
至于它们是否属于源代码控制，我不太确定。
I am not sure indexes should be the same across different environments since they have different data sizes. Unless your test and production environments have the same exact data, the indexes would be different.
As to whether they belong in source control, am not really sure.
我没有将索引放入源代码管理中，而是将索引的创建脚本放入其中。 ;-)
索引命名：
I do not put my indexes in source control but the creation script of the indexes. ;-)
Index-naming:
在我当前的项目中，我在源代码控制中有两件事 - 空数据库的完整转储（使用 pg_dump -c 因此它具有创建表和索引的所有 ddl）和确定您拥有的数据库版本的脚本，并应用更改/删除/添加将其提升到当前版本。 前者在我们在新站点上安装时以及 QA 开始新一轮测试时运行，后者在每次升级时运行。 当您更改数据库时，您需要更新这两个文件。
On my current project, I have two things in source control - a full dump of an empty database (using pg_dump -c so it has all the ddl to create tables and indexes) and a script that determines what version of the database you have, and applies alters/drops/adds to bring it up to the current version. The former is run when we're installing on a new site, and also when QA is starting a new round of testing, and the latter is run at every upgrade. When you make database changes, you're required to update both of those files.