005-mysql Database Design Principles

1. Core Principles

Do not operate in the database;
CPU computing must be moved to the business layer;
Number of control columns (field few but good, number of fields recommended within 20);
Equilibrium paradigm and redundancy (efficiency first; often sacrificial paradigm)
Reject 3B (Deny large SQL statement: Big SQL, reject large things: big transaction, reject Mass: big batch);

2. Field class principle

Use a good value type (save space with the appropriate field type);
Convert characters into numbers (the best conversions that can be transformed, as well as saving space and improving query performance);
Avoid using null fields (Null fields are difficult to query for optimizations, NULL field indexes require extra space, invalid composite index for null fields);
Less text type (use varchar instead of the text field as much as possible);

3. Index class principle

Rational use of indexes (improve the query, slow down the update, the index must not be more the better);
The character field must have a prefix index;
Do not perform column operations in the index;
InnoDB the primary key is recommended to use the self-increment column (the primary key to establish the clustered index, the primary key should not be modified, the string should not be the master key) (Understand the InnoDB index preservation structure to know);
No foreign keys (guaranteed by the program);

4. SQL class principle

SQL statements as simple as possible (a SQL can only be in a CPU operation, large statements to split small statements, reduce lock time, a large SQL can block the entire library);
A simple transaction;
Avoid using TRIG/FUNC (triggers, functions are not replaced by client programs);
No SELECT * (Consumes Cpu,io, memory, bandwidth, this program is not extensible);
or overwrite to in (or efficiency is n level);
or rewrite to union (MySQL index merge is mentally 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 to%;
Use COUNT (*) with caution;
Limit efficient paging (the greater the limit, the less efficient);
Use UNION ALL instead of union (union has a de-heavy cost);
Use less connection join;
Use GROUP by;
Please use the same type comparison;
Break up batch update;

5. Performance analysis Tools

Show profile;
Mysqlsla;
Mysqldumpslow;
Explain:http://www.cnblogs.com/bjlhx/p/7567110.html
Show slow log;
Show Processlist;

005-mysql Database Design Principles