log_gold_cahngeテーブル対応 #863

対象テーブル

jrye.log_gold_change
jrye_log.log_gold_change
jrye_log.search_log_gold_change

対応方法

1. log_gold_changeを同構成の新テーブルとスイッチ
2. 集計に必要なデータを旧テーブルから移動
当日のログデータ（0,6時に昨日のデータをバッチ内で取得するので、移動は6時までか6時以降が良い）
3. 保管用テーブル（パーティション化）を作成
4. バッチを設定して一定期間ごとに保管用テーブルに移動
del_log_gold_change.inc

テーブル参照箇所

• log_gold_change

・当日のゴールド用途別消費量集計バッチ（cpanel_daily_gold_analytics）
・管理画面のゴールド消費ログ（m_user_detail_gold_log）
・管理画面紅玉消費ログ（m_user_detail_red_ball）
・前日の合計集金合計バッチ（daily_user_collect_gold）
・ログ移動バッチ（del_log_gold_change）

関連SQL

#テーブル作成
CREATE TABLE `log_gold_change` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_no` int(11) NOT NULL,
  `status` smallint(6) unsigned NOT NULL COMMENT '1:建築、2：造船、3：雇用、4：集金、5：プレゼントBOX、6:ミッションモンス、7:ミッションアイテム、8:カード合成、9:交易、15:建築キャンセル',
  `info` int(11) NOT NULL DEFAULT '0',
  `gold` int(11) NOT NULL,
  `after_gold` int(11) NOT NULL,
  `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`reg_date`),
  KEY `user_no` (`user_no`,`status`),
  KEY `reg_date` (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='gold消費ﾛｸﾞ'

#パーティション化
ALTER TABLE log_gold_change
PARTITION BY RANGE ( UNIX_TIMESTAMP(reg_date) ) (
  PARTITION p202106 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-07-01 00:00:00') ),
  PARTITION p202107 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-08-01 00:00:00') ),
  PARTITION p202108 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-09-01 00:00:00') ),
  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') )
);

#パーティション追加
ALTER TABLE bucket ADD PARTITION (
    PARTITION p202501 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-02-01 00:00:00') ),
    PARTITION p202502 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-03-01 00:00:00') ),
    PARTITION p202503 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-04-01 00:00:00') ),
    PARTITION p202504 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-05-01 00:00:00') ),
    PARTITION p202505 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-06-01 00:00:00') ),
    PARTITION p202506 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-07-01 00:00:00') ),
    PARTITION p202507 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-08-01 00:00:00') ),
    PARTITION p202508 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-09-01 00:00:00') ),
    PARTITION p202509 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-10-01 00:00:00') ),
    PARTITION p202510 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-11-01 00:00:00') ),
    PARTITION p202511 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-12-01 00:00:00') ),
    PARTITION p202512 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_gold_change__new';

EXPLAIN PARTITIONS SELECT * FROM log_gold_change__new;

#切り替え
RENAME TABLE log_gold_change TO log_gold_change__old, log_gold_change__new TO log_gold_change;