'Internet MySQL Development Specifications' compiled based on years of experience

Written on the front: There is no circle without rules. For those who have just joined the Internet, they will definitely come into contact with MySQL, MySQL, as the most popular relational database product on the Internet, it has its strengths and weaknesses. , based on its characteristics, combined with the characteristics of most applications on the Internet, based on my many years of experience as a MySQL DBA in Internet companies, the author has now summarized some development specifications for the Internet MySQL, for reference only.

1. Basic specifications

(1) Use INNODB storage engine

(2) Character Set using UTF8

(3) All tables need to add comments

(4) It is recommended that the data volume of a single table be controlled at 5000W Within

(5) Do not store graphs in the database⽚, files and other big data

(6) It is prohibited to do database stress testing online

(7) Forbidden⽌Direct connection to the database from the test and development environment

2. Naming convention

(1) Library name, table name and field name must have a fixed naming length, 12 characters or less

(2) Library name, table name, field name are forbidden⽌ limited to more than 32 characters. The meaning of the name must be seen

(3) Library name, table name, field name forbidden⽌Disabled⽤ Use MySQL Reserved words

(4) Temporary library and table names must be prefixed with tmp and ⽇ Date is suffix

(5) Backup libraries and tables must be prefixed with bak and suffixed with date

3. Library, table, field development and design specifications

(1) forbidden⽌Use partition table

(2) Split large fields and have low access frequency fields, separating hot and cold Data

(3) Use HASH to enter ⾏, the table name suffix is ​​ ⽤⼗base number , the subscript starts from 0

(4) The table by date and time must comply with YYYY[MM][DD][HH] format

(5) Adopt appropriate database and table sharding strategy. For example, Qianku Ten Tables, Ten Databases Hundred Tables, etc.

(6) Try not to use TEXT, BLOB types

(7) Use DECIMAL instead of FLOAT and DOUBLE to store precise floating point numbers

(8) The simpler, the better: convert characters For numbers, use TINYINT instead of ENUMtype

(9) All fields are defined as NOT NULL

(10) Use UNSIGNED to store non-negative integers

(11) INT type fixed occupation 4byte storage

(12) Use timestamp to store time

(13) Use INT UNSIGNED to store IPV4

(14) USE VARBINARY Storage case-sensitive variable-length strings

(15) It is forbidden to store plain text passwords in the database, and the passwords should be encrypted and stored

(16) Use numeric type fields

Tinyint (1Byte)

smallint (2Byte)

mediumint (3Byte)

int (4Byte)

bigint (8Byte)

0

If the numerical field is not that big, don’t use bigint

(17) Storageip is best to use intstorage Instead of char(15)

(18) is not allowed to use ENUM

(19) Avoid using NULL Field

The NULL field is difficult to query optimization, the index of the NULL field requires additional space, the composite index of the NULL field is invalid

(20) Use less The performance of text/blob, varchar will be much higher than text, it is really unavoidable blob, please disassemble the table

(21) Large files or photos are not allowed to be stored in the database. You can put large objects on the disk and store their paths in the database

4. Index specifications

1, the number of indexes should be controlled:

(1) The number of indexes in a single table should not exceed 5

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

(3) Use prefix index for strings, and the prefix index length does not exceed 8 characters(4) It is recommended to give priority to prefix index. If necessary, you can add pseudo columns and create indexes

2, primary key guidelines

(1) The table must have a primary key

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

(3) Try not to choose string columns as primary keys

(4) Do not use

UUID MD5 HASH

These are used as primary keys(The values ​​are too discrete)

(5) By default, ⽤non-empty unique key is used as the primary key

(6) It is recommended to choose auto-increment or issuer

3, important SQL must be indexed, such as:

(1) UPDATE, DELETE statement WHEREConditions Column

(2) ORDER BY , GROUP BY, DISTINCT fields

4 , multiple tablesJOIN The fields of should be noted as follows:

(1) The field with the greatest distinction is placed in front

(2) core⼼ SQL Prioritize covering indexes

(3) Avoid redundant and duplicate indexes

(4) Indexes should comprehensively evaluate data density and distribution and consider query and update ratio

5, index taboos

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

(2) Do not perform mathematical operations and functions on index columns Operation

6, try not to use foreign keys

(1) Foreign keys are used to protect referential integrity and can be implemented on the business side

(2) Operations on the parent table and child table will affect each other, reducing availability

