Shulou(Shulou.com)06/01 Report--

A database design specification

1 naming convention

All database object names must be separated by lowercase letters and underscores

Object names prohibit the use of MySQL reserved keywords

Naming should be no more than 32 bytes.

Temporary tables must have a tmp prefix and date suffix

Backup database table bak prefix date suffix

All column names and types that store the same data must be the same

2 basic design specification of database

All tables must use the innodb engine

Database and table character sets agree to use utf8 (full storage in Chinese can use gbk or gbk2312)

All tables and fields are annotated with comment

Maintain the data dictionary from the very beginning

It is recommended that the amount of data in a single table should be controlled within 5 million as far as possible.

Use MySQL partitioned tables with caution

Try to separate hot and cold data to reduce the width of the table.

Prohibit the establishment of reserved fields in the table

It is prohibited to store binary data such as picture files in the database

It is forbidden to do stress testing of databases online.

It is forbidden to connect to the database of the production environment from the development environment test environment

3 Index design specification

Limit the number of indexes per table. It is recommended that there are no more than 5 indexes per table.

Each innodb table must have a primary key. Column primary keys that are not updated frequently are not suitable for leaving more primary keys.

Common index retention recommendations: columns in where clauses in select update delete statements

Fields contained in order by, group by distinct

Associated columns for multi-table joins

How to select the order of index columns:

The most differentiated column is placed on the far left of the federated index (choose the primary key as much as possible)

The columns with small fields are placed on the far left of the federated index

The most frequently used columns are placed on the left side of the federated index

Foreign key constraints are not recommended, but must be indexed on the associated keys between tables

4 database field design specification

Limited selection of the smallest data type that meets storage needs

For example, numeric type storage for string conversion

Unsigned × × × is used to store non-negative data (unsigned int)

Avoid using text blob enum types

Try to define the column as not null

Use large datetime or timestamp types to store time

Finance-related amount data must use the decimal type

5 Database sql development specification

It is recommended to use precompiled statements for database operation.

Avoid using double% query conditions such as like% w%

Future extensions should be considered

Program connection

Prohibit the use of select *

Avoid using subqueries that can be converted to join operations

Avoid using join to associate too many tables. It is recommended that there be no more than 5.

Reduce the number of interactions with the database

Obviously there will be no duplicate value is to use union all instead of union

Split a complex large sql into multiple small sql

6. Database operation behavior specification

Batch operations of more than 1 million lines need to be carried out in batches for many times.

Use pt-online-schema-change to modify the table structure for large tables

It is forbidden to grant super permissions to accounts used by the program, follow the principle of minimum permissions, and are not allowed to have drop permissions

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.