分区裁剪

分区裁剪

概述

分区裁剪（Partition Pruning）是数据库查询优化的一个过程，它能够识别并排除那些不必要的分区，从而减少查询需要扫描的数据量。当执行一个查询时，如果查询条件与表的分区键相关联，数据库系统能够自动确定并仅访问包含相关数据的分区，而忽略其它分区以大幅减少所需计算的数据量。

例如：

  1. CREATE TABLE t1 (
  2.     col1 INT NOT NULL,
  3.     col2 DATE NOT NULL,
  4.     col3 INT NOT NULL,
  5.     PRIMARY KEY(col1, col3)
  6. ) PARTITION BY KEY(col1, col3) PARTITIONS 4;
  8. INSERT INTO t1 VALUES
  9. (1, '1980-12-17', 7369),
  10. (2, '1981-02-20', 7499),
  11. (3, '1981-02-22', 7521),
  12. (4, '1981-04-02', 7566),
  13. (5, '1981-09-28', 7654),
  14. (6, '1981-05-01', 7698),
  15. (7, '1981-06-09', 7782),
  16. (8, '0087-07-13', 7788),
  17. (9, '1981-11-17', 7839),
  18. (10, '1981-09-08', 7844),
  19. (11, '2007-07-13', 7876),
  20. (12, '1981-12-03', 7900),
  21. (13, '1987-07-13', 7980),
  22. (14, '2001-11-17', 7981),
  23. (15, '1951-11-08', 7982),
  24. (16, '1927-10-13', 7983),
  25. (17, '1671-12-09', 7984),
  26. (18, '1981-11-06', 7985),
  27. (19, '1771-12-06', 7986),
  28. (20, '1985-10-06', 7987),
  29. (21, '1771-10-06', 7988),
  30. (22, '1981-10-05', 7989),
  31. (23, '2001-12-04', 7990),
  32. (24, '1999-08-01', 7991),
  33. (25, '1951-11-08', 7992),
  34. (26, '1927-10-13', 7993),
  35. (27, '1971-12-09', 7994),
  36. (28, '1981-12-09', 7995),
  37. (29, '2001-11-17', 7996),
  38. (30, '1981-12-09', 7997),
  39. (31, '2001-11-17', 7998),
  40. (32, '2001-11-17', 7999);
  42. mysql> EXPLAIN VERBOSE SELECT * FROM t1 WHERE (col1 = 1 AND col3 = 7369) OR (col1 = 6 AND col3 = 7698);
  43. +-----------------------------------------------------------------------------------------------------+
  44. | QUERY PLAN                                                                                          |
  45. +-----------------------------------------------------------------------------------------------------+
  46. | Project (cost=1000.00 outcnt=1000.00 selectivity=1.0000)                                            |
  47. |   Output: t1.col1, t1.col2, t1.col3                                                                 |
  48. |   ->  Table Scan on db2.t1 (cost=1000.00 outcnt=1000.00 selectivity=1.0000 blockNum=1)              |
  49. |         Output: t1.col1, t1.col2, t1.col3                                                           |
  50. |         Table: 't1' (0:'col1', 1:'col2', 2:'col3')                                                  |
  51. |         Hit Partition: p0, p2                                                                       |
  52. |         Filter Cond: (((t1.col1 = 1) and (t1.col3 = 7369)) or ((t1.col1 = 6) and (t1.col3 = 7698))) |
  53. +-----------------------------------------------------------------------------------------------------+
  54. 7 rows in set (0.00 sec)

该查询通过裁剪分区，仅扫描了 p0 和 p2 分区。

分区裁剪在 KEY 分区表上的应用

适用的 KEY 分区表场景

只有与分区键匹配的等值比较查询条件才能支持 KEY 分区表的裁剪。

  1. CREATE TABLE t1 (
  2.     col1 INT NOT NULL,
  3.     col2 DATE NOT NULL,
  4.     col3 INT PRIMARY KEY
  5. ) PARTITION BY KEY(col3) PARTITIONS 4;
  7. mysql> EXPLAIN SELECT * FROM t1 WHERE col3 = 7990 OR col3 = 7988;
  8. +-------------------------------------------------------------------+
  9. | QUERY PLAN                                                        |
  10. +-------------------------------------------------------------------+
  11. | Project                                                           |
  12. |   ->  Table Scan on db1.t1                                        |
  13. |         Hit Partition: p0, p1                                     |
  14. |         Filter Cond: ((t1.col3 = 7990) or (t1.col3 = 7988))       |
  15. |         Block Filter Cond: ((t1.col3 = 7990) or (t1.col3 = 7988)) |
  16. +-------------------------------------------------------------------+
  17. 5 rows in set (0.00 sec)

