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

This article mainly introduces the specification for the use of MySQL, which is very detailed and has a certain reference value. Friends who are interested must finish it!

Recently, there are many operations related to the database, and the company's existing norms are not too comprehensive. According to the relevant norms of various gods on the Internet, I have sorted out some standard usage for my own use, and Wanwang corrected it.

Database environment

Dev: development environment

The development can be read and write, and the table structure can be modified. Developers can modify the table structure, and the data in it can be modified at will, but they need to ensure that it does not affect other developer colleagues.

Test: test environment

Developers can read and write, and developers can modify the table structure through tools.

Online: online environment

Developers are not allowed to operate the database directly in the online environment, and if they need to do so, they must go to DBA for operation and record them accordingly, and stress testing is prohibited.

The key issue is that the user rights corresponding to the mysql servers in each environment must be clearly defined, identifiable, and able to specifically distinguish business scenarios.

Naming convention

Basic naming rules

Use meaningful English words with an underscore in the middle. (do not use pinyin) only use English letters, numbers, underscores, and start with the English letter. Libraries, tables, fields are all lowercase, do not use hump naming. Avoid using reserved words like ORACLE and MySQL, such as desc, and keywords such as index. Naming is forbidden to exceed 32 characters. It is recommended that nouns should be used instead of verb databases, data tables should always use prefix temporary database, table names must be prefixed with tmp, and date must be used as suffix backup library. Tables must be prefixed with bak and suffixed with date.

Why are libraries, tables, and fields all lowercase?

In MySQL, database and table pairs are located in directories and files under those directories. Therefore, the sensitivity of the operating system determines the case sensitivity of database and table names.

Windows is case-insensitive. Under Linux, database names and table names are strictly case-sensitive; table aliases are strictly case-sensitive; column names and column aliases are case-sensitive in all cases; variable names are also strictly case-sensitive; what if hump naming has been set? You need to add lower_case_table_names = 1 to the configuration file my.ini of MySQL.

Table naming

Tables of the same module use the same prefix as much as possible, and table names express meaning as much as possible. All log tables begin with log_

Field naming

An English word or abbreviation that expresses its actual meaning. The field of Boolean meaning is prefixed with is_, followed by the past participle of the verb. Fields with the same meaning between tables should have the same name. Fields with the same meaning between tables are named after the table name _ field name with the module prefix removed. The foreign key field represents its association with the table name _ field name. The primary key of a table is generally agreed to be id, and the self-increasing type is that the foreign keys of other tables are indicated by xxx_id.

Index naming

Non-unique indexes must be named according to "idx_ field name _ field name [_ field name]" unique index must be named according to "uniq_ field name _ field name [_ field name]"

Constraint naming

Primary key constraint: pk_ table name. Unique constraint: uk_ table name _ field name. (uniqueness checking logic is required in the application at the same time. )

Table design specification

The table engine depends on the actual application scenario; myisam is recommended for log and report tables, and innodb engine is recommended for tables related to transactions, audits, and amounts. If there is no instruction, the innodb engine will be used when building tables.

