MySQL 5.6分區(qū)表測(cè)試：
DROP TABLE IF EXISTS `my_orders`;
CREATE TABLE `my_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
  `pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID',
  `price` decimal(15,2) NOT NULL COMMENT '單價(jià)',
  `num` int(11) NOT NULL COMMENT '購(gòu)買(mǎi)數(shù)量',
  `uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
  `atime` datetime NOT NULL COMMENT '下單時(shí)間',
  `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時(shí)間',
  `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標(biāo)識(shí)',
  PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


/*********分區(qū)信息**************/

站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到昭化網(wǎng)站設(shè)計(jì)與昭化網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類(lèi)型包括：成都做網(wǎng)站、網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、主機(jī)域名、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋昭化地區(qū)。

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 '表主鍵',
  `pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID',
  `price` decimal(15,2) NOT NULL COMMENT '單價(jià)',
  `num` int(11) NOT NULL COMMENT '購(gòu)買(mǎi)數(shù)量',
  `uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
  `atime` datetime NOT NULL COMMENT '下單時(shí)間',
  `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時(shí)間',
  `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標(biāo)識(shí)',
  PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/**************************向分區(qū)表插入數(shù)據(jù)****************************/

INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');

/**************************向未分區(qū)表插入數(shù)據(jù)****************************/

INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00'；

/**********************************主從復(fù)制大量數(shù)據(jù)******************************/

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`;

/***************************查詢性能分析**************************************/

SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
/****用時(shí)0.084s****/
SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
/****用時(shí)0.284s****/
EXPLAIN PARTITIONS SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | my_orders | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL | 16419 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+-------------+
EXPLAIN PARTITIONS SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< '2018-05-01 00:00:00';
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | my_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 32099 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+-------------+
如果查詢只有分區(qū)鍵，也可以用到分區(qū)裁剪，但沒(méi)有用到索引
EXPLAIN PARTITIONS SELECT * FROM `my_orders` WHERE `id`=36 AND `atime`< CURRENT_TIMESTAMP();;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | my_orders | p0,p1      | range | PRIMARY       | PRIMARY | 9       | NULL |    2 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+-------------+
EXPLAIN PARTITIONS SELECT * FROM `my_order` WHERE `id`=36 AND `atime`< CURRENT_TIMESTAMP();;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | my_order | NULL       | range | PRIMARY       | PRIMARY | 9       | NULL |    1 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+-------------+


如果采用where id and atime查詢，則通過(guò)主鍵索引可以查詢，且可以用到分區(qū)

/*****************HASH 分區(qū)表*****************/

CREATE TABLE `msgs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
  `sender` int(10) unsigned NOT NULL COMMENT '發(fā)送者ID',
  `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',
  `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息類(lèi)型',
  `msg` varchar(225) NOT NULL COMMENT '消息內(nèi)容',
  `atime` int(10) unsigned NOT NULL COMMENT '發(fā)送時(shí)間',
  `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部門(mén)ID',
  PRIMARY KEY (`id`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*********分區(qū)信息**************/

PARTITION BY HASH(sub_id)
PARTITIONS 10;

/*****************LIST分區(qū)表*********************/

CREATE TABLE `products` (
`id`  bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '表主鍵' ,
`name`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '產(chǎn)品名稱(chēng)' ,
`metrial`  tinyint UNSIGNED NOT NULL COMMENT '材質(zhì)' ,
`weight`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '重量' ,
`vol`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '容積' ,
`c_id`  tinyint UNSIGNED NOT NULL COMMENT '供貨公司ID' ,
PRIMARY KEY (`id`,`c_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

/*********分區(qū)信息**************/

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)
);
創(chuàng)建分區(qū)表：主鍵索引是分區(qū)鍵的一部分

當(dāng)前名稱(chēng)：mysql5.6分區(qū)表應(yīng)用
當(dāng)前路徑：http://www.chinadenli.net/article14/piipge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、標(biāo)簽優(yōu)化、網(wǎng)頁(yè)設(shè)計(jì)公司、小程序開(kāi)發(fā)、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、動(dòng)態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)