Concern public number: IT elder brother, read a dry goods technical article every day, a year later you will find a different self

  • Database object naming conventions

  • Database object

  • Global naming conventions for database objects

  • Database naming conventions

  • Table naming conventions

  • Field naming conventions

  • Index naming convention

  • View naming conventions

  • Stored procedure naming conventions

  • Function naming conventions

  • Trigger naming convention

  • Constraint naming convention

  • User naming conventions

  • Specification for database object design

  • Storage engine selection

  • Character set selection

  • Specification for table design

  • Field design specification

  • Index design specification

  • Constraint design specification

  • SQL Usage Specification

  • The normalization of select retrieval

  • Standardization of operation

  • Procedural constraints

Database object naming conventions

Database object

A database object is a component of a database. Common objects are as follows: Table, Index, View, Diagram, Default, Rule, Trigger, Stored Procedure, User, etc. Naming conventions refer to naming conventions for database objects such as SCHEMA, TABLE, INDEX, CONSTRAINTS, and so on.

Global naming conventions for database objects

1, the use of meaningful English words, words in the middle of the following underlined space

2. The name must start with letters, digits, and underscores (_)

3, avoid using MySQL reserved words such as backup, call, group, etc

4, all database objects use lowercase letters, in fact, MySQL is case-sensitive can be set, in order to ensure uniformity, we standardize all lowercase representation.

Database naming conventions

1. The database name should contain no more than 30 characters.

2, the database name is usually the project name + abbreviation of the meaning of the library, such as IM project workflow database, can be IM_flow.

The default character set and collation rule clause must be added when the database is created. The default character set is UTF8 (dumbo uses UTf8MB4).

4. Use lowercase names.

Table naming conventions

T stands for table. The naming rule is T + module (short for module) + table (short for table). For example, the education information table of user module: T_user_eduinfo.

Temp prefix + module + table + date suffix: temp_user_eduinfo_20210719 temporary table (RD, QA, or DBA for temporary data processing)

3. Name the backup table (used to save and archive historical data or Dr Data) with the prefix bak, module, table, and date suffix bak_user_eduinfo_20210719

4. Tables in the same module should use the same prefix as much as possible, and table names should express meanings as much as possible

5, more than one word separated by underscore _

6, the general table table name should not exceed 30 characters, temp table and BAK table depending on the situation, also as short as possible appropriate, should be named in lowercase

Field naming conventions

1. Name a field using an English word or abbreviation to indicate its actual meaning. The words are connected by underscores (_), such as service_IP and service_port.

Create time (create_time); create time (create_time); create time (create_time);

3. Separate multiple words by underscore _

4. The field name should contain no more than 30 characters and should be named in lowercase

Index naming convention

Create unique index uni_uid on T_user_basic (uid)

2. Create index idx_UNAME_mobile on T_user_basic (uname,mobile).

3. Separate multiple words by underscore _.

4. The index name should contain no more than 50 characters. The index name should be lowercase.

Select idx_MID_fid, idx_mid_fid, idx_mid_fid, idx_MID_fid, idx_MID_fid, idx_MID_fid, idx_MID_fid, idx_MID_fid

6, understand the left prefix principle of composite index, avoid repeated construction index, if the establishment of (a,b, C), equivalent to the establishment of (a), (a,b), (a, B, C).

View naming conventions

1. The view name starts with v, which means view. The complete structure is the abbreviation of V + view content meaning.

V + table name if the view is from a single table. If the view is associated with several tables, use v+ underscore (_) to connect several table names. The view name should not exceed 30 characters. If there are more than 30 characters, use the abbreviation.

3. Developers are forbidden to create views unless they have special needs.

4. Use lowercase names.

Stored procedure naming conventions

1. Storage procedure names start with SP, indicating storage procedure names. Multiple words are then underlined (_). The function of a stored procedure should be reflected in its name. The stored procedure name cannot exceed 30 characters.

2. Input parameters in a stored procedure start with i_ and output parameters start with o_.

3. Use lowercase names.

create procedure sp_multi_param(
    in i_id bigint,
    in i_name varchar(32),
    out o_memo varchar(100)
)

Copy the code

Function naming conventions

