Mysql 5.6 Partition Table Application 03/31 Update SLTechnology News&Howtos

Mysql 5.6 Partition Table Application

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Mysql 5.6 Partition Table Test:

DROP TABLE IF EXISTS `my_ orders`

CREATE TABLE `my_ orders` (

`id`int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'table primary key'

`pid`int (10) unsigned NOT NULL COMMENT 'product ID'

`price`decimal (15jin2) NOT NULL COMMENT 'unit price'

`num`int (11) NOT NULL COMMENT 'purchase quantity'

`uid` int (10) unsigned NOT NULL COMMENT 'customer ID'

`atime`datetime NOT NULL COMMENT 'time to place the order

`utime`int (10) unsigned NOT NULL DEFAULT 0 COMMENT 'modification time'

`isdel`tinyint (4) NOT NULL DEFAULT'0' COMMENT 'soft delete ID'

PRIMARY KEY (`id`, `atime`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * Zone Information * /

PARTITION BY RANGE (YEAR (atime))

(

PARTITION p0 VALUES LESS THAN (2016)

PARTITION p1 VALUES LESS THAN (2017)

PARTITION p2 VALUES LESS THAN MAXVALUE

);

DROP TABLE IF EXISTS `my_ order`

CREATE TABLE `my_ order` (

`id`int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'table primary key'

`pid`int (10) unsigned NOT NULL COMMENT 'product ID'

`price`decimal (15jin2) NOT NULL COMMENT 'unit price'

`num`int (11) NOT NULL COMMENT 'purchase quantity'

`uid` int (10) unsigned NOT NULL COMMENT 'customer ID'

`atime`datetime NOT NULL COMMENT 'time to place the order

`utime`int (10) unsigned NOT NULL DEFAULT 0 COMMENT 'modification time'

`isdel`tinyint (4) NOT NULL DEFAULT'0' COMMENT 'soft delete ID'

PRIMARY KEY (`id`, `atime`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * * insert data into the partition table * /

INSERT INTO my_orders (`pid`, `price`, `num`, `uid`, `atime`) VALUES

/ * * insert data into an unpartitioned table * /

INSERT INTO my_order (`pid`, `price`, `num`, `uid`, `atime`) VALUES

/ * * copy a large amount of data from master / slave * * /

INSERT INTO `my_ orders` (`pid`, `price`, `num`, `uid`, `atime`) SELECT `pid`, `price`, `num`, `uid`, `atime`FROM `my_ orders`

INSERT INTO `my_ order` (`pid`, `price`, `num`, `uid`, `atime`) SELECT `pid`, `price`, `num`, `uid`, `atime`FROM `my_ order`

/ * query performance Analysis * /

SELECT * FROM `my_ orders` WHERE `uid` = 89757 AND `atime` < CURRENT_TIMESTAMP ()

/ * 0.084s****/ when in use

SELECT * FROM `my_ order` WHERE `uid` = 89757 AND `atime` < CURRENT_TIMESTAMP ()

/ * 0.284s****/ when in use

EXPLAIN PARTITIONS SELECT * FROM `my_ orders` WHERE `uid` = 89757 AND `atime` < CURRENT_TIMESTAMP ()

+-- +

+-- +

+-- +

EXPLAIN PARTITIONS SELECT * FROM `my_ order` WHERE `uid` = 89757 AND `atime` < '2018-05-01 0000 my_ 00'

+-- +

+-- +

+-- +

If the query has only partition keys, partition clipping can also be used, but no index is used

EXPLAIN PARTITIONS SELECT * FROM `my_ orders` WHERE `id` = 36 AND `atime` < CURRENT_TIMESTAMP ()

+-- +

+-- +

+-- +

EXPLAIN PARTITIONS SELECT * FROM `my_ order` WHERE `id` = 36 AND `atime` < CURRENT_TIMESTAMP ()

+-- +

+-- +

+-- +

If where id and atime query is used, it can be queried through the primary key index, and partitioning can be used.

/ * HASH partition table * /

CREATE TABLE `msgs` (

`id`bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'table primary key'

`sender`int (10) unsigned NOT NULL COMMENT 'sender ID'

`roomver` int (10) unsigned NOT NULL COMMENT 'recipient ID'

`msg_ type`tinyint (3) unsigned NOT NULL COMMENT 'message type'

`msg` varchar (225) NOT NULL COMMENT 'message content'

`atime`int (10) unsigned NOT NULL COMMENT 'send time'

`sub_ id`tinyint (3) unsigned NOT NULL COMMENT 'department ID'

PRIMARY KEY (`id`, `sub_ id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * Zone Information * /

PARTITION BY HASH (sub_id)

PARTITIONS 10

/ * LIST partition table * /

CREATE TABLE `products` (

`id`bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'table primary key

`name`varchar (64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Product name'

`metrial`tinyint UNSIGNED NOT NULL COMMENT 'material'

`weight`double UNSIGNED NOT NULL DEFAULT 0 COMMENT 'weight'

`vol`double UNSIGNED NOT NULL DEFAULT 0 COMMENT 'volume'

`cid`tinyint UNSIGNED NOT NULL COMMENT 'supplier ID'

PRIMARY KEY (`id`, `cid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * Zone Information * /

PARTITION BY LIST (c_id)

(

PARTITION pA VALUES IN (1, 3, 11, 13)

PARTITION pB VALUES IN (2, 4, 12, 14)

PARTITION pC VALUES IN (5, 7, 15, 17)

PARTITION pD VALUES IN (6, 8, 16, 18)

PARTITION pE VALUES IN (9, 10, 19, 20)

);

Create a partitioned table: the primary key index is part of the partitioning key

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.