问题描述
除了 users 表，Wordpress 还有一个包含以下列的 usersmeta 表
Alongside the users table, Wordpress has a usersmeta table with the following columns
- meta_id
- user_id
- meta_key(例如名字)
- meta_value(例如 Tom)
每个用户在 usersmeta 表中都有 20 行，无论这些行是否填充了 meta_value.也就是说，将始终存在的元行添加到用户表中不是更有效吗?
Each user has 20 rows in the usersmeta table, regardless of whether or not the rows have a filled-in meta_value. That said, would it not be more efficient to add the always-present meta rows to the users table?
我猜测 users 表中的信息被更频繁地查询(例如 user_id、username、pass)，因此将这些行保持更小更有效.这是真的?这种表格分离还有其他原因吗?
I'm guessing that the information in the users table is more frequently queried (e.g. user_id, username, pass), so it is more efficient to keep those rows smaller. Is this true? And are there other reasons for this separation of tables?
推荐答案
Entity Attribute Value
它被称为实体属性值 (EAV) 数据模型，并允许将任意数量的属性分配给给定的实体.这意味着每个用户有任意数量的元数据条目.
Entity Attribute Value
It's known as the Entity Attribute Value (EAV) data model, and allows an arbitrary number of attributes to be assigned to a given entity. That means any number of meta-data entries per user.
默认情况下，wordpress 设置了几个键(问题中说明了 20 个)，但可以有任何数字.如果所有用户都有一千个元数据条目 - 每个用户的 usermeta 表中只有一千个条目 - 它没有(就数据库结构而言)对用户可以拥有的元数据条目数量的限制.它还允许一个用户拥有一千个元数据整体，而所有其他用户拥有 20 个并且仍然有效地存储数据 - 或任何排列.
By default there are a few keys that wordpress sets (20 stated in the question) but there can be any number. If all users have one thousand meta data entries - there are simply one thousand entries in the usermeta table for each user - it doesn't have (in terms of the database structure) a limit to the number of meta data entries a user can have. It also permits one user to have one thousand meta data entires, whilst all others have 20 and still store the data efficiently - or any permutation thereof.
除了灵活性之外，使用这种结构还允许主用户表保持较小 - 这意味着更高效的查询.
In addition to flexibility, using this kind of structure permits the main users table to remain small - which means more efficient queries.
使用 EAV 的替代方法包括:
The alternatives to using EAV include:
- 每当属性数量发生变化时修改架构
- 将所有属性存储在一个序列化的字符串中(在用户对象上)
- 使用无模式数据库
权限是第一点的最大问题，授予全面访问权限以更改数据库表的架构并不是一个好主意，并且对于许多(即使不是大多数)wordpress 安装(托管在 wordpress 上)来说也是一个(理智的)障碍.com 或在 db 用户没有更改权限的共享主机上).Mysql 也有 4096 列和 65,535 的硬限制每行字节.尝试在单个表中存储大量列最终会失败，同时创建一个查询效率低下的表.
Permissions is the biggest problem with the first point, it is not a good idea to grant blanket access to alter the schema of your database tables, and is a (sane) roadblock for many if not most wordpress installs (hosted on wordpress.com or on a shared host where the db user has no alter permissions). Mysql also has a hard-limit of 4096 columns and 65,535 bytes per row. Attempting to store a large number of columns in a single table will eventually fail, along the way creating a table that is inefficient to query.
将所有属性存储在序列化字符串中会使按元数据值查询变得困难和缓慢.
Storing all attribute in a serialized string would make it difficult and slow to query by a meta-data value.
Wordpress 与 mysql 紧密相关，因此更改数据存储不是一个现实的选择.
Wordpress is quite tied to mysql, and therefore changing datastore isn't a realistic option.
如果你没有使用任何/许多插件，你可能在 usermeta 表中为每个用户有固定数量的行，但通常你添加的每个插件可能需要为用户添加元数据；添加的数字可能不是微不足道的，这些数据存储在 usermeta 表中.
If you aren't using any/many plugins it's possible you will have a constant number of rows in the usermeta table for each user, but typically each plugin you add may need to add meta-data for users; the number added may not be trivial and this data is stored in the usermeta table.
add_meta_user 的文档可能会更清楚地说明为什么数据库是这样构建的.如果你把这样的代码放在某处:
The docs for add_meta_user may add some clarity as to why the database is structured that way. If you put code like this somewhere:
add_user_meta($user_id, "favorite_color", "blue");
它将在 usermeta 表中为给定的 user_id 创建一行，而无需在主用户表中添加列 (favorite_color).这使得通过最喜欢的颜色查找用户变得容易，而无需修改用户表的架构.
It will create a row in the usermeta table for the given user_id, without the need to add a column (favorite_color) to the main users table. That makes it easy-ish to find users by favorite color without the need to modify the schema of the users table.
这篇关于为什么 Wordpress 有单独的“usersmeta"和“users"SQL 表.为什么不把它们结合起来?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！