1. Function names start with func, denoting function. Multiple words are then connected by an underscore (_). The function name should reflect its function. The function name should contain no more than 30 characters.

2. Use lowercase names.

create function func_format_date(ctime datetime)

Copy the code

Trigger naming convention

1. A trigger starts with trig.

2. The basic part describes the table added by the trigger. The trigger name should not exceed 30 characters.

3, suffix (_i,_u, _D), indicating the trigger condition trigger mode (INSERT,update, or DELETE).

4. Use lowercase names.

DROP TRIGGER IF EXISTS trig_attach_log_d;   
CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;

Copy the code

Constraint naming convention

1, unique constraint: uk_ table name _ field name UK is short for UNIQUE KEY. ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);

Foreign key constraint: fk_ table name, followed by the name of the table where the foreign key resides and the corresponding primary table name (excluding t_). Child and parent table names are separated by an underscore (_). ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3. Non-null constraint: If there is no special requirement, it is recommended that all fields default to not null. Different data types must give default values.

4. For performance reasons, it is recommended not to use foreign keys if there is no special need. Referential integrity is controlled by code. This is also a common practice, with integrity controls from a procedural point of view, but it can also produce dirty data if you are not careful.

5. Use lowercase names.

User naming conventions

1. The user name format used for production is CODE_ Application

2. The read-only user is named read_ application

Specification for database object design

Storage engine selection

1. If there is no special requirement, you must use InnoDB storage engine.

To view the current default engine, run show variables like ‘default_storage_engine’. There are MyISAM and InnoDB, and InnoDB engine is used by default from version 5.5.

The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. Tables of the MyISAM type emphasize performance and execute faster than the InnoDB type, but do not provide transaction support, which InnoDB provides as well as advanced database features such as external keys.

Character set selection

1. Use UTF8 or UTF8MB4 if there is no special requirement.

In China, the utF8 format, which supports both Chinese and other languages, is the best way. MySQL added utF8MB4 encoding after 5.5, which stands for most Bytes 4, specifically for four-byte Unicode compatibility.

So UTF8MB4 is a superset of UTF8, and no conversion is required except to change the encoding to UTF8MB4. Of course, to save space, utF8 is usually enough.

You can use the following script to view the encoding format of the database

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'; 
SHOW VARIABLES Like '%char%';

Copy the code

Specification for table design

1. The association between database tables corresponding to different applications should be reduced as far as possible, and foreign keys are not allowed to be used for the association between tables, so as to ensure the independence of the corresponding tables of components and provide the possibility for the reconstruction of the system or table structure. Current industry practice is generally controlled by procedures for referential integrity.

2. From the perspective of table design, database design should not be carried out for the whole system, but should be divided into components in the system architecture and designed for the business handled by each component.

3, the table must have PK, the advantage of primary key is unique identification, effective reference, efficient retrieval, so generally try to have primary key fields.

4. A field has only one meaning.

5. Tables should not have duplicate columns.

6. Do not use complex data types (arrays, custom, etc.). Json is optional.

7. The data types of the fields (join keys) that need to be joined must be absolutely consistent to avoid implicit conversion. For example, the associated fields are of type int.

8. Design should at least meet the third normal form and minimize data redundancy. Some special scenarios allow for antiformal design, but the design of redundant fields needs to be explained during the project review.

9. The TEXT field, as a large volume TEXT, must be stored in an independent table and associated with the main table by PK. Do not use TEXT and BLOB fields unless special requirements are met.

10. The table whose expired data needs to be deleted (or transferred) regularly is solved by dividing the table. Our approach is to migrate the historical data with low operation frequency to the history table according to the 2/8 rule, and make cutting points according to the time or the Id of the history.

11. Do not have too many fields in a single table. It is recommended that the maximum number of fields in a single table be not more than 50. Excessively wide tables also have a significant impact on performance.

12. It is recommended that the physical size of a single table be limited to 16GB and the number of rows in the table be limited to 2000W.

The rule in the industry is that above 2000W performance begins to degrade significantly. But this value is flexible, you can test according to the actual situation to judge, for example, Ali’s standard is 500W, Baidu is indeed 2000W. In fact, whether the table is wide or not, the space occupied by a single row of data all play a role.