The utf8mb4 character set is used by default, and the database collation is utf8mb4_general_ci. (because the database definition uses the default, the data table can no longer be defined, but to be on the safe side, it is recommended to write

Why utf8 is not selected for character set and utf8_general_ci is not used for collation

MySQL with utf8 encoding cannot save Emoji emoticons with a placeholder of 4 bytes. In order to make the back-end project, fully support the client input Emoji facial expressions, upgrade coding to utf8mb4 is the best solution. For cases where characterEncoding is set to utf8 for the JDBC connection string or emoji data cannot be inserted normally after the above configuration, you need to specify the character set of the connection as utf8mb4 in the code.

All tables and fields apply the comment column attribute to describe the true meaning represented by the table and field. For example, it is recommended to define the contents used in this field.

If not specified, the first id field in the table must be the primary key and grow automatically, prohibiting data transfer as a condition as context within a non-transaction. The use of varchar types as primary key statement design is prohibited.

If not specified, the table must contain create_time and modify_time fields, that is, the table must contain fields for record creation time and modification time

If not specified, the table must contain is_del to indicate whether the data has been deleted. In principle, physical deletion of database data is not allowed.

Use as little storage space as possible to store the data of a field. If you can use int, you don't need char or varchar. If you can use tinyint, you don't need int to use UNSIGNED to store non-negative values. It is not recommended to use ENUM or SET types, but use TINYINT instead of short data types. For example, when the value range is 0-80, you must use DECIMAL instead of FLOAT and DOUBLE time fields to store exact floating point numbers using TINYINT UNSIGNED. Except in special cases, int is used to record unix_timestamp storage years. Use YEAR type. The storage date uses the DATE type. The storage time (accurate to seconds) is recommended to use the TIMESTAMP type because TIMESTAMP uses 4 bytes and DATETIME uses 8 bytes. It is recommended that you use INT UNSIGNED to store IPV4. Do not use TEXT, BLOB types as much as possible to prohibit the use of VARBINARY, BLOB in the database to store pictures, files, etc. It is recommended to use other methods of storage (TFS/SFS). MySQL only saves pointer information. Single record size is prohibited from exceeding 8k (column length (Chinese) _ 3 (UTF8) + column length (English) _ 1)

What is the difference between datetime and timestamp?

Similarities:

The TIMESTAMP column is displayed in the same format as the DATETIME column. The display width is fixed at 19 characters and the format is YYYY-MM-DD HH:MM:SS.

Differences:

TIMESTAMP

4-byte storage, time range: 1970-01-01 08:00:01-2038-01-19 11:14:07 values are saved in UTC format, involving time zone conversion, the current time zone is converted when storage, and then back to the current time zone when searching. Datetime8 byte storage, time range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59 actual format storage, regardless of time zone

How do I use the auto-assignment property of TIMESTAMP?

Take the current time as the default value for ts: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP. When the row is updated, update the value of ts: ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP.

You can combine 1 and 2: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

How do I use INT UNSIGNED to store ip?

Use INT UNSIGNED instead of char (15) to store ipv4 addresses and convert them through the MySQL functions inet_ntoa and inet_aton. Currently, there is no conversion function for Ipv6 address, and you need to use DECIMAL or two bigINT to store it.

If there is no comment, NOT NULL is set for all fields and default values are set; if plaintext passwords are not stored in the database, all Boolean fields, such as is_hot and is_deleted, must be set to a default value and set to 0; if there is no comment, the sort field order_id is arranged in descending order by default in the program; length is not added to the shaping definition, such as using INT instead of INT [4]

INT [M], what does M value mean?

Note that the number after the parenthesis of the numeric type only indicates the width and has nothing to do with the storage range. Many people think that the values of INT (4) and INT (10) range from (- 9999 to 9999) and (- 9999999999 to 9999999999) respectively, which is wrong. In fact, the M value in an integer can be used in conjunction with the ZEROFILL attribute to achieve column values of equal width. Regardless of the M value in INT [M], the range of values is (- 2147483648 to 2147483647 signed), (0 to 4294967295 unsigned).

The display width does not limit the range of values that can be saved in the column, nor does it limit the display of values that exceed the specified width of the column. When used in conjunction with the optional extension attribute ZEROFILL, the default added space is replaced by zero. For example: for a column declared as INT (5) ZEROFILL, the value 4 is retrieved as 00004. Note that if you save a value that exceeds the display width in an integer column, MySQL will encounter problems when generating temporary tables for complex joins, because in these cases MySQL believes that the data is appropriate for the width of the original column, and if you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to that column.

Using VARBINARY to store case-sensitive variable-length strings

When to use CHAR, when to use VARCHAR?

CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They are also different in terms of maximum length and whether trailing spaces are retained. The length of the CHAR and VARCHAR type declarations indicates the maximum number of characters you want to save. For example, CHAR (30) can occupy 30 characters.

The length of the CHAR column is fixed to the length declared when the table was created. The length can be any value from 0 to 255. When the Char values are saved, fill in blanks to the right of them to reach the specified length. When the Char value is retrieved, the trailing space is deleted. No case conversion occurs during storage or retrieval.

The value in the VARCHAR column is a variable length string. The length can be specified as a value between 0 and 65535. The maximum effective length of a VARCHAR is determined by the maximum line size and the character set used. The overall maximum length is 65532 bytes). In contrast to CHAR, the VARCHAR value is saved with only the number of characters required, plus one byte to record the length (two bytes are used if the column declaration is longer than 255bytes). The VARCHAR value is not populated when saved. The trailing space is retained when the value is saved and retrieved, which conforms to the standard SQL.

