You ask God for help because you believe in him; God doesn’t help you because he believes in you.
About MySQL database specification, I believe you have read some documents. This article gives you a detailed classification and summary of the database related specifications, from the database table naming design specification to the index design specification, and then gives suggestions on SQL writing. I believe these specifications are applicable to most companies, and we hope that everyone can use our database according to the specification, so that our database can play a higher performance.
- [mandatory] the name of the library must be controlled within 32 characters, and all English characters should be lowercase.
- Name format of mandatory database: business system name_ Subsystem name.
- [mandatory] library names can only use English letters, numbers, and underscores, and start with English letters.
- [mandatory] when creating a database, the character set must be explicitly specified, and the character set can only be utf8 or utf8mb4. Create database SQL example:
Create database db1 default character set utf8;
- [suggestion] the name of temporary database and table should be
tmp_The backup database and table are prefixed with the date as the suffix
bak_Is prefixed with a date suffix.
- [mandatory] table and column names must be controlled within 32 characters. Table names can only use letters, numbers and underscores, all in lowercase.
- Mandatory name is the first mock exam. The name of the table is strongly related. The same name is used in the same module.
- [mandatory] when creating a table, the character set must be explicitly specified as utf8 or utf8mb4.
- [mandatory] try not to use keywords (such as type, order, etc.) for column names.
- Mandatory: when creating a table, you must explicitly specify the table storage engine type. If there is no special requirement, it should be InnoDB.
- [mandatory] table creation must have a comment.
- [mandatory] for large tables with more than 100W rows, alter table must be reviewed by DBA and executed in the low peak period of business. Multiple alters should be integrated together.
Because the alter table will produce a table lock, blocking during the period may have a great impact on all writes to the table.
- [suggestion] when creating tables, about primary keys: tables must have primary keys
(1) The primary key must be ID, type int or bigint, and auto_ It is recommended to use the unsigned type for increment.
(2) The field identifying the body of each row in the table should not be set as the primary key. It is recommended to set it to other fields such as user_ id，order_ ID, and create a unique key index.
If it is set as the primary key and the primary key value is randomly inserted, it will lead to page splitting and a large number of random I / O in InnoDB, and the performance will be degraded.
- [suggestion] the core table (such as user table) must have the creation time field of row data create_ Time and last update time fields update_ Time, easy to check the problem.
- [suggestion] all the fields in the table should be not null as far as possible, and the business can define the default value as required.
Because of using null value, each row will take up extra storage space, data migration is prone to errors, and aggregate function calculation results deviation and so on.
- [suggestion] the intermediate table is used to keep the intermediate result set. The name must be
tmp_start. The backup table is used to backup or snapshot the source table. The name must be
bak_start. The intermediate table and backup table are cleaned up regularly.
[demonstration] a more standard table building statement:
CREATE TABLE user_info ( `id` int unsigned NOT NULL AUTO_ Increment comment 'Auto increment primary key', `user_ ID ` bigint (11) not null comment 'user ID', `Username ` varchar (45) not null comment 'real name', `Email ` varchar (30) not null comment 'user mailbox', `Nickname ` varchar (45) not null comment 'nickname', `Birthday ` date not null comment 'birthday', `Sex ` tinyint (4) default '0' comment 'gender', `short_ Introduction 'varchar (150) default null comment' introduce yourself, with 50 Chinese characters at most ', `user_ Resume ` varchar (300) not null comment 'user submitted resume storage address', `user_ register_ IP ` int not null comment 'the source IP when the user registered', `create_ time` timestamp NOT NULL DEFAULT CURRENT_ Timestamp comment 'creation time', `update_ time` timestamp NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ Timestamp comment 'modification time', `user_ review_ Status ` tinyint not null comment 'user data audit status: 1 is passed, 2 is being reviewed, 3 is not passed, 4 is not submitted for audit', PRIMARY KEY (`id`), UNIQUE KEY `uniq_user_id` (`user_id`), KEY `idx_username`(`username`), KEY `idx_create_time_status`(`create_time`,`user_review_status`) ）Engine = InnoDB default charset = utf8 comment ='basic information of website users'
- [mandatory] InnoDB table must have primary key id int / bigint auto_ Increment, and the primary key value cannot be updated.
- [mandatory] InnoDB and MyISAM storage engine tables. The index type must be BTREE.
- [suggestion] the name of the primary key should be
pk_The unique key starts with
uk_The normal index starts with
idx_At the beginning, always use lowercase format, with the field name or abbreviation as the suffix.
- [suggestion] the number of indexes on a single table cannot exceed 8.
- [suggestion] when building an index, we should consider building a joint index, and put the field with the highest discrimination first. For example, the distinguishability of the column userid can be determined by
select count(distinct userid)Calculate it.
- [suggestion] in SQL for 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.
- [suggestion] when creating a table or adding an index, ensure that there is no redundant index in each table.
For MySQL, if key (a, b) already exists in the table, then key (a) is redundant index and needs to be deleted.
- [mandatory] program side select statement must specify a specific field name, and cannot be written as *.
- [force] program side insert statement specifies the specific field name. Do not write it as insert into T1 values (…) )。
- [mandatory] except for static tables or small tables (within 100 rows), DML statements must have a where condition and use index search.
- [mandatory] the types of the fields around the equal sign in the where condition must be consistent, otherwise the index cannot be used.
- [mandatory] in the where clause, it is forbidden to use only the fully fuzzy like condition for searching. There must be other equivalent or range query conditions, otherwise the index cannot be used.
- [force] do not use function or expression for index column, otherwise index cannot be used. For example, where length (name) =’admin ‘or where user_ id+2=10023。
insert into…values(XX),(XX),(XX)..The value of XX should not exceed 5000.
If the value is too high, it will cause the delay of master-slave synchronization.
- [recommendation] do not use union in select statements. Union all is recommended, and the number of union clauses is limited to 5.
Because union all does not need to remove duplicate, it saves database resources and improves performance.
- Join statements across dB are prohibited.
- [suggestion] it is not recommended to use subquery. It is recommended to split the subquery SQL and combine it with the program for multiple queries, or use join instead of subquery.
- [suggestion] in the online environment, multiple table join should not exceed 5 tables.
- [suggestion] in multi table join, select the table with smaller result set as the driving table to join other tables.
- [suggestion] for batch operation of data, it is necessary to control the transaction interval and make necessary sleep.
- [suggestion] the transaction contains no more than 5 SQL
Because too long transaction will lead to long lock data, excessive consumption of internal cache and connection in MySQL.
- [suggestion] update statements in transactions should be based on the primary key or unique key, such as update where id=XX;
Otherwise, the gap lock will be generated, and the locking range will be expanded internally, which will lead to system performance degradation and deadlock.
- [suggestion] reduce the use of order by, and communicate with the business to avoid sorting, or put sorting on the program side. Order by, group by, distinct statements consume CPU, and the CPU resources of database are extremely valuable.
- [suggestion] SQL like order by, group by and distinct can be used to retrieve sorted data directly. For example, where a = 1 order by B, key (a, b) can be used.
- [suggestion] it contains query statements such as order by, group by and distinct. The result set filtered by the where condition should be kept within 1000 lines, otherwise SQL will be very slow.