mysql 5.6分区表运用
发布时间：2022-03-21 18:16:07 所属栏目：MySql教程 来源：互联网
导读：mysql 5.6分区表测试： 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 产品ID, `price` decimal(15,2) NOT NULL COMMENT 单价, `
|
mysql 5.6分区表测试： 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 '产品ID', `price` decimal(15,2) NOT NULL COMMENT '单价', `num` int(11) NOT NULL COMMENT '购买数量', `uid` int(10) unsigned NOT NULL COMMENT '客户ID', `atime` datetime NOT NULL COMMENT '下单时间', `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间', `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识', PRIMARY KEY (`id`,`atime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); /****用时0.084s****/ SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); /****用时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 | +----+-------------+----------+------------+------+---------------+------+---------+------+-------+-------------+ 如果采用where id and atime查询，则通过主键索引可以查询，且可以用到分区 /*****************HASH 分区表*****************/ CREATE TABLE `msgs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键', `sender` int(10) unsigned NOT NULL COMMENT '发送者ID', `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID', `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型', `msg` varchar(225) NOT NULL COMMENT '消息内容', `atime` int(10) unsigned NOT NULL COMMENT '发送时间', `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID', PRIMARY KEY (`id`,`sub_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*********分区信息**************/ PARTITION BY HASH(sub_id) PARTITIONS 10; /*****************LIST分区表*********************/ CREATE TABLE `products` ( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '表主键' , `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称' , `metrial` tinyint UNSIGNED NOT NULL COMMENT '材质' , `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 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
