在實際項目開發中經常需要對各種操作日志進行保存，時間一久數據量就變得很大，所以當面臨對這些表直接查詢時，往往都會耗費較長的時間，從而影響用戶的體驗。由于這些表中都是會保存時間序列，并且在具體業務中時間跨度比較小，所以可以通過按月或者按天的操作來進行分表，從而降低查詢的代價，提高查詢的速度。在postgresql中，可以利用內置的INHERIENTS機制來實現分區，降低整個代價。
在postgrelsql文檔中，主要描述了以下幾個優點： 1）當分區之后，大多數對該表的查詢都集中在一個或者幾個分區內，查詢性能會得到大幅度的提升。因為其降低了索引的大小，使得其索引結構在內存中的結構更合理。 Ps:只是知道數據庫中的索引結構是B-TREE或者B+TREE，這個怎么優化了不是很了解。 2）當查詢或者更新某個分區的大部分條目時，可以利用該分區的序列掃描而不用借助索引或者隨機訪問的方式來進行，因此也能改善該性能。 3）對某一塊（某段時間或者某個范圍內）數據的加載和刪除時，可以明顯改善性能 4）很少訪問，使用的數據可以移動到成本更低的低速存儲設施中。
Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can PRovide several benefits: Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory. When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index and random access reads scattered across the whole table. Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. ALTER TABLE NO INHERIT and DROP TABLE are both far faster than a bulk Operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE. Seldom-used data can be migrated to cheaper and slower storage media.在實際的開發中，博主是在插入數據時實時檢測分區是否存在，不存在則創建。 由于是在Spring中進行開發，此處博主用的是JDBCTemplate（可以通過@Autowired和@Qualifier來按bean名進行區分注入）來進行數據庫的DDL操作的。代碼如下：
private synchronized void checkExistTable(Long operateTime) { Long month = Long.valueOf(DateFormatUtils.format(operateTime, DateTimeUtil.YYYYMM)); if(existDate.contains(month)) { return; } boolean isExist = operationRecordMapper.checkOperateRecordTable("operate_record_" + month); if(!isExist) { Date startDate = DateUtils.truncate(new Date(operateTime), Calendar.MONTH); Date endDate = DateUtils.addMonths(startDate, 1); jdbcTemplate.execute("create table operation_record_" + month + "(check(operation_time>= " + startDate.getTime() + " and operation_time<" + endDate.getTime() + "), LIKE operation_record including CONSTRAINTS including DEFAULTS including indexes) INHERITS(operation_record)"); jdbcTemplate.execute("create rule rule_operation_record_" + month + " as on insert to operation_record WHERE (operation_time >=" + startDate.getTime() + " and operation_time< " + endDate.getTime() + ") DO INSTEAD INSERT INTO operation_record_" + month + "(id, target_type, reference_id, operation_type, content, remark, operation_user, operation_time) VALUES (NEW.id, NEW.target_type, NEW.reference_id, NEW.operation_type, NEW.content, NEW.remark, NEW.operation_user, NEW.operation_time)"); existDate.add(month); }}定義規則：
create rule rule_operation_record_" + month + " as on insert to operation_record WHERE (operation_time >=" + startDate.getTime() + " and operation_time< " + endDate.getTime() + ") DO INSTEAD INSERT INTO operation_record_" + month + "(id, target_type, reference_id, operation_type, content, remark, operation_user, operation_time) VALUES (NEW.id, NEW.target_type, NEW.reference_id, NEW.operation_type, NEW.content, NEW.remark, NEW.operation_user, NEW.operation_time)"); existDate.add(month);新聞熱點
疑難解答
