Shulou(Shulou.com)06/01 Report--

This article is to share with you about the usage specification of MySQL database. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

A platform or system with the passage of time and the increase in the number of users, the database operation will often become slow; and in the Java application development, the database is particularly important, in most cases the performance of the database determines the performance of the program, such as the earlier buried more pits to the later database will become the bottleneck of the whole system; therefore, a more standardized use of MySQL is indispensable in the development.

I. MySQL database naming convention

1. All table prefixes in the database use the project name acronym.

2. All object names in the database are in lowercase letters, and words are separated by underscores.

3. MySQL reserved words and keywords are prohibited for all object names in the database. SQL queries involving keywords need to enclose keywords in single quotation marks.

4. The names of all objects in the database should not exceed 32 characters, and the naming should follow the principle of knowing the meaning by name.

5. The database temporary table must be prefixed with pro_tmp_ and suffixed with date 20190917, and the backup table must be prefixed with pro_bac and suffixed with timestamp. (pro is the initials of the project name)

6. All column names and column types that store the same data in the database must be the same.

II. Basic Design Specification for MySQL Database

1. If there is no special instruction, the Innodb storage engine will be used when creating the table.

Choosing the right engine can improve database performance. For example, InnoDB and MyISAM,InnoDB and MyISAM are the two most commonly used table types for many people when using MySQL. These two table types have their own advantages and disadvantages, depending on the specific application.

The basic differences are: MyISAM type does not support advanced processing such as transaction processing, while InnoDB type supports; tables of MyISAM type emphasize performance, which executes faster than InnoDB type, but does not provide transaction support, while InnoDB provides transaction support and advanced database functions such as external keys

Therefore, it supports transaction processing, foreign keys, crash repair and concurrency control are the preferred storage engine when building tables.

2. UTF8 is used in the character set of database and table.

The character set of database and table uses utf8. If there are fields that need to store emoji emoticons, set the table or field to utf8mb4; because utf8 is known as universal code, which requires no transcoding, no risk of garbled code and saves space, while utf8mb4 is backward compatible with utf8.

3. All tables and fields must be annotated when designing the database

Use Comment clauses to add comments for tables and columns, or add comments directly to the comments bar of the database connection tool to maintain the data dictionary from the beginning of the project.

Use Comment clauses to add comments such as:

-- 1. Create table: CREATE TABLE T1 (id varchar2 (32) primary key,name VARCHAR2 (8) NOT NULL,age number);-- 2, add table notes: Comment on table T1 is' personal information';-- 3, add field notes: comment on column t1.id is' id';comment on column t1.nameis' name'; comment on column t1.age is' age'

Use the database connection tool to add comments:

4. The data size of a single table is controlled within 5 million.

Try to control the amount of data in a single table, and it is recommended that it be controlled within 5 million; 5 million is not the limit of MySQL database, but too much data is not conducive to modifying the table structure, backing up and restoring data. Appropriate means such as sub-database and sub-table are used to control the amount of data in a single table.

5. Be careful when using MySQL partition tables

Partitioning is to divide the data of a table into several smaller, easier-to-manage parts in a certain way, for example, according to time and month, but it is still a table logically; a partitioned table is physically represented as multiple files, and logically it is still the same table, so you need to choose the partitioning key carefully; cross-partition query may be less efficient, and it is recommended to use physical partition tables to manage big data.

6. try to meet the separation of hot and cold data and reduce the width of the table.

MySQL limits each table to store a maximum of 4096 columns, and the size of each row of data is no more than 65535 bytes. In order to reduce the overhead of disk IO threads, it is necessary to properly control the width of the table, because the wider the table, the more memory it takes to load the table into the memory buffer pool, and the more IO threads will be consumed. In addition, in order to ensure the in-memory cache hit rate of hot data, make more effective use of cache, avoid reading useless cold data, try to put frequently used columns into the same table, and avoid unnecessary association operations.

7. Caution should be taken in establishing reserved fields.

When designing database tables, some friends not only designed the fields currently needed, but also set aside a few fields as backup. For example, I have designed a personnel table (Person), which has added various necessary fields, including name (Name), gender (Sex), date of birth (birthday), and so on.

Just in case, for example, the Person table may later involve the graduate college, work unit, marriage, photos and other information, so add five varchar2 fields, called Text1, Text2... The operation of Text5; seems to be against it, but in fact, it is not so, because a large number of reserved fields will waste space, the reserved fields can not see the meaning of the name, the reserved fields can not confirm the stored data type, and changing its field type may also cause table locking and other problems.