Char is suitable for storing the MD5 hash of a user's password, which is always the same length. Char is also better than varchar for values that change frequently, because fixed-length rows are not easily fragmented, and char is more efficient than varchar for short columns. The char (1) string takes only one byte for a single-byte character set, but varchar (1) takes up two bytes because one byte is used to store length information.

Index design specification

The query speed of MySQL depends on good index design, so indexes are essential for high performance. A reasonable index will speed up the query (including UPDATE and DELETE, MySQL will load the page containing the row into memory, and then perform UPDATE or DELETE operations), and unreasonable indexes will slow down. The MySQL index search is similar to the pinyin and radical search of Xinhua Dictionary. When the pinyin and radical indexes do not exist, it can only be found by turning pages. When an MySQL query cannot use an index, MySQL performs a full table scan, which consumes a lot of IO. The purpose of the index: deduplicating, accelerating positioning, avoiding sorting, overwriting the index.

What is an overlay index?

In the InnoDB storage engine, secondary index (non-primary key index) does not directly store row addresses and primary key values. If users need to query data columns that are not included in secondary index, they need to find the primary key value through secondary index first, and then query other data columns through the primary key, so they need to query twice. The concept of the overlay index is that the query can be completed in an index, the overlay index will be more efficient, and the primary key query is a natural overlay index. Reasonable creation of indexes and reasonable use of query statements can improve performance when overriding indexes are used. For example, SELECT email,uid FROM user_email WHERE uid=xx, if uid is not the primary key, you can add the index as index (uid,email) in due course to achieve a performance improvement.

Basic specification of index

The number of indexes is controlled, the number of indexes in a single table does not exceed 5, and the number of fields in a single index does not exceed 5. Comprehensive assessment of data density and distribution taking into account the query and update ratio

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

InnoDB's secondary index is stored using b+tree, so b+tree needs to be adjusted in UPDATE, DELETE, and INSERT. Too many indexes will slow down the update.

Prefix indexing is used for strings. The prefix index length is no more than 8 characters. It is recommended to give priority to prefix indexing, and pseudo columns can be added and indexed if necessary.

Do not index fields such as blob/text, do not index large fields, this will make the index take up too much storage space

What is a prefix index?

To put it bluntly, the prefix index is to index the first few characters of the text (specifically, several characters are specified when indexing), so the index is smaller, so the query is faster. Prefix index can effectively reduce the size of index file and improve the speed of index. But prefix indexes also have their disadvantages: MySQL cannot use prefix indexes in ORDER BY or GROUP BY, nor can they be used as override indexes (Covering Index).

Syntax for prefix indexing: ALTER TABLE table_name ADD KEY (column_name (prefix_length))

Primary key criterion

The table must have primary keys, do not use frequently updated columns, try not to select string columns, do not use UUID MD5 HASH by default, use non-empty unique keys, suggest choosing self-increment or numberer

Important SQL must be indexed, and core SQL gives priority to overriding index