7, index naming: non-unique indexes must start with idx_field1_field 2 is named, unique so it must be named after uniq_field1_field2, the index name must be all lowercase

8 . The newly created unique index must not duplicate the primary key

9, the default value of the index field cannot be NULL, it must be changed to other default or empty. NULLvery affects the query efficiency of the index

10, repeatedly check the SQLSQL

related to the table, and build the index in line with the characteristics of the leftmost prefix. For statements with multiple repeated fields, you need to modify the order of the statement condition fields and create a joint index for it to reduce the number of indexes

11

. If you can use a unique index, you must use a unique index to improve query efficiency

12, R&D should use explain frequently. If they find that the index selectivity is poor, they must learn to use

hint

五、 SQL Find a way to break it into smaller pieces sql

statement

( fully utilizes

QUERY CACHE and fully utilizes multi-core CPU)(2) The transaction should be simple and the length of the entire transaction Not too long (3) Avoid using triggers, functions, stored procedures(4)

Reduce business coupling and

sacle out , sharding

Leave room for(5)

Avoid doing ⾏ Mathematical operations (MySQL is not good at math operations And logical judgment )

(4) Don’t use select *, just select these fields (5) The use of

OR in sql is rewritten as IN() (or is not as efficient as in

)

(6) It is recommended that the number of numbers in should be controlled within 1000

(7) limitPay attention to efficiency when paginating. LimitThe larger, the lower the efficiency. You can rewrite limit, for example, rewrite the example:

select id fromtlimit 10000, 10; => select id from t where id > 10000 limit10;

( 9) Use union all instead of union

(10) Avoid using ⽤ for big watches JOIN

(11) Use group by Group and automatically sort

(12) Update the data in batches and do not update too much data at one time

(13) ) Reduce the number of interactions with the database

(13) Pay attention to the use of performance analysis tools

Sql explain / showprofile / mysqlsla

(14) SQL statement requirements All R&D, SQLkeywords are all capitalized, and each word is only allowed to have one space

(15) SQL statements cannot have implicit conversions, such as select id from table where id='1'

(16) INThe number of data in the condition should be small, I remember it should be within 500, you must learn to use it exist replaces in, exist in some scenarios the query will be faster than in

(17) can be used NOT INNo need to NOTIN, there are too many pitfalls. . Will detect nulls and NULL

(18) In SQL statements, the prohibited prefix is ​​% ’s like

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

(19) About paging queries: The program recommends the rational use of paging to improve efficiencylimit , offset is larger and should be used with subqueries

(20) It is forbidden to run large queries in the database

(21) make ⽤Precompiled statements, only passing parameters, are more efficient than passing SQL statements; parse once, use multiple times; reduce SQL injection probability

( 22) forbidden to use⽤order by rand()

(23) forbidden⽌ SingleSQLstatement at the same time Update multiple tables

6. Process specification

(1) All table creation operations need to inform the query involved in the table in advancesql ；

(2) All table creation needs to determine which indexes should be established before the table can be built online;

(3) All operations of changing the table structure and adding indexes must be done It is necessary to send out the query sql involving the changed table to inform DBA and other relevant personnel;

(4) Before creating a new table and adding fields, require R&D The email must be sent out at least 3days in advance to give dba time for evaluation, optimization and review

(5)Batch import and export data must be notified in advance DBAAssist in observation

(6) forbidden⽌Execute background management and statistical queries online from the library

(7) Ban⽌App accounts with super permissions exist

(8) Promotion activities or new features must be notified in advanceDBAEnter⾏ to conduct traffic assessment

(9) not in business Batch update and database query during peak period

Receive Brothers IT Education’s original Linux operation and maintenance engineer video/detailed Linux tutorial for free. For details, please consult the official website customer service: http://www.lampbrother.net/linux/

Learn PHP, Linux, HTML5, UI, Android and other video tutorials (courseware + notes + video)! Contact Q2430675018

Welcome to joinlinux communication group 478068715

Type	bytes	Minimum value	Maximum
		(signed/unsigned)	(signed/unsigned) Symbolic)
TINYINT	1	-128	127
Unsigned		0	255
SMALLINT	2	-32768	32767
Unsigned		0	65535
MEDIUMINT	3	-8388608	8388607
None Symbol		0	16777215
INT	4	-2147483648	2147483647
Unsigned		BIGINT	8
-9223372036854775808	9223372036854775807	unsigned
18446744073709551615