MySQL:很多表还是很多数据库? [英] MySQL: Many tables or many databases?

问题描述

对于一个项目，我们有一堆始终具有相同结构且未链接在一起的数据. 有两种保存数据的方法:

For a project we having a bunch of data that always have the same structure and is not linked together. There are two approaches to save the data:

• 为每个池(约15-25个表)创建一个新数据库
• 在一个数据库中创建所有表，并根据表名称更改池.

哪一个更容易，更快地处理MySQL?

Which one is easier and faster to handle for MySQL?

编辑:我对数据库设计没有兴趣，只是对这两种可能性中的哪一种更快感到兴趣.

I am not interessed in issues of database design, I am just interessed in which of the two possibilities is faster.

我将尝试使其更加清晰.如前所述，我们将获得数据，其中一些日期很少会属于不同的池.将一种类型的所有数据放在一个表中并与一个池ID链接不是一个好主意:

EDIT 2: I will try to make it more clear. As said we will have data, where some of the date rarely belongs together in different pools. Putting all the data of one type in one table and linking it with a pool id is not a good idea:

• 很难备份/删除特定的池(并且我们希望过一会儿(即使使用big int时)用尽主键)

因此，想法是为每个池创建一个数据库或在一个数据库中创建很多表.针对数据库的50％查询将是简单的inserts. 49％是主键上的一些简单selects.

So the idea is to make a database for every pool or create a lot of tables in one database. 50% of the queries against the database will be simple inserts. 49% will be some simple selects on a primary key.

问题是，MySQL的处理速度更快?很多表还是很多数据库?

The question is, what is faster to handle for MySQL? Many tables or many databases?

推荐答案

单个数据库中的多个表与单独数据库中的多个表之间应该没有明显的性能差异.

There should be no significant performance difference between multiple tables in a single database versus multiple tables in separate databases.

在MySQL中，数据库(标准SQL为此使用术语模式")主要用作表的命名空间.数据库只有几个属性，例如默认字符集和排序规则.使用GRANT可以方便地控制每个数据库的访问权限，但这与性能无关.

In MySQL, databases (standard SQL uses the term "schema" for this) serve chiefly as a namespace for tables. A database has only a few attributes, e.g. the default character set and collation. And that usage of GRANT makes it convenient to control access privileges per database, but that has nothing to do with performance.

您可以从单个连接访问任何数据库中的表(前提是它们由MySQL Server的同一实例管理).您只需要限定表名:

You can access tables in any database from a single connection (provided they are managed by the same instance of MySQL Server). You just have to qualify the table name:

SELECT * FROM database17.accounts_table;

这纯粹是语法上的差异.它应该对性能没有影响.

This is purely a syntactical difference. It should have no effect on performance.

关于存储，您不能像@Chris推测的那样将表组织到每个数据库文件中.使用MyISAM存储引擎，每个表始终有一个文件.使用InnoDB存储引擎，您可以拥有一组合并所有表的存储文件，或者每个表都有一个文件(此文件是为整个MySQL服务器配置的，而不是为每个数据库配置的).无论哪种情况，在单个数据库与多个数据库中创建表都没有性能优势或劣势.

Regarding storage, you can't organize tables into a file-per-database as @Chris speculates. With the MyISAM storage engine, you always have a file per table. With the InnoDB storage engine, you either have a single set of storage files that amalgamate all tables, or else you have a file per table (this is configured for the whole MySQL server, not per database). In either case, there's no performance advantage or disadvantage to creating the tables in a single database versus many databases.

每个数据库没有很多MySQL配置参数.影响服务器性能的大多数参数在整个服务器范围内.

There aren't many MySQL configuration parameters that work per database. Most parameters that affect server performance are server-wide in scope.

关于备份，您可以指定表的子集作为mysqldump命令的参数.备份每个数据库的逻辑表集可能更方便，而不必在命令行上命名所有表.但这对性能没有影响，在输入备份命令时只会给您带来方便.

Regarding backups, you can specify a subset of tables as arguments to the mysqldump command. It may be more convenient to back up logical sets of tables per database, without having to name all the tables on the command-line. But it should make no difference to performance, only convenience for you as you enter the backup command.

这篇关于MySQL:很多表还是很多数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！