MySQL database design code and principles

MySQL database design specification 1, the database naming specification uses 26 English letters (case-sensitive) and 0-9 natural numbers (often do not need) with the underscore ' _ ' composition; the name is concise and clear (the length cannot exceed 30 characters); For example: User, stat, log, or Wifi_ User, Wifi_stat, wifi_log add a prefix to the database, unless the backup database can add 0-9 of the natural number: USER_DB_20151210;2, database table name specification uses 26 letters (case-sensitive) and 0-9 natural numbers (often not required) Add underline ' _ ' composition, concise and clear name, multiple words with underscore ' _ ' separated; For example: User_login, User_profile, User_detail, User_role, User_role_relation, User_role_ Right, the user_role_right_relation table prefix ' user_ ' can effectively bring together tables of the same relationship; 3. The database table field name specification uses 26 English letters (case sensitive) and 0-9 natural numbers (often not required) with an underscore ' _ ' The name is concise and clear, and multiple words are separated by the underscore ' _ '; for example: User_login table field user_id, user_name, Pass_word, Eamil, TickIT, status, Mobile, add_time; There must be a self-increment primary key in each table, the Add_time (default system time) table is the same as the associated field name requirement between tables; 4, the Database table field type specification uses as few storage space as possible to save the data of one field; For example: you can use int instead of varchar, char, You can use varchar (16) to avoid using varchar (n); IP addresses are best used with the type int; fixed-length types are best used with char, for example: ZIP code; You can use tinyint to avoid smallint,int; it's best to give each field a default value. It is best not to have a clear name for the NULL;5, database table index specification, for example: User_login table user_name field index should be user_name_index unique index, create a primary key index for each table, and create a reasonable index for each table; To set up a composite index please be careful; 6, simple familiar with the first paradigm of database paradigm (1NF): The field values are atomic and can no longer be divided (all relational database systems satisfy the first paradigm); For example, the last Name field, where the surname and name is a whole, if you distinguish first and last names then you must establish two separate fields; 2NF): A table must have a masterThe key, that is, each row of data can be uniquely differentiated; note: The first paradigm must first be satisfied; the third paradigm (3NF): A table cannot contain information about non-critical fields in other related tables, that is, the data table cannot have a sink field; Note: The second paradigm must be met first; note: Often we do not obey the third paradigm  Because the reasonable sink remainder field will give us to reduce the join query;  For example: The album table will be added to the image of the click Number field, in the album Picture table will also add the image of the click Number field; MySQL Database design principles 1, the core principle is not the database to do the operation; CPU calculation must be moved to the business layer, the number of control columns (field few but good, the number of fields suggested within 20); Balance paradigm and redundancy (efficiency first; often sacrificing paradigm) reject 3B (large SQL statement rejected: Big SQL, Reject big things: big transaction, reject Mass: Big Batch), 2, field class principle with good value type (save space with appropriate field type); convert characters to numbers (the best conversions that can be converted, save space and improve query performance); Avoid using null fields ( Null field is difficult to query optimization, NULL field index requires extra space, null field's composite index is invalid), less text type (try to use varchar instead of text field); 3. Index class principle use index (improve query, slow update, index must not be more better); Character fields must be indexed; InnoDB primary key is recommended to use the self-increment column (primary key to establish clustered index, the primary key should not be modified, the string should not be the master key) (Understanding InnoDB index preservation structure to know); no foreign key (guaranteed by the program); 4. SQL class principle SQL statement as simple as possible (a SQL can only be in a CPU operation, large statements to remove small statements, reduce lock time, a large SQL can block the entire library); simple transactions; avoid using TRIG/FUNC (triggers, functions not replaced by client programs); no SELECT * (Consumes Cpu,io, memory, bandwidth, this program is not extensible), or is rewritten as in (or is an n-level efficiency), or is rewritten as union (MySQL's index merge is mentally retarded); Select ID from t where phone = ' 159′or name = ' John '; =>select ID from the where phone= ' 159′unionselect ID from t where name= ' jonh ' avoids negative%; use COUNT (*) sparingly; limit efficient paging (the greater the limit, The lower the efficiency); Use UNION ALL instead of union (union has a de-heavy cost); less connection Join; Use GROUP by; Use the same type comparison; break up batch update; 5. Performance analysis tool show profile;mysqlsla;mysqldumpslow;explain;show slow log;show processlist; 

　　

MySQL database design code and principles