Shulou(Shulou.com)05/31 Report--

This article is to share with you what the iron law of MySQL database has. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Good database specification helps to reduce the complexity of software implementation and communication costs. This iron law mainly covers the processing conventions of building database and table, indexing, writing SQL, ORM mapping and so on.

1. Iron law of building storehouse

-Iron Law Level remarks character set uses utf-8. If you store facial expressions, choose utf8mb4 to store them. Compulsion

Collation is enforced using utf8_general_ci

two。 Iron law of building table

-Iron Law Level remarks must have field comments. Compulsion

Utf-8 is used for encoding. If you store facial expressions, choose utf8mb4 to store them. Compulsion

Whether the concept of the field must be named with is_xx, the data type is unsigned tinyint (1 is 0 no) such as is_deleted (1 delete 0 is not deleted). Force any field if the non-negative number must be unsigned table name, field name can only use lowercase letters, underscores or numbers; prohibit underscores or the beginning of numbers; prohibit only numbers between two underscores; disable reserved words; table names prohibit the use of plural nouns. Compulsion

The database name of the library name and table name is consistent with the application name as far as possible, and the table name is best named with the function of the business name _ table. Compulsion

Index naming primary key reference pk_ field name; unique index reference uk_ field name; normal index reference idx_ field name. Force pk_, that is, primary key;uk_, that is, unique key;idx_, that is, index decimal data type, is decimal. If you prohibit the use of float, double,float and double, there is a loss of precision. 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. Compulsion

Varchar type varchar is a variable-length string with no pre-allocated storage space and a length of no more than 5000 characters. If the length is greater than 5000, apply text (separate a table and correspond with a primary key to avoid affecting the indexing efficiency of other fields). Compulsion

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). Compulsion

Field redundancy fields allow appropriate redundancy, but data consistency must be taken into account. Redundant fields should have 1) do not modify frequently; 2) are not varchar super-long fields, let alone text fields. Recommend

Sub-database sub-form table is recommended only when the number of rows of the sub-database sub-form table exceeds 5 million rows or the capacity of a single table exceeds 2GB. Recommend

Setting the appropriate character storage length can not only save the database table space and index storage, but also improve the retrieval speed.

3. Iron law of indexing

-Iron Law Level remarks unique index of fields with unique characteristics in business, even if it is a combination of multiple fields, a unique index must be built. Although the unique index affects the speed of insert, this loss can be ignored, but it obviously improves the query speed; in addition, even if there is a very perfect check control in the application layer, as long as there is no unique index, according to Murphy's law, dirty data must be generated. Compulsion

Join forbids join for more than three tables, and requires join fields with the same data type; when you associate queries with multiple tables, ensure that the associated fields need to be indexed; even if two tables are join, you should also pay attention to table index and SQL performance. Compulsion

To build an index on the varchar field, the index length must be specified, and there is no need to index the whole field. The index length can be determined according to the actual text differentiation. Index length and discrimination are a pair of contradictions. Generally, for string type data with a length of 20, the discrimination can be as high as 90%, which can be determined by the discrimination of count (distinct left (column name, index length)) / count (*). Compulsion

Page search forbids fuzzy page search forbids left fuzzy or full fuzzy, if necessary, please go to the search engine to solve the problem. Reason for prohibition: the index file has the leftmost prefix matching feature of B-Tree, and this index cannot be used if the value on the left is not determined. Compulsion

Order by if there is an order by scenario, please pay attention to the ordering of the index. The last field of order by is part of the composite index and is placed at the end of the index combination order to avoid the occurrence of file_sort and affect query performance. Positive example: where axiom? And baked? The order by c; index should be built as a where index; counterexample: if there is a range lookup in the index, then the index ordering cannot be utilized, for example, the index a > 10 order by b; index cannot be sorted. Recommend

4. Write the Iron Law of SQL

-Iron Law Level Note count (*) do not use count (column name) or count (constant) instead of count (*). Count (*) is the standard syntax for counting rows defined by SQL92, which has nothing to do with database, NULL and non-NULL. Count (*) counts rows with a value of NULL, while count (column name) does not count rows with this column NULL. Compulsion

Count (distinct col) calculates the number of non-repeating rows in the column except NULL. Note that count (distinct col1, col2), if one column is all NULL, returns 0 even if the other column has a different value. Compulsion

Sum (col) when all values of a column are NULL, the return result of count (col) is 0, but the return result of sum (col) is NULL, so you need to pay attention to the NPE problem when using sum (). NPE problems can be avoided in the following ways: select if (isnull (sum (g)), 0, sum (g)) from table; enforcement

Isnull uses isnull () to determine whether it is a NULL value. The comparison of NULL to any value is NULL. Compulsion

Paging query logic should be returned directly if count is 0, avoiding the execution of subsequent paging statements. Compulsion

Foreign keys and cascades prohibit the use of foreign keys and cascades, and all foreign key concepts must be solved in the application layer. Reason: foreign keys and cascading are not suitable for distributed and highly concurrent clusters, cascading updates are strongly blocked, there is a risk of database update storm, and foreign keys affect the insertion speed of the database. Compulsion

Stored procedures prohibit the use of stored procedures, which are difficult to debug and extend, let alone portability. Compulsion

Data correction data correction (especially delete, modify record operation) should first select, avoid erroneous deletion, confirm that it is correct before the update statement can be executed. Compulsion

If the inin operation can be avoided, if it cannot be avoided, the number of collection elements behind the in should be limited to 1000. Recommend

Truncate table forbids the use of truncate table,truncate table faster than delete and uses fewer system and log resources, but truncate has no transactions and does not trigger trigger, which may cause accidents, so do not use this statement in development code. Referenc

Iron law of 5.ORM mapping

-Iron Law Level remarks Table query forbids the use of * as the list of fields for the query, and which fields are required must be clear. Compulsion

The Boolean attribute of the POJOPOJO class cannot be added with is, but the database field must be added with is, which requires the mapping between fields and attributes in resultMap. Compulsion

The return parameter forbids using resultClass as the return parameter, even if all class property names correspond to database fields one by one, it needs 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. Compulsion

The return parameters prohibit the direct use of HashMap and HashTable as the output of the query result set. Reason: the type of property value is not controllable. Compulsion

Sql.xml configuration parameters sql.xml configuration parameters use # {}, # param#, do not use ${}, ${} is prone to SQL injection. Compulsion

QueryForList prohibits the use of queryForList (String statementName, int start, int size) that comes with Mybatis. Reason: it is realized by fetching all the records of the SQL statement corresponding to statementName in the database, and then fetching the subset of start and size through subList. Compulsion

Update time when updating a database table record, the corresponding modification time of the record must be updated at the same time. Compulsion

Update database table records do not write a large and comprehensive data update interface (passed in to the POJO class). When performing SQL, do not update unchanged fields for reasons such as error prone, inefficient, and increased binlog storage. Recommend

Do not abuse @ Transactional@Transactional transactions. Transactions affect the QPS of the database. In addition, where transactions are used, various rollback schemes need to be considered, including cache rollback, search engine rollback, message compensation, statistical correction and so on. Referenc

Mybatis dynamic sql tags

The compareValue in is a constant compared to an attribute value, usually a number, indicating that the corresponding SQL statement is executed when equal

Executes when it is not empty and is not null

Executes when it is not null. Referenc

Thank you for reading! This is the end of this article on "what are the iron rules of MySQL database?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.