MySQL Design Specification (cont.)

1.1    database field Design specification

prioritize the smallest data types that meet storage needs :

The non-negative data is stored by the unsigned integer type;

varchar (N) in N represents the number of characters, not the number of bytes ( Oracle is the number of bytes, such as the Chinese GBK 2 bytes in a Chinese character), that is, the MySQL varchar (2) can store 2 Chinese characters, occupy 6 bytes (UTF8).

Avoid using text,blob data type:

the common text type can store 64k, which is generally not used in such a large space;

To use it, it is recommended that you separate the blob or text column into a separate extension table and avoid using select *;

Text, the Blob can only use the prefix index, and the text,blob column cannot have a default value by default.

Avoid using ENUM Data type :

Modify The ENUM value requires an ALTER statement;

the ORDER by operation of the ENUM type is inefficient and requires additional operation;

prohibit use of numeric values as The enumeration value of the enum.

define all columns as notnull as possible :

Index The null column takes up extra space to save (or is empty), so it takes up more space;

compare and calculate the NULL does special processing, and the index may become invalid.

Use TIMESTAMP or DATETIME type storage time:

TIMESTAMP 1970-01-01~2038-01-19, occupies 4 bytes and the same as INT storage;

Other scopes used DATETIME type storage.

Financial Financial related data, using DECIMAL Type:

The occupied space has a defined width decision;

can be used to store integer data that is larger than bigint.

1.2    DatabaseSqlDevelopment Specifications

We recommend that you use pre-compiled statements for database operations:

pass only arguments, than passing SQL statements are more efficient;

The same statement can be resolved once, use multiple times, and improve processing efficiency.

Avoid implicit conversions of data types :

Implicit conversions can cause an index to fail.

make the most of an existing index on a table :

avoid using double -percent query criteria, such as ' 123% ' to use the index;

a SQL can use only a single column of federated indexes for scope queries;

PS:index (A, B, c),where a = Xxand B > Low and B < high can use the index to column B,

Wherea > Low and a < high and B = xx,b This column is not used in the index;

This processing is consistent with the federated Index of Oracle.

use the left join or NOT exists to optimize the not in operation.

To disallow cross-Library queries:

The program connects different databases using different accounts;

(a) Make room for database migrations and sub-tables;

Reduce the coupling degree of the business;

avoid the security risks caused by too much authority, SQL injection also sees only one library.

Disable the use of Select * :

excessive consumption of CPU, IO, network resources;

Cannot use overwrite index;

Can reduce the impact of table structure changes.

Disallow inserts without a field list :

INSERT into T (A, B) VALUES (...);

Can reduce the impact of table structure changes.

try to avoid using subqueries, you can optimize the query to join Operation:

Of course, not all sub-queries can be converted to join, such as group by , such as sub-query;

is usually in sub-queries such statements.

The subquery result set cannot use the index; the subquery generates temporary table operations, and if the subquery data volume is heavily impacting efficiency; consuming too much CPU,IO.

Avoid using JOIN Too many tables are associated:

every Join A table takes up a portion of memory (join_buffer_size);

will generate temporary table operations, affecting query efficiency;

MySQL is allowed to correlate up to a maximum of 5 tables, with no more than one recommended .

Reduce the number of interactions with the database:

Database is more suitable for batch operation;

Merge multiple identical operations together;

PS: such as ALTER TABLE T addcolumn C1 int, change column C2 c2 int ...

use in instead of or:

the value of in does not exceed four;

the in operation can effectively utilize the index.

prohibit use of order Byrand () to randomly sort:

The data in the table will be loaded into memory for sorting;

consume a lot of Cpu/io/mem;

Processing: It is recommended to get a random value in the program and then get the data in the database (pro-think developers are willing to implement redundant algorithms?). All want a SQL to return a result set).

WHERE clauses prohibit function conversions and calculations on columns:

where date (createtime) = ' 20171010 ' à where createtime >= ' 20171010 ' and Createtime < ' 20171011 '

UnionAll OR UNION :

these two sorts of methods and Oracle is fully consistent,union ALL is not sorted, andunion is sorted.

Split Complex SQL is multiple small sql:

MySQL a SQL can only be calculated using one CPU ;

SQL split can improve processing efficiency through parallel queries.

1.3    database operation behavior Specification

more than 100W rows of data in bulk DML operations are performed several times in batches :

Need to consider the master-slave delay;

Binlog logs generate a large number of logs for the row format;

Avoid the operation of large transactions, blocking parallelism.

Large table Table structure modification :

It is recommended to use Pt-online-schema-change to modify table structure;

The active delay caused by large table modification can be avoided;

Avoid locking tables When you modify a table field.

prohibit granting super to program account Permissions:

When the maximum number of connections is reached, a user connected with super privileges is also allowed;

The Super privilege can only be used by the DBA to handle the problem account.

For program accounts, follow the principle of least privilege:

The program uses the database account only in one DB is not allowed for cross-library use;

The program account does not allow drop permissions in principle.

MySQL Design Specification (cont.)