我有一个有 300k 行的表。该表非常重,因此每次查询都会变慢。在尝试了很多索引和其他优化之后,我决定在表上创建分区。
现在我有 3 个版本的表格
- e_update
- e_update_partition(20 个分区使用 HASH(在 event_id 上))
- e_update_partition_event(12 个分区,每个分区范围 25K 条目(event_id) )
现在我正在每个表上逐个运行相同的查询并比较时间
SELECT eu.event_id
FROM e_update eu
INNER JOIN event e ON e.id=eu.event_id
WHERE eu.start_date > 2010-10-15
AND e.published=1
AND eu.event_id > 25000
AND eu.event_id < 50000;
所用时间- 189911 行,2 个警告(14.43 秒)
SELECT eu.event_id
FROM e_update_partition eu
INNER JOIN event e ON e.id=eu.event_id
WHERE eu.start_date > 2010-10-15
AND e.published=1
AND eu.event_id > 25000
AND eu.event_id < 50000;
所用时间- 189911 行,2 个警告(15.87 秒)
解释结果-
+----+-------------+-------+-----------------------------------------------------------------------+-------+--------------------------------+-----------+---------+--------------------+--------+-----------------------+ |身份证 |select_type |表 |分区 |类型 |possible_keys |键 |key_len |参考文献 |行 |额外 | +----+-------------+-------+-----------------------------------------------------------------------+-------+--------------------------------+-----------+---------+--------------------+--------+-----------------------+ | 1 |简单 |e |空 |范围 |主要,已发布 |出版 |6 |空 |120674 |使用索引条件 | | 1 |简单 |欧盟 |P0,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19 |参考文献 |event_id,start_date,event_id_2 |event_id |4 |biztradeshows.e.id | 1 |使用位置 | +----+-------------+-------+-----------------------------------------------------------------------+-------+--------------------------------+-----------+---------+--------------------+--------+-----------------------+
SELECT eu.event_id
FROM e_update_partition_event eu
INNER JOIN event e ON e.id=eu.event_id
WHERE eu.start_date > 2010-10-15
AND e.published=1
AND eu.event_id > 25000
AND eu.event_id < 50000;
所用时间- 设置189911行,2 个警告(20.56 秒)
解释结果-
+----+-------------+-------+----------------------------------+--------+--------------------------------+-----------+---------+--------------------+--------+-----------------------+ |身份证 |select_type |表 |分区 |类型 |possible_keys |键 |key_len |参考文献 |行 |额外 | +----+-------------+-------+----------------------------------+--------+--------------------------------+-----------+---------+--------------------+--------+-----------------------+ | 1 |简单 |e |空 |范围 |主要,已发布 |出版 |6 |空 |120674 |使用索引条件 | | 1 |简单 |欧盟 |P3,P4,P5,P6,P7,P8,P9,P10,P11,P12 |eq_ref |event_id,start_date,event_id_2 |event_id |4 |biztradeshows.e.id | 1 |使用位置 | +----+-------------+-------+----------------------------------+--------+--------------------------------+-----------+---------+--------------------+--------+-----------------------+
第三个查询的分区架构
(PARTITION p1 VALUES LESS THAN (25000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (50000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (75000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (100000) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (125000) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (150000) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (175000) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (200000) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (225000) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (250000) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (275000) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (300000) ENGINE = InnoDB)
为什么我的第三个查询比其他两个查询花费更多的时间并使用几乎所有的分区?
再多的分区也无济于事:
e.published=1
无法有效地为布尔字段编制索引。为什么?因为它们只有两个值中的一个。这看起来像一个可变字段(您更新的字段,因为已发布的内容可能会被打开和关闭)。这样的字段也不能用于分区。
您的第一个选择是将此published
字段与另一个字段组合在一起,并创建一个复合索引,并希望它具有足够的基数来成为有用的索引。
第二个选项是创建存档表并将未发布的项目移出到存档表中。
顺便说一句,您的查询有一个没有多大意义的条件:
and eu.event_id >25000 and eu.event_id>50000;
这可以缩短为
and eu.event_id > 50000;
更新
为什么要查询所有分区?那么你的第一个分区方案是在哈希分区上
按 HASH 进行分区主要用于确保均匀分布 预定数量的分区中的数据。
所以你的数据在所有分区中
第二种方案,如果你仔细观察,你会发现其中两个分区没有被使用。这些是你的 where 子句遗漏的分区。
所以问题出在你的 Where 子句 :-)
BY HASH 是无用
的拥有event_id > ...
和BY HASH(event_id)
是一个完全无用的组合。 哈希不知道哪些值将位于哪个分区中,除了一个接一个。 因此,它只是假设需要所有分区。
然后,它必须打开每个分区,执行查找,通常在那里找不到任何所需的值,然后移动到下一个分区。 因此,与PARITIIONing
相比,使用需要更多的时间。 即使event_id
没有索引,非分区版本也可能稍微快一些。 有了INDEX(event_id)
,非分区版本可能会快得多。
我还没有找到任何BY HASH
提供任何性能优势的用例。
非分区选项 1
对于您提出的一个查询,我的第一个猜测是不分区,但我会有
INDEX(start_date),
INDEX(event_id)
优化器将查看其微薄的统计信息并在它们之间进行选择。
非分区选项 2
同样,假设该查询,我的第二个猜测是这个"覆盖"索引:
INDEX(start_date, event_id)
关于分区的提示:对于小于一百万行的表,甚至不要考虑它。
更多讨论。
二维分区
由于两个"范围",该查询本质上是一个二维问题。 但是要使分区有用,您必须使用BY RANGE
,而不是BY HASH
。 因此,按任一方式进行分区
BY RANGE(TO_DAYS(start_date)) together with
PRIMARY KEY(event_id, ..., start_date)
或
BY RANGE(event_id) together with
PRIMARY KEY(start_date, ..., event_id)
请务必使用 InnoDB 来利用其在 PK 上的集群。 (我上面的链接讨论了将移动时间作为分区键的一些问题。