什麼是RANGE分區 - PolarDB

RANGE分區是對歷史資料進行分區的一種方便的方法。RANGE分區用邊界定義了表或索引中分區的範圍和分區間的順序。

RANGE分區通常用於在DATE類型的列上按時間間隔組織資料。因此，大多數訪問RANGE分區的SQL語句都關注時間範圍。這方面的一個例子是類似於“從特定時間段選擇資料”的SQL語句。在這樣的情境中，如果每個分區代表一個月的資料，那麼查詢“尋找21-12的資料”必須只訪問2021年的12月份的分區。這將掃描的資料量減少到可用資料總量的一小部分，這種最佳化方法稱為分區修剪（Partition Purning）。

對於定期載入新資料和清除舊資料的情境，RANGE分區也是理想的分區方式。例如，通常會保留一個滾動的資料視窗，將過去36個月的資料保持線上。RANGE分區簡化了這個過程。要添加新月份的資料，需要將其載入到一個單獨的表中，對其進行清理、建立索引，然後使用EXCHANGE PARTITION語句將其添加到RANGE分區表中，同時原始表保持線上狀態。添加新分區後，可以使用DROP PARTITION語句刪除最後一個月。

以下情況下可以考慮使用RANGE分區：

經常在某些列上的按照範圍掃描非常大的表（例如訂單表ORDER或購買明細表LINEITEM）。在這些列上對錶進行分區可以實現分區剪枝。
希望維護資料的滾動視窗。
不能在指定的時間內完成大型表的管理操作，例如備份和恢複，但是可以根據分區範圍列將它們劃分為更小的邏輯塊。

樣本：建立為期9年+的表orders，並根據列o_orderdate按範圍對其進行分區，將資料分成8個年度，每個年度對應一個分區。通過短時間間隔分析銷售資料可以利用分區修剪。銷售表也支援滾動視窗方法。

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(o_orderdate))
(PARTITION item1 VALUES LESS THAN (TO_DAYS('1992-01-01')),
 PARTITION item2 VALUES LESS THAN (TO_DAYS('1993-01-01')),
 PARTITION item3 VALUES LESS THAN (TO_DAYS('1994-01-01')),
 PARTITION item4 VALUES LESS THAN (TO_DAYS('1995-01-01')),
 PARTITION item5 VALUES LESS THAN (TO_DAYS('1996-01-01')),
 PARTITION item6 VALUES LESS THAN (TO_DAYS('1997-01-01')),
 PARTITION item7 VALUES LESS THAN (TO_DAYS('1998-01-01')),
 PARTITION item8 VALUES LESS THAN (TO_DAYS('1999-01-01')),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));

 EXPLAIn select * from orders where o_orderDATE = '1992-03-01';
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | item2      | ref  | i_o_orderdate | i_o_orderdate | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

由於RANGE本身的限制，使用TO_DAYS分區後，再執行SHOW CREATE TABLE後可能看不到原來的DDL。樣本如下：

show create table orders;
| orders | CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`o_orderDATE`))
(PARTITION item1 VALUES LESS THAN (727563),
 PARTITION item2 VALUES LESS THAN (727929),
 PARTITION item3 VALUES LESS THAN (728294),
 PARTITION item4 VALUES LESS THAN (728659),
 PARTITION item5 VALUES LESS THAN (729024),
 PARTITION item6 VALUES LESS THAN (729390),
 PARTITION item7 VALUES LESS THAN (729755),
 PARTITION item8 VALUES LESS THAN (730120),
 PARTITION item9 VALUES LESS THAN MAXVALUE) */ |

RANGE COLUMNS分區是RANGE分區的擴充，是基於多個列值使用範圍定義分區，可以使用非整數類型的列作為分區列。

RANGE COLUMNS分區與RANGE分區的區別如下：

RANGE COLUMNS不接受運算式，只接受列的名稱；
RANGE COLUMNS接受一個或多個列的列表。RANGE COLUMNS分區基於元組（列值列表）之間的比較，而不是標量值之間的比較。RANGE COLUMNS分區中的行位置也基於元組之間的比較；
RANGE COLUMNS分區列不限於整數列；字串類型、DATE和DATETIME列也可以用作分區列。

建立RANGE COLUMNS分區的樣本如下：

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
 PARTITION item2 VALUES LESS THAN ('1993-01-01'),
 PARTITION item3 VALUES LESS THAN ('1994-01-01'),
 PARTITION item4 VALUES LESS THAN ('1995-01-01'),
 PARTITION item5 VALUES LESS THAN ('1996-01-01'),
 PARTITION item6 VALUES LESS THAN ('1997-01-01'),
 PARTITION item7 VALUES LESS THAN ('1998-01-01'),
 PARTITION item8 VALUES LESS THAN ('1999-01-01'),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));