MySQL Optimization table construction

Take the common user table, the article Class table, the log table to analyze the following CREATE table ' user ' (  ' id ' int (one) unsigned not NULL auto_increment COMMENT ' user ID ',  ' Name ' varchar (255) NOT null COMMENT ' user name ',  ' password ' char (all) NOT null COMMENT ' user password ',  ' email ' varchar ' NOT null COMMENT ' user email ',  ' mobile ' char (one) NOT null COMMENT ' user mobile phone ',  ' remark ' Text COMMENT ' user remark ',  ' subscribed ' tinytext COMMENT ' whether to subscribe to the manuscript, for instance ',  ' status ' tinyint (4) not NULL DEFAULT ' 1 ' COMMENT ' User status (1: Pending review, 2: Approval, 3: Dismissed, 4: Reserved status, 5: Reserved status) ',  ' is_super ' tinyint (4) not NULL DEFAULT ' 0 ' COMMENT ' user right Limit: 0: Normal User 1: Super Admin 2 normal admin ',  ' is_recommend ' tinyint (1) Not NULL DEFAULT ' 1 ' COMMENT ' whether to allow recommendation [0: Disallow 1: Allow] ',  ' login_ Time ' int (one) default NULL COMMENT ' last login time ',  ' login_cur ' int (one) default ' 0 ' COMMENT ' current login ',  ' create_time ' Int (one) NOT null COMMENT ' Create Time ',  ' modify_time ' timestamp not null DEFAULT current_timestamp on UPDATE curren T_timestamp COMMENT ' last Modify Time ',  PRIMARY key (' id '),  unique key ' email ' (' email ') USING btree,  unique key ' email_2 ' (' Emai L ') USING BTREE) engine=innodb auto_increment=273 DEFAULT charset=utf8 comment= ' user basic information '  create TABLE ' news ' (  ' id ' int (one) not null auto_increment,  ' PID ' Mediumint (9) Not NULL COMMENT ' instance ID ',  ' catid ' mediumint (ten) not NU LL default ' 0 ' COMMENT ' class ID ',  ' module ' char (5) Not NULL default ' text ' COMMENT ' manuscript model ',  ' item_id ' varchar (50) Default NULL COMMENT ' article ID ',  ' tag_id ' varchar (TEN) default null COMMENT ' classification; News: Smell 6_7,listen: Listen to 163_164,comment: 161 _162,focus: Home Focus 6,imp_news: News ",  ' tag_type ' varchar () DEFAULT NULL COMMENT ' category label; For example, current situation, dispatches, collision ',  ' title ' varchar ' ($) NOT null COMMENT ' article title ',  ' content_title ' varchar (+) DEFAULT NULL COMMENT ' Short title ',  ' sub_title ' varchar (+) default null COMMENT ' subtitle ',  ' intro_title ' varchar (+) default null COMMENT ' shoulder Title ',  ' keywords ' varchar (255) Not NULL COMMENT ' keyWord list, between keywords with "|" Delimited ',  ' time ' char (one) NOT null COMMENT ',  ' authors ' varchar (+) DEFAULT NULL COMMENT ' author ',  ' Update_ti Me ' varchar (one) default null COMMENT ' article update time ',  ' description ' varchar ($) Default null COMMENT ' description ',  ' content ' Text COMMENT ' article content ',  ' doings_source ' varchar (+) Default NULL COMMENT ' source ',  ' show_type ' tinyint (4) Default ' 0 ' COMMENT ' 1 is a large map, 2 is a small picture, 3 is three charts, 4 is a large picture list, 5 is a graph list of two graphs, 6 is a keyword search, 7 News ',  ' image_list ' text COMMENT ' picture list ',  ' URL ' varchar ($) default null COMMENT ' unknown ',  ' share_url ' varchar ' default null COMMENT ' alternate article share address ',  ' audio ' Tex T COMMENT ' audio info ',  ' video ' text COMMENT ' visual info ',  ' createtime ' int (ten) not NULL DEFAULT ' 0 ' COMMENT ' article creation time ', &nbs P ' Modifytime ' timestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp COMMENT ' modified time ',  ' Is_del ' Tinyi NT (1) not NULL DEFAULT ' 0 ' COMMENT ' whether delete [0: No, 1: Yes] ',  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= ' manuscript table '  create TA BLE ' Weibo_log ' (  ' lid ' bigint () unsigned not NULL auto_increment COMMENT ' operation record ID ',  ' name ' varchar (+) not Null default ' non platform ' COMMENT ' platform user name ',  ' user_id ' int (ten) unsigned not NULL default ' 0 ' COMMENT ' System user ID ',  ' PLATF orm_id ' int (6) unsigned NOT NULL COMMENT ' platform ID ',  ' related_user_id ' varchar (+) NOT null COMMENT ' Weibo user id ',  ' par Ameter ' text NOT null COMMENT ' operation parameter ',  ' result ' text not null,  ' item_id ' varchar (a) NOT null COMMENT ' associated with the operation Weibo item ',  ' time ' int (one) not null COMMENT ' times, timestamp format ',  ' date ' int (8) NOT null COMMENT ' for day class ',  ' action_id ' Tinyint (4) not NULL DEFAULT ' 0 ' COMMENT ' action name associated with this operation ID 1: Delete Weibo, 2: Modify Weibo, 3: Resend Weibo, 4: Send immediately ',  ' result_id ' tinyint (4) Not N ULL DEFAULT ' 0 ' COMMENT' Status Category 1: Publish successful, 2: Pending send, 3: Send failed, 4: ',  ' ip ' varchar ' not NULL COMMENT ' operation IP ',  ' location ' varchar (+) not NU LL COMMENT ' The operation's geographic information ',  ' timer_id ' int (ten) not NULL DEFAULT ' 0 ' COMMENT ' timed task ID ',  ' del_name ' varchar (+) Defau LT null COMMENT ' the person performing the operation ',  ' comment_count ' int (ten) not NULL DEFAULT ' 0 ' COMMENT ' The number of comments on the tweet ',  ' repost_count ' int ( Not null default ' 0 ' COMMENT ' The number of forwards of the microblog ',  ' praise_count ' int (ten) NOT null default ' 0 ' COMMENT ' The number of likes of the Weibo ',  ' Editors ' varchar (+) NOT null COMMENT ' edit multiple comma separated ',  ' author_departments ' varchar (+) NOT NULL COMMENT ' department _ Weibo author multiple Comma separated ',  ' is_crawl ' tinyint (4) Not NULL default ' 0 ' COMMENT ' is crawling over, not on the platform, defaults to 0 ',  PRIMARY key (' lid '),  key ' platform_id ' (' platform_id '),  key ' user_id ' (' user_id '),  key ' time ' (' time '),  key ' Date ' (' Date '), & nbsp 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 the DELETE NO ACTION on UPDATE CASCADE) Engine=innodb AUT o_increment=73 DEFAULT Charset=utf8 comment= ' Weibo operations record '   personal experience:  1.id: Each table should have an ID   unsigned not NULL auto_ INCREMENT COMMENT ' User ID ' references the tables in the next discuz find his ID usually has int (ten) Mediumint (8) smallint (6) Personal preference for the use of Int (10) Of course, for specific circumstances can be slightly adjusted &NBS P;2.name: This field depends on the name specified in the requirements, if it is a user name personally feel that with varchar (15) is Enough (Chinese), if the user randomly loses a flag name such as nickname, filename that is set to varchar ( 255), easy, lest many think, if it is fixed length with char bar  3.password: Password field, this depends on how the password is encrypted, simple as PHP MD5 encryption generally with char (+), hash encryption char (48)  4.email: Mail field, designed to be varchar (100) Almost, don't think  5.mobile:char (11) Nothing to say, if it is a landline to build another field it  6.remark: Comment field is generally used text , also used in this field, such as the article content, description  7.subscribed: Whether the subscription, the type of only two-sided nature of the field such as sex, Is_recommend, is_super is defined as a Boolean, There are also tinyint (1) types (0 and 12 values) that are defined according to their own items, and have more than two properties that are enumerated in enum type, such as status (state value) 8. Time-related, such as Login_time  create_time   Modify_time Such a field, the personal custom is defined as a timestamp shape such as 2016-04-06 14:18:24 or int (10) Form 1430833379, convenient to calculate and convert  9.ip: personal habits with int (15 unsigned not NULL   This type of fixed-length field gives you the advantage of querying, especially when you need to use such a where condition: IP between Ip1 and Ip2 10.title: This article title can be used in varchar   NOTE: 1. When a table field is set with a set type of 0, the corresponding integer 0 is transformed into a string 0 in the process of writing the SQL statement, otherwise the insertion is unsuccessful  2. For Common Criteria fields ( The field that appears after the where) is indexed, and the effect is particularly noticeable  3. Try to use not NULL unless there is a very special reason to use null 4. Two tables when associated with a field (foreign key) using the same type and shape of the same character set and indexing   5. Limited and fixed gender sector status values   The enum type with enum is very fast and compact. In fact, it is TINYINT, but it appears as the string " 6." A fixed-length field can be used with fixed lengths such as Char int, which is longer like a varchar blog text  7. Use the right storage engine for different scenarios (You can refer to this http://www.youdiancms.com/info/348.html)  8. Annotate each word and table to make it easier for others  temporarily can only think of so many, after the experience to add it ~More information: http://blog.csdn.net/qc20042/article/details/8791561http://developer.51cto.com/art/201307/402791_2.htm

MySQL Optimization table construction