如何加快SQL查询？指标？

I have the following database structure :

我有以下数据库结构：

create table Accounting
(
  Channel,
  Account
)

create table ChannelMapper
(
  AccountingChannel,
  ShipmentsMarketPlace,
  ShipmentsChannel
)

create table AccountMapper
(
  AccountingAccount,
  ShipmentsComponent
)

create table Shipments
(
   MarketPlace,
   Component,
   ProductGroup,
   ShipmentChannel,
   Amount
 )

I have the following query running on these tables and I'm trying to optimize the query to run as fast as possible :

我在这些表上运行以下查询，并且我正在尝试优化查询以尽可能快地运行：

 select Accounting.Channel, Accounting.Account, Shipments.MarketPlace
 from Accounting join ChannelMapper on Accounting.Channel = ChannelMapper.AccountingChannel

 join AccountMapper on Accounting.Accounting = ChannelMapper.AccountingAccount
 join Shipments on 
 (
     ChannelMapper.ShipmentsMarketPlace = Shipments.MarketPlace
     and ChannelMapper.AccountingChannel = Shipments.ShipmentChannel
     and AccountMapper.ShipmentsComponent = Shipments.Component
 )
 join (select Component, sum(amount) from Shipment group by component) as Totals
    on  Shipment.Component = Totals.Component

How do I make this query run as fast as possible ? Should I use indexes ? If so, which columns of which tables should I index ?

如何使此查询尽可能快地运行？我应该使用索引吗？如果是这样，我应该索引哪些表的哪些列？

Here is a picture of my query plan :

这是我的查询计划的图片：

Thanks,

谢谢，

3 个解决方案

#1

Indexes are essential to any database.

索引对任何数据库都至关重要。

Speaking in "layman" terms, indexes are... well, precisely that. You can think of an index as a second, hidden, table that stores two things: The sorted data and a pointer to its position in the table.

用“外行”来说，索引是......好吧，恰恰相反。您可以将索引视为存储两件事的第二个隐藏表：排序数据和指向其在表中位置的指针。

Some thumb rules on creating indexes:

一些关于创建索引的拇指规则：

1. Create indexes on every field that is (or will be) used in joins.
2. 在连接中使用（或将要）的每个字段上创建索引。
3. Create indexes on every field on which you want to perform frequent where conditions.
4. 在要在各种条件下执行频繁执行的每个字段上创建索引。
5. Avoid creating indexes on everything. Create index on the relevant fields of every table, and use relations to retrieve the desired data.
6. 避免在一切上创建索引。在每个表的相关字段上创建索引，并使用关系来检索所需的数据。
7. Avoid creating indexes on double fields, unless it is absolutely necessary.
8. 避免在双字段上创建索引，除非绝对必要。
9. Avoid creating indexes on varchar fields, unless it is absolutely necesary.
10. 避免在varchar字段上创建索引，除非绝对必要。

I recommend you to read this: http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

我建议你阅读：http：//dev.mysql.com/doc/refman/5.5/en/using-explain.html

#2

Your JOINS should be the first place to look. The two most obvious candidates for indexes are AccountMapper.AccountingAccount and ChannelMapper.AccountingChannel.

你的JOINS应该是第一个看的地方。两个最明显的索引候选者是AccountMapper.AccountingAccount和ChannelMapper.AccountingChannel。

You should consider indexing Shipments.MarketPlace,Shipments.ShipmentChannel and Shipments.Component as well.

您应该考虑索引Shipments.MarketPlace，Shipments.ShipmentChannel和Shipments.Component。

However, adding indexes increases the workload in maintaining them. While they might give you a performance boost on this query, you might find that updating the tables becomes unacceptably slow. In any case, the MySQL optimiser might decide that a full scan of the table is quicker than accessing it by index.

但是，添加索引会增加维护它们的工作量。虽然它们可能会对此查询提供性能提升，但您可能会发现更新表变得非常缓慢。在任何情况下，MySQL优化器可能会决定对表的完整扫描比通过索引访问它更快。

Really the only way to do this is to set up the indexes that would appear to give you the best result and then benchmark the system to make sure you're getting the results you want here, whilst not compromising the performance elsewhere. Make good use of the EXPLAIN statement to find out what's going on, and remember that optimisations made by yourself or the optimiser on small tables may not be the same optimisations you'd need on larger ones.

实际上，唯一的方法是设置索引，这些索引看起来会给你最好的结果，然后对系统进行基准测试，以确保你在这里得到你想要的结果，同时不影响其他地方的性能。充分利用EXPLAIN语句来了解正在发生的事情，并记住您自己或优化器在小表上进行的优化可能与您在较大表上所需的优化不同。

#3

The other three answers seem to have indexes covered so this is in addition to indexes. You have no where clause which means you are always selecting the whole darn database. In fact, your database design doesn't have anything useful in this regard, such as a shipping date. Think about that.

其他三个答案似乎都覆盖了索引，所以这是索引的补充。你没有where子句，这意味着你总是选择整个数据库。实际上，您的数据库设计在这方面没有任何用处，例如发货日期。考虑一下。

You also have this:

你也有这个：

join (select Component, sum(amount) from Shipment group by component) as Totals
on  Shipment.Component = Totals.Component

That's all well and good but you don't select anything from this subquery. Therefore why do you have it? If you did want to select something, such as the sum(amount), you will have to give that an alias to make it available in the select clause.

这一切都很好，但你没有从这个子查询中选择任何东西。那为什么你呢？如果你确实想要选择一些东西，比如总和（金额），你必须给它一个别名，使其在select子句中可用。