由于许多性能问题都涉及数据库问题，因此在调优查询和存储过程时，我将介绍一些需要注意的具体问题。

避免在大多数数据库中使用游标。也要避免循环。大多数时候，数据访问应该基于设置，而不是逐条记录处理。这包括当您希望一次插入1,000,000条记录时，不要重用单个记录存储过程。

不要使用select *，只返回实际需要的字段。如果存在任何连接，则尤其如此，因为连接字段将重复，从而在服务器和网络上造成不必要的负载。

避免使用相关的子查询。使用连接(尽可能包括到派生表的连接)(我知道这对于Microsoft SQL Server是正确的，但是在使用不同的后端时测试建议)。

索引，索引，索引。如果适用于您的数据库，请更新这些统计数据。

使查询sargable。这意味着避免一些不可能使用索引的事情，例如在like子句的第一个字符中使用通配符，或在join中的函数中使用通配符，或作为where语句的左侧部分。

使用正确的数据类型。在日期字段上进行日期计算要比尝试将字符串数据类型转换为日期数据类型然后进行计算快得多。

永远不要在触发器中放入任何形式的循环!

大多数数据库都有一种方法来检查如何执行查询。在Microsoft SQL Server中，这被称为执行计划。先检查一下，看看问题出在哪里。

在确定需要优化的内容时，考虑查询运行的频率以及运行所需的时间。有时，对一个每天运行数百万次的查询稍作调整，可以获得比删除一个月只运行一次的long_running查询更多的性能。

使用某种分析器工具来找出发送到数据库和从数据库发送的内容。我记得过去有一次，我们不知道为什么页面加载这么慢，而存储过程却很快，并通过分析发现网页多次而不是一次地请求查询。

剖析器还将帮助您找到谁在阻止谁。一些单独运行时执行很快的查询可能会因为来自其他查询的锁而变得非常慢。

建议:

Pre-compute rather than re-calculate: any loops or repeated calls that contain calculations that have a relatively limited range of inputs, consider making a lookup (array or dictionary) that contains the result of that calculation for all values in the valid range of inputs. Then use a simple lookup inside the algorithm instead. Down-sides: if few of the pre-computed values are actually used this may make matters worse, also the lookup may take significant memory. Don't use library methods: most libraries need to be written to operate correctly under a broad range of scenarios, and perform null checks on parameters, etc. By re-implementing a method you may be able to strip out a lot of logic that does not apply in the exact circumstance you are using it. Down-sides: writing additional code means more surface area for bugs. Do use library methods: to contradict myself, language libraries get written by people that are a lot smarter than you or me; odds are they did it better and faster. Do not implement it yourself unless you can actually make it faster (i.e.: always measure!) Cheat: in some cases although an exact calculation may exist for your problem, you may not need 'exact', sometimes an approximation may be 'good enough' and a lot faster in the deal. Ask yourself, does it really matter if the answer is out by 1%? 5%? even 10%? Down-sides: Well... the answer won't be exact.

很难对这个问题给出一般的答案。这实际上取决于你的问题领域和技术实现。一种与语言无关的通用技术:识别无法消除的代码热点，并手工优化汇编代码。

目前最重要的限制因素是有限的内存带宽。多核只会让情况变得更糟，因为带宽是在核之间共享的。此外，用于实现缓存的有限芯片区域也分配给了内核和线程，这进一步恶化了这个问题。最后，保持不同缓存一致性所需的芯片间信号也会随着核数的增加而增加。这也增加了一个惩罚。

这些是您需要管理的影响。有时是通过对代码的微观管理，但有时是通过仔细考虑和重构。

很多注释已经提到了缓存友好的代码。至少有两种不同的风格:

避免内存读取延迟。 降低内存总线压力(带宽)。

第一个问题与如何使数据访问模式更规则有关，从而使硬件预取器更有效地工作。避免动态内存分配，这会将数据对象分散在内存中。使用线性容器代替链表、散列和树。

第二个问题与提高数据重用有关。修改算法以处理适合可用缓存的数据子集，并在数据仍在缓存中时尽可能多地重用这些数据。

更紧密地封装数据并确保在热循环中使用缓存线路中的所有数据，将有助于避免这些其他影响，并允许在缓存中安装更多有用的数据。

我大半辈子都在这里度过。大致的方法是运行你的分析器并记录它:

Cache misses. Data cache is the #1 source of stalls in most programs. Improve cache hit rate by reorganizing offending data structures to have better locality; pack structures and numerical types down to eliminate wasted bytes (and therefore wasted cache fetches); prefetch data wherever possible to reduce stalls. Load-hit-stores. Compiler assumptions about pointer aliasing, and cases where data is moved between disconnected register sets via memory, can cause a certain pathological behavior that causes the entire CPU pipeline to clear on a load op. Find places where floats, vectors, and ints are being cast to one another and eliminate them. Use __restrict liberally to promise the compiler about aliasing. Microcoded operations. Most processors have some operations that cannot be pipelined, but instead run a tiny subroutine stored in ROM. Examples on the PowerPC are integer multiply, divide, and shift-by-variable-amount. The problem is that the entire pipeline stops dead while this operation is executing. Try to eliminate use of these operations or at least break them down into their constituent pipelined ops so you can get the benefit of superscalar dispatch on whatever the rest of your program is doing. Branch mispredicts. These too empty the pipeline. Find cases where the CPU is spending a lot of time refilling the pipe after a branch, and use branch hinting if available to get it to predict correctly more often. Or better yet, replace branches with conditional-moves wherever possible, especially after floating point operations because their pipe is usually deeper and reading the condition flags after fcmp can cause a stall. Sequential floating-point ops. Make these SIMD.

我还喜欢做一件事:

将编译器设置为输出程序集清单，并查看它为代码中的热点函数发出了什么。所有那些聪明的优化，“一个好的编译器应该能够自动为你做”?实际的编译器可能不会执行这些操作。我见过GCC发出真正的WTF代码。

我想这已经用不同的方式说过了。但是当你在处理一个处理器密集型算法时，你应该以牺牲其他所有东西为代价来简化最内部循环中的所有东西。

That may seem obvious to some, but it's something I try to focus on regardless of the language I'm working with. If you're dealing with nested loops, for example, and you find an opportunity to take some code down a level, you can in some cases drastically speed up your code. As another example, there are the little things to think about like working with integers instead of floating point variables whenever you can, and using multiplication instead of division whenever you can. Again, these are things that should be considered for your most inner loop.

有时，您可能会发现在内循环中对整数执行数学运算的好处，然后将其缩小为随后可以使用的浮点变量。这是一个牺牲一个部分的速度来提高另一个部分的速度的例子，但在某些情况下，这样做是值得的。