CREATE TABLE `access_log_tbl` ( `access_log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT(11) NOT NULL COMMENT 'ユーザID', `ip` VARCHAR(255) NULL DEFAULT NULL, `user_agent` TEXT NULL COMMENT 'ユーザーエージェント', `referer` VARCHAR(200) NOT NULL COMMENT 'アクセス元URL', `url` VARCHAR(200) NOT NULL COMMENT 'アクセスURL', `created_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '作成日時', PRIMARY KEY (`access_log_id`, `created_at`) ) COMMENT='アクセスログテーブル\'\r\n/*!50500 PARTITION BY RANGE COLUMNS(created_at)\r\n(PARTITION p201712 VALUES LESS THAN (\'2018-01-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201801 VALUES LESS THAN (\'2018-02-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201802 VALUES LESS THAN (\'2018-03-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201803 VALUES LESS THAN (\'2018-04-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201804 VALUES LESS THAN (\'2018-05-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201805 VALUES LESS THAN (\'2018-06-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201806 VALUES LESS THAN (\'2018-07-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201807 VALUES LESS THAN (\'2018-08-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201808 VALUES LESS THAN (\'2018-09-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201809 VALUES LESS THAN (\'2018-10-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201810 VALUES LESS THAN (\'2018-11-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201811 VALUES LESS THAN (\'2018-12-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201812 VALUES LESS THAN (\'2019-01-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201901 VALUES LESS THAN (\'2019-02-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201902 VALUES LESS THAN (\'2019-03-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201903 VALUES LESS THAN (\'2019-04-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201904 VALUES LESS THAN (\'2019-05-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201905 VALUES LESS THAN (\'2019-06-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201906 VALUES LESS THAN (\'2019-07-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201907 VALUES LESS THAN (\'2019-08-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201908 VALUES LESS THAN (\'2019-09-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201909 VALUES LESS THAN (\'2019-10-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201910 VALUES LESS THAN (\'2019-11-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201911 VALUES LESS THAN (\'2019-12-01 00:00:00\') ENGINE = InnoDB,\r\n PARTITION p201912 VALUES LESS THAN (\'2020-01-01 00:00:00' COLLATE='utf8_general_ci' /*!50100 PARTITION BY RANGE COLUMNS(created_at) (PARTITION p201712 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB, PARTITION p201801 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB, PARTITION p201802 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB, PARTITION p201803 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB, PARTITION p201804 VALUES LESS THAN ('2018-05-01 00:00:00') ENGINE = InnoDB, PARTITION p201805 VALUES LESS THAN ('2018-06-01 00:00:00') ENGINE = InnoDB, PARTITION p201806 VALUES LESS THAN ('2018-07-01 00:00:00') ENGINE = InnoDB, PARTITION p201807 VALUES LESS THAN ('2018-08-01 00:00:00') ENGINE = InnoDB, PARTITION p201808 VALUES LESS THAN ('2018-09-01 00:00:00') ENGINE = InnoDB, PARTITION p201809 VALUES LESS THAN ('2018-10-01 00:00:00') ENGINE = InnoDB, PARTITION p201810 VALUES LESS THAN ('2018-11-01 00:00:00') ENGINE = InnoDB, PARTITION p201811 VALUES LESS THAN ('2018-12-01 00:00:00') ENGINE = InnoDB, PARTITION p201812 VALUES LESS THAN ('2019-01-01 00:00:00') ENGINE = InnoDB, PARTITION p201901 VALUES LESS THAN ('2019-02-01 00:00:00') ENGINE = InnoDB, PARTITION p201902 VALUES LESS THAN ('2019-03-01 00:00:00') ENGINE = InnoDB, PARTITION p201903 VALUES LESS THAN ('2019-04-01 00:00:00') ENGINE = InnoDB, PARTITION p201904 VALUES LESS THAN ('2019-05-01 00:00:00') ENGINE = InnoDB, PARTITION p201905 VALUES LESS THAN ('2019-06-01 00:00:00') ENGINE = InnoDB, PARTITION p201906 VALUES LESS THAN ('2019-07-01 00:00:00') ENGINE = InnoDB, PARTITION p201907 VALUES LESS THAN ('2019-08-01 00:00:00') ENGINE = InnoDB, PARTITION p201908 VALUES LESS THAN ('2019-09-01 00:00:00') ENGINE = InnoDB, PARTITION p201909 VALUES LESS THAN ('2019-10-01 00:00:00') ENGINE = InnoDB, PARTITION p201910 VALUES LESS THAN ('2019-11-01 00:00:00') ENGINE = InnoDB, PARTITION p201911 VALUES LESS THAN ('2019-12-01 00:00:00') ENGINE = InnoDB, PARTITION p201912 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
上記テーブル(ページ遷移毎のユーザーのアクセスの動向を記録するテーブル)に関して、
現在運用しているサービスに関して実装する予定でありますが、
何か懸念点ございますでしょうか?
(パーテーションに関しては一旦2年分)
(多い時で1時間に2000レコード近くのinsertが発生する見込み)
(テーブルのデータ量圧迫のため周期を決めて不要データ削除も検討)
あなたの回答
tips
プレビュー