KoukiВот таблица поменьше (7 млн записей):
CREATE TABLE AbonentWH.Payment (
PaymentID int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор оплаты',
PublicUtilitiesID tinyint(4) UNSIGNED NOT NULL COMMENT 'Идентификатор организации ЖКХ',
PaymentSourceID smallint(6) UNSIGNED NOT NULL COMMENT 'Идентификатор источника оплаты',
PaymentMethodID smallint(6) UNSIGNED NOT NULL COMMENT 'Идентификатор способа оплаты',
DateDiscountID smallint(6) UNSIGNED NOT NULL COMMENT 'Идентификатор даты учета оплаты',
DatePaymentID smallint(6) UNSIGNED NOT NULL COMMENT 'Идентификатор фактической даты оплаты',
DateID smallint(6) UNSIGNED NOT NULL COMMENT 'Идентификатор даты, за которую осуществлена оплата',
ServiceID smallint(6) UNSIGNED NOT NULL COMMENT 'Идентификатор услуги',
AbonentID int(11) UNSIGNED NOT NULL COMMENT 'Идентификатор абонента',
SubsidyID tinyint(4) UNSIGNED NOT NULL COMMENT 'Идентификатор субсидирования',
ContractorID smallint(6) UNSIGNED NOT NULL COMMENT 'Идентификатор внешней организации',
PaymentTypeID tinyint(4) UNSIGNED NOT NULL COMMENT 'Идентификатор типа оплаты',
PrimaryDocumentCode int(11) UNSIGNED NOT NULL COMMENT 'Код первичного документа об оплате',
TicketCode int(11) UNSIGNED NOT NULL COMMENT 'Код квитанции об оплате',
DiscountDay tinyint(4) UNSIGNED NOT NULL COMMENT 'День учета оплаты',
DiscountMonth tinyint(4) UNSIGNED NOT NULL COMMENT 'Месяц учета оплаты',
DiscountYear smallint(6) UNSIGNED NOT NULL COMMENT 'Год учета оплаты',
PaymentSum decimal(18, 4) NOT NULL COMMENT 'Сумма оплаты',
RegistrationDate date NOT NULL COMMENT 'Дата регистрации записи в ХД',
PRIMARY KEY (PaymentID),
INDEX IDX_Payment_AbonentID (AbonentID),
INDEX IDX_Payment_ContractorID (ContractorID),
INDEX IDX_Payment_DateDiscountID (DateDiscountID),
INDEX IDX_Payment_DateID (DateID),
INDEX IDX_Payment_DatePaymentID (DatePaymentID),
INDEX IDX_Payment_PaymentMethodID (PaymentMethodID),
INDEX IDX_Payment_PaymentSourceID (PaymentSourceID),
INDEX IDX_Payment_PaymentTypeID (PaymentTypeID),
INDEX IDX_Payment_ServiceID (ServiceID),
INDEX IDX_Payment_SubsidyID (SubsidyID)
)
ENGINE = MYISAM
Пример запроса:
select
`Service`.`ResourceName` as `c0`,
`Service`.`ServiceName` as `c1`,
`PaymentSource`.`PaymentSourceName` as `c2`,
`Date_2`.`Year` as `c3`,
`Date_2`.`Quarter` as `c4`,
`Date_2`.`Month` as `c5`,
`Date_2`.`Day` as `c6`,
sum(`Payment`.`PaymentSum`) as `m0`,
count(distinct `Payment`.`TicketCode`) as `m1`
from
`Service` as `Service`,
`Payment` as `Payment`,
`PaymentSource` as `PaymentSource`,
`Date` as `Date_2`
where
`Payment`.`ServiceID` = `Service`.`ServiceID`
and
`Service`.`ResourceName` in ('Водоотведение', 'Горячее водоснабжение', 'Холодное водоснабжение')
and
`Service`.`ServiceName` in ('Водоотведение', 'Горячая вода', 'Холодная вода')
and
`Payment`.`PaymentSourceID` = `PaymentSource`.`PaymentSourceID`
and
`PaymentSource`.`PaymentSourceName` in ('КВЦ', 'Касса', 'Ошибочно проведенная оплата', 'Перерасчет квитанций', 'По суду', 'Реестры оплаты из ЦОКП', 'Списание по приказу')
and
`Payment`.`DateDiscountID` = `Date_2`.`DateID`
AND Date_2.yesr_mon = '2013-03'
and
`Date_2`.`Year` = 2013
and
`Date_2`.`Quarter` = 3
and
`Date_2`.`Month` = '9'
group by
`Service`.`ResourceName`,
`Service`.`ServiceName`,
`PaymentSource`.`PaymentSourceName`,
`Date_2`.`Year`,
`Date_2`.`Quarter`,
`Date_2`.`Month`,
`Date_2`.`Day`
ПрофилированиеSorting result 9,488223 83,72
Copying to tmp table 1,447273 12,77
Sending data 0,344958 3,04
statistics 0,038657 0,34
removing tmp table 0,009193 0,08
Creating tmp table 0,003196 0,03
freeing items 0,000843 0,01
starting 0,000168 0,00
init 0,000074 0,00
end 0,000071 0,00
preparing 0,000070 0,00
Opening tables 0,000058 0,00
optimizing 0,000025 0,00
checking permissions 0,000016 0,00
System lock 0,000015 0,00
closing tables 0,000014 0,00
logging slow query 0,000006 0,00
executing 0,000005 0,00
cleaning up 0,000004 0,00
query end 0,000002 0,00
План
1.
2.
3.
Date_2 1 SIMPLE index_merge PRIMARY,IDX_Date_Quarter,IDX_Date_Year,IDX_Date_Month IDX_Date_Year,IDX_Date_Month 2,1 32 Using intersect(IDX_Date_Year,IDX_Date_Month); Using where; Using temporary; Using filesort
Payment 1 SIMPLE ref IDX_Payment_DateDiscountID,IDX_Payment_PaymentSourceID,IDX_Payment_ServiceID IDX_Payment_DateDiscountID 2 AbonentWH.Date_2.DateID 12104
Service 1 SIMPLE eq_ref PRIMARY PRIMARY 2 AbonentWH.Payment.ServiceID 1 Using where
PaymentSource 1 SIMPLE eq_ref PRIMARY PRIMARY 2 AbonentWH.Payment.PaymentSourceID 1 Using where
судя по плану у вас 12К записей в день. в запросе 1 месяц - 30 дней , значит 400К записей.
Основное время 10 сец, 83,72% занял сортинг -- похоже на сортировку для
группировки на диске.
Опятже, эта проблема с сортировкой может пропасть после
Мондриан оптимизации (например с использованием сквозного индекса по YEAR_MON).
А если останется -- надо думать про настройки сервера (наконецто!).
Тут надо попросить более опытных товаришей в плане расчетов
ресурсов: как настроить сорт буфера для
400К записей типа AbonentWH.Payment
(очевидно надо заменить 3 поля для ДискаунтДатеДей/Монс/Уеар
на ДискаунтДатеИД, или это разные ДИскаунты???)
Сами тоже можете попробовать настроить буфера с помошью утилиты:
wget mysqltuner.pl
perl mysqltuner.pl
