MySQL Database performance

Source: Internet
Author: User

MySQL Database design specification https://www.cnblogs.com/Luke-Me/p/8994432.html

We in the project at the beginning of the design, we should be busy to consider the database design, table, field, index, SQL, and so on, and in the project relatively large, team development because of the simultaneous, so early design specification is a very critical step in project development, then about the database design specifications, The following 6 items are included:

Copy Code
1. Database Naming conventions

2. Database Basic Design Code

3. Database Index Design Specification

4. database field Design specification

5.SQL Development Specification

6. Database Operation Behavior Specification
Copy Code
The next step is to introduce each specification in detail:

1. Database Naming conventions:

1) All database object names must be split with lowercase letters and underlined (MySQL is sensitive to case)

2) Disable the use of MySQL reserved keywords (for example: Select User,from,age from Tb_user this time cannot recognize the FROM keyword, unless used from , reverse single quotation marks to distinguish)

3) The name of the database object can be found in the last known meaning, and preferably not more than 32 characters, such as: User Database Bd_userdb (Baidu _ User database), User account table User_account

4) The Temporary library table must have a TMP prefix and a date suffix, tmp_user_20180505 provide a more explicit table name

Back up the library, the backup table must be prefixed with bak and dated as a suffix, bak_userdb_20180504 bak_user_20180505

5) All column names and types that store the same data must be identical, for example: user_id (int unsigned not NULL) in different library tables, then the name and type must be consistent, otherwise the query efficiency will be reduced.

2. Database Basic Design Code:

1) All tables must use the InnoDB storage engine. After MySQL5.6, InnoDB has become the default storage engine, which supports transactional, row-level locks, better recoverability, and better performance under high concurrency

2) uniform use of UTF-8 for database and table character sets. In order to avoid garbled, performance and other issues

3) All tables and fields need to be annotated. Use comment to add note information and maintain database dictionaries from the start

4) As far as possible to control the size of the single-table data, it is recommended to control within 5 million lines. Control the size of data volumes using historical data archiving and sub-database table manipulation

5) Use the partition table in MySQL carefully. The efficiency of cross-partition query is relatively low, it is recommended to use physical table to manage big data

6) As far as possible to do cold data separation, reduce table width. Field too many cases, as far as possible, the table, will be commonly used in a piece, infrequently used fields into other tables, effectively reduce the disk IO, to ensure the cache hit rate of hot data

7) prohibit the establishment of reservation fields in the table. Because the types of reserved fields cannot be predicted, modifying the fields later can be a resource-intensive and locking table.

8) prohibit the storage of images, files and other binary files, resulting in MySQL performance impact. These should be stored in a dedicated image, file server, where only the corresponding information identity is stored in the database.

Prohibit online database stress testing

Prohibit direct-Connect build environment database from development environment and test environment

3. Index Design Specification:

1) Limit the number of indexes per table, we recommend a single table index of no more than 5. Indexing is not the more the better, can improve query efficiency, also can reduce efficiency. The index should be built according to the actual situation.

2) Each InnoDB table must have a primary key (the only non-empty column). Instead of using the frequently updated column as the primary key, do not use Md5,uuid,hash, the string column as the primary key. Primary key recommended option to use the self-increment ID value

3) Common index column suggestions: The column in the WHERE clause order BY, the group by, the field in the distinct, the Association column of the Multi-table join, if the sentence is a single column, then separate index, there are multiple columns, it can establish a federated index

4) How to select the order of the indexed columns, the columns with the highest degree of sensitivity (such as the primary key column), the field length is small, the columns with frequent use are placed on the leftmost side of the federated Index

5) Avoid creating redundant and duplicate indexes: Index (A,B,C), index (A, a), index (a) is the duplicate index for a

6) Use overwrite index for frequent query precedence: Index containing all query fields

7) Try to avoid using foreign key constraints. Foreign keys affect write operations on parent and child tables to reduce performance

4. Database field Design specification:

1) Prioritize the minimum data types that meet your storage needs. Example: Converting a string to a digital store

Unsigned is preferred for non-negative numbers to store. For example: Primary key ID, unsigned more storage space than signed.

