1. Background and Purpose of Specification

The purpose of this specification is to help or guide RD , QA, OP and other technical personnel to make database design suitable for online business. Standardize database changes and processing procedures, database table design, SQL writing, etc., so as to provide guarantee for the stable and healthy operation of the company’s business system.

2. Design Specification

2.1. Database Design

• All the following specifications It will be marked according to the three levels of [High Risk], [Mandatory], and [Recommended], and follow the priority from high to low.

• For designs that do not meet the two levels of [High Risk] and [Mandatory], the DBA has the right to forcefully call back and modify it.

2.1.1. Library name

• [Mandatory] The name of the library must be controlled within 32 characters , The table name and table name of related modules reflect the join relationship as much as possible, such as user table and user_login table.
• [Mandatory] The name format of the library: business system name_subsystem name, and the library name used by the same module should use a uniform prefix as much as possible. _
• [Mandatory] The naming format of the general sub-library name is library wildcard name_number, and the number increases from 0. For example, the name format of wenda_001 to sub-library by time is “library wildcard name_time”
• [Mandatory] The character set must be explicitly specified when creating the database, and the character set can only be utf8 or utf8mb4. SQL example for creating a database:
  create database db1 default character set utf8;

2.1.2. Table structure

• 【 Mandatory] The table must have a primary key, and set id as an auto-incrementing primary key.
• [Mandatory] Tables prohibit the use of foreign keys. If you want to ensure completeness, it should be implemented by the terminal. Foreign keys make the tables coupled with each other, affecting the performance of update, delete, etc., and may cause deadlock. High concurrency environment can easily lead to database performance bottlenecks.
• [Mandatory] Table and column names must be controlled within 32 characters. Table names can only use letters, numbers and underscores, all in lower case. If the table name is too long, abbreviations can be used.
• [Mandatory] When creating a table, you must explicitly specify the character set as utf8 or utf8mb4.
• [Mandatory] When creating a table, you must explicitly specify the table storage engine type. If there is no special requirement, it will be InnoDB. When a storage engine other than InnoDB/MyISAM/Memory needs to be used, it must be audited by the DBA before it can be used in a production environment. Because Innodb tables support important features of relational databases such as transactions, row locks, crash recovery, and MVCC, it is the most used MySQL storage engine in the industry. This is something that most other storage engines do not have, so InnoDB is the first choice.
• [Mandatory] There must be a comment to create a table, and both the table level and the field level must have comments.
• [Recommendation] Regarding the primary key when building a table: (1) Mandatory primary key is id, type is int or bigint (for future scalability, here requires new tables to be unified as bigint), and is auto_increment(2 ) Do not set the field of the main body of each row in the identification table as the primary key. It is recommended to set other fields such as user_id, order_id, etc., and establish a unique key index. Because if it is set as the primary key and the primary key value is randomly inserted, it will cause innodb internal page splitting and a large amount of random I/O, and performance will decrease.
• [Recommendation] Core tables (such as user tables, money-related tables) must have the creation time field create_time and the last update time field update_time of the row data, so that it is easy to check the problem.
• [Recommendation] All fields in the table must be NOT NULL default default value attributes, and the business can define the DEFAULT value as needed. Because the use of NULL values ​​will have problems such as each row will occupy additional storage space, data migration is prone to errors, aggregate function calculation results deviation, and index failure.
• [Suggestion] It is recommended to split the large fields such as blob and text in the table into other tables vertically, and select only when you need to read these objects
• 【Suggestion 】Anti-paradigm design: Make redundant copies of fields that often need to be joined in other tables. For example, the user_name attribute is redundant in the user_account, user_login_log and other tables to reduce join queries.
• [Mandatory] The intermediate table is used to retain the intermediate result set, and the name must start with tmp_. The backup table is used to back up or grab a snapshot of the source table. The name must start with bak_. Intermediate tables and backup tables are cleaned up regularly.
• [Mandatory] For online implementation of DDL changes, it must be reviewed by the DBA and executed by the DBA during the low peak period of the business.

2.1.3. Column data type optimization

