mysqlはパーティショニングをサポートしていますか?

Mysql はバージョン 5.1 からパーティショニング機能をサポートします。 MySQL 5.1 では、パーティション式は整数、または整数を返す式である必要がありますが、MySQL 5.5 では非整数式のパーティション化がサポートされています。 MySQL データベースのパーティションはローカル パーティション インデックスであり、1 つのパーティションにデータとインデックスの両方が格納されます。つまり、各ゾーンのクラスター化インデックスと非クラスター化インデックスはそれぞれのゾーン (異なる物理ファイル) に配置されます。 MySQL は、RANGE パーティション、LIST パーティション、HASH パーティション、KEY パーティションの 4 つのパーティション タイプをサポートします。

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

mysql はパーティショニングをサポートしています。

mysql パーティショニングの概要

MySQL は 5.1 で水平パーティショニングのサポートを追加しました。パーティショニングとは、テーブルまたはインデックスを、より小さく管理しやすい部分に分割することです。各ゾーンは独立しており、独立して処理することも、より大きなオブジェクトの一部として処理することもできます。これはMySQLがサポートする機能であり、業務コードを変更する必要はありません。 MySQL は、TIDB などの他の DB とは異なり、OLTP 指向のデータであることを知っておく必要があります。パーティションを使用するときは十分に注意する必要があり、パーティションの使用方法を知らないと、パフォーマンスに悪影響を及ぼす可能性があります。

MySQL データベースのパーティションはローカル パーティション インデックスであり、パーティションにはデータとインデックスの両方が保存されます。つまり、各ゾーンのクラスター化インデックスと非クラスター化インデックスは、それぞれのゾーン (異なる物理ファイル) に配置されます。現在、MySQL データベースはグローバル パーティショニングをサポートしていません。

どのようなタイプのパーティショニングであっても、テーブルに主キーまたは一意のインデックスがある場合、パーティショニング列は一意のインデックスのコンポーネントである必要があります。

パーティション化されたテーブルの制限要因

(1). テーブルには最大 1024 個のパーティションしか含めることができません。

(2). MySQL5.1 では、パーティション式は整数、または整数を返す式である必要があります。非整数式パーティショニングのサポートは、MySQL 5.5 で提供されます。

(3). パーティション フィールドに主キー列または一意のインデックス列がある場合は、多数の主キー列と一意のインデックス列を含める必要があります。つまり、パーティション フィールドに主キーまたはインデックス列が含まれていないか、すべての主キーとインデックス列が含まれています。

(4). 外部キー制約はパーティションテーブルでは使用できません。

(5) MySQL パーティショニングは、テーブル内のすべてのデータとインデックスに適用されます。テーブル データはパーティション化できますが、インデックスはパーティション化できません。インデックスはパーティション化できますがテーブルはパーティション化できません。テーブルのみをパーティション化することはできません。データ パーティションの一部。

パーティション タイプ

現在、MySQL は、RANGE パーティション、LIST パーティション、HASH パーティション、KEY パーティションといった複数のタイプのパーティションをサポートしています。テーブルに主キーまたは一意のインデックスがある場合、パーティション列は一意のインデックスのコンポーネントである必要があります。実際の戦闘では、RANGE 分割が使用される可能性が最も高くなります。

RANGE パーティション

RANGE パーティションは、実際に最も一般的に使用されるパーティション タイプです。行データは、指定された連続間隔に属する列値に基づいてパーティションに配置されます。 。ただし、挿入されたデータのパーティションに値が定義されていない場合は、例外がスローされることに注意してください。

RANGE パーティショニングは、主にトランザクション テーブル、売上テーブルなどの日付列のパーティショニングに使用されます。年月ごとにデータを保存できます。一意のインデックスで日付型データをパーティション化する場合、オプティマイザは 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 を使用し、値のみを定義できます。

ハッシュ パーティション

ハッシュといえば、その目的は明白です。事前に定義されたパーティションにデータを均等に分散して、各パーティションの数を確保します。ほぼ同じです。

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>&#39;2016-08-25 00:00:00&#39; and startTime<&#39;2016-08-25 23:59:00&#39;;  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| 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 中国語 Web サイトの他の関連記事を参照してください。