MySQL ISAM搜索优化

Question

我有一个包含1,019,502条记录的表和一个运行时间为1.6秒的特定查询。如果可能的话，我想减少运行时间。

该表是MySQL 5.7上的INNODB (在Ubuntu上)：

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 |
+----+-------------+--------------+------------+------+----------------------------------+---------+---------+-------------+--------+----------+----------------------------------------------+

Answer 1

where子句中唯一的常量部分包括：

WHERE propId = @propId
AND dateType = 'day'
AND elemType = @elemType

因此，声明一个涉及到这3个字段的非唯一复合索引可能会有一些好处(nb:我不确定是否能在这样的索引中指定这些列的顺序，这可能需要一些实验)，我会在定义这样的索引后尝试解释，但在试验时确保这些变量保持为NULL：

@sku = NULL,
@family = NULL,
@subcategory = NULL,
@category = NULL

如果综合指数有任何改进，现在尝试将这4个变量中的任何一个变量设为非空。这对你的解释计划有什么影响？然后，您可能会发现，为了支持where子句的可变性，您需要对这些列中的每一列分别使用非唯一索引。

也就是说，当你改变变量时，解释计划也会不同。

但是:在超过100万行中，大约1.6秒，您就进入了收益递减的领域。