MySQL Development standard Specification

First, table design

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

2. The Library name, table name, field name is not more than 12 characters.

3. The Library name, table name, field name are known, try to use nouns rather than verbs.

4. Prioritize the use of the InnoDB storage engine.

5. Store exact floating-point numbers instead of float and double using decimal.

6. Use unsigned to store non-negative values.

7. Use the int unsigned to store the IPV4. "FAQ"

8. No length is added to the shaping definition, such as using int instead of int[4]. "FAQ"

9. Use tinyint UNSIGNED when using short data types, such as a value range of 0-80.

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

11. Do not use text, blob type as much as possible.

varchar (n), n means 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.

. 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.

VARCHAR (N), n>5000, use BLOB type.

table Character Set select UTF8.

16. Use varbinary to store variable-length strings.

17. The year type is used for storage years.

18. The date type is used for storing dates.

storage time (accurate to seconds) use the timestamp type because timestamp uses 4 bytes and datetime uses 8 bytes. "FAQ"

20. The field is defined as not NULL.

21. Split the large font segment into another table.

22. Do not use varbinary, blobs to store pictures, files, etc. in the database.

Second, the index

1. Non-unique indexes are named by the Idx_ field name _ Field name [_ Field names].

2. The unique index is named according to "uniq_ Field name _ Field name [_ Field names]".

3. The index name uses lowercase.

4. the number of fields in the index does not exceed 5.

5. The unique key consists of 3 following fields, and when the fields are shaped, a unique key is used as the primary key.

6. When there are no unique keys or unique keys that do not meet the criteria in 5, use the self-increment (or get through the generator) ID as the primary key.

7. The unique key does not repeat with the primary key.

8. The order of the indexed fields needs to take into account the number of the field values after the weight, the number is placed in front.

9. The ORDER by,group by,distinct field needs to be added after the index.

10. The number of indexes on a single table is controlled within 5. #索引少走索引查询快.

11. Use explain to determine whether the SQL statement uses the index reasonably, and to avoid extra columns from appearing: Using File sort,using temporary. "FAQ"

The UPDATE, DELETE statement needs to be indexed based on the Where condition. #注意要是不加条件可能全部执行, catastrophic, must be avoided, or by technical means to prevent such a way of execution.

13. It is not recommended to use the% prefix for fuzzy queries, such as like "%weibo". #相当于全表做匹配, the query will be slower.

14. Use a different method when indexing a varchar field with a length greater than 50. "FAQ"

15. Reasonably Create a federated index (avoid redundancy), (A,B,C) equivalent to (a), (A, B), (A,B,C).

16. Make reasonable use of the coverage index. "FAQ"

17. Use force INDEX in reasonable circumstances.

SQL changes need to confirm that the index needs to be changed and notify the DBA.

Iii. SQL statements

1. Using prepared statement, you can provide performance and avoid SQL injection. #参考文档: http://www.cnblogs.com/liuhongfeng/p/4175765.html

2. The in SQL statement contains no more than 500 values.

3. The UPDATE and DELETE statements do not use limit. #没理解, wouldn't it be faster to use limit. There are restrictions.

4. Use the appropriate type in the where condition to avoid the implicit type conversion of MySQL. "FAQ"

5. The SELECT statement gets only the required fields.

6. The SELECT, INSERT statement explicitly indicates the field name, does not use SELECT *, and does not apply to insert into table ().

7. Using select Column_name1, column_name2 from table where [condition] instead of select column_name1 from table where [condition] and SEL ECT column_name2 from Table WHERE

[Condition]. #加上相应的条件.

8. Non-equivalent conditions in the Where condition (in, between, <, <=, >, >=) cause subsequent conditions to not be used for indexing.

9. Avoid mathematical operations or function operations in SQL statements, and easily couple business logic with DB.

Insert statement using batch submission (INSERT into table VALUES (), (), ()?? ), the number of values does not exceed 500.

11. Avoid using stored procedures, triggers, functions, and so on, it is easy to combine business logic with DB, and there are some bugs in the stored procedures, triggers, and functions of MySQL. #没理解

12. Avoid using join.

13. Use reasonable SQL statements to reduce the number of interactions with the database. "FAQ"

14. Do not use ORDER by RAND () and replace it with another method. "FAQ"

15. Use a reasonable paging method to improve the efficiency of paging. "FAQ"

16. Count (*) is used instead of Count (Primary_key) and COUNT (1) for the number of records in the statistics table.

17. Disable query to perform background management and statistics type functions from the library.

Four, the scattered table

1. The data volume of each table is controlled below 5000w.

2. Hash table can be used in combination with hash, range, lookup table.

3. Hash hash table, table name suffix using 16, such as USER_FF.

4. Use the time hash table, the table name suffix uses the date, for example by the daily scatter table user_20110209, by the monthly scatter table user_201102.

V. Other

1. Batch import, export data requires a DBA to review and observe the service during execution.

2. Batch update data, such as update,delete operations, requires a DBA to review and observe the service during execution. #避免影响其他数据.

3. Product non-database platform operations caused by problems and failures, such as the front-end is caught, please promptly notify the DBA, easy maintenance service stability.

4. In the event of a bug in the business unit and other issues affecting the database service, please notify the DBA timely and maintain stable service.

