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

This article mainly introduces the relevant knowledge of MySQ design and development specifications, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this MySQ design and development specification articles. Let's take a look at it.

Database object naming convention database object

Database objects are part of the database, and there are the following common types: table (Table), index (Index), view (View), chart (Diagram), default value (Default), rule (Rule), trigger (Trigger), stored procedure (Stored Procedure), User and so on. Naming conventions refer to naming conventions for database objects such as database (SCHEMA), table (TABLE), index (INDEX), constraint (CONSTRAINTS), etc. [recommended: mysql video tutorial]

Global naming convention for database objects

1. Naming uses meaningful English words, separated by underscores in the middle.

2. Naming can only use English letters, numbers, underscores and begin with the English letter.

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

4. All database objects use lowercase letters. In fact, case sensitivity can be set in MySQL. In order to ensure consistency, we specify all lowercase representations here.

Database naming convention

1. Database naming should not exceed 30 characters as long as possible.

2. The database name is generally the project name + the abbreviation that represents the meaning of the library, such as the workflow database of the IM project, which can be im_flow.

3. The default character set and proofreading rule clause must be added when the database is created. The default character set is UTF8 (utf8mb4 for migrated dumbo)

4. Naming should be in lowercase.

Table naming convention

1. The general table name starts with t _, and t stands for table. The naming rule is t + module (abbreviation for module meaning) + table (abbreviation for table meaning), such as educational information table for user module: t_user_eduinfo.

2. Temporary tables (tables used by RD, QA or DBA students for temporary data processing). Naming rules: temp prefix + module + table + date suffix: temp_user_eduinfo_20210719

3. Naming rules for backup table (for saving and archiving historical data or data for disaster recovery), bak prefix + module + table + date suffix: bak_user_eduinfo_20210719

4. The tables of the same module use the same prefix as much as possible, and the table name expresses the meaning as much as possible.

5. Multiple words are underlined _ separated

6. The table name of the regular table should not exceed 30 characters, and the temp table and bak table should be as short as possible, and the naming should be in lowercase.

Field naming convention

1. Field naming requires English words or abbreviations that represent its actual meaning, and the words are connected with an underscore, such as service_ip and service_port.

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

3. Multiple words are underlined _ separated

4. The field name should not exceed 30 characters, and the naming should be in lowercase.

Index naming convention

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

2. The non-unique index is named using the idx + field name: create index idx_uname_mobile on t_user_basic (uname,mobile).

3. Multiple words are underlined _ separated.

4. The index name should not exceed 50 characters as far as possible, the naming should be lowercase, and the fields of the combined index should not be too many, otherwise it is not conducive to the improvement of query efficiency.

5. For the column name composed of multiple words, take abbreviations that represent the meaning as much as possible, such as the combined index on test_ contacttable member _ id and friend_id: idx_mid_fid.

6. Understand the leftmost prefix principle of the combinatorial index and avoid repeated construction of the index. if you establish (a), (b), (a), (b), (b).

View naming convention

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

2. If the view comes from only a single table, it is the v+ table name. If the view is generated by several table associations, join several table names with a v + underscore (_). The view name should not exceed 30 characters as long as possible. If more than 30 characters, take shorthand.

3. If there is no special need, developers are strictly prohibited from creating views.

4. Naming should be in lowercase.

Stored procedure naming convention

1. The stored procedure name begins with sp, which represents the stored procedure (storage procedure). After that, multiple words are connected with an underscore (_). Its function should be reflected in the naming of stored procedures. The stored procedure name should not exceed 30 characters as much as possible.

2. The input parameters in the stored procedure start with I _, and the output parameters start with o _.

3. Naming 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 specification

1. The function name starts with func and represents function. After that, a number of words are connected with an underscore (_), and its function should be reflected in the function naming. The function name should be no more than 30 characters.

2. Naming should be in lowercase.

Naming convention for create function func_format_date (ctime datetime) triggers

1. Triggers start with trig, which means trigger triggers.

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

3. The suffix (_ iMagnum _ delete) indicates the trigger mode of the trigger condition (insert,update or delete).

4. Naming 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 the name is not repeated, 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 _). Child table names and parent table names 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 constraint: if there is no special need, it is recommended that all fields be non-empty by default (not null), and different data types must give default values (default).

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 if there is no special need. Referential integrity is controlled by code. This is also our common practice of integrity control from a procedural point of view, but dirty data will be generated if we do not pay attention to it.

5. Naming should be in lowercase.

User naming convention

1. The user naming format used in production is code_ application.

2. The naming rule for read-only users is read_ application

Database object Design Specification selection of Storage engine

1. If there are no special requirements, you must use the innodb storage engine.

You can view the current default engine through show variables like 'default_storage_engine'. There are mainly MyISAM and InnoDB, and the InnoDB engine has been used by default since version 5.5. Click here to do the exercises.

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

