composer require barryvdh/laravel-debugbar --dev
APP_DEBUG TRUE
Configure .env file for local debugging. Avoid deploying to production as this tool consumes significant resources.
For a Laravel 5.7 project using ORM with occasional native queries, we focused on optimizing MySQL performance rather than implementing master-slave replication for message推送 services. Key findings from ApsaraDB diagnostics include:
Problematic Query Example:
SELECT * FROM sale_out_storage
WHERE shop_id = ? AND is_delete = ? AND storage_status = ?
AND NOT EXISTS (
SELECT * FROM k3_sale_out_storage_task
WHERE sale_out_storage.id = k3_sale_out_storage_task.sale_out_storage_id
AND is_delete = ? AND is_cancel = ? AND status IN (?) AND shop_id = ?
)
AND EXISTS (
SELECT * FROM sale_order
WHERE sale_out_storage.sale_order_id = sale_order.id AND order_status != ?
)
AND storage_date > ?
Key Optimization Recommendations:
- High row scan ratio (178673:1) indicates missing appropriate indexes
- Avoid excessive IN clauses with large value ranges
- Time range queries require proper indexing
- Excessive indexes on log tables increase CPU overhead
- Join operations consume significant memory even with small tables
- Add indexes strategically for Laravel with() relationships
Before/After Performance:
| SQL Statement | Database | Thread ID | User | Client IP | Operation | Status | Duration (ms) | Execution Time | Rows Affected | Rows Scanned |
|---|---|---|---|---|---|---|---|---|---|---|
select * from operation_log where admin_id = 14 and operation_admin_id != 14 and is_read = 1 and message_type in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19) and create_time >= '2019-06-01 00:00:00' order by id desc limit 10 offset 0 |
v2 | 60130 | youse | 172.18.112.8 | SELECT | Success | 0.25 | 2019-09-06 10:57:50 | 0 | 13 |
select count(*) as aggregate from operation_log where user_id = ? and is_read = ? |
v2 | 24 | youse | 172.18.112.8 | SELECT | Success | 0.04 | 2019-09-06 10:57:50 | 0 | 0 |
select * from sale_voucher where is_delete = 10 and shop_id = 1 and admin_id = 14 and status = 30 and create_time >= '2019-01-01' order by id desc limit 5 offset 0 |
v2 | 60130 | youse | 172.18.112.8 | SELECT | Success | 0.48 | 2019-09-06 10:57:50 | 0 | 289 |
Optimized Laravel Query Example:
$orderDetails = Order::where('merchant_id', $merchantId)
->where('is_deleted', 10)
->with([
'related_orders' => function ($query) {
$query->where('is_deleted', 10)
->with([
'inventory_records' => function ($query) {
$query->where('is_deleted', 10)
->where('status', 20)
->with([
'product_variants' => function ($query) {
$query->where('is_deleted', 10);
}
]);
},
'payment_info' => function ($query) {
$query->where('is_deleted', 10);
}
]);
},
'settlement_data' => function ($query) {
$query->where('is_deleted', 10)
->with([
'settlement_items' => function ($query) {
$query->where('is_deleted', 10);
}
]);
},
'payment_history' => function ($query) {
$query->where('is_deleted', 10)
->where('transaction_status', 20);
}
])
->where('order_id', $parentOrderId)
->first();
Performance improvement: 80-95% reduction in query execution time on RDS MySQL 5.7 (2CPU/4GB).
Core optimization strategy: Identify high-impact tables through business analysis, prioritize index optimization, and enforce code standards for query patterns.