The most detailed MySQ design and development specifications [recommended collection]

The following article will sort out the most detailed MySQL design and development specifications for you. I hope it will be helpful to you.

                                                                                                               

Database object naming convention

Database object

Database objects are components of the database. Common ones include the following : Table, Index, View, Diagram, Default, Rule, Trigger, Stored Procedure, User wait. Naming convention refers to the naming convention for database objects such as database (SCHEMA), table (TABLE), index (INDEX), constraints (CONSTRAINTS), etc. [Recommendation: mysql video tutorial]

Global naming specification for database objects

1. Use meaningful English words for naming, with underscores in the middle of the words. Separation

2. Names can only use English letters, numbers, and underscores, starting with English letters

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

4. All database objects use lowercase letters. In fact, it is possible to set case sensitivity in MySQL. In order to ensure uniformity, we standardize all lowercase expressions here.

Database naming convention

1. The database name should not exceed 30 characters.

2. The database naming is generally the project name, which represents the abbreviation of the library meaning. For example, the workflow database of the IM project can be im_flow.

3. The default character set and collation rule clauses must be added when creating the database. The default character set is UTF8 (the migrated dumbo uses utf8mb4)

4. The name should be lowercase.

Table naming convention

1. Regular table names start with t_, t represents table, and the naming rule is t module (including the abbreviation of the module meaning) Table (contains the abbreviation of the table meaning), such as the education information table of the user module: t_user_eduinfo.

2. Temporary table (a table used by RD, QA or DBA students for temporary data processing), naming rule: temp prefix module table date suffix: temp_user_eduinfo_20210719

3 , Backup table (used to save and archive historical data or as disaster recovery data) naming rules, bak prefix module table date suffix: bak_user_eduinfo_20210719

4. All tables in the same module The same prefix may be used, and the table name expresses the meaning as much as possible

5. Multiple words are separated by underscore_

6. The regular table name should not exceed 30 characters. The temp table and bak The table depends on the situation, and should be as short as possible. The name should use lowercase

Field naming specifications

1. Field naming needs to be an English word that expresses its actual meaning or Abbreviation, use underscore _ to connect words, such as service_ip, service_port.

2. Fields with the same meaning between tables must have the same name. For example, table a and table b both have creation time, which should be unified as create_time. Inconsistency will cause confusion.

3. Multiple words are separated by underscore _

4. Field names should be no more than 30 characters, and the naming should be in lowercase

index naming Specifications

1. The unique index is named using the uni field name: create unique index uni_uid on t_user_basic(uid).

2. Use the idx field name to name the non-unique index: create index idx_uname_mobile on t_user_basic(uname,mobile).

3. Multiple words are separated by underscore _.

4. The index name should not exceed 50 characters. The name should be lowercase. The number of fields in the combined index should not be too many, otherwise it will not be conducive to improving query efficiency.

5. For column names composed of multiple words, use the abbreviation that represents the meaning as much as possible, such as the combination on the test_contact tablemember_id and friend_id Index: idx_mid_fid.

6. Understand the leftmost prefix principle of combined indexes and avoid repeatedly building indexes. If (a,b,c) is created, it is equivalent to creating (a), (a,b), (a,b ,c).

View naming convention

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

2. If the view only comes from a single table, it is the v table name. If the view is generated by the association of several tables, use v and underscore (_) to connect several table names. The view name should not exceed 30 characters. If it exceeds 30 characters, use the abbreviation.

3. Developers are strictly prohibited from creating views without special needs.

4. Names should be in lowercase.

Stored procedure naming convention

1. The stored procedure name starts with sp, which means stored procedure (storage procedure). Multiple words are connected with underscores (_). The function of the stored procedure should be reflected in its naming. The stored procedure name should not exceed 30 characters.

2. The input parameters in the stored procedure start with i_, and the output parameters start with o_.

3. Names should be in lowercase.

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

Function naming convention

1. The function name starts with func, which means function. After that, multiple words are connected with underscores (_), and their functions should be reflected in the function naming. Try to keep function names no longer than 30 characters.

2. The name should be lowercase.

create function func_format_date(ctime datetime)

Trigger naming convention

1. The trigger starts with trig, which means trigger trigger.

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

3. The suffix (_i, _u, _d) indicates the triggering method of the trigger condition (insert, update or delete).

4. Names should be in lowercase.

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

Constraint naming convention

1. Unique constraint: uk_table name_field name. uk is the abbreviation of UNIQUE KEY. For example, add a unique constraint to the department name of a department to ensure that there are no duplicate names, as follows:

ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);

2. Foreign key constraint: fk_table name, followed by the table name where the foreign key is located and the corresponding Main table name (excluding t_). The child table name and the parent table name are separated by an underscore (_). As follows:

ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3. Non-null constraints: If there are no special needs, it is recommended that all fields be non-null by default, and different data types must be given default values.

