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

The following mainly brings you the most practical military rules on MySQL. I hope these contents can bring you practical use, which is also the main purpose of my article on editing the most practical military rules on MySQL. All right, don't talk too much nonsense, let's just read the following.

I. Core military regulations

-do not do operations in the database: cpu computing must be moved to the business layer

-Control the amount of data in a single table: record in a single table at 1000W

-Control the number of columns: the number of fields is limited to 20.

-balancing paradigm and redundancy: sacrificing paradigm design for efficiency, redundant data

-reject 3B: reject big sql, big things, large quantities

II. Field military regulations

-make good use of numerical types

Tinyint (1Byte)

Smallint (2Byte)

Mediumint (3Byte)

Int (4Byte)

Bigint (8Byte)

Bad case:int (1) / int (11)

-convert characters to numbers

Using int instead of char (15) to store ip

-avoid using NULL fields

The NULL field is difficult to query and optimize.

The index of the NULL field requires extra space

The composite index of the NULL field is invalid

-use less text/blob

The performance of varchar will be much higher than text.

Blob cannot be avoided, please disassemble the meter

-do not store pictures in the database

III. Indexing military regulations

-prudent and rational use of indexes

Improve queries and slow down updates

It must not be as many indexes as possible (if you can not add it, you must add it).

Too many overwrite records are not suitable for indexing, such as "gender"

-character fields must be indexed with a prefix

-do not do column operations in the index

Bad case:

Select id where age + 1 = 10

-self-incrementing column is recommended for innodb primary key

Primary key to establish clustered index

The primary key should not be modified

String should not be the primary key

If you do not specify a primary key, innodb uses a unique and non-null index instead

-No foreign keys are needed

Please be bound by the program.

Fourth, sql class military regulations

The-sql statement is as simple as possible

A sql can only be operated on one cpu.

Big statement disassembles small statement to reduce lock time

One big sql can block the whole library.

-simple transaction

Transaction time is as short as possible

Bad case:

Upload picture transaction

-avoid using trig/func

Flip-flops, functions are not used

The client program takes its place

For the above about the most practical military rules on MySQL, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like 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.