Shulou(Shulou.com)06/01 Report--

About the library: [mandatory] the name of the library must be limited to 32 characters, all lowercase English. [mandatory] Library name format: business system name _ subsystem name. It is not recommended to use the shulan prefix. [mandatory] Library names can only use letters, numbers, underscores and begin with the English letter. [mandatory] the character set must be explicitly specified when creating the database, and the character set can only be utf8 or utf8mb4. Create database SQL example: Create database db1 default character set utf8; [recommended] temporary library, table name with tmp_ as prefix and date as suffix, backup library and table with bak_ as prefix and date as suffix. About tables

[mandatory] the names of tables and columns must be limited to 32 characters, and table names can only use letters, numbers and underscores, all in lowercase.

[mandatory] the table name requires that the module name is strongly related, and the table name used by the same module uses a uniform prefix as far as possible.

[force] when creating a table, you must explicitly specify the character set as utf8 or utf8mb4.

[mandatory] column names try not to use keywords (such as type,order, etc.).

[mandatory] when creating a table, you must explicitly specify the type of table storage engine. If there is no special requirement, it will always be InnoDB.

[mandatory] comment is required to create a table.

[mandatory] alter table for large tables with more than 100W rows must be audited by DBA and executed during the business trough, and multiple alter needs to be integrated.

Because alter table generates table locks, blocking all writes to the table during the period can have a significant impact on the business.

[suggestion] about primary key when creating a table: the table must have a primary key

(1) the primary key is required to be id, the type is int or bigint, and unsigned unsigned type is recommended for auto_increment.

(2) the field that identifies 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, etc., and establish a unique key index.

Because if the primary key is set as the primary key and the primary key value is randomly inserted, it will result in internal page splitting and a large number of random page O, resulting in performance degradation.

[recommended] Core tables (such as user tables) must have row data creation time field create_time and last update time field update_time to facilitate problem checking.

[recommended] all fields in the table are NOT NULL attributes as far as possible, and business can define default values as needed.

Because of the use of null values, there are some problems, such as extra storage space for each row, error prone to data migration, deviation in the calculation results of aggregate functions, and so on.

[recommended] the intermediate table is used to retain the intermediate result set, and the name must start with tmp_. Backup tables are used to back up or take snapshots of source tables, and the name must start with bak_. Intermediate tables and backup tables are cleaned regularly.

[demonstration] A more standardized table-building statement:

Mysql "> CREATE TABLE user_info (`id`int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-adding primary key', `user_ id`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 day`date NOT NULL COMMENT', `sex`tinyint (4) DEFAULT'0' COMMENT 'gender' Introduce yourself in the sentence `short_ introduce`varchar (150) DEFAULT NULL COMMENT' Up to 50 Chinese characters', 'user_ resume` varchar (300) NOT NULL COMMENT' address of resume submitted by user', 'user_register_ ip` 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 approved, 2 is under review 3: failed, 4: not submitted for review', 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=' website user basic information about index [mandatory] the primary key of InnoDB table must be id int/bigint auto_increment, and the primary key value cannot be updated. [mandatory] InnoDB and MyISAM store engine tables, and the index type must be BTREE. [recommended] the name of the primary key begins with pk_, the unique key begins with uniq_ or uk_, and the ordinary index begins with idx_. All primary keys are in lowercase format and are suffixed with the name or abbreviation of the field. [recommended] the number of indexes on a single table cannot exceed 8. [suggestion] when building an index, more consideration should be given to establishing a joint index, and the fields with the highest degree of differentiation should be put first. For example, the discrimination of the column userid can be calculated by select count (distinct userid). [suggestion] in the SQL of multi-table join, ensure that there is an index on the join column of the driven table, so that join is the most efficient. [suggestion] when building a table or indexing, make sure that there are no redundant indexes in the table.

For MySQL, if key (a) already exists in the table, then key (a) is a redundant index and needs to be deleted. SQL must specify specific field names when writing [forcing] program-side SELECT statements, which are forbidden to be written as *. [force] the program side insert statement specifies the specific field name, which should not be written as insert into T1 values (…) . [force] except for static tables or small tables (less than 100 rows), DML statements must have where conditions and use index lookups. [mandatory] the field type around the equal sign in the where condition must be the same, otherwise the index cannot be utilized. [mandatory] it is forbidden to use only fully fuzzy LIKE conditions for lookups in the WHERE clause. There must be other equivalent or range query conditions, otherwise the index cannot be utilized. [force] Index columns do not use functions or expressions, otherwise the index cannot be utilized. For example, where length (name) = 'Admin' or where user_id+2=10023. [suggestion] insert into... Values (XX), (XX), (XX).. The value of XX here should not exceed 5000.

Too much value can be launched quickly, but it will cause master-slave synchronization delay. [recommended] do not use UNION for SELECT statements, but UNION ALL is recommended, and the number of UNION clauses is limited to 5.

Because union all does not need to be duplicated, it saves database resources and improves performance. [force] prohibit join statements across db. [recommendation] it is not recommended to use subquery. It is recommended to disassemble subquery SQL and combine program to query multiple times, or to use join instead of subquery. [recommended] in online environment, the join of multiple tables should not exceed 5 tables. [suggestion] in multi-table join, try to select the table with small result set as the driver table to join other tables. [recommended] when operating data in batches, it is necessary to control the transaction interval and carry out the necessary sleep. Recommended] contains no more than 5 SQL in the transaction

Because too long transactions will lead to locking data for a long time, MySQL internal cache, connection consumption and other problems. [suggestion] update statements in transactions should be based on primary keys or unique key, such as update. Where id=XX

Otherwise, a gap lock will occur, and the locking range will be expanded internally, resulting in a decline in system performance and a deadlock. [suggestion] reduce the use of order by, communicate with business without sorting, or put sorting on the program side to do. The statements such as Order by, group by and distinct consume more CPU, and the CPU resources of the database are extremely valuable. [recommended] SQL such as order by, group by and distinct try their best to use the index to directly retrieve the sorted data. For example, where axiom 1 order by b can make use of key (afield b). [recommended] contains statements for queries such as order by, group by and distinct. The result set filtered by where condition should be kept within 1000 rows, otherwise SQL will be slow.

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.