Selection of character set

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 which is very perfect for Chinese and various languages. MySQL adds utf8mb4 coding after 5.5. mb4 means most bytes 4 and is specially used to be compatible with four bytes of unicode.

So utf8mb4 is a superset of utf8, and there is no need to do any conversion except to change the encoding to utf8mb4. Of course, in order to save space, utf8 is generally sufficient.

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-or 3 SHOW VARIABLES Like'% char%'; table design specification

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

2. from the point of view of table design, the database should not be designed for the whole system, but should be divided according to the components in the system architecture, and the database should be designed for the business handled by each component.

3, the table must have competition, the advantage of primary key is unique identification, valid reference, efficient retrieval, so generally try to have primary key field.

4. A field has only one meaning.

5. The table should not have duplicate columns.

6, prohibit the use of complex data types (arrays, customizations, etc.), the use of Json type depends on the situation.

7. For fields that need join (connection key), the data type 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 reduce data redundancy as much as possible. Some special scenarios allow anti-stylized design, but the design of redundant fields needs to be explained during project review.

9. As a large amount of text storage, the TEXT field must be placed in a separate table and associated with the main table with competition. If there is no special need, the use of TEXT and BLOB fields is prohibited.

10. Tables that need to delete (or transfer) expired data on a regular basis are solved through sub-tables. our approach is to migrate the historical data with less frequent operation frequency to the history table in accordance with the rule of 2max and 8, and do the cutting point according to time or Id.

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

12. The performance of MySQL begins to degrade significantly when dealing with large tables, so it is recommended that the physical size of a single table is limited to 16GB, and the number of rows of data in the table is limited to 2000W.

The rule in the industry is that over 2000W performance begins to degrade significantly. But this value is flexible, you can judge according to the actual situation, 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 plays a role.

13. If the amount of data or data growth is large in the early planning, then the split table strategy should be included in the design review. Later, there will be special articles to analyze the practice of data splitting: vertical split (vertical split and vertical split), horizontal split (split table and in-database split table)

14. No special needs, so it is forbidden to use partition tables.

Field design specification

1. INT: if there is no special need, use UNSIGNED int type to store integer numbers, and the number after the integer field represents the display length. Such as id int (11) NOT NULL

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

For TIMESTAMP, it converts the time of the write from the current time zone to UTC (Universal Standard time) for storage. When querying, it is converted to the client's current time zone for return. For DATETIME, no change is made, basically as-is input and output.

In addition, the scope of DATETIME storage is also relatively large:

The time range that a timestamp can store ranges from '1970-01-01 00-0000-01.000000' to' 2038-01-1903-14purl 07.999999'.

The time range that the datetime can store ranges from '1000-01-01 0000VL 00.000000' to' 9999-12-31 2323RV 59.999999'.

But in special cases, TIMESTAMP is more suitable for services across time zones.

3. VARCHAR: all dynamic length strings use the VARCHAR type, similar to fields with limited categories such as status, and also use strings that can clearly express the actual meaning, instead of numbers such as INT. VARCHAR (N)

N represents 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 characters stored is determined by the selected character set.

If UTF8 stores a character with a maximum of 3 bytes, then varchar should not exceed 21845 characters when storing characters that occupy 3 bytes in length. At the same time, the length of N is used to request memory for memory operations such as sorting and creating temporary tables. (in principle, a single varchar field is not allowed to exceed 255 characters if there is no special need)

4. TEXT: you can use the TEXT type to store character class data only when the number of characters may exceed 20000, because all MySQL databases use the UTF8 character set.

All fields that use the TEXT type must be separated from the original table and stored in a separate table with the primary key of the original table, isolated from large text fields for the purpose. Do not use MEDIUMTEXT, TEXT, LONGTEXT types if there is no special need

5. For accurate floating-point data storage, DECIMAL is required, and FLOAT and DOUBLE are 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 in the field, and the default value can be used instead of NULL

8. The self-increment field type must be integer and must be UNSIGNED, and the recommended type must be INT or BIGINT, and the self-increment field must be a primary key or part of a primary key.

Index design specification

1. Index differentiation

The index must be created on a column with high index selectivity (discrimination), and the selectivity is calculated as follows: 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

For identifying multiple fields that need to form a composite index, it is recommended that the highly selective fields be placed forward at design time. When in use, the first field of the composite index must be in the where condition and must be matched according to the leftmost prefix rule.

3. The use of foreign keys is prohibited and integrity can be constrained at the program level

4. If you need to create an index for Text type fields, you must use a prefix index.

5. Theoretically, the number of indexes in a single table should be controlled within 5. There are often a large number of insert and update operation tables, and the index should be built as little as possible. theoretically, the principle of index establishment is the scenario of more reading and less writing.

6. The fields of ORDER BY,GROUP BY,DISTINCT need to be added at the end of the index to form an overlay index.

7. Correctly understand and calculate the differentiation degree of the index field. There are calculation rules in this paper, and the index with high discrimination degree can quickly locate the data, but the discrimination degree is too low to effectively use the index. It may be necessary to scan a large number of data pages, which is no different from not using the index.

