我应该索引什么？用户名或用户 ID？
我有一个相当简单的表（请原谅错误/愚蠢，我仍在学习。为 MySQL 编写）：
CREATE TABLE IF NOT EXISTS `userdata` (
`userid` UNSIGNED int(18446744073709551615) AUTO_INCREMENT,
`username` char(255) NOT NULL,
`password` char(255) NOT NULL,
`salt` char(255) NOT NULL,
`email` char(255) NOT NULL,
PRIMARY KEY(`userid`)
);
我读到添加索引可以提高查询的性能，因为它不会需要查看整个数据库。相反，它会查看索引并匹配数据（如果我错了，请纠正我）。
我已经找到了如何足够好地创建索引，但还没有找到我应该索引的内容。
我应该在用户名上建立索引吗？电子邮件地址、用户 ID 或某些我尚未添加的字段？
I have a fairly simple table (forgive errors / stupidity, I'm still learning. Written for MySQL):
CREATE TABLE IF NOT EXISTS `userdata` (
`userid` UNSIGNED int(18446744073709551615) AUTO_INCREMENT,
`username` char(255) NOT NULL,
`password` char(255) NOT NULL,
`salt` char(255) NOT NULL,
`email` char(255) NOT NULL,
PRIMARY KEY(`userid`)
);
I've read that adding an index improves the performance of a query, as it doesn't need to look through the entire database. Instead, it will look through the index and match data (correct me if I'm wrong).
I've found out how to create an index well enough, but not what I should be indexing.
Should I have my index on usernames? Email addresses, user ID, or some field I've yet to add?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您应该仅根据
WHERE中列的实际使用情况创建索引：
如果您从未有
WHERE username='xyz'，则不需要索引添加索引
如果您有很多这样的索引，请在 JOIN 中 用法：
如果您从未有任何
JOIN xxxx ON x.userid=u.userid，则不需要索引如果有很多，那么添加一个索引
you should ONLY create an index based on you actual usage of the column
usage in a WHERE:
if you never have
WHERE username='xyz', the no index is neededif you have many of these, then add an index
usage in a JOIN:
if you never have any
JOIN xxxx ON x.userid=u.userid, then no index is neededif you have many of these, then add an index
您应该在几乎所有要进行键控查找的列上建立索引。在您的查询之一中是否会执行
where userid = ?操作？然后对userid建立索引。您要查找用户名吗？然后对用户名建立索引。密码怎么样？可能不会，所以别打扰。You should have an index on pretty much any column that you're doing keyed lookups on. Is something going to do a
where userid = ?in one of your queries? Then index onuserid. Are you going to be doing lookups onusername? Then index on username. What about onpassword? Probably not, so don't bother.您将在哪些领域进行搜索？这些应该是您索引的第一个候选者。还要对将成为其他表中的外键的任何列建立索引（
userid似乎是一个可能的候选者）。What fields will you be searching on? Those should be your first candidates for indexes. Also index any columns that will become foreign keys in other tables (
useridseems like a likely candidate).主要要考虑的是该字段是否经常用于通过记录搜索和过滤。如果是，则对其进行索引。其他答案很好地解决了这个问题。
但是，请记住，索引会增加一些开销来插入和更新操作。如果某个字段不断更新，但很少在搜索子句中使用，您可能会失望地发现索引实际上减慢了您的速度，而不是提高了性能。
The main thing to consider is whether this field will often be used for searching and filtering through records. If yes, index it. Other answers addressed that well.
However, keep in mind that indexes add some overhead to insert and update operations. If a field is updated constantly, but rarely used in search clauses, you may be disappointed to find out that the index is actually slowing you down instead of improving performance.
主要 ID 应转到用户 ID。
从现在和长远来看，这都非常有帮助。
Primay ID shoud go to the userid.
It's very helpful in now and in the long run.