Mysql database design specification


Database Design Specification

Database Naming Specification

Standard Remarks
Unified use of lowercase letters and underlining for database object names eg:mkt_tool
Database object name prohibits the use of MySQL to retain keywords Eg: password, from, etc.
The name of the database object should be defined by its name and not exceed 32 characters at most. Be as concise and clear as possible
Temporary tables are prefixed with TMP and suffixed with date eg:mp_user_20180921
Backup tables are prefixed with Bak and suffixed with date eg:bak_user_20180921
All column names and column types that store the same data must be consistent The uid data types of tables eg: A and B are identical to column types

Basic Design Specification for Database

Standard Remarks
All tables use InnoDB as storage engine After 5.6, as the default engine, it supports transactions, row-level locks, better recovery, and better performance under high concurrency.
Unified use of UTF8 for character sets of databases and tables The main reason is to avoid garbled code generated by character set conversion. Secondly, utf8 contains more characters, and a Chinese character occupies 3 bytes.
Data tables and fields need to be annotated Easy follow-up maintenance
Single meter data quantity is controlled within 5 million The size limit of data stored in a single table depends on storage settings and file systems.
Separate hot and cold data as far as possible and reduce the width of the table Avoid querying useless data
Forbid reserving fields in tables and storing binary data such as pictures and files in Databases Increase on Demand to Avoid Rapid Growth of Table Data
Do not do database stress testing online
Prohibit development environment to connect directly to production environment database Avoid data clutter

Database Index Specification

Standard Remarks
Limit the number of indices per table to no more than 5 indices per table Index is not the more the better. It will increase query efficiency and reduce update efficiency at the same time.
Each InnoDB table must have a primary key Do not use frequently updated keys, do not use hash, string, MD5 as the primary key, preferring to select automatically growing columns as the primary key
How to Choose the Order of Joint Index The most differentiated column is on the left side of the index; the least field length is on the left side; and the most frequently used column is on the left side.
Avoiding redundancy and duplicate column indexes increases the time for the optimizer to generate query plans For redundant index, it will not improve the performance of index, but affect the generation of query plan.
Overlay index is preferred for frequent queries Avoid Innodb’s secondary search for indexes
Avoid using foreign keys as much as possible Foreign keys are used to ensure referential integrity. It is recommended that foreign key constraints be implemented on the business side, because each write operation needs to detect foreign key constraints to reduce performance.

Database field specification

Standard Remarks
Prioritize the smallest type of data needed for storage The int type has smaller storage space than the string type, preferring unsigned integer storage
Avoid using text, bolb, enum types When using text type, note that you can only use prefix index, not default value.
Define as much as possible as NOT NULL Indexing NULL requires extra space to save, and special processing of NULL is required for comparison and computation.
Avoid using strings to store date-type data and use TIMESTAMP or DATETIME to store 1. Cannot use date function to compare and calculate; 2. More space is needed for string storage.
Storing amount class data using decimal type 1. Decimal does not lose accuracy in calculation, and the space occupied is determined by the defined width; 2. It can be used to store data larger than bigint.

Database SQL Development Specification

Standard Remarks
Using precompilation for database operations First, parse once and reuse execution plan many times; second, avoid SQL injection brought by dynamic SQL;
Avoid implicit conversion of data types Implicit conversion leads to index failure eg:select * from test where id = '1'
Make full use of existing indexes 1. Avoid double-percent queries and replace them with later-percent queries. 2. An SQL can only use a column in a composite index for range queries. 3. Use left join or not exists to optimize not in operations.
Do not use select * queries 1. Consumption of more CPU and IO resources; 2. Cannot use overlay index;
Prohibit the use of INSERT statements without field lists Specify the fields of insert explicitly
Avoiding subqueries, you can optimize subqueries to join operations 1. The result set of the sub-query can not be used in the index; 2. The sub-query generates temporary table operation, and the amount of data will affect the efficiency; 3. The generated temporary table consumes too much CPU and IO.
Avoid using JOIN to associate too many tables 1. Each join table will occupy a part of memory (join_buffer_size); 2. Temporary table operations will occur, which will affect query efficiency; 3. MySQL allows Association of 61 tables, up to five.
Reduce the number of interactions with the database 1. The database is suitable for batch processing, avoiding multiple requests for the database; 2. Merge multiple identical operations together, such as modifying multiple fields at a time.
Use in instead of on If the value of in is not more than 500, in can effectively utilize the index.
Prohibit order by Rand () sort Order by Rand () loads all eligible data into memory for sorting
Where clause forbids functional transformation and calculation of columns Functional transformation of columns will result in the inability to use index eg:where date(time) = '20180123'
Use UNION ALL without duplicate values UNION will place data in temporary tables for de-duplication
Splitting complex large SQL into multiple small ones MySQL can only use one CPU to compute a single SQL. After splitting the SQL, the efficiency can be improved by parallel execution.

Behavior Specification for Database Operations

Standard Remarks
More than 1 million rows of data batch write operations, batch operations need to be repeated 1. Writing in large quantities can easily cause master-slave delay 2, generate a large number of logs 3, and avoid large transaction operations.
Structural modification of large tables It is easy to lock the table by directly modifying the large table. Use Pt online schema change to modify the table structure.
Do not grant root privileges to accounts used by programs and follow the principle of minimum privileges The account used by the program is not allowed to have drop permission. The account used by the program can only connect to one database.