[reprinted] MySQL operations experience accumulated over the years

Original: http://mp.weixin.qq.com/s?__biz=MzA3MzYwNjQ3NA==&mid=207132223&idx=1&sn= f5d98146f28235d91fe3e675cead4ce5&scene=1&key= C76941211a49ab58058d73eaffa6e940a8545c66f8e80de85e1684bfae3d6a6bc86ae97b552146a5cc18f571240dc78b&ascene=0 &uin=mjk1odmyntyymg%3d%3d&devicetype=imac+macbookpro9%2c2+osx+osx+10.10.3+build (14D136) &version= 11020012&pass_ticket=w1e%2fnptlaxtviyfdjifa%2buf0symiiom6y58jx6vaph28vllg%2fs8kwpgowvuytq8h

"Internet MySQL Development code" based on years of experience2015-07-09 Liu Qiuqi operation and maintenance help

Written in front: no rules inadequate surrounding area. For the friends who just joined the internet, will certainly come into contact with Mysql,mysql as the internet's most popular relational database products, it has its expertise in the place, but also its short board, for its characteristics, combined with the internet most of the characteristics of the application, the author according to their own years of internet company MySQL DBA experience , we summarize some development specifications of Internet MySQL, for reference only.

The author is a subscription number Yunweibang Technical Experts Liu Qiuqi, years of database experience, if there is a problem can subscribe to Yunweibang and leave a message.

Summary:

• Basic specifications

• Naming conventions

• Code for design of library, table and field development

• Index specification

• SQL specification

• Process specification

One

Basic Specifications

(1) using the InnoDB storage engine

(2) Table character set using UTF8

(3) All tables need to add comments

(4) Single-table data volume recommended control within 5000W

(5) Do not store maps, files and other big data in the database.

(6) Prohibit on-line database stress test

(7) Ban? Direct database from test and development environment

Two

Naming Conventions

(1) The Name field name of the library name must have a fixed name length, within 12 characters

(2) Library name, table name, field name is forbidden more than 32 characters. Must see the meaning of the name

(3) Library name, table name, field name forbidden? Use MySQL to keep the word

(4) The temporary library, the table name must be the TMP prefix, and the date suffix

(5) Backup library, table must be prefixed with bak and date suffix

Three

code for design of library, table and field development

(1) Prohibited use of partitioned tables

(2) splitting large and low-frequency fields, separating hot and cold data

(3) Hash table, table name suffix to make?? Binary number, subscript starting from 0

(4) According to the date time table must conform to YYYY[MM][DD][HH] format

(5) Adopt appropriate sub-database sub-table strategy. such as the Thousand library ten tables, 10 library hundred meters and so on

(6) If possible, do not use text, blob type

(7) storing exact floating-point numbers in decimal instead of float and double

(8) The simpler the better: convert characters to numbers, use tinyint instead of enum type

(9) All fields are defined as not NULL

(10) Use unsigned to store non-negative integers

(one) int type fixed 4 byte storage

(12) Storage time using timestamp

(13) using the int unsigned storage IPV4

(14) using varbinary to store case-sensitive variable-length strings

(15) Prohibit storing plaintext password in database, encrypt password and store

(16) Use a good value type field

Tinyint (1Byte)

smallint (2Byte)

Mediumint (3Byte)

Int (4Byte)

BigInt (8Byte)

Type	Bytes	Minimum value	Maximum Value
		(Signed/unsigned)	(Signed/unsigned)
TINYINT	1	-128	127
No sign		0	255
SMALLINT	2	-32768	32767
No sign		0	65535
Mediumint	3	-8388608	8388607
No sign		0	16777215
Int	4	-2147483648	2147483647
No sign		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
No sign		0	18446744073709551615

If the value field is not that large, do not use bigint

(17) Storage IP is best stored in int rather than char (15)

(18) Do not allow the use of enum

(19) Avoid using null fields

Null field is difficult to query optimization, the index of NULL field requires extra space, the compound index of NULL field is invalid

(20) Less use of Text/blob,varchar performance will be much higher than the text, can not avoid the blob, please split the table

(21) The database is not allowed to store large files, or photos, you can put large objects on disk, the database store its path

Four

Index Specification

1, the number of indexes to control:

(1) The number of indexes in a single table is not more than 5

(2) The number of fields in a single index does not exceed 5

(3) prefix index for string, prefix index length not more than 8 characters

(4) It is recommended to prioritize the prefix index and, if necessary, to add pseudo-columns and build indexes

2. Primary KEY criteria

(1) The table must have a primary key

(2) Do not use frequently updated columns as primary keys

(3) Try not to select a string column as the primary key

