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分区