• [Recommendation] Auto-increment column in the table (auto_increment attribute) , It is recommended to use the bigint type. Because the storage range of unsigned int is -2147483648~2147483647 (about 2.1 billion), an error will be reported after overflow.
• [Recommendation] For fields such as status and type with few options in the business, it is recommended to use tinytint or smallint to save storage space.
• [Recommendation] The int type is recommended for the IP address field in the business, and char(15) is not recommended. Because int only occupies 4 bytes, you can use the following functions to convert each other, and char(15) occupies at least 15 bytes. Once the number of table data rows reaches 100 million, 1.1G more storage space is needed. SQL: select inet_aton(‘192.168.2.12’); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);
• [Recommendation] Enum, set is not recommended . Because they waste space, and the enumeration value is hard-coded, it is inconvenient to change. It is recommended to use tinyint or smallint.
• [Recommendation] Blob, text and other types are not recommended. They all waste hard disk and memory space. When loading table data, large fields are read into memory, which wastes memory space and affects system performance. It is recommended to communicate with PM and RD whether such a large field is really needed.
• [Recommendation] The field for storing money, it is recommended to use int, the terminal multiplies by 100 and divides by 100 for access. Or use decimal type instead of double.
• [Recommendation] Use varchar to store text data as much as possible. Because varchar is a variable-length storage, it saves more space than char. The MySQL server layer stipulates that all text in a line can store up to 65535 bytes.
• [Recommendation] Try to select datetime as the time type. Although timestamp occupies less space, there is a problem that the time range is from 1970-01-01 00:00:01 to 2038-01-01 00:00:00.

2.1.4. Index Design

• [Mandatory] InnoDB tables must have the primary key id int/bigint auto_increment, and the primary key The value must not be updated.
• [Recommendation] The unique key starts with “uk_” or “uq_”, and the common index starts with “idx_”. All lowercase format is used, with the field name or abbreviation as the suffix.
• [Mandatory] InnoDB and MyISAM storage engine tables, the index type must be BTREE; MEMORY table can choose HASH or BTREE type index as needed.
• [Mandatory] The length of each index record in a single index cannot exceed 64KB.
• [Recommendation] The number of indexes on a single table cannot exceed 5.
• [Recommendation] When creating an index, consider building a joint index, and put the most distinguished field at the top. For example, the distinction of column userid can be calculated by select count (distinct userid).
• [Recommendation] In the SQL of a multi-table join, ensure that there is an index on the join column of the driven table, so that the join execution efficiency is the highest.
• [Recommendation] When building tables or adding indexes, ensure that there are no redundant indexes in the tables. For MySQL, if key(a,b) already exists in the table, key(a) is a redundant index and needs to be deleted.

2.1.5. Sub-database, sub-table, partition table

• [Mandatory] Partition field of the partition table (Partition-key) must have an index, or be the first column of a composite index.
• [Mandatory] The number of partitions (including subpartitions) in a single partition table cannot exceed 1024.
• [Mandatory] Before going online, RD or DBA must specify the partition table creation and cleanup strategy.
• [Mandatory] The SQL used to access the partition table must contain the partition key.
• [Recommendation] A single partition file should not exceed 2G, and the total size should not exceed 50G. It is recommended that the total number of partitions does not exceed 20.
• 【Mandatory】Alter table operation for partition table must be executed during low peak period of business.
• [Mandatory] The number of libraries cannot exceed 1024 if the database is divided into a strategy
• [Mandatory] If the strategy is adopted, the number of tables cannot exceed 4096
• [Recommendation] It is recommended that a single sub-table does not exceed 500W rows, so as to ensure better data query performance.
• [Recommendation] Try to use modulo as much as possible for the horizontal sub-table, and reserve enough buffer to avoid the need to re-split and migrate in the future. It is recommended to use the date for log and report data.

2.1.6. Character Set

• [Mandatory] All character sets of the database itself, tables, and columns must be consistent. It is utf8 or utf8mb4.
• [Mandatory] The character set of the front-end program or the character set in the environment variable must be consistent with the character set of the database and table, unified to utf8.

2.1.7. An example of a standardized table-building statement

