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

(if the data types of the two associated columns are not the same, implicit conversion will be performed during the association, resulting in invalid indexes on the columns and greatly reduced query efficiency)

Use innodb uniformly without special needs

Unified character set can avoid garbled caused by character set conversion. Database and table character sets use utf8 (if you want to store emoticons, you need to use utf8's extended character set, but you must be unified).

All tables and fields should be annotated

Maintain the data dictionary from the very beginning

Try to control the amount of data in a single table (historical data archiving, sub-database and sub-table), and it is recommended to keep it within 500w rows (too large tables have great problems in modifying table structure, backup and recovery)

Use mysql partitioned tables with caution

Choose partition keys carefully and try to avoid cross-partition queries, which may be less efficient.

The advantage of choosing a partition: because the partition table is physically represented as multiple files (favorable disk io) and logically as a table

For order tables: if you use the order number as the partition key to partition, if you want to query all of someone's orders, you will query across partitions, or even scan all partitions, which may be less efficient than querying a single large table.

It is suggested that the physical table should be used to manage big data.

Try to separate hot and cold data and reduce the width of the table (reduce disk io to ensure memory cache hit rate of hot data)

Make more efficient use of caching to avoid reading useless cold data (such as select *)

Prohibit the establishment of reserved fields in the table

(for mysql, the cost of modifying a field is much greater than adding one.)

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

Database stress testing in online production environment is prohibited

1. Face-to-face access to normal business

two。 It will generate a lot of junk data, which will cause trouble in the future.

It is prohibited to generate environmental databases directly from the development environment and the test environment (causing damage to data integrity)

Method

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.