You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
303 lines
9.7 KiB
303 lines
9.7 KiB
#!/usr/bin/env node
|
|
|
|
/**
|
|
* 数据库性能基准测试脚本
|
|
* 用于评估数据库优化效果
|
|
*/
|
|
|
|
import db from '../src/db/index.js'
|
|
import { UserModel } from '../src/db/models/UserModel.js'
|
|
import { ArticleModel } from '../src/db/models/ArticleModel.js'
|
|
import { BookmarkModel } from '../src/db/models/BookmarkModel.js'
|
|
import { bulkCreate } from '../src/db/transaction.js'
|
|
import { getQueryStats, resetStats, getSlowQueries } from '../src/db/monitor.js'
|
|
import { DbQueryCache } from '../src/db/index.js'
|
|
|
|
// 测试配置
|
|
const TEST_CONFIG = {
|
|
userCount: 1000,
|
|
articleCount: 500,
|
|
bookmarkCount: 2000,
|
|
iterations: 5,
|
|
cacheEnabled: true
|
|
}
|
|
|
|
async function setupTestData() {
|
|
console.log('准备测试数据...')
|
|
|
|
// 清空现有数据
|
|
await db('bookmarks').del()
|
|
await db('articles').del()
|
|
await db('users').del()
|
|
|
|
// 创建测试用户
|
|
const usersData = []
|
|
for (let i = 1; i <= TEST_CONFIG.userCount; i++) {
|
|
usersData.push({
|
|
username: `user_${i}`,
|
|
email: `user${i}@example.com`,
|
|
password: `password_${i}`,
|
|
name: `User ${i}`,
|
|
role: i % 10 === 0 ? 'admin' : 'user'
|
|
})
|
|
}
|
|
|
|
console.log(`创建 ${usersData.length} 个测试用户...`)
|
|
await bulkCreate('users', usersData, { batchSize: 100 })
|
|
|
|
// 创建测试文章
|
|
const articlesData = []
|
|
for (let i = 1; i <= TEST_CONFIG.articleCount; i++) {
|
|
articlesData.push({
|
|
title: `Article ${i}`,
|
|
content: `This is the content of article ${i}. It contains some sample text for testing purposes.`,
|
|
author: `user_${(i % TEST_CONFIG.userCount) + 1}`,
|
|
category: `category_${i % 10}`,
|
|
status: i % 5 === 0 ? 'draft' : 'published',
|
|
view_count: Math.floor(Math.random() * 1000)
|
|
})
|
|
}
|
|
|
|
console.log(`创建 ${articlesData.length} 篇测试文章...`)
|
|
await bulkCreate('articles', articlesData, { batchSize: 100 })
|
|
|
|
// 创建测试书签
|
|
const bookmarksData = []
|
|
for (let i = 1; i <= TEST_CONFIG.bookmarkCount; i++) {
|
|
bookmarksData.push({
|
|
user_id: (i % TEST_CONFIG.userCount) + 1,
|
|
title: `Bookmark ${i}`,
|
|
url: `https://example.com/bookmark/${i}`,
|
|
description: `Description for bookmark ${i}`
|
|
})
|
|
}
|
|
|
|
console.log(`创建 ${bookmarksData.length} 个测试书签...`)
|
|
await bulkCreate('bookmarks', bookmarksData, { batchSize: 100 })
|
|
|
|
console.log('测试数据准备完成!\n')
|
|
}
|
|
|
|
async function runPerformanceTests() {
|
|
console.log('开始性能基准测试...\n')
|
|
|
|
// 重置统计
|
|
resetStats()
|
|
DbQueryCache.clear()
|
|
|
|
const results = {
|
|
singleQueries: [],
|
|
batchQueries: [],
|
|
cacheTests: [],
|
|
transactionTests: []
|
|
}
|
|
|
|
// 运行多次测试取平均值
|
|
for (let i = 0; i < TEST_CONFIG.iterations; i++) {
|
|
console.log(`运行第 ${i + 1} 轮测试...`)
|
|
|
|
// 1. 单记录查询测试
|
|
const singleQueryTime = await testSingleRecordQueries()
|
|
results.singleQueries.push(singleQueryTime)
|
|
|
|
// 2. 批量查询测试
|
|
const batchQueryTime = await testBatchQueries()
|
|
results.batchQueries.push(batchQueryTime)
|
|
|
|
// 3. 缓存测试
|
|
const cacheTestTime = await testCachePerformance()
|
|
results.cacheTests.push(cacheTestTime)
|
|
|
|
// 4. 事务测试
|
|
const transactionTime = await testTransactionPerformance()
|
|
results.transactionTests.push(transactionTime)
|
|
|
|
console.log(`第 ${i + 1} 轮测试完成\n`)
|
|
}
|
|
|
|
// 计算平均值并显示结果
|
|
displayResults(results)
|
|
}
|
|
|
|
async function testSingleRecordQueries() {
|
|
const startTime = Date.now()
|
|
|
|
// 测试单用户查询
|
|
for (let i = 1; i <= 100; i++) {
|
|
const userId = (i % TEST_CONFIG.userCount) + 1
|
|
await UserModel.findById(userId)
|
|
}
|
|
|
|
// 测试单文章查询
|
|
for (let i = 1; i <= 100; i++) {
|
|
const articleId = (i % TEST_CONFIG.articleCount) + 1
|
|
await ArticleModel.findById(articleId)
|
|
}
|
|
|
|
// 测试单书签查询
|
|
for (let i = 1; i <= 100; i++) {
|
|
const bookmarkId = (i % TEST_CONFIG.bookmarkCount) + 1
|
|
await BookmarkModel.findById(bookmarkId)
|
|
}
|
|
|
|
return Date.now() - startTime
|
|
}
|
|
|
|
async function testBatchQueries() {
|
|
const startTime = Date.now()
|
|
|
|
// 测试用户列表查询
|
|
await UserModel.findAll({ page: 1, limit: 50 })
|
|
await UserModel.findAll({ page: 2, limit: 50 })
|
|
await UserModel.findAll({ page: 3, limit: 50 })
|
|
|
|
// 测试文章列表查询
|
|
await ArticleModel.findAll({ page: 1, limit: 50 })
|
|
await ArticleModel.findPublished(0, 50)
|
|
await ArticleModel.findDrafts()
|
|
|
|
// 测试书签列表查询
|
|
await BookmarkModel.findAllByUser(1)
|
|
await BookmarkModel.findAllByUser(2)
|
|
await BookmarkModel.findAllByUser(3)
|
|
|
|
return Date.now() - startTime
|
|
}
|
|
|
|
async function testCachePerformance() {
|
|
const startTime = Date.now()
|
|
|
|
if (TEST_CONFIG.cacheEnabled) {
|
|
// 第一次查询（无缓存）
|
|
for (let i = 1; i <= 50; i++) {
|
|
await db('users').where('id', i).cache(10000) // 10秒缓存
|
|
}
|
|
|
|
// 第二次查询（有缓存）
|
|
for (let i = 1; i <= 50; i++) {
|
|
await db('users').where('id', i).cache(10000) // 10秒缓存
|
|
}
|
|
}
|
|
|
|
return Date.now() - startTime
|
|
}
|
|
|
|
async function testTransactionPerformance() {
|
|
const startTime = Date.now()
|
|
|
|
// 测试批量创建性能
|
|
const testData = []
|
|
for (let i = 0; i < 50; i++) {
|
|
testData.push({
|
|
username: `tx_user_${Date.now()}_${i}`,
|
|
email: `tx_user_${Date.now()}_${i}@example.com`,
|
|
password: 'password123'
|
|
})
|
|
}
|
|
|
|
await bulkCreate('users', testData, { batchSize: 25 })
|
|
|
|
return Date.now() - startTime
|
|
}
|
|
|
|
function displayResults(results) {
|
|
console.log('==================== 性能测试结果 ====================')
|
|
console.log(`测试配置: ${TEST_CONFIG.userCount} 用户, ${TEST_CONFIG.articleCount} 文章, ${TEST_CONFIG.bookmarkCount} 书签`)
|
|
console.log(`测试轮数: ${TEST_CONFIG.iterations}\n`)
|
|
|
|
// 计算平均值
|
|
const avgSingleQuery = results.singleQueries.reduce((a, b) => a + b, 0) / results.singleQueries.length
|
|
const avgBatchQuery = results.batchQueries.reduce((a, b) => a + b, 0) / results.batchQueries.length
|
|
const avgCache = results.cacheTests.reduce((a, b) => a + b, 0) / results.cacheTests.length
|
|
const avgTransaction = results.transactionTests.reduce((a, b) => a + b, 0) / results.transactionTests.length
|
|
|
|
console.log('性能指标:')
|
|
console.log(`- 单记录查询平均时间: ${avgSingleQuery.toFixed(2)}ms`)
|
|
console.log(`- 批量查询平均时间: ${avgBatchQuery.toFixed(2)}ms`)
|
|
console.log(`- 缓存查询平均时间: ${avgCache.toFixed(2)}ms`)
|
|
console.log(`- 事务处理平均时间: ${avgTransaction.toFixed(2)}ms\n`)
|
|
|
|
// 显示查询统计
|
|
const queryStats = getQueryStats()
|
|
console.log('查询统计:')
|
|
console.log(`- 总查询数: ${queryStats.totalQueries}`)
|
|
console.log(`- 慢查询数: ${queryStats.slowQueries}`)
|
|
console.log(`- 慢查询率: ${queryStats.slowQueryRate}%`)
|
|
console.log(`- 错误数: ${queryStats.errors}`)
|
|
console.log(`- 错误率: ${queryStats.errorRate}%\n`)
|
|
|
|
// 显示缓存统计
|
|
const cacheStats = DbQueryCache.stats()
|
|
console.log('缓存统计:')
|
|
console.log(`- 缓存项总数: ${cacheStats.size}`)
|
|
console.log(`- 有效缓存项: ${cacheStats.valid}`)
|
|
console.log(`- 过期缓存项: ${cacheStats.expired}`)
|
|
console.log(`- 缓存命中率: ${cacheStats.hitRate ? (cacheStats.hitRate * 100).toFixed(2) : 'N/A'}%`)
|
|
console.log(`- 内存使用: ${cacheStats.totalSize ? (cacheStats.totalSize / 1024).toFixed(2) : 0}KB\n`)
|
|
|
|
// 显示慢查询
|
|
const slowQueries = getSlowQueries(5)
|
|
if (slowQueries.length > 0) {
|
|
console.log('慢查询 (前5个):')
|
|
slowQueries.forEach((query, index) => {
|
|
console.log(` ${index + 1}. ${query.duration}ms - ${query.sql.substring(0, 100)}...`)
|
|
})
|
|
console.log('')
|
|
}
|
|
|
|
// 性能评估
|
|
console.log('性能评估:')
|
|
if (avgSingleQuery < 50) {
|
|
console.log('✓ 单记录查询性能优秀')
|
|
} else if (avgSingleQuery < 100) {
|
|
console.log('○ 单记录查询性能良好')
|
|
} else {
|
|
console.log('⚠ 单记录查询性能需要优化')
|
|
}
|
|
|
|
if (avgBatchQuery < 200) {
|
|
console.log('✓ 批量查询性能优秀')
|
|
} else if (avgBatchQuery < 500) {
|
|
console.log('○ 批量查询性能良好')
|
|
} else {
|
|
console.log('⚠ 批量查询性能需要优化')
|
|
}
|
|
|
|
if (queryStats.slowQueryRate < 1) {
|
|
console.log('✓ 慢查询率控制良好')
|
|
} else {
|
|
console.log('⚠ 慢查询率较高，需要优化')
|
|
}
|
|
|
|
console.log('\n🎉 性能基准测试完成!')
|
|
}
|
|
|
|
async function main() {
|
|
try {
|
|
console.log('数据库性能基准测试\n')
|
|
|
|
// 准备测试数据
|
|
await setupTestData()
|
|
|
|
// 运行性能测试
|
|
await runPerformanceTests()
|
|
|
|
// 清理测试数据
|
|
console.log('\n清理测试数据...')
|
|
await db('bookmarks').del()
|
|
await db('articles').del()
|
|
await db('users').del()
|
|
console.log('测试数据清理完成!')
|
|
|
|
} catch (error) {
|
|
console.error('性能测试失败:', error)
|
|
process.exit(1)
|
|
}
|
|
}
|
|
|
|
// 如果直接运行此脚本，则执行测试
|
|
if (import.meta.url === `file://${process.argv[1]}`) {
|
|
main()
|
|
}
|
|
|
|
export default main
|