MySQL Schema Design and SQL Development Standards Practice

Tencent Cloud Academy announced a live broadcast featuring MySQL experts Ye Jinrong and Wu Bingxi, focusing on MySQL schema design and development standards. The session is scheduled for September 17 at 20:00 and can be reserved by scanning the QR code.

Key development standards presented:

1. Default to InnoDB engine – InnoDB supports almost all MySQL use cases and is the default for system tables since MySQL 8.0.

2. Use utf8mb4 as the default character set – It avoids garbled text and supports emojis; latin1 may be used only to save space.

3. Keep InnoDB row size under 8 KB – Rows larger than ~8 KB are stored on overflow pages; large TEXT/BLOB columns should be moved to separate tables.

4. Table partitioning – Useful for performance or operational convenience, but not a universal solution; excessive partitions can cause replication lag.

5. Stored procedures and triggers – Acceptable in appropriate scenarios, but avoid them for distributed architectures because they do not work across instances.

6. Choose appropriate data types – Use INT UNSIGNED for IPv4, TINYINT for enums, prefer DATETIME over TIMESTAMP , always add NOT NULL unless NULL is truly needed, and avoid SELECT * when large BLOB/TEXT columns exist.

7. Index usage – Limit to about five indexes per table, each with no more than five columns; use prefix indexes for long strings; monitor CPU usage as high consumption often indicates bad index usage; be aware of optimizer decisions that may switch to full table scans; ensure sorting columns belong to the driving table in multi‑table joins.

8. Additional best practices – Regularly analyze slow‑query logs with pt‑query‑digest , avoid DDL or heavy backups during peak traffic, enable sql_safe_updates , verify critical SQL is optimized before production release, and always cast user inputs to the proper type to prevent SQL injection.

The announcement also promotes a comprehensive MySQL course covering architecture, optimization, and migration, aimed at technical directors, architects, developers, DBAs, and operations engineers.