MySQL Development specifications and principles Daquan

First, table Design

1. Library names, table names, field names must be split with lowercase letters and "_".
2. The library name, table name, and field name must be no more than 12 characters.
3. The name of the library, table name, field name is known, it is recommended to use nouns instead of verbs.
4. We recommend that you use the InnoDB storage engine.
5. Storage precision floating-point numbers you must use decimal instead of float and double.
6. We recommend using unsigned to store non-negative values.
7. It is recommended to use the int unsigned storage IPV4.
8. The shape definition does not add length, such as using int instead of INT (4).
9. Use tinyint UNSIGNED when using short data types, such as a value range of 0-80.
10. It is not recommended to use the enum type, instead of using tinyint.
11. Do not use text, blob types as much as possible.
12. varchar (n), n means that the number of characters is not the number of bytes, such as varchar (255), you can store up to 255 characters, you need to select N according to the actual width.
13. varchar (n), n as small as possible, because the maximum length of all varchar fields in a table is 65,535 bytes, and when you sort and create a temporary table class of memory operations, you use the length of N to request memory.
14. Table Character Set Select UTF8.
15. Use varbinary to store variable-length strings.
16. The year type is used for storage years.
17. The date type is used for storing dates.
18. Storage time (accurate to seconds) it is recommended to use the timestamp type because timestamp uses 4 bytes and datetime uses 8 bytes.
19. The recommended field is defined as not NULL.
20. Splits a large segment into another table.
21. Prohibit the use of varbinary, blob storage pictures, files, etc. in the database.
22. A table structure change requires a DBA audit to be notified.

Second, Index

1. Non-unique indexes must be named according to the Idx_ field name _ Field name [_ Field names].
2. The unique index must be named according to the Uniq_ field name _ Field name [_ Field names].
3. The index name must use lowercase.
4. The number of fields in the index is recommended to be no more than 5.
5. The number of indexes on a single table is controlled within 5.
6. The unique key consists of 3 of the following fields, and when the fields are shaped, a unique key is used as the primary key.
7. When there are no unique keys or unique keys that do not conform to the criteria in 5, use the self-increment (or get through the generator) ID as the primary key.
8. The unique key does not repeat with the primary key.
9. The order of the indexed fields takes into account the number of the field values to be weighed back, and the number is placed in front.
10. The field for ORDER By,group by,distinct needs to be added after the index.
11. Use explain to determine if the SQL statement uses the index reasonably, and try to avoid extra columns from appearing: Using File sort,using temporary.
12. The UPDATE, DELETE statement needs to be indexed based on the Where condition.
13. It is not recommended to use the% prefix for fuzzy queries, such as like "%weibo".
14. When indexing a varchar field that is too long, add the CRC32 or MD5 hash field to index the hash field.
15. Reasonable creation of federated indexes (avoid redundancy), (a,b,c) equals (a), (b), (A,B,C).
16. Make reasonable use of the overlay index.
17. SQL changes need to confirm that the index requires changes and notifies the DBA.

Third, SQL Statement

1. uses prepared statement to provide performance and avoid SQL injection. In the
2. SQL statement, the in contains values should not be too large. The
3. UPDATE, DELETE statement does not use limit. The
4. where condition must use the appropriate type to avoid the implicit type conversion of MySQL. The
5. SELECT statement gets only the fields that you want. The
6. Select, INSERT statement must explicitly indicate the field name, do not use SELECT *, and do not use INSERT into table ().
7. uses select Column_name1, column_name2 from table where [condition] instead of select column_name1 from table where [Condi tion] and select column_name2 from table WHERE [condition]. Non-equivalent conditions in the
8. where condition (in, between, <, <=, >, >=) cause subsequent conditions to not be used for indexing.
9. avoids mathematical operations or function operations in SQL statements, and it is easy to couple business logic with DB. The
10. INSERT statement uses batch submission (INSERT into table VALUES (), (), () ... , the number of values should not be too large.
11. Avoid using stored procedures, triggers, functions, and so on, to easily couple business logic with DB, and there are bugs in the stored procedures, triggers, and functions of MySQL.
12. Avoid using join. The
13. uses reasonable SQL statements to reduce the number of interactions with the database.
14. does not use ORDER by RAND () and replaces it with another method.
15. recommends using a reasonable paging method to increase the efficiency of paging. The Count (*) is used instead of Count (Primary_key) and COUNT (1) for the number of records in the
16. statistics table.
17. prevents query from performing background management and statistics type functionality from the library.

Four, Hash Table

1. The amount of data per table is recommended to be controlled below 5000w.
2. Hash, range, lookup table can be used in conjunction with hash table.
3. If a hash table is hashed using MD5 (or a similar hash algorithm), the table name suffix uses 16 binary, such as USER_FF.
4. It is recommended to use CRC32 (or similar arithmetic algorithm) for hashing, table name suffix using numbers, numbers must start from 0 and equal width, such as 100 tables, suffix from 00-99.
5. With a time hash table, the table name suffix must be in a specific format, such as user_20110209 by a daily hash table and user_201102 by a monthly hash table.

Five, other

1. Bulk import, export of data requires a DBA to review and observe the service during execution.
2. Batch update of data, such as update,delete operations, requires a DBA to review and observe the service during execution.
3. Product non-database platform operation caused by problems and failures, such as the front-end is caught, please promptly notify the DBA, easy maintenance service stability.
4. Business Unit Program bugs and other issues affecting the database services, please promptly notify the DBA, easy maintenance service stability.
5. Business unit promotion Activities, please inform the DBA in advance of service and access assessment.
6. If a business unit causes data loss due to a human error, you need to restore the data, notify the DBA at the first time, and provide important clues such as accurate time, error-handling statements, etc.

———————————————————————————————————————————————--------------------------------------------------------

FAQs

1-1. Library name, table name, field name must use lowercase letters, "_" split.

A) MySQL has configuration parameters lower_case_table_names, cannot be changed dynamically, Linux system default is 0, that is, the library table name is stored in the actual situation, case sensitive. If it is 1, it is stored in lowercase and is not case sensitive. If it is 2, it is stored in the actual situation but is compared in lowercase.

