Innodb 存储引擎 学习笔记 -分区表

 

两种分区方式：

水平分区:将同一表中不同行的记录分配到不同的物理文件中。

垂直分区:将同一表中不同列的记录分配到不同的物理文件中。

 

水平分区

注意：分区列必须是唯一索引

col3并不是unique key，不能y以col3分区！

改为由co1分区：

如果建表时没有指定主键，唯一索引，可以指定任何一列作为分区列，允许为null值：

分区类型：

1.RANGE分区：

插入3条记录：

通过p0分区查找到了id=9 的数据：

mysql> explain select * from partition_by_range where id =9;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | partition_by_range | p0         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

再添加一个从20到maxvalue的分区：

2.LIST分区

与RANGE分区十分相似，只是LIST分区是离散的。

mysql> insert into p_by_list values (1,3),(2,3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>  select table_name,partition_name,table_rows
    ->     from information_schema.PARTITIONS
    ->      where table_name = 'p_by_list' and table_schema = database()\G;
*************************** 1. row ***************************
    TABLE_NAME: p_by_list
PARTITION_NAME: p0
    TABLE_ROWS: 2
*************************** 2. row ***************************
    TABLE_NAME: p_by_list
PARTITION_NAME: p1
    TABLE_ROWS: 0
2 rows in set (0.09 sec)

3.HASH分区：

hash分区的目的是将数据均匀的分布到各个分区中。

创建一个以年份为key值的hash表，把数据分为4个分区：

mysql> create table p_hash(
    -> s int,
    -> b datetime
    -> )engine = innodb
    -> partition by hash(year(b))
    -> partitions 4;
Query OK, 0 rows affected (0.33 sec)

插入一行数据（1，‘2019-03-12’）

按年份进行hash,所以分到的分区为：

MOD(year('2019-03-12'),4)

= MOD(2019,4)

=3

mysql> insert into p_hash select 1,'2019-03-12';
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> select table_name,partition_name,table_rows
    -> from information_schema.PARTITIONS
    -> where table_schema = database() and table_name='p_hash';
+------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+------------+----------------+------------+
| p_hash     | p0             |          0 |
| p_hash     | p1             |          0 |
| p_hash     | p2             |          0 |
| p_hash     | p3             |          1 |
+------------+----------------+------------+
4 rows in set (0.02 sec)

4.COLUMNS分区，

与前面的RANGE,LIST,HASH分区不同，这三种分区的数据必须是整形，而COLUMNS分区可以直接用非整形数据进行分区。

COLUNMS分区支持一下的数据类型：

所有的整形，如INT,SMALLINT,TINYINT,BIGINT

日期类型，DATE,DATETIME

字符串类型，CHAR,VARCHAR,BINARY,VARBINARY

 

创建columns分区表

我们不再需要使用year()或者to_days()函数，可以直接对datetime类型数据进行分区：
 

mysql> create table p_columns_range(
    -> a int,
    -> b datetime
    -> )engine = innodb
    -> partition by range columns(b)(
    -> partition p0 values less than('2019-01-01'),
    -> partition p1 values less than('2020-01-01')
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into p_columns_range select 1,'2019-03-12';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into p_columns_range select 1,'2019-03-15';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select table_name,partition_name,table_rows
    -> from information_schema.PARTITIONS
    -> where table_schema = database() and table_name = 'p_columns_range';
+-----------------+----------------+------------+
| TABLE_NAME      | PARTITION_NAME | TABLE_ROWS |
+-----------------+----------------+------------+
| p_columns_range | p0             |          0 |
| p_columns_range | p1             |          2 |
+-----------------+----------------+------------+
2 rows in set (0.00 sec)