在这个 SQL 中，条件 col3 = 7990 可以确定所有结果都位于分区 p0 上。条件 col3 = 7988 可以确定所有结果都位于分区 p1 上。由于这两个条件的关系是 OR，因此只需要扫描 p0 和 p1 两个分区，分区裁剪的结果是 p0 和 p1。

不适用的 KEY 分区表场景

场景一

Key 分区由于内部使用哈希算法造成的无序性，不适用于连续查询，如 between、> < >= <= 等条件，无法使用分区裁剪优化。

  1. mysql> EXPLAIN SELECT * FROM t1 WHERE col3 >= 7782;
  2. +----------------------------------------------+
  3. | QUERY PLAN                                   |
  4. +----------------------------------------------+
  5. | Project                                      |
  6. |   ->  Table Scan on db1.t1                   |
  7. |         Hit Partition: all partitions        |
  8. |         Filter Cond: (t1.col3 >= 7782)       |
  9. |         Block Filter Cond: (t1.col3 >= 7782) |
  10. +----------------------------------------------+
  11. 5 rows in set (0.00 sec)

场景二

对于只能在执行计划生成阶段获取过滤条件的场景，无法利用分区裁剪优化。

  1. mysql> EXPLAIN SELECT * FROM t1 WHERE col3 = (SELECT col3 FROM t2 WHERE t1.col3 = t2.col3 AND t2.col1 < 5);
  2. +------------------------------------------------------+
  3. | QUERY PLAN                                           |
  4. +------------------------------------------------------+
  5. | Project                                              |
  6. |   ->  Filter                                         |
  7. |         Filter Cond: (t1.col3 = t2.col3
  9. )             |
  10. |         ->  Join                                     |
  11. |               Join Type: SINGLE                      |
  12. |               Join Cond: (t1.col3 = t2.col3)         |
  13. |               ->  Table Scan on db1.t1               |
  14. |                     Hit Partition: all partitions    |
  15. |               ->  Table Scan on db1.t2               |
  16. |                     Hit Partition: all partitions    |
  17. |                     Filter Cond: (t2.col1 < 5)       |
  18. |                     Block Filter Cond: (t2.col1 < 5) |
  19. +------------------------------------------------------+
  20. 12 rows in set (0.00 sec)

这个查询每读取一行数据，都会从子查询中获取结果并构建等值过滤条件 col3 = ?。然而，分区裁剪只在查询计划生成阶段生效，而不是执行阶段，因此无法进行分区裁剪。

分区裁剪在 Hash 分区表上的应用

适用的 HASH 分区表场景