1 `id` int(11) NOT NULL,2 `name` varchar(30) DEFAULT '',3 `deptId` int(11) DEFAULT ,4 `salary` float DEFAULT NULL,

4. For performance reasons, it is recommended not to use foreign keys unless there are special needs. Referential integrity is controlled by code. This is also our common practice, to control integrity from a program perspective, but if you are not careful, dirty data will also be generated.

5. Names should be in lowercase.

User naming convention

1. The user naming format used in production is code_application

2. The read-only user naming convention is read_application

Database object design specifications

Selection of storage engine

1. If there are no special requirements, the innodb storage engine must be used .

You can view the current default engine by show variables like ‘default_storage_engine‘. There are mainly MyISAM and InnoDB. Starting from version 5.5, the InnoDB engine is used by default. Click here to practice quizzes.

The basic difference is: The MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. The MyISAM type of table emphasizes performance and its execution speed is faster than the InnoDB type, but does not provide transaction support, while InnoDB provides transaction support and foreign keys and other advanced database functions.

Character set selection

1. If there are no special requirements, utf8 or utf8mb4 must be used.

In China, it is the best way to choose the utf8 format that has perfect support for Chinese and various languages. MySQL added utf8mb4 encoding after 5.5, mb4 means most bytes 4, which is specially designed to be compatible with four-byte unicode.

So utf8mb4 is a superset of utf8, and no other conversion is required except changing the encoding to utf8mb4. Of course, in order to save space, it is usually enough to use utf8.

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

1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';2 -- 或3 SHOW VARIABLES Like '%char%';

Table design specifications

1. Between database tables corresponding to different applications Associations should be minimized as much as possible, and foreign keys are not allowed to be used to associate tables to ensure the independence between tables corresponding to components and provide the possibility of reconstructing the system or table structure. Current industry practice generally involves programmatic control of referential integrity.

2. From the perspective of table design, the database design should not be based on the entire system, but should be based on the division of components in the system architecture, and the database design should be based on the business handled by each component.

3. The table must have a PK. The advantages of the primary key are unique identification, effective reference, and efficient retrieval, so in general, try to have a primary key field.

4. A field only represents one meaning.

5. The table should not have duplicate columns.

6. It is prohibited to use complex data types (arrays, custom, etc.). The use of Json types depends on the situation.

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

8. The design should at least meet the third paradigm and minimize data redundancy. Some special scenarios allow denormalization design, but the design of redundant fields needs to be explained during project review.

9, TEXT fields are stored as large amounts of text and must be placed in an independent table and associated with the main table using PK. Unless otherwise required, the TEXT and BLOB fields are prohibited.

10. Tables that need to regularly delete (or transfer) expired data can be solved by dividing the table. Our approach is to migrate historical data with low operating frequency to the historical table according to the 2/8 rule, according to the time Or use Id as the cutting point.

11. The number of fields in a single table should not be too many, and it is recommended not to exceed 50 at most. Excessively wide tables also have a great impact on performance.

12. When MySQL processes large tables, its performance begins to decrease significantly. Therefore, it is recommended that the physical size of a single table be limited to 16GB, and the number of data rows in the table be controlled within 2000W.

The rule in the industry is that performance begins to decrease significantly beyond 2000W. But this value is flexible, and you can test it based on the actual situation. For example, Alibaba’s standard is 500W, and Baidu’s is indeed 2000W. In fact, whether the table is wide or not and the space occupied by a single row of data all play a role.

13. If the amount of data or data growth is large in the early planning, then the table splitting strategy should be added during the design review. There will be a special article later to analyze the data splitting method: vertical splitting Split (vertical database splitting and vertical table splitting), horizontal splitting (database splitting and table splitting within the database);

14. Without special requirements, the use of partition tables is strictly prohibited

Field design specifications

1, INT: If there is no special need, use the UNSIGNED INT type to store integer numbers. The number after the integer field represents Display length. For example id int(11) NOT NULL

2, DATETIME: Use # for all fields that need to be accurate to time (hours, minutes and seconds) ##DATETIME, do not use the TIMESTAMP type.

For

TIMESTAMP, it converts the written time from the current time zone to UTC (Universal Coordinated Time) for storage. When querying, it is converted into the current time zone of the client and returned. For DATETIME, no changes are made, and the input and output are basically the same.

In addition

DATETIMEThe storage range is also relatively large:

timestampThe time range that can be stored is: '1970-01-01 00: 00:01.000000' to '2038-01-19 03:14:07.999999'.

datetimeThe time range that can be stored is: '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.

But in special circumstances, for businesses across time zones,

TIMESTAMP is more suitable.

3,

VARCHAR: All dynamic length strings use the VARCHAR type, which is similar to limited categories of fields such as status. It can also be used to more clearly express the actual meaning. Strings should not be replaced by numbers such as INT; VARCHAR(N),

N represents the number of characters rather than the number of bytes. For example,

