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

具体的操作&＃xff1a;

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

最后分区示例&＃xff1a;

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),