昂贵的查询使数据库服务器瘫痪-寻找缓解方法 [英] expensive query takes down database server -- looking for ways to mitigate
本文介绍了昂贵的查询使数据库服务器瘫痪-寻找缓解方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个非常昂贵的查询，该查询创建一个临时表用于报告.我遇到一个问题，即在其中有50多个查询堆积(全部来自同一用户)的情况下，发生了拒绝服务攻击.这导致数据库服务器几乎被关闭.该查询可能需要1到10秒钟才能运行，并且在生成的临时表中可能有很多行.
I have a very expensive query that creates a temporary table for reporting purposes. I am having a problem where a denial of service attack takes place where 50+ of these queries pile up (All from the same user). This causes the database server to pretty much be taken down. This query can take 1-10 seconds to run and can have a lot of rows in the resulting temporary table.
我不确定是否有一种简便的方法来提高查询性能，而无需重新构建应用程序的整个报告部分.
I am not sure if there is an easy way to improve the query performance without re-architecting the entire reporting piece of the application.
我可以使用哪些解决方案来解决这些查询堆积并关闭服务器的问题.
What are some solutions I can use to apply that will solve the problem with these queries piling up and taking down my server.
我的问题是:
-
您能想到一种提高查询性能的解决方案(以下查询)
Can you think of a solution for improving query performance (query below)
为查询设置会话限制为1是否合理，这样它只能运行一次并防止堆积
Does it make sense to place a session limit of 1 for the query so it can only run once and prevent a pile up
关于如何防止这种攻击的其他任何想法.
Any other ideas on how to prevent this attack.
以下是查询:
CREATE TEMPORARY TABLE phppos_sales_items_temp
(SELECT phppos_sales.deleted as deleted,phppos_sales.deleted_by as deleted_by, sale_time, date(sale_time) as sale_date,
phppos_sales_items.sale_id, comment,payment_type, customer_id, employee_id, phppos_items.item_id, NULL as item_kit_id,
supplier_id, quantity_purchased, item_cost_price, item_unit_price, category, discount_percent,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) as subtotal,
phppos_sales_items.line as line, serialnumber, phppos_sales_items.description as description,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)+(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as total,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) as profit
FROM phppos_sales_items
INNER JOIN phppos_sales ON phppos_sales_items.sale_id=phppos_sales.sale_id
INNER JOIN phppos_items ON phppos_sales_items.item_id=phppos_items.item_id
LEFT OUTER JOIN phppos_suppliers ON phppos_items.supplier_id=phppos_suppliers.person_id
LEFT OUTER JOIN phppos_sales_items_taxes ON phppos_sales_items.sale_id=phppos_sales_items_taxes.sale_id and phppos_sales_items.item_id=phppos_sales_items_taxes.item_id and phppos_sales_items.line=phppos_sales_items_taxes.line
WHERE sale_time BETWEEN "2014-04-01 00:00:00" and "2014-04-30 23:59:59" and phppos_sales.location_id='1' and phppos_sales.store_account_payment=0
GROUP BY sale_id, item_id, line)
UNION ALL
(SELECT phppos_sales.deleted as deleted,phppos_sales.deleted_by as deleted_by, sale_time, date(sale_time) as sale_date,
phppos_sales_item_kits.sale_id, comment,payment_type, customer_id, employee_id, NULL as item_id, phppos_item_kits.item_kit_id, '' as supplier_id,
quantity_purchased, item_kit_cost_price, item_kit_unit_price, category, discount_percent,
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) as subtotal,
phppos_sales_item_kits.line as line, '' as serialnumber, phppos_sales_item_kits.description as description,
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)+(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as total,
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax,
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) - (item_kit_cost_price*quantity_purchased) as profit
FROM phppos_sales_item_kits
INNER JOIN phppos_sales ON phppos_sales_item_kits.sale_id=phppos_sales.sale_id
INNER JOIN phppos_item_kits ON phppos_sales_item_kits.item_kit_id=phppos_item_kits.item_kit_id
LEFT OUTER JOIN phppos_sales_item_kits_taxes ON phppos_sales_item_kits.sale_id=phppos_sales_item_kits_taxes.sale_id and phppos_sales_item_kits.item_kit_id=phppos_sales_item_kits_taxes.item_kit_id and phppos_sales_item_kits.line=phppos_sales_item_kits_taxes.line
WHERE sale_time BETWEEN "2014-04-01 00:00:00" and "2014-04-30 23:59:59" and phppos_sales.location_id='1' and phppos_sales.store_account_payment=0
GROUP BY sale_id, item_kit_id, line)
ORDER BY sale_id, line
说明:
+----------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------------------------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | phppos_sales | range | PRIMARY,location_id,sales_search | sales_search | 12 | NULL | 113 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | phppos_sales_items | ref | PRIMARY,item_id | PRIMARY | 4 | pos.phppos_sales.sale_id | 1 | |
| 1 | PRIMARY | phppos_items | eq_ref | PRIMARY | PRIMARY | 4 | pos.phppos_sales_items.item_id | 1 | |
| 1 | PRIMARY | phppos_suppliers | ref | person_id | person_id | 4 | pos.phppos_items.supplier_id | 1 | Using index |
| 1 | PRIMARY | phppos_sales_items_taxes | ref | PRIMARY,item_id | PRIMARY | 12 | pos.phppos_sales_items.sale_id,pos.phppos_sales_items.item_id,pos.phppos_sales_items.line | 1 | |
| 2 | UNION | phppos_sales_item_kits | ALL | PRIMARY,item_kit_id | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 2 | UNION | phppos_item_kits | eq_ref | PRIMARY | PRIMARY | 4 | pos.phppos_sales_item_kits.item_kit_id | 1 | |
| 2 | UNION | phppos_sales_item_kits_taxes | ref | PRIMARY,item_id | PRIMARY | 12 | pos.phppos_sales_item_kits.sale_id,pos.phppos_sales_item_kits.item_kit_id,pos.phppos_sales_item_kits.line | 1 | |
| 2 | UNION | phppos_sales | eq_ref | PRIMARY,location_id,sales_search | PRIMARY | 4 | pos.phppos_sales_item_kits.sale_id | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------------------------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
组10行(0.00秒)
10 rows in set (0.00 sec)
创建表格:
mysql> show create table phppos_sales_items;
| Table | Create Table |
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_sales_items | CREATE TABLE `phppos_sales_items` (
`sale_id` int(10) NOT NULL DEFAULT '0',
`item_id` int(10) NOT NULL DEFAULT '0',
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`serialnumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`line` int(3) NOT NULL DEFAULT '0',
`quantity_purchased` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
`item_cost_price` decimal(23,10) NOT NULL,
`item_unit_price` decimal(23,10) NOT NULL,
`discount_percent` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`sale_id`,`item_id`,`line`),
KEY `item_id` (`item_id`),
CONSTRAINT `phppos_sales_items_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`),
CONSTRAINT `phppos_sales_items_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `phppos_sales` (`sale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.01 sec)
mysql> show create table phppos_sales;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_sales | CREATE TABLE `phppos_sales` (
`sale_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`customer_id` int(10) DEFAULT NULL,
`employee_id` int(10) NOT NULL DEFAULT '0',
`comment` text COLLATE utf8_unicode_ci NOT NULL,
`show_comment_on_receipt` int(1) NOT NULL DEFAULT '0',
`sale_id` int(10) NOT NULL AUTO_INCREMENT,
`payment_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`cc_ref_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`auth_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
`deleted_by` int(10) DEFAULT NULL,
`deleted` int(1) NOT NULL DEFAULT '0',
`suspended` int(1) NOT NULL DEFAULT '0',
`store_account_payment` int(1) NOT NULL DEFAULT '0',
`location_id` int(11) NOT NULL,
PRIMARY KEY (`sale_id`),
KEY `customer_id` (`customer_id`),
KEY `employee_id` (`employee_id`),
KEY `deleted` (`deleted`),
KEY `location_id` (`location_id`),
KEY `phppos_sales_ibfk_4` (`deleted_by`),
KEY `sales_search` (`location_id`,`store_account_payment`,`sale_time`,`sale_id`),
CONSTRAINT `phppos_sales_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `phppos_locations` (`location_id`),
CONSTRAINT `phppos_sales_ibfk_4` FOREIGN KEY (`deleted_by`) REFERENCES `phppos_employees` (`person_id`),
CONSTRAINT `phppos_sales_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `phppos_employees` (`person_id`),
CONSTRAINT `phppos_sales_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `phppos_customers` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)
mysql> show create table phppos_items;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items | CREATE TABLE `phppos_items` (
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`supplier_id` int(11) DEFAULT NULL,
`item_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`tax_included` int(1) NOT NULL DEFAULT '0',
`cost_price` decimal(23,10) NOT NULL,
`unit_price` decimal(23,10) NOT NULL,
`promo_price` decimal(23,10) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`reorder_level` decimal(23,10) DEFAULT NULL,
`item_id` int(10) NOT NULL AUTO_INCREMENT,
`allow_alt_description` tinyint(1) NOT NULL,
`is_serialized` tinyint(1) NOT NULL,
`image_id` int(10) DEFAULT NULL,
`override_default_tax` int(1) NOT NULL DEFAULT '0',
`is_service` int(1) NOT NULL DEFAULT '0',
`deleted` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`item_id`),
UNIQUE KEY `item_number` (`item_number`),
UNIQUE KEY `product_id` (`product_id`),
KEY `phppos_items_ibfk_1` (`supplier_id`),
KEY `name` (`name`),
KEY `category` (`category`),
KEY `deleted` (`deleted`),
KEY `phppos_items_ibfk_2` (`image_id`),
CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`),
CONSTRAINT `phppos_items_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)
mysql> show create table phppos_suppliers;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_suppliers | CREATE TABLE `phppos_suppliers` (
`person_id` int(10) NOT NULL,
`company_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`account_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`deleted` int(1) NOT NULL DEFAULT '0',
UNIQUE KEY `account_number` (`account_number`),
KEY `person_id` (`person_id`),
KEY `deleted` (`deleted`),
CONSTRAINT `phppos_suppliers_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `phppos_people` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)
mysql> show create table phppos_sales_items_taxes;
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_sales_items_taxes | CREATE TABLE `phppos_sales_items_taxes` (
`sale_id` int(10) NOT NULL,
`item_id` int(10) NOT NULL,
`line` int(3) NOT NULL DEFAULT '0',
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`percent` decimal(15,3) NOT NULL,
`cumulative` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`sale_id`,`item_id`,`line`,`name`,`percent`),
KEY `item_id` (`item_id`),
CONSTRAINT `phppos_sales_items_taxes_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `phppos_sales_items` (`sale_id`),
CONSTRAINT `phppos_sales_items_taxes_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)
推荐答案
嗯，我可能会尝试按照以下方式编写您的查询:
Hmm, I might try writing your query along these lines:
SELECT Sale_Item.deleted, Sale_Item.deleted_by,
Sale_Item.sale_time, Sale_Item.sale_date,
Sale_Item.comment,
Sale_Item.payment_type,
Sale_Item.customer_id,
Sale_Item.employee_id,
Sale_Item.category,
Sale_Item.sale_id, Sale_Item.item_id, NULL as item_kit_id, Sale_Item.line,
Sale_Item.supplier_id,
Sale_Item.serialnumber, Sale_Item.description,
Sale_Item.quantity_purchased, Sale_Item.item_cost_price, Sale_Item.item_unit_price,
Sale_Item.discount_percent,
Sale_Item.lineSubtotal,
Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + (Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + Sale_Item.non_cumulative) * COALESCE(Tax.cumulative, 0) AS lineTax,
Sale_Item.lineSubtotal + (Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + (Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + Sale_Item.non_cumulative) * COALESCE(Tax.cumulative, 0)) AS lineTotal,
Sale_Item.lineSubtotal - (Sale_Item.item_cost_price * Sale_Item.quantity_purchased) AS profit
FROM (SELECT Sale.deleted, Sale.deleted_by,
Sale.sale_time, DATE(Sale.sale_time) AS sale_date,
Sale.comment,
Sale.payment_type,
Sale.customer_id,
Sale.employee_id,
Item.category,
Sale_Item.sale_id, Sale_Item.item_id, NULL as item_kit_id, Sale_Item.line,
Sale_Item.supplier_id,
Sale_Item.serialnumber, Sale_Item.description,
Sale_Item.quantity_purchased, Sale_Item.item_cost_price, Sale_Item.item_unit_price,
Sale_Item.discount_percent,
(Sale_Item.item_unit_price * Sale_Item.quantity_purchased) - (Sale_Item.item_unit_price * Sale_Item.quantity_purchased * Sale_Item.discount_percent / 100) as lineSubtotal
FROM phppos_sales_items Sale_Item
JOIN phppos_sales Sale
ON Sale.sale_id = Sale_Item.sale_id
AND Sale.sale_time >= TIMESTAMP('2014-04-01')
AND Sale.sale_time < TIMESTAMPADD(MONTH, 1, '2014-04-01')
AND Sale.location_id = 1
AND Sale.store_account_payment = 0) Sale_Item
LEFT JOIN (SELECT Tax.sale_id, Tax.item_id, Tax.line,
SUM(CASE WHEN Tax.cumulative = 1 THEN Tax.percent ELSE 0 END) as cumulative,
SUM(CASE WHEN Tax.cumulative <> 1 THEN Tax.percent ELSE 0 END) as non_cumulative
FROM phppos_sales_item_taxes Tax
JOIN phppos_sales Sale
ON Sale.sale_id = Tax.sale_id
AND Sale.sale_time >= TIMESTAMP('2014-04-01')
AND Sale.sale_time < TIMESTAMPADD(MONTH, 1, '2014-04-01')
AND Sale.location_id = 1
AND Sale.store_account_payment = 0
GROUP BY Tax.sale_id, Tax.item_id, Tax.line) Tax
ON Tax.sale_id = Sale_Item.sale_id
AND Tax.item_id = Sale_Item.sale_id
AND Tax.line =Sale_Item.line
出于组织目的移动了几列.这应该对处理时间没有太大影响.
Moved several columns for organizational purposes. This should have no large effect on processing time.
我将对phppos_suppliers的引用删除为:
- 您不使用表中的任何列
- 这是一个
LEFT JOIN，表示您不需要在那里存在行.
- You don't use any columns from the table
- It's a
LEFT JOIN, meaning you don't require rows to exist there.
我将GROUP BY移到了新的子查询中，因为phppos_sales_item_taxes是唯一可以在给定条件下具有重复行的表.我包括了对phppos_sales的引用，因为我不确定MySQL的优化器(或任何其他优化器)是否足够聪明，足以降低citeria的要求.
I moved the GROUP BY into a new subquery, because phppos_sales_item_taxes is the only table that could have duplicate rows for the given criteria. I included the reference to phppos_sales because I'm not sure if MySQL's optimizer (or any, really) is smart enough to push citeria down.
查询的主要部分已经简单地移到了子查询，因此我不需要多次键入lineSubtotal的公式.我一直使用相同的公式，但是有简化的版本:
The main part of the query has been moved to a subquery simply so I wouldn't need to type the formula for lineSubtotal multiple times. I've used the same formulas throughout, but there are simplified versions available:
Sale_Item.item_unit_price * Sale_Item.quantity_purchased * (1 - (Sale_Item.discount_percent / 100)) as lineSubtotal
Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative + Tax.cumulative + Tax.non_cumulative * Tax.cumulative, 0) as Tax
....但是，您可能必须通过记帐来运行它们，因为它们(对于操作顺序而言)往往是(可以理解的)敏感的.这个可能导致运行速度更快，但是我对此表示怀疑.主要是为了将术语简化为更易读的内容.
.... you may have to run these by accounting, though, as they tend to be (understandably) touchy about order of operations. This may result in a faster runtime but I doubt it; mostly this is about simplification of the terms to something more readable.
您没有为查询的另一半提供任何表布局，但我想它是相似的.相关的修改留给读者练习.
You didn't provide any table layouts for the other half of the query, but I presume it's similar. The related modification is left as an exercise for the reader.
除了可能会改变查询速度之外，还可以采取许多措施来解决该问题:
Beyond any potential speedup changing the query might have, there's a number of things you could do to curtail the problem:
- 在您的应用程序层中，强制此查询(以及其他查询)进行作业提交过程，其结果可在以后检索.在上一个查询完成之前，无法运行此查询的新副本.我认为php已经为此提供了一个库.通常，您只需要限制提交即可.
- 正在检索的数据似乎可以缓存-将所有内容存储在最近处理过的
sale_date之前，然后仅即时获取新信息(尽管转换的确不是那么简单 -但是，简单地不进行更多连接可能会有所帮助. - 禁止在当前处理时间范围内进行查询.这应该使系统避免尝试访问尚未提交的行，并有可能远离正在修改的索引页.如果您的存储布置为利用并发I/O，则这种技巧最有效.
- In your application layer, force this query (and possibly others) to go through a job submission process whose results can be retrieved later. A new copy of this query can't be run until the previous one completes. I assume php has an existing library for this. Simply throttling submission in general may be all you need.
- The data being retrieved appears amenable to caching - store everything prior to the most recent processed
sale_date, and then only get new information on-the-fly (although the transformation isn't really that different from the original - however, simply not doing more joins may help). - Disallow queries over the current processing timespan. This should keep the system from attempting to access rows that haven't been committed yet, and potentially away from index pages under modification. This sort of trick works best if your storage is laid out to take advantage of concurrent I/O.
这篇关于昂贵的查询使数据库服务器瘫痪-寻找缓解方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
