MySQL数据库分区

数据库分区处理

如果一张表的数据量太大的话&＃xff0c;myd、myi就会变得很大&＃xff0c;查找数据就会变的很慢&＃xff0c;我接触到的是有关温州出租车网约车GPS数据量的查询&＃xff0c;大概数据量为1天4000万条记录&＃xff0c;不分区查询速度慢到怀疑人生。在物理上我们可以把数据表分割成不同的小块&＃xff0c;查找数据只需要查找需要的那一块数据即可&＃xff0c;查询速度大大提升。

我们项目中我只用到了rang分区&＃xff1a;
参考mysql分区

range分区

自动分区

create table cb03_cbrfid(
ZWMC VARCHAR2(50),
DTID VARCHAR2(50),
RFID VARCHAR2(50),
CBSBH VARCHAR2(50),
CBLX VARCHAR2(50),
NUM NUMBER(10),
ZDW VARCHAR2(50) ,
SJ DATE ,
ZZZ VARCHAR2(50) ,
ZZXCG VARCHAR2(50),
JD NUMBER(19,16),
WD NUMBER(19,16),
WZ VARCHAR2(50),
RESERVED1 FLOAT(8),
RESERVED2 FLOAT(8),
XXLX INTEGER ,
GXSJ DATE )partition by range(gxsj)interval(numtodsinterval(1,&＃39;day&＃39;))( partition cbrfid1 values less than(to_date(&＃39;2017-10-15 00:00:00&＃39;,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)));

partition by range (字段名)
(partition 分区名 values less than (TO_DATE(&＃39; 2014-07-25 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR&＃61;GREGORIAN&＃39;))
);

alter table 表名 add partition 分区名 values less than (TO_DATE(&＃39; 2014-07-27 00:00:00, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;))

create table T_WYC_GPS_HISTORY
(vehicle_no VARCHAR2(20),vehicle_color VARCHAR2(8),alarm_id VARCHAR2(32),status VARCHAR2(32),lat VARCHAR2(32),lon VARCHAR2(32),speed VARCHAR2(16),direction VARCHAR2(8),geometry MDSYS.SDO_GEOMETRY,load_time DATE,pre_geohash VARCHAR2(20),last_geohash VARCHAR2(20),platform_id INTEGER
)
partition by range (LOAD_TIME)
(partition T_WYC_GPS_HISTORY_20180226 values less than (TO_DATE(&＃39; 2018-02-27 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR&＃61;GREGORIAN&＃39;))tablespace WZYG_WYC_GPSpctfree 10initrans 1maxtrans 255storage(initial 8Mnext 1Mminextents 1maxextents unlimited),partition T_WYC_GPS_HISTORY_20180227 values less than (TO_DATE(&＃39; 2018-02-28 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR&＃61;GREGORIAN&＃39;))tablespace USERSpctfree 10initrans 1maxtrans 255storage(initial 8Mnext 1Mminextents 1maxextents unlimited),partition T_WYC_GPS_HISTORY_20180228 values less than (TO_DATE(&＃39; 2018-03-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR&＃61;GREGORIAN&＃39;))tablespace USERSpctfree 10initrans 1maxtrans 255storage(initial 8Mnext 1Mminextents 1maxextents unlimited),