这是我尝试执行的查询。虽然它运行良好并提供了我需要的信息,但GROUP BY会减慢查询速度。
SELECT
activations.id,
activations.product,
activations.transaction_date AS activation_date,
activations.control_number,
activations.retail AS plan,
activations.account_number,
activations.invoice_date AS act_inv_date,
activations.invoice_number AS act_inv,
activations.commission_status,
commission1.settlement_date AS commission1_date,
commission1.total AS commission1_credit,
commission1.invoice_date AS commission1_inv_date,
commission1.invoice_number AS commission1_inv,
commission2.settlement_date AS commission2_date,
commission2.total AS commission2_credit,
commission2.invoice_date AS commission2_inv_date,
commission2.invoice_number AS commission2_inv,
payment1.transaction_date AS payment1_date,
payment1.retail AS payment1_retail,
payment1.cost AS payment1_cost,
payment1.commission AS payment1_commission,
payment1.account_number AS payment1_account,
payment1.invoice_date AS payment1_inv_date,
payment1.invoice_number AS payment1_inv,
payment2.transaction_date AS payment2_date,
payment2.retail AS payment2_retail,
payment2.cost AS payment2_cost,
payment2.commission AS payment2_commission,
payment2.account_number AS payment2_account,
payment2.invoice_date AS payment2_inv_date,
payment2.invoice_number AS payment2_inv,
payment3.transaction_date AS payment3_date,
payment3.retail AS payment3_retail,
payment3.cost AS payment3_cost,
payment3.commission AS payment3_commission,
payment3.account_number AS payment3_account,
payment3.invoice_date AS payment3_inv_date,
payment3.invoice_number AS payment3_inv,
spiff1.invoice_number AS spiff1_inv,
spiff1.total AS spiff1_credit,
spiff1.invoice_date AS spiff1_inv_date,
spiff2.invoice_number AS spiff2_inv,
spiff2.total AS spiff2_credit,
spiff2.invoice_date AS spiff2_inv_date,
spiff3.invoice_number AS spiff3_inv,
spiff3.total AS spiff3_credit,
spiff3.invoice_date AS spiff3_inv_date
FROM
`commissions_debits` AS activations
LEFT OUTER JOIN
commissions_credits AS commission1
ON
activations.control_number = commission1.control_number
AND
DATE(activations.transaction_date) = commission1.activation_date
AND
commission1.`type` = 'Commission'
LEFT OUTER JOIN
commissions_credits AS commission2
ON
activations.control_number = commission2.control_number
AND
DATE(activations.transaction_date) = commission2.activation_date
AND
commission2.id != commission1.id
AND
commission2.`type` = 'Commission'
LEFT OUTER JOIN
commissions_credits AS spiff1
ON
activations.control_number = spiff1.control_number
AND
DATE(activations.transaction_date) = spiff1.activation_date
AND
spiff1.`type` = 'Spiff'
LEFT OUTER JOIN
commissions_credits AS spiff2
ON
activations.control_number = spiff2.control_number
AND
DATE(activations.transaction_date) = spiff2.activation_date
AND
spiff2.`type` = 'Spiff'
AND
spiff2.id != spiff1.id
LEFT OUTER JOIN
commissions_credits AS spiff3
ON
activations.control_number = spiff3.control_number
AND
DATE(activations.transaction_date) = spiff3.activation_date
AND
spiff3.`type` = 'Spiff'
AND
spiff3.id != spiff2.id
AND
spiff3.id != spiff1.id
LEFT OUTER JOIN
commissions_debits AS payment1
ON
activations.control_number = payment1.control_number
AND
payment1.`type` = 'Payment'
LEFT OUTER JOIN
commissions_debits AS payment2
ON
activations.control_number = payment2.control_number
AND
payment2.`type` = 'Payment'
AND
payment2.id != payment1.id
LEFT OUTER JOIN
commissions_debits AS payment3
ON
activations.control_number = payment3.control_number
AND
payment3.`type` = 'Payment'
AND
payment3.id != payment2.id
AND
payment3.id != payment1.id
WHERE
activations.`type` = 'Activation'
AND
activations.product != 'Simple SIM Act'
GROUP BY
activations.control_number
ORDER BY
activations.transaction_date
因此,它包含以下内容:找到的行:46780警告:0 1查询的持续时间:9.431秒
如果我将GROUP BY更改为activations.transaction_date,它将减少到4秒,但我会丢失一些有效行。
以下是两个表的创建:
CREATE TABLE `commissions_debits` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`settlement_date` DATE NOT NULL,
`product` VARCHAR(300) NOT NULL COLLATE 'utf8_unicode_ci',
`type` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`transaction_date` DATETIME NOT NULL,
`control_number` VARCHAR(15) NOT NULL COLLATE 'utf8_unicode_ci',
`retail` DECIMAL(9,2) NOT NULL,
`discount` DECIMAL(9,4) NOT NULL,
`cost` DECIMAL(9,2) NOT NULL,
`commission` DECIMAL(9,2) NOT NULL,
`account_number` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`invoice_date` DATE NOT NULL,
`invoice_number` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`due_date` DATE NOT NULL,
`commission_status` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `account_number` (`account_number`),
INDEX `type` (`type`),
INDEX `product` (`product`(255)),
INDEX `transaction_date` (`transaction_date`),
INDEX `control_number` (`control_number`),
INDEX `id_type` (`id`, `type`),
INDEX `type_product_control` (`type`, `product`(255), `control_number`),
INDEX `invoice_number` (`invoice_number`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=116672
;
CREATE TABLE `commissions_credits` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`settlement_date` DATE NOT NULL,
`activation_date` DATE NOT NULL,
`type` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`item_description` VARCHAR(300) NOT NULL COLLATE 'utf8_unicode_ci',
`control_number` VARCHAR(15) NOT NULL COLLATE 'utf8_unicode_ci',
`dealer_code` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`debit` DECIMAL(9,2) NOT NULL,
`credit` DECIMAL(9,2) NOT NULL,
`tax` DECIMAL(9,2) NOT NULL,
`total` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`account_number` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`invoice_date` DATE NOT NULL,
`invoice_number` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`due_date` DATE NOT NULL,
PRIMARY KEY (`id`),
INDEX `dealer_code` (`dealer_code`),
INDEX `account_number` (`account_number`),
INDEX `type` (`type`),
INDEX `control_number` (`control_number`),
INDEX `date_type` (`activation_date`, `type`),
INDEX `date_id_type` (`activation_date`, `id`, `type`),
INDEX `invoice_number` (`invoice_number`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=72568
;
解释:
|| *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* ||
|| 1 || SIMPLE || activations || ref || type,product,type_product_control || type || 302 || const || 50997 || Using where; Using temporary; Using filesort ||
|| 1 || SIMPLE || commission1 || ref || type,control_number,date_type,date_id_type || control_number || 47 || payst.activations.control_number || 1 || ||
|| 1 || SIMPLE || commission2 || ref || type,control_number,date_type,date_id_type || control_number || 47 || payst.activations.control_number || 1 || ||
|| 1 || SIMPLE || spiff1 || ref || type,control_number,date_type,date_id_type || control_number || 47 || payst.activations.control_number || 1 || ||
|| 1 || SIMPLE || spiff2 || ref || type,control_number,date_type,date_id_type || control_number || 47 || payst.activations.control_number || 1 || ||
|| 1 || SIMPLE || spiff3 || ref || type,control_number,date_type,date_id_type || control_number || 47 || payst.activations.control_number || 1 || ||
|| 1 || SIMPLE || payment1 || ref || type,control_number,type_product_control || control_number || 47 || payst.activations.control_number || 1 || ||
|| 1 || SIMPLE || payment2 || ref || type,control_number,type_product_control || control_number || 47 || payst.activations.control_number || 1 || ||
|| 1 || SIMPLE || payment3 || ref || type,control_number,type_product_control || control_number || 47 || payst.activations.control_number || 1 || ||
也许我可以以某种方式提高指数?如有任何帮助,我们将不胜感激。
activations.control_number
没有索引。尝试为该列添加索引,然后如果出现负面或正面问题,请通知我。
执行计划看起来不错,对每个联接使用单个索引和ref
类型访问。没有对多个索引进行合并操作,这也很好。
注意Using temporary
和Using filesort
备注。
Using temporary
是什么意思
数据太大,无法在内存中处理,因此创建了一个(计算成本高昂)临时表来执行SORT
。如果使用服务器分发的默认配置,这是正常的。对于成长和成熟的生产数据库来说,innodb缓冲区通常太小。如果可以的话,可以使用服务器参数和InnoDB参数。
这里的重要参数是:
- 排序缓冲区大小
- innodb_buffer_pool_size
- 读取缓冲区大小
Using filesort
是什么意思
此处使用的索引(control_number
)不会按SORT
和GROUP
语句所需的顺序返回行。您可以通过使用基于SORT
列的索引来提高这些列的性能,该索引的顺序与SORT
语句的顺序相同。
其中一个应该做到这一点:
INDEX `foo` (`control_number`, `transaction_date`)
INDEX `foo2` (`transaction_date`, `control_number`)
手册上说:
为了提高ORDERBY的速度,请检查是否可以让MySQL使用索引,而不是额外的排序阶段。如果这不可能,你可以尝试以下策略:
增加sort_buffer_size变量值。
增加read_rnd_buffer_size变量值。
每行使用更少的RAM,只声明存储值所需的列大小。例如,如果值从不超过16个字符,则CHAR(16)比CHAR(200)更好。
更改tmpdir系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的几个路径;您可以使用此功能将负载分散到多个目录中。路径应在Unix上用冒号(":")分隔,在Windows、NetWare和OS/2上用分号(";")分隔。路径应该命名位于不同物理磁盘上的文件系统中的目录,而不是同一磁盘上的不同分区。