如何加快sql查询?索引?

2021-12-20 00:00:00 optimization sql database mysql

我有以下数据库结构:

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 :

谢谢，

推荐答案

索引对于任何数据库都是必不可少的.

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. 在连接中使用(或将使用)的每个字段上创建索引.
  2. 在要对其执行频繁 where 条件的每个字段上创建索引.
  3. 避免为所有内容创建索引.在每个表的相关字段上创建索引，并使用关系检索所需数据.
  4. 避免在 double 字段上创建索引，除非绝对必要.
  5. 避免在 varchar 字段上创建索引，除非绝对必要.
  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 where conditions.
  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 double fields, unless it is absolutely necessary.
  5. Avoid creating indexes on varchar fields, unless it is absolutely necesary.

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

相关文章