b) If the case is mixed, there may be multiple tables such as ABC,ABC,ABC coexistence, which can easily lead to confusion.

c) Field names are case-sensitive, but the actual use is not differentiated, that is, you can not create two names, but not the same case of a field.

D) In order to unify the specification, the Library name, table name, field name use lowercase letters.

1-2. The library name, table name, and field name must be no more than 12 characters.

Library names, table names, field names support up to 64 characters, but must be no more than 12 characters for uniform specification, easy identification, and reduced throughput.

1-3. The name of the library, table name, field name is known, it is recommended to use nouns instead of verbs.

A) User reviews can be usercomment or comment with the table name.

b) The library table is an objective thing, an object, so it is recommended to use nouns.

1-4. It is recommended to use the InnoDB storage engine.

A) after 5.5 default breaking, support transactions, row-level locks, better recoverability, high concurrency under better performance, multi-core, large memory, SSD and other hardware support better.

b) An official white paper comparing the visible annexes.

1-5. Storage precision floating-point numbers you must use decimal instead of float and double.

A) numeric types in MySQL (not including integral types):
IEEE754 floating point number: float (single), double or real (double)
Fixed-point number: decimal or numeric

The valid numeric binary for single-precision floating-point numbers is 24 bits, which, in decimal terms, is 8 digits, and the valid number binary for double-precision floating-point numbers is 53 bits, which, in decimal terms, is 16 bits.
A valid number of real numbers over 8 bits, with single-precision floating-point numbers to represent, will produce errors! Similarly, if a valid number of real numbers exceeds 16 bits, a double-precision floating-point is used to indicate an error.
b) The IEEE754 standard computer floating-point number is internally expressed in binary notation, but it also causes errors when converting a decimal to a binary floating point, because not all numbers can be converted to a finite-length binary number.
That is, a binary can be accurately converted to decimal, but a decimal with decimal does not necessarily be able to accurately represent the binary.

Instance:
drop table if exists t;

CREATE table T (value float (10,2));

INSERT into T values (131072.67), (131072.68);

Select value from T;

+ ——— –+

| Value |

+ ——— –+

| 131072.67 |

| 131072.69 |

+ ——— –+

1-6. We recommend using unsigned to store non-negative values.

The same number of bytes stores a larger range of values. If the tinyint has a symbol of-128-127, unsigned 0-255

1-7. How to use the int unsigned storage IP?

Use the int unsigned instead of char (15) to store the IPv4 address and convert it through the MySQL function Inet_ntoa and Inet_aton. The Ipv6 address currently has no conversion function and requires a decimal or two bigint to store it. For example:

SELECT Inet_aton (' 209.207.224.40′);

3520061480

SELECT Inet_ntoa (3520061480);

209.207.224.40

1-8. What does the Int[m],m value mean?

Note that the number after the parentheses of the numeric type only represents the width and is not related to the storage range, such as int (3) shows 3 bits by default, space is padded, the display is normal when exceeded, Python, Java client, etc. do not have this function.

1-10. It is not recommended to use Enum, set type, tinyint instead.

MySQL Development specifications and principles Daquan