Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the development specifications of MySQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

# MySQL Development Specification

# # naming rules

Library names, table names, and field names must be in lowercase letters and separated by underscores

Library name, table name and field name are prohibited from exceeding 32 characters. You must see the meaning of the name.

Mysql reserved words are prohibited for library names, table names and field names.

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

Backup library and table names must be prefixed with bak and suffixed with date

Create table user... tmp_user_20160606bak_user_20160606

# # basic Specification

Using the innodb storage engine

The table character set uses utf-8

All tables must be annotated

The amount of data in a single table is controlled within 5000w.

Big data does not store pictures, files, etc. in the database.

Do not do database stress testing online

# # Database Table Design

Prohibit the use of partition tables

Split large fields and fields with low access frequency to separate hot and cold data

Hash the table with hash, the table name suffix uses a decimal number, and the subscript starts at 0

The date and time table should be in the format of YYYY [MM] [DD] [HH].

Adopt appropriate sub-database and sub-table strategy. For example, ten libraries and hundreds of watches, etc.

# # Field Design

All fields are defined as not null

Try not to use TEXT and BLOB types.

Using decimal instead of float and double to store exact floating point numbers

Use tinyint instead of enum type

Using unsigned to store nonnegative integers

Use timestamp to store time

Use int to store timestamps

Using int to store ip

Prohibit the storage of plaintext passwords in the database

# # Index Specification

The number of indexes in a single table shall not exceed 5

The number of fields in a single index does not exceed 5

The prefix index length does not exceed 8 characters

The fields with the highest degree of differentiation are placed in front. Such as: joint index

Try not to select string columns

Do not use foreign keys

Do not use frequently updated columns

Do not use UUID MD5 HASH

Queries that do not use% leading, such as like "% ab"

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

Do not index on low cardinality columns, such as gender

Do not perform mathematical and functional operations in the index column

# # SQL Design

Make full use of prefix index, for example, leftmost prefix

Do not use select *

Two range conditions cannot be used at the same time.

Do not use stored procedures, triggers, views, etc.

Do not use large table join

Do not use mathematical operations and logical judgments

Do not use order by rand ()

Update multiple tables at the same time without using a single sql statement

Use insert more often. On duplicate key update

Use replace into, insert ignore, insert into values (), ()

Use update more often. Where id in (10, 10, 20, 50, 50...)

Use in instead of or. The value of in is no more than 1000.

Use explain diagnostics to avoid generating temporary tables

Use union all instead of union

# good select * from profiles where sex='M' order by rating limit 10; select * from profiles inner join (select from profiles where x.sexuals M' order by rating limit 100000Magne10) as x using (); # bad select * from profiles where sex='M' order by rating limit 100000Mae 10

# # Code of Conduct

Prohibit the existence of application accounts with super permissions

Do not update or query the database in batches during the peak of the business.

Submit online table creation and table modification requirements, all relevant sql statements must be specified in detail

These are the MySQL development specifications that Xiaobian shared with you. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.