如何优化特定查询的表？ [英] How do you optimize tables for specific queries?

问题描述

1. 您用来确定频繁查询的模式是什么？


2. 如何选择优化因素？


3. 可以进行哪些更改？

< $>


解决方案

这是一个很好的问题，如果相当广泛（而且更糟糕的是）。

如果我了解你，那么你在问如何从头开始攻击优化问题。





要问的第一个问题是：是否有性能问题？

如果没有问题，那你完成了这是情况。很好





另一方面...





确定频繁的查询





日志记录将让您经常查询。

如果您使用某种数据访问层，则添加代码可能很简单查询。

执行查询并记录每个查询所需的时间也是个好主意。这可以让您了解问题在哪里。

此外，请求用户哪些位令他们烦恼。如果缓慢的响应不会使用户烦恼，那没关系。





选择优化因素？





（我可能会误会这部分问题）
您正在寻找查询/响应时间中的任何模式。

这些通常是查询大型表或查询，它们在单个查询中连接多个表。 ...但如果您记录响应时间，您可以由他们指导。





可以进行更改的类型？





您具体询问优化表。

这里有一些你可以寻找的东西：






• 非规范化。这样可以将多个表组合成一个更宽的表，因此您可以在几个表中加入查询，而只需读取一个表。这是一个非常普遍和强大的技术。注意。我建议保持原来的标准化表格，并建造非正规化表格 - 这样，你不会丢弃任何东西。如何保持最新的是另一个问题。您可以在基础表上使用触发器，或定期运行刷新过程。


• 规范化。这通常不被认为是一个优化过程，但是在两种情况下：
  
  
  
  
  • 更新。标准化使更新更快，因为每个更新都是最小的（您正在更新最小的列 - 列 - 可能的表）这几乎是归一化的定义。
  
  
  • 查询非正规化表以获取更小（较少行）表上存在的信息可能会导致问题，在这种情况下，存储归一化表以及非标准化表（见上文）。
  
  


• 水平分区，这意味着通过将一些行放在另一个相同的表中来使表更小，一个常见的用例是让所有在表 ThisMonthSales 中的这个月行，以及表 OldSales 中的所有旧行，其中两个表都具有相同的模式，如果大多数查询是最近的数据，则该策略可以意味着所有查询中只有99％的数据只能查看1％的数据 - 这是一个巨大的胜利。


• 垂直分区将它们放在一个新表中，该表被主键加入到主表中。这对于非常宽的表（例如具有数十个字段）可能是有用的，并且如果表被稀疏地填充可能有帮助。


• Indeces 。我不知道你的问题是否涵盖了这些，但是关于使用屁股，还有很多其他答案。找到索引的一个很好的方法是找到一个缓慢的查询。查看查询计划并查找表扫描。该表上的索引字段，以便删除表扫描。如果需要，我可以写更多的内容 - 发表评论。






您可能还会喜欢我的帖子这个。



1. What are the patterns you use to determine the frequent queries?
2. How do you select the optimization factors?
3. What are the types of changes one can make?

解决方案

This is a nice question, if rather broad (and none the worse for that).
If I understand you, then you're asking how to attack the problem of optimisation starting from scratch.

The first question to ask is: "is there a performance problem?"
If there is no problem, then you're done. This is often the case. Nice.

On the other hand...

Determine Frequent Queries

Logging will get you your frequent queries.
If you're using some kind of data access layer, then it might be simple to add code to log all queries.
It is also a good idea to log when the query was executed and how long each query takes. This can give you an idea of where the problems are.
Also, ask the users which bits annoy them. If a slow response doesn't annoy the user, then it doesn't matter.

Select the optimization factors?

(I may be misunderstanding this part of the question) You're looking for any patterns in the queries / response times.
These will typically be queries over large tables or queries which join many tables in a single query. ... but if you log response times, you can be guided by those.

Types of changes one can make?

You're specifically asking about optimising tables.
Here are some of the things you can look for:

• Denormalisation. This brings several tables together into one wider table, so in stead of your query joining several tables together, you can just read one table. This is a very common and powerful technique. NB. I advise keeping the original normalised tables and building the denormalised table in addition - this way, you're not throwing anything away. How you keep it up to date is another question. You might use triggers on the underlying tables, or run a refresh process periodically.
• Normalisation. This is not often considered to be an optimisation process, but it is in 2 cases:
  • updates. Normalisation makes updates much faster because each update is the smallest it can be (you are updating the smallest - in terms of columns and rows - possible table. This is almost the very definition of normalisation.
  • Querying a denormalised table to get information which exists on a much smaller (fewer rows) table may be causing a problem. In this case, store the normalised table as well as the denormalised one (see above).
• Horizontal partitionning. This means making tables smaller by putting some rows in another, identical table. A common use case is to have all of this month's rows in table ThisMonthSales, and all older rows in table OldSales, where both tables have an identical schema. If most queries are for recent data, this strategy can mean that 99% of all queries are only looking at 1% of the data - a huge performance win.
• Vertical partitionning. This is Chopping fields off a table and putting them in a new table which is joinned back to the main table by the primary key. This can be useful for very wide tables (e.g. with dozens of fields), and may possibly help if tables are sparsely populated.
• Indeces. I'm not sure if your quesion covers these, but there are plenty of other answers on SO concerning the use of indeces. A good way to find a case for an index is: find a slow query. look at the query plan and find a table scan. Index fields on that table so as to remove the table scan. I can write more on this if required - leave a comment.

You might also like my post on this.

这篇关于如何优化特定查询的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！