MySQL Shallow turtle set

One, try not to be a word database do operations

1, try not to do the database operation,

2, move the complex operation to the CPU

3, apply MySQL as simple as possible

Second, the Control table data volume

1, Pure int no more than 1000w

2, with char not exceeding 500w

3, recommended trousers no more than 300-400 tables

Three, keep the table slim figure

1, number of fields in the table few but good

2, the maximum number of fields in a single table is 20~50

Four, balance paradigm and redundancy

1, efficiency first, improve performance

2, there is no absolute right and wrong

3, appropriate sacrificial paradigm, added redundancy

Five, Reject 3B

1, Big SQL

2, big Business

3, large volume

Six, field set turtle

1. Convert characters to numbers (more efficient, faster query, less space)

2, precedence using Enum or set (value known or limited, such as gender)

3. Avoid using null fields (it is difficult to optimize queries, indexes require extra space, and null composite indexes are not valid)

4, try not to use TEXT/BLOB (must be used when splitting to a separate table)

5, no longer data stock images

Seven, index shallow turtle set

1, add the index cautiously

2, preferably no more than the number of fields 20%

3, prefix the field

4, do not operate on indexed columns

Eight, primary key settings

1, the level two index stores the primary key value 2, the primary key should not be updated or modified 3, according to the increment order to insert the value 4, avoid using the string key 5, if the primary key is not specified, InnoDB will use a unique and non-null value index instead

Nine, transaction, trigger, Or,limit light Turtle 1, transaction/Connection Usage principles: instant-on, out-of-the-box

2, transaction-independent operations are placed outside the transaction, reducing the lock resource footprint

3, without compromising consistency, use multiple short transactions instead of long transaction 4, with as few stored procedures as possible

5, use as little as possible trigger 6, reduce the use of MySQL function to process the results (the client program is responsible for)

7, try not to apply select *, only the required data columns

8, change or for in () "or efficiency O (n), in efficiency O (log n)" control the number of in, recommended less than 2009, change or to Union

10, reduce the use of count (*)

11,limit (the larger the offset, the slower it becomes)

Select Id,col1,col2 from Test limit 1999999, 10;

To be replaced by:

Select Id,col1,col2 from test where id>1999999 limit 10;

Miscellaneous Light Turtle Set:

1, the appropriate decomposition of the connection to ensure efficient concurrency

2, never the program end explicitly lock "external lock on the database is not controllable, high concurrency is a disaster"

3, unified character set is Utf-8

4, Uniform naming convention the library table name is unified lowercase, the index name defaults to "idx_ field name", and the Library name is abbreviated "

MySQL Shallow turtle set