数据库优化建议

发布于 2024-10-19 04:31:43 字数 1711 浏览 0 评论 0原文

我有一个名为members 的表。我正在寻求如何改进它的建议。

id ：这是用户 ID（唯一）（自动递增）（索引）
status ：可以包含“已激活”、“暂停”、“验证”、“删除”
admin ：仅包含 0 或 1（如果人员是管理员或管理员）不是）
暂停的注释：如果会员帐户被暂停，我可以添加注释，这样当他们尝试登录时，他们就会看到该注释。
failed_login_count ：基本上是从 0 到 4 的 1 位数字，对失败的登录进行计数。
last_visited ：上次访问站点时的 unix 时间戳；（注销时更新）（我通过 php with time() 执行此操作）
username ：可以包含 3 到 15 个字符（唯一且有索引）
first_name ：只能包含字母，长度为 3 到 40 个字符
Last_name ：只能包含字母长度为 2 到 50 个字符
电子邮件：可以包含电子邮件地址（我使用 php 电子邮件过滤器来检查是否有效）
密码：长度可以包含 6 到 10 个字符，并且经过哈希处理，并且在数据库中包含一次 40 个字符的固定长度散列
date_time ：unix 时间戳（我通过 php 的 time() 执行此操作）。当用户登录时
ip ：注册/登录时的成员 ip
激活密钥：我使用 md5 和盐来创建唯一的激活密钥；长度始终为 32 个字符
性别：空白或男/女，仅此而已。
websiteurl：可以添加他们的网站url；
msn : 可以包含 msn 电子邮件地址 (使用正则表达式匹配)
goal : 目标昵称 (使用正则表达式匹配)
yim : yim 昵称 (使用正则表达式匹配)
twitter : twitter 用户名 (使用正则表达式匹配) )

挂起_注意；名;姓;日期时间； ip；性别;网站网址；微信；目的; yim; twitter 可以为空，因为注册时只需要用户名、电子邮件和密码，因此除了注册/登录时获取的 ip 之外，这些字段在填写之前将为空（它们基本上是可选的，不是必需的）。

谁能根据我提供的信息告诉我如何更有效地改进和更改此表？我想说我可以改进它，因为我倾向于使用 varchar 来做大多数事情，并且希望从中获得最佳性能。

我倾向于进行大量选择并将用户数据存储在会话中，以避免每次都查询数据库。用户名是唯一的，并且像 id 一样进行索引，因为我的大多数选择都将用户名与我的查询中的 LIMIT 1 进行比较。

更新：

我想问我是否更改为枚举，例如我将如何在 php 中对枚举进行选择和比较查询？我确实在网上查找过，但找不到任何使用枚举的示例查询。另外，如果我将 date_time 更改为时间戳，我是否仍然使用 php 中的 time() 将 unix 时间戳插入 date_time 列数据库？

我问的原因是我正在网上阅读一篇教程，其中提到当查询、选择、更新行等时，MySQL 会自动更新该行的时间戳；这是真的吗，因为我宁愿在时间戳字段中使用 php time() 插入时间戳。我已经使用 php time() 作为 date_time 但目前使用 varchar 而不是时间戳。

另外，服务器时间在美国，在 php.ini 中我将其设置为英国时间，但我想 mysql 会将其存储在服务器上的时间中，这又不好，因为我希望它们采用英国时间。

原文

I have a table called members. I am looking on advice how to improve it.

id : This is user id (unique) (auto increment) (indexed)
status : Can contain 'activated', 'suspended', 'verify', 'delete'
admin : This just contains either 0 or 1 (if person is admin or not)
suspended_note : If a members account is suspended i can add a note so when they try and login they will see the note.
failed_login_count : basically 1 digit from 0 to 4, counts failed logins
last_visited : unix timestamp of when they last visited site; (updated on logout) (i do this via php with time() )
username : can contain from 3 to 15 characters (unique and indexed)
first_name : can contain letters only and from 3 to 40 chars in length
last_name : can contain letters only and from 2 to 50 chars in length
email : can contain an email address (i use php email filter to check if valid)
password : can contain from 6 to 10 chars in length and is hashed and contains fixed length of 40 chars in database once hashed
date_time : unix timestamp (i do this via php with time() ). When user logs in
ip : members ip on registration/logins
activationkey : i use md5 and a salt to create a unique activation key; length is always 32 chars
gender : either blank or male/female and nothing else.
websiteurl: can add they site url;
msn : can contain msn email address (use regular expression to match this)
aim : aim nickname (use regular expression to match this)
yim : yim nickname (use regular expression to match this)
twitter : twitter username (use regular expression to match this)

suspended_note; first_name; last_name; date_time; ip; gender; websiteurl; msn; aim; yim; twitter can be null because on registration only username, email and password is required so those fields will be null until filled in (they are basically optional and not required) apart from ip which is taken on signup/login.

Could anyone tell me based on the information I have given how I can improve and alter this table more efficently? I would say I could improve it as I tend to use varchar for most things and am looking to get the best performance out of it.

I tend to do quite a few selects and store the user data in sessions to avoid having to query database every time. Username is unique and indexed like id as most of my selects compare have username in it with LIMIT 1 on my queries.

UPDATE:

I wanted to ask if I changed to enum for example how would I do a select and compare query for example in php for enum? I did look online but cannot find any example queries with enum being used. Also if I changed date_time for example to timestamp do I still use time() in php to insert the unix timestamp into date_time column database?

The reason I ask is I was reading one tutorial online that says when the row is queried, selected, updated etc MySQL automatically updates the timestamp for that row; is this true as I rather insert the timestamp using php time() in timestamp field. I use php time() already for date_time but use currently use varchar not timestamp.

Plus server time is in US and in php.ini I set it to UK time but I guess mysql would store it in the time on the server which again is no good as I want them in UK time.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萧瑟寒风 2024-10-26 04:31:44

一些提示：

您的状态应该是连接到查找的 int 或枚举。
性别同上
您可以使用 char 代替 varchar。关于这一点有很多讨论，但是虽然 varchar 确实可以帮助您减少大小，但大多数时候这并不是一个大问题。 char 可以更快。但这是一个棘手的问题。
将您的 date_time 保护为时间戳。有一个同上的数据类型
Last_visited
你的 ip 字段对我来说看起来有点长。
int(5) 可以容纳太多内容。因此，如果您的失败次数最多为 4，则不需要那么大的数字！ tinyint 最多可以容纳 127 个有符号数，或 255 个无符号数。

评论中的注释：

你可能可以标准化一些
fields：经常更新的字段，例如
失败登录计数、IP、上次访问次数
可能在另一个表中。这边走
你的会员表本身没有
经常更改并且可以在缓存中

我同意这一点:)

编辑：在您提出新问题后进行一些更新。

示例我将如何在 php 中对枚举进行选择和比较查询？

您可以将其与值进行比较，就好像它是一个字符串一样。唯一的区别是，在插入或更新时，您只能使用给定值。只需使用

SELECT * FROM table WHERE table.enum = "yourEnumOption"

将 date_time 更改为时间戳，我是否仍然使用 php 中的 time() 将 unix 时间戳插入 date_time 列数据库？

你可以在mysql中使用now()吗？（这只是我的一个想法，可能有一个小错误，但是：

INSERT INTO table (yourTime) VALUES (NOW());

我问的原因是我正在网上阅读一篇教程，其中提到当查询、选择、更新行等时，MySQL 会自动更新该行的时间戳；这是真的吗，因为我宁愿在时间戳字段中使用 php time() 插入时间戳。我已经使用 php time() 作为 date_time 但目前使用 varchar 而不是时间戳。

您可以使用 php 时间。时间戳不会自动更新，请参阅手册 (http:// dev.mysql.com/doc/refman/5.0/en/timestamp.html）：您可以在定义中使用类似的内容：

CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP)

Some tips:

Your status should be an int connected to a lookup, or an enum.
ditto for gender
You could use a char instead of varchar. There is a lot of discussion available on that, but while varchar does help you cut down on the size, that is hardly a big issue most of the time. char can be quicker. this is tricky point though.
safe your date_time as a timestamp. There is a datatype for that
ditto for last_visited
Your ip field looks a bit long to me.
an int(5) can hold too much. So if your failed count is max 4, you don't need that big of a number! A tinyint can hold upt o 127 signed, or 255 unsigned.

A note from the comments:

You could probably normalize some
fields: fields that update often, like
failed_login_count, ip, last_visited
could be in another table. This way
your members table itself doesn't
change as often and can be in cache

I agree with this :)

Edit: some updates after your new questions.

example how would I do a select and compare query for example in php for enum?

You can just compare it to the value as if it was a string. The only difference is that with an insert or update, you can only use the give value. Just use

SELECT * FROM table WHERE table.enum = "yourEnumOption"

changed date_time for example to timestamp do I still use time() in php to insert the unix timestamp into date_time column database?

You can use now() in mysql? (this is just a quick fromthetopofmyhead, could have a minor mistake, but:

INSERT INTO table (yourTime) VALUES (NOW());

reason I ask is I was reading one tutorial online that says when the row is queried, selected, updated etc MySQL automatically updates the timestamp for that row; is this true as I rather insert the timestamp using php time() in timestamp field. I use php time() already for date_time but use currently use varchar not timestamp.

You can use the php time. The timestamp does not get updated automatically, see the manual (http://dev.mysql.com/doc/refman/5.0/en/timestamp.html): you would use something like this in the definition:

CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP)

回复收藏 0 原文

隐诗 2024-10-26 04:31:44

首先你应该使用mysql的内置字段类型：

status为ENUM('activated', 'suspended', 'verify', 'delete');
性别是 ENUM('male','female','unknown')
last_visited 是 TIMESTAMP
dead_note 是 TEXT
失败登录计数是 TINYINT(1) 因为您不会有 10000 次失败登录 - INT(5)
date_time 是 DATETIME 或时间戳
1. 添加用户名和密码（组合）索引，以便更快地登录
2. 索引，唯一的电子邮件，因为您将通过它查询来检索密码，并且它应该是唯一的

您也可能想要规范化此表和单独的 dead_note、网站、IP、目标等到一个名为配置文件的单独表。这样，登录、会话更新、密码检索都是在更小的表中运行的查询，并且仅在需要具有个人资料/成员页面等数据的页面中选择其余数据。

然而，这往往会有很大差异，具体取决于您的应用程序的设计方式，但通常更好的做法是标准化。