Mysql user guide version 20180223, mysql20180223

Mysql user guide version 20180223, mysql20180223

 

 

Force: you cannot manually connect to, query, or change online data on the stepping stone or production server.

Force: All launch scripts must be executed in the test environment before being executed in the production environment.

Force: the online script encoding format is unified to UTF-8

Force: you must use a DNS domain name to access the database. You cannot write an IP address directly.

Suggestion: the database name, table name, and field name must be in lower case. The length should not exceed 15.

 

Force: Table creation _ the new table must contain the auto-incrementing primary key column, and the primary key column should not be modified;

Force: Create Table _ MySQL Character Set utf8mb4 storage engine innodb

Force: Create Table _ fields that may need to be indexed. It is NOT allowed to be empty. It is not null in other fonts.

Force: Create a table _ add field comments (preferably Chinese) for each field of each table)

Force: Table creation _ Foreign keys are not allowed

Force: Table creation _ The enum, set, and bit data types are not allowed

Force: You must specify the default value for the timestamp type when creating a table.

Force: The table creation _ column name cannot contain keywords

Force: Create a table _ to control the data volume of a single table. A single table cannot exceed 2000 million. We recommend that you do not exceed 500 million;

Mandatory: Table creation _ reasonable table sharding: Limit the number of tables in a single database to less than 300, except for possible table sharding in the future;

Force: Create Table _ to control the number of columns. The number of fields is smaller than 20;

Suggestion: it is best to create a table _ auto-increment column as a non-Signed column.

Suggestion: Create Table _ auto-increment columns to be compatible with Discontinuous holes, such

Suggestion: Table creation _ Use less text/blob varchar will have a much higher performance than text. blob cannot be avoided. Please split the table.

Suggestion: Create a table. Do not use HINT to forcibly use indexes.

 

Force: SQL _ does not perform database computing. The cpu computing must be moved to the business layer;

Force: SQL _ disable cross-database query.

Force: SQL _ online programs are not allowed to perform DDL operations

Force: SELECT * is not allowed in SQL _select program code *

Force: at most one SELECT statement in SQL _select program code cannot exceed 50 thousand rows of records

Force: the execution time of a single SELECT statement in SQL _select Code cannot exceed 5 seconds. It is recommended that the execution time not exceed 200 ms.

Force: SQL _ delete. The where condition is not allowed in the update statement.

Force: SQL _ delete, change (update) statement does not use LIMIT

Force: SQL _ delete (delete). The WHERE condition must use indexes for tables with more than 0.5 million rows in the update statement.

Force: SQL _ delete. The number of rows affected by a change statement cannot exceed.

Force: SQL _ delete (delete), update (update). After the INSERT statement affects thousands of rows, it takes 1 second to run the next group. No concurrency, no Multithreading

Suggestion: SQL _ reject 3B reject large SQL statement: big SQL reject large transaction: big transaction reject large batch: big batch

Force: SQL _ large statements remove small statements to reduce lock time; a large SQL statement can block the entire database;

Suggestion: the efficiency of rewriting SQL _OR to IN () or is n level; the log (n) level of in messages;

Suggestion: SQL _OR is rewritten to UNION. In fact, we suggest doing merge in the program, and keep the statement as simple as possible.

Suggestion: We recommend that you set the number of SQL _in instances to less than 200;

Suggestion: the higher the efficient paging limit of SQL _limit, the lower the efficiency. We suggest using id> $ last_selected_id limit 10;

Suggestion: SQL _ replacing union with union all has the deduplicated overhead.

Suggestion: SQL _ join is used less; subquery is not used, and subquery is converted to JOIN for execution

Suggestion: use the same type of comparison for SQL _. type conversion occurs for different types of comparison. When type conversion occurs on the index field, indexes cannot be used, it is likely to cause slow queries to scan tables.

Suggestion: SQL _ batch UPDATE/DELETE, UPDATE/DELETE a SQL statement to operate hundreds of thousands or millions of rows of data, split into small SQL statements, batch operations, reduce the impact on the database.

 

Mandatory: Index _ a table, which can contain up to 16 indexes

Mandatory: Index _ a maximum of five columns can be contained in an index.

Force: The index _ does not allow duplicate indexing. Redundant indexing is allowed in special cases, but a clear reason must be given.

Force: Index _ full-text index not used

Mandatory: Index _ long character fields must have a prefix Index

Suggestion: The index _ the more the index, the better. (If you can add it without adding it, you must add it );

Suggestion: Check whether the unique index prefix is "uniq _"