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

1. Naming convention

1. Library names, table names, and field names must be in lowercase letters and separated by underscores.

A) MySQL has a configuration parameter lower_case_table_names, which cannot be changed dynamically. The default value of linux system is 0, that is, the database table name is stored in the actual situation and is case-sensitive. If it is 1, it is stored in lowercase and is case-insensitive. If it is 2, store it in reality, but compare it in lowercase.

B) if mixed case is used, multiple tables such as abc,Abc,ABC may coexist, which can easily lead to confusion.

C) the field name display is case-sensitive, but actually makes it indistinguishable, that is, it is not possible to create two fields with the same name but different case.

D) for uniform specification, library names, table names, and field names are in lowercase letters.

two。 Library name, table name, and field name are prohibited from exceeding 32 characters.

Library names, table names, and field names support up to 64 characters, but more than 32 characters are prohibited in order to standardize, easy to identify and reduce the amount of transmission.

3. Use the INNODB storage engine.

INNODB engine is the default lead power after MySQL5.5 version, which supports transaction and row-level locks, better data recovery, better concurrency performance, better support for hardware such as multi-core, large memory, SSD, hot backup of data, etc., so INNODB has obvious advantages over MyISAM.

4. MySQL reserved words are prohibited for library names, table names, and field names.

When attributes such as library name, table name, field name, etc., contain reserved words, the SQL statement must refer to the attribute name in reverse quotation marks, which will make the writing of SQL statements and the escape of variables in SHELL scripts very complicated.

5. The use of partition tables is prohibited.

Partitioned tables have strict requirements on partitioning keys; after partitioned tables become larger, it becomes more difficult to implement rows DDL, SHARDING, single table recovery, and so on. Therefore, the use of partition tables is prohibited, and the business side is advised to manually SHARDING.

6. It is recommended that you use UNSIGNED to store nonnegative values.

With the same number of bytes, the range of values for non-negative storage is larger. For example, the signed TINYINT is-128127and the unsigned is 0255.

7. It is recommended that you use INT UNSIGNED to store IPV4.

Using UNSINGED INT to store IP addresses takes 4 bytes, while CHAR (15) takes up 15 bytes. In addition, the computer processes integer types faster than string types. Use INT UNSIGNED instead of CHAR (15) to store IPV4 addresses and convert them through the MySQL functions inet_ntoa and inet_aton. There is currently no conversion function for IPv6 addresses, and you need to use DECIMAL or two BIGINT to store them.

For example:

SELECT INET_ATON ('209.207.224.40'); 3520061480SELECT INET_NTOA (3520061480); 209.207.224.40

8. It is strongly recommended that you use TINYINT instead of the ENUM type.

The ENUM type requires online DDL when you need to modify or add enumerated values, which is expensive; if the ENUM column value contains a numeric type, it may cause confusion of default values.

9. Use VARBINARY to store case-sensitive variable-length strings or binary content.

VARBINARY is case-sensitive by default, has no concept of character set, and is fast.

The 10.INT type always takes up 4 bytes of storage

For example, INT (4) only represents the display character width of 4 digits, not the storage length. The number after the parenthesis of the numerical type only indicates the width and has nothing to do with the storage range. For example, INT (3) displays 3 digits by default, blanks are filled, and displays normally when it is exceeded. Python, java clients do not have this function.

11. Distinguish between the use of DATETIME and TIMESTAMP.

The storage year uses the YEAR type. The storage date uses the DATE type. The TIMESTAMP type is recommended for storage time (accurate to seconds).

Both DATETIME and TIMESTAMP are accurate to seconds, so TIMESTAMP is preferred because TIMESTAMP has only 4 bytes and DATETIME8 has 4 bytes. At the same time, TIMESTAMP has the features of automatic assignment and automatic update. Note: in versions 5.5 and earlier, if there were more than one timestamp column in a table, at most one column could have automatic updates.

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

A) automatic initialization and automatic update:

Column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

B) just initialize automatically:

Column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

C) automatic update with an initialization value of 0:

Column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

D) the initialization value is 0:

Column1 TIMESTAMP DEFAULT 0

twelve。 All fields are defined as NOT NULL.

A) for each row of the table, each column that is NULL requires additional space to identify.

B) the null value is not stored when the B-tree is indexed, so if the index field can be NULL, the indexing efficiency will be reduced.

C) it is recommended that null values be replaced with 0, special values, or empty strings.

Skills of using MySQL

1. Split the fields with large fields and low access frequency into separate tables for storage, and separate hot and cold data.

It is conducive to the effective use of cache, prevent reading of useless cold data, less disk IO, and ensure that hot data is resident in memory and high cache hit rate.

two。 It is prohibited to store plaintext passwords in the database.

The encrypted string is used to store the password, and the password can not be decrypted, and the random string is added with salt to ensure the security of the password.

3. The table must have a primary key, and it is recommended to use the UNSIGNED self-incrementing column as the primary key.

Tables do not have primary keys, and INNODB sets hidden primary key columns by default; tables without primary keys are very difficult to locate data rows and reduce the efficiency of row-based replication.

4. Redundant indexing is prohibited.

Index is a double-edged sword, which will increase the maintenance burden and increase the pressure on IO. (a), (b), (b), the latter is a redundant index. Prefix indexing can be used to achieve acceleration and reduce the maintenance burden.

5. Duplicate indexes are prohibited.

Primary key a share Uniq index a; duplicate indexes increase the maintenance burden and take up disk space without any benefit.

6. Indexes are not built on low-cardinality columns, such as gender.

In most scenarios, the precise lookup of indexing on low cardinality columns has no advantage over full table scans without indexing, and increases the burden on IO.

7. Reasonable use of overlay indexes to reduce IO and avoid sorting.

The overlay index can get all the required fields from the index, avoid going back to the table for secondary lookup, and save IO.

In the INNODB storage engine, secondary index (non-primary key index, also known as secondary index, secondary index) does not directly store row addresses, but 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 first through secondary index, and then query other data columns through the primary key, so they need to query twice. The overlay index can get all the data needed in one index, so it is more efficient.

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.

8. Use IN instead of OR. The IN in the SQL statement should not contain too many values and should be less than 1000.

IN is a range lookup, and MySQL will sort the list values of IN and look up them, which is more efficient than OR.

9. The table character set uses UTF8, and if necessary, you can apply for the UTF8MB4 character set.

A) the UTF8 character set takes 3 bytes to store Chinese characters and one byte to store English characters.

B) UTF8 is unified and universal, and there is no risk of garbled code in transcoding.

C) if you encounter the storage needs of emoticons such as EMOJ, you can apply to use the UTF8MB4 character set.

10. Use UNION ALL instead of UNION.

UNION ALL does not need to sort the result set again.

11. The use of order by rand () is prohibited.

Order by rand () adds a pseudo column to the table, then uses the rand () function to calculate the rand () value for each row of data, and then sorts based on that row, which usually results in a temporary table on disk, so it is very inefficient. It is recommended that you first use the rand () function to get the random primary key value, and then get the data through the primary key.

twelve。 It is recommended to use a reasonable paging method to improve the efficiency of paging.

If you have a paging statement like the following:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000 no. 10

This paging method results in a large amount of io because MySQL uses a read-ahead strategy.

Recommended paging method:

SELECT * FROM table WHERE TIME

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.