Laravel 5.5 优化查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47879431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel 5.5 optimize query
提问by Hoàng ??ng
I have question about reducing database connection in for every time retrieve data from DB. I assume I have 5 tables (Notifications, Orders, Users, Products, Transactions)
我对每次从数据库检索数据时减少数据库连接有疑问。我假设我有 5 个表（通知、订单、用户、产品、交易）
In dashboard, I have to show:
在仪表板中，我必须显示：
- Unread notifications
- Order statistics
- Number of products
- Transactions statistics
- 未读通知
- 订单统计
- 产品数量
- 交易统计
For the easiest ways (pseudo-code):
对于最简单的方法（伪代码）：
$notis = Notification::where('unread')->get();
$orderStat = Order::join('user')->where('count(order.id)', 'status')->groupby('status');
$product = Product::count();
$transactions = Transaction::join('user')->where('count(trans.id)', 'status')->groupby('status');
So I have to run 4 separate queries, as my mentor said that this solution would reduce the speech of server in case there are many many recordsin each tables or the dashboard needs more tables (not for joining)to query.
I already did:
所以我必须运行 4 个单独的查询，正如我的导师所说，如果每个表中有很多记录，或者仪表板需要更多表（不是用于加入）来查询，这个解决方案会减少服务器的发言。
我已经做了：
- Index on foreign key columns
- Eager-loading for eloquent if it's available
- (OR using ajax to load data after UI rendered)
- 外键列索引
- 如果可以的话，急切地加载雄辩
- （或在 UI 呈现后使用 ajax 加载数据）
I want to ask: Are there any else method to reduce processing time for the above case?.
And other question about connection pool, he said use it to increase speed. After researching a found Laravel already did connection pool, isn't it?
Edit:
User have many notification, orders, transaction.
I just want to ask which method to improve the performance that I not mention above.
请问：上述情况有没有其他方法可以减少处理时间？.
和其他关于 的问题connection pool，他说用它来提高速度。经过研究发现Laravel已经做了连接池，不是吗？
编辑：
用户有很多通知、订单、交易。
我只想问上面没有提到的提高性能的方法。
回答by marcus
Firstly correct your pseudo code which should be like:
首先更正你的伪代码，它应该是这样的：
$notis = Notification::where('unread')->get();
$orderStat = Order::join('user')->select('count(order.id)', 'status')->groupBy('status');
$product = Product::count();
$transactions = Transaction::join('user')->select('count(trans.id)', 'status')->groupBy('status');
I have a few additional tips which you can take into consideration:
我有一些额外的提示，您可以考虑：
1) Do not use a query cache (The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.)
1) 不要使用查询缓存（查询缓存从 MySQL 5.7.20 开始被弃用，在 MySQL 8.0 中被移除。）
2) use InnoDB as Storage Engine for your tables and SQL Server buffer pool to improve a capacity - https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
2) 使用 InnoDB 作为您的表和 SQL Server 缓冲池的存储引擎以提高容量 - https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
3) If it is possible do not get all records from query use LIMIT and OFFSET to narrow data (limit and skip method in Laravel or only paginate)
3）如果可能不从查询中获取所有记录，请使用 LIMIT 和 OFFSET 来缩小数据范围（Laravel 中的限制和跳过方法或仅分页）
4) If you do not need user data do not use INNER JOIN with users table (if each transaction and order always has user)
4）如果你不需要用户数据，不要使用带有用户表的INNER JOIN（如果每个交易和订单总是有用户）
5) test requests using abhttp://httpd.apache.org/docs/2.2/programs/ab.htmlto set your server correctly
5) 使用ab http://httpd.apache.org/docs/2.2/programs/ab.html测试请求以正确设置您的服务器
6) use php >= 7.0 with fpm and opcache it will increase the limit of requests
6) 使用 php >= 7.0 with fpm 和 opcache 它会增加请求的限制
7) store results of your queries with Cache API https://laravel.com/docs/5.5/cache(update cache if data is changed)
7) 使用缓存 API https://laravel.com/docs/5.5/cache存储查询结果（如果数据更改，则更新缓存）
8) Profile Your Queries using https://github.com/barryvdh/laravel-debugbar
回答by Zugor
回答by Rahul Hirve
You have to use Laravel Relationship go through below link for proper guidance
您必须使用 Laravel 关系通过以下链接获得适当的指导
For many to many relationship:
对于多对多关系：
https://laravel.com/docs/5.5/eloquent-relationships#many-to-many
https://laravel.com/docs/5.5/eloquent-relationships#many-to-many
According to your condition use appropriate one. Hope it will help you.
根据您的情况使用适当的一种。希望它会帮助你。
After setting relationship you can access table's data calling single object.
设置关系后，您可以调用单个对象访问表的数据。
