The process specification
- Before the service is officially launched, make sure that the database changes are online. Otherwise it will cause an online accident!
- The test environment also needs to be there, otherwise the test environment will be more and more difficult to build behind.
The design specification
Specifications are divided into three levels: [Mandatory], [Recommendation] and [reference], with priority from high to low. If there is a violation of [mandatory] specifications, please call back.
Naming conventions
[Mandatory] The best name of the table is “business name _ table’s purpose”, no additional database name starting.
[Mandatory] The table name and field name must contain lowercase letters or **** digits. Do not start with a digit or contain only digits between two underscores (_). Positive example :app_user, level3_name Negative example :AppUser, rdcConfig, level_3_NAME
[Mandatory] The table name must be singular. Note: The table name should only represent the contents of the table, not the number of entities.
[Mandatory] The prefix of the index name must be IDx_, and the prefix of the unique constraint/index must be uk_
[Mandatory] All names must use full names, except those with default conventions. If more than 30 characters are used, abbreviations are used
[Mandatory] Each field and table must provide a clear comment, RDS now does not allow table building without comment.
【 Recommended 】 The best name of the library is system name_subsystem name.
Field specification
[Mandatory] Mandatory three fields
- Id (unsigned Bigint), self-incremented for a single table. Prevent id Generator generation during enumeration
- Create_time (timestamp), used to record the active creation time. Default value: current_timestamp
- Update_time (timestamp), used to record the last update time. The default value is current_timestamp
[Mandatory] Each table contains a maximum of 30 fields
[Mandatory] If the stored string is of a fixed length, char is used as a fixed-length string
[Mandatory] VARCHAR should be used as far as possible to store text data with variable length to save storage space. The length should not exceed 2700. If the storage length is greater than this value, set the field type to TEXT, create an independent table, and use the primary key to correspond to it to avoid affecting the index efficiency of other fields.
[Mandatory] If the value of a storage field is non-negative, the value is signed by default.
[Mandatory] Decimal type is decimal; float and double are prohibited. There is no precision loss for Decimal, and the data type Decimal (P, S) needs to specify the largest number of decimal places (p) and the number of decimal places (s), respectively.
[Mandatory] If the meaning of a field is changed or the status of a field is appended, the field comment must be updated in a timely manner.
[Mandatory] TINYINT is recommended for fields such as ** status and ** type. Enumerations start at 1 and write the meaning in the field remarks. Disallow using bool to store state.
[Mandatory] Do not set the index field to NULL
[Mandatory] The utF8MB4 character set is preferred, which is the most compatible and supports emoji characters.
[Recommendation] Json data is stored as JSON instead of text. MySQL will do the JSON validation for us. Note the length limit. (See business scenario)
[Recommended] Set the field to NOT NULL and provide the default value for the field. For example, the default value of the character type is an empty string value; The default value of numeric type is 0; The default value for logical type is 0;
[Recommendation] Select soft and hard delete based on service scenarios. If necessary, add delete_AT to soft delete.
ToDo: Repair GORM and Build Tools @Liu Siqi @Liu Tao
Note:
-
Count (*) will count rows that are NULL, and count(column name) will not count rows that are NULL
-
Count (DISTINCT COL) Count the number of distinct rows in this column except NULL. Note that count(DISTINCT COL 1, COL2) Returns 0 even if the other column has different values if all of the columns are NULL.
[Recommendation] Remove BLOB and TEXT fields and associate them with primary keys. If the value exceeds 4k, consider changing the storage solution and risk of master/slave delay.
[Recommendation] Anti-paradigm design: Fields allow for appropriate redundancy to improve query performance. For example, unique keys such as application name and application ID in the APP table can be stored in other service tables, avoiding the use of Join.
[Recommended] The time type istimestampData type becausedatetimeIt takes 8 bytes,timestampIt takes only 4 bytes and ranges fromThe 1970-01-01 00:00:01 到2038-01-01 00:00:00
Appropriate character storage length can not only save database table space, save index storage, but also improve the speed of retrieval.
| Type (synonym) | Storage Length (BYTES) | The minimum value (SIGNED/UNSIGNED) | The maximum (SIGNED/UNSIGNED) |
|---|---|---|---|
| integer | |||
| TINYINT | 1 | 128/0 – | 127/255 |
| SMALLINT | 2 | 32768/0 – | 32767/65535 |
| MEDIUMINT | 3 | 8388608/0 – | 8388607/16777215 / |
| INT(INTEGER) | 4 | – 2,14,7483,648/0 | 2147483647/4294967295 / |
| BIGINT | 8 | 63/0-2 ^ | 1/2 ^ 2 ^ 63-64-1 |
| Time to type | |||
| DATETIME | 8 | 1001-01-01 00:00:00 | The 9999-12-31 23:59:59 |
| DATE | 3 | 1001-01-01 | 9999-12-31 |
| TIME | 3 | 00:00:00 | 23:59:59 |
| YEAR | 1 | 1001 | 9999 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00 | 2038-01-01 00:00:00 |
The index specification
[Mandatory] Write more read less The maximum number of indexes is 5. The maximum number of indexes in the table is 10.
[Mandatory] A unique index must be created for a field that has a unique feature, even if it is a combination of multiple fields.
[Mandatory] ** Do not use join. ** Maximum of two table joins, if necessary. The data types of the fields to be joined must be absolutely consistent. When multiple tables are used for associated query, ensure that the associated fields have indexes.
[Mandatory] When creating an index for a VARCHAR field, the index length must be specified. It is not necessary to create an index for all fields. The index length can be determined based on the actual text discrimination. Note: Index length and distinction is a pair of contradictory body, generally for string type data, the length of 20 index, the distinction will be as high as 90%.
[Mandatory] When creating an index, consider creating a joint index and put the most distinguished field first.
[Mandatory] Only XXX % is allowed in fuzzy query. If necessary, go to ES to solve the problem.
[Mandatory] Do not use foreign keys. All foreign key concepts are handled at the application layer.
[Recommendation] Use overwrite indexes to perform query operations to avoid returning to the table. Select b from tableA where a = 1
[Recommendation] If the selectivity is greater than 20%(the distinction is less than 20%), then full table scan performs better than using indexes, that is, there is no need to set indexes.
[Recommendation] Prevent index invalidation due to implicit conversion caused by different field types.
If there is an order by scenario, please take advantage of the orderality of the index. The last field of order BY is part of the composite index and placed at the end of the index combination order to avoid file_sort, which affects query performance.
Do not index fields that are frequently updated
Avoid the following extreme misunderstandings when creating indexes
-
Mistake 1: Better too many than too few. Consider that a query requires an index.
-
Mistake no. 2: Better than all. Indexes are considered to consume space and significantly slow down updates and additions.
-
Mistake 3: Resist unique indexes. It is believed that business uniqueness needs to be resolved by “search and plug later” at the application layer.
Use standard
[Mandatory] Use Explain to do SQL performance optimization, type must be at least range level, requirement is ref level, if can consts is best
| value | describe |
|---|---|
| system | The table has only one row (= system table). This is a special case of the const join type. |
| const | The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once. |
| eq_ref | One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index. |
| ref | All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type. |
| fulltext | The join is performed using a FULLTEXT index. |
| ref_or_null | This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table: |
| index_merge | This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used |
| unique_subquery | This type replaces eq_ref for some IN subqueries of the following form: |
| index_subquery | This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form |
| range | Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type. |
| index | The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways: – If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data. -A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column. -MySQL can use this join type when the query uses only columns that are part of a single index. |
| ALL | A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables. |
【 Mandatory 】 Do not use count(column name) or count(constant) instead of count(). Count () is a standard row count syntax defined in SQL92.
- Count (*) counts rows with a value of NULL, while count(column name) does not count rows with a value of NULL.
[Mandatory] In the case of query involving tables, ensure that the data types of the associated conditions are the same to avoid nesting
[mandatory] use ISNULL() to check whether the value ISNULL. Note: A direct comparison of NULL to any value is NULL.
-
NULL<>NULL returns NULL, not false.
-
NULL=NULL returns NULL, not true.
-
NULL<>1 returns NULL, not true.
[Mandatory] Do not perform calculations on index columns or use functions when querying. Avoid type conversions for indexed columns and string encoding conversions.
[Mandatory] Update data with more than 1000 update, DELETE, and INSERT statements, requiring multiple batch updates
[Mandatory] Disable advanced features such as stored procedures, views, triggers, and events
No more than 500 values in the in clause
【 Mandatory 】 When the RDS DML work order is lifted, if the length of update or delete is determined, add limit < number of rows >. In this way, even if the work order DML SQL is written wrong, the impact caused by the error is controllable. (Reduce the risk of accidents caused by work orders)
[Mandatory] Write a background note when making a work order.
[Recommendation] It is not recommended to use the subquery, it is recommended to separate the subquery SQL and combine the program for multiple queries.
Do not use negative queries for index fields, such as not in,not like