MySQL Development specification

1. The library name, the table name, the field name must use the lowercase letter, and uses the underline to divide.

A) MySQL has configuration parameters lower_case_table_names, not dynamic changes, Linux system defaults to 0, that is, the library table name to the actual situation of storage, case sensitive. If it is 1, it is stored in lowercase and is not case sensitive. If it is 2, it is stored in real condition but is compared in lowercase.

b If the case is mixed, there may be multiple tables coexisting with ABC,ABC,ABC, which can easily lead to confusion.

c) field names are explicitly case-sensitive, but are actually used without distinction, that is, you cannot create two fields with the same name but not the same case.

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

2. The Library name, table name, field name is forbidden to exceed 32 characters.

The library name, table name, field name supports up to 64 characters, but more than 32 characters are prohibited for uniform specification, easy identification, and reduced transmission.

3. Use the InnoDB storage engine.

InnoDB engine is the MySQL5.5 version after the default, support transactions, row-level locks, better data recovery capabilities, better concurrency performance, at the same time for multi-core, large memory, SSD and other hardware support better, support data hot backup, so InnoDB compared to MyISAM have obvious advantages.

4. Library name, table name, field name prohibit use of MySQL reserved word.

When properties such as library name, table name, field name, and so on contain reserved words, the SQL statement must refer to the property name in reverse quotation marks, which makes it very complicated to write SQL statements and escape variables in the shell script.

5. Prohibit the use of partitioned tables.

Partitioned tables have strict requirements for partitioning keys; When a table becomes larger, it becomes more difficult to perform DDL, SHARDING, and single table recovery. Therefore, the use of partitioned tables is prohibited and the business end is recommended for manual sharding.

6. It is recommended that you use unsigned to store non-negative values.

The same number of bytes, the value of nonnegative storage is larger. If the tinyint has a sign of-128-127, unsigned is 0-255.

7. It is recommended to use the int unsigned storage IPV4.

The unsinged int storage IP address occupies 4 bytes and CHAR (15) occupies 15 bytes. In addition, the computer handles integer types faster than string types. The IPV4 address is stored using an int unsigned instead of char (15), which is transformed by the MySQL function Inet_ntoa and Inet_aton. The IPV6 address does not currently have a conversion function and needs to be stored using decimal or two bigint.

For example:

SELECT Inet_aton (' 209.207.224.40 '); 3520061480

SELECT Inet_ntoa (3520061480); 209.207.224.40

8. It is strongly recommended that tinyint be used instead of the enum type.

The enum type requires an online DDL when it needs to modify or increase the enumeration value, and the cost is greater; The enum column value, if it contains a numeric type, may cause default values to be confused.

9. Use varbinary to store case sensitive variable-length strings or binary content.

VarBinary default case sensitivity, no character set concept, fast speed.

The 10.INT type holds 4 bytes of storage, such as int (4), which only represents a display character width of 4 bits and does not represent a storage length.

Numeric type the number following the parentheses is only for width and has nothing to do with the storage scope, such as int (3) By default display 3 bits, padded space, beyond the normal display, Python, Java client, etc. do not have this function.

11. Distinguish between using datetime and timestamp. The year type is used for storage years. Stored dates use the date type. The timestamp type is recommended for storing time (accurate to second).

Both datetime and timestamp are accurate to the second, timestamp is preferred because timestamp is only 4 bytes and DATETIME8 bytes. At the same time, timestamp has an automatic assignment and an automatic Update feature. Note: In versions 5.5 and earlier, if there is more than one timestamp column in a table, only one column can have the Automatic Update feature.

How do I use Timestamp's automatic assignment properties?

A) automatic initialization and Automatic Update: Column1 TIMESTAMP DEFAULT current_timestamp on update current_timestamp

b) Just automatic initialization: column1 TIMESTAMP DEFAULT Current_timestamp

c) Automatic update, initialized with a value of 0:column1 TIMESTAMP default 0 on update current_timestamp

d) The value initialized is 0:column1 TIMESTAMP default 0

12. All fields are defined as not NULL.

A For each row of the table, each null column requires additional space to identify it.

b The B-tree index does not store null values, so indexing is less efficient if the index field can be null.

c) It is recommended to replace null values with 0, special values, or empty strings.

13. Split large and low frequency fields into separate tables for storing and separating hot and cold data.

It helps to effectively use the cache, prevent the reading of useless cold data, less disk IO, while ensuring that the thermal data resident memory increases the cache hit rate.

14. Prohibit the storage of plaintext passwords in the database.

Use the encrypted string to store the password and ensure the password is not decrypted, and use random string to add salt to ensure the password security. Preventing database data from being acquired by internal personnel or hackers, and using dictionary attacks to break user passwords violently.

15. The table must have a primary key and it is recommended to use unsigned as the primary key.

The table does not have a primary key, and InnoDB defaults to the hidden primary key columns; tables without primary keys are very difficult to locate rows of data, and also reduce the efficiency of row-based replication.

16. Disable redundant indexing.

Index is a double-edged sword, will increase the maintenance burden, increase IO pressure. (A,b,c), (A,B), which is a redundant index. The prefix index can be used to speed up the goal and reduce the maintenance burden.

17. Prevent duplicate indexing.

Primary key A;uniq index A; Duplicate indexing increases maintenance burdens, takes up disk space, and has no benefit.

18. Do not index on low cardinality columns, such as "gender".

In most scenarios, an accurate lookup of an index on a low cardinality column has no advantage over a full table scan without indexing, and increases the IO burden.

19. Reasonable use of coverage index to reduce IO, avoid sorting.

