本文目录导读：
我来为您提供一个完整的ThinkPHP项目关联查询性能测试方案。
测试准备
创建测试数据表
-- 用户表 CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `email` varchar(100) NOT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB; -- 订单表 CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `order_no` varchar(50) NOT NULL, `amount` decimal(10,2) NOT NULL, `status` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), KEY `idx_order_no` (`order_no`) ) ENGINE=InnoDB; -- 订单详情表 CREATE TABLE `order_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NOT NULL, `product_name` varchar(100) NOT NULL, `price` decimal(10,2) NOT NULL, `quantity` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`) ) ENGINE=InnoDB;
生成测试数据
<?php
namespace app\common\command;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Db;
class GenerateTestData extends Command
{
protected function configure()
{
$this->setName('generate:test-data')
->setDescription('生成测试数据');
}
protected function execute(Input $input, Output $output)
{
// 生成10000条用户数据
$users = [];
for ($i = 0; $i < 10000; $i++) {
$users[] = [
'name' => 'user_' . $i,
'email' => 'user' . $i . '@example.com'
];
// 每1000条插入一次
if (count($users) >= 1000) {
Db::name('users')->insertAll($users);
$users = [];
}
}
// 生成50000条订单数据
$orders = [];
for ($i = 0; $i < 50000; $i++) {
$orders[] = [
'user_id' => rand(1, 10000),
'order_no' => 'ORDER' . str_pad($i, 8, '0', STR_PAD_LEFT),
'amount' => rand(100, 10000) / 10,
'status' => rand(1, 5),
'created_at' => date('Y-m-d H:i:s', strtotime('-1 month') + $i * 10)
];
if (count($orders) >= 1000) {
Db::name('orders')->insertAll($orders);
$orders = [];
}
}
// 生成订单详情数据
$items = [];
for ($i = 1; $i <= 50000; $i++) {
$itemCount = rand(1, 5);
for ($j = 0; $j < $itemCount; $j++) {
$items[] = [
'order_id' => $i,
'product_name' => 'product_' . rand(1, 100),
'price' => rand(10, 500) / 10,
'quantity' => rand(1, 10)
];
}
if (count($items) >= 1000) {
Db::name('order_items')->insertAll($items);
$items = [];
}
}
$output->writeln('测试数据生成完成');
}
}
性能测试脚本
基础关联查询测试
<?php
namespace app\api\controller;
use think\facade\Db;
use think\response\Json;
class PerformanceTest extends Base
{
/**
* 测试1：一对多关联查询（hasMany）
*/
public function testHasMany()
{
$startTime = microtime(true);
$startMemory = memory_get_usage();
// 方法1：使用关联查询
$users = Db::name('users')
->alias('u')
->leftJoin('orders o', 'o.user_id = u.id')
->field('u.id, u.name, COUNT(o.id) as order_count')
->group('u.id')
->limit(100)
->select();
$endTime = microtime(true);
$endMemory = memory_get_usage();
return json([
'code' => 1,
'data' => [
'method' => 'JOIN查询',
'time' => round(($endTime - $startTime) * 1000, 2) . 'ms',
'memory' => round(($endMemory - $startMemory) / 1024, 2) . 'KB',
'count' => count($users)
]
]);
}
/**
* 测试2：使用ThinkPHP关联模型
*/
public function testRelationModel()
{
$startTime = microtime(true);
$startMemory = memory_get_usage();
// 使用模型关联
$users = \app\model\User::with(['orders'])
->limit(100)
->select();
$endTime = microtime(true);
$endMemory = memory_get_usage();
return json([
'code' => 1,
'data' => [
'method' => '模型关联',
'time' => round(($endTime - $startTime) * 1000, 2) . 'ms',
'memory' => round(($endMemory - $startMemory) / 1024, 2) . 'KB',
'count' => count($users)
]
]);
}
/**
* 测试3：多级关联查询
*/
public function testMultiLevelRelation()
{
$startTime = microtime(true);
$startMemory = memory_get_usage();
// 查询用户及其订单和订单详情
$users = \app\model\User::with(['orders.items'])
->limit(50)
->select();
$endTime = microtime(true);
$endMemory = memory_get_usage();
return json([
'code' => 1,
'data' => [
'method' => '多级关联',
'time' => round(($endTime - $startTime) * 1000, 2) . 'ms',
'memory' => round(($endMemory - $startMemory) / 1024, 2) . 'KB',
'count' => count($users)
]
]);
}
/**
* 测试4：大数据量分页关联查询
*/
public function testLargeDataPagination()
{
$page = input('page', 1);
$limit = 20;
$startTime = microtime(true);
// JOIN查询带条件
$orders = Db::name('orders')
->alias('o')
->join('users u', 'o.user_id = u.id')
->join('order_items oi', 'oi.order_id = o.id')
->where('o.status', 1)
->field('o.id, o.order_no, o.amount, u.name, COUNT(oi.id) as item_count')
->group('o.id')
->page($page, $limit)
->select();
$endTime = microtime(true);
return json([
'code' => 1,
'data' => [
'method' => '分页JOIN',
'time' => round(($endTime - $startTime) * 1000, 2) . 'ms',
'page' => $page,
'count' => count($orders)
]
]);
}
}
对比测试脚本
<?php
namespace app\common\library;
use think\facade\Db;
use think\facade\Log;
class QueryPerformanceTest
{
// 测试方法集合
protected $tests = [];
public function __construct()
{
$this->tests = [
'simple_query' => '简单查询',
'join_query' => 'JOIN查询',
'relation_query' => '关联模型',
'sub_query' => '子查询',
'batch_query' => '批量查询'
];
}
/**
* 执行所有测试
*/
public function runAllTests($iterations = 100)
{
$results = [];
foreach ($this->tests as $method => $name) {
$results[$name] = $this->benchmark($method, $iterations);
}
return $results;
}
/**
* 基准测试
*/
protected function benchmark($method, $iterations)
{
$times = [];
$memories = [];
for ($i = 0; $i < $iterations; $i++) {
$startTime = microtime(true);
$startMem = memory_get_usage();
// 执行测试方法
call_user_func([$this, $method]);
$endTime = microtime(true);
$endMem = memory_get_usage();
$times[] = ($endTime - $startTime) * 1000;
$memories[] = ($endMem - $startMem) / 1024;
}
return [
'avg_time' => round(array_sum($times) / count($times), 2),
'max_time' => round(max($times), 2),
'min_time' => round(min($times), 2),
'avg_memory' => round(array_sum($memories) / count($memories), 2) . 'KB',
'total_time' => round(array_sum($times), 2)
];
}
/**
* 简单查询
*/
protected function simple_query()
{
return Db::name('users')->limit(100)->select();
}
/**
* JOIN查询
*/
protected function join_query()
{
return Db::name('users')
->alias('u')
->join('orders o', 'o.user_id = u.id')
->field('u.*, COUNT(o.id) as order_count')
->group('u.id')
->limit(100)
->select();
}
/**
* 关联模型查询
*/
protected function relation_query()
{
return \app\model\User::with(['orders'])
->limit(100)
->select();
}
/**
* 子查询
*/
protected function sub_query()
{
$subQuery = Db::name('orders')
->field('user_id, COUNT(*) as order_count')
->group('user_id');
return Db::name('users')
->alias('u')
->join(['' . $subQuery->buildSql() => 'o'], 'o.user_id = u.id')
->limit(100)
->select();
}
/**
* 批量查询
*/
protected function batch_query()
{
$userIds = Db::name('users')->limit(100)->column('id');
$orders = Db::name('orders')
->where('user_id', 'in', $userIds)
->select();
return $orders;
}
}
性能监控工具类
<?php
namespace app\common\library;
use think\facade\Db;
use think\facade\Log;
class QueryMonitor
{
protected $queries = [];
protected $startTime;
protected $startMemory;
/**
* 开始监控
*/
public function start()
{
$this->startTime = microtime(true);
$this->startMemory = memory_get_usage();
// 开启SQL日志
Db::listen(function ($sql, $time, $explain) {
$this->queries[] = [
'sql' => $sql,
'time' => $time,
'explain' => $explain
];
});
}
/**
* 结束监控并返回结果
*/
public function stop()
{
$endTime = microtime(true);
$endMemory = memory_get_usage();
$result = [
'total_time' => round(($endTime - $this->startTime) * 1000, 2) . 'ms',
'memory_usage' => round(($endMemory - $this->startMemory) / 1024, 2) . 'KB',
'query_count' => count($this->queries),
'queries' => $this->queries
];
$this->queries = [];
return $result;
}
/**
* 分析慢查询
*/
public function analyzeSlowQueries($threshold = 100)
{
$slowQueries = array_filter($this->queries, function ($query) use ($threshold) {
return $query['time'] > $threshold;
});
if (!empty($slowQueries)) {
Log::warning('慢查询检测', [
'count' => count($slowQueries),
'queries' => $slowQueries
]);
}
return $slowQueries;
}
}
性能优化实践
查询优化策略
<?php
namespace app\common\library;
class QueryOptimization
{
/**
* 使用索引优化
*/
public function indexOptimization()
{
// 确保索引使用
Db::name('orders')
->where('user_id', 100)
->where('status', 1)
->field('id, order_no, amount')
->select();
// 复合索引
// ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
}
/**
* 分页优化 - 延迟关联
*/
public function paginationOptimization($page = 1, $limit = 20)
{
// 先查询ID，再关联数据
$ids = Db::name('orders')
->where('status', 1)
->order('id', 'desc')
->limit(($page - 1) * $limit, $limit)
->column('id');
if (empty($ids)) {
return [];
}
$data = Db::name('orders')
->alias('o')
->join('users u', 'o.user_id = u.id')
->where('o.id', 'in', $ids)
->field('o.*, u.name')
->select();
return $data;
}
/**
* 缓存优化
*/
public function cacheOptimization($userId)
{
$cacheKey = 'user_orders_' . $userId;
// 尝试获取缓存
$orders = cache($cacheKey);
if (empty($orders)) {
// 从数据库查询
$orders = Db::name('orders')
->where('user_id', $userId)
->select();
// 存入缓存，有效期5分钟
cache($cacheKey, $orders, 300);
}
return $orders;
}
}
测试执行和报告
<?php
namespace app\api\controller;
use app\common\library\QueryPerformanceTest;
use app\common\library\QueryMonitor;
class TestController
{
/**
* 运行性能测试
*/
public function runTest()
{
// 创建测试实例
$tester = new QueryPerformanceTest();
// 运行所有测试
$results = $tester->runAllTests(50);
// 格式化为报告
$report = $this->formatReport($results);
return json([
'code' => 1,
'data' => $report
]);
}
/**
* 格式化报告
*/
protected function formatReport($results)
{
$html = '<table border="1">';
$html .= '<tr><th>查询方法</th><th>平均耗时</th><th>最大耗时</th><th>最小耗时</th><th>内存使用</th></tr>';
foreach ($results as $name => $data) {
$html .= "<tr>";
$html .= "<td>{$name}</td>";
$html .= "<td>{$data['avg_time']}ms</td>";
$html .= "<td>{$data['max_time']}ms</td>";
$html .= "<td>{$data['min_time']}ms</td>";
$html .= "<td>{$data['avg_memory']}</td>";
$html .= "</tr>";
}
$html .= '</table>';
return $html;
}
}
测试建议
测试场景覆盖
- 不同的数据量级（1万、10万、100万条数据）
- 不同的关联深度（1级、2级、3级关联）
- 不同条件的查询（带where、排序、分组）
- 并发查询测试
监控指标
- 响应时间
- 内存使用
- SQL执行次数
- 索引使用情况
优化建议
根据需要选择不同的查询方式：
- 简单查询：使用
Db类 - 关联查询：使用JOIN或者模型关联
- 大数据量：使用分页和延迟关联
- 频繁查询：使用缓存
注意事项
- 避免N+1查询问题
- 合理使用预加载（
with） - 建立合适的索引
- 避免在大数据量下使用
select * - 使用
findInSet或JSON_CONTAINS时注意性能
这个测试方案可以帮助您全面评估ThinkPHP项目的关联查询性能,并指导您进行相应优化。
