Summary of MySQL development standards and usage skills

1. Naming conventions

1. Database names, table names, and field names must use lowercase letters and be separated by underscores.

a)MySQL has a configuration parameter lower_case_table_names, which cannot be changed dynamically. The default value of the Linux system is 0, which means that the library and table names are stored according to the actual situation and are case-sensitive. If it is 1, it is stored in lowercase, case insensitive. If 2, store as is, but compare in lower case.

b) If uppercase and lowercase letters are mixed, multiple tables such as abc, Abc, ABC, etc. may coexist, which may easily lead to confusion.

c) Field names are displayed as case-sensitive, but are not actually used, that is, you cannot create two fields with the same name but different case.

d) To unify standards, use lowercase letters for library names, table names, and field names.

2. The library name, table name, and field name must not exceed 32 characters.

The library name, table name, and field name support a maximum of 64 characters. However, for the sake of uniformity, easy identification, and reduced transmission volume, no more than 32 characters are allowed.

3. Use the INNODB storage engine.

The INNODB engine is the default engine after MySQL version 5.5. It supports transactions and row-level locks, has better data recovery capabilities, better concurrency performance, and better support for multi-core, large memory, SSD and other hardware. It supports hot data backup, etc. Therefore, INNODB has obvious advantages over MyISAM.

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

When attributes such as library names, table names, and field names contain reserved words, SQL statements must use backticks to quote the attribute names, which makes SQL statement writing and variable escaping in Shell scripts very complicated.

5. The use of partition tables is prohibited.

Partitioned tables have strict requirements on partition keys. When partitioned tables become larger, it becomes more difficult to execute DDL, SHARDING, and single-table recovery. Therefore, the use of partition tables is prohibited, and manual SHARDING is recommended on the business side.

6. It is recommended to use UNSIGNED to store non-negative values.

For the same number of bytes, non-negative storage has a larger range of values. For example, TINYINT has a signed value of -128-127 and an unsigned value of 0-255.

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

Using UNSINGED INT to store an IP address takes up 4 bytes, while CHAR(15) takes up 15 bytes. Additionally, computers process integers faster than strings. Use INT UNSIGNED instead of CHAR(15) to store IPv4 addresses, and convert them using the MySQL functions inet_ntoa and inet_aton. There is currently no conversion function for IPv6 addresses, so they need to be stored using DECIMAL or two BIGINTs.

For example:

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

8. It is strongly recommended to use TINYINT instead of ENUM type.

When the ENUM type needs to modify or add enumeration values, online DDL is required, which is costly. If the ENUM column value contains numeric types, it may cause confusion about the default value.

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

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

10.INT type occupies 4 bytes of storage

For example, INT(4) only means that the display character width is 4 bits, not the storage length. The number after the bracket of a numeric type only indicates the width and has nothing to do with the storage range. For example, INT(3) displays 3 digits by default, with spaces added to fill the width. If the width exceeds the limit, the display will be normal. Python and Java clients do not have this function.

11. Distinguish between using DATETIME and TIMESTAMP.

To store years, use the YEAR type. To store dates, use the DATE type. It is recommended to use the TIMESTAMP type to store time (accurate to seconds).

Both DATETIME and TIMESTAMP are accurate to seconds. TIMESTAMP is preferred because TIMESTAMP only has 4 bytes, while DATETIME has 8 bytes. At the same time, TIMESTAMP has the characteristics of automatic assignment and automatic update. Note: In versions 5.5 and earlier, if a table has multiple timestamp columns, at most one column can have the automatic update feature.

How to use the automatic assignment attribute of TIMESTAMP?

a) Automatic initialization and automatic update:

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b) Just automatically initialize:

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c) Automatic update, the initial value is 0:

column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d) The initial value is 0:

column1 TIMESTAMP DEFAULT 0

12. All fields are defined as NOT NULL.

a) For each row of the table, each NULL column requires additional space to identify it.

b) B-tree indexes do not store NULL values, so if the index field can be NULL, the index efficiency will decrease.

c) It is recommended to use 0, special values ​​or empty strings instead of NULL values.

MySQL usage tips

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

It is conducive to the effective use of cache, preventing the reading of useless cold data, reducing disk IO, and ensuring that hot data resides in memory to improve the cache hit rate.

2. It is prohibited to store plain text passwords in the database.

Use encrypted strings to store passwords and ensure that the passwords cannot be decrypted. Use random strings and salt to ensure password security.

3. The table must have a primary key. It is recommended to use an UNSIGNED auto-increment column as the primary key.

