How to Implement Monthly Partitioning for MySQL Log Tables with Webman and ThinkORM

Overview

When log tables grow large, storing all records in a single table degrades query performance. Partitioning data by month reduces the size of each table, improves query speed, and simplifies archiving. This article shows how to implement monthly partitioning for a MySQL log table.

Concept

Each month a new table named with the month suffix is created. A base table name (e.g., resty_log) remains constant, and its structure is copied for the new monthly table.

Implementation

Table Design

Base log table definition:

CREATE TABLE `resty_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `method` varchar(12) NOT NULL COMMENT '请求方式',
  `path` varchar(128) NOT NULL COMMENT '请求路径',
  `message` varchar(255) NOT NULL COMMENT '消息',
  `ip` varchar(32) NOT NULL COMMENT 'ip',
  `params` text NOT NULL COMMENT '请求参数',
  `status` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态码',
  `create_time` int(11) unsigned NOT NULL COMMENT '时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

Core query to check whether a monthly table already exists:

SELECT COUNT(`TABLE_NAME`) AS tmp_resty_log
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA`='demo.webman.tinywan.com'
  AND `TABLE_NAME`='resty_log_202410';

Technology Stack

The solution uses the Webman high‑performance HTTP framework and ThinkORM for database operations.

Webman : A Workerman‑based HTTP service framework offering superior performance over traditional PHP‑FPM.

ThinkORM : A PHP PDO‑based ORM library, originally part of ThinkPHP, now standalone and requiring PHP 8.0+.

Code Implementation

Install Webman: composer create-project workerman/webman Install ThinkORM plugin: composer require -W webman/think-orm Create model RestyLogModel.php:

<?php
/**
 * @desc System log model
 */
declare(strict_types=1);

namespace app\model;

use think\db\exception\BindParamException;
use think\facade\Db;
use think\Model;

class RestyLogModel extends Model {
    /** Table name used as base */
    protected $table = 'resty_log';

    /** Get the name of the monthly partition table */
    public static function getTableName(): string {
        $time = time();
        $tableName = self::getTable() . '_';
        $year = date('Y', $time);
        $month = str_pad(date('m', $time), 2, '0', STR_PAD_LEFT);
        $tableName .= $year . $month;
        $sql = "select COUNT(`TABLE_NAME`) as tmp_resty_log from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='" . \config('thinkorm.connections.mysql.database') . "' and `TABLE_NAME`='" . $tableName . "'";
        $query = Db::query($sql);
        if (empty($query[0]['tmp_resty_log']) || $query[0]['tmp_resty_log'] == 0) {
            self::_createTable($tableName);
        }
        return $tableName;
    }

    /** Create a new monthly table */
    private static function _createTable(string $table): void {
        $sql = "
            CREATE TABLE `" . $table . "` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
              `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
              `method` varchar(12) NOT NULL COMMENT '请求方式',
              `path` varchar(128) NOT NULL COMMENT '请求路径',
              `message` varchar(255) NOT NULL COMMENT '消息',
              `ip` varchar(32) NOT NULL COMMENT 'ip',
              `params` text NOT NULL COMMENT '请求参数',
              `status` int(4) unsigned NOT NULL DEFAULT '0' COMMENT '状态码',
              `create_time` int(11) unsigned NOT NULL COMMENT '时间',
              PRIMARY KEY (`id`) USING BTREE,
              KEY `idx_user_id` (`user_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='日志表'";
        Db::execute($sql);
    }

    /** Record a log entry */
    public function recordLog() {
        $insertData = [
            'user_id' => time(),
            'method' => request()->method(),
            'path' => request()->uri(),
            'message' => '用户登录日志',
            'ip' => request()->getRealIp(),
            'params' => json_encode(request()->all(), JSON_UNESCAPED_UNICODE),
            'status' => response()->getStatusCode(),
            'create_time' => time()
        ];
        \think\facade\Db::table(RestyLogModel::getTableName())->insert($insertData);
    }
}

Start the Webman server:

/var/www/demo.webman.tinywan.com # php start.php start

Send a request to generate a log entry, e.g.: http://127.0.0.1:8217/index/index?name=Tinywan Resulting screenshots (illustrating successful log insertion) are shown below.