mysql - 合并分区如何增加扫描的行数？

Question

我有一个包含 4.2+M 条记录的表，我想知道分区是否可以帮助我提高查询性能，所以我进行了测试。定义了适当的索引后，我复制了该表，然后我对第二个表进行了分区。所以现在我有两个相同的表；一个没有分区，另一个有。

这是我的表的结构(简化):

CREATE TABLE `cse` (
  `id` bigint(20) unsigned NOT NULL,
  `type` varchar(45) DEFAULT NULL,
  `name` varchar(1000) NOT NULL,
  `dt` datetime NOT NULL,
  PRIMARY KEY (`id`,`dt`),
  KEY `inx1` (`type`),
  KEY `inx2` (`type`,`dt`),
  KEY `inx3` (`dt`,`name`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下面是我如何划分副本:

ALTER TABLE cse_p PARTITION BY RANGE COLUMNS (dt) (
    PARTITION p11_09 VALUES LESS THAN ('2011-09-01'),
    PARTITION p11_10 VALUES LESS THAN ('2011-10-01'),
    PARTITION p11_11 VALUES LESS THAN ('2011-11-01'),
    PARTITION p11_12 VALUES LESS THAN ('2011-12-01'),
    PARTITION p12_01 VALUES LESS THAN ('2012-01-01'),
    PARTITION p12_02 VALUES LESS THAN ('2012-02-01'),
    PARTITION p12_03 VALUES LESS THAN ('2012-03-01'),
    PARTITION p12_04 VALUES LESS THAN ('2012-04-01'),
    PARTITION p12_05 VALUES LESS THAN ('2012-05-01'),
    PARTITION p12_06 VALUES LESS THAN ('2012-06-01'),
    PARTITION p12_07 VALUES LESS THAN ('2012-07-01'),
    PARTITION p12_08 VALUES LESS THAN ('2012-08-01'),
    PARTITION p12_09 VALUES LESS THAN ('2012-09-01'),
    PARTITION p12_10 VALUES LESS THAN ('2012-10-01'),
    PARTITION p12_11 VALUES LESS THAN ('2012-11-01'),
    PARTITION p12_12 VALUES LESS THAN ('2012-12-01'),
    PARTITION p13_01 VALUES LESS THAN ('2013-01-01'),
    PARTITION p13_02 VALUES LESS THAN ('2013-02-01'),
    PARTITION p13_03 VALUES LESS THAN ('2013-03-01'),
    PARTITION p13_04 VALUES LESS THAN ('2013-04-01'),
    PARTITION p13_05 VALUES LESS THAN ('2013-05-01'),
    PARTITION p13_06 VALUES LESS THAN ('2013-06-01'),
    PARTITION p13_07 VALUES LESS THAN ('2013-07-01'),
    PARTITION p13_08 VALUES LESS THAN ('2013-08-01'),
    PARTITION p13_09 VALUES LESS THAN ('2013-09-01'),
    PARTITION p13_10 VALUES LESS THAN ('2013-10-01'),
    PARTITION p13_11 VALUES LESS THAN ('2013-11-01'),
    PARTITION p13_12 VALUES LESS THAN ('2013-12-01'),
    PARTITION p_rest VALUES LESS THAN (MAXVALUE)
);

这是每个分区的基数(我知道!):

SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'cse_p';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p11_09         |    1030353 |
| p11_10         |     577326 |
| p11_11         |          0 |
| p11_12         |          0 |
| p12_01         |          0 |
| p12_02         |          0 |
| p12_03         |     601575 |
| p12_04         |     766727 |
| p12_05         |     855438 |
| p12_06         |     262869 |
| p12_07         |          0 |
| p12_08         |          0 |
| p12_09         |          0 |
| p12_10         |          0 |
| p12_11         |          0 |
| p12_12         |          0 |
| p13_01         |          0 |
| p13_02         |          0 |
| p13_03         |          0 |
| p13_04         |          0 |
| p13_05         |          0 |
| p13_06         |          0 |
| p13_07         |          0 |
| p13_08         |          0 |
| p13_09         |          0 |
| p13_10         |          0 |
| p13_11         |          0 |
| p13_12         |          0 |
| p_rest         |          0 |
+----------------+------------+

设置主干后，我使用以下查询测试了两个表的性能:

EXPLAIN PARTITIONS
SELECT DATE(dt), name, COUNT(*) AS count
FROM cse
WHERE (type = 'A' OR type = 'B' OR type = 'C')
AND dt > '2012-04-01'
AND dt < '2012-05-01'
GROUP BY DATE(dt), name;

下面是上述查询对两个表的输出:

cse

+----+-------------+-------+------------+-------+----------------+------+---------+------+------+--------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key  | key_len | ref  | rows | Extra                                                  |
+----+-------------+-------+------------+-------+----------------+------+---------+------+------+--------------------------------------------------------+
|  1 | SIMPLE      | cse   | NULL       | range | inx1,inx2,inx3 | inx2 | 143     | NULL | 4919 | Using index condition; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+----------------+------+---------+------+------+--------------------------------------------------------+

cse_p

+----+-------------+-------+------------+-------+----------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+------------+-------+----------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | cse_p | p12_05     | range | inx1,inx2,inx3 | inx2 | 143     | NULL | 7736 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+----------------+------+---------+------+------+----------------------------------------------+

问题(最后)

为什么引入分区会增加扫描的行数，而在这两种情况下都使用相同的索引？

[更新]

因为我忘了提及 MySQL 的版本，它是 5.6.16-1+sury.org~precise+1 - (Ubuntu)。

Answer 1

看起来非分区表正在使用ICP .如链接文档中所述，MySQL 5.6 及以下版本不支持分区表上的 ICP。该问题已在 5.7 中解决。你能确认你的MySQL版本吗？

这应该可以解决问题本身，但更重要的是，这个查询还可以做其他事情。

type、dt 和 name 的三列索引可能会显着提高性能。 MySQL 中真正的 killer 通常是文件排序。摆脱文件排序通常比减少行数更有帮助。

实际上，如果您不需要对结果进行排序，您可以通过在查询末尾添加 ORDER BY NULL 来提高性能。 MySQL 中的 GROUP BY 按与组相同的字段隐式排序，如果不需要，这有时会降低性能。告诉它您不关心顺序(如果您真的不关心)可能会阻止文件排序。