我有一个包含 1,019,502 条记录的表和一个需要 1.6 秒才能运行的特定查询。如果可能的话，我想减少运行时间。
该表是MySQL 5.7(在Ubuntu上(上的INNODB：
mysql> describe summary_data;
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| propId | int(10) unsigned | NO | PRI | NULL | |
| elemType | varchar(50) | NO | PRI | NULL | |
| sku | varchar(100) | NO | PRI | NULL | |
| family | varchar(100) | NO | PRI | NULL | |
| subcategory | varchar(100) | NO | PRI | NULL | |
| category | varchar(100) | NO | PRI | NULL | |
| details | varchar(255) | YES | | NULL | |
| merchSales | float(12,2) | YES | | NULL | |
| orders | int(10) unsigned | YES | | NULL | |
| quantity | int(10) unsigned | YES | | NULL | |
| margin | float(12,2) | YES | | NULL | |
| grossSales | float(12,2) | YES | | NULL | |
| discount | float(12,2) | YES | | NULL | |
| shipping | float(12,2) | YES | | NULL | |
| tax | float(12,2) | YES | | NULL | |
| createDate | datetime | YES | | NULL | |
| date | date | NO | PRI | NULL | |
| dateType | varchar(10) | NO | PRI | NULL | |
+--------------+------------------+------+-----+---------+-------+
查询如下：
SET @propId = 1,
@from = '2016-01-01',
@to = '2016-12-31',
@elemType = 'sku',
@sku = NULL,
@family = NULL,
@subcategory = NULL,
@category = NULL;
SELECT SUM(ifnull(merchSales,0)+ifnull(discount,0)) as totalSales
,SUM(ifnull(merchSales,0)) as merchSales
,SUM(ifnull(orders,0)) as orders
,SUM(ifnull(quantity,0)) as quantity
,sum(ifnull(grossSales,0)) as grossSales
,sum(ifnull(discount,0))*(-1) as discount
,sum(ifnull(shipping,0)) as shipping
,elemType
,sku
,family
,category
,subcategory
,details
,SUM(ifnull(margin,0)) as margin
,sum(ifnull(margin,0)) / sum(ifnull(merchSales,0))*100 as marginPerc
,SUM(ifnull(grossSales,0))/SUM(ifnull(orders,0)) as avgOrderVal
,sum(ifnull(merchSales,0)+ifnull(discount,0))/sum(ifnull(margin,0))*100 as marginPercTotal
FROM summary_data
WHERE propId = @propId
AND dateType = 'day'
AND elemType = @elemType
AND (@sku IS NULL OR sku = @sku)
AND (@family IS NULL OR family = @family)
AND (@subcategory IS NULL OR subcategory = @subcategory)
AND (@category IS NULL OR category = @category)
GROUP BY category,subcategory,family,sku
ORDER BY merchSales DESC;
查询使用的索引：
mysql> show indexes from summary_data;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| summary_data | 0 | PRIMARY | 1 | propId | A | 218 | NULL | NULL | | BTREE | | |
| summary_data | 0 | PRIMARY | 2 | elemType | A | 1529 | NULL | NULL | | BTREE | | |
| summary_data | 0 | PRIMARY | 3 | category | A | 5528 | NULL | NULL | | BTREE | | |
| summary_data | 0 | PRIMARY | 4 | subcategory | A | 11198 | NULL | NULL | | BTREE | | |
| summary_data | 0 | PRIMARY | 5 | family | A | 15678 | NULL | NULL | | BTREE | | |
| summary_data | 0 | PRIMARY | 6 | sku | A | 17470 | NULL | NULL | | BTREE | | |
| summary_data | 0 | PRIMARY | 7 | dateType | A | 17470 | NULL | NULL | | BTREE | | |
| summary_data | 0 | PRIMARY | 8 | date | A | 985490 | NULL | NULL | | BTREE | | |
查询使用 1,019,502 条记录中的大约 115,000 条。结果返回 2106 个聚合行。
任何建议将不胜感激！
*****编辑*****
添加解释：
+----+-------------+--------------+------------+------+----------------------------------+---------+---------+-------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+----------------------------------+---------+---------+-------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | summary_data | NULL | ref | PRIMARY,propId_4,propId_5,propId | PRIMARY | 156 | const,const | 492745 | 10.00 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------------+------+----------------------------------+---------+---------+-------------+--------+----------+----------------------------------------------+
where 子句的唯一常量部分涉及：
WHERE propId = @propId
AND dateType = 'day'
AND elemType = @elemType
因此，声明涉及这 3 个字段 propid、elemtype、datetype 的非唯一复合索引可能会有一些优势(注意：我不相信我可以在这样的索引中指定这些列的顺序，这可能需要一些经验(，我会在定义这样的索引后尝试解释，但请确保这些变量在试用时保持 NULL：
@sku = NULL,
@family = NULL,
@subcategory = NULL,
@category = NULL
如果该复合索引有任何改进，现在尝试使这 4 个变量中的任何一个为非 null。对您的解释计划有什么影响？然后，您可能会发现您需要在每个列上单独使用非唯一索引，以帮助支持 where 子句的可变性。
即，当您更改变量时，解释计划也会有所不同。
但是：从>100 万行开始 ~1.6 秒，您将进入收益递减的领域。