In view of these situations, you can refer to the following two solutions:

If the quantity is small and the nature of the information is closely related to the original table, then you can directly add fields to the original table and update the relevant data.

If the number is large, or if it is not a critical property of the original table object, you can add a new table and connect it by key value

8. It is prohibited to store large binary data such as pictures and files in the database.

If the file is stored in the database table, and the file is usually very large, when the database reads, a large number of random IO operations will be carried out, and the large file makes the IO operation very time-consuming and performance-consuming, resulting in a rapid growth of data in a short period of time; therefore, pictures and files are usually stored in the file server, and the database is only used to store file address information.

III. MySQL database field design specification

1. Give priority to selecting the smallest data type that meets the storage needs.

The main consideration is the performance of the index, because the larger the fields of the column, the more space needed to build the index, so there are fewer index nodes that can be stored in a page, the more IO times are needed during traversal, and the worse the performance of the index.

2. Avoid using TEXT and BLOB data types

Avoid using TEXT and BLOB data types. The most common TEXT type can store 64K data. Temporary tables in MySQL memory do not support big data types such as TEXT and BLOB. If such data are included in the query, memory temporary tables cannot be used when performing sorting and other operations. Disk temporary tables must be used for operations.

TEXT and BLOB types can only use prefix indexes (when the index is a long sequence of characters, the index will take up a lot of memory and will be very slow, so the prefix index will be used; the so-called prefix index is to go to the first few letters of the index as the index, but to reduce the repetition rate of the index, we also have to judge the repetition rate of the prefix index. Because MySQL is limited to the length of index fields, TEXT types can only use prefix indexes, and there can be no default values on TEXT columns

If you need to use it, it is recommended to separate the BLOB or TEXT columns into a separate extension table, and do not use select * when querying, just take out the necessary columns.

3. Avoid using ENUM enumerated types

The ALTER statement is required to modify the ENUM value

ORDER BY of ENUM type is inefficient.

Prohibit the use of numeric values as enumerated values for ENUM.

4. The default values of all columns are defined as NOT NULL

All the columns in the database that are NULL require extra space to store, so it takes up more space.

The database needs to do special processing on the NULL value when comparing and calculating.

5. Use TIMESTAMP (4 bytes) or DATETIME (8 bytes) to store time

The time range of TIMESTAMP storage is: 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

TIMESTAMP occupies 4 bytes the same as INT, but its readability is higher than that of INT type. If it is out of the range of TIMESTAMP values, DATETIME type storage is used.

The disadvantages of using the string type to store time: the date function cannot be used for comparative calculation, and the string storage takes up more space.

6. The amount data related to finance must use decimal type.

Precision floating point: decimal

Imprecise floating point: float, double

The Decimal type is an accurate floating-point number and does not lose precision in the calculation; the amount of space occupied is determined by the defined width, with 9 digits for every 4 bytes and one byte for the decimal point; in addition, the Decimal type can be used to store larger data types than bigint.

IV. MySQL Index Design Specification

1. The number of indexes per table does not exceed 5.

Indexes can increase query efficiency, but it will also reduce the efficiency of inserts and updates, and even in some cases, query efficiency, so it is not the more the better, to control its number.

2. Each Innodb table must have a primary key

Innodb is an index organization table in which the logical order of data storage and the order of indexes are the same.

Each table can have multiple indexes, but the table can only be stored in one order. Innodb organizes the table according to the order of the primary key index, so do not use frequently updated columns, UUID, MD5, HASH, and string columns as primary keys. These columns do not guarantee the growth of the data order, and the primary key recommends using self-increasing ID values.

3. Avoid using foreign key constraints as much as possible

Foreign key constraints (foreign key) are not recommended, but be sure to build indexes on the associated keys between tables

Although the foreign key can ensure the referential integrity of the data, the foreign key will also affect the write operation of the parent table and child table, thus reducing the performance, and make the table more coupled, which is recommended to be implemented on the business side.

Fifth, MySQL database SQL development specification

1. It is recommended to use precompiled statements for database operation.

Precompiled sentences can be reused, and the same SQL statements can be parsed at once and used many times, which can reduce the time needed for SQL compilation and improve processing efficiency. In addition, it can effectively solve the problem of SQL injection caused by dynamic SQL.

2. Avoid implicit conversion of data types

Implicit conversion such as: SELECT 1 + "1"; numeric + character implicit conversion may lead to index failure, as well as some unexpected results.

3. Make full use of the indexes that exist in the table

1) avoid using query conditions with double% numbers

