您好,登錄后才能下訂單哦!
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)買數(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ū)信息**************/
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)買數(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 '消息類型',
`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 '部門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)品名稱' ,
`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ū)鍵的一部分
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。