고객별 사용량 추적 & 분석

-- usage_logs 테이블 생성 CREATE TABLE usage_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, endpoint VARCHAR(255) NOT NULL, method ENUM('GET', 'POST', 'PUT', 'DELETE') NOT NULL, status_code SMALLINT NOT NULL, response_time INT UNSIGNED NOT NULL COMMENT '밀리초', request_size INT UNSIGNED DEFAULT 0, response_size INT UNSIGNED DEFAULT 0, ip_address VARCHAR(45), user_agent VARCHAR(500), api_key_id INT UNSIGNED, called_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_customer_date (customer_id, called_at), INDEX idx_endpoint (endpoint, called_at), INDEX idx_status (status_code, called_at), FOREIGN KEY (customer_id) REFERENCES customers(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PARTITION BY RANGE (TO_DAYS(called_at)) ( PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')), PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')), PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')), PARTITION p_future VALUES LESS THAN MAXVALUE ); -- 일별 집계 테이블 (빠른 조회용) CREATE TABLE usage_daily_summary ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, log_date DATE NOT NULL, total_calls INT UNSIGNED DEFAULT 0, error_calls INT UNSIGNED DEFAULT 0, avg_response DECIMAL(8,2) DEFAULT 0, total_request_bytes BIGINT UNSIGNED DEFAULT 0, total_response_bytes BIGINT UNSIGNED DEFAULT 0, UNIQUE INDEX idx_customer_date (customer_id, log_date) );

// middleware/UsageLogger.php class UsageLogger { private $pdo; private $startTime; public function __construct($pdo) { $this->pdo = $pdo; } /** * 요청 시작 시 호출 - 타이밍 시작 */ public function before($request) { $this->startTime = microtime(true); } /** * 응답 후 호출 - 로그 기록 */ public function after($request, $response) { $responseTime = (microtime(true) - $this->startTime) * 1000; $stmt = $this->pdo->prepare(" INSERT INTO usage_logs (customer_id, endpoint, method, status_code, response_time, request_size, response_size, ip_address, user_agent, api_key_id, called_at) VALUES (:customer_id, :endpoint, :method, :status_code, :response_time, :request_size, :response_size, :ip_address, :user_agent, :api_key_id, NOW()) "); $stmt->execute([ 'customer_id' => $request->getCustomerId(), 'endpoint' => $request->getPath(), 'method' => $request->getMethod(), 'status_code' => $response->getStatusCode(), 'response_time' => (int)$responseTime, 'request_size' => strlen($request->getBody()), 'response_size' => strlen($response->getBody()), 'ip_address' => $request->getClientIp(), 'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '', 'api_key_id' => $request->getApiKeyId(), ]); } } // 미들웨어 적용 예시 $logger = new UsageLogger($pdo); $logger->before($request); // ... 비즈니스 로직 처리 ... $logger->after($request, $response);