For example, if WHERE first_name like'% James%', has no prefix% but only post%, the index on the column will be used when the SQL statement is executed, and the index on the column will not be used by the double% sign.

2) A SQL statement can only use one column in the composite index to query the range.

For example, for a federated index with weight, age, and sex columns, and a range query with a weight column in the query condition, the index on the age and sex columns will not be used; therefore, when defining a federated index, if a column needs to use a range query, put the column to the right of the federated index.

3) use not exists instead of not in

Because not in causes the index to fail when it is executed in a SQL statement.

4. Do not use SELECT *. You must use SELECT query.

Because using SELECT * queries consumes more CPU, IO, and network broadband resources, and override indexes cannot be used when querying.

5. Prohibit the use of INSERT statements without field lists

For example, INSERT into table_name values ('1pm, 2pm, 3'); change to an INSERT statement with a field list: INSERT into table_name (c2pm, c3') values (1p, c2m, c3')

6. Avoid using subqueries, which can be optimized to join associated operations.

However, when the general subquery is in the in clause, and the subquery is simple SQL (that is, it does not contain union, group by, order by, limit clauses), the subquery can be transformed into a join associated query for optimization.

Reasons for poor performance of subqueries:

The result set of the subquery cannot use the index, and the result set of the general subquery is stored in the temporary table, and there is no index in either the memory temporary table or the disk temporary table, so the query performance will be affected to a certain extent.

Because the subquery will produce a large number of temporary tables and no indexes, it will consume too much CPU and IO resources, resulting in a large number of slow queries.

7. Avoid using JOIN to associate too many tables

1) in Mysql, for multiple tables in the same SQL association (join), each join will allocate one more associated cache. The more tables associated in a SQL, the more memory will be consumed.

2) if the operation of multi-table association is widely used in the program, and the setting of join_buffer_size (the number of associated caches allowed by MySQL) is unreasonable, it is easy to cause the server memory overflow, which will affect the stability of the server database performance.

3) in addition, for association operations, temporary tables will affect query efficiency, while Mysql allows a maximum of 61 tables to be associated, and no more than 5 are recommended

8. When making or judgment on the same list of objects, use in instead of or

As long as there are no more than 500 values involved in in, in operations can make more efficient use of indexes, and indexes are rarely used by or in most cases.

9. Do not use order by rand () for random sorting

10. Prohibition of function conversion and calculation of columns in WHERE clauses

Because the index cannot be used when the column is functionally converted or evaluated in the WHERE clause.

No recommends:

Where date (end_time) = '20190101'

Recommended:

Where end_time > = '20190101' and end_time < '20190102'

11. Use UNION ALL instead of UNION when it is clear that there are no duplicate values

1) UNION will put all the data of the two result sets into a temporary table before deduplicating.

2) UNION ALL no longer removes duplicates from the result set

12. Split the complex and long SQL into several small SQL for execution

1) large SQL is logically complex, which takes up a lot of CPU to calculate a SQL statement.

2) in MySQL, a SQL statement can only be evaluated using one CPU

3) after the SQL is split, the processing efficiency can be improved by parallel execution.

VI. Code of conduct for MySQL database

1. Batch operations (update delete insert) of more than 1 million rows of data are performed multiple times.

Large quantities of operations may cause serious master-slave delays.

When binlog logs are in row format, a large number of logs will be generated.

Avoid operation of big things.

2. Use pt-online-schema-change to modify the table structure for large tables

1) avoid the master-slave delay caused by large table modification and avoid locking the table when modifying the table field

2) pt-online-schema-change first creates a new table with the same structure as the original table, modifies the table structure on the new table, then copies the data from the original table to the new table, and adds some triggers to the original table Then, the new data in the original table is also copied to the new table. After all the data in the row is copied, the new table is named as the original table, and the original table is deleted. The original DDL operation is divided into several small batches for execution.

3. It is prohibited to grant super permissions to accounts used by the program.

When the maximum number of connections is reached, one user with super permission to connect to super can only be used by the DBA account dealing with the problem.

4. For the program to connect to the database account, follow the principle of minimum permissions

The database account used by the program can only be used under one database, and the account used by the program does not grant drop permission in principle.

Thank you for reading! On the use of MySQL database specifications to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.