Signed: Signed int-2147483648 ~ 2147483647

unsigned: unsigned int 0 ~ 4294967295

varchar (n) n represents the number of characters, not the number of bytes, using UTF8 to store Chinese character varchar (255) = 765 bytes

An oversized length consumes more memory and allocates memory based on the length of the field.

2) Avoid the use of text, BLOB data types, if needed, as far as possible to allocate to the special extension table

3) Avoid using enum enum types. The order by operation is inefficient. Prohibit use of numeric values as Enum enumeration value

4) Define all columns as not NULL whenever possible. The index null column requires more storage space to save. The index is invalidated.

5) Avoid using strings to store date time, use timestamp or datatime to store time

6) Financial-related amount type data, you must use the decimal type. The accuracy of the data is guaranteed and the accuracy is not lost.

5. Database SQL Development Specification:

1) It is recommended to use pre-compiled statements for database operations. Only pass parameters, more efficient than the delivery of SQL, the same statement once resolved, multiple use, saving the cost of SQL resolution, improve processing efficiency.

2) Avoid implicit conversions of data types. An implicit conversion causes an index to fail, and a type conversion that typically occurs in the WHERE clause condition causes the index to fail.

3) make reasonable use of existing indexes instead of blindly adding indexes.

Avoid using double-percent query conditions: like '%123% ', as long as the prefix% is present, the index fails.

A SQL can only take advantage of a column of the composite index to query the scope, if the Federated Index Index (A,B,C) to a scope query, then B and C will be invalidated, should put a to the far right

Use the LEFT JOIN or not exists to optimize the not in operation, which does not invalidate the index

4) The program should use different accounts when connecting different databases, prohibit cross-Library query

5) Prohibit use of SELECT * Must use Select <字段列表> query, consuming excessive IO and CPU and network bandwidth resources

6) prohibit the use of INSERT statements that do not contain fields, in order to reduce the impact of changes in the table structure: INSERT into table values (' A ', ' B ', ' C '); The column to insert should be indicated, insert into table (C1,C2,C3) VALUES (' A ', ' B ', ' C ');

7) Avoid using subqueries, you can optimize subqueries as JOIN operations: subqueries Create temporary tables, consume CPU and IO resources, and sub-query result sets cannot use indexes.

8) Avoid using joins to correlate too many tables:

Each associated with a table, taking up part of the memory (join_buffer_size)

Generates temporary table operations that affect query efficiency

MySQL is allowed to correlate up to 61 tables, with no more than 5 recommended tables

9) Reduce the number of interactions with the database

10) use in instead of OR. In has a value of less than 500, in can use the index effectively, or not.

11) It is forbidden to use ORDER by rand () for random ordering, which has a great effect on performance, and tries to get random values from the database and get the data from it.

12) Disables the function conversion and calculation of columns in the WHERE clause, which causes the index to be invalidated. where data (createtime) = ' 2018-01-01 ', try to calculate in the program as much as possible

13) Use UNION ALL instead of union when there is clearly no duplicate value. The Union loads all the data into the staging table and then goes to the weight, and the union all does not go heavy.

14) split the complex large SQL into multiple small SQL. Parallel execution of small SQL to improve processing efficiency

6. Database Operation Behavior Specification:

1) More than 1 million lines of bulk write operations, in batches to operate multiple times:

Large-scale operation can cause serious master-slave delay problems

Binlog log is in row format, Hu generates a large number of logs, resulting in insufficient resources

Avoid operations that produce large transactions

2) Use the Pt-online-schema-change tool for large tables to modify the table structure. The process is: Create a new table, then copy the old table data to the new table, change the new table name to the old table name, and finally delete the old table

3) Disable Super Super-control privileges for the account used by the program

4) For Program connection database account, follow the principle of least privilege. The program uses a database sub-branch and can only be used in one db, no cross-Library, the program uses the account in principle no drop permission

These are some of the MySQL design specifications, of course, not necessarily to follow the above principles, depending on the actual application scenario, through the DBA guidance to specify the principle.

This article original, reproduced please mark the source: http://www.cnblogs.com/Luke-Me/p/8994432.html

MySQL Database performance

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.