Hash 分区表的使用方式与 Key 分区表基本相同，只有等值比较查询条件才能支持 Hash 分区表的裁剪。

  1. CREATE TABLE employees (
  2.     id INT NOT NULL,
  3.     fname VARCHAR(30),
  4.     lname VARCHAR(30),
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',
  7.     job_code INT,
  8.     store_id INT
  9. )
  10. PARTITION BY HASH(store_id) PARTITIONS 4;
  12. INSERT INTO employees VALUES
  13. (10001, 'Georgi', 'Facello', '1953-09-02','1986-06-26',120, 1),
  14. (10002, 'Bezalel', 'Simmel', '1964-06-02','1985-11-21',150, 7),
  15. (10003, 'Parto', 'Bamford', '1959-12-03','1986-08-28',140, 3),
  16. (10004, 'Chirstian', 'Koblick', '1954-05-01','1986-12-01',150, 3),
  17. (10005, 'Kyoichi', 'Maliniak', '1955-01-21','1989-09-12',150, 18),
  18. (10006, 'Anneke', 'Preusig', '1953-04-20','1989-06-02',150, 15),
  19. (10007, 'Tzvetan', 'Zielinski', '1957-05-23','1989-02-10',110, 6),
  20. (10008, 'Saniya', 'Kalloufi', '1958-02-19','1994-09-15',170, 10),
  21. (10009, 'Sumant', 'Peac', '1952-04-19','1985-02-18',110, 13),
  22. (10010, 'Duangkaew', 'Piveteau', '1963-06-01','1989-08-24',160, 10),
  23. (10011, 'Mary', 'Sluis', '1953-11-07','1990-01-22',120, 8),
  24. (10012, 'Patricio', 'Bridgland', '1960-10-04','1992-12-18',120, 7),
  25. (10013, 'Eberhardt', 'Terkki', '1963-06-07','1985-10-20',160, 17),
  26. (10014, 'Berni', 'Genin', '1956-02-12','1987-03-11',120, 15),
  27. (10015, 'Guoxiang', 'Nooteboom', '1959-08-19','1987-07-02',140, 8),
  28. (10016, 'Kazuhito', 'Cappelletti', '1961-05-02','1995-01-27',140, 2),
  29. (10017, 'Cristinel', 'Bouloucos', '1958-07-06','1993-08-03',170, 10),
  30. (10018, 'Kazuhide', 'Peha', '1954-06-19','1987-04-03',170, 2),
  31. (10019, 'Lillian', 'Haddadi', '1953-01-23','1999-04-30',170, 13),
  32. (10020, 'Mayuko', 'Warwick', '1952-12-24','1991-01-26',120, 1),
  33. (10021, 'Ramzi', 'Erde', '1960-02-20','1988-02-10',120, 9),
  34. (10022, 'Shahaf', 'Famili', '1952-07-08','1995-08-22',130, 10),
  35. (10023, 'Bojan', 'Montemayor', '1953-09-29','1989-12-17',120, 5),
  36. (10024, 'Suzette', 'Pettey', '1958-09-05','1997-05-19',130, 4),
  37. (10025, 'Prasadram', 'Heyers', '1958-10-31','1987-08-17',180, 8),
  38. (10026, 'Yongqiao', 'Berztiss', '1953-04-03','1995-03-20',170, 4),
  39. (10027, 'Divier', 'Reistad', '1962-07-10','1989-07-07',180, 10),
  40. (10028, 'Domenick', 'Tempesti', '1963-11-26','1991-10-22',110, 11),
  41. (10029, 'Otmar', 'Herbst', '1956-12-13','1985-11-20',110, 12),
  42. (10030, 'Elvis', 'Demeyer', '1958-07-14','1994-02-17',110, 1),
  43. (10031, 'Karsten', 'Joslin', '1959-01-27','1991-09-01',110, 10),
  44. (10032, 'Jeong', 'Reistad', '1960-08-09','1990-06-20',120, 19),
  45. (10033, 'Arif', 'Merlo', '1956-11-14','1987-03-18',120, 14),
  46. (10034, 'Bader', 'Swan', '1962-12-29','1988-09-21',130, 16),
  47. (10035, 'Alain', 'Chappelet', '1953-02-08','1988-09-05',130, 3),
  48. (10036, 'Adamantios', 'Portugali', '1959-08-10','1992-01-03',130, 14),
  49. (10037, 'Pradeep', 'Makrucki', '1963-07-22','1990-12-05',140, 12),
  50. (10038, 'Huan', 'Lortz', '1960-07-20','1989-09-20',140, 7),
  51. (10039, 'Alejandro', 'Brender', '1959-10-01','1988-01-19',110, 20),
  52. (10040, 'Weiyi', 'Meriste', '1959-09-13','1993-02-14',140, 17);
  54. mysql> EXPLAIN SELECT * FROM employees WHERE store_id = 10;
  55. +------------------------------------------------+
  56. | QUERY PLAN                                     |
  57. +------------------------------------------------+
  58. | Project                                        |
  59. |   ->  Table Scan on db1.employees              |
  60. |         Hit Partition: p0                      |
  61. |         Filter Cond: (employees.store_id = 10) |
  62. +------------------------------------------------+
  63. 4 rows in set (0.00 sec)

在这个 SQL 中，由于分区表的分区键为 store_id，条件 store_id = 10 可以确定所有结果都在一个分区中。数值 10 经过 Hash 后确定位于分区 p0 中。因此只需要扫描分区 p0，无需访问绝对不包含相关结果的 p1、p2、p3 分区。从执行计划中可见，只出现一个 TableScan 算子，其中指定了 Hit Partition 为 p0，确保了分区裁剪的生效。

不适用的 HASH 分区表场景

场景一

