log_del_presentパーティション化 #1096

log_gold_collectパーティション化 #1036

準備

• ログ移動スクリプト修正

14日後に移動

• 当日データ移動バッチ作成

log_gold_collectと同じ

• search_log_del_present_boxを置換

log_del_present_boxに置換

対応方法

1. ログdbのlog_del_presentを作り直し、パーティション化
2. 本番dbのlog_del_presentを同構成の新テーブルとスイッチ
3. 集計に必要な当日データを旧テーブルからバッチで移動
ゲーム内でプレゼントログを取得するため即時移動
or スイッチ前に移動しておく
4. ログ移動バッチを設定

ログ管理テーブル

CREATE TABLE `log_del_present_box` (
  `id` bigint(20) NOT NULL COMMENT '管理ID',
  `user_no` int(11) NOT NULL COMMENT 'ユーザーNo',
  `user_name` char(255) NOT NULL DEFAULT '' COMMENT '申請されたユーザー名',
  `sender_no` int(11) NOT NULL COMMENT '送ったユーザーNo',
  `sender_name` char(255) NOT NULL COMMENT 'ユーザー名',
  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1:ｶｰﾄﾞ 2:ｱｲﾃﾑ 3:gold,4:素材',
  `present_id` int(11) NOT NULL COMMENT 'アイテムID',
  `info` int(11) NOT NULL,
  `growth` tinyint(4) NOT NULL DEFAULT '0' COMMENT '成長タイプ：カードのみ有効',
  `count` smallint(6) NOT NULL DEFAULT '1',
  `flg` tinyint(4) NOT NULL DEFAULT '0' COMMENT '未取得:0,取得済み:1',
  `reg_date` datetime NOT NULL COMMENT '配布時間',
  `holder_id` int(11) NOT NULL,
  `receive_time` timestamp NOT NULL,
  PRIMARY KEY (`id`,`receive_time`),
  KEY `reg_date` (`reg_date`),
  KEY `receive_time` (`receive_time`),
  KEY `user_no` (`user_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='プレゼント配信キュー管理';

#パーティション化
ALTER TABLE log_del_present_box
PARTITION BY RANGE ( UNIX_TIMESTAMP(receive_time) ) (
  PARTITION p202109 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-10-01 00:00:00') ),
  PARTITION p202110 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-11-01 00:00:00') ),
  PARTITION p202111 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-12-01 00:00:00') ),
  PARTITION p202112 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-01-01 00:00:00') ),
  PARTITION p202201 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-02-01 00:00:00') ),
  PARTITION p202202 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-03-01 00:00:00') ),
  PARTITION p202203 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-04-01 00:00:00') ),
  PARTITION p202204 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-05-01 00:00:00') ),
  PARTITION p202205 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-06-01 00:00:00') ),
  PARTITION p202206 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-07-01 00:00:00') ),
  PARTITION p202207 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-08-01 00:00:00') ),
  PARTITION p202208 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-09-01 00:00:00') ),
  PARTITION p202209 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-10-01 00:00:00') ),
  PARTITION p202210 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-11-01 00:00:00') ),
  PARTITION p202211 VALUES LESS THAN ( UNIX_TIMESTAMP('2022-12-01 00:00:00') ),
  PARTITION p202212 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-01-01 00:00:00') ),
  PARTITION p202301 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-02-01 00:00:00') ),
  PARTITION p202302 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-03-01 00:00:00') ),
  PARTITION p202303 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-04-01 00:00:00') ),
  PARTITION p202304 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-05-01 00:00:00') ),
  PARTITION p202305 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-06-01 00:00:00') ),
  PARTITION p202306 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-07-01 00:00:00') ),
  PARTITION p202307 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-08-01 00:00:00') ),
  PARTITION p202308 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-09-01 00:00:00') ),
  PARTITION p202309 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-10-01 00:00:00') ),
  PARTITION p202310 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-11-01 00:00:00') ),
  PARTITION p202311 VALUES LESS THAN ( UNIX_TIMESTAMP('2023-12-01 00:00:00') ),
  PARTITION p202312 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-01-01 00:00:00') ),
  PARTITION p202401 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-02-01 00:00:00') ),
  PARTITION p202402 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-03-01 00:00:00') ),
  PARTITION p202403 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-04-01 00:00:00') ),
  PARTITION p202404 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-05-01 00:00:00') ),
  PARTITION p202405 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-06-01 00:00:00') ),
  PARTITION p202406 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-07-01 00:00:00') ),
  PARTITION p202407 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-08-01 00:00:00') ),
  PARTITION p202408 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-09-01 00:00:00') ),
  PARTITION p202409 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-10-01 00:00:00') ),
  PARTITION p202410 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-11-01 00:00:00') ),
  PARTITION p202411 VALUES LESS THAN ( UNIX_TIMESTAMP('2024-12-01 00:00:00') ),
  PARTITION p202412 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-01-01 00:00:00') )
);

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='log_del_present_box';

新テーブルとスイッチ

CREATE TABLE `log_del_present_box__new` (
  `id` bigint(20) NOT NULL COMMENT '管理ID',
  `user_no` int(11) NOT NULL COMMENT 'ユーザーNo',
  `user_name` char(255) NOT NULL DEFAULT '' COMMENT '申請されたユーザー名',
  `sender_no` int(11) NOT NULL COMMENT '送ったユーザーNo',
  `sender_name` char(255) NOT NULL COMMENT 'ユーザー名',
  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1:ｶｰﾄﾞ 2:ｱｲﾃﾑ 3:gold,4:素材　5：特産品 6:ガチャチケット 7:クリスタル 8:ベル 10:トランプ11:ハイレア以上保証チケット12:技玉メダル',
  `present_id` int(11) NOT NULL COMMENT 'アイテムID',
  `info` int(11) NOT NULL,
  `growth` tinyint(4) NOT NULL DEFAULT '0' COMMENT '成長タイプ：カードのみ有効',
  `count` int(11) NOT NULL DEFAULT '1',
  `flg` tinyint(4) NOT NULL DEFAULT '0' COMMENT '未取得:0,取得済み:1',
  `reg_date` datetime NOT NULL COMMENT '配布時間',
  `holder_id` int(11) NOT NULL DEFAULT '0',
  `receive_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `reg_date` (`reg_date`),
  KEY `sender_no` (`sender_no`),
  KEY `user_no` (`user_no`,`flg`),
  KEY `receive_time` (`receive_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='プレゼント配信キュー管理';

RENAME TABLE log_del_present_box TO log_del_present_box__old, log_del_present_box__new TO log_del_present_box;