(4) Do not use UUID MD5 hash these as primary keys (values are too discrete)

(5) By default, a non-null unique key as the primary key

(6) Suggest to choose the self-increment or the generator number

3. Important SQL must be indexed, such as:

(1) Where Condition column for UPDATE, DELETE statement

(2) ORDER by, GROUP by, distinct fields

4. The fields of the multi-table join note the following:

(1) The field with the highest degree of differentiation is placed in front

(2) Nuclear? SQL overrides the overriding index

(3) Avoid redundant and duplicate indexes

(4) Index to comprehensively assess data density and distribution and consider query and update ratios

5. Index Taboo

(1) Do not index on low cardinality columns, such as "gender"

(2) Do not perform mathematical operations and function operations on indexed columns

6, try not to use foreign keys

(1) Foreign key to protect referential integrity, can be implemented on the business side

(2) Actions on parent and child tables affect each other, reducing availability

7, Index name: non-unique index must be named in the Idx_ field 1_ Field 2, unique so must be named in the Uniq_ field 1_ Field 2, the index name must all lowercase

8. The new unique index must not be duplicated with the primary key

9. The default value of the indexed field cannot be null, and it should be changed to the other defaults or null. Null affects query efficiency of indexes very much

10. Repeatedly view the SQL associated with the table, and index the characteristics of the leftmost prefix. Multiple-field repeating statements, to modify the order of the statement condition fields, to establish a federated index for them, and to reduce the number of indexes

11, can use unique index to use unique index, improve query efficiency

12, research and development to often use explain, if found poor index selection, must let them learn to use hint

Five

SQL Specification

(1) SQL statements as simple as possible

Large SQL to find a way to break down into small SQL statements (make full use of query cache and take advantage of multicore CPUs)

(2) The transaction should be simple, the whole transaction time is not too long

(3) Avoid using triggers, functions, stored procedures

(4) Reduce business coupling degree, for sacle out, sharding leave leeway

(5) Avoid mathematical operations in the database (MySQL is not good at math and logical judgment)

(4) Do not use SELECT *, query which fields in the select these several fields

(5) The rewrite of SQL used to or is used in () (or the efficiency of the or is not high in efficiency)

(6) in inside the number of numbers recommended to control within 1000

(7) Limit paging pay attention to efficiency. The greater the limit, the less efficient. You can rewrite the limit, such as an example rewrite:

Select ID from Tlimit 10000, 10; = = Select id from t where ID > 10000 limit10;

(9) Use UNION ALL instead of union

(10) Avoid the join of the big table

(11) Using GROUP by grouping, automatic sorting

(12) Update the data to break up after batch update, do not update too much data at once

(13) Reduce the number of interactions with the database

(13) Note Using the Performance analysis tool

SQL Explain/showprofile/mysqlsla

SQL statement requires all development, SQL keywords are all uppercase, each word only allows one space

The SQL statement cannot have implicit conversions, such as select ID from table where id= ' 1 '

(+) in the condition of the number of data to be less, I remember should be within 500, to learn to use exist instead of in,exist in some scenes query will be faster than in

(17) Can not do without notin, there are too many pits. Will check for null and NULL.

(18) In SQL statements, prohibit the use of the prefix is% of like

(19) Do not use negative queries, such as not in/like

(19) About paging query: The program recommended reasonable use of paging to improve efficiency limit,offset larger to be used in conjunction with sub-query

(20) Prohibit running large queries in the database

(21) Pre-compiled statements, only parameters, more efficient than the delivery of SQL statements, one parse, multiple use; reduce SQL injection probability

(22) Prohibit the order by rand ()

(23) Prohibit single SQL statement update multiple tables at the same time

Six

Process Specification

(1) All the table operations need to inform the table in advance of the query SQL involved;

(2) All the tables need to determine which indexes should be established before the table can be built on-line;

(3) All the changes to the table structure, plus index operations need to be related to the Modified Table query SQL sent to inform the DBA and other relevant personnel;

(4) prior to the construction of new tables and fields, required to develop at least 3 days before the mail out, to the DBA to assess, optimize and review the time

(5) Batch import and export data must be notified in advance to DBA for assistance observation

(6) Ban? Perform background management and statistical class queries from the library online

(7) Prohibition of application account with super privilege exists

(8) Promotion activities or new features must be notified to DBA in advance. Traffic Assessment

(9) Do not batch update, query database during peak business hours

The author is a subscription number Yunweibang Technical Experts Liu Qiuqi, years of database experience, if there is a problem can subscribe to Yunweibang and leave a message.

Reproduced in this article please be sure to have the QR code and the author information of this subscription number

[reprinted] MySQL operations experience accumulated over the years