优化现有数据库时要检查的第一个问题是什么？ [英] What are the first issues to check while optimizing an existing database?
问题描述
在优化（性能调整，故障排除）现有（但未知的）数据库时，首要问题和重要性顺序是什么？
先前优化中的哪些操作/度量给出最大的效果（可能最小的工作）？
What are the top issues and in which order of importance to look into while optimizing (performance tuning, troubleshooting) an existing (but unknown to you) database?
Which actions/measures in your previous optimizations gave the most effect (with possibly the minimum of work) ?
我想将此问题划分为以下类别（按我感兴趣的顺序）：
I'd like to partition this question into following categories (in order of interest to me):
- 需要在最短的时间内显示性能提升（改进）。即最具成本效益的方法/行动;
- 非干扰性或最不麻烦的最有效的方法（不改变现有模式等）
- 干扰方法
更新：
假设我有一个数据库的副本，无法访问生产环境统计信息，最常用的查询，性能计数器等。
这是与开发相关的但不是与DBA相关的问题。
Update2：
假设数据库是由其他人开发的，并且在交付到生产之前被授予我进行优化（审查）。
这是很常见的外包开发，用户。
Update:
Suppose I have a copy of a database on dev machine without access to production environment to observe stats, most used queries, performance counters, etc. in real use.
This is development-related but not DBA-related question.
Update2:
Suppose the database was developed by others and was given to me for optimization (review) before it was delivered to production.
It is quite usual to have outsourced development detached from end-users.
此外，还有一个数据库设计范例，与应用程序数据存储相反，数据库本身应该是一个独立于使用它或使用上下文的特定应用程序的值的使用。
Besides, there is a database design paradigm that a database, in contrast to application data storage, should be a value in itself independently on specific applications that use it or on context of its use.
Update3：感谢所有回音者！你都推我打开子问题
你如何在本地加载dev数据库（服务器）？
Update3: Thanks to all answerers! You all pushed me to open subquestion
How do you stress load dev database (server) locally?
推荐答案
如果你对运行时感兴趣数据库的行为，例如什么是最频繁执行的查询和那些消耗最多时间的，你只能对数据库结构本身做一个静态分析。因为你只能检查一些坏的设计的关键指标，但是你不能真正地告诉你所使用的系统的动力学。
If you're not interested in the runtime behavior of the database, e.g. what are the most frequently executed queries and those that consume the most time, you can only do a "static" analysis of the database structure itself. That has a lot less value, really, since you can only check for a number of key indicators of bad design - but you cannot really tell much about the "dynamics" of the system being used.
我将在数据库中检查的事情，我作为一个 .bak 文件 - 无法收集实时和实际的运行时性能统计信息：
Things I would check for in a database that I get as a .bak file - without the ability to collect live and actual runtime performance statistics - would be:
-
规范化 - 归一化为第三个法线形式的表格结构？ （至少大部分时间 - 可能有一些例外）
normalization - is the table structure normalized to third normal form? (at least most of the time - there might be some exceptions)
所有表都有主键吗？ （如果它没有主键，它不是表）
do all tables have a primary key? ("if it doesn't have a primary key, it's not a table", after all)
对于SQL Server：所有表都有一个<强>好聚类索引？一个唯一的，狭窄的，静态的，并且最好是增加的聚集键 - 理想情况下是一个INT IDENTITY，绝对不是很多字段的大复合索引，没有GUID和大的VARCHAR字段（见Kimberly Tripp's 有关主题的精彩博文）
For SQL Server: do all the tables have a good clustering index? A unique, narrow, static, and preferably ever-increasing clustered key - ideally an INT IDENTITY, and most definitely not a large compound index of many fields, no GUID's and no large VARCHAR fields (see Kimberly Tripp's excellent blog posts on the topics for details)
是否对数据库表有任何检查和默认约束？
are there any check and default constraints on the database tables?
都是由非聚集索引备份的所有外键字段，以加快JOIN查询？
are all the foreign key fields backed up by a non-clustered index to speed up JOIN queries?
在数据库中有任何其他明显的致命的罪，例如
are there any other, obvious "deadly sins" in the database, e.g. overly complicated views, or really badly designed tables etc.
但是，没有实际的运行时统计信息，从静态分析的角度来看，你可以做什么。真正的优化只会发生在你有一个工作日常规的日子，查看什么查询经常使用，并强调你的数据库 - >使用米奇的清单检查这些点。
But again: without actual runtime statistics, you're quite limited in what you can do from a "static analysis" point of view. The real optimization can only really happen when you have a workload from a regular day of operation, to see what queries are used frequently and put the most stress on your database --> use Mitch's checklist to check those points.
这篇关于优化现有数据库时要检查的第一个问题是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
