High-performance extensible MySQL Database design and Architecture Optimization e-commerce project _1

A Database design specification

1 Naming conventions

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

Object name prohibit use of MySQL reserved keyword

Naming to be done in the name, not more than 32 bytes

Temp table must have TMP prefix date suffix

Backup library table bak prefix date suffix

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

2 Database Basic Design code

All tables must be using the InnoDB engine

Database and table character sets agree to use UTF8 (full storage Chinese can use GBK or gbk2312)

All tables and fields are annotated with comment

Maintenance of data dictionaries from the outset

Try to control the size of the single-table data volume recommendation 5 million or less

Use MySQL partition table sparingly

As far as possible cold and hot data separation reduce table width

Prohibit establishment of reserved fields in tables

Prevents binary data such as picture files from being stored in the database

Stress testing on online database is forbidden

Prohibit connecting to a production environment database from a development environment test environment

3 Index Design Specification

Limit the number of indexes per table, we recommend that you index no more than 5 sheets

Each InnoDB table must have a primary key that does not apply frequently updated column Primary key does not apply multiple left primary key

Common Index Recommendations: columns in the WHERE clause in the SELECT Update DELETE statement

Fields included in order by, GROUP by distinct

association columns for multiple table joins

How to select the Order of indexed columns:

The highest-differentiated column is placed on the leftmost side of the federated Index (as far as possible, the primary key is selected)

Columns with small fields are placed on the leftmost side of the federated Index

The most frequently used columns are placed to the left of the Federated index

FOREIGN KEY constraints are not recommended, but must be indexed on the association keys between tables and tables

4 database Field Design specifications

Limited selection meets the minimum data type required for storage,

For example, the numeric type of string conversion storage

Non-negative data is stored with unsigned shaping (unsigned int)

Avoid using the text blob enum type

Try to define the column as NOT NULL

Storing time with a large datetime or timestamp type

Financial-related amount class data must use the decimal type

5 Database SQL Development specification

Using precompiled statements for database operations is recommended

Avoid using double-percent query conditions like%w%

Future extensions should be considered

Program Connection

Disable the use of SELECT *

Avoid using subqueries to convert to join operations

Avoid using join to associate too many tables suggest not less than 5

Reduce the number of interactions with the database

Obviously there is no duplicate value, and union all is used instead of union.

Split complex large SQL into multiple small SQL

6 Database Operation Behavior Specification

Over 1 million lines of bulk operations to be done in batches several times

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

Prohibit the use of the program to give super permissions, follow the principle of least privilege, no drop permissions

High-performance extensible MySQL Database design and Architecture Optimization e-commerce project _1