01_ Database Design Specification

"Database Design Specification"

Database Naming conventions

Database Basic Design Code

Database Index Design Specification

database field Design Specification

SQL Development Specification

Database operation specification

"1. Database naming Specification"

1. All database object names must be separated by lowercase letters and underlined with underscores

MySQL is case-sensitive, and if the design is case-insensitive, the following messy situation may occur

Different database names DbName DbName

Different table names tables table table

2. All database object names prohibit the use of MySQL reserved key I Word

3. Name the database object to be known, and preferably not more than 32 characters

4. Temporary library, table must be prefixed with TMP and date suffix

5. Backup library, table must be prefixed with bak and date suffix

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

"2. Basic Database Design Code"

1.mysql5.5 using the previous MyISAM (default storage engine), it is recommended to use the InnoDB storage engine.

InnoDB is the default storage engine after 5.6, which supports transactions, row-level locks, better recoverability, and better performance under high concurrency.

2. Data and table character sets recommended uniform use of UTF-8

The uniform character set avoids garbled characters due to character set conversions.

Note: The UTF-8 character set kanji in MySQL accounts for 3 bytes, and the ASCII code occupies 1 bytes.

3. Add comments to all tables and fields

Use comment clauses to add notes for tables and columns.

Maintenance of the data dictionary from the outset.

4. Try to control the size of the single-table data volume, the proposed control within 5 million

5 million is not a limitation of MySQL data (there is a big problem with modifying table structure, backup, and recovery)

How much data can mysql store? -This limitation depends on the storage device and the file system.

If the sense of single-table data is too large, you can use historical archiving, sub-database and other means to control the size of the data volume.

5. Use MySQL partition table sparingly

A partitioned table is physically represented as multiple files, logically represented as a table

Use the partition key sparingly, and cross-partition queries may be less efficient.

It is recommended to use physical tables to manage big data.

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

Reduce disk IO to ensure memory cache hit rate for hot data

Use caching more efficiently to avoid reading useless cold data

Place the columns that you frequently use together in a table.

7. Prohibit the creation of reservation fields in a table

Naming of reserved fields is difficult to recognize

Reserved field cannot confirm the stored data type, so the appropriate type cannot be selected

The table is locked for modifications to the reserved field

8. Prohibit storing binary data such as pictures, files, etc. in the database

9. Prohibit online database stress test

10. Prohibit direct connection of production environment database from development environment and test environment

"3. Index design Specification"

1. Limit the number of indexes on each table and suggest that the index of a single table should not exceed 5

Index is not the more the better, index can improve query efficiency, but also reduce the efficiency of INSERT, UPDATE, query

It is forbidden to establish a separate index for each column in the table.

2.INNODB organizes tables in the order of primary key indexes, so each InnoDB table must have a primary key.

Do not use columns that are frequently updated as primary keys, and do not use multi-column primary keys.

Do not use UUID, MD5, hash, string column as primary key.

The primary key recommends using the self-increment ID value.

3. Common Index Column recommendations

Columns in the WHERE clause of a SELECT, UPDATE, DELETE statement

Fields included in order by, GROUP by, distinct

association columns for multiple table joins

4. How to select the Order of indexed columns

The highest-differentiated column is placed on the leftmost side of the federated Index

Try to place columns with small field lengths at the far left of the Federated index

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

5. Avoid creating redundant indexes and repeating indexes

6. Use an overlay index for frequent query prioritization (that is, include all indexes that require a query field)

Two lookups to avoid indexing InnoDB tables

Avoids random io into sequential io to speed up query efficiency

7. Avoid using foreign keys as much as possible

FOREIGN KEY constraints are not recommended, but it is important to index the association keys between tables.

Foreign keys can be used to guarantee referential integrity of data, but it is recommended to be implemented on the business side.

Foreign keys can affect write operations on parent and child tables to reduce performance.

"4. Database field Design Specification"

1. Prioritize the smallest data types that meet your storage needs.

Converts a string into a storage of numeric types.

Inet_aton (' 255.255.255.255 ') = 4294967295

Inet_ntoa (4294967295) = ' 255.255.255.255 '

For non-negative data, it is preferable to use an unsigned integer to store it.

N in VARCHAR (n) represents the number of characters, not the number of bytes

Use UTF8 to store Chinese characters varchar (255) = 765 bytes

2. Avoid using text, BLOB data types

If you must use it, it is recommended that you separate the text and BLOB columns into separate extension tables

Note: The text or blob type can only use the prefix index

3. Avoid using enum data types

Modifying an enum value requires the use of the ALTER statement

An enum type's order by operation is inefficient and requires additional action

Suppress the use of numeric values as enumerations for enum values

4. Define all columns as not NULL whenever possible

The index null column requires extra space to save, so it takes more space.

Special handling of NULL values is performed when comparing and calculating.

5. Store date type data do not use a string type, to use the timestamp or datetime type.

Question of storing dates using string types: The date function cannot be used for calculations and comparisons, and the string storage date takes up more space.

TIMESTAMP Range: 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

TIMESTAMP occupies 4 bytes and is the same as int, but is more readable than int.

Using datetime types that exceed the range of timestamp values

6. Financial-related amount data, must use decimal type

The decimal type is a precision floating point number, and the precision is not lost when calculated.

Occupy space is determined by the width defined

can be used to store integer data that is larger than bigint.

"5. Database SQL Development Specification"

1. It is recommended to use pre-compiled statements for database operations.

Only arguments are more efficient than passing SQL statements.

The same statement can be resolved once, use multiple times, and improve processing efficiency.

2. Avoid implicit conversions of data types

Implicit conversions Cause index invalidation

SELECT * from user where id = ' 111 ' #实际id是Long类型

3. Make the most of the indexes that already exist on the table.

Avoid using double-percent query conditions. such as: Like '%123% '

A SQL can only take advantage of a column in a composite index for scope queries

Use LEFT JOIN or not exists to optimize not in operation

4. When designing a database, you should consider future extensions.

5. Program connection different database use different accounts, prohibit cross-Library query.

Leave room for database migrations and sub-tables.

Reduce the business coupling degree.

Avoid security risks caused by excessive permissions.

6. Disable SELECT *, you should use Select < Field list > to query.

7. Prohibit the use of INSERT statements that do not contain a field list

8. Avoid using subqueries as much as possible, and you can optimize the query to join operations.

The result set of a subquery cannot use the index

A subquery produces a temporary table operation if the subquery data volume has a significant impact on performance.

Consumes too much CPU and IO resources.

9. Also avoid using joins to correlate too many tables. (The associated tables are less acceptable.)

One table per join consumes a portion of memory (join_buffer_size)

Generates temporary table operations that affect query efficiency

MySQL allows a maximum of 61 tables, with no more than 5 recommendations

10. Reduce the number of interactions in the database

Database is more suitable for reasonable batch operation

Combine multiple identical operations together to improve processing efficiency

11. Use in instead of or

In with no more than 500 values

In operations can effectively use the index

12. Do not use ORDER by rand () for random sorting

In this way, the qualifying data is loaded into memory for sorting.

Consumes a lot of CPU, IO, and memory resources.

It is recommended to get a random value in the program and then fetch the data from the database.

function conversion and calculation of columns is forbidden in 13.WHERE clauses

function conversions or calculations on columns can result in an unusable index

　　

14. Use UNION ALL instead of union when there is clearly no duplicate value

The Union will put all the data into the temporary table and then go back to the operation.

UNION all no longer does a redo of the result set.

15. Split complex large SQL into multiple small SQL

MySQL a SQL can only be computed using one CPU.

SQL splits can be performed in parallel to improve processing efficiency.

"Database Operations Behavior Specification"

1. More than 1 million lines of bulk write operations, in batches to operate multiple times.

Large volumes of write operations can cause severe master-slave delays.

A large number of logs are generated when the Binlog log is in row format.

Avoid generating large transaction operations.

2. Changes to the large table data structure must be cautious, causing serious lock-table operations, especially in the production environment, is intolerable.

For large tables, use Pt-online-shcema-change to modify the table structure.

3. Prohibit granting super privileges to the account used by the program.

4. For program connection database account, follow the principle of least privilege.

The program uses a database account to use only one db, not cross-library.

The account used by the program cannot have the drop permission in principle.

01_ Database Design Specification