The overlay index can retrieve all the fields needed from the index, thus avoiding two lookups to the table and saving IO. In the InnoDB storage Engine, secondary index (not primary key, also known as secondary index, and level two index) does not store row addresses directly, but instead stores primary key values. If the user needs to query for data columns not included in secondary index, you need to query two times to find the primary key value through secondary index and then query to another data column through the primary key. Overriding an index allows you to get all the data you need in one index, so it's more efficient. A primary key query is a natural overlay index. For example, select Email,uid from User_email WHERE uid=xx, if the UID is not a primary key, you can add indexes to index (UID,EMAIL) to gain performance gains when appropriate.

20. In place of OR. The SQL statement should contain less than 1000 values in.

In is the range lookup, the MySQL interior will sort the list values in and find them more efficiently than or.

21. The table character set uses UTF8 and can be applied for using the UTF8MB4 character set if necessary.

A the UTF8 character set stores Chinese characters for 3 bytes and stores English characters in one byte.

b) UTF8 Unified and Universal, there will be no garbled risk of transcoding.

c If you encounter the storage requirements of emoj and other emoticons, you can apply to use the UTF8MB4 character set.

22. Use UNION ALL instead of union.

UNION all does not need to sort the result set again.

23. Prohibit the use of order by rand ().

Order BY Rand () adds a pseudo column to the table, calculates the rand () value for each row of data with the rand () function, and then sorts based on that row, which typically generates temporary tables on the disk, so it's very inefficient. It is recommended that you use the rand () function to obtain a random primary key value and then get the data through the primary key.

24. It is recommended that a reasonable paging approach be used to improve paging efficiency.

The first type of paging:

SELECT *

From T

where thread_id = 771025

and deleted = 0

ORDER BY gmt_create ASC limit 0, 15;

SELECT * FROM t

where thread_id = 771025

and deleted = 0

ORDER BY gmt_create ASC limit 0, 15;

Principle: All the fields are sorted and returned according to the filter condition.

Data Access Cost = Index io+ index all record results corresponding to table data IO

Disadvantage: This kind of writing more to the back of the execution efficiency is worse, the longer, especially the table data volume is very big time.

Scenario: applies when intermediate result sets are small (below 10000 lines) or if the query conditions are complex (referring to multiple different query fields or multiple table joins).

The second type of paging:

Select T.* from (

Select ID from t

where thread_id = 771025 and deleted = 0 ORDER by gmt_create ASC limit 0) A, t

where a.id = T.id;

Premise: Suppose the T-table primary key is an ID column, and has an overlay index secondary key: (thread_id, deleted, gmt_create)

Principle: First, according to filter conditions, using the overlay index to remove the primary key ID to sort, and then the join operation to remove other fields.

Data Access cost = Table data io corresponding to the result (15 rows in the example) of the index io+ after index paging.

Advantages: Each page will consume the same resources and time, just like the first page.

Scenario: applies when the Query and sort fields (that is, the fields involved in the WHERE clause and the ORDER BY clause) have a corresponding overlay index, and the intermediate result set is large.

25.SELECT only get the necessary fields and prohibit the use of select *.

Reduce network bandwidth consumption;

Can effectively use the coverage index;

Table structure changes have no effect on the program.

In 26.SQL, you avoid functions that have indeterminate results such as now (), Rand (), Sysdate (), Current_User ().

Statement-level replication scenarios, resulting in inconsistencies between master and slave data; a function of indeterminate value, the resulting SQL statement cannot take advantage of query CACHE.

27. Adopt the appropriate sub-table strategy. For example, thousand library ten tables, 10 hundred tables and so on.

It is advantageous for the database to be split horizontally at the later stage of the business development by using the appropriate sub-table strategy, while the library can effectively utilize the multithread replication characteristics of MySQL.

28. Reduce the number of interactions with the database, as far as possible using batch SQL statements.

Use the following statement to reduce the number of interactions with DB:

A) INSERT ... On DUPLICATE KEY UPDATE

b) REPLACE into

c) INSERT IGNORE

D INSERT into VALUES ()

29. Split complex SQL into multiple small SQL, avoid large transactions.

Simple SQL is easy to use for MySQL query CACHE, reduce lock table time especially MyISAM; you can use multi-core CPUs.

30. Multiple alter operations on the same table must be merged into one operation.

MySQL changes the table most operations need to lock the table and rebuild the table, and the lock table will affect the online business. To reduce this effect, multiple alter operations on the table must be merged into one operation. For example, to add a field B to a table T, and to index an existing field AA, the usual practice is divided into two steps:

ALTER TABLE t add column B varchar (10);

Then add the index:

ALTER TABLE T add index IDX_AA (AA);

The correct approach is:

ALTER TABLE t add column B varchar (a), add index IDX_AA (AA);

31. Avoid using stored procedures, triggers, views, custom functions, and so on.

These advanced features have performance issues, as well as more unknown bugs. When business logic is put into a database, it becomes more difficult for database DDL, SCALE out, sharding and so on.

32. Prohibit the presence of application accounts with super privileges.

Safety first. Super privileges can cause read only to fail, leading to more bizarre problems and difficult to track.

33. On-Line table to modify the table requirements, must specify all the SQL statements involved (including INSERT, DELETE, UPDATE), for DBA Audit and optimization.

It is not just a SELECT statement that requires an index. UPDATE, delete all need to navigate to the data before the change can be performed. It is therefore necessary for the business to provide all SQL statements to facilitate DBA auditing.

34. Do not store the business logic in the MySQL database.

The database is stateful service, the change is complex and slow, if the business logic into the database, will limit the rapid development of the business. It is suggested that the business logic should be put forward to the front-end or intermediate logic layer, and the database should be used as storage layer to realize the separation of logic and storage.

35. Example of building a table statement