MySQL 如何加快sql查询？索引？

How to speed up sql queries ? Indexes?

提问by Attilah

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,

谢谢，

回答by Barranka

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. Create indexes on every field on which you want to perform frequent whereconditions.
3. Avoid creating indexes on everything. Create index on the relevant fields of every table, and use relations to retrieve the desired data.
4. Avoid creating indexes on doublefields, unless it is absolutely necessary.
5. Avoid creating indexes on varcharfields, unless it is absolutely necesary.

1. 在连接中使用（或将使用）的每个字段上创建索引。
2. 在要对其执行频繁where条件的每个字段上创建索引。
3. 避免在所有内容上创建索引。在每个表的相关字段上创建索引，并使用关系检索所需的数据。
4. 避免在double字段上创建索引，除非绝对必要。
5. 避免在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

回答by Attilah

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

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

You should consider indexing Shipments.MarketPlace,Shipments.ShipmentChanneland Shipments.Componentas 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 EXPLAINstatement 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语句来找出发生了什么，并记住您自己或优化器对小表所做的优化可能与您在大表上需要的优化不同。

回答by Dan Bracuk

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.

这一切都很好，但您没有从这个子查询中选择任何内容。所以你为什么拥有它？如果您确实想选择某些内容，例如 sum(amount)，则必须为其指定别名以使其在 select 子句中可用。