如何加速SQL查询？索引？

Question

我有以下数据库结构：

create table Accounting
(
  Channel,
  Account
)

create table ChannelMapper
(
  AccountingChannel,
  ShipmentsMarketPlace,
  ShipmentsChannel
)

create table AccountMapper
(
  AccountingAccount,
  ShipmentsComponent
)

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

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

 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

我该如何尽可能地加快此查询速度？ 我应该使用索引吗？如果是，应该在哪些表的哪些列上创建索引？

这是我的查询计划图：

谢谢。

- Attilah

---

索引是必不可少的。在WHERE子句中的任何内容都可以作为索引的候选项。你能发布实际模式而不是抽象版本吗？ - tadman

数据建模至关重要。首先：为列添加一些真实类型（可能是域）。其次：PK / FK约束是基本的。第三：（作为经验法则）如果表似乎具有两个以上的候选键，则它们是可疑的。您的channelmapper和shipments表可能会受到这种现象的影响（可能是BCNF或4NF违规），但您没有显示任何候选键，甚至语义也很模糊。 - wildplasser

Answer 1

索引（Indexes）对于任何数据库都是必不可少的。

通俗易懂地说，索引就是…确切地说，您可以将索引视为第二个隐藏表，它存储两个东西：排序后的数据和指向表中位置的指针。

以下是创建索引的一些规则：

1. 为每个用于连接的字段创建索引。
2. 为您想执行频繁的 where 条件的每个字段创建索引。
3. 避免在所有字段上创建索引。在每个表的相关字段上创建索引，并使用关联检索所需的数据。
4. 除非绝对必要，否则避免在 double 字段上创建索引。
5. 除非绝对必要，否则避免在 varchar 字段上创建索引。

我建议您阅读这篇文章：http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

- Barranka

---

双精度浮点数和字符型变量的索引在它们相关时是可以的。现在已经是2013年了，不再是20世纪80年代了。 - Denis de Bernardy

@Denis 这是一个很好的观点。我避免使用它们，因为通常我可以使用其他索引字段，并获得相同的结果。 (顺便说一下，我并不是那么老;-)) - Barranka

Answer 2

您应该首先查看您的连接（JOINS）。两个最明显的索引候选者是AccountMapper.AccountingAccount和ChannelMapper.AccountingChannel。

您还应该考虑对Shipments.MarketPlace、Shipments.ShipmentChannel和Shipments.Component进行索引。

但是，添加索引会增加维护工作量。尽管它们可能会在此查询上提高性能，但您可能会发现更新表变得不可接受地慢。无论如何，MySQL优化器可能会决定全表扫描比通过索引访问更快。

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

- user1864610

Answer 3

其他三个答案似乎已经涵盖了索引，所以这是补充索引的内容。没有where子句意味着你总是选择整个数据库。事实上，你的数据库设计在这方面没有任何有用的东西，比如运输日期。请考虑一下这个问题。

你还有这个：

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

这样做很好，但是你没有从子查询中选择任何内容。那么你为什么要使用它呢？如果你想选择一些内容，比如sum(amount)，你需要给它一个别名以便在select子句中使用。

- Dan Bracuk