MySQL外键和性能影响
我目前正在从头开始设计一个新的数据库，试图预见到可能会随着规模增加而可能出现的任何问题，
我正在制作登录系统目前
我拥有表：（ innodb，dynamiC）
- < strong>用户 （有关新用户创建的更新，仅存储ID，电子邮件，哈希，胡椒）
- user_profile （在用户修改配置文件信息上更新这是 ecom，因此不规则）
- user_addresses （在用户添加地址的更新
- ）
- user_attempts （密码失败的更新尝试）
- user_devices （登录更新）
，< strong>所有表都有“用户”表的ID的外键 - 它们可能会或可能没有自己的ID，具体取决于是否需要。 “ user_profile”还具有“用户”表的“电子邮件”的外
键流量，监视点击等。
我的设置是否最佳地进行？我可以提出改善绩效的任何修订建议吗？即我甚至需要外键吗？援助将不胜感激
。
预见您的问题：
为什么
我希望在登录时能获得性能提高...也许不是？老实说，并不熟悉外国钥匙，它们似乎一直在阻碍他们的帮助，但是我真的想从一开始就做“正确”的事情，
100k用户，因为如果达到这么大的规模，我很确定我可以雇用一个开发人员大声笑
编辑：谢谢-1没有任何评论，很高兴您比我更好地了解mysql的方式
I am designing a new database from scratch at the moment, trying to foresee any issues that may arise as scale increases
I'm making the login system at the moment
I have tables: (InnoDB, Dynamic)
- user (Updates on new user creation, only stores id, email, hash, pepper)
- user_profile (Updates on user modifying profile info - it is
ecom, so irregular) - user_addresses (Updates on user adding an address)
- user_reset (Updates on password recovery attempt)
- user_attempts (Updates on failed password attempt)
- user_devices (Updates on login)
At present, all tables have a foreign key of ID from "user" table - they may or may not have their own ID too depending if it is needed. "user_profile" also has foreign key of "email" from "user" table
I also plan to add marketing tables which foreign key from "user" table "id" in the future which will do anything from tracking traffic, to monitoring clicks, etc.
Will the setup I have perform optimally? Any suggestions of revisions I could make to improve performance? i.e. Do I even need foreign keys? Assistance would be greatly appreciated
.
Anticipating Your Questions:
Why split user and user_profile?
I'm hoping for performance gain on login... Maybe not? Not that familiar with foreign keys in all honesty, they have always seemed to hinder more than they help, but I really want to do things "right" from the beginning
Define "scale"
100k users on a website, since if it ever got to that size I'm pretty sure I could hire a team of developers lol
Edit: Thanks for -1 without any comment, glad you understand MySQL way better than I do
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
6个更新 - 没什么大不了的。应该能够处理每秒数百个用户。
如果这些表中的任何一个是“ 1：1”，请考虑将它们结合起来。 （如果它们是“ 1：许多”，则单独的表是合适的。）
索引对于性能很重要。
外键是两件事：索引和一致性检查。许多教科书都推动使用FKS；我通常只对等效索引感到满意。6 updates -- not a big deal. Should be able to handle hundreds of users per second.
If any of those tables are "1:1", then consider combining them. (If they are "1:many", then separate tables is appropriate.)
Indexes are important for performance. A
FOREIGN KEYis two things: an index, and a consistency check. Many textbooks push for using FKs; I am usually happy with just the equivalent index.