8. Correctly understand and calculate the field length of the prefix index, there are judgment rules in this paper, the appropriate length should ensure high differentiation and the most appropriate index storage capacity, only to achieve the best state is to ensure high efficiency of the index.

9. Union index pay attention to the leftmost matching principle: it must be matched in the order from left to right. MySQL will match the index to the right until it encounters a range query (>, 'and job=1. If you build (depno,empname,job) the index, job will not use the index.

10. Take the strategy as needed. When querying records, do not use * as soon as possible. Only take the data you need. If possible, you can only use index coverage, which can reduce table return operations and improve efficiency.

11. Correctly judge whether or not to use a federated index (the above section on the use of the federated index explains the judgment rules). You can also further analyze the index push-down (IPC) to reduce back table operations and improve efficiency.

12. the principle of avoiding index failure: forbidding the use of functions and operators on the index field will invalidate the index. This is actually the need to ensure the "cleanliness" of the corresponding fields of the index.

Avoid unnecessary type conversions. Comparing string fields with numeric values will cause the index to be invalid.

14. Fuzzy query'% value%' will invalidate the index and become a full table scan, because it is impossible to determine the scan interval, but 'value%' can effectively use the index.

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

16. Expand the index as much as possible, and do not create a new index unless it is necessary. For example, if you already have an index of an in the table, and now you want to add the index of (a), you only need to modify the original index.

Example: for example, a brand table is indexed as follows, a primary key index and a unique index

1 PRIMARY KEY (`id`), 2 UNIQUE KEY `uni_brand_ define` (`app_ id`, `define_ id`)

When the search statement in your colleague's business code is as follows, you should warn immediately that the index is not overwritten and the leftmost prefix principle is not followed:

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

It is recommended that it be changed as follows:

1 select brand_id,brand_name from ds_brand_system where app_id=? And define_id=? And status=? Constraint design specification

1. Competition should be orderly and meaningless, customized by developers, as short as possible, and self-increasing sequence.

2. In addition to competition, there are uniqueness constraints in the table, so you can create a unique constraint index prefixed with "uk_" in the database.

3. Competitive fields are not allowed to be updated.

4. It is forbidden to create foreign key constraints, which are controlled by the program.

5. If there is no special need, all fields must add a non-empty constraint, namely not null.

6. If there is no special need, all fields must have default values.

The normalization of SQL usage Specification select Retrieval

1. Avoid using select * as far as possible. Using select * in the join statement may cause queries that only need to access the index to fetch data from the table.

One is that it is possible to fetch a lot of unneeded data, which is a disaster for wide tables; the other is to avoid returning to the table as much as possible, because it is not cost-effective to take some data that is not needed at all and return to the table leads to poor performance.

2. It is forbidden to use select * from t_name without any where conditions. For the same reason, it will become a full-table full-field scan.

3. Text type field storage in MySQL:

3.1, do not store with other ordinary fields, because the reading efficiency is low, it will also affect the access efficiency of other lightweight fields.

3.2.If you don't need a text field and use select *, the execution consumes a lot of io and is inefficient

4. Related functions can be used on the fetched field, but functions with uncertain results such as now (), rand (), sysdate () and so on should be avoided as far as possible. Any function, including data type conversion function, is strictly prohibited on the filter condition field in the Where condition. A large number of calculations and transformations can lead to inefficiency, which is also described in the index.

5. Paging query statements all need to have sort conditions, otherwise it is easy to cause disorder

6. Replacing or with in () / union will be more efficient, and note that the number of in is less than 300.

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

8. Avoid using subqueries and optimize them to join operations.

The general subquery is in the in clause, and when the subquery is simple SQL (does not include union, group by, order by, limit clauses), the subquery can be transformed into an associated query for optimization.

Reasons for poor performance of subqueries:

If the result set of a subquery cannot use an index, the result set of an ordinary subquery will be stored in a temporary table, and there will be no index in either the memory temporary table or the disk temporary table, so the query performance will be affected to some extent.

Especially for subqueries with large result sets, the greater the impact on query performance.

Because the subquery will produce a large number of temporary tables and no indexes, it will consume too much CPU and IO resources, resulting in a large number of slow queries.

Standardization of operation

1. Prohibit the use of INSERT statements without field lists

For example, insert into values ('axiomagy, c2, and c3); insert into t_name (C1, c2, and c3) values;

2. Write operations in large quantities (UPDATE, DELETE, INSERT), which need to be performed multiple times in batches.

Mass operations can cause serious master-slave delays, especially in master-slave mode, because slave is required to read logs from master's binlog for data synchronization.

When binlog logs are in row format, a large number of logs will be generated.

This is the end of the article on "what are the MySQ design and development specifications?" Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "what are the MySQ design and development specifications". If you want to learn more knowledge, you are welcome to follow the industry information channel.

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.