mysql优化之表建设

就拿常见的用户表、文章类的表、日志表来分析如下
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id',
  `name` varchar(255) NOT NULL COMMENT 'user name',
  `password` char(41) NOT NULL COMMENT 'user password',
  `email` varchar(64) NOT NULL COMMENT 'user email',
  `mobile` char(11) NOT NULL COMMENT 'user mobile phone',
  `remark` text COMMENT 'user remark',
  `subscribed` tinytext COMMENT '是否订阅稿件,针对实例',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'user status(1:待审核,2:审核通过,3:驳回,4:预留状态,5:预留状态)',
  `is_super` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用户权限:0:普通用户 1:超级管理员 2 普通管理员',
  `is_recommend` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否允许推荐[0:不允许1:允许]',
  `login_time` int(11) DEFAULT NULL COMMENT '最近一次登录时间',
  `login_cur` int(11) DEFAULT '0' COMMENT '当前登录时间',
  `create_time` int(11) NOT NULL COMMENT 'create time',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'last modify time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`) USING BTREE,
  UNIQUE KEY `email_2` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=273 DEFAULT CHARSET=utf8 COMMENT='用户基本信息'
 
CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` mediumint(9) NOT NULL COMMENT '实例id',
  `catid` mediumint(10) NOT NULL DEFAULT '0' COMMENT '分类id',
  `module` char(5) NOT NULL DEFAULT 'text' COMMENT '稿件模型',
  `item_id` varchar(50) DEFAULT NULL COMMENT '文章id',
  `tag_id` varchar(10) DEFAULT NULL COMMENT '分类;news:闻 6_7,listen:听 163_164,comment:评 161_162,focus:首页焦点 6,imp_news:要闻 10',
  `tag_type` varchar(20) DEFAULT NULL COMMENT '分类标签; 例如 时局,时评,碰撞',
  `title` varchar(200) NOT NULL COMMENT '文章标题',
  `content_title` varchar(100) DEFAULT NULL COMMENT '短标题',
  `sub_title` varchar(100) DEFAULT NULL COMMENT '副标题',
  `intro_title` varchar(100) DEFAULT NULL COMMENT '肩标题',
  `keywords` varchar(255) NOT NULL COMMENT '关键字列表,关键字之间用“|”分隔',
  `time` char(11) NOT NULL COMMENT '时间',
  `authors` varchar(100) DEFAULT NULL COMMENT '作者',
  `update_time` varchar(11) DEFAULT NULL COMMENT '文章更新时间',
  `description` varchar(200) DEFAULT NULL COMMENT '描述',
  `content` text COMMENT '文章内容',
  `doings_source` varchar(100) DEFAULT NULL COMMENT '来源',
  `show_type` tinyint(4) DEFAULT '0' COMMENT '1是大图,2是一张小图,3是三张图,4是图列表的一张大图,5是图列表的两张图,6是关键字搜索,7快讯',
  `image_list` text COMMENT '图片列表',
  `url` varchar(200) DEFAULT NULL COMMENT '未知',
  `share_url` varchar(300) DEFAULT NULL COMMENT '备用 文章分享地址',
  `audio` text COMMENT '音频信息',
  `video` text COMMENT '视频信息',
  `createtime` int(10) NOT NULL DEFAULT '0' COMMENT '文章创建时间',
  `modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_del` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除[0:否,1:是]',
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_id_2` (`tag_id`,`item_id`) USING BTREE,
  KEY `tag_id` (`tag_id`) USING BTREE,
  KEY `tag_type` (`tag_type`) USING BTREE,
  KEY `title` (`title`) USING BTREE,
  KEY `item_id` (`item_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='稿件表'
 
CREATE TABLE `weibo_log` (
  `lid` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '操作记录 id',
  `name` varchar(512) NOT NULL DEFAULT '非本平台' COMMENT '平台用户名',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '系统用户id',
  `platform_id` int(6) unsigned NOT NULL COMMENT '平台id',
  `related_user_id` varchar(31) NOT NULL COMMENT '微博用户id',
  `parameter` text NOT NULL COMMENT '操作的参数',
  `result` text NOT NULL,
  `item_id` varchar(20) NOT NULL COMMENT '与该操作关联的微博item',
  `time` int(11) NOT NULL COMMENT '时间,时间戳格式',
  `date` int(8) NOT NULL COMMENT '用于按天分类',
  `action_id` tinyint(4) NOT NULL DEFAULT '0' COMMENT '与该操作关联的操作名id 1:删除微博,2:修改微博,3:重新发送微博,4:立即发送',
  `result_id` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态类别 1:发布成功, 2:待发送, 3:发送失败, 4:已删除',
  `ip` varchar(48) NOT NULL COMMENT '操作的IP',
  `location` varchar(128) NOT NULL COMMENT '该操作的地理信息',
  `timer_id` int(10) NOT NULL DEFAULT '0' COMMENT '定时任务的id',
  `del_name` varchar(25) DEFAULT NULL COMMENT '执行操作的人',
  `comment_count` int(10) NOT NULL DEFAULT '0' COMMENT '该条微博的评论数',
  `repost_count` int(10) NOT NULL DEFAULT '0' COMMENT '该条微博的转发数',
  `praise_count` int(10) NOT NULL DEFAULT '0' COMMENT '该条微博的点赞数',
  `editors` varchar(512) NOT NULL COMMENT '编辑 多个 逗号分隔',
  `author_departments` varchar(512) NOT NULL COMMENT '部门_微博作者 多个 逗号分隔',
  `is_crawl` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是抓取过来的,非本平台的,默认0',
  PRIMARY KEY (`lid`),
  KEY `platform_id` (`platform_id`),
  KEY `user_id` (`user_id`),
  KEY `time` (`time`),
  KEY `date` (`date`),
  KEY `result_id` (`result_id`),
  KEY `action_id` (`action_id`),
  KEY `is_crawl` (`is_crawl`),
  CONSTRAINT `weibo_log_ibfk_1` FOREIGN KEY (`platform_id`) REFERENCES `weibo_platform` (`platform_id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8 COMMENT='微博 操作记录'
 
个人经验之谈:
 
1.id: 每个表应该都有一个id   unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id' 参考了下discuz中的各个表 发现他的id 一般有 int(10) mediumint(8) smallint(6) 个人偏向于习惯用int(10) 当然针对具体情况可稍作调整
 
2.name:这个字段得看需求中的name具体表示什么了,如果是用户姓名个人觉得用varchar(15)就足以了(中文),如果是用户随便输的一个标志名如nickname、filename那就设置成varchar(255),省事,免得多想,如果是固定长度的就用char吧
 
3.password:密码字段,这就得看这个密码是怎么加密的,简单的如PHP的md5加密一般就用char(32),hash加密char(48)
 
4.email:邮件字段,设计成varchar(100)差不多了,别多想
 
5.mobile:char(11)没啥说的,如果是座机就另建一个字段吧
 
6.remark: 评论字段一般就用text,还用到这个字段的比如文章内容、描述
 
7.subscribed:是否订阅,类型这样的只有两面性质的字段如sex 、is_recommend、is_super 就定义成boolean,也有根据自己的项目定义成tinyint(1)型的(0和1两个值),有超过两面性质的就用enum枚举类型的,如status(状态值)
 
8.与时间有关的比如login_time  create_time  modify_time 这样的字段,个人习惯定义成timestamp形如2016-04-06 14:18:24或者int(10)形式如1430833379,方便计算和转换
 
9.IP:个人习惯用int(15) unsigned NOT NULL  用这类定长的字段会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2
 
10.title:这种文章标题可用varchar(200)
 
11.记录时间的字段一般用int(10) 方便查询
 
注意事项:
1.当表字段设置的set类型有0这个值时在写sql语句的过程中需要把对应的整型0变换成字符串0,否则插入不成功
 
2.为常用的条件字段(where后面出现的字段)建立索引,效果特别明显
 
3.尽量使用NOT NULL 除非有一个非常特别的理由去使用NULL
 
4.两张表通过某个字段关联时(外键)因使用相同类型和形同字符集并建立索引
 
5.取值有限而固定的性别 部门 状态值 就用enum 的 【 ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串】
 
6.能用固定长度的字段就用固定长度如char int,变长的如 varchar blog text 
 
7.根据不同场景使用合适的存储引擎(可以参考这个http://www.youdiancms.com/info/348.html)
 
8.为每一个字和表写明注释,方便自己更方便他人
 
暂时只能想到这么多了,以后根据经验再补充吧~
 
更多参考:
 

posted on 2016-05-27 20:16  Ryanyanglibin  阅读(200)  评论(0编辑  收藏  举报

导航