我有一个销售数据表，其中平均每天插入1329415行。我必须每天从表中生成不同格式的报告。但是表中的查询太慢了。下面是我的show create table命令输出。
CREATE TABLE `query_manager_table` (
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`region_id` int(2) NOT NULL,
`rtslug` varchar(10) DEFAULT NULL,
`dsid` int(3) NOT NULL,
`dpid` int(3) NOT NULL,
`route_number` int(4) NOT NULL,
`route_id` int(11) NOT NULL,
`rtlid` int(11) NOT NULL,
`retailer_code` varchar(16) DEFAULT NULL,
`platform_code` varchar(16) DEFAULT NULL,
`prid` int(4) NOT NULL,
`skid` int(4) NOT NULL,
`group` int(4) NOT NULL,
`family` int(4) NOT NULL,
`volume` float DEFAULT NULL,
`value` float(7,2) DEFAULT NULL,
`date` date NOT NULL DEFAULT '0000-00-00',
`outlets` int(4) NOT NULL,
`visited` int(4) NOT NULL,
`channel` int(3) DEFAULT NULL,
`subchannel` int(3) DEFAULT NULL,
`tpg` int(4) DEFAULT NULL,
`ioq` int(10) DEFAULT NULL,
`sales_time` int(11) DEFAULT NULL,
PRIMARY KEY (`dpid`,`route_id`,`rtlid`,`prid`,`skid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (YEAR(date) * 100 + QUARTER(date))
(PARTITION y2017q1 VALUES IN (201701) ENGINE = InnoDB,
PARTITION y2017q2 VALUES IN (201702) ENGINE = InnoDB,
PARTITION y2017q3 VALUES IN (201703) ENGINE = InnoDB,
PARTITION y2017q4 VALUES IN (201704) ENGINE = InnoDB,
PARTITION y2018q1 VALUES IN (201801) ENGINE = InnoDB,
PARTITION y2018q2 VALUES IN (201802) ENGINE = InnoDB,
PARTITION y2018q3 VALUES IN (201803) ENGINE = InnoDB,
PARTITION y2018q4 VALUES IN (201804) ENGINE = InnoDB,
PARTITION y2019q1 VALUES IN (201901) ENGINE = InnoDB,
PARTITION y2019q2 VALUES IN (201902) ENGINE = InnoDB,
PARTITION y2019q3 VALUES IN (201903) ENGINE = InnoDB,
PARTITION y2019q4 VALUES IN (201904) ENGINE = InnoDB) */现在我只想通过下面的查询来了解从9月1日到9月9日零售商的销售额-
SELECT
query_manager_table.dpid,
query_manager_table.route_id,
query_manager_table.rtlid,
query_manager_table.prid,
SUM(query_manager_table.`volume`) AS sales,
1 AS memos
FROM
query_manager_table
WHERE
query_manager_table.date BETWEEN '2018-09-01'
AND '2018-09-08'
GROUP BY
query_manager_table.dpid,
query_manager_table.rtlid,
query_manager_table.date但大约需要500-700秒。我补充说 dpid IN (1,2,.....) 以及 prid IN (1,2,....) 因为两个文件都作为主键添加。300秒后输出。我做错了什么？
+----+-------------+---------------------+------+---------------+------+---------+------+-----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+------+-----------+----------------------------------------------+
| 1 | SIMPLE | query_manager_table | ALL | PRIMARY | NULL | NULL | NULL | 129065467 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+------+---------------+------+---------+------+-----------+----------------------------------------------+当我在where条件中添加所有dpid和prid时，expain看起来像
+----+-------------+---------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | query_manager_table | range | PRIMARY | PRIMARY | 4 | NULL | 128002 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+有没有办法优化表或查询？如果我运行解释分区选择。。。对于第一个然后得到-
+----+-------------+---------------------+-------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+-----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+-----------+----------------------------------------------+
| 1 | SIMPLE | query_manager_table | y2017q1,y2017q2,y2017q3,y2017q4,y2018q1,y2018q2,y2018q3,y2018q4,y2019q1,y2019q2,y2019q3,y2019q4 | ALL | PRIMARY | NULL | NULL | NULL | 127129410 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+-------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+-----------+----------------------------------------------+我得到的第二个-
+----+-------------+---------------------+-------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | query_manager_table | y2017q1,y2017q2,y2017q3,y2017q4,y2018q1,y2018q2,y2018q3,y2018q4,y2019q1,y2019q2,y2019q3,y2019q4 | range | PRIMARY | PRIMARY | 4 | NULL | 153424 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+-------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
2条答案
按热度按时间g52tjvyc1#
INDEXes用于提高SELECTs.那个
PRIMARY KEY（在mysql中）定义为INDEX. 它应该具有唯一标识行的最小列集。任何唯一索引（包括pk）也是一个“唯一性约束”--这可以防止插入具有同一组if值的多行。
索引是“从左边”使用的。也就是说，用
INDEX(a,b)，如果a是没有用的，它不会到达b.PARTITION BY LIST实际上是没用的。它很少（如果有的话）提高性能。您向我们展示了几个问题；让我们看看更多的典型查询，这样我们就可以帮助您进行索引和分区。乞求
INDEX(date). 在复合索引中，不会到达“range”后面的列。也就是说，在INDEX(date, x, y)，测试date对于一个范围（例如WHERE)，不会让它利用x或者y. 另一方面，WHERE date = '2018-09-01' AND x=1将使用更多的索引。float(7,2)--不要使用(m,n)选项开启FLOAT或者DOUBLE. 相反，切换到DECIMAL.INT总是4字节。看到了吗TINYINT（1字节），SMALLINT（2字节）等。仅此一项就可以将表的大小减半。要解释这一点：
设法对伪范围使用第一个（记住：“最左边的”）
IN，但不能在pk中使用任何其他内容，因为route_id是下一个。这就解释了为什么第二个
EXPLAIN有一个较小的“行”。另外，请注意“key\u len”中的“4”--这是中的字节数dpid.在进行了一些更改之后，请回来讨论使用摘要表来加快速度。然而，“修改”可能会导致这种优化的复杂性。
你有多少公羊？它的价值是什么
innodb_buffer_pool_size?除非必须，否则不要使用guid；由于随机性，它们会减慢大table上的动作。
webghufk2#
我不会结合实际数据字段来生成主键。我将使用一个字段，并使用一个自动递增的整数，或者使用一个guid作为值。必须通过六个字段来识别一个唯一的记录要比通过一个字段花费更多的时间，而且正如您所说的，如果用户正在输入关键数据，则会有重复字段的风险。
如果您有业务原因使这六个字段在一起时具有唯一性，那么您还应该制定一个例程，以确定插入的记录是否与这些字段的现有记录重复。如果您是批量插入，您将希望在插入记录后执行此操作，而不是在插入记录时检查每个记录。您还需要索引这六个字段，以加快对重复项的查询。
至于你的
SELECT查询时，可能需要索引WHERE条款。在任何情况下，您都需要阅读执行计划，并尝试使用不同的索引和键结构（可能更容易在数据的子集上进行）。谷歌“mysql执行计划”提供了很多信息。