如何加速sql查询？索引？ [英] How to speed up sql queries ? Indexes?

问题描述

我有以下数据库结构：

 创建表会计
（
 Channel，
帐户
）
 
创建表ChannelMapper 
（
 AccountingChannel，
 ShipmentsMarketPlace，
 ShipmentsChannel 
）
 
创建表AccountMapper 
（
 AccountingAccount，
 ShipmentsComponent 
）
 
创建表货件
（
 MarketPlace， 
 Component，
 ProductGroup，
 ShipmentChannel，
 Amount 
）
  

我对这些表运行以下查询，我试图优化查询以尽可能快地运行：

 选择Accounting.Channel，Accounting.Account，Shipments.MarketPlace 
从Accounting AccountingChannel的ChannelMapper中AccountChannel = ChannelMapper.AccountingChannel 
 
连接AccountMapper在Accounting.Accounting = ChannelMapper .AccountingAccount 
在
上加入出货（
 ChannelMapper.ShipmentsMarketPlace = Shipments.MarketPlace 
和ChannelMapper.AccountingChannel = Shipments.ShipmentChannel 
和AccountMapper.ShipmentsComponent = Shipments.Component 
）
 join（选择Component，sum（amount）from Shipment group by component）as Totals 
 on Shipment.Component = Totals.Component 
  pre> 
 
 
如何使此查询尽可能快地运行？我应该使用索引吗？如果是，我应该索引哪些表的哪些列？
 
 
 
这是我的查询计划的图片：
 
 
 
  
 
 
 
 p> 
 
 
  
解决方案
索引对任何数据库都是必不可少的。
 
 
 
好吧，正是这样。你可以把一个索引想象成第二个隐藏的表，它存储两个东西：排序的数据和指向它在表中位置的指针。
 
 
 
一些略图规则创建索引：
 
 
在连接中使用的每个字段上创建索引。
 
 
在每个要执行频繁其中条件的字段上创建索引。
 
 
避免在一切上创建索引。在每个表的相关字段上创建索引，并使用关系检索所需的数据。
 
 
避免在 double 除非绝对必要。
 
 
避免在 varchar 字段上创建索引，除非绝对必要。
 
 
我建议您阅读： http://dev.mysql.com/doc/refman/5.5/en/using-explain.html  
 
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,

解决方案
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:

Create indexes on every field that is (or will be) used in joins.
Create indexes on every field on which you want to perform frequent where conditions.
Avoid creating indexes on everything. Create index on the relevant fields of every table, and use relations to retrieve the desired data.
Avoid creating indexes on double fields, unless it is absolutely necessary.
Avoid creating indexes on varchar fields, unless it is absolutely necesary.
I recommend you to read this: http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

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