需要关于如何进一步优化该数据库的建议

Question

我即将开发一个基于PHP / MySQL的聊天系统。数据库和网站都将运行在同一台服务器上。我希望能够同时支持300个在线用户。这显然会持续产生大量的查询。因此，优化数据库设计是必不可少的。

我在MySQL方面的技能一般。我尽我最大的努力去学习优化，做我所理解的每一件事。但我还是不确定它是否足够好。我希望你们能看看它，并给出如何进一步优化它的建议。

关于如何减少服务器负载的任何其他提示也是受欢迎的。请记住，由于当地法律的原因，所有消息都必须保存。

CREATE TABLE IF NOT EXISTS `bans` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `userid` int(11) NOT NULL,
    `ipaddress` varchar(32) NOT NULL,
    `reason` text,
    `bandate` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `BAN_INDEX` (`userid`,`ipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `chatboxes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `boxname` varchar(32) NOT NULL,
    `password` varchar(32) NOT NULL,
    `accesslvl` tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
    UNIQUE KEY `BOX_INDEX` (`boxname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `ignorelist` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `userid` int(11) NOT NULL,
    `blockedusr` varchar(16) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `messages` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `userid` int(11) NOT NULL,
    `boxname` varchar(32) NOT NULL,
    `message` text,
    `receiver` varchar(16) NOT NULL,
    `alert` tinyint(1) NOT NULL DEFAULT '0',
    `msgdate` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(16) NOT NULL,
    `password` varchar(32) NOT NULL,
    `email` varchar(64) NOT NULL,
    `gender` tinyint(1) NOT NULL DEFAULT '0',
    `sexpref` tinyint(1) NOT NULL DEFAULT '0',
    `birthdate` date DEFAULT NULL,
    `regdate` datetime DEFAULT NULL,
    `lastlogin` datetime DEFAULT NULL,
    `warnings` tinyint(1) NOT NULL DEFAULT '0',
    `accesslvl` tinyint(1) NOT NULL DEFAULT '1',
    `kicks` tinyint(3) NOT NULL DEFAULT '0',
    `ipaddress` varchar(32) NOT NULL,
    `online` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `USER_INDEX` (`username`,`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Answer 1

虽然我不知道选择的原因，但我建议对于在users表(bans、ignorelist、messages)中存储对用户的引用的表，应该存储用户主键id (创建一个userid字段以在每个表中存储数据)，而不是username。这将允许标准化数据。如果用户更改其名称，则不希望返回并更新所有其他表。

此外，IP地址并不总是静态的，所以除非您只是禁止用户使用特定的IP地址(不太有效)，并且假设用户必须登录聊天，我建议再次基于userid禁止使用UNIQUE KEY，这样您就可以在userid上使用您的UNIQUE KEY了。

要查询显示用户名作为结果集一部分的ban，可以将bans连接到users (例如，SELECT bans.reason, users.username FROM bans JOIN users ON bans.userid = users.id)

希望这能有所帮助。