If the table does not have a primary key, INNODB will set a hidden primary key column by default; a table without a primary key is very difficult to locate data rows, and it will also reduce the efficiency of row-based replication.

4. Redundant indexes are prohibited.

Indexing is a double-edged sword that increases the maintenance burden and increases IO pressure. (a,b,c), (a,b), the latter is a redundant index. Prefix indexes can be used to achieve acceleration and reduce maintenance burden.

5. Duplicate indexing is prohibited.

primary key a;uniq index a;Duplicate indexes increase the maintenance burden and take up disk space without providing any benefit.

6. Do not create indexes on low cardinality columns, such as "Gender".

In most scenarios, precise search with indexes on low-cardinality columns has no advantage over full table scans without indexes, and increases the IO burden.

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

Covering indexes can obtain all required fields from the index, thus avoiding returning to the table for a second search and saving IO.

In the INNODB storage engine, the secondary index (non-primary key index, also known as auxiliary index, secondary index) does not directly store the row address, but stores the primary key value.

If the user needs to query a data column that is not included in the secondary index, the user needs to first find the primary key value through the secondary index, and then query other data columns through the primary key, so the query needs to be performed twice. Covering indexes can obtain all required data in one index, so they are more efficient.

For example SELECT email，uid FROM user_email WHERE uid=xx . If uid is not the primary key, you can add an index as index(uid, email) to improve performance.

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

IN is a range search. MySQL will sort the IN list values ​​internally before searching, which is more efficient than OR.

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

a) The UTF8 character set takes up 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 characters during transcoding.

c) If you need to store 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.

11. Do not use order by rand().

order by rand() will add a pseudo column to the table, then use the rand() function to calculate the rand() value for each row of data, and then sort based on the row. This usually generates a temporary table on disk, so it is very inefficient. It is recommended to use the rand() function to obtain a random primary key value first, and then obtain data through the primary key.

12. It is recommended to use a reasonable paging method to improve paging efficiency.

If there is a paging statement similar to the following:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000, 10;

This paging method will cause a lot of IO because MySQL uses a read-ahead strategy.

Recommended paging method:

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.
SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) as t
USING(id)

13.SELECT only obtains necessary fields. SELECT * is prohibited.

Reduce network bandwidth consumption;

Can effectively use covering indexes;

Changes to the table structure have little impact on the program.

14. Avoid using functions with uncertain results such as now(), rand(), sysdate(), and current_user() in SQL.

In statement-level replication scenarios, master-slave data is inconsistent; SQL statements generated by functions with uncertain values ​​cannot use QUERY CACHE.

15. Adopt appropriate database and table sharding strategies. For example, one thousand warehouses and ten tables, ten warehouses and one hundred tables, etc.

Adopting an appropriate database and table sharding strategy is conducive to the rapid horizontal splitting of the database in the later stage of business development. At the same time, database sharding can effectively utilize the multi-threaded replication feature of MySQL.

16. Reduce the number of interactions with the database and try to use batch SQL statements.

Use the following statement to reduce the number of interactions with the db:

a) INSERT ... ON DUPLICATE KEY UPDATE

b)REPLACE INTO

c) INSERT IGNORE

d) INSERT INTO VALUES()

17. Split complex SQL into multiple small SQLs to avoid large transactions.

Simple SQL can easily use MySQL's QUERY CACHE; reduce table lock time, especially MyISAM; and use multi-core CPUs.

18. Multiple alter operations on the same table must be combined into one operation.

Most MySQL table modification operations require locking and rebuilding the table, and locking the table will affect online business. To reduce this impact, multiple alter operations on the table must be combined into one operation. For example, to add a field b to table t and create an index for the existing field aa,

The usual approach is divided into two steps:

alter table t add column b varchar(10);

Then add the index:

alter table t add index idx_aa(aa);

The correct way to do it is:

alter table t add column b varchar(10),add index idx_aa(aa);

19. Avoid using stored procedures, triggers, views, custom functions, etc.

These advanced features have performance issues and many unknown bugs. Putting business logic into the database will make database DDL, SCALE OUT, SHARDING, etc. more difficult.

20. Application accounts with super permissions are prohibited.

Safety first. The super permission will make read only invalid, causing many strange problems that are difficult to track.

21. Don’t store business logic in the MySQL database.

The database is a stateful service that is complex and slow to change. If you put business logic into the database, it will limit the rapid development of the business. It is recommended to put the business logic in the front-end or middle logic layer in advance, and use the database as the storage layer to achieve the separation of logic and storage.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links