Fields of WHERE condition columns ORDER BY, GROUP BY, DISTINCT of UPDATE and DELETE statements, fields of multi-table JOIN

The fields with the highest degree of differentiation are put in front.

Select fields with better filtering properties at the front, such as order number, userid, etc., type,status and other screening properties are generally not recommended to be placed in the front index according to the left prefix principle, when a joint index is established. Then the query condition can only walk the index when it contains (a) or (a), and (a) can only use a column index when it is used as a condition, so make sure that there are not too many columns returned by an at this time, otherwise the sentence design will be unreasonable, (bforce c) can not use the index to create a joint index (to avoid redundancy). (a) equivalent to (a), (a), (b), (a)

Index taboo

Do not build indexes on low cardinality columns, such as "gender" does not perform mathematical and functional operations on index columns. Do not index small tables that are commonly used. Try not to use foreign keys to protect referential integrity. Operations on parent and child tables can affect each other on the business side, reducing the limitations of availability INNODB itself on online DDL.

Restrictions on indexes in MYSQL

The total index length of the MYISAM storage engine cannot exceed 1000 bytes

Columns of BLOB and TEXT types can only create prefix indexes

MYSQL currently does not support functional indexing.

MYSQL cannot use indexes when using is not equal to (! = or).

After the filter field uses a function operation, such as abs (column), MYSQL cannot use the index.

MYSQL cannot use the index when the join conditional field type is inconsistent in the join statement

When using a LIKE operation, if the condition starts with a wildcard (such as'% abc …') The index cannot be used by MYSQL.

MYSQL cannot use Hash indexes when using non-equivalent queries.

Sentence design specification

Use precompiled statements

Only pass parameters, which is more efficient than passing SQL statements to parse at one time, and use it many times to reduce the probability of SQL injection.

Avoid implicit conversion

Will cause the index to fail.

Make full use of prefix index

Must be the leftmost prefix. It is impossible to use two range conditions at the same time. Queries that do not use% prefixes, such as like "% ab"

Do not use negative queries, such as not in/like

Unable to use index, resulting in full table scan, resulting in reduced buffer pool utilization

Avoid using stored procedures, triggers, UDF, events, etc.

Let databases do what they do best to reduce business coupling, leaving room for sacle out and sharding to avoid BUG.

Avoid using JOIN with large tables

What MySQL is good at is primary key / secondary index query of single table.

JOIN consumes more memory and produces temporary tables

Avoid mathematical operations in the database

MySQL is not good at mathematical operations and logical judgment can not use the index

Reduce the number of interactions with the database

INSERT... ON DUPLICATE KEY UPDATEREPLACE INTO, INSERT IGNORE, INSERT INTO VALUES (), (), UPDATE... WHERE ID IN (10, 20, 50, …)

Reasonable use of paging

Limit the number of pages displayed in paging only by clicking on the previous page and using delayed association on the next page.

How to use paging correctly?

If there is a paging statement similar to the following: SELECT * FROM table ORDER BY id LIMIT 10000, 10 because the way LIMIT OFFSET is handled in MySQL is to take out all the data from OFFSET+LIMIT, then remove the OFFSET and return the LIMIT at the bottom. Therefore, when the OFFSET value is large, the query performance of MySQL will be very low. You can use id > n to solve the problem:

The use of id > n has limitations, and the problem of id discontinuity can be solved by passing in the last id when turning the page.

Http://example.com/page.php?last=100 select * from table where id110 order by id desc limit 10

The big disadvantage of this approach is that if there is an insert / delete operation in browsing, the page turn will not be updated, and the total number of pages may still be calculated based on the new count (*), resulting in some records not being accessed. To fix this problem, you can continue to introduce operations that affect the total number of records, such as the current page number and whether there are insertions / deletions after the last page turn, and cache them.

