MySQL design specifications and principles

MYSQL database design specification

1. Database naming specification

uses 26 English letters (size-sensitive written) and a natural number from 0 to 9 (often not needed) plus an underscore '_';

The name should be concise and clear (the length cannot exceed 30 characters);

For example: user, stat , log, you can also add a prefix to the database with wifi_user, wifi_stat, wifi_log;

Unless it is a backup database, you can add a natural number from 0 to 9: user_db_20151210;

2. Database table name The naming convention

is composed of 26 English letters (case sensitive) and natural numbers from 0 to 9 (often not needed) plus the underscore '_';

The naming is concise and clear. Multiple words are separated by 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 be valid Display tables with the same relationship together;

3. Database table field name naming convention

uses 26 English letters (case sensitive) and 0-9 It consists of a natural number (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 an auto-increment primary key, add_time (default system time)

The associated field names between tables It is required to be as identical as possible;

4. Database table field type specifications

Use as little storage space as possible to store data in one field;

For example: if you can use int, do not use varchar, char, if you can use varchar(16), do not use varchar(256);

It is best to use int type for IP address;

Fixed length It is best to use char for the type, for example: zip code;

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

It is best to give each field a default value, and it is best not to be null;

5. Database table index specifications

The naming is concise and clear. For example: the index of the user_name field of the user_login table should be the unique index of user_name_index;

is the unique index for each Create a primary key index for the table;

Create a reasonable index for each table;

Please be careful when establishing a composite index;

6. Simply familiarize yourself with the database paradigm

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

For example: name field, where surname and The first name is a whole. If you want to distinguish between the last name and the first name, you must set up two independent fields;

Second Normal Form (2NF): A table must have a primary key, that is, each row of data can be uniquely distinguished;

Remarks: The first normal form must be satisfied first;

Third normal form (3NF): A table cannot contain information about non-key fields in other related tables, that is, the data table cannot have redundant fields. ;

Remarks: The second normal form must be satisfied first;

The third normal form of the database:

①Fields are inseparable.

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

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

MYSQL database design principles

1. Core principles

Do not perform operations in the database;

CPU calculation must be moved to the business layer;

Control the number of columns (the number of fields is small and precise, the number of fields is recommended to be within 20);

Balance paradigm and redundancy (efficiency first ; often sacrifice paradigm)

Reject 3B (reject big sql statement: big sql, reject big things: big transaction, reject large batch: big batch);

2, field class Principle

Use numerical types well (use appropriate field types to save space);

characters are converted into numbers (the best conversion can be done, which also saves space and improves query performance) ;

Avoid using NULL fields (NULL fields are difficult to query and optimize, indexes on NULL fields require additional space, and composite indexes on NULL fields are invalid);

Use text types less often (try to use varchar instead text field);

3. Index principles

Use indexes reasonably (to improve queries and slow down updates, the more indexes, the better);

Character fields must build prefix indexes;

Do not perform column operations on the index;

Innodb primary key recommends using auto-increment columns (the primary key creates a clustered index, the primary key should not be modified, string It should not be the primary key) (you will know it if you understand Innodb’s index storage structure);

No foreign keys are used (guaranteed by the program);

4. SQL class principles

The sql statement is as simple as possible (one sql can only be operated on one CPU, large statements are split into smaller statements to reduce lock time, and one large sql can block the entire library);

Simple transactions ;

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

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

OR is rewritten as IN (the efficiency of or is n level);

OR is rewritten as UNION (mysql index merging is very retarded);

select id from t where phone = ’159′ or name = ‘john’;

=>

select id from t where phone=’159′
union
select 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 the overhead of deduplication);

Use less join;

Use group by;

Please use same type comparison;

Batch batch update;

5. Performance analysis tool

show profile;

mysqlsla;

mysqldumpslow;

explain;

show slow log;

show processlist;

Recommended tutorial: "MySQL Tutorial"

The above is the detailed content of MySQL design specifications and principles. For more information, please follow other related articles on the PHP Chinese website!