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

Today, the editor will bring you an article about the design specifications and principles of MySQL database. The editor feels that the summary is in place, so I will share it for you as a reference. Let's follow the editor and have a look.

1. Database naming specification

It is composed of 26 English letters (case-sensitive) and 0-9 natural numbers (often not needed) plus an underscore'_'.

The naming is concise and clear (the length cannot exceed 30 characters)

For example, user, stat, log, or wifi_user, wifi_stat, wifi_log can prefix the database.

Unless it is a backup database, you can add a natural number of 0-9: user_db_20151210

2. Database table name naming convention

It is composed of 26 English letters (case-sensitive) and 0-9 natural numbers (often not needed) plus an underscore'_'.

The naming is concise and clear, and multiple words are separated by an underscore'_'.

For example: user_login, user_profile, user_detail, user_role, user_role_relation

User_role_right, user_role_right_relation

The table prefix 'user_' can effectively display tables with the same relationship together.

3. Naming specification for database table field names

It is composed of 26 English letters (case-sensitive) and 0-9 natural numbers (often not needed) plus an underscore'_'.

The naming is concise and clear, and multiple words are separated by an underscore'_'.

For example: user_login table fields user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time

Each table must have a self-incrementing primary key, add_time (default system time)

The associated field names between the table and the table should be as identical as possible

4. Database table field type specification

Use as little storage space as possible to store the data of a field

For example, do not use varchar or char if you can use int, and do not use varchar if you can use varchar (16).

It is best to use the int type for IP addresses

It is best to use char for fixed length types, for example, zip code

If you can use tinyint, do not use smallint,int.

It is best to give each field a default value, preferably not null.

5. Database table index specification

The name is concise and clear, for example: the index of the user _ name field of the user_ log table should be the user_name_index unique index

Create a primary key index for each table

Create a reasonable index for each table

Please be careful in establishing compound index

6. Be familiar with the database paradigm simply

First normal form (1NF): field values are atomic and cannot be further divided (all relational database systems meet the first normal form)

For example: name field, where the last name and first name are a whole, if you distinguish between last name and first name, you must set up two separate fields

Second normal form (2NF): a table must have a primary key, that is, each row of data can be uniquely distinguished.

Note: the first paradigm must be satisfied first.

The third normal form (3NF): a table cannot contain information about non-key fields in other related tables, that is, a data table cannot have residual fields.

Note: the second paradigm must be satisfied first.

Three paradigms of database:

The ① field is not separable.

② has a primary key, and non-primary key fields depend on the primary key.

③ non-primary key fields cannot depend on each other.

Design principles of MYSQL Database

1. Core principles

Do not do operations in the database

Cpu computing must be moved to the business layer

Control the number of columns (fields are few but fine, and it is recommended that the number of fields be less than 20)

Balance paradigm and redundancy (efficiency first; paradigm is often sacrificed)

Reject 3B (reject large sql statements: big sql, reject big things: big transaction, reject large quantities: big batch)

2. Principle of field class

Make good use of numeric types (save space with appropriate field types)

Convert characters to numbers (the best conversion that can be converted, also save space and improve query performance)

Avoid using NULL fields (the NULL field is difficult to query and optimize, the index of the NULL field requires extra space, and the composite index of the NULL field is invalid)

Use less text types (try to use varchar instead of text fields)

3. The principle of index

Rational use of indexes (improve queries, slow down updates, indexes must not be as many as possible)

Character fields must be indexed with a prefix

Do not do column operations in the index

Self-incrementing columns are recommended for innodb primary keys (primary keys are clustered, primary keys should not be modified, and strings should not be used as primary keys) (just understand the index preservation structure of Innodb)

No foreign keys (constraints guaranteed by the program)

4. SQL principles

The sql statement is as simple as possible (a sql can only operate on one cpu, large statements break down small statements, reduce lock time, and a large sql can block the entire library)

A simple transaction

Avoid using trig/func (triggers and functions are not replaced by client programs)

No select * (consumes cpu,io, memory, bandwidth, this kind of program is not scalable)

Rewrite OR to IN (the efficiency of or is n-level)

OR is rewritten to UNION (the index merge of mysql is very retarded)

Select id from t where phone = '159' or name =' john'

= >

Select id from t where phone='159'unionselect id from t where name='jonh'

Avoid negative%

Use count (*) with caution

Limit efficient paging (the larger the limit, the lower the efficiency)

Use union all instead of union (union has deduplication overhead)

Use less connection join

Use group by

Please use the same type comparison

Break up batch updates

5. Performance analysis tools

Show profile

Mysqlsla

Mysqldumpslow

Explain

Show slow log

Show processlist

These are the details of the design specifications and principles of the MySQL database. Is there anything to gain after reading it? If you want to know more about it, you are welcome to follow the industry information!

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.