Select * from table where id > = (select id from table order by id limit # offset#, 1) reject large SQL, split into small SQL to make full use of QUERY CACHE to make full use of multicore CPU to use in instead of or,in, no more than 1000 prohibit the use of order by rand () to use EXPLAIN diagnostics, and avoid generating temporary tables

The EXPLAIN statement (executed in the MySQL client) can get information about how MySQL executes the SELECT statement. By executing EXPLAIN on the SELECT statement, you can know whether MySQL uses information such as indexes, full table scans, temporary tables, sorting, and so on, when executing the SELECT statement. Try to avoid MySQL to scan full tables, use temporary tables, sort and so on. See the official documentation for details.

Use union all instead of union

What's the difference between union all and union?

Both the union and union all keywords combine two result sets into one, but they are different in terms of usage and efficiency.

Union filters out duplicate records after table linking, so it sorts the resulting result set after table linking, deletes duplicate records and returns the result. Such as:

Select * from test_union1 union select * from test_union2

This SQL first takes out the results of the two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may lead to sorting by disk if the table has a large amount of data.

Union all simply merges the two results and returns. In this way, if there is duplicate data in the two result sets returned, the returned result set will contain duplicate data.

In terms of efficiency, union all is much faster than union, so if you can confirm that the two merged result sets do not contain duplicate data, use union all, as follows:

The select * from test_union1 union all select * from test_union2 program should have a handling mechanism to catch SQL exceptions. It forbids a single SQL statement to update multiple tables at the same time without using select *. The SELECT statement only gets the required field consumption CPU and IO, and consumes network bandwidth and cannot use the overlay index to reduce the impact of table structure changes. Because of large, select/join may generate temporary tables UPDATE, DELETE statements do not use the limit statement must explicitly specify the field name Do not use the INSERT INTO table () INSERT statement and use batch to submit (INSERT INTO table VALUES (), (), ()... ), use COUNT (*) instead of COUNT (primary_key) and COUNT (1) when the number of values does not exceed the number of records in 500 statistical tables. Note: only for Myisam data update, it is recommended to use the secondary index to query out the primary key first, and then to update the data according to the primary key to prohibit the use of cross-database queries and prohibit the use of subqueries. It is recommended to convert subqueries into associated queries for the program processing of varchar type fields. Please verify the user input. Do not exceed its preset length

Sub-table specification

If the amount of data in a single table exceeds 500w or the data capacity exceeds 10G in one to two years, you need to consider the historical data migration in advance or the application to delete the historical data by itself. You can use the same amount of balanced sub-table or sub-table according to business rules. The data table to be divided into tables must discuss the sub-table strategy with DBA.

Use HASH to hash the table, the table name suffix uses a decimal number, and the subscript starts from 0 to divide the table according to the date-time format of YYYY [MM] [dd] [HH] and adopt the appropriate database and table strategy. For example, partitioned tables are forbidden to use partitioned tables, and partitioned tables have strict requirements on partitioning keys. It is more difficult for partitioned tables to perform DDL, SHARDING, single table recovery and so on after the tables become larger. Split large fields and fields with low access frequency to separate hot and cold data

Code of conduct

Batch import and export data must be notified in advance to DBA to assist in observing the prohibition of performing background management and statistical queries from the database online. Applications with super permissions are prohibited from having failures caused by products other than the database. Timely notify DBA to assist in troubleshooting promotion activities or launch new functions must notify DBA in advance to evaluate the loss of database data. Contact DBA in time to recover multiple alter operations on a single table must be merged into one operation database scheme selection and design of major items of business logic not stored in MySQL database must inform DBA in advance to participate in particularly important database tables, communicate with DBA in advance to determine maintenance and backup priorities, do not update in batch during peak business periods, query database other specifications submit online table modification requirements All relevant SQL statements must be specified in detail

Other norms

It is not recommended to store log data on MySQL. Priority is given to Hbase or OceanBase. If storage is needed, please find DBA for evaluation and use compressed table storage.

The above is all about the use of MySQL specifications, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.