13, if the amount of data or data growth in the early planning is large, then in the design review should be added to the table strategy, there will be a special article to analyze the data split: vertical split (vertical sub-table and vertical sub-table), horizontal split (sub-table and sub-table in the database);

14, no special requirements, strictly prohibit the use of partition table

Field design specification

1, INT: If not required, use an UNSIGNED INT to store integer numbers. The number after an integer field represents the display length. Example: id int(11) NOT NULL

DATETIME: All fields that need to be accurate to the time (hour, minute, second) use DATETIME, not TIMESTAMP type.

For TIMESTAMP, it converts the time written from the current time zone to UTC (Universal Standard Time) for storage. When querying, it is returned as the current time zone of the client. For DATETIME, nothing is changed and the input and output are basically as-is.

In addition, the range of DATETIME storage is relatively large:

Timestamp The time range that can be stored is ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’.

The value of datetime ranges from ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’.

However, in special cases, TIMESTAMP is more suitable for cross-time zone services.

3. VARCHAR: All dynamic length strings should use VARCHAR type. Similar to the fields of limited categories such as states, they should also use strings that can clearly represent the actual meaning, instead of using numbers such as ints. VARCHAR (N),

N is the number of characters, not bytes. For example, VARCHAR(255) can store up to 255 characters (including English letters, Chinese characters, special characters, etc.). However, N should be as small as possible, because the maximum length of all VARCHAR fields in a MySQL table is 65535 bytes, and the number of stored characters is determined by the selected character set.

If UTF8 stores a maximum of three bytes of a character, vARCHar should store a maximum of 21845 characters of three bytes. At the same time, N is used to allocate memory for memory operations such as sorting and creating temporary tables. (In principle, a single VARCHAR field cannot exceed 255 characters unless otherwise required.)

4, TEXT: use TEXT only when the number of characters may exceed 20000, because all MySQL databases use the UTF8 character set.

All fields using TEXT type must be separated from the original table, and the original table primary key to form another table for storage, and large TEXT field isolation, the purpose is. The MEDIUMTEXT, TEXT, and LONGTEXT types are not used unless otherwise required

5. For exact floating-point data stores, use DECIMAL; FLOAT and DOUBLE are strictly prohibited.

6. Avoid using BLOB types unless there is a special need

7. If there is no special requirement, you are advised to use the NOT NULL attribute for the field. The default value can be used instead of NULL

The type of the increment field must be an integer and UNSIGNED. The recommended type is INT or BIGINT. The increment field must be a primary key or part of a primary key.

Index design specification

1. Index differentiation

Indexes must be created on columns with high index selectivity. The selectivity is calculated as: selecttivity = count(distinct c_name)/count(*); If the distinction is less than 0.2, do not create an index on this column. Otherwise, SQL execution may be slowed down

2. Follow the left-most prefix

For multiple fields that need to form a composite index, it is recommended that the fields with high selectivity be placed first in the design. When used, the first field of the composite index must be in the WHERE condition and must be matched according to the leftmost prefix rule.

Foreign keys are prohibited and integrity can be constrained at the program level

4. If you want to create an index for a Text field, you must use a prefix index

5. The number of indexes in a single table should theoretically be limited to 5. There are often large quantities of insert, update operation table, should be as little as possible to build index, the principle of index establishment is more read and less write scenario.

6, ORDER BY, GROUP BY, DISTINCT columns need to be added after the index to form an overwrite index

7, correctly understand and calculate the index field distinction, the article has calculation rules, high distinction index, can quickly locate the data, distinction is too low, can not effectively use the index, may need to scan a large number of data pages, and do not use the index there is no difference.

8, the correct understanding and calculation of prefix index field length, there are judgment rules, the appropriate length to ensure a high degree of discrimination and the most appropriate index storage capacity, only to achieve the best state, is to ensure high efficiency of the index.

MySQL matches all indexes in left-to-right order until it encounters range queries (>, <, between, like) and then stops matching.

Depno =1 and empname> “and job=1; depno=1 and empname>”;

10, on demand and fetch strategy, when querying records, do not use * at the beginning, only take the data needed, if possible, only use index coverage, can reduce back to the table operation, improve efficiency.

