Record MySQL development specifications

Mysql video tutorial column introduces the development specifications of MySQL

Recommended (free): mysql video tutorial

##Basic specifications

• Use innodb storage engine

• Table character set uses utf-8

• Add comments to all tables

• It is recommended that the data volume of a single table be controlled within 5000W

• No longer store big data such as graphs and files in the database

• It is prohibited to create a database online Stress test

• Prohibit test and development environments from directly connecting to the database

Naming convention

• Library name, table name, field name must have a fixed naming length, within 12 characters

• Library name, table name, field name cannot exceed 32 characters character. You must see the meaning of the name

• It is forbidden to use MySQL reserved words in library names, table names, and field names

• Temporary library and table names must start with tmp As the prefix, and use the date as the suffix

• The backup library and table must be prefixed with bak, and use the date as the suffix

Development and design specifications for libraries, tables, and fields

• It is prohibited to use partitioned tables

• Split large fields and Turn over fields with low consulting frequency and separate hot and cold data

• Use HASH to enter the scattered table, the suffix of the table name is a base number, and the subscript starts from 0

• Table sharding by date and time must comply with the

  YYYY[MM][DD][HH] format

• Use a suitable sharding strategy. For example, thousands of databases with ten tables, ten databases with hundreds of tables, etc.

• Do not use text and blob types as much as possible

• Use decimal instead of float and double storage Exact floating point numbers

• The simpler the better: convert characters to numbers, use tinyint instead of enum type

• All fields are not null

• Use unsigned to store non-negative numbers

• int type fixedly occupies 4 bytes of storage

• Use timestamp storage time

• Use int unsigned to store ipv4

• Use varbinary to store case-sensitive variable-length strings

• It is forbidden to store plain text passwords in the database, and the passwords must be encrypted and stored

• Use numeric type fields well

  
  

  Type
  Byte
  Minimum value
  Maximum value
  ##tinyint1-128127## smallintmediumint

  2	-32768	32767
  3	##-8388608	##8388607	int
  4	-2147483648	2147483647	bigint
  8	-9223372036854775808	9223372036854775807	If the numerical field is not that big, don’t use bigint It is best to use int to store IP instead of char(15) The use of enum is not allowed Avoid using null fields Null fields are difficult to query, and the index of null fields requires additional Space, composite index on null field is invalid. Index specifications The number of indexes in a single table shall not exceed 5 The number of fields in a single index does not exceed 5 Use prefix index for strings, and the length of the prefix index does not exceed 8 characters It is recommended to give priority to prefix indexes. If necessary, you can add pseudo columns and create indexes The table must have a primary key Do not use frequently updated columns as primary keys Try not to select string columns as primary keys Do not use uuid md5 hash these As the primary key - too discrete The default is to use the non-null unique key as the primary key It is recommended to choose auto-increment or numberer Important SQL must be indexed, such as where condition columns of update and delete statements, order by, group by, distinct fields Multiple table join Pay attention to the fields <p>1.区分度最大的字段放在前面<br>2.核SQL优先考虑覆盖索引<br>3.避免冗余和重复索引<br>4.索引要综合评估数据密度和分布以及考虑查询和更新比例<br></p> Index taboo <p>1.不在低基数列上建立索引、例如性别<br>2.不在索引列记性数学运算和函数运算<br></p> Try not to use foreign keys <p>1.外键用来保护参照完整性，可以业务端实现<br>2.对父表和字表的操作会互相影响，降低可用性<br></p> Index naming <p>1.非唯一索引必须以 inx_字段1_字段22.唯一索引必须以 uniq_字段1_字段2<br></p> The default value of the index field cannot be empty. Null greatly affects the query efficiency of the index. Repeatedly check the SQL related to the table and build an index based on the characteristics of the leftmost prefix. For indexes with multiple fields that are repeated, it is necessary to modify the order of the statement condition fields and create a joint index for them to reduce the number of indexes. If you can use a unique index, use a unique index R&D should often use explain. If they find that the index selectivity is poor, they must learn to use hints . SQL specification ##SQL statements are as simple as possible Things should be simple, and the entire thing should not take too long. Avoid using triggers, functions, and stored procedures Reduce business coupling and leave room for sacle out and sharding Avoid mathematical operations in the database, MySQL is not good at mathematical operations and logical judgment Don’t use selecy *, just select those fields when querying those fields If you use or in sql, rewrite it as in. The efficiency of or is not as high as that of in The number of numbers in in is recommended to be within 1000 limit paging and pay attention to efficiency. The larger the limit, the lower the efficiency. Use union all instead of union Avoid joining large tables Use group bu grouping , automatic sorting Updates to data should be broken up and updated in batches, do not update too much data at one time Reduce the number of interactions with the database Pay attention to the use of performance analysis tools SQL statements require all R&D, SQL keywords are all capitalized, and only one space is allowed per word SQL statements cannot have implicit conversions If you don’t need not in, don’t use it Forbidden to use The prefix is ​​% like Do not use negative queries, such as not in, not like It is forbidden to run large queries in the database Make precompiled statements and only pass parameters, which is more efficient than passing SQL statements. Reduce SQL injection. Disable order by rand Prohibit a single SQL statement from updating multiple tables at the same time Process specification All table creation operations need to inform the query SQL involved in the table in advance All table creation needs to determine which indexes are created before the table can be built online All table structure modification and index adding operations require the query SQL involving the table to be issued. Notify the DBA and other relevant personnel Before adding fields to the new table, the R&D team is required to send out an email at least 3 days in advance for evaluation, optimization and review by the DBA department Batch import and export data must be notified to the DBA in advance to assist in observation It is prohibited to perform back-end management and statistical queries from the online database Prohibit the existence of application accounts with super permissions No more batch updates and database queries during peak business periods

The above is the detailed content of Record MySQL development specifications. For more information, please follow other related articles on the PHP Chinese website!