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

This article will explain in detail what MySQL development specifications are, Xiaobian thinks it is quite practical, so share it with you as a reference, I hope you can gain something after reading this article.

base specification

Using innodb storage engine

Table character set uses utf-8

All tables add comments

It is suggested that the data volume of single table should be controlled within 5000W.

No longer store big data such as maps and files in the database

Do not perform database stress tests online

Test and development environments are prohibited.

naming conventions

Library Name Table Name Field name must have a fixed naming length, within 12 characters

Library name, table name and field name are prohibited to have more than 32 characters. Must see the meaning of the name

Database name, table name, field name MySQL reserved words are prohibited

Temporary library and table names must be prefixed with tmp and suffixed with date

Backup libraries and tables must be prefixed with bak and suffixed with date

Design Specification for Library, Table and Field Development

Prohibiting partition tables

Split large fields and fields with low turn-over frequency to separate hot and cold data

HASH table, table name suffix decimal number, subscript starting from 0

Table by date and time shall conform to YYYY[MM][DD][HH] format

Adopt appropriate strategy of dividing storehouse and table. For example, thousands of libraries and ten tables, ten libraries and hundreds of tables, etc.

As far as possible text, blob type

Decimal instead of float and double to store precise floating-point numbers

The simpler the better: convert characters to numbers, use tinyint instead of enum type

All fields are not null

Using unsigned to store nonnegative numbers

int type fixed 4 bytes of storage

Use timestamp to store time

Using int unsigned to store ipv4

Use varbinary to store case-sensitive variable-length strings

It is forbidden to store plaintext passwords in the database, and store passwords after encryption

Make good use of numeric type fields

type

bytes

minimum value

maximum value

tinyint

1

-128

127

smallint

2

-32768

32767

mediumint

3

-8388608

8388607

int

4

-2147483648

2147483647

bigint

8

-9223372036854775808

9223372036854775807

Don't use bigint if the numeric field isn't that big

IP is best stored as int rather than char(15)

Enum is not allowed

Avoid null fields

null field is difficult to query a flower, null field index needs extra space, null field composite index invalid.

index specification

No more than 5 indexes in a single table

No more than 5 fields in a single index

Prefix indexes are used for strings with prefix indexes no longer than 8 characters

It is recommended to give priority to prefix index. If necessary, pseudo-columns can be added and indexes established.

Table must have primary key

Do not use frequently updated columns as primary keys

Try not to select string columns as primary keys

Do not use uuid md5 hash these as primary keys-too discrete

By default, non-null unique keys are used as primary keys

It is recommended to choose self-increment or signal generator

Important SQL must be indexed, such as update, delete where condition column, order by, group by, distinct field

Multi-table join fields Note

1. The most discriminating field comes first.

2. Core SQL gives priority to overwriting indexes

3. Avoid redundant and duplicate indexing

4. The index comprehensively evaluates data density and distribution, as well as considering query and update ratios

index taboo

1. Do not index on low cardinality columns, e.g. gender

2. Not indexed list memory mathematical operations and functional operations

Try not to use foreign keys

1. Foreign keys are used to protect referential integrity and can be implemented on the business side.

2. Operations on parent and word tables affect each other, reducing usability

index naming

1. Non-unique index must be inx_field1_field22. Unique index must be uniq_field1_field2

Default value of index field cannot be empty. null greatly affects the query efficiency of the index.

Repeatedly look at SQL related to tables, and establish indexes in accordance with the characteristics of the leftmost prefix. For indexes with multiple duplicate fields, to modify the order of statement condition fields, establish a joint index for them and reduce the number of indexes.

Use a unique index if you can

R & D should often use explain, and if index selectivity is found to be poor, they must learn to use hint.

SQL specification

SQL statements are as simple as possible

Things should be simple, and the whole thing should not take too long to produce

Avoid triggers, functions, stored procedures

Reduce the degree of service coupling, leaving room for socket out, sharding

Avoid mathematical operations in databases, mysql is not good at mathematical operations and logical judgments

Do not use select *, query those fields and select those fields

In sql, the use of or is rewritten as in, and the efficiency of or is not as high as in.

The number of numbers in the recommended 1000

Limit paging attention efficiency. The higher the limit, the lower the efficiency.

Use union all instead of union

Avoid making large tables join

Use group bu grouping, automatic sorting

Update the data in batches after breaking it up. Don't update too much data at once.

Reduce the number of interactions with databases

Note the use of performance analysis tools

SQL statement requires all R & D, SQL keywords are all capitalized, and only one space is allowed for each word.

SQL statements cannot have implicit conversions

Not in, not in.

Prohibited use of like with prefix %

Do not use negative queries, such as not in, not like

Prohibiting running large queries in the database

Making precompiled statements pass only arguments is more efficient than passing SQL statements. Reduce SQL injection.

Prohibiting order by rand

A single SQL statement is prohibited from updating multiple tables simultaneously

process specifications

All table creation operations need to inform the query SQL involved in the table in advance

All tables need to be determined after which indexes are established before they can be built online

All table structure modification and index addition operations need to involve the query SQL of the table to notify DBAs and other relevant personnel.

Before adding fields to new tables, R & D is required to send an email at least 3 days in advance to evaluate, optimize and review dba doors.

Batch import and export data must be notified in advance to assist DBA observation

Prohibiting online execution of backend administrative and statistical queries from libraries

Application accounts with forbidden super permissions exist

No longer batch update and query database during peak business hours

What about MySQL development specifications are shared here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.