5. Business unit promotion activities, please inform DBA in advance for service and access assessment.

6. If the business unit is causing data loss due to human error, you need to restore the data, notify the DBA at the first time, and provide accurate time, error operation statements and other important clues.

6.FAQs

1. How do i 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

2. 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.

3. Why is it recommended to use timestamp to store time instead of datetime?

Both datetime and timestamp are accurate to the second, with preference for timestamp, because timestamp has only 4 bytes and a datetime of 8 bytes. At the same time, timestamp has automatic assignment and auto-Update feature.

4. How do I use the automatic assignment attribute of timestamp?

A) Use the current time as the default value for TS: TS TIMESTAMP defaults current_timestamp.

b) When the row is updated, update the TS value: TS TIMESTAMP default 0 on update current_timestamp.

c) 1 and 2 can be combined: TS TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp.

5. How do I index a varchar field with a length greater than 50?

The following table adds a column of URL_CRC32 and then indexes the URL_CRC32, reducing the length of the indexed field and improving efficiency.

? CREATE TABLE URL (

?? URL VARCHAR (255) not NULL for default 0, URL_CRC32 INT UNSIGNED NOT null default 0,?? Index Idx_url (URL_CRC32)

6. Why do I need to avoid the implicit type conversion of MySQL?

Because after the hermit type conversion of MySQL, it is possible to convert the indexed field type to the type of the right value of the = sign, resulting in the use of indexes, and avoiding the use of functions in indexed fields is similar.

7. Why avoid the use of complex SQL?

Rejecting the use of complex SQL, splitting large SQL into multiple simple SQL steps. Reason: Simple SQL is easy to use to MySQL query cache; Reduce lock table time especially MyISAM; you can use multicore CPUs.

8. Why is the use of SELECT * Not recommended?

Adds a lot of unnecessary consumption (CPU, IO, memory, network bandwidth), increases the likelihood of using an overlay index, and the previous paragraph needs to be updated when the table structure changes.

9. Why does the InnoDB storage engine avoid using count (*)?

The InnoDB table avoids using the count (*) operation, the count statistics real-time request is strong can use the memcache or the Redis, the non-real-time statistics may use the separate statistic table, the periodic update.

How do I split pages in MySQL?

If there is a paging statement similar to the following: SELECT * from table ORDER by Time DESC LIMIT 10000, 10; This paging will result in a lot of Io, because MySQL uses an advance read strategy. Recommended Paging method: SELECT * from table WHERE time

11. Why can't I use ORDER by rand ()?

Because order by RAND () reads the data from the disk, sorts it, consumes a lot of Io and CPU, can get a Rand value in the program, and then gets the corresponding value from the database.

12. How can I reduce the number of interactions with the database?

Use the following statement to reduce the number of interactions with DB: INSERT ... On DUPLICATE KEY UPDATE REPLACE inserts IGNORE INSERT into VALUES (), ()#即数据尽量一次插入, do not separate multiple insertions, reduce the number of interactions.

13. How can I use multiple latitude for hashing?

For example, tweet message, CRC32 a message to 16 libraries, and then for the table in each library, follow the message_id

A new table is generated on a single day.

Are additional storage generated in varchar?

VARCHAR (M), if m<256 uses a byte to store the length, if m>=256 uses two bytes to store the length.

15. Why is MySQL performance dependent on the index?

MySQL's query speed relies on a good index design, so indexing is critical for high performance. A reasonable index will speed up the query (including the speed of the update and delete, MySQL will load the page containing the row into memory, and then update or delete operations), the unreasonable index will slow down. The MySQL index looks similar to the pinyin and radical lookups of the Xinhua dictionary, and when the pinyin and the radical index does not exist, it can only be found by page-by-page paging. When the MySQL query cannot use the index, MySQL makes a full table scan and consumes a lot of IO.

16. Why can't there be too many indexes in a single table?

InnoDB's secondary index is stored using B+tree, so the b+tree needs to be adjusted at update, DELETE, insert, and too many indexes slow down the update.

17. What is an overlay index?

In the InnoDB storage engine, there is no direct storage of row addresses in secondary index (non-primary key indexes), and the primary key value is stored. If a user needs to query a data column that is not contained in secondary index, it needs to be queried two times by secondary index to find the primary key value and then querying to the other data column through the primary key. The concept of an overlay index is that the query can be done in an index, the coverage index is more efficient, and the primary key query is a natural overwrite index. Reasonable index creation and reasonable use of query statements, when used to overwrite the index can achieve performance gains. For example, select Email,uid from User_email WHERE uid=xx, if the UID is not a primary key, you can add the index as index (UID,EMAIL) at the appropriate time for a performance gain. #相当于就是都走索引操作效率会变高.

Explain statements

The explain statement (executed in the MySQL client) can obtain information on how MySQL executes the SELECT statement. by performing a explain on the SELECT statement, you can know if MySQL uses information such as indexes, full table scans, temporary tables, sorting, and so on when executing the SELECT statement. Try to avoid MySQL full table scan, use temporary table, sort, etc.

#参考文档http://blog.csdn.net/solmyr_biti/article/details/54293492

MySQL Development standard Specification