HASH 分区由于内部使用哈希算法造成的无序性，不适用于连续查询，如 between、> < >= <= 等条件，无法使用分区裁剪优化。

  1. CREATE TABLE employees (
  2.     id INT NOT NULL,
  3.     fname VARCHAR(30),
  4.     lname VARCHAR(30),
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',
  7.     job_code INT,
  8.     store_id INT
  9. )
  10. PARTITION BY HASH(store_id) PARTITIONS 4;
  12. mysql> EXPLAIN SELECT * FROM employees WHERE store_id > 15;
  13. +------------------------------------------------+
  14. | QUERY PLAN                                     |
  15. +------------------------------------------------+
  16. | Project                                        |
  17. |   ->  Table Scan on db1.employees              |
  18. |         Hit Partition: all partitions          |
  19. |         Filter Cond: (employees.store_id > 15) |
  20. +------------------------------------------------+
  21. 4 rows in set (0.00 sec)

在这个 SQL 中，条件 store_id > 15 无法确定对应的 Hash 分区，因此无法使用分区裁剪优化。

场景二

对于只能在执行计划生成阶段获取过滤条件的场景，无法利用分区裁剪优化。

  1. mysql> EXPLAIN SELECT * FROM t1 WHERE col1 = (SELECT store_id FROM employees WHERE employees.store_id = t1.col1 AND employees.id = 10010);
  2. +---------------------------------------------------------+
  3. | QUERY PLAN                                              |
  4. +---------------------------------------------------------+
  5. | Project                                                 |
  6. |   ->  Filter                                            |
  7. |         Filter Cond: (t1.col1 = employees.store_id)     |
  8. |         ->  Join                                        |
  9. |               Join Type: SINGLE                         |
  10. |               Join Cond: (t1.col1 = employees.store_id) |
  11. |               ->  Table Scan on db1.t1                  |
  12. |                     Hit Partition: all partitions       |
  13. |               ->  Table Scan on db1.employees           |
  14. |                     Hit Partition: all partitions       |
  15. |                     Filter Cond: (employees.id = 10010) |
  16. +---------------------------------------------------------+
  17. 11 rows in set (0.01 sec)

这个查询每读取一行数据，都会从子查询中获取结果并构建等值过滤条件 col3 = ?，然而，分区裁剪仅在查询计划生成阶段生效，而不是执行阶段，因此无法进行分区裁剪。

场景三

目前，不支持将函数表达式用作 Hash 分区表达式的分区裁剪。

  1. CREATE TABLE t3 (
  2. col1 INT,
  3. col2 CHAR(10),
  4. col3 DATETIME
  5. ) PARTITION BY HASH (YEAR(col3)) PARTITIONS 4;
  7. INSERT INTO t3 VALUES
  8. (10001, 'Georgi', '1999-04-05 11:01:02'),
  9. (10002, 'Bezalel', '2004-04-03 13:11:10'),
  10. (10003, 'Parto', '1997-04-05 11:01:02'),
  11. (10004, 'Chirstian', '2004-04-03 13:11:10'),
  12. (10005, 'Mary', '1998-04-05 11:01:02'),
  13. (10006, 'Patricio', '2004-04-03 13:11:10'),
  14. (10007, 'Eberhardt', '1953-09-02 13:11:10'),
  15. (10008, 'Kazuhide', '1986-06-26 19:21:10'),
  16. (10009, 'Tempesti', '1956-11-14 08:11:10'),
  17. (10010, 'Nooteboom', '1987-03-18 23:11:10');
  19. mysql> EXPLAIN SELECT * FROM t3 WHERE YEAR(col3) = 1999;
  20. +---------------------------------------------+
  21. | QUERY PLAN                                  |
  22. +---------------------------------------------+
  23. | Project                                     |
  24. |   ->  Table Scan on db1.t3                  |
  25. |         Hit Partition: all partitions       |
  26. |         Filter Cond: (YEAR(t3.col3) = 1999) |
  27. +---------------------------------------------+
  28. 4 rows in set (0.00 sec)
  30. mysql> SELECT * FROM t3 WHERE YEAR(col3) = 1999;
  31. +-------+--------+---------------------+
  32. | col1  | col2   | col3                |
  33. +-------+--------+---------------------+
  34. | 10001 | Georgi | 1999-04-05 11:01:02 |
  35. +-------+--------+---------------------+
  36. 1 row in set (0.01 sec)

分区剪裁的性能调优示例

