MySQL Development specification

1: Library table Design

• The library name, table name, field names are in lowercase letters and are underlined, and the tables for related functions should use the same prefix, such as member_xxx, where the prefix is usually a library name or a dependent master entity object;
• All tables and fields must have notes, a detailed table, and the meaning of the field
• Data that involves currency amounts or other precision sensitive must use fixed-point decimal to replace float and double
• Library name, table name, field name prohibit the use of MySQL reserved words, such as data, like, DESC, etc.
• Control table field number, the single table does not exceed 50 pure int/20 a varchar (10) field equals the number of fields of the storage volume, the upper limit is controlled in 20~50

2: Index Design

Basic rule: The index is not the more the better, can not add the index as far as possible without index, too many indexes can seriously reduce the efficiency of insert and update, and bring read-write conflict and deadlock

• The index name must be lowercase, the normal index is named according to the Idx_ field name _ Field name, and the unique index is name by uniq_ field name _ Field name [_ Field name]
• The table must have a primary key, it is recommended to use the Independent Business auto_increment column or the Global ID Generator master key, prohibit the use of multiple fields to do the federated primary key
• Do not use functions such as uuid/md5/hash to generate non-rule values of the key, the efficiency is very poor
• Index Quantity Control

1. The number of indexes in a single table does not exceed 5
2. The number of fields in a single index does not exceed 5
3. Use a prefix index on a string with an index length of no more than 10 characters

• The order of the indexed fields takes into account the amount of each field to be weighed later, with the largest number of the most differentiated [most] placed in front
• Reasonable creation of federated indexes (avoid redundancy), consistent with the leftmost prefix principle: (a,b,c) relative to (a), (b), (A,B,C)
• Fields that you might want to add an index to

1. The character of Order By,group By,distinct is added after the index as needed
2. Update,delete statements need to be indexed according to where conditions
3. For join operations, you need to add an index on the join field

• Index Use Taboo

1. Queries that do not use% leading, such as like '%ab '
2. Do not use negative queries, such as not In/not lilke/<>
3. Do not index on a column of sensitivity, such as gender
4. Do not perform mathematical operations and function operations on indexed columns

Example: Assume that an ID is indexed in the tables table

1. Select Col_a,col_b from table where id+1>100 does not use the index
2. Select Col_a,col_b from table where id>100+1 uses the index

3:sql Optimization

• Try to use less large SQL on the line, perhaps a large SQL to block the entire database, the complex SQL split into a number of simple SQL, simplify

1. A SQL can operate on only one CPU, and if SQL is more complex, execution is very inefficient.
2. Simple SQL cache Hit ratio higher
3. Reduced lock Table Time
4. Leverage multi-core CPUs to increase concurrency efficiency

• Reduce the mathematical and logical judgment on MySQL side, avoid SQL statements MD5 (), ORDER by rand (), etc.
• Filter table record Merge does not go to heavy situation, rewrite union to union all
• Reduce the probability of SQL injection by using the precompiled SQL, which reduces the use of spliced
• Non-equivalent conditions in the Where condition (in between < <= > >=) result in subsequent fields that do not use the federated index, and be careful to avoid
• Where Condition comparison, field type and incoming value must persist type all the time, avoid implicit conversions

Example

Field: Code varchar (a) NOT NULL coment ' encoded ' #code index established

1. SELECT * FROM table_name where code=10001; index is not used
2. SELECT * FROM table_name where code= ' 10001 '; indexes are used

• Limit Paging optimization

1. Traditional paging

SELECT * FROM table limit 1000,10

Limit principle:

LIMIT accepts one or two numeric parameters. parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first return record row, and the second parameter specifies the maximum number of rows to be returned for a record

Limit 1000,10//retrieves the record line 1001-1010, the greater the offset, the slower

1. Featured pagination

• Paging Mode 1:

SELECT * FROM table WHERE ID >= (SELECT id from table limit 10000,1) limit;

ID is the primary key query speed is relatively fast

• Paging Mode 2:

• The program takes id:select ID from table limit 10000, 10;

• Select * FROM table WHERE ID in (123,456 ...);

MySQL Development specification