连接来自不同数据库的表的性能影响 [英] performance effect of joining tables from different databases

问题描述

我有一个使用名为"site1"的数据库的网站.我计划将另一个站点放在同一服务器上，该服务器还将使用"site1"中的某些表.

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"(用于第一个站点的特定数据)，"site2"(用于第二个站点的特定数据)和"general"(用于公用表).其中，数据库general与site1和site2之间将存在join语句.还是应该将所有表都放在一个数据库中?

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?

哪个是最佳做法? 每种情况下的演奏有何不同? 我正在使用MySQL.那么对于MySQL来说情况如何呢?

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?

预先感谢...

推荐答案

我可以从最近的个人经历中发言.我在一些PHP代码中有一些旧的mysql查询，这些查询在相对较小的数据库中也可以正常工作，但是随着查询的增长，查询的速度越来越慢.

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.

我有freeradius在自己的数据库中运行mysql以及我编写的另一个管理php应用程序. freeradius表大于150万行.我试图将表从我的应用程序数据库连接到freeradius数据库.我可以肯定地说150万行太多了.运行一些查询完全锁定了我的应用程序.我最终不得不重写我的php应用程序的某些部分以做不同的事情(即，不加入来自不同数据库的2个表).我还在一些关键字段上为半径核算表建立了索引，并对一些查询进行了优化(mysql EXPLAIN语句可以很好地帮助实现这一点).现在事情快得多了.

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.

除非确实有必要，否则我将来会很犹豫地联接来自不同数据库的2个表.

I will definitely be hesitant to join 2 tables from different databases in the future unless really really necessary.

这篇关于连接来自不同数据库的表的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！