1. All database object names must be separated by underscores (_) and lowercase letters.
  2. Disallow MySQL reserved keywords for all database object names (e.g. Desc, range, match, delayed, etc.)
  3. Temporary library tables must be prefixed with TMP and suffixed with date (tmp_)
  4. Backup libraries and libraries must be prefixed with bak and suffixed with date (bak_)
  5. All column names and column types that store the same data must be the same. (Fields such as user_id in multiple tables must be of the same type)
  6. Mysql5.5 default storage engine is MyISam, no special requirement, all tables must use InnoDB (InnoDB support error, row-level locking, high concurrency performance, multi-core, large memory, SSD support better)
  7. The character set of the database and table should be unified as far as possible using UTF8. (The character set must be unified to avoid garbled characters caused by character set conversion. Chinese characters in UTF8 account for 3 bytes.)
  8. All tables and fields are commented, and the data dictionary is maintained from the start
  9. Try to limit the size of a single table to 500W, over 500W can be archived, database and table. (5 million rows is not the limit of MySQL database. Too large for modify table structure, backup, recovery will be a big problem. MySQL has no storage restrictions, depending on storage Settings and file system)
  10. Use mysql partitioned tables with caution (partitioned tables physically appear as multiple files and logically as one table)
  11. Careful selection of partition keys may reduce cross-partition query efficiency
  12. You are advised to use physical sub-tables to manage big data
  13. Try to separate hot and cold data and reduce the width of the table (mysql limits the number of rows to 4096, but the total number of bytes per row cannot exceed 65535). Column restriction benefits: Reduces disk IO, ensures memory cache hit ratio for hot data, and avoids reading useless cold data.)
  14. Do not create reserved fields in the table (the table will be locked if the data type of the reserved fields is not confirmed).
  15. Disallow storing images, files binary data in data (using file server)
  16. Do not perform database stress tests online
  17. Do not connect the development environment or test environment directly to the production environment database
  18. Limit the number of indexes on each table. You are advised to limit the number of indexes in a single table to 5 (indexes increase query efficiency, but reduce insert and update speed).
  19. Avoid creating redundant and duplicate indexes (redundant: index(a,b,c) index(a,b) index(a))
  20. Disallow creating a separate index for each column in a table
  21. Every innoDB table must have a primary key, select auto-increment ID (UUID,MD5,HASH, string columns cannot be used as primary key)
  22. The most differentiated column is placed at the far left of the union index
  23. Try to place columns with the smallest field length at the far left of the union index
  24. Avoid using foreign keys (physical foreign keys are prohibited, logical foreign keys are recommended)
  25. The smallest data type that meets storage requirements is preferred
  26. Unsigned integers are preferred for storage
  27. The smallest data type is preferred (vARCHar (N), where N represents the number of characters, not bytes, and N represents how many Chinese characters can be stored)
  28. Avoid using Text or Blob types
  29. Avoid using ENUM data types (ALTER statements are required to change ENUM values. The ORDER BY operation of ENUM types is inefficient and requires additional operations. Do not use book values as ENUM values
  30. Define as many fields as possible as NOT NULL (index NULL requires extra space to store, so more memory is needed temporarily, and special handling of NULL values is required for comparison and calculation)
  31. Use the timestamp or datetime type to store time
  32. For financing-related amount data, use decimal (no loss of precision, no float and double)
  33. Avoid the use of double % signs and like, and do not use left or full blur (use search engines if necessary). Index files have the left-most prefix matching feature of b-tree. If the left value is not determined, then this string cannot be used.
  34. You are advised to use precompiled statements for database operations
  35. Prohibit cross-library query (leave room for data migration and sub-database sub-table, reduce coupling degree, reduce risk)
  36. Disable select * query (consume more CPU and IO and network bandwidth resources, cannot use coverage index)
  37. Insert into T values (‘ a ‘, ‘b’, ‘c’) not allowed
  38. If the in operation can be avoided, it can be avoided. If it really cannot be avoided, it is necessary to carefully evaluate the number of set elements following in and control it within 1000
  39. Disallow random sorting using order by rand ()
  40. Disallow column conversions and calculations in WHERE clauses (e.g., where date (createtime) = ‘20160901’). Where createtime>=’20160901′ and createTime <‘20160902’)
  41. Try to use union all instead of union
  42. Split complex large SQL into multiple small SQL (MySQL can only use one CPU per SQL)
  43. Instead of using subqueries, you can optimize subqueries to join operations. (Indexes cannot be used in the result set of subqueries, and temporary table operations will be generated in subqueries. If the subquery has a large amount of data, the efficiency will be affected and excessive CPU and IO resources will be consumed.)
  44. Batch write operations of more than 1 million rows must be performed multiple times in batches (Large batch operations may cause serious primary-slave delay. Binlog in row format generates a large number of logs, avoiding large transaction operations).
  45. Modify table structure with pt-online-schema-change for large tables (avoid master/slave delay caused by large table changes and avoid table locking when table fields are modified)
  46. For application connection database accounts, follow the principle of minimum permissions
  47. More than three tables disallow joins. (The data type of the field to be joined must be absolutely the same; When multiple tables are used for associated query, ensure that the associated fields have indexes. Pay attention to table indexes and SQL performance even when double table JOINS.
  48. When creating an index on a VARCHAR field, the index length must be specified. It is not necessary to create an index for all fields.
  49. Target for SQL performance optimization: at least range level, ref level, conSTS is preferred
  50. Use ISNULL() to determine whether a NULL value is present.
  51. Try not to use physical delete (delete directly, if you want to delete in advance to make a backup), but use logical delete, use the field delete_flag for logical delete, the type is tinyint, 0 indicates that is not deleted, 1 indicates that is deleted
  52. If there is an order by scenario, take advantage of the orderliness of the index. The last field of order by is part of the index combination and placed at the end of the index combination order to avoid file_sort, which affects query performance.
  53. When writing paging query logic in code, if count is 0, return directly to avoid subsequent paging statements

Reference:

  1. Alibaba Java Development Manual
  2. “High-performance scalable MySQL Database Design and Architecture Optimization E-commerce Project”

Welcome to point out corrections and supplements!