MYSQL，使用唯一的表名VS使用ids [英] MYSQL, using unique table names VS using ids

问题描述

我目前在MYSQL数据库中有26个表。我的老板要我重新创建这26个表，每当我们有一个新的客户端，并附加一些客户端缩写到这些新的表。所以例如有公司1〜系统〜用户和公司2〜系统〜用户等等。

我宁愿在数据库中添加一个表来跟踪我们的客户端，并自动增加11位INT主键，并在其他26个表中引用它相反，如果有200个客户端，那么我们不用4000个表格来混乱数据库。

我认为他的恐惧是，如果我去做我想做的方法，因此执行查询需要更长时间的MYSQL，因为每个2000到5000个记录的客户端将共享表。因此，例如，从名为系统〜用户的表中搜索属于company1的用户，具有1,500,000条记录将比从2,000个记录的名为company1〜system〜users的表搜索用户慢。我认为MYSQL搜索如果每个客户端有26套表（这是每个客户端26个），实际上会更慢。

哪种方法实际上较慢？

解决方案

数据库或每个公司？然后在构建查询时，甚至不需要构建动态表名。这是一个更加健全的解决方案。更重要的是，它会使客户端数据更加分离，所以任何相互依赖关系可能会更加明显。

当应用程序层也是分开的时候，上述效果最好可以为每个实例提供一组不同的数据库登录凭据。

如果不是这样，根据您的安装，它可能会正常工作或尴尬或不正确平台你正在使用等等。

追加公司名称是一个黑客，但可以让我工作，我猜。

在记录中拥有客户端ID也是常见的方法。只要表格适当索引，我不一定担心从性能的角度来看，有150万条记录。这不是很大的记录。此外，公司ID标准也应该相当好地限制结果。

I currently have 26 tables in a MYSQL database. My boss wants me to recreate those 26 tables whenever we have a new client, and append a client abbreviation of some sort to those new tables. So for example there's company1~system~users and company2~system~users and so on and so forth.

I'd rather just add a table to the database that keeps track of our clients, with an auto incrementing 11 digit INT primary key and reference it in the other 26 tables instead, so we're not cluttering up the database with 4000 tables if have 200 clients.

I think his fear is that if we go the method I'd rather do, it will take MYSQL noticeably longer to perform queries because clients with anywhere between 2000 and 5000 records each will be sharing tables. So for example, searching for users that belong to company1 from a table called system~users with 1,500,000 records would be slower than searching for users from a table called company1~system~users with 2,000 records. I think it would actually be slower for MYSQL to search if each client we have has 26 sets of tables ( that's 26 per client ).

Which method is actually slower?

解决方案

Why not just create a database or each company? And then you don't even need to construct dynamic table names when constructing your queries. It's a far more sound solution. What's more, it'll make client data more separated so any inter-dependency will probably be more obvious.

The above works best when the application layers are also separate so you can provide each instance with a different set of database login credentials.

If that isn't the case it might work fine or be awkward or fine depending on your installation, what platform you're using and so on.

Appending a company name is a hack but it can be made to work I guess.

Having a client ID in records is also a common approach. I wouldn't necessarily worry about 1.5 million records from a performance point of view as long as the tables are appropriate indexed. This isn't a huge amount of records. Plus the company ID criteria should limit results fairly well anyway.

这篇关于MYSQL，使用唯一的表名VS使用ids的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！