|
Good database specifications help reduce the complexity of software implementation and reduce communication costs. This iron rule mainly covers the processing conventions in building databases and tables, creating indexes, writing SQL, ORM mapping, etc. 1. The Iron Law of Database Construction | - | Iron Rule | Level | Remark |
|---|
| Character Set | Use utf-8. If you are storing expressions, use utf8mb4 for storage. | Enforcement | | | Sorting rules | Using utf8_general_ci | Enforcement | |
2. The iron rule of table creation | - | Iron Rule | Level | Remark |
|---|
| Notes | Field annotations are a must. | Enforcement | | | coding | Use utf-8. If you are storing expressions, use utf8mb4 for storage. | Enforcement | | | Whether the concept field | Must be named is_xx, data type is unsigned tinyint (1 for yes, 0 for no), for example is_deleted (1 for deleted, 0 for not deleted). | Enforcement | Any field must be unsigned if it is non-negative. | | Table name, field name | Only lowercase letters, underscores, or numbers can be used. The name cannot start with an underscore or a number. It cannot have only numbers between two underscores. Reserved words are prohibited. Table names cannot use plural nouns. | Enforcement | | | Naming of database and table names | The library name should be consistent with the application name as much as possible, and the table name should be named with the business name_table function. | Enforcement | | | Index naming | The primary key index uses pk_field name; the unique index uses uk_field name; the common index uses idx_field name. | Enforcement | pk_ is the primary key; uk_ is the unique key; idx_ is the index | | Decimal Type | The data type is decimal. Float and double are prohibited. Float and double have precision loss. If the stored data range exceeds the range of decimal, it is recommended to split the data into integers and decimals and store them separately. | Enforcement | | | varchar type | varchar is a variable-length string that does not pre-allocate storage space and should not exceed 5,000 characters in length. If the length is greater than 5,000, use text (create a separate table and use the primary key to correspond to it to avoid affecting the index efficiency of other fields). | Enforcement | | | Table name must have three fields | id (data type is unsigned bigint, single table increment, step size is 1), gmt_create, gmt_modified (active creation time, passive update time, data type is datetime). | Enforcement | | | Field Redundancy | Fields are allowed to be appropriately redundant, but data consistency must be considered. Redundant fields should 1) not be frequently modified; 2) not be varchar ultra-long fields, let alone text fields. | recommend | | | Sub-library and sub-table | It is recommended to shard the database and tables only when the number of rows in a single table exceeds 5 million or the capacity of a single table exceeds 2GB. | recommend | |
Setting an appropriate character storage length can not only save database table space and index storage, but more importantly, it can improve retrieval speed. 3. The Iron Rule of Indexing | - | Iron Rule | Level | Remark |
|---|
| Unique Index | Fields that are unique in business terms must be indexed uniquely, even if they are a combination of multiple fields. Although the unique index affects the insert speed, this loss can be ignored, but it significantly improves the query speed. In addition, even if very complete verification control is done at the application layer, as long as there is no unique index, dirty data will inevitably be generated according to Murphy's Law. | Enforcement | | | join | It is forbidden to join more than three tables. The data types of the fields to be joined must be consistent. When querying multiple tables, ensure that the associated fields have indexes. Even when joining two tables, pay attention to table indexes and SQL performance. | Enforcement | | | Creating an index on a varchar field | The index length must be specified. It is not necessary to index the entire field. The index length can be determined based on the actual text differentiation. Index length and discrimination are contradictory. Generally, for string data, the discrimination of an index with a length of 20 will be as high as 90% or more. You can use count(distinct left(column name, index length))/count(*) to determine the discrimination. | Enforcement | | | Page search prohibits fuzziness | Page search cannot be left blurred or fully blurred. If necessary, please use the search engine to solve it. Prohibition reason: The index file has the leftmost prefix matching feature of B-Tree. If the value on the left is not determined, this index cannot be used. | Enforcement | | | order by | If there is an order by scenario, please pay attention to the order of the index. The last field in order by is part of the composite index and is placed at the end of the index composite order to avoid file_sort situations that affect query performance. Positive example: where a=? and b=? order by c; the index should be created as a_b_c; Negative example: if there is a range search in the index, the index order cannot be used, such as where a>10 order by b; the index a_b cannot be sorted. | recommend | |
4. The iron rule of writing SQL | - | Iron Rule | Level | Remark |
|---|
| count(*) | Do not use count(column name) or count(constant) instead of count(*). Count(*) is the standard syntax for counting rows defined in SQL92. It has nothing to do with the database and has nothing to do with NULL or non-NULL. count(*) will count the rows whose values are NULL, while count(column name) will not count the rows whose values are NULL. | Enforcement | | | count(distinct col) | Count the number of unique rows in this column excluding NULL. Note that count(distinct col1, col2) returns 0 if one of the columns is all NULL, even if the other column has distinct values. | Enforcement | | | sum(col) | When all values in a column are NULL, count(col) returns 0, but sum(col) returns NULL. Therefore, you need to be careful about NPE when using sum(). You can avoid NPE problem by following the method: select if(isnull(sum(g)), 0, sum(g)) from table; | Enforcement | | | isnull | Use isnull() to check whether it is a NULL value. NULL compares to any value as NULL. | Enforcement | | | Paging query logic | If count is 0, it should return directly to avoid executing the subsequent paging statements. | Enforcement | | | Foreign keys and cascading | Foreign keys and cascades are prohibited, and all foreign key concepts must be resolved at the application layer. Reason: Foreign keys and cascades are not suitable for distributed, high-concurrency clusters. Cascade updates are strongly blocked and there is a risk of database update storms. Foreign keys affect the insertion speed of the database. | Enforcement | | | Stored Procedures | The use of stored procedures is prohibited. Stored procedures are difficult to debug and expand, and are not portable. | Enforcement | | | Data Correction | When correcting data (especially deleting or modifying records), you must select first to avoid accidental deletion, and only execute the update statement after confirming that everything is correct. | Enforcement | | | in | Avoid the in operation if possible. If it cannot be avoided, the number of elements in the collection after in should be controlled within 1000. | recommend | | | truncate table | Do not use truncate table. Truncate table is faster than delete and uses fewer system and log resources. However, truncate has no transaction and does not trigger a trigger, which may cause an accident. Therefore, do not use this statement in development code. | refer to | |
5. Iron Law of ORM Mapping | - | Iron Rule | Level | Remark |
|---|
| Table Query | It is forbidden to use * as the field list for the query. The required fields must be clear. | Enforcement | | | POJO | The Boolean attribute of the POJO class cannot be added with is, but the database field must be added with is, which requires mapping between fields and attributes in resultMap. | Enforcement | | | Return Parameter | It is forbidden to use resultClass as a return parameter. Even if all class attribute names correspond one-to-one with database fields, they still need to be defined; conversely, each table must have an attribute corresponding to it. Reason: Configure the mapping relationship to couple the field with the DO class for easy maintenance. | Enforcement | | | Return Parameter | It is forbidden to directly use HashMap or HashTable as the output of query result set. Cause: The type of the attribute value is uncontrollable. | Enforcement | | | sql.xml configuration parameters | Use #{}, #param# for sql.xml configuration parameters. Do not use ${}, as ${} is prone to SQL injection. | Enforcement | | | queryForList | It is forbidden to use the queryForList(String statementName, int start, int size) provided by Mybatis. Reason: The implementation method is to obtain all records of the SQL statement corresponding to statementName in the database, and then obtain the sub-collection of start and size through subList. | Enforcement | | | Update time | When updating a database table record, the modification time of the record must be updated at the same time. | Enforcement | | | Update database table records | Don't write a large and comprehensive data update interface (pass in a POJO class). When executing SQL, do not update unchanged fields. Reasons: it is prone to errors, inefficient, and increases binlog storage. | recommend | | | @Transactional | Don't abuse @Transactional transactions. Transactions affect the QPS of the database. In addition, where transactions are used, various rollback solutions need to be considered, including cache rollback, search engine rollback, message compensation, statistical correction, etc. | refer to | | | Mybatis dynamic sql tags | The compareValue in < isEqual> is a constant that is compared with the attribute value, usually a number, which means that the corresponding SQL statement is executed when they are equal; < isNotEmpty> means that it is executed when it is not empty and not null; < isNotNull> means that it is executed when it is not null. | refer to | |
The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM. You may also be interested in:- Detailed explanation of MySQL database binlog cleanup command
- How to compare two database table structures in mysql
- Example of how to check the capacity of MySQL database table
- Method of using MySQL system database for performance load diagnosis
- mysql create database, add users, user authorization practical method
|
