A, database command specification
All database object names must be lowercase letters separated by underscores.
Mysql reserved keywords are prohibited for all database object names (if the table name contains the keyword query, it needs to be enclosed in single quotation marks).
The naming of database objects should be able to identify the name and meaning, and it should not exceed 32 characters.
Temporary library tables must be prefixed with tmp_ and suffix with date, and backup tables must be prefixed with bak_ and suffix with date (timestamp).
All column names and column types that store the same data must be consistent (generally, as an associated column, if the associated column types are inconsistent during query, the data types will be automatically converted implicitly, resulting in invalid indexes on the column and reducing query efficiency).
Second, the basic design specification of the database
1. All tables must use the Innodb storage engine.
There are no special requirements (that is, functions that Innodb cannot meet, such as column storage and spatial data storage. ), all tables must use the Innodb storage engine (Myisam is used by default before mysql5.5, and Innodb is used by default after MySQL 5.6) to support transactions, support row-level locks, and have better recoverability and performance under high concurrency.
2.UTF8 is the character set used for databases and tables.
Better compatibility, unified character set can avoid the garbled code caused by character set conversion, and the index will be invalid if different character sets need to be converted before comparison.
3. All tables and fields need comments.
Use the comment clause to add comments to tables and columns. Maintain the data dictionary from scratch.
4. Try to control the data volume of a single table, and it is recommended to control it within 5 million.
5 million yuan is not the limit of MySQL database, too much will cause great problems in modifying table structure, backup and recovery.
You can use historical data archiving (applied to log data), sub-database and sub-table (applied to business data) to control the size of data.
5. Use MySQL partition table with caution.
The partition table is physically represented as multiple files and logically represented as a table. Choose partition keys carefully, and the efficiency of cross-partition query may be lower. It is recommended to manage big data through physical partitions.
6. Try to separate the hot and cold data and reduce the width of the table.
MySQL limits each table to store 4,096 more columns, and the size of each row of data cannot exceed 65,535 bytes to reduce the IO of the disk, thus ensuring the hit rate of hot data in the memory cache (the wider the table, the more memory it will occupy when loading the table into the memory buffer pool, and the more IO it will consume), making more effective use of the cache and avoiding reading useless columns that are often used with cold data into a table (avoiding more associated operations).