Location>code7788 >text

MySQL query table building specifications

Popularity：531 ℃/2025-03-16 17:32:11

Because I have been searching for some better database specifications before, so that when connecting MySQL to query/building tables during development, it can be executed according to the specifications, so as to improve the query speed/execute SQL performance and improve the overall performance of MySQL. Here, some better database design specifications are mainly stored (mainly used).A colleague in the companyThe compiled database specifications have been authorized by the boss), and I have added some rules on this basis.

1. Basic Specifications

  1. Use the innodb storage engine. Use the TokuDB storage engine later.
  2. Table character set uses UTF8mb4
  3. All tables and table fields must be noted, and the table notes format is:Business description of the person, creation time, table
  4. All tables must add primary keys and self-increment ids. Self-increment columns have no business meaning and need to be added.
  5. Production mysql database does not allow storage of big data such as pictures and files
  6. Stress testing is prohibited on online databases
  7. It is recommended to build a partition table with large data volume
  8. Forbidden use of foreign keys Allow foreign key dependencies in the logical sense
  9. If you can use the proxy account link to the database, try to use the proxy account link to facilitate the operation and maintenance of database migration work in the future.
  10. If only query operations are involved, use an account that only checks permissions to create a link, for example:SupersetInquiry on the report establishment in the bi system
  11. Generally speaking, the database account used by online services is the one written, because online services generally do not involve changing the field/table definition, and management permissions account is generally only used to develop local connections by yourself.
  12. When applying/creating a database, you can usually use the master configuration, that is, one library is used as the master library and one library is used as the master library to read it.
  13. Modify the test library at the ddl level. Scripts need to be submitted to the upgrade document. Execute the scripts together during upgrade. Ensure that the test library and the official library are unified.

2. Naming Specifications

  1. The table name is recommended to have the business English name, starting with the first letter + "_", for example: data_service business, the table name should all start with d_, and be controlled within 15 characters.
  2. Table name characters are prohibited from exceeding 32 characters
  3. MySQL reserved words are prohibited
  4. Temporary library and table names must betmpprefixed with date as suffix
  5. Backup libraries and tables mustbakprefixed with date as suffix
  6. The library and table recommendations for operation log types arelogAs the suffix
  7. Normal index naming:idx_start + field name(Single-stage fields are named with full field, and double-stage or third-stage uses the first letter of the first and last-stage, for example: create_time index is named: idx_ctime)
  8. Unique index name:uniq_start + field nameMethod reference secondary index naming

3. Data table design specifications

  1. The auto-increment id must be added unsigned
  2. The field setting is not null must have a default value
  3. Use text types less, use varchar as much as possible, and prohibit the use of blob types. If blobs are really unavoidable, please disassemble the table.
  4. Unsigned is required to store non-negative integers
  5. FLOAT and DOUBLE are prohibited to store floating point numbers, and use DECIMAL instead.
  6. Don't use as much as possibledefault null, use null unless necessary, it is recommended to use it insteaddefault 0ordefault ''(To avoid the null value query needs to be processed separately when using query), and it is not recommended to use it.default ''
  7. All business fields such as type and status use the TINYINT type. Varchar is prohibited. If varchar cannot be avoided, the enum type is used.
  8. Use datetime storage time (after mysql5.6)
  9. Use numerical type fields well. If the numerical field is not that big, don't use bigint
  10. It is prohibited to store plain text passwords in the database, encrypt the password and store it
  11. Use partition tables as much as possible
  12. Before table design, the word length of the field must be evaluated to avoid unnecessary space waste and performance loss.
  13. It is recommended to control the number of fields in a single table to within 20. It is recommended to divide the vertical tables more.
  14. Foreign key constraints are generally not created on the database, but only express a logical concept and are controlled by the program.
  15. All tables must have create_time and update_time, and add triggers (CURRENT_TIMESTAMP and CURRENT_TIMESTAMP ON UPDATE)

Four. Index specification

  1. The table must have a primary key (clustered index)
  2. Do not use UUID MD5 HASH these as primary keys (numerical values ​​are too discrete)
  3. By default, non-empty unique keys are used as primary keys
  4. Generally, self-increasing columns are primary keys, and the partition keys of the partition table can be compounded with self-increasing columns.
  5. The number of single index fields shall not exceed 5
  6. Prioritize coverage indexes
  7. Avoid redundancy and duplicate indexing
  8. Avoid using fields with low cardinality as indexing, such as gender, type, status
  9. Low-base numeric fields are recommended to establish a composite index based on where predicate conditions.
  10. The fields after ORDER BY, GROUP BY, DISTINCT, WHERE must be indexed according to the actual situation.
  11. The default value of the index field cannot be NULL
  12. If you can use a unique index, you must use a unique index, but the requirement is that the word length cannot exceed 8 bits and non-discrete fields, and the super 8 bits and discrete fields, and use a prefix index.

5. SQL Development Specifications

  1. Use is prohibited in the codeselect *
  2. Do not use scalar subquery, change external connections as much as possible
  3. Table association is controlled within 3 tables
  4. Use primary keys whenever possible in table association fields
  5. It is prohibited to sub-query for large tables
  6. The table association sorting field must be in the driver table
  7. The table-related fields must have an index
  8. Don't use scalar subquery, change to left join or right join as much as possible
  9. Not usedin/not in/exists/not existsSubquery, change to inner join as much as possible
  10. Forbidden to make big tables and big tables
  11. OR is rewritten to IN () or UNION
  12. Use union all instead of union
  13. The pagination needs to be rewritten as follows:select id,text from test limit 10000,10; --> select , from (select id from test a limit 10000,10) left join test b on =
  14. It is not recommended to use like with a prefix of % (index cannot be used)
  15. Not recommendedwhereIn the conditionsin (subquery), even if in is an index fieldrefer to

6. Table creation example

1_ddl_New demo table.sql

CREATE TABLE `c_demo` (
   `id` bigint (11) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar (50) NOT NULL default '' COMMENT ' Name ',
   `creator` varchar (50) NOT NULL default '' COMMENT ',
   `is_deleted` tinyint (2) NOT NULL DEFAULT '0' COMMENT ' Is logically deleted 0. No 1. Yes ',
   `create_time` datetime NOT NULL DEFAULT '2020-01-13 00:00:00' COMMENT ' Creation time ', -- This is for compatibility with versions before MySQL 5.6. Versions before 5.6 cannot create two fields with CURRENT_TIMESTAMP triggered at the same time
   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' Update time ',
   PRIMARY KEY (`id`),
   KEY `idx_name_deleted` (`name`,`is_deleted`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='zhangsan-2020.1.3 - Test Case '

refer to

  1. Questions and answers on Zhihu about "How to deal with some details in database design?"
  2. Alibaba's Java Development Manual (Songshan Edition).pdf
<Previous Next>