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

1. Database design specification

Database naming convention

Basic database design specification

Design specification of database index

Database field design specification

SQL development specification

Database operation specification

1.1 Database naming convention

Objects are separated by lowercase letters and underscores

Prohibit the use of MySQL reserved keywords

The object name should not exceed 32 characters

PS: in fact, in previous versions of oracle11g, objects were less than 32 bits, and 12C seemed to grow to 128C.

Temporary tables are suffixed with tmp prefix date

Backup list with bak prefix date suffix

The column names and types that store the same data should be the same.

1.2 basic database design specification

All tables use the InnoDB storage engine

Unified use of UTF8 for libraries and tables

In MySQL, Chinese characters under UTF8 occupy 3 bytes, and ASCII codes occupy 1 byte.

Add comments to tables and fields

Try to control the amount of data in a single table. It is recommended to control less than 500W.

PS: the amount of data in a single table is too large, so there are great problems in backing up, restoring and modifying the table structure DDL.

PS: historical data archiving, sub-database and sub-table control the amount of data in a single table.

Use partition tables carefully; (this is the advice given in the lesson plan)

PS: the partition table is physically divided into multiple files, and logically it is shown as a table. If you choose the partition key carefully, the efficiency of cross-partition query may be lower. It is recommended to use physical sub-tables to manage big data.

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

The PS:MySQL table supports up to 4096 columns.

Prohibit the establishment of reserved fields in the table

PS: the reserved field cannot select the appropriate data type. If you modify the reserved field, the table lock will affect the concurrency.

It is prohibited to store binary data such as pictures and files in the database.

PS:DB stores address information for pictures or files that point to a file server.

Prohibit stress testing of the database online

Direct connection to the production library from the development test environment is prohibited

1.3 Specification for database index design

It is recommended that the number of single table indexes should not exceed 5.

Each InnoDB table must have a primary key

PS: you cannot use frequently updated columns as primary keys, or multiple columns as primary keys

Cannot use uuid, md5, hash, or string as primary key

The primary key recommends the use of self-incrementing ID values.

Index column recommendations:

The column after the where clause, the single-column index or combined index of the fields contained in group by,order by, distinct, and the Join column of multi-table federation.

The order of the index columns (the following priorities are from top to bottom):

The ones with high selectivity are listed on the left (priority)

The column with small field length is on the left.

The most frequently used column is on the left.

Avoid creating redundant and duplicate indexes:

Such as primary key (id), index (id), unique (id) à duplicate index

For example, index (a, b, c), index (a, b), index (a) à have redundant indexes on a column.

Adaptive overlay indexes are preferred for frequent queries:

Somewhat similar to oracle's back-to-table operation, the Select column is fetched directly from the index column.

Avoid the secondary lookup of the index by the InnoDB table.

Select a, b from tab where c = xxx

Create index index_name on tab (c, a, b)

Random IO can be converted into sequential IO.

Avoid using foreign keys as much as possible:

When you create a foreign key, it automatically builds an index on the foreign key.

Foreign key constraints are not recommended, but indexes are established on the associated columns between tables

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.