示例 1: KEY 分区表的等值条件

  1. CREATE TABLE t1 (
  2.     col1 INT NOT NULL,
  3.     col2 DATE NOT NULL,
  4.     col3 INT NOT NULL,
  5.     PRIMARY KEY(col1, col3)
  6. ) PARTITION BY KEY(col1, col3) PARTITIONS 4;
  8. mysql> EXPLAIN SELECT * FROM t1 WHERE col1 = 1 AND col3 = 7369;
  9. +------------------------------------------------------+
  10. | QUERY PLAN                                           |
  11. +------------------------------------------------------+
  12. | Project                                              |
  13. |   ->  Table Scan on db2.t1                           |
  14. |         Hit Partition: p0                            |
  15. |         Filter Cond: (t1.col3 = 7369), (t1.col1 = 1) |
  16. +------------------------------------------------------+
  17. 5 rows in set (0.00 sec)

这个查询经过分区裁剪后，只会访问分区 p0，因为查询条件与 p0 中的数据匹配。

示例 2: KEY 分区表的 OR 条件

  1. mysql> EXPLAIN SELECT * FROM t1 WHERE (col1 = 1 AND col3 = 7369) OR (col1 = 6 AND col3 = 7698);
  2. +-----------------------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                                          |
  4. +-----------------------------------------------------------------------------------------------------+
  5. | Project (cost=1000.00 outcnt=1000.00 selectivity=1.0000)                                            |
  6. |   Output: t1.col1, t1.col2, t1.col3                                                                 |
  7. |   ->  Table Scan on db2.t1 (cost=1000.00 outcnt=1000.00 selectivity=1.0000 blockNum=1)              |
  8. |         Output: t1.col1, t1.col2, t1.col3                                                           |
  9. |         Table: 't1' (0:'col1', 1:'col2', 2:'col3')                                                  |
  10. |         Hit Partition: p0, p2                                                                       |
  11. |         Filter Cond: (((t1.col1 = 1) and (t1.col3 = 7369)) or ((t1.col1 = 6) and (t1.col3 = 7698))) |
  12. +-----------------------------------------------------------------------------------------------------+
  13. 7 rows in set (0.00 sec)

这个查询裁剪了分区 p0 和 p2，因为查询条件与这两个分区中的数据匹配。

示例 3: HASH 分区表的等值条件

  1. CREATE TABLE employees (
  2.     id INT NOT NULL,
  3.     fname VARCHAR(30),
  4.     lname VARCHAR(30),
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',
  7.     job_code INT,
  8.     store_id INT
  9. ) PARTITION BY HASH(store_id) PARTITIONS 4;
  11. mysql> EXPLAIN SELECT * FROM employees WHERE store_id = 10;
  12. +------------------------------------------------+
  13. | QUERY PLAN                                     |
  14. +------------------------------------------------+
  15. | Project                                        |
  16. |   ->  Table Scan on db1.employees              |
  17. |         Hit Partition: p0                      |
  18. |         Filter Cond: (employees.store_id = 10) |
  19. +------------------------------------------------+
  20. 4 rows in set (0.00 sec)

在这个查询中，只有分区 p0 包含与条件 store_id = 10 匹配的数据，因此只扫描了 p0 分区。

示例 4: HASH 分区表的多条件查询

  1. mysql> EXPLAIN SELECT * FROM employees WHERE store_id = 10 OR store_id = 15;
  2. +---------------------------------------------------+
  3. | QUERY PLAN                                        |
  4. +---------------------------------------------------+
  5. | Project                                           |
  6. |   ->  Table Scan on db1.employees                 |
  7. |         Hit Partition: p0, p3                     |
  8. |         Filter Cond: ((employees.store_id = 10) or (employees.store_id = 15)) |
  9. +---------------------------------------------------+
  10. 5 rows in set (0.00 sec)

这个查询裁剪了 p0 和 p3 分区，因为这两个分区中包含了与条件 store_id = 10 OR store_id = 15 匹配的数据。

示例 5: 不适用于分区裁剪的场景

  1. mysql> EXPLAIN SELECT * FROM t1 WHERE col1 > 5;
  2. +------------------------------------------------+
  3. | QUERY PLAN                                     |
  4. +------------------------------------------------+
  5. | Project                                        |
  6. |   ->  Table Scan on db1.t1                     |
  7. |         Hit Partition: all partitions          |
  8. |         Filter Cond: (t1.col1 > 5)             |
  9. |         Block Filter Cond: (t1.col1 > 5)       |
  10. +------------------------------------------------+
  11. 5 rows in set (0.00 sec)

在这个查询中，条件 col1 > 5 无法确定对应的分区，因此无法使用分区裁剪。

限制

MatrixOne 的分区表支持四种分区形式：Key、Hash、Range、List：

  • 仅支持对 Key 和 Hash 两种分区表进行分区裁剪，其他分区表的裁剪将在后续逐步实现。