Sphinx问题：构建数据库
我正在开发一项工作服务，具有径向搜索、全文搜索、进行全文搜索+禁用某些工作列表的能力（例如取消选中文本框并不再返回全职工作）等功能。
使用 Sphinx 的开发人员希望数据库信息全部存储为带有键的整数（因此在表“作业类型”下可能存储值，例如 1=“兼职”和 2=“全职”） ）...而其他开发人员希望将数据库保留为字符串（因此在“作业类型”表下显示“兼职”或“全职”。
是否有理由将数据库保留为整数？或者 应该好吗
？
沃克
I'm developing a job service that has features like radial search, full-text search, the ability to do full-text search + disable certain job listings (such as un-checking a textbox and no longer returning full-time jobs).
The developer who is working on Sphinx wants the database information to all be stored as intergers with a key (so under the table "Job Type" values might be stored such as 1="part-time" and 2="full-time")... whereas the other developers want to keep the database as strings (so under the table "Job Type" it says "part-time" or "full-time".
Is there a reason to keep the database as ints? Or should strings be fine?
Thanks!
Walker
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
选择密钥会对性能产生巨大影响。尽可能使用整数而不是字符串。这称为使用“代理键”，其中键提供了一种独特且快速的方式来查找数据，而不是独立的数据。
字符串比较是资源密集型的，可能比比较数字差几个数量级。
您可以使 UI 脱离代理键，但显示另一列（例如 job_type）。这样，当您访问数据库时，您可以将 int 传入，并避免查找表来查找具有匹配字符串的行。
当涉及到连接数据库中的表时，如果您使用整数或其他数字作为主键，它们的运行速度会快得多。
编辑：在您提到的具体情况下，如果您的字段只有两个选项，并且不太可能更改，您可能需要研究类似位字段的内容，并且可以将其命名为 IsFullTime。位或布尔字段包含 1 或 0，除此之外没有其他内容，并且通常与其他字段无关。
Choosing your key can have a dramatic performance impact. Whenever possible, use ints instead of strings. This is called using a "surrogate key", where the key presents a unique and quick way to find the data, rather than the data standing on it's own.
String comparisons are resource intensive, potentially orders of magnitude worse than comparing numbers.
You can drive your UI off off the surrogate key, but show another column (such as job_type). This way, when you hit the database you pass the int in, and avoid looking through to the table to find a row with a matching string.
When it comes to joining tables in the database, they will run much faster if you have int's or another number as your primary keys.
Edit: In the specific case you have mentioned, if you only have two options for what your field may be, and it's unlikely to change, you may want to look into something like a bit field, and you could name it IsFullTime. A bit or boolean field holds a 1 or a 0, and nothing else, and typically isn't related to another field.
如果你正在标准化你的结构（我希望你是）那么数字键将是最有效的。
if you are normalizing your structure (i hope you are) then numeric keys will be most efficient.
除了使用整数主键的常见原因之外，在 Sphinx 中使用整数也是必不可少的，因为结果成功的 Sphinx 搜索返回的集合是与匹配项关联的文档 ID 列表。然后使用这些 ID 从数据库中提取相关数据。 Sphinx 不直接从数据库返回行。
有关更多详细信息，请参阅 Sphinx 手册，特别是 3.5。对源数据的限制。
Aside from the usual reasons to use integer primary keys, the use of integers with Sphinx is essential, as the result set returned by a successful Sphinx search is a list of document IDs associated with the matched items. These IDs are then used to extract the relevant data from the database. Sphinx does not return rows from the database directly.
For more details, see the Sphinx manual, especially 3.5. Restrictions on the source data.