MYSQL partition table function test analysis _ MySQL

Source: Internet
Author: User
Test the function of MYSQL partition table
1. check whether the Mysql version supports partition show variables like '% partition % '; + ------------------- + ------- + | Variable_name | Value | + ----------------- + ------- + | have_partitioning | YES | + ----------------- + ------- + if the VALUE is YES, partitions are supported,
2. test that storage engine supports partition INOODB engine mysql> Create table engine1 (id int) engine = innodb partition by range (id) (partition po values less than (10); Query OK, 0 rows affected (0.01 sec) MRG_MYISAM engine mysql> Create table engine2 (id int) engine = MRG_MYISAM partition by range (id) (partition po values less than (10 )); ERROR 1572 (HY000): Engine cannot be used in partitioned tables blackhole engine mysql> Create table engine3 (id int) Engine = blackhole partition by range (id) (partition po values less than (10); Query OK, 0 rows affected (0.01 sec) CSV engine mysql> Create table engine4 (id int) engine = csv partition by range (id) (partition po values less than (10); ERROR 1572 (HY000 ): engine cannot be used in partitioned tables Memory engine mysql> Create table engine5 (id int) Engine = memory partition by range (id) (partition po values less than (10 )); query OK, 0 rows affected (0.01 sec) federated engine mysql> Create table engine6 (id int) engine = federated partition by range (id) (partition po values less than (10); Query OK, 0 rows affected (0.01 sec) archive engine mysql> Create table engine7 (id int) engine = archive partition by range (id) (partition po values less than (10); Query OK, 0 rows affected (0.01 sec) myisam engine mysql> Create table engine8 (id int) engine = myisam partition by range (id) (partition po values less than (10); Query OK, 0 rows affected( 0.01 sec)
3. mysql partition table, partition engine test table partition storage engine is the same as mysql> Create table pengine1 (id int) engine = myisam partition by range (id) (partition po values less than (10) engine = myisam, partition p1 values less than (20) engine = myisam); Query OK, 0 rows affected (0.05 sec) different storage engines for table partitions mysql> Create table pengine2 (id int) engine = myisam partition by range (id) (partition po values less than (10) engine = myisam, partition p1 values less (20) engine = innodb); ERROR 1497 (HY000 ): the mix of handlers in the partitions is not allowed in this version of MySQL all partitions in The same partition table must use the same storage engine, and The storage engine must be consistent with the primary table. 4. partition type Range: multiple rows are allocated to partitions based on the column values in a continuous interval. LIST: The column values match a discrete set. Hash: Select partitions based on the return values of user-defined expressions, expression to calculate the column values in the table to be inserted. This function can contain any expressions that are valid in SQL and generate non-negative integer values. KEY: similar to HASH partition, the difference is that the expression of KEY partition can be one or more columns, and MYSQL provides its own HASH function.
5. test the RANGE partition MAXVALUE and extra-point partition. Create a table PRANGE. The last partition value is MAXVALUEmysql> Create table prange (id int) engine = myisam partition by range (id) (partition po values less than (10), partition p1 values less than (20), partition p2 values less than maxvalue); Query OK, 0 rows affected (0.06 sec) mysql> alter table prange add partition (partition p3 values less than (20); ERROR 1481 (HY000 ): MAXVALUE can only be used in last partition definition add a partition mysql> alter table prange add partition (partition p3 values less than (1) before partition P0; ERROR 1481 (HY000 ): MAXVALUE can only be used in last partition definition indicates that after a MAXVALUE value is available, the directly added points are not feasible;
Create a table PRANGE1 with no MAXVALUE mysql> Create table prange1 (id int) engine = myisam partition by range (id) (partition po values less than (10 ), partition p1 values less than (20), partition p2 values less than (30); Query OK, 0 rows affected (0.08 sec) add a partition after the maximum value mysql> alter table prange1 add partition (partition p3 values less than (40); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 add a partition before the minimum value of the partition mysql> alter table prange1 add partition (partition p43 values less than (1); ERROR 1493 (HY000 ): values less than value must be strictly increasing for each partition it can be seen that the RANGE partition method can only be added after the maximum value when adding a partition, but not before the maximum value;
6. create table ptime2 (id int, createdate datetime) engine = myisam partition by range (to_days (createdate) (partition po values less than (20100801 ), partition p1 values less than (20100901); Query OK, 0 rows affected (0.01 sec) mysql> create table ptime3 (id int, createdate datetime) engine = myisam partition by range (createdate) (partition po values less than (20100801), partition p1 values l Ess than (20100901); ERROR 1491 (HY000): The PARTITION function returns the wrong type is not allowed to directly use The time column. the columns returned by The range partition function must be integer. Mysql> create table ptime6 (id int, createdate datetime) engine = myisam partition by range (year (createdate) (partition po values less than (2010 ), partition p1 values less than (2011); Query OK, 0 rows affected (0.01 sec) can also be partitioned using the annual function.
7. mysql available partition functions DAY () DAYOFMONTH () DAYOFWEEK () DAYOFYEAR () DATEDIFF () EXTRACT () HOUR () MICROSECOND () MINUTE () MOD () MONTH () QUARTER () SECOND () TIME_TO_SEC () TO_DAYS () WEEKDAY () YEAR () YEARWEEK (), of course, FLOOR (), CEILING (), etc, the premise is that the partition key for using the two partition functions must be an integer. Be careful when using some of these functions to avoid logical errors and cause full table scanning. For example, create table ptime11 (id int, createdate datetime) engine = myisam partition by range (day (createdate) (partition po values less than (15 ), partition p1 values less than (31); mysql> insert into ptime11 values (1, '2017-06-17 '); mysql> explain partitions select count (1) from ptime11 where createdate> '2017-08-17 '/G; * *************************** 1. row ************************* id: 1select_type: SIMPLE table: ptime11 partitions: po, p1 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 Extra: Using where1 row in set (0.00 sec)
8. primary key and constraint test partition key is not included in the primary key mysql> create table pprimary (id int, createdate datetime, primary key (id )) engine = myisam partition by range (day (createdate) (partition po values less than (15), partition p1 values less than (31); ERROR 1503 (HY000 ): a primary key must include all columns in the table's partitioning function partition key included in the primary KEY mysql> create table pprimary1 (id int, createdate datetime, PRIMARY key (id, created Ate) engine = myisam partition by range (day (createdate) (partition po values less than (15), partition p1 values less than (31); Query OK, 0 rows affected (0.05 sec) indicates that the partition key must be included in the primary key. Mysql> create table pprimary2 (id int, createdate datetime, uid char (10), primary key (id, createdate), unique key (uid )) engine = myisam partition by range (to_days (createdate) (partition p0 values less than (20100801), partition p1 values less than (20100901); ERROR 1503 (HY000 ): a unique index must include all columns in the table's partitioning function indicates that there is A problem in creating A constrained INDEX on the table. the constrained INDEX column must be included in the partition key.
Mysql> create table pprimary3 (id int, createdate datetime, uid char (10), primary key (id, createdate), unique key (createdate )) engine = myisam partition by range (to_days (createdate) (partition p0 values less than (20100801), partition p1 values less than (20100901); Query OK, 0 rows affected (0.00 sec) although you can add a constraint index to a table, it is only included in the partition key. this problem may occur in actual application, this problem may be improved in later MYSQL versions.
9. in the subpartition test, only the RANGE and LIST partitions can have subpartitions. the number of subpartitions in each partition must be the same. mysql> create table pprimary7 (id int, createdate datetime, uid char (10 ), primary key (id, createdate) engine = myisam partition by range (to_days (createdate) subpartition by hash (to_days (createdate) (partition p0 values less than (20100801) (subpartition so, subpartition s1), partition p1 values less than (20100901) (subpartition s0, subpartition s1); ERROR 1517 (HY000): Duplicate partition name s1 prompts a Duplicate partition name error, which is different from the description in the MYSQL5.1 help document. I wonder if this problem has been modified in a small version.
10. test the MYSQL partition key NULL value. MYSQL regards the NULL value as 0. automatically inserts the smallest partition.
11. MYSQL partition management test mysql> alter table pprimary4 truncate partition p1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'truncate partition P1' at line 15.1 does not support this syntax, which is already supported in MySQL 5.5, a good command; alter table reorganize can reorganize partitions. Author: sss0213bitsCN.com
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.