VARCHAR(255) can store up to 255 characters (characters include English letters, Chinese characters, special characters, etc.). But 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 takes up to 3 bytes to store a character, then varchar should not exceed 21845 characters when storing characters that occupy 3 bytes in length. At the same time, when performing memory operations such as sorting and creating temporary tables, the length of N will be used to apply for memory. (If there is no special need, in principle, a single

varchar type field is not allowed to exceed 255 characters)

4,

TEXT: only when the number of characters may exceed 20,000 Only then can the TEXT type be used to store character data, because all MySQL databases use the UTF8 character set.

All fields using the

TEXT type must be split from the original table, and formed into another table separately from the original table's primary key for storage, in order to isolate them from large text fields. If there is no special need, do not use MEDIUMTEXT, TEXT, LONGTEXT types

5. For precise floating point data storage, you need to use

DECIMAL, the use of FLOAT and DOUBLE is strictly prohibited.

6. If there is no special need, try not to use the

BLOB type

7. If there is no special need, it is recommended to use the

NOT NULL attribute for the field. The default value can be used instead of NULL

8. The auto-increment field type must be an integer and must be

UNSIGNED. The recommended type is INT or BIGINT, and the auto-increment field must be the primary key or part of the primary key.

Index design specifications

1. Index distinction

The index must be created on the column with higher index selectivity (discrimination), select The calculation method of selectivity is:

selecttivity = count(distinct c_name)/count(*); If the discrimination result is less than 0.2, it is not recommended to create an index on this column, otherwise it will most likely slow down SQL execution.

2. Follow the leftmost prefix

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

3、禁止使用外键，可以在程序级别来约束完整性

4、Text类型字段如果需要创建索引，必须使用前缀索引

5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。

6、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

7、正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

8、正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

9、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

如：depno=1 and empname>'' and job=1 如果建立(<code>depno,empname,job)顺序的索引，job是用不到索引的。

10、应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

11、正确判断是否使用联合索引（上面联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

12、避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

13、避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

14、模糊查询’%value%’会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%’是可以有效利用索引。

15、索引覆盖排序字段，这样可以减少排序步骤，提升查询效率

16、尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

举例子：比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引

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

当你同事业务代码中的检索语句如下的时候，应该立即警告了，即没有覆盖索引，也没按照最左前缀原则：

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

建议改成如下：

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

约束设计规范

1、PK应该是有序并且无意义的，由开发人员自定义，尽可能简短，并且是自增序列。

2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。

3、PK字段不允许更新。

4、禁止创建外键约束，外键约束由程序控制。

5、如无特殊需要，所有字段必须添加非空约束，即not null。

6、如无特殊需要，所有字段必须有默认值。

SQL使用规范

select 检索的规范性

1、尽量避免使用select *，join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。

2、严禁使用 select * from t_name ，而不加任何where条件，道理一样，这样会变成全表全字段扫描。

3、MySQL中的text类型字段存储：

3.1、不与其他普通字段存放在一起,因为读取效率低，也会影响其他轻量字段存取效率。

3.2、如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下

4. Related functions can be used to extract fields, but now(), rand(), sysdate(), etc. should be avoided as much as possible. For functions with uncertain results, it is strictly prohibited to use any function on the filter condition field in the Where condition, including data type conversion functions. A large number of calculations and conversions will cause inefficiency, which is also described in the index.

5. All paging query statements need to have sorting conditions, otherwise it will easily cause disorder

6. Use in()/union to replace or , the efficiency will be better, and please note that the number of in is less than 300

7. It is strictly prohibited to use the % prefix for fuzzy prefix query: such as: select a,b,c from t_name where a like '%name'; You can use % fuzzy suffix query such as: select a,b from t_name where a like 'name%';

8. Avoid using subqueries, you can Optimize the subquery into the join operation

Usually the subquery is in the in clause, and the subquery is simple SQL (excluding union, group by, order by, limit clause), the subquery can be converted into a related query for optimization.

Reasons for poor subquery performance:

· The result set of the subquery cannot use the index. Usually the result set of the subquery will be stored in a temporary table. There are no indexes in either memory temporary tables or disk temporary tables, so query performance will be affected to a certain extent;

· Especially for subqueries that return relatively large result sets, the The greater the impact on query performance;

· Since the subquery 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 query.

Normalization of operations

1. It is prohibited to use INSERT statements without field lists

For example: insert into values ​​('a','b','c'); should use insert into t_name(c1,c2,c3) values ​​('a','b','c'); .

2. Large batch write operations (UPDATE, DELETE, INSERT) need to be performed multiple times in batches

· Large batch operations may cause serious master-slave delays, especially in master-slave mode, large batch operations may cause serious master-slave delays because slaveslave# is required. Read the log in the binlog of ##master for data synchronization.

· binlogWhen the log is in the row format, a large number of logs will be generated

The above is the detailed content of The most detailed MySQ design and development specifications [recommended collection]. For more information, please follow other related articles on the PHP Chinese website!