Regulations on mysql development in the market

Preface to the 36 military rules for mysql development on the market: the importance of disaster tolerance is always remembered after a disaster occurs. I remember someone reminded me that I had suffered a loss. (1) core military rules (1) do not perform operations in the database: the cpu computing must be moved to the business layer (2) control the data volume in a single table: the number of records in a single table is controlled at 1000 (3: word

Preface to the 36 military rules for mysql development on the market: the importance of disaster tolerance is always remembered after a disaster occurs. I remember someone reminded me that I had suffered a loss. (1) core military rules (1) do not perform operations in the database: the cpu computing must be moved to the business layer (2) control the data volume in a single table: the number of records in a single table is controlled at 1000 (3: word

36 military rules for mysql development on the market

Preface:
The importance of disaster tolerance is always remembered after a disaster occurs;
I remember someone reminded me that I have always suffered a loss.

(1) core military rules
(1) do not perform operations in the database: the cpu computing must be moved to the business layer.
(2) control the data volume of a single table: the data volume of a single table is controlled at 1000
(3) control the number of columns: the number of fields is limited to 20
(4) Balanced paradigm and redundancy: designed to improve efficiency at the expense of paradigm, redundant data
(5) reject 3B: reject large SQL statements, large transactions, and large batches

(2) field military rules
(6) make good use of the numerical type
Tinyint (1 Byte)
Smallint (2 Byte)
Mediumint (3 Byte)
Int (4 Byte)
Bigint (8 Byte)
Bad case: int (1)/int (11)
(7) convert characters to numbers
Use int instead of char (15) to store ip addresses
(8) enum or set is preferred.
Example: 'sex' enum ('F', 'M ')
(9) Avoid using NULL Fields
It is difficult to query and optimize NULL fields.
The index of the NULL field requires additional space.
The compound index of the NULL field is invalid.
Bad case:
'Name' char (32) default null
'Age' int not null
Good case:
'Age' int not null default 0
(10) Use less text/blob
The performance of varchar is much higher than that of text.
Blob cannot be avoided. Split the table.
(11) do not store images in the database: Do you need to explain them?

(3) Indexing military rules
(12) exercise caution and make rational use of Indexes
Improves queries and slows down updates
The more indexes you need, the better. (If you do not add indexes, you must add indexes)
Too many records are covered, which is not suitable for indexing, such as "gender"
(13) prefix indexes must be created for character Fields
(14) column operations are not performed on Indexes
Bad case:
Select id where age + 1 = 10;
(15) Auto-increment columns are recommended for innodb primary keys (SK: not recognized by bloggers)
Create a clustered index with the primary key
Primary Key should not be modified
The string should not be the primary key.
If no primary key is specified, innodb uses a unique and non-null index instead.
(16) do not use foreign keys
Please be guaranteed by the program

(4) SQL military rules
(17) SQL statements should be as simple as possible
One SQL statement can only be operated on one cpu
Remove large statements to reduce lock time
A large SQL statement can block the entire database
(18) Simple transactions
The transaction time is as short as possible
Bad case:
Upload image transaction
(19) Avoid using trig/func
Triggers and functions are not required
Replace the client program
(20) No select *
Cpu, io, memory, and bandwidth consumption
This type of program is not scalable
(21) OR rewrite to IN ()
Or is n-level.
Log (n) level for in messages
We recommend that you set the number of in instances to less than 200.
Select id from t where phone = '000000' or phone = '000000 ′;
=>
Select id from t where phone in ('000000', '000000 ′);
(22) OR rewrite to UNION
Mysql index merge is very retarded
Select id from t where phone = '000000' or name = 'john ';
=>
Select id from t where phone = '000000 ′
Union
Select id from t where name = 'jonh'
(23) Avoid negative %
(24) Use count with caution (*)
(25) Same as above
(26) Efficient limit Paging
Higher limit, lower efficiency
Select id from t limit 10000, 10;
=>
Select id from t where id> 10000 limit 10;
(27) replace union with union all
Union has deduplication overhead
(28) join less
(29) use group
Group
Automatic Sorting
(30) use the same type of comparison
(31) use load data to import data
Load data is about 20 times faster than insert;
(32) Batch update
(33) New Energy Analysis Tools
Show profile;
Mysqlsla;
Mysqldumpslow;
Explain;
Show slow log;
Show processlist;
Show query_response_time (percona)