mysql archive分区_MYSQL-分区表

1、MYSQL数据库支持的分区类型为水平分区，并不支持垂直分区；

2、查看自己的mysql是不是启动了分区功能：

mysql> show variables like '%partition%'\G;

*************************** 1. row ***************************

Variable_name: have_partitioning

Value: YES

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> show plugins\G;

*************************** 1. row ***************************

Name: binlog

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 2. row ***************************

Name: mysql_native_password

Status: ACTIVE

Type: AUTHENTICATION

Library: NULL

License: GPL

*************************** 3. row ***************************

Name: mysql_old_password

Status: ACTIVE

Type: AUTHENTICATION

Library: NULL

License: GPL

*************************** 4. row ***************************

Name: CSV

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 5. row ***************************

Name: MEMORY

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 6. row ***************************

Name: MyISAM

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 7. row ***************************

Name: MRG_MYISAM

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 8. row ***************************

Name: ARCHIVE

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 9. row ***************************

Name: BLACKHOLE

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 10. row ***************************

Name: FEDERATED

Status: DISABLED

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 11. row ***************************

Name: InnoDB

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 12. row ***************************

Name: INNODB_TRX

Status: ACTIVE

Type: INFORMATION SCHEMA

Library: NULL

License: GPL

*************************** 13. row ***************************

Name: INNODB_LOCKS

Status: ACTIVE

Type: INFORMATION SCHEMA

Library: NULL

License: GPL

*************************** 14. row ***************************

Name: INNODB_LOCK_WAITS

Status: ACTIVE

Type: INFORMATION SCHEMA

Library: NULL

License: GPL

*************************** 15. row ***************************

Name: INNODB_CMP

Status: ACTIVE

Type: INFORMATION SCHEMA

Library: NULL

License: GPL

*************************** 16. row ***************************

Name: INNODB_CMP_RESET

Status: ACTIVE

Type: INFORMATION SCHEMA

Library: NULL

License: GPL

*************************** 17. row ***************************

Name: INNODB_CMPMEM

Status: ACTIVE

Type: INFORMATION SCHEMA

Library: NULL

License: GPL

*************************** 18. row ***************************

Name: INNODB_CMPMEM_RESET

Status: ACTIVE

Type: INFORMATION SCHEMA

Library: NULL

License: GPL

*************************** 19. row ***************************

Name: PERFORMANCE_SCHEMA

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 20. row ***************************

Name: partition

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

20 rows in set (0.05 sec)

ERROR:

No query specified

3、mysql分区表类型：

eg：

mysql> create table  ttt(

-> col1 int null,

-> col2 date null,

-> col3 int null,

-> col4 int null,

-> unique key (col1,col2,col3,col4)

-> )

-> partition by hash(col3)

-> partitions 4;

Query OK, 0 rows affected (0.33 sec)

mysql>

①、range分区

mysql> create table dd(

-> id int

-> )

-> engine=innodb

-> partition by range (id)(

-> partition p0 values less than (10),

-> partition p1 values less than (20));

Query OK, 0 rows affected (0.09 sec)

②、list分区

③、hash分区

④、key分区