A more standardized table-building statement is:< /p>

CREATE TABLE user (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL default ‘0’ COMMENT ‘userid’
  `username` varchar(45) NOT NULL default ‘’ COMMENT ‘real name’,
  `email` varchar(30) NOT NULL default ‘’COMMENT ‘user mailbox’,
  `nickname` varchar(45) NOT NULL default ‘’ COMMENT ‘nickname’,
  `avatar` int(11) NOT NULL default ‘0’ COMMENT ‘avatar’,
  `birthday` date NOT NULL default ‘0000-00-00’ COMMENT ‘birthday’,
  `sex` tinyint(4) not null DEFAULT ‘0‘ COMMENT ‘sex’,
  `short_introduce` varchar(150) not null DEFAULT ‘’COMMENT ‘Introduce yourself in one sentence, up to 50 Chinese characters’,
  `user_resume` varchar(200) NOT NULL default ‘’COMMENT  ‘the storage address of the resume submitted by the user’,
  `user_register_ip` int NOT NULL COMMENT ‘source ip when user registered’,
  `create_time` datetime NOT NULL default current_timestamp COMMENT ‘the time when the user record was created’,
  `update_time` datetime default current_timestamp on update current_timestamp NOT NULL COMMENT ‘the time of user information modification’,
  `user_review_status` tinyint NOT NULL default ‘1’ COMMENT ‘user profile review status, 1 is passed, 2 is under review, 3 is not passed, 4 is not yet submitted for review’,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Basic information of website users';

2.2. SQL writing; h2>

2.2.1. DML statement

• [Mandatory] The SELECT statement must specify the specific field name, and it is forbidden to write it as . Because select will also read data that should not be read from MySQL, causing pressure on the network card. And once the table fields are updated, but the terminal does not have time to update, the system will report an error.
• [Mandatory] The insert statement specifies the specific field name, do not write insert into t1 values(…), the same is true.
• [Suggestion] insert into…values(XX),(XX),(XX)…. The value of XX here should not exceed 500. Too many values, although the online is very fast, but will cause the master-slave synchronization delay.
• [Recommendation] Do not use UNION in the SELECT statement, it is recommended to use UNION ALL, and the number of UNION clauses is limited to 3 or less. Because union all does not need to be deduplicated, it saves database resources and improves performance.
• [Recommendation] The in value list is limited to 500. For example, select… where userid in (…500 within…). This is done to reduce the underlying scanning, reduce the pressure on the database and speed up the query.
• [Suggestion] To update the data in batches in the transaction, you need to control the amount, and do the necessary sleep, and do it in small amounts and multiple times.
• [Mandatory] All tables involved in the transaction must be innodb tables. Otherwise, once it fails, it will not all roll back, and it will easily cause the synchronization of the master and slave libraries to be interrupted.
• [Mandatory] Writes and transactions are sent to the main library, and read-only SQL is sent to the slave library, that is, the terminal realizes read-write separation.
• [Mandatory] DML statements must have where conditions and use index search.
• [Mandatory] Hints are prohibited in the production environment, such as sql_no_cache, force index, ignore key, straight join, etc. Because hint is used to force SQL to execute according to a certain execution plan, but as the amount of data changes, we cannot guarantee that our original prediction is correct. We should try our best to let the MySQL optimizer choose the execution plan by itself.
• [Mandatory] The field types of the left and right equal signs in the where condition must be the same, otherwise the index cannot be used.
• [Recommendation] SELECT|UPDATE|DELETE|REPLACE must have a WHERE clause, and the conditions of the WHERE clause must be searched by index.
• [Mandatory] It is strongly not recommended that a full table scan occurs on large tables in a production database, but a full table scan can be used for static tables with less than 100 rows. The query data volume should not exceed 25% of the number of table rows, otherwise the index will not be used.
• [Mandatory] In the WHERE clause, it is forbidden to use only the fuzzy LIKE condition for searching. If you want to use like, please use the like’xxxx%’ method. There must be other equivalent or range query conditions. Otherwise, the index cannot be used.
• [Recommendation] Do not use functions or expressions in the index column, otherwise the index cannot be used. Such as where length(name)=‘Admin’ or where user_id+2=10023.
• [Recommendation] Reduce the use of or statements, optimize the or statements to union, and then build indexes on each where condition. For example, where a=1 or b=2 is optimized to where a=1…union…where b=2, key(a), key(b).
• [Recommendation] Paging query, when the limit starting point is high, you can use the filter criteria to filter first. For example, select a,b,c from t1 limit 10000,20; optimized to: select a,b,c from t1 where id>10000 limit 20;.

2.2.2. Multi-table join

• [Mandatory] Cross-db join statement is prohibited. Because this can reduce the coupling between modules and lay a solid foundation for database splitting.
• [Mandatory] It is forbidden to use join in business update SQL statements, such as update t1 join t2…
• [Recommendation] It is not recommended to use sub-queries. It is recommended to split the sub-query SQL and combine the program for multiple queries, or use join instead of sub-queries.
• [Recommendation] For online environment, multi-table join should not exceed 3 tables.
• [Recommendation] It is recommended to use aliases for multi-table join queries, and aliases should be used to reference fields in the SELECT list, database. Table format, such as select a from db1.table1 alias1 where ….
• [Recommendation] In a multi-table join, try to select the table with the smaller result set as the driving table to join other tables.

2.2.3. Transactions

• [Recommendation] The number of rows operated by the INSERT|UPDATE|DELETE|REPLACE statement in the transaction is controlled within Within 1000, and the number of parameters passed in the IN list in the WHERE clause is controlled within 500.
• [Suggestion] When operating data in batches, you need to control the transaction processing interval and perform necessary sleep. Generally, the recommended value is 1-2 seconds.
• [Recommendation] For insert operations into tables with auto_increment attribute fields, the concurrency needs to be controlled within 200.
• [Mandatory] Program design must consider the impact of “database transaction isolation level”, including dirty reads, non-repeatable reads, and phantom reads. The online recommended transaction isolation level is repeatable-read.
• [Recommendation] The transaction contains no more than 5 SQL (except payment services). Because too long a transaction will lead to a long time lock data, MySQL internal cache, connection consumption and other avalanche problems.
• [Recommendation] The update statement in the transaction should be based on the primary key or unique key as much as possible, such as update… where id=XX; Otherwise, a gap lock will be generated, and the lock range will be expanded internally, resulting in system performance degradation and deadlock.
• [Recommendation] Try to move some typical external calls out of the transaction, such as calling webservice, accessing file storage, etc., so as to avoid the transaction from being too long.
  [Suggestion] For select statements that are strictly sensitive to the MySQL master-slave delay, please enable transactions to force access to the master database.

2.2.4. Sorting and grouping

• [Recommendation] Reduce the use of order by, and communicate with business without sorting Do not sort, or put the sort in the terminal to do it. Statements such as order by, group by, and distinct consume more CPU, and the CPU resources of the database are extremely precious.
• 【Suggestion】Order by, group by, distinct SQL try to use the index to directly retrieve the sorted data. For example, where a=1 order by can use key(a,b).
• [Suggestion] Contains query statements such as order by, group by, distinct, and the result set filtered by the where condition should be kept within 1000 rows, otherwise the SQL will be very slow.

2.2.5. SQL statements prohibited online

• [High Risk] Disable update|delete t1… where a=XX limit XX; This kind of update statement with limit. If it is a binlog format other than row format, it will lead to inconsistency between master and slave, resulting in data confusion. It is recommended to add order by PK.
• [High Risk] It is forbidden to use associated sub-queries, such as update t1 set… where name in(select name from user where…); The efficiency is extremely low.
• [Mandatory] Disable procedure, function, trigger, views, event, and foreign key constraints. Because they consume database resources, reduce the scalability of the database instance. Recommendations are implemented in the terminal.
• [Recommendation] Disable insert into …on duplicate key update…, replace into and other statements. In a high concurrency environment, it is very easy to cause deadlock.
• [Mandatory] It is forbidden to join table update statements, such as update t1, t2 where t1.id=t2.id…