分区剪枝不适用于DAYOFYEAR

Question

我有一个表stat_activity，它使用这样的查询创建：

CREATE TABLE `stat_activity_new` (
  `player_key` VARCHAR(36) NULL DEFAULT NULL,
  `activity_date` DATETIME NULL DEFAULT NULL,
  `remote_ip_addr` VARCHAR(15) NULL DEFAULT NULL,
  `player_id` VARCHAR(80) NULL DEFAULT NULL,
  `player_status` VARCHAR(80) NULL DEFAULT NULL,
  INDEX `idx_player_key` (`player_key`),
  INDEX `idx_play_time` (`activity_date`),
  INDEX `idx_remote_ip_address` (`remote_ip_addr`),
  INDEX `idx_player_id` (`player_id`),
  INDEX `idx_player_key_id_date` (`activity_date`, `player_key`, `player_id`),
  INDEX `idx_player_id_key_date` (`player_id`, `player_key`, `activity_date`)
)
ENGINE=InnoDB
PARTITION BY LIST(DAYOFYEAR(`activity_date`)) (
  PARTITION d1 VALUES IN (1),
  PARTITION d2 VALUES IN (2),
  PARTITION d3 VALUES IN (3),
  PARTITION d4 VALUES IN (4),
  PARTITION d5 VALUES IN (5),
  #...
  PARTITION d366 VALUES IN (366)
);

我正在试着测试这个分区剪枝正在使用这个表：

mysql> EXPLAIN PARTITIONS SELECT * FROM stat_activity WHERE activity_date >= '2018-01-01 00:00' AND activity_date < '2018-01-02 00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stat_activity
   partitions: d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31,d32,d33,d34,d35,d36,d37,d38,d39,d40,d41,d42,d43,d44,d45,d46,d47,d48,d49,d50,d51,d52,d53,d54,d55,d56,d57,d58,d59,d60,d61,d62,d63,d64,d65,d66,d67,d68,d69,d70,d71,d72,d73,d74,d75,d76,d77,d78,d79,d80,d81,d82,d83,d84,d85,d86,d87,d88,d89,d90,d91,d92,d93,d94,d95,d96,d97,d98,d99,d100,d101,d102,d103,d104,d105,d106,d107,d108,d109,d110,d111,d112,d113,d114,d115,d116,d117,d118,d119,d120,d121,d122,d123,d124,d125,d126,d127,d128,d129,d130,d131,d132,d133,d134,d135,d136,d137,d138,d139,d140,d141,d142,d143,d144,d145,d146,d147,d148,d149,d150,d151,d152,d153,d154,d155,d156,d157,d158,d159,d160,d161,d162,d163,d164,d165,d166,d167,d168,d169,d170,d171,d172,d173,d174,d175,d176,d177,d178,d179,d180,d181,d182,d183,d184,d185,d186,d187,d188,d189,d190,d191,d192,d193,d194,d195,d196,d197,d198,d199,d200,d201,d202,d203,d204,d205,d206,d207,d208,d209,d210,d211,d212,d213,d214,d215,d216,d217,d218,d219,d220,d221,d222,d223,d224,d225,d226,d227,d228,d229,d230,d231,d232,d233,d234,d235,d236,d237,d238,d239,d240,d241,d242,d243,d244,d245,d246,d247,d248,d249,d250,d251,d252,d253,d254,d255,d256,d257,d258,d259,d260,d261,d262,d263,d264,d265,d266,d267,d268,d269,d270,d271,d272,d273,d274,d275,d276,d277,d278,d279,d280,d281,d282,d283,d284,d285,d286,d287,d288,d289,d290,d291,d292,d293,d294,d295,d296,d297,d298,d299,d300,d301,d302,d303,d304,d305,d306,d307,d308,d309,d310,d311,d312,d313,d314,d315,d316,d317,d318,d319,d320,d321,d322,d323,d324,d325,d326,d327,d328,d329,d330,d331,d332,d333,d334,d335,d336,d337,d338,d339,d340,d341,d342,d343,d344,d345,d346,d347,d348,d349,d350,d351,d352,d353,d354,d355,d356,d357,d358,d359,d360,d361,d362,d363,d364,d365,d366
         type: range
possible_keys: idx_play_time,idx_player_key_id_date
          key: idx_play_time
      key_len: 9
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)

如果分区修剪有效，partitions字段将只包含"d1“。那为什么不起作用呢？

MySQL版本: 5.5.37 (不幸)，没有升级选项

UPDATE：更改表的方案以支持TO_DAYS分区

ALTER TABLE `stat_activity`
  PARTITION BY HASH( TO_DAYS(`activate_date`) )
  PARTITIONS 100;

不过，我仍然看到它在每个分区中搜索：

mysql> EXPLAIN PARTITIONS SELECT * FROM stat_activity WHERE activity_date >= '2018-01-01 00:00:00' AND activity_date < '2018-01-02 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stat_activity
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p89,p90,p91,p92,p93,p94,p95,p96,p97,p98,p99
         type: range
possible_keys: idx_play_time,idx_player_key_id_date
          key: idx_play_time
      key_len: 9
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

Answer 1

这种类型的优化可以在分区表达式由相等或范围组成的情况下应用，这些相等或范围可以缩小为一组等式，或者当分区表达式表示递增或递减关系时。当分区表达式使用年()、或TO_DAYS()函数时，还可以对在日期或日期时间列上分区的表应用剪枝。此外，在MySQL 5.7中，当分区表达式使用TO_SECONDS()函数时，可以对这些表进行剪枝。

源https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

Answer 2

我正在尝试在MariaDB 10.4.6中按日期时间进行分区：

通过hash(TO_DAYS(dt))分区7创建表test_part2(id text，dt datetime)分区；

分区使用INSERT正确填充，但我不确定如何使用SELECT语句来使用剪枝来提取给定日期的所有记录(如yyyy)？

解释分区选择*来自test_part2，其中dt在"2019-06-25“和"2019-06-26"之间

分区：p0，p1，p2，p3，p4，p5，p6：不修剪

解释分区选择*从test_part2，其中日期(Dt)= "2019-06-25"；

分区：p0，p1，p2，p3，p4，p5，p6：no剪枝

解释分区从dt="2019-06-25"；所在的test_part2中选择*

分区：p3

剪枝在这里工作，但 dt是一个日期时间，所以所有记录都有时间，没有记录匹配日期没有时间。

有办法让这件事成功吗？或者是否需要添加一个额外的日期(没有时间)列，分区列？如果是这样，如何实际使用DATETIME列上的分区？