mysql是否支援分割區

mysql從5.1版本開始支援分割區功能。 MySQL5.1中分區表達式必須是整數，或傳回整數的表達式；而MySQL5.5中提供了非整數表達式分割的支援。 MySQL資料庫的分區是局部分區索引，一個分區中既存了數據，又放了索引；也就是說，每個區的聚集索引和非聚集索引都放在各自區的（不同的物理文件）。 MySQL支援4種分區類型：RANGE分區，LIST分區，HASH分區，KEY分區。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

mysql支援分割區。

mysql分割區概述

MySQL在5.1時加入了對水平分割區的支援。分區是將一個表或索引分解成多個更小，更可管理的部分。每個區都是獨立的，可以獨立處理，也可以作為一個更大物件的一部分來處理。這個是MySQL支援的功能，業務程式碼無需改動。要知道MySQL是面向OLTP的數據，它不像TIDB等其他DB。那麼對於分區的使用應該要非常小心，如果不清楚如何使用分區可能會對效能產生負面的影響。

MySQL資料庫的分割區是局部分割區索引，一個分割區中既存了數據，又放了索引。也就是說，每個區的聚集索引和非聚集索引都放在各自區的（不同的實體檔案）。目前MySQL資料庫還不支援全域分區。

無論哪種類型的分割區，如果表中存在主鍵或唯一索引時，分割區列必須是唯一索引的一個組成部分。

分區表的限制因素

（1）、一個表格最多只能有1024個分割區。

（2）、 MySQL5.1中，分割區表達式必須是整數，或是傳回整數的表達式。在MySQL5.5中提供了非整數表達式分區的支援。

（3）、如果分區欄位中有主鍵或唯一索引的列，那麼多有主鍵列和唯一索引列都必須包含進來。即：分區欄位要麼不包含主鍵或索引列，要麼包含全部主鍵和索引列。

（4）、分區表中無法使用外鍵約束。

（5）、MySQL的分區適用於一個表的所有資料和索引，不能只對錶資料分區而不對索引分區，也不能只對索引分區而不對錶分區，也不能只對錶的一部分資料分區。

分區類型

目前MySQL支援幾種類型的分區，RANGE分區，LIST分區，HASH分區，KEY分區。如果表存在主鍵或唯一索引時，分區列必須是唯一索引的一個組成部分。實戰十有八九都是用RANGE分區。

RANGE分區

RANGE分區是實戰最常用的一種分區類型，行資料是基於屬於一個給定的連續區間的列值被放入分區。但是記住，當插入的資料不在一個分區中定義的值的時候，會拋異常。

RANGE分區主要用於日期列的分區，例如交易表啊，銷售表啊等。可以根據年月來存放資料。如果你分區走的唯一索引中date類型的數據，那麼注意了，優化器只能對YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()這類函數進行最佳化選擇。實戰中可以用int型，那麼只用存子yyyyMM就好了。也不必關心函數了。

CREATE TABLE `m_test_db`.`Order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `partition_key` INT NOT NULL,
    `amt` DECIMAL(5) NULL,
    PRIMARY KEY (`id` , `partition_key`)
) PARTITION BY RANGE (partition_key) PARTITIONS 5 (
PARTITION part0 VALUES LESS THAN (201901) , 
PARTITION part1 VALUES LESS THAN (201902) , 
PARTITION part2 VALUES LESS THAN (201903) , 
PARTITION part3 VALUES LESS THAN (201904) , 
PARTITION part4 VALUES LESS THAN (201905));

這時候我們先插入一些資料

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

現在我們查詢一下，透過EXPLAIN PARTITION指令發現SQL優化器只需搜尋對應的區，不會搜尋所有分割區

#如果sql語句有問題，那麼就會走所有區。會很危險。所以分區表後，select語句必須走分區鍵。

 

以下3種不是太常用，就一筆帶過了。

LIST分區

LIST分區和RANGE分區很相似，只是分區列的值是離散的，不是連續的。 LIST分區使用VALUES IN，因為每個分區的值是離散的，因此只能定義值。

HASH分區

說到哈希，那麼目的很明顯了，將資料均勻的分佈到預先定義的各個分區中，保證每個分區的數量大致相同。

KEY分區

KEY分區和HASH分區相似，不同之處在於HASH分區使用使用者定義的函數進行分區，KEY分區使用資料庫提供的函數進行分區。

分区和性能

一项技术，不是用了就一定带来益处。比如显式锁功能比内置锁强大，你没玩好可能导致很不好的情况。分区也是一样，不是启动了分区数据库就会运行的更快，分区可能会给某些sql语句性能提高，但是分区主要用于数据库高可用性的管理。

数据库应用分为2类，一类是OLTP（在线事务处理），一类是OLAP（在线分析处理）。对于OLAP应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。在OLTP应用中，分区更加要小心，通常不会获取一张大表的10%的数据，大部分是通过索引返回几条数据即可。

比如一张表1000w数据量，如果一句select语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3，现在有10个分区。那么不是要(3+3)*10次的逻辑IO？（3次聚集索引，3次辅助索引，10个分区）。所以在OLTP应用中请小心使用分区表。

在日常开发中，如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取，partitions标识走了哪几个分区。

mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)

注：

1.MySQL Workbench下添加分区的截图

2. Table has no partition for value 12

在12月的某一天，我查看了生产的日志文件，忽然发现系统一直在报错：Table has no partition for value 12。仔细检查分区sql发现分区的时候用的是less than

也就是说我在注释1截图里面的分区是不包括12月的区的。执行以下命令增加分区：

ALTER TABLE table_name ADD PARTITION (PARTITION p_12 VALUES LESS THAN (13));

如果没有进行适当的处理，将会报错。所以在进行 RANGE 分区时，要思考这种情况。一般情况下，就时在最后添加一个 MAXVALUE 分区，如下：

PARTITION p_max VALUES LESS THAN MAXVALUE

【相关推荐：mysql视频教程】

以上是mysql是否支援分割區的詳細內容。更多資訊請關注PHP中文網其他相關文章！