11, correctly judge whether to use the joint index (the above section of the use of the joint index has explained the judgment rules), can also be further analyzed to index push down (IPC), reduce back table operations, improve efficiency.

12, avoid index failure principle: prohibit the use of index field function, operator operation, will make the index failure. This is essentially to ensure the cleanliness of the fields in the index.

13. Avoid unnecessary type conversions, as string field comparisons with numeric values will result in invalid indexes.

14. A fuzzy query ‘%value%’ invalidates an index into a full table scan because it is impossible to determine the range of the scan, but ‘value%’ is a valid use of the index.

15. The index overwrites the sort field, which reduces sorting steps and improves query efficiency

16, as far as possible to expand the index, do not create a new index. For example, if you want to add (a,b) to a table that already has an index of A, you only need to modify the original index.

For example, for a brand table, create the following index, a primary key index, a unique index

PRIMARY KEY (`id`),   
UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

Copy the code

An immediate warning should be given when a search statement in your colleague’s business code looks like this: there is no overwriting index, and the left-most prefix rule is not followed:

select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?

Copy the code

The suggestion is as follows:

select brand_id,brand_name from  ds_brand_system where app_id=? and define_id=?  and  status=?

Copy the code

Constraint design specification

1. PK should be ordered and meaningless, customized by the developer, as short as possible, and an increment sequence.

2. Create a unique constraint index prefixed with “uk_” in the database if there is uniqueness constraint in the table other than PK.

3. The PK field cannot be updated.

4. Do not create foreign key constraints. Foreign key constraints are controlled by programs.

5. All fields must have a non-null constraint (not NULL) unless otherwise required.

6. All fields must have default values unless otherwise required.

SQL Usage Specification

The normalization of select retrieval

1. Avoid the use of SELECT * in the JOIN statement. Select * may cause a query that only needs to access the index to complete.

One is to pull out a lot of data that you don’t need, which is a disaster for a wide table. One way is to avoid returning to the table as much as possible, because it is not cost-effective to return to the table to retrieve some data that is not needed at all.

Select * from T_name; select * from t_name; select * from t_name;

MySQL > select * from user where id = 1;

3.1. Do not store it together with other common fields, because the low reading efficiency will affect the access efficiency of other lightweight fields.

If you do not need a text field and use select *, the execution will consume a lot of IO and be inefficient

4. Use related functions on fetch fields, but avoid uncertain results such as now(), rand(), sysdate(), etc. Do not use any functions on filter fields of Where conditions, including data type conversion functions. Heavy computations and conversions can lead to inefficiencies, as described in the index.

5, paging query statements all need to have sorting conditions, otherwise it is easy to cause disorder

6. Replace or with in()/union for better efficiency, and note that the number of in is less than 300

Select a,b,c from t_name where a like ‘%name’; Select a,b from t_name where a like ‘name%’;

8. Avoid subqueries and optimize them for Join operations

Generally, subqueries can be converted into associated queries for optimization only when the subquery is in clause and the subquery is simple SQL(excluding union, group BY, Order BY, and limit clauses).

Reasons for poor sub-query performance:

The result set of “·” subquery cannot use indexes. Usually, the result set of subquery is stored in temporary tables. No index exists in memory temporary tables or disk temporary tables, so the query performance is affected.

“·” especially for subqueries with large result sets, the impact on query performance will be greater;

“·” Because the sub-query will generate a large number of temporary tables and no indexes, it will consume too much CPU and IO resources and generate a large number of slow queries.

Standardization of operation

1. Disallow INSERT statements without a list of fields

Insert into values (‘a’,’b’,’c’); Insert into T_name (c1,c2,c3) values (‘a’,’b’,’c’); .

2, batch write operations (UPDATE, DELETE, INSERT), need to batch multiple operations

  • A batch operation may cause serious master/slave delay, especially in master/slave mode, because the slave needs to read logs from the master’s binlog for data synchronization.

  • Binlog Logs in ROW format generate a large number of logs

Procedural constraints

The subsequent goal of our team is to develop the review tool to analyze the statements of database building, table building, data brushing and query submitted by the developer to see if they meet the due specifications. If not, reject the modification.