心得笔记网站首页 心得笔记

mysql按时间字段进行数据表分区

发布时间：2018-08-12 07:32:16编辑：雪饮阅读（）

Mysql环境:

5.5.54-log

表分区类型很多，这里是最常见的按时间进行数据表分区示例

示例1：

这里的时间字段是字符串时间格式，而不是unix时间戳的秒数。

DROP TABLE IF EXISTS `s_order`;

CREATE TABLE `s_order` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`user_id` bigint(20) NOT NULL,

`state` char(4) DEFAULT '0' COMMENT '-1:退款',

`businesses_id` int(8) NOT NULL COMMENT '商家的id',

`staff_receive` int(10) DEFAULT NULL COMMENT '收件人（店铺所派去客户处取件人）',

`receive_time` char(30) DEFAULT NULL COMMENT '收件中的时间（确认订单的时间,派快递员去取件时间）',

`start_time` char(25) DEFAULT NULL COMMENT '订单开始的时间',

`start_time_str` char(255) DEFAULT NULL COMMENT '下单时间（区分上午还是下午）',

`finish_time` char(25) DEFAULT NULL COMMENT '订单完成的时间(也是配送完成时间)',

`remind` varchar(255) DEFAULT NULL COMMENT '催单',

`remindReturn` varchar(255) DEFAULT NULL,

`remindint` int(11) DEFAULT '0',

`remark` varchar(50) DEFAULT NULL COMMENT '备注',

`username` varchar(8) DEFAULT NULL COMMENT '用户的名字',

`userphone` varchar(12) DEFAULT NULL COMMENT '用户的电话',

`useraddress` varchar(80) DEFAULT NULL COMMENT '用户地址',

`userdelete` char(2) DEFAULT '0' COMMENT '用户是否删除（0是不删除，1是删除）',

`money` decimal(10,2) DEFAULT '0.00' COMMENT '总计',

`after_sale` char(80) DEFAULT NULL COMMENT '售后的理由',

`cancel_reason` char(70) DEFAULT NULL COMMENT '取消的理由',

`washtypeinfo` char(100) DEFAULT NULL COMMENT '洗护简介',

`address_lng` char(50) DEFAULT NULL COMMENT '地址的经度',

`address_lat` char(50) DEFAULT NULL COMMENT '地址的维度',

`clothescount` smallint(5) DEFAULT NULL COMMENT '衣物的数量',

`finishcount` smallint(5) DEFAULT NULL COMMENT '洗护完毕的数量',

`transport_time` char(25) DEFAULT '\\' COMMENT '运输时间（店长向工厂发货）',

`wash_time` char(25) DEFAULT NULL COMMENT '洗护中的时间(也是工厂确认收货的时间)',

`user_make_time` char(25) DEFAULT NULL COMMENT '客户预约的取件时间',

`photos_state` char(2) DEFAULT '0' COMMENT '拍照状态:\r\n默认0，待拍照1，已经拍照2',

`businesses_name` char(10) DEFAULT NULL COMMENT '店面的名字',

`ifarrive` int(11) DEFAULT '0',

`businessToFactoryState` int(11) DEFAULT '0',

`businessToFactoryTime` char(255) DEFAULT '""' COMMENT '从店铺发出时间',

`factoryId` int(11) DEFAULT NULL COMMENT '工厂id',

`takePartCompleteTime` char(100) DEFAULT NULL COMMENT '从客户手中取件完成（上传照片接口更新此时间）',

`beingDeliveredTiime` char(100) DEFAULT NULL COMMENT '正在配送时间',

`pendingEvaluationTime` varchar(255) DEFAULT NULL COMMENT '评价时间',

`S_finish_time` varchar(100) DEFAULT NULL COMMENT '用户端订单已完成时间',

`del` int(11) DEFAULT '0' COMMENT '0未删除，1已删除',

`pay` int(11) DEFAULT '0' COMMENT '0未支付，1已支付，2退款',

`jpushStatus` int(11) DEFAULT '1' COMMENT '用户下单后给商家端推送,1成功0失败,-1暂无',

