2b1c7bfb88
- 순찰/점검 기능 개선 (zone-detail 페이지 추가) - 출근/근태 시스템 개선 (연차 조회, 근무현황) - 작업분석 대분류 그룹화 및 마이그레이션 스크립트 - 모바일 네비게이션 UI 추가 - NAS 배포 도구 및 문서 추가 Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
434 lines
12 KiB
JavaScript
434 lines
12 KiB
JavaScript
// utils/queryOptimizer.js - 데이터베이스 쿼리 최적화 유틸리티
|
|
|
|
const { getDb } = require('../dbPool');
|
|
|
|
/**
|
|
* SQL Injection 방지를 위한 화이트리스트 검증
|
|
*/
|
|
const ALLOWED_ORDER_DIRECTIONS = ['ASC', 'DESC'];
|
|
const ALLOWED_TABLE_NAME_PATTERN = /^[a-zA-Z_][a-zA-Z0-9_]*$/;
|
|
const ALLOWED_COLUMN_NAME_PATTERN = /^[a-zA-Z_][a-zA-Z0-9_.]*$/;
|
|
|
|
const validateOrderDirection = (direction) => {
|
|
const normalized = (direction || 'DESC').toUpperCase();
|
|
if (!ALLOWED_ORDER_DIRECTIONS.includes(normalized)) {
|
|
throw new Error(`Invalid order direction: ${direction}`);
|
|
}
|
|
return normalized;
|
|
};
|
|
|
|
const validateIdentifier = (identifier, type = 'column') => {
|
|
if (!identifier || typeof identifier !== 'string') {
|
|
throw new Error(`Invalid ${type} name`);
|
|
}
|
|
if (!ALLOWED_COLUMN_NAME_PATTERN.test(identifier)) {
|
|
throw new Error(`Invalid ${type} name: ${identifier}`);
|
|
}
|
|
return identifier;
|
|
};
|
|
|
|
const validateTableName = (tableName) => {
|
|
if (!tableName || typeof tableName !== 'string') {
|
|
throw new Error('Invalid table name');
|
|
}
|
|
if (!ALLOWED_TABLE_NAME_PATTERN.test(tableName)) {
|
|
throw new Error(`Invalid table name: ${tableName}`);
|
|
}
|
|
return tableName;
|
|
};
|
|
|
|
/**
|
|
* 페이지네이션 헬퍼
|
|
*/
|
|
const paginate = (page = 1, limit = 10) => {
|
|
const pageNum = Math.max(1, parseInt(page));
|
|
const limitNum = Math.min(100, Math.max(1, parseInt(limit))); // 최대 100개 제한
|
|
const offset = (pageNum - 1) * limitNum;
|
|
|
|
return {
|
|
limit: limitNum,
|
|
offset,
|
|
page: pageNum
|
|
};
|
|
};
|
|
|
|
/**
|
|
* 페이지네이션된 쿼리 실행
|
|
*/
|
|
const executePagedQuery = async (baseQuery, countQuery, params = [], options = {}) => {
|
|
const { page = 1, limit = 10, orderBy = 'id', orderDirection = 'DESC' } = options;
|
|
const { limit: limitNum, offset, page: pageNum } = paginate(page, limit);
|
|
|
|
// SQL Injection 방지: 컬럼명과 정렬방향 검증
|
|
const safeOrderBy = validateIdentifier(orderBy, 'column');
|
|
const safeOrderDirection = validateOrderDirection(orderDirection);
|
|
|
|
try {
|
|
const db = await getDb();
|
|
|
|
// 전체 개수 조회
|
|
const [countResult] = await db.execute(countQuery, params);
|
|
const totalCount = countResult[0]?.total || 0;
|
|
|
|
// 데이터 조회 (ORDER BY와 LIMIT 추가) - 검증된 값만 사용
|
|
const pagedQuery = `${baseQuery} ORDER BY ${safeOrderBy} ${safeOrderDirection} LIMIT ${limitNum} OFFSET ${offset}`;
|
|
const [rows] = await db.execute(pagedQuery, params);
|
|
|
|
// 페이지네이션 메타데이터 계산
|
|
const totalPages = Math.ceil(totalCount / limitNum);
|
|
|
|
return {
|
|
data: rows,
|
|
pagination: {
|
|
currentPage: pageNum,
|
|
totalPages,
|
|
totalCount,
|
|
limit: limitNum,
|
|
hasNextPage: pageNum < totalPages,
|
|
hasPrevPage: pageNum > 1
|
|
}
|
|
};
|
|
|
|
} catch (error) {
|
|
throw new Error(`페이지네이션 쿼리 실행 오류: ${error.message}`);
|
|
}
|
|
};
|
|
|
|
/**
|
|
* 인덱스 최적화 제안
|
|
*/
|
|
const suggestIndexes = async (tableName) => {
|
|
// SQL Injection 방지: 테이블명 검증
|
|
const safeTableName = validateTableName(tableName);
|
|
|
|
try {
|
|
const db = await getDb();
|
|
|
|
// 현재 인덱스 조회 - 검증된 테이블명 사용
|
|
const [indexes] = await db.execute(`SHOW INDEX FROM \`${safeTableName}\``);
|
|
|
|
// 테이블 구조 조회 - 검증된 테이블명 사용
|
|
const [columns] = await db.execute(`DESCRIBE \`${safeTableName}\``);
|
|
|
|
const suggestions = [];
|
|
|
|
// 외래키 컬럼에 인덱스 제안
|
|
const foreignKeyColumns = columns.filter(col =>
|
|
col.Field.endsWith('_id') && !indexes.some(idx => idx.Column_name === col.Field)
|
|
);
|
|
|
|
foreignKeyColumns.forEach(col => {
|
|
suggestions.push({
|
|
type: 'INDEX',
|
|
column: col.Field,
|
|
reason: '외래키 컬럼에 인덱스 추가로 JOIN 성능 향상',
|
|
sql: `CREATE INDEX idx_${safeTableName}_${col.Field} ON \`${safeTableName}\`(\`${col.Field}\`);`
|
|
});
|
|
});
|
|
|
|
// 날짜 컬럼에 인덱스 제안
|
|
const dateColumns = columns.filter(col =>
|
|
(col.Type.includes('date') || col.Type.includes('timestamp')) &&
|
|
!indexes.some(idx => idx.Column_name === col.Field)
|
|
);
|
|
|
|
dateColumns.forEach(col => {
|
|
suggestions.push({
|
|
type: 'INDEX',
|
|
column: col.Field,
|
|
reason: '날짜 범위 검색 성능 향상',
|
|
sql: `CREATE INDEX idx_${safeTableName}_${col.Field} ON \`${safeTableName}\`(\`${col.Field}\`);`
|
|
});
|
|
});
|
|
|
|
return {
|
|
tableName: safeTableName,
|
|
currentIndexes: indexes.map(idx => ({
|
|
name: idx.Key_name,
|
|
column: idx.Column_name,
|
|
unique: idx.Non_unique === 0
|
|
})),
|
|
suggestions
|
|
};
|
|
|
|
} catch (error) {
|
|
throw new Error(`인덱스 분석 오류: ${error.message}`);
|
|
}
|
|
};
|
|
|
|
/**
|
|
* 쿼리 성능 분석
|
|
*/
|
|
const analyzeQuery = async (query, params = []) => {
|
|
try {
|
|
const db = await getDb();
|
|
|
|
// EXPLAIN 실행
|
|
const explainQuery = `EXPLAIN ${query}`;
|
|
const [explainResult] = await db.execute(explainQuery, params);
|
|
|
|
// 쿼리 실행 시간 측정
|
|
const startTime = Date.now();
|
|
await db.execute(query, params);
|
|
const executionTime = Date.now() - startTime;
|
|
|
|
// 성능 분석
|
|
const analysis = {
|
|
executionTime,
|
|
explainResult,
|
|
recommendations: []
|
|
};
|
|
|
|
// 성능 권장사항 생성
|
|
explainResult.forEach(row => {
|
|
if (row.type === 'ALL') {
|
|
analysis.recommendations.push({
|
|
type: 'WARNING',
|
|
message: `테이블 전체 스캔 발생: ${row.table}`,
|
|
suggestion: '적절한 인덱스 추가 권장'
|
|
});
|
|
}
|
|
|
|
if (row.rows > 1000) {
|
|
analysis.recommendations.push({
|
|
type: 'WARNING',
|
|
message: `많은 행 검사: ${row.rows}행`,
|
|
suggestion: 'WHERE 조건 최적화 또는 인덱스 추가 권장'
|
|
});
|
|
}
|
|
|
|
if (row.Extra && row.Extra.includes('Using filesort')) {
|
|
analysis.recommendations.push({
|
|
type: 'INFO',
|
|
message: '파일 정렬 사용 중',
|
|
suggestion: 'ORDER BY 컬럼에 인덱스 추가 고려'
|
|
});
|
|
}
|
|
});
|
|
|
|
return analysis;
|
|
|
|
} catch (error) {
|
|
throw new Error(`쿼리 분석 오류: ${error.message}`);
|
|
}
|
|
};
|
|
|
|
/**
|
|
* 배치 삽입 최적화
|
|
*/
|
|
const batchInsert = async (tableName, data, batchSize = 100) => {
|
|
if (!Array.isArray(data) || data.length === 0) {
|
|
throw new Error('삽입할 데이터가 없습니다.');
|
|
}
|
|
|
|
// SQL Injection 방지: 테이블명 검증
|
|
const safeTableName = validateTableName(tableName);
|
|
|
|
try {
|
|
const db = await getDb();
|
|
const connection = await db.getConnection();
|
|
|
|
await connection.beginTransaction();
|
|
|
|
const columns = Object.keys(data[0]);
|
|
// 컬럼명도 검증
|
|
const safeColumns = columns.map(col => validateIdentifier(col, 'column'));
|
|
const placeholders = safeColumns.map(() => '?').join(', ');
|
|
const columnList = safeColumns.map(col => `\`${col}\``).join(', ');
|
|
const insertQuery = `INSERT INTO \`${safeTableName}\` (${columnList}) VALUES (${placeholders})`;
|
|
|
|
let insertedCount = 0;
|
|
|
|
// 배치 단위로 처리
|
|
for (let i = 0; i < data.length; i += batchSize) {
|
|
const batch = data.slice(i, i + batchSize);
|
|
|
|
for (const row of batch) {
|
|
const values = columns.map(col => row[col]);
|
|
await connection.execute(insertQuery, values);
|
|
insertedCount++;
|
|
}
|
|
}
|
|
|
|
await connection.commit();
|
|
connection.release();
|
|
|
|
return {
|
|
insertedCount,
|
|
batchSize,
|
|
totalBatches: Math.ceil(data.length / batchSize)
|
|
};
|
|
|
|
} catch (error) {
|
|
throw new Error(`배치 삽입 오류: ${error.message}`);
|
|
}
|
|
};
|
|
|
|
/**
|
|
* 쿼리 캐시 키 생성
|
|
*/
|
|
const generateCacheKey = (query, params = [], prefix = 'query') => {
|
|
const paramString = params.length > 0 ? JSON.stringify(params) : '';
|
|
const queryHash = require('crypto')
|
|
.createHash('md5')
|
|
.update(query + paramString)
|
|
.digest('hex');
|
|
|
|
return `${prefix}:${queryHash}`;
|
|
};
|
|
|
|
/**
|
|
* 자주 사용되는 최적화된 쿼리들
|
|
*/
|
|
const optimizedQueries = {
|
|
// 작업자 목록 (페이지네이션)
|
|
getWorkersPaged: async (page = 1, limit = 10, search = '', status = '', departmentId = null) => {
|
|
let baseQuery = `
|
|
SELECT w.*, d.department_name, COUNT(dwr.id) as report_count
|
|
FROM workers w
|
|
LEFT JOIN daily_work_reports dwr ON w.worker_id = dwr.worker_id
|
|
LEFT JOIN departments d ON w.department_id = d.department_id
|
|
`;
|
|
|
|
let countQuery = `
|
|
SELECT COUNT(*) as total FROM workers w
|
|
LEFT JOIN departments d ON w.department_id = d.department_id
|
|
`;
|
|
let params = [];
|
|
let conditions = [];
|
|
|
|
// 검색 조건
|
|
if (search) {
|
|
conditions.push('(w.worker_name LIKE ? OR w.position LIKE ?)');
|
|
params.push(`%${search}%`, `%${search}%`);
|
|
}
|
|
|
|
// 상태 조건
|
|
if (status) {
|
|
conditions.push('w.status = ?');
|
|
params.push(status);
|
|
}
|
|
|
|
// 부서 조건
|
|
if (departmentId) {
|
|
conditions.push('w.department_id = ?');
|
|
params.push(departmentId);
|
|
}
|
|
|
|
// 조건 조합
|
|
if (conditions.length > 0) {
|
|
const whereClause = ' WHERE ' + conditions.join(' AND ');
|
|
baseQuery += whereClause;
|
|
countQuery += whereClause;
|
|
}
|
|
|
|
baseQuery += ' GROUP BY w.worker_id';
|
|
|
|
return executePagedQuery(baseQuery, countQuery, params, {
|
|
page, limit, orderBy: 'w.worker_id', orderDirection: 'DESC'
|
|
});
|
|
},
|
|
|
|
// 프로젝트 목록 (페이지네이션)
|
|
getProjectsPaged: async (page = 1, limit = 10, status = '') => {
|
|
let baseQuery = `
|
|
SELECT p.*, COUNT(dwr.id) as report_count,
|
|
SUM(dwr.work_hours) as total_hours
|
|
FROM projects p
|
|
LEFT JOIN daily_work_reports dwr ON p.project_id = dwr.project_id
|
|
`;
|
|
|
|
let countQuery = 'SELECT COUNT(*) as total FROM projects p';
|
|
let params = [];
|
|
|
|
if (status) {
|
|
const statusCondition = ' WHERE p.status = ?';
|
|
baseQuery += statusCondition + ' GROUP BY p.project_id';
|
|
countQuery += statusCondition;
|
|
params = [status];
|
|
} else {
|
|
baseQuery += ' GROUP BY p.project_id';
|
|
}
|
|
|
|
return executePagedQuery(baseQuery, countQuery, params, {
|
|
page, limit, orderBy: 'p.project_id', orderDirection: 'DESC'
|
|
});
|
|
},
|
|
|
|
// 일일 작업 보고서 (날짜 범위, 페이지네이션)
|
|
getDailyWorkReportsPaged: async (startDate, endDate, page = 1, limit = 10) => {
|
|
const baseQuery = `
|
|
SELECT dwr.*, w.worker_name, p.project_name,
|
|
wt.name as work_type_name, wst.name as work_status_name,
|
|
et.name as error_type_name, u.name as created_by_name
|
|
FROM daily_work_reports dwr
|
|
LEFT JOIN workers w ON dwr.worker_id = w.worker_id
|
|
LEFT JOIN projects p ON dwr.project_id = p.project_id
|
|
LEFT JOIN work_types wt ON dwr.work_type_id = wt.id
|
|
LEFT JOIN work_status_types wst ON dwr.work_status_id = wst.id
|
|
LEFT JOIN error_types et ON dwr.error_type_id = et.id
|
|
LEFT JOIN users u ON dwr.created_by = u.user_id
|
|
WHERE dwr.report_date BETWEEN ? AND ?
|
|
`;
|
|
|
|
const countQuery = `
|
|
SELECT COUNT(*) as total
|
|
FROM daily_work_reports dwr
|
|
WHERE dwr.report_date BETWEEN ? AND ?
|
|
`;
|
|
|
|
return executePagedQuery(baseQuery, countQuery, [startDate, endDate], {
|
|
page, limit, orderBy: 'dwr.report_date', orderDirection: 'DESC'
|
|
});
|
|
}
|
|
};
|
|
|
|
/**
|
|
* 데이터베이스 성능 모니터링
|
|
*/
|
|
const getPerformanceStats = async () => {
|
|
try {
|
|
const db = await getDb();
|
|
|
|
// 연결 상태 조회
|
|
const [connections] = await db.execute('SHOW STATUS LIKE "Threads_connected"');
|
|
const [maxConnections] = await db.execute('SHOW VARIABLES LIKE "max_connections"');
|
|
|
|
// 쿼리 캐시 상태 (MySQL 8.0 이전 버전)
|
|
let queryCacheStats = null;
|
|
try {
|
|
const [qcStats] = await db.execute('SHOW STATUS LIKE "Qcache%"');
|
|
queryCacheStats = qcStats;
|
|
} catch (error) {
|
|
// MySQL 8.0+에서는 쿼리 캐시가 제거됨
|
|
}
|
|
|
|
// 슬로우 쿼리 로그 상태
|
|
const [slowQueries] = await db.execute('SHOW STATUS LIKE "Slow_queries"');
|
|
|
|
return {
|
|
connections: {
|
|
current: parseInt(connections[0]?.Value || 0),
|
|
max: parseInt(maxConnections[0]?.Value || 0)
|
|
},
|
|
queryCacheStats,
|
|
slowQueries: parseInt(slowQueries[0]?.Value || 0),
|
|
timestamp: new Date().toISOString()
|
|
};
|
|
|
|
} catch (error) {
|
|
throw new Error(`성능 통계 조회 오류: ${error.message}`);
|
|
}
|
|
};
|
|
|
|
module.exports = {
|
|
paginate,
|
|
executePagedQuery,
|
|
suggestIndexes,
|
|
analyzeQuery,
|
|
batchInsert,
|
|
generateCacheKey,
|
|
optimizedQueries,
|
|
getPerformanceStats
|
|
};
|