MYSQL分區管理

--在mysql5.6之後檢視分區采用

show plugins;

--不論建立何種類型的分區,如果表中存在主鍵或唯一索引時,分區列必須是唯一索引的一個組成部分

mysql> create table t1(id int not null,id2 int not null,unique key(id)) partition by hash(id2) partitions 4;    

error 1503 (hy000): a primary key must include all columns in the table is partitioning function

--innodb與分區表不相容

partitioned innodb tables cannot have foreign key references, nor can they have columns referenced by foreign keys.

innodb tables which have or which are referenced by foreign keys cannot be partitioned

mysql> alter table t2 add foreign key(id) references t1(id);

error 1215 (hy000): cannot add foreign key constraint

--檢視分區表

select * from information_schema.partitions p where p.partition_name is not null

and p.table_name='t1';

--檢視某一分區執行計劃

mysql> explain partitions select * from t1 where id2=1;  

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | extra       |

|  1 | simple      | t1    | p1         | all  | null          | null | null    | null |    2 | using where | 

--檢視某一分區内資料，如果有多個分區要查時，請使用分号隔開

mysql> select * from t1 partition(p1);                  

+----+-----+

| id | id2 |

|  4 |   5 | 

|  8 |   9 | 

--mysql不支援在日期類型上直接建立分區，必需借助于函數

create table employees (

id int not null,

fname varchar(30),

lname varchar(30),

hired date not null default '1970-01-01',

separated date not null default '9999-12-31',

job_code int,

store_id int

)

partition by range ( year(separated) ) (

partition p0 values less than (1991),

partition p1 values less than (1996),

partition p2 values less than (2001),

partition p3 values less than maxvalue

);

--按月進行分區，還是利用的函數

create table t (

fired_date date not null default '1970-01-01'

partition by range ( extract(year_month from fired_date) ) (

partition p0 values less than (201601),

partition p1 values less than (201602),

partition p2 values less than (201603),

--通過使用range columns而不再需要使用函數

the use of partitioning columns employing date or time types other than date or datetime is not supported with range columns

create table members (

firstname varchar(25) not null,

lastname varchar(25) not null,

username varchar(16) not null,

email varchar(35),

joined date 

partition by range columns(joined) (

partition p0 values less than ('1960-01-01'),

partition p1 values less than ('1970-01-01'),

partition p2 values less than ('1980-01-01'),

partition p3 values less than ('1990-01-01'),

partition p4 values less than maxvalue

--對于range partition的null，mysql預設把它當作最小的值看待，如果列值為空，其會把它插入到第一個分區中

mariadb [test]> insert into members(firstname,lastname,username) values ('rudy','gao','rudy.gao');

query ok, 1 row affected (0.01 sec)

mariadb [test]> select * from members;

+-----------+----------+----------+-------+--------+

| firstname | lastname | username | email | joined |

| rudy      | gao      | rudy.gao | null  | null   |

1 row in set (0.00 sec)

--在第一個分區中查詢

mariadb [test]> select * from members partition(p0);

--對于list partition的null，如果沒有指定一個list存儲null時，其是不允許插入null值的

--a table that is partitioned by list admits null values if and only if one of its partitions is defined using that value-list that contains null. 

--the converse of this is that a table partitioned by list which does not explicitly use null in a value list rejects rows resulting in a null value for the partitioning expression

mysql> insert into ts1 values (null, 'mothra');

error 1504 (hy000): table has no partition for value null

--對于hash或者key分區，null值被當做0處理

--null is handled somewhat differently for tables partitioned by hash or key. 

--in these cases, any partition expression that yields a null value is treated as though its return value were zero

--注意對于range columns其不能是表達式，但其可以接受多個列

 range columns does not accept expressions, only names of columns.

 range columns accepts a list of one or more columns

--如果range columns有多列時，其必須滿足所有列都符合相應條件時，才放入相對應的分區，否則會預設分區

create table rc1 (a int,b int)

partition by range columns(a, b) (

partition p0 values less than (5, 12),

partition p3 values less than (maxvalue, maxvalue)

mysql> insert into rc1 values (5,10), (5,11), (5,12);

--檢視各個分區表中行數

mysql> select partition_name,table_rows from information_schema.partitions where table_name = 'rc1';

+----------------+------------+

| partition_name | table_rows |

| p0             |          2 |

| p3             |          1 |

--其類似于如下的sql比較

mysql> select (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);

--建立以key為分區的表，需要一個主鍵，如果沒有，則mysql使用預設的虛拟主鍵

--key takes only a list of zero or more column names. any columns used as the partitioning key must comprise part or all of the table's primary key, 

--if the table has one. where no column name is specified as the partitioning key, the table's primary key is used, if there is one

create table k1 (

id int not null primary key,

name varchar(20)

partition by key()

partitions 2;

--對于已經建立的分區，最好不要改變它們的sql_mode

it is strongly recommended that you never change the server sql mode after creating partitioned tables

sometimes a change in the server sql mode can make partitioned tables unusable

differing sql modes on master and slave can lead to partitioning expressions being evaluated differently