`jpushUserStatus` int(11) DEFAULT '-1' COMMENT '运输员到用户家取件完成（拍照完成）的推送，1成功，0失败，-1暂无',

`jpushAllStatus` int(11) DEFAULT '-1',

`jpushStaffStatus` int(11) DEFAULT '-1' COMMENT '店长接了用户的单子派给指定运输员后给该运输员的推送，0失败，1成功，-1暂无',

`payType` int(11) DEFAULT '-1' COMMENT '0线下支付，1微信支付，2支付宝支付,3余额支付,4优惠活动0元支付',

`price` varchar(255) DEFAULT '0' COMMENT '没有产生优惠前的原价',

`factoryReceivingJpushStatus` int(11) DEFAULT '-1' COMMENT '工厂端收货后给店铺推送情况,1为成功，0为失败,-1暂无',

`xhzgyhtsStatus` int(11) DEFAULT '-1' COMMENT '-1暂无状态，1推送成功，0推送失败（洗护中给用户推送）',

`Cancellationtime` int(11) NOT NULL DEFAULT '0',

`qId` int(255) DEFAULT '0' COMMENT '用户手上的券id，不是真正的券id',

`qType` char(255) DEFAULT '' COMMENT '券类型(可选)，0或1为优惠券，2为代金券，3为折扣券',

`out_trade_no` varchar(255) DEFAULT '' COMMENT '微信或支付宝的订单号',

`state2` char(255) DEFAULT '' COMMENT '运输员从客户手中取件完成进行拍照后给店长的推送,1成功,0失败',

`wash_time_int` bigint(255) DEFAULT '0' COMMENT '洗护时间(工厂接收订单时间)int',

`refundState` int(11) NOT NULL DEFAULT '0',

`userCancel` int(11) DEFAULT '0',

`refuse` varchar(50) DEFAULT '0' COMMENT '1拒接',

PRIMARY KEY (`id`,`start_time`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

PARTITION BY RANGE COLUMNS(start_time)(

PARTITION p201707 VALUES LESS THAN ('2017-01-01'),

PARTITION p201801 VALUES LESS THAN ('2018-01-01'),

PARTITION p201809 VALUES LESS THAN (MAXVALUE)

);

示例2：

这里的时间是unix时间戳的秒数

DROP TABLE IF EXISTS `ims_ewei_shop_order`;

CREATE TABLE `ims_ewei_shop_order` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`uniacid` int(11) DEFAULT '0',

`openid` varchar(50) DEFAULT '',

`agentid` int(11) DEFAULT '0',

`ordersn` varchar(30) DEFAULT '',

`price` decimal(10,2) DEFAULT '0.00',

`goodsprice` decimal(10,2) DEFAULT '0.00',

`discountprice` decimal(10,2) DEFAULT '0.00',

`status` tinyint(3) DEFAULT '0',

`paytype` tinyint(1) DEFAULT '0',

`transid` varchar(30) DEFAULT '0',

`remark` varchar(1000) DEFAULT '',

`addressid` int(11) DEFAULT '0',

`dispatchprice` decimal(10,2) DEFAULT '0.00',

`dispatchid` int(10) DEFAULT '0',

`createtime` int(10) NOT NULL DEFAULT '0',

`dispatchtype` tinyint(3) DEFAULT '0',

`carrier` text,

`refundid` int(11) DEFAULT '0',

`iscomment` tinyint(3) DEFAULT '0',

`creditadd` tinyint(3) DEFAULT '0',

`deleted` tinyint(3) DEFAULT '0',

`userdeleted` tinyint(3) DEFAULT '0',

`finishtime` int(11) DEFAULT '0',

`paytime` int(11) DEFAULT '0',

`expresscom` varchar(30) NOT NULL DEFAULT '',

`expresssn` varchar(50) NOT NULL DEFAULT '',

`express` varchar(255) DEFAULT '',

`sendtime` int(11) DEFAULT '0',

`fetchtime` int(11) DEFAULT '0',

`cash` tinyint(3) DEFAULT '0',

`canceltime` int(11) DEFAULT NULL,

`cancelpaytime` int(11) DEFAULT '0',

`refundtime` int(11) DEFAULT '0',

`isverify` tinyint(3) DEFAULT '0',

`verified` tinyint(3) DEFAULT '0',

`verifyopenid` varchar(255) DEFAULT '',

`verifycode` varchar(255) DEFAULT '',

`verifytime` int(11) DEFAULT '0',

`verifystoreid` int(11) DEFAULT '0',

`deductprice` decimal(10,2) DEFAULT '0.00',

`deductcredit` int(10) DEFAULT '0',

`deductcredit2` decimal(10,2) DEFAULT '0.00',

`deductenough` decimal(10,2) DEFAULT '0.00',

`virtual` int(11) DEFAULT '0',

`virtual_info` text,

`virtual_str` text,

`address` text,

`sysdeleted` tinyint(3) DEFAULT '0',

`ordersn2` int(11) DEFAULT '0',

`changeprice` decimal(10,2) DEFAULT '0.00',

`changedispatchprice` decimal(10,2) DEFAULT '0.00',

`oldprice` decimal(10,2) DEFAULT '0.00',

`olddispatchprice` decimal(10,2) DEFAULT '0.00',

`isvirtual` tinyint(3) DEFAULT '0',

`couponid` int(11) DEFAULT '0',

`couponprice` decimal(10,2) DEFAULT '0.00',

`diyformdata` text,

`diyformfields` text,

`diyformid` int(11) DEFAULT '0',

`storeid` int(11) DEFAULT '0',

`printstate` tinyint(1) DEFAULT '0',

`printstate2` tinyint(1) DEFAULT '0',

`address_send` text,

`refundstate` tinyint(3) DEFAULT '0',

`closereason` text,

`remarksaler` text,

`remarkclose` text,

`remarksend` text,

`ismr` int(1) NOT NULL DEFAULT '0',

`isdiscountprice` decimal(10,2) DEFAULT '0.00',

`isvirtualsend` tinyint(1) DEFAULT '0',

`virtualsend_info` text,

`verifyinfo` text,

`verifytype` tinyint(1) DEFAULT '0',

`verifycodes` text,

`invoicename` varchar(255) DEFAULT '',

`merchid` int(11) DEFAULT '0',

`ismerch` tinyint(1) DEFAULT '0',

`parentid` int(11) DEFAULT '0',

`isparent` tinyint(1) DEFAULT '0',

`grprice` decimal(10,2) DEFAULT '0.00',

`merchshow` tinyint(1) DEFAULT '0',

`merchdeductenough` decimal(10,2) DEFAULT '0.00',

`couponmerchid` int(11) DEFAULT '0',

`isglobonus` tinyint(3) DEFAULT '0',

`merchapply` tinyint(1) DEFAULT '0',

`isabonus` tinyint(3) DEFAULT '0',

`isborrow` tinyint(3) DEFAULT '0',

`borrowopenid` varchar(100) DEFAULT '',

`merchisdiscountprice` decimal(10,2) DEFAULT '0.00',

`apppay` tinyint(3) NOT NULL DEFAULT '0',

`coupongoodprice` decimal(10,2) DEFAULT '1.00',

`buyagainprice` decimal(10,2) DEFAULT '0.00',

`authorid` int(11) DEFAULT '0',

`isauthor` tinyint(1) DEFAULT '0',

`ispackage` tinyint(3) DEFAULT '0',

`packageid` int(11) DEFAULT '0',

`taskdiscountprice` decimal(10,2) NOT NULL DEFAULT '0.00',

`seckilldiscountprice` decimal(10,2) DEFAULT '0.00',

`verifyendtime` int(11) NOT NULL DEFAULT '0',

`willcancelmessage` tinyint(1) DEFAULT '0',

`sendtype` tinyint(3) NOT NULL DEFAULT '0',

`lotterydiscountprice` decimal(10,2) NOT NULL DEFAULT '0.00',

`contype` tinyint(1) DEFAULT '0',

`wxid` int(11) DEFAULT '0',

`wxcardid` varchar(50) DEFAULT '',

`wxcode` varchar(50) DEFAULT '',

`dispatchkey` varchar(30) NOT NULL DEFAULT '',

`quickid` int(11) NOT NULL DEFAULT '0',

`istrade` tinyint(3) NOT NULL DEFAULT '0',

`isnewstore` tinyint(3) NOT NULL DEFAULT '0',

`liveid` int(11) DEFAULT NULL,

`ordersn_trade` varchar(32) DEFAULT NULL,

`tradestatus` tinyint(1) DEFAULT '0',

`tradepaytype` tinyint(1) DEFAULT NULL,

`tradepaytime` int(11) DEFAULT '0',

`dowpayment` decimal(10,2) NOT NULL DEFAULT '0.00',

`betweenprice` decimal(10,2) NOT NULL DEFAULT '0.00',

`isshare` int(11) NOT NULL DEFAULT '0',

`officcode` varchar(50) NOT NULL DEFAULT '',

`wxapp_prepay_id` varchar(100) DEFAULT NULL,

`syssn` char(100) DEFAULT '' COMMENT '钱方syssn',

`zsje` decimal(10,2) DEFAULT '0.00',

`cache` text,

`cacheType` int(11) DEFAULT NULL,

PRIMARY KEY (`id`,`createtime`) USING BTREE,

KEY `idx_uniacid` (`uniacid`) USING BTREE,

KEY `idx_openid` (`openid`) USING BTREE,

KEY `idx_shareid` (`agentid`) USING BTREE,

KEY `idx_status` (`status`) USING BTREE,

KEY `idx_createtime` (`createtime`) USING BTREE,

KEY `idx_refundid` (`refundid`) USING BTREE,

KEY `idx_paytime` (`paytime`) USING BTREE,

KEY `idx_finishtime` (`finishtime`) USING BTREE,

KEY `idx_merchid` (`merchid`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

PARTITION BY RANGE (createtime)

(

PARTITION p20180807 VALUES LESS THAN (1533571200),

PARTITION p20181201 VALUES LESS THAN (1543593600),

PARTITION p20190601 VALUES LESS THAN (1559318400),

PARTITION p20191201 VALUES LESS THAN (1575129600),

PARTITION p20200601 VALUES LESS THAN (1590940800),

PARTITION p20201201 VALUES LESS THAN (1606752000),

PARTITION pfuture VALUES LESS THAN MAXVALUE

);

查看分区及查询效率分析

mysql> explain partitions select count(*) from ims_ewei_shop_order;

+----+-------------+---------------------+---------------------------------------------------------------------+-------+---------------+------------+---------+------+------+-------------+

+----+-------------+---------------------+---------------------------------------------------------------------+-------+---------------+------------+---------+------+------+-------------+

+----+-------------+---------------------+---------------------------------------------------------------------+-------+---------------+------------+---------+------+------+-------------+

1 row in set

分区建立成功后用navicat也可以查看到分区情况

开启sql慢查询分析

开启慢查询分析后，每条sql执行会被记录在一个表中，每条记录可以查看到执行的查询时间

mysql> set profiling=1;

Query OK, 0 rows affected

mysql> show profiles;

Empty set

mysql> select count(*) from ims_ewei_shop_order;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set

mysql> show profiles;

+----------+----------+------------------------------------------+

| Query_ID | Duration | Query |

+----------+----------+------------------------------------------+

| 1 | 0.00036 | select count(*) from ims_ewei_shop_order |

+----------+----------+------------------------------------------+

1 row in set

mysql>

注意：

分区后查询时也要在where中使用上该时间字段方能有性能提升的效果.

被用来进行分区的字段必须是主键,另外表中貌似不能有外键约束。

关键字词：

上一篇：马哥linux运维学习笔记-脚本编程之八脚本完成磁盘分区格式化

下一篇：马哥linux运维学习笔记-Linux网络配置之四 ifconfig及ip命令详解

无相关信息

您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记

mysql按时间字段进行数据表分区

相关文章

您当前的位置：首页 > 学无止境 > 心得笔记网站首页心得笔记