MySQL Database development specification

Adherence to the norms can be 28 principles, not dogma. Some specifications can be overlooked to meet actual needs.

1. Index specification * Target |--use minimal index cost to find the required Row Records * Principle |--do prefix matching |--Select high-sensitivity column to prefix index column |--try to use a repeating index to match the where condition |--avoid heavy The |--index column of a complex index contains as much as possible the columns involved in the group by and order by '--when the query results are larger, consider the index overlay 2.     SQL Specification * Principle |--Avoid large transactions to reduce waiting and competition |--do not use * To use a specific column name |--encounter a paging query using deferred association resolution (If you have offset, you can select the ID first and then associate with the primary key ID) |--Avoid multiple table queries avoid join |--in elements prohibit more than 300 |--avoid the% prefix to do fuzzy query, do not go index |--do not update large amounts of data at once (>30000) |--try to use union or in place of or |--avoid using subqueries as much as possible with the join '- -Do not use negative query not in/like instead of 3 using in reverse.    DDL specification * |--DDL locks the table to assess the impact of the lock table on the business system * Principle |--all changes are submitted one day in advance |--daily changes after work, major change time after the communication to determine |--all DDL changes required and research and development communication review and then submit    '--all newly-launched tables must be indexed before they can go online 4. Design criteria for tables * principle |--using the InnoDB storage engine, the end of the table must be Engine=innodb auto_increment=1 Default charset=uft8mb4    |--table must have physical primary key |    |--id Int (ten) unsigned primary key not NULL Auto_increment |    |--id bigint (unsigned) primary key not NULL Auto_increment | |--int (m) m indicates the maximum display width, and the number of digits is not related.    Int (3), int (8) occupies 4 btyes of storage on disk | '--a table with an infinite number of estimated data, primary key all so use bigint unsigned |--forbidden charactersMaster Key |--Disable Federated primary key |--do not store large data such as picture files in the database '--prohibit the use of partitioned tables, and periodically create partitioned tables will cause problems for system operations 5. Column Design Specification * principle |--Minimize storage space |--use numeric types as much as possible +unsig    Ned |--Disabling the Blob/text field |--disabling null values, it is recommended to add a default value when defining |    |--null fields are difficult to query for optimization |    Invalid composite index for |--null field | '--null field indexes require extra space |--prohibit using foreign keys |--use unsigned store non-negative numbers '--varchar (n) n means that the number of characters is not the number of bytes. The maximum length of all varchar fields in a table in |--mysql and    is 65,535 bytes '--When you sort and create a temporary table, you use the length of N to request memory 6. Naming specification * Specification |--Library/table/column/index name all lowercase |--names do not exceed 32 characters |--Index |    |--format is idx_ column name abbreviation | '--Unique index uniq_ column name '--to be known

MySQL Database development specification