Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the specifications for the use of MySQL". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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)

You can only use English letters, numbers, underscores and begin 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 prohibited for more than 32 characters. It is recommended to use nouns rather than verbs.

Database, data tables all use prefixes

Temporary libraries and table names must be prefixed with tmp and suffixed with date

Backup libraries and 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.

Case rules under Linux

Database names and table names are strictly case-sensitive

Aliases for tables are strictly case sensitive

Column names and column aliases are case ignored in all cases

Variable names are also strictly case-sensitive

What if the hump naming has been set up? 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]"

The 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

Those who can use int do not need char or varchar.

Those who can use tinyint do not need int.

Use UNSIGNED to store non-negative values.

ENUM or SET types are not recommended. Use TINYINT instead.

Use short data types, such as TINYINT UNSIGNED when the value range is 0-80

DECIMAL must be used instead of FLOAT and DOUBLE to store exact floating point numbers

Time field, except for special circumstances, all use int to record unix_timestamp

The storage year uses the 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 or BLOB types as much as possible

It is forbidden to use VARBINARY or BLOB 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, set NOT NULL for all fields and set default values

Prohibit storing plaintext passwords in the database

If there is no comment, all Boolean fields, such as is_hot and is_deleted, must have a default value set to 0

If there is no comment, the sort field order_id is sorted in descending order by default in the program

Do not add length 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

Consider 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

Table must have a primary key

Do not use frequently updated columns

Try not to select string columns

Do not use UUID MD5 HASH

Unique keys that are not empty are used by default

It is recommended to choose self-increment or number generator.

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

WHERE conditional columns of UPDATE and DELETE statements

Fields of ORDER BY, GROUP BY, DISTINCT

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.

According to the left prefix principle, when a joint index is established, the query condition can only walk the index if it contains (a) or (a). When (a) is used as a condition, only a column index can be used, so at this time, it is necessary to make sure that there are not too many return columns in a, otherwise the sentence design is unreasonable.

Create a joint index reasonably (avoid redundancy), (a), (a), (b), (a), (b), (b)

Index taboo

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

Do not perform mathematical and functional operations in the index column

Do not index commonly used small tables

Try not to use foreign keys

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

Operations on parent and child tables affect each other, reducing availability

INNODB's own restrictions 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

Pass only parameters, which is more efficient than passing SQL statements

Parse once, use it many times

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% leading, such as like "% ab"

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

Unable to use index, resulting in full table scan

Full table scan results in lower buffer pool utilization

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

Let the database do what it does best

Reduce business coupling, leaving room for sacle out and sharding

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.

Unable to use index

Reduce the number of interactions with the database

INSERT... ON DUPLICATE KEY UPDATE

REPLACE 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 big SQL and split it into small SQL

Make full use of QUERY CACHE

Make full use of multicore CPU

The number of values that use in instead of or,in does not exceed 1000

Prohibit using order by rand ()

Use EXPLAIN diagnostics to 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:

Select * from test_union1 union all select * from test_union2

The program should have a handling mechanism to catch SQL exceptions.

Prohibit a single SQL statement from updating multiple tables at the same time

Instead of using select *, the SELECT statement only gets the required fields

Consume CPU and IO, consume network bandwidth

Cannot use override index

Reduce the impact of table structure changes

Because of its large size, select/join may generate temporary tables

UPDATE and DELETE statements do not use LIMIT

The INSERT statement must explicitly specify the field name without using INSERT INTO table ()

The INSERT statement uses batch to submit (INSERT INTO table VALUES (), (), ()... ), the number of values does not exceed 500

Use COUNT (*) instead of COUNT (primary_key) and COUNT (1) to record numbers in statistical tables. Note: for Myisam only

To update the data, it is recommended to use the secondary index to query the primary key, and then update the data according to the primary key.

Prohibit the use of cross-library queries

The use of subqueries is prohibited. It is recommended to convert subqueries into associated queries.

For program processing of varchar type fields, please verify the user input and 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.

Hash the table with HASH, the table name suffix uses a decimal number, and the subscript starts at 0

The date and time table should be in the format of YYYY [MM] [dd] [HH].

Adopt appropriate sub-database and sub-table strategy. For example, ten tables in thousands of libraries, ten hundred tables in ten libraries, etc.

The use of partitioned tables is prohibited. Partitioned tables have strict requirements on partitioning keys, and it becomes more difficult for partitioned tables to perform DDL, SHARDING, single table recovery, and so on, after the table becomes 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 to DBA in advance to assist observation.

Prohibit the execution of background management and statistical queries from the library online

Prohibit the existence of application accounts with super permissions

Timely notify DBA to assist in troubleshooting when there is a failure caused by the product not caused by database

DBA must be notified in advance for traffic assessment for promotional activities or launch new features.

If the database data is lost, contact DBA in time for recovery

Multiple alter operations on a single table must be merged into one operation

Do not store business logic in MySQL database

DBA must be notified in advance to participate in database scheme selection and design for major projects.

Communicate with DBA in advance to determine maintenance and backup priorities for particularly important database tables.

Do not update or query other specifications of the database in batches during the business peak.

Submit online table creation and 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.

This is the end of the content of "what are the specifications for the use of MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.