MySQL Development code

1. Naming
A) is meaningful.
b) databases, tables, all in lowercase, using only underscores and lowercase letters.
c) The index begins with Idx_.
d) name not too long, try to be less than 25 characters.
e) Do not use reserved words.
f) field type, named consistency, the same field in different tables to the same type, the same length, the same name.
g) Backup table, time suffix.
2. Index
A) The combined index, the number of fields not exceeding 5.
b) The number of single-table indexes, no more than 5.
c) do not repeat the unique and primary keys.
D) Be aware of the order of the fields when creating the index.
e) The field used by the order By/group by, placed behind the federated index.
f) According to the Explain tool, adjust the SQL so that the index is used reasonably, as far as possible, using Filesoft, using temporary is not present.
g) too long varchar, you can add a hash field to index the hash field. Easier to use MD5.
h) The scope condition is placed at the end of the composite index.
3. Table Design
A) It is recommended to select all InnoDB engines.
b) Each table should have a primary key.
c) storing the field as much as possible with the not Null,null value requires additional space and can result in more complex comparison operations.
d) using shorter columns, such as a short integer, the integer columns are often executed more quickly.
e) separating large and infrequently used fields into another table, the smaller the table, the faster the execution. or separate frequently updated tables into other tables, because frequent updates can invalidate the cached result set and may affect performance. Note: If you need to make a table connection after separation, it is not worth the loss, MySQL connection table performance is poor, or you can consider using a program to connect.
f) Exact number of points, using decimal, do not use float/double, will not be accurate.
g) integer definition, do not define the display length.
h) It is recommended that you do not use enum types.
i) do not use the Text/blob type as much as possible.
j) varchar (n), n is the number of characters, not the number of bytes, such as varchar (255), stored Chinese characters, can save up to 255. n should be as small as possible, when sorting and creating temporary tables, use the length of N to request memory, which is improved after 5.7. The maximum length of a varchar field is 65,535 bytes.
k) Character set, select UTF8.
L) Store date, use date, storage time recommended Tmestamp,timestamp is 4 bytes, datetime is 8 bytes.
m) do not store files in the database.
4. SQL statements
A) do not select *
b) Use placeholder methods to improve performance and prevent SQL injection attacks when you pass a parameter.
c) split the large operation.
D) The IN clause should not contain too many values, suggesting less than 100.
E) Insert display indicates the name of the field, in bulk, the number should not be too much each time.
f) Avoid mathematical or functional operations in SQL statements, and avoid coupling business logic and data storage.
g) Avoid using stored procedures, triggers, functions, and so on, which will couple business logic with data, and there may be bugs in these features of MySQL.
h) Table connection will reduce performance, so can not connect, can be less connected to less.
i) use reasonable SQL to reduce and interact with the database, but this tradeoff is that a complex, costly SQL can be split into multiple simple SQL, although it will increase the database interaction, but the performance is unabated.
5. SQL scripts
Remove special symbols such as ^m, file into Linux format, and use UTF8 without BOM format.
6. Data volume
A) If the optimization is good enough, the single expression of billions of data is not a problem, but this is the ideal situation. In fact, the single-table data volume control under 50 million, or even, preferably under 10 million. If the amount of data is too large, then split into multiple tables, sub-table to use the Application layer table, it is best not to use the MySQL table features, there may be a bug.
b) Before developing a function, the data storage should be estimated, and if the amount of data is large, the optimization is considered in advance.

MySQL Development specification