Code for design of database development

Naming conventions

1. Library name, table name, field name with 26 English letters and 0-9 of these 10 numbers, plus the underscore ' _ ' composition, can not appear other characters (except annotations).
2. The library name, table name, field name are forbidden for more than 30 characters.
3. Library name, table name, field name prohibit use of MySQL reserved word
4. The names of the data objects and variables are in English characters and are forbidden to be named in Chinese.
5. The temporary library, the table name must be prefixed with TMP, and the date suffix. such as tmp_tablea_20151130
6. The backup library, the table must be prefixed with BAK, and the date suffix. such as bak_databasename_20151130

Basic specifications

1. The MySQL5.6 version recommends using the InnoDB storage engine
2. The table character set uses UTF8, and the column that supports emoji emoticons is set to UTF8MB4
3. All tables and columns need to add comments in principle
4. Single-table data volume recommended control within 1 million -1000w or less than 10G
5. Do not store pictures, files in the database.
6. It is forbidden to do the database stress test on-line, if there is special need, must be reported in advance.
7. Prohibit direct client operation test, production database

Field Design Specification:

1. Use the text, blob type as much as possible. Store the Text,blob field separately after splitting it if necessary.
2. float and double are not recommended, and it is recommended to use decimal or bigint instead.
3. It is not recommended to use the enum type, consider tinyint instead.
4. The default null is forbidden in principle, and it is recommended that not NULL be set defaults.
5. If you do not store negative numbers, we recommend using unsigned to store non-negative integers
6. Storage time using bigint
7. Using the int unsigned storage IPV4
8. InnoDB engine takes precedence over the use of varbinary to store case-sensitive variable-length strings
9. Prohibit storing plaintext passwords in the database

Index specification

1. The number of indexes in a single table does not exceed 5
2. The number of fields in a single composite index does not exceed 5
3. Use a prefix index on a string that has a prefix index of no more than 8 characters.
4. It is recommended that you prioritize the prefix index and, if necessary, add pseudo-columns and build indexes.
5. The InnoDB table must have a primary key, even if the primary key is meaningless.
6. Do not use columns that are frequently updated as indexes.
7. Important SQL that is frequently executed must be indexed
8. The Where Condition column of the UPDATE, DELETE statement, ORDER by, GROUP by, distinct fields, and the fields of the multi-table join suggest setting the index
9. The most selected field is placed in front
10. Core SQL overrides the overriding index
11. Avoid redundant and duplicate indexes

Index Taboo

1. Do not index on low-selectivity columns, such as "gender", "status", "type"
2. Do not perform mathematical operations and function operations on indexed columns
3. Try not to use foreign keys
4. Do not use a leading query, like "%ab" as "%ab%"

For statements that use%ab%, one is to rewrite the SQL, and the second is to read the memory first through Redis and then in the read operation (the first reading is slow), and the third is solved by Elasticsearch. According to the business to take a moderate solution.

SQL design

1. Require all SQL to submit tests, use explain to view execution plans, avoid full table scans, avoid generating temporary tables
2. Use precompiled statements. Efficient and relatively safe.
3. Prohibit comparison of different types of fields to avoid implicit conversions
4. Try not to use stored procedures, triggers, UDFs, events, etc.
5. Avoid joins with large tables
6. Avoid mathematical operations in the database, as far as possible to do processing at the terminal, to the database.
7. Try to split large SQL into small SQL
8. Prevent single SQL statements from updating multiple tables at the same time
9. Disable SELECT *
10. The Insert statement must display the specified field name

Operating Specifications:

1. Batch import and export data must be notified to DBA for assistance in advance.
2. Prevents background management and statistical class queries from being performed on-line from the library. Try to keep the backup library in action.
3. Minimize the privilege principle. Only the necessary permissions are assigned to the app. Delete permission needs to be assigned by table.
4. Try not to store business logic in the MySQL database.
5. Database project selection and design of major projects notify DBA in advance.
6. Large promotions or new features on-line must be notified to the DBA in advance for evaluation.
7. For particularly important library tables, communicate with DBAs in advance to prioritize maintenance and backup.
8. Avoid bulk updates and querying databases during peak business hours.
9. All relevant SQL statements must be noted in detail on the online data changes.

Online data changes: To create a task and mail it to the DBA.

Precautions:

1. Applications that need to build the library need to provide the IP of the app to authorize the database user.

2. mysql on-line environment involves database DDL (Create,alter,drop,declare) operations that are audited by the DBA.

Code for design of database development