mysql - 查询从几小时开始 --> 4 秒，但我想做得更好 -- 仍然有文件排序

Question

我对系统进行了非规范化处理，从一个需要数小时的查询变成了一个需要 4 秒的查询。该表有 338,293 行。

如果我删除分组依据并求和，则需要 0.4 秒；它要快得多；但随后我必须处理 Web 服务器上的数千行，并且必须发回大量数据。

有没有我可以做的更多索引，或者有什么方法可以让它更快而不使用文件排序或临时表？

下面是查询和架构。

EXPLAIN 
SELECT item_name, category_id, category_name as category, sum(phppos_sales_items.subtotal) as subtotal, sum(phppos_sales_items.total) as total, sum(phppos_sales_items.tax) as tax, sum(phppos_sales_items.profit) as profit, sum(phppos_sales_items.quantity_purchased) as item_sold
    FROM `phppos_sales_items` FORCE INDEX (sales_search)
    WHERE `phppos_sales_items`.`sale_time` BETWEEN '2016-01-01 00:00:00' and '2016-12-31 23:59:59' and
   `phppos_sales_items`.`location_id` IN (1) and 
   `phppos_sales_items`.`suspended` != 2
    AND `deleted` =0
    GROUP BY `category_id`
   LIMIT 500 

+----+-------------+--------------------+-------+----------------------------------------+--------------+---------+------+--------+-------------------------------------------------------------------+
| id | select_type | table              | type  | possible_keys                          | key          | key_len | ref  | rows   | Extra                                                             |
+----+-------------+--------------------+-------+----------------------------------------+--------------+---------+------+--------+-------------------------------------------------------------------+
|  1 | SIMPLE      | phppos_sales_items | range | phppos_sales_items_ibfk_5,sales_search | sales_search | 13      | NULL | 169146 | Using index condition; Using MRR; Using temporary; Using filesort |
+----+-------------+--------------------+-------+----------------------------------------+--------------+---------+------+--------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

和...

mysql> show 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',
  `rule_id` int(10) DEFAULT NULL,
  `rule_discount` decimal(23,10) DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `serialnumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `line` int(11) 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,
  `regular_item_unit_price_at_time_of_sale` decimal(23,10) DEFAULT NULL,
  `discount_percent` decimal(15,3) NOT NULL DEFAULT '0.000',
  `commission` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `is_bogo` tinyint(1) NOT NULL DEFAULT '0',
  `sale_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `suspended` int(1) NOT NULL DEFAULT '0',
  `was_layaway` int(1) NOT NULL DEFAULT '0',
  `was_estimate` int(1) NOT NULL DEFAULT '0',
  `store_account_payment` int(1) NOT NULL DEFAULT '0',
  `location_id` int(11) DEFAULT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `subtotal` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `tax` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `total` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `profit` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  PRIMARY KEY (`sale_id`,`item_id`,`line`),
  KEY `item_id` (`item_id`),
  KEY `phppos_sales_items_ibfk_3` (`rule_id`),
  KEY `item_name` (`item_name`),
  KEY `phppos_sales_items_ibfk_5` (`category_id`),
  KEY `sales_search` (`sale_time`,`location_id`,`deleted`,
                      `suspended`,`was_layaway`,`was_estimate`,
                      `store_account_payment`,`category_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`),
  CONSTRAINT `phppos_sales_items_ibfk_3` FOREIGN KEY (`rule_id`) REFERENCES `phppos_price_rules` (`id`),
  CONSTRAINT `phppos_sales_items_ibfk_4` FOREIGN KEY (`location_id`) REFERENCES `phppos_sales` (`location_id`),
  CONSTRAINT `phppos_sales_items_ibfk_5` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

1 row in set (0.00 sec)

索引状态:

mysql> mysql> show index from phppos_sales_items WHERE Key_name='sales_search';
+--------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| phppos_sales_items |          1 | sales_search |            1 | deleted     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| phppos_sales_items |          1 | sales_search |            2 | location_id | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| phppos_sales_items |          1 | sales_search |            3 | suspended   | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| phppos_sales_items |          1 | sales_search |            4 | sale_time   | A         |      341905 |     NULL | NULL   |      | BTREE      |         |               |
| phppos_sales_items |          1 | sales_search |            5 | category_id | A         |      341905 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

所以我的答案是。我已经用一个包含 1.000.000 行的示例表对其进行了测试，它在更改前 6 秒和更改后 125 毫秒使用

1) 从查询中删除 FORCE INDEX

2) 添加更好的 key

 ALTER TABLE phppos_sales_items 
   add KEY `sales_search2` (`deleted`,`location_id`,`suspended`,`sale_time`,`category_id`);