"Internet MySQL Development code" based on years of experience

First, the basic norms

• Using the INNODB storage Engine
• Table character set using UTF8
• Add comments to all tables
• Single-table data volume recommended for control within 5000W
• Do not store maps, files and other big data in the database
• Prohibit online database stress testing
• A direct database from the test and development environment

Second, naming norms

• Library Name table Name field name must have a fixed name length, within 12 characters
• The library name, table name, field name are forbidden more than 32 characters. Must see the meaning of the name
• Library name, table name, field name forbidden? Use MySQL to keep the word
• Temporary libraries, table names must be prefixed with TMP and date suffix
• Backup library, table must be prefixed with bak and date suffix

Three, library, table, field development design code

• Ban? Using partitioned tables
• Split large and low-frequency fields, separating hot and cold data
• Hash table with hash, table name suffix using decimal number, subscript starting from 0
• By date time table to conform to YYYY[MM][DD][HH] format
• Adopt the appropriate sub-database sub-table strategy. such as the Thousand library ten tables, 10 library hundred meters and so on
• Try not to use TEXT , BLOB type
• Storing exact floating-point numbers in DECIMAL instead of float and DOUBLE
• The simpler the better: convert characters to numbers, use TINYINT instead of ENUM type
• All fields are defined as not NULL
• storing non-negative integers using UNSIGNED
• type int fixed 4 byte storage
• Storage time using timestamp
• Using the INT UNSIGNED storage IPV4
• Using VARBINARY to store case-sensitive variable-length strings
• Prohibit storing plaintext passwords in the database, encrypting passwords and storing them
• Use a good value type field
• Storage IP is best stored in int rather than char
• ENUM not allowed
• Avoid null fields, null fields are difficult to query for optimizations, NULL field indexes require extra space, and NULL composite indexes for fields are invalid
• Less use of text/blob , varchar performance will be much higher than the text , can not avoid the blob, please split the table
• The database is not allowed to store large files, or photos, you can put large objects on disk, the database stores its path

IV. Index Specification

1, the number of indexes to control:

• The number of indexes in a single table does not exceed 5
• The number of fields in a single index does not exceed 5
• Prefix index for string, prefix index length not more than 8 characters
• It is recommended that you prioritize the prefix index, add pseudo-columns if necessary, and make an index

2. Primary KEY Criteria

• Table must have a primary key
• Do not use columns that are frequently updated as primary keys
• Try not to select a string column as the primary key
• Do not use UUID MD5 HASH These as primary keys (values are too discrete)
• The default is to have a non-null unique key as the primary key
• Recommended selection of self-increment or generator number

3. Important SQL must be indexed, such as:

• The WHERE Condition column of the UPDATE , DELETE statement
• Fields for ORDER by, GROUPBY , DISTINCT

4. The fields of the multi-table join note the following

• The field with the highest degree of sensitivity is placed in front
• Nuclear? SQL overrides the overriding index
• Avoid redundant and duplicate indexes
• Index to comprehensively assess data density and distribution and consider query and update ratios

5. Index Taboo

• Do not index on low cardinality columns, such as "gender"
• Do not perform mathematical operations and function operations on indexed columns

6, try not to use foreign keys

• Foreign keys are used to protect referential integrity and can be implemented on the business side
• Actions on parent and child tables affect each other, reducing availability

7, Index name: non-unique index must be named in the Idx field 1 Field 2, unique so must be named in the uniq field 1 Field 2, the index name must all lowercase8. The new unique index must not be duplicated with the primary key9. The default value of the indexed field cannot be null , and it should be changed to the other defaults or null. NULL affects query efficiency of indexes very much10. Repeatedly view the SQL associated with the table, and index the characteristics of the leftmost prefix. Multiple-field repeating statements, to modify the order of the statement condition fields, to establish a federated index for them, and to reduce the number of indexes11, can use unique index to use unique index, improve query efficiency12, research and development to often use explain , if found poor index selection, must let them learn to use hintV. SQL specification

• SQL statements as simple as possible, large SQL to find a way to break down into small SQL statements (take full advantage of querycache and make full use of multicore CPUs)
• Transaction is simple, the whole transaction length is not too long
• Avoid using triggers, functions, stored procedures
• Reduced business coupling for S acle out, sharding
• Avoid in-database math operations (MySQL is not good at math and logic judgments)
• Do not use SELECT * to query which fields are in SELECT these fields
• The use OR of rewrite in SQL IN() ( OR efficiency is not IN high)
• The number of numbers in the inside is recommended to be controlled within

• LIMITPaging attention to efficiency. LIMITthe larger, the less efficient. You can rewrite the LIMIT , such as an example rewrite:

  10000 Ten 10000 ten;

• Use Union all instead of Union

• Avoid joins with large tables
• Using GROUP by grouping, automatic sorting
• Updates to the data to be broken up after batch update, do not update too much data at once
• Reduce the number of interactions with the database

• Note Using the Performance analysis tool

  SQL Explain/showprofile/mysqlsla

• SQL statement requires all development, SQL keywords are all uppercase, each word only allowed a space

• No implicit conversions can occur for SQL statements, such as SELECT ID from TABLE WHERE id='1'
• In terms of the number of data to be less, I remember should be within 500, to learn to use exist instead of in,exist in some scenes query will be faster than in
• Can not do without notin , there are too many pits. Will check for null and null .
• In SQL statements, the use of prefixes is forbidden % .LIKE
• Do not use a negative query like
• About paging query: The program recommended reasonable use of paging to improve efficiency LIMIT , OFFSET larger to match sub-query use
• Prohibit running large queries in the database
• To pre-compile statements, pass only parameters, more efficient than pass SQL statements, one parse, multiple use; reduce SQL injection probability
• Prohibit the order by RAND ()
• A single SQL statement updates multiple tables at the same time

VI. Process Specifications

• All the table operations need to be informed in advance of the query SQL involved in the table;
• All the tables need to determine which indexes should be established before the table can be built on-line;
• All changes to the table structure, plus index operations need to be related to the table of query SQL sent to inform the DBA and other relevant personnel;
• Before the new Table plus field, ask for research and development at least 3 days in advance of the mail, to the DBA to assess, optimize and review the time
• Batch import, export data must be notified to DBA for assistance in advance observation
• Prevents background management and statistical class queries from being performed on-line from the library
• Disable the presence of super-privileged application accounts
• The DBA must be notified in advance of a promotion or a new feature on-line traffic assessment
• Do not bulk update, query database during peak business hours

The original--> Poke Me

Turn from--> Poke Me

"Internet MySQL Development code" based on years of experience