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) 注意事項：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.為每一個字和表寫明注釋，方便自己更方便他人 暫時只能想到這麼多了，以後根據經驗再補充吧~ 更多參考：http://blog.csdn.net/qc20042/article/details/8791561http://developer.51cto.com/art/201307/402791_2.htm 

mysql最佳化之表建設