I have a web site using a database named lets say "site1". I am planning to put another site on the same server which will also use some of the tables from "site1".
我有一个使用名为“site1”的数据库的网站。我计划在同一服务器上放置另一个站点，该服务器也将使用“site1”中的一些表。
So should I use three different databases like "site1" (for first site specific data), "site2" (for second site specific data), and "general" (for common tables). In which there will be join statements between databases general and site1 and site2. Or should I put all tables in one database?
因此，我应该使用三个不同的数据库，如“site1”(用于第一个站点的特定数据)、“site2”(用于第二个站点的特定数据)和“general”(用于公共表)。其中将会有数据库一般和site1和site2之间的连接语句。还是应该将所有表放在一个数据库中?
Which is the best practice to do? How performances differ in each situation? I am using MySQL. So how is the situation especially for MySQL?
最好的做法是什么?每一种情况下的表现有何不同?我使用MySQL。那么MySQL的情况如何呢?
Thanks in advance...
提前谢谢…
2 个解决方案
#1
3
I can speak from recent personal experience. I have some old mysql queries in some PHP code that worked fine with a relatively small database, but as it grew the query got slower and slower.
我能从最近的个人经历中得出结论。我在一些PHP代码中使用了一些旧的mysql查询，这些查询在相对较小的数据库中运行良好，但是随着它的增长，查询变得越来越慢。
I have freeradius running mysql in its own database along with another management php app that I wrote. The freeradius table is > 1.5 million rows. I was attempting to join tables from my app's database to the freeradius database. I can say for sure 1.5 million rows is too many. Running some queries locked up my app altogether. I ended up having to re-write portions of my php app to do things differently (ie not joining 2 tables from different database). I also indexed the radius accounting table on some key fields and optimized some queries (mysql EXPLAIN statement is wonderful to help with this). Things are MUCH faster now.
我有freeradius在自己的数据库中运行mysql以及我编写的另一个管理php应用程序。freeradius表是> 150万行。我试图从我的应用程序的数据库连接到freeradius数据库。我可以肯定的说150万行太多了。运行一些查询将我的应用程序全部锁定。最后，我不得不重新编写php应用程序的部分来做一些不同的事情(即不从不同的数据库连接两个表)。我还在一些关键字段上索引了radius会计表，并优化了一些查询(mysql EXPLAIN语句很好地帮助了这一点)。现在一切都快多了。
I will definitely be hesitant to join 2 tables from different databases in the future unless really really necessary.
除非真的有必要，否则我将来一定会犹豫是否要加入来自不同数据库的两个表。
#2
10
From the performance point of view, there won't be ANY difference. Just keep your indexes in place and you will not notice whether you are using single DB or multiple DBs.
从性能的角度来看，没有什么不同。只要将索引放在适当的位置，您就不会注意到您是在使用单个DB还是多个DBs。
Apart from performance, there are 2 small implications that I can think of: 1. You can not have foreign keys across DBs. 2. Partitioning tables in DB based on their usage or based on applications can help you manage permissions in easy way.
除了性能，我还能想到两个小的含义:1。在DBs中不能有外键。2。基于数据库中的表的使用或基于应用程序的分区可以帮助您轻松地管理权限。
#1
3
I can speak from recent personal experience. I have some old mysql queries in some PHP code that worked fine with a relatively small database, but as it grew the query got slower and slower.
我能从最近的个人经历中得出结论。我在一些PHP代码中使用了一些旧的mysql查询，这些查询在相对较小的数据库中运行良好，但是随着它的增长，查询变得越来越慢。
I have freeradius running mysql in its own database along with another management php app that I wrote. The freeradius table is > 1.5 million rows. I was attempting to join tables from my app's database to the freeradius database. I can say for sure 1.5 million rows is too many. Running some queries locked up my app altogether. I ended up having to re-write portions of my php app to do things differently (ie not joining 2 tables from different database). I also indexed the radius accounting table on some key fields and optimized some queries (mysql EXPLAIN statement is wonderful to help with this). Things are MUCH faster now.
我有freeradius在自己的数据库中运行mysql以及我编写的另一个管理php应用程序。freeradius表是> 150万行。我试图从我的应用程序的数据库连接到freeradius数据库。我可以肯定的说150万行太多了。运行一些查询将我的应用程序全部锁定。最后，我不得不重新编写php应用程序的部分来做一些不同的事情(即不从不同的数据库连接两个表)。我还在一些关键字段上索引了radius会计表，并优化了一些查询(mysql EXPLAIN语句很好地帮助了这一点)。现在一切都快多了。
I will definitely be hesitant to join 2 tables from different databases in the future unless really really necessary.
除非真的有必要，否则我将来一定会犹豫是否要加入来自不同数据库的两个表。
#2
10
From the performance point of view, there won't be ANY difference. Just keep your indexes in place and you will not notice whether you are using single DB or multiple DBs.
从性能的角度来看，没有什么不同。只要将索引放在适当的位置，您就不会注意到您是在使用单个DB还是多个DBs。
Apart from performance, there are 2 small implications that I can think of: 1. You can not have foreign keys across DBs. 2. Partitioning tables in DB based on their usage or based on applications can help you manage permissions in easy way.
除了性能，我还能想到两个小的含义:1。在DBs中不能有外键。2。基于数据库中的表的使用或基于应用程序的分区可以帮助您轻松地管理权限。