在MySQL数据库的使用过程中，很多线上问题其实都源于设计阶段的不规范操作，比如字段类型选错导致存储空间浪费、索引设计不合理引发全表扫描、表结构冗余造成数据一致性问题等。通过提前制定并遵守统一的设计规约，能够从根源上减少这类问题的发生。
一、表结构设计规约
表结构是数据库设计的基础，不合理的表结构会直接影响后续的数据存储和查询效率。
1. 表命名规范
表名需要清晰体现业务含义，建议使用小写字母和下划线组合，避免使用MySQL保留关键字。比如用户相关的表可以命名为user_info，订单相关的表命名为order_detail，不要使用user、order这类容易和关键字冲突的名称。
2. 必备基础字段
每张业务表建议都包含以下基础字段，方便后续的数据管理和问题排查：
- id：主键，建议使用自增无符号整数，避免使用UUID作为主键，否则会导致索引碎片增加，影响查询性能
- create_time：记录创建时间，默认值为当前时间戳
- update_time：记录更新时间，每次更新时自动更新为当前时间戳
- is_deleted：逻辑删除标识，默认值为0，删除时更新为1，避免使用物理删除导致数据无法恢复
3. 避免宽表设计
单张表的字段数量建议控制在30个以内，如果字段过多可以考虑拆分表。比如用户表如果同时包含基本信息和详细的扩展信息，可以拆分为user_base和user_extend两张表，通过用户id关联，减少单表的数据量。
二、字段类型选择规约
字段类型的选择直接影响存储空间和查询效率，需要根据实际业务场景选择最合适的类型。
1. 数值类型选择
优先选择占用空间小的类型，比如存储年龄可以使用TINYINT UNSIGNED，而不是INT；存储金额时如果需要精确到分，可以使用DECIMAL(10,2)，避免使用FLOAT或DOUBLE导致精度丢失。
2. 字符串类型选择
固定长度的字符串使用CHAR类型，比如手机号、身份证号，长度固定为11位和18位；可变长度的字符串使用VARCHAR类型，长度根据实际业务需求设置，不要盲目设置过大的长度，比如存储用户昵称设置VARCHAR(50)即可，不需要设置VARCHAR(255)。
3. 时间类型选择
存储时间优先使用DATETIME或TIMESTAMP类型，不要使用字符串存储时间。TIMESTAMP占用4个字节，支持的时间范围是1970-01-01到2038-01-19，DATETIME占用8个字节，支持的时间范围更大，可以根据需求选择。
三、索引设计规约
索引是提升查询性能的关键，但是不合理的索引设计反而会降低写入性能，需要遵循以下规约。
1. 索引创建原则
- 优先为经常作为查询条件、排序条件、分组条件的字段创建索引
- 联合索引需要遵循最左前缀原则，比如创建了
(a,b,c)的联合索引，那么查询条件包含a、a和b、a和b和c时都可以命中索引，但是只包含b或者c则无法命中 - 不要为低区分度的字段创建索引，比如性别字段只有男和女两个值，创建索引的意义不大
2. 避免索引失效的场景
以下场景会导致索引失效，需要尽量避免：
- 查询条件中对索引字段使用函数或者表达式，比如
WHERE YEAR(create_time) = 2024 - 查询条件中使用
LIKE以通配符开头，比如WHERE name LIKE '%张三' - 查询条件中使用
OR连接，且其中一个字段没有索引 - 字符串类型的索引字段查询时没有加引号，比如
WHERE phone = 13800138000，phone是VARCHAR类型，需要写成WHERE phone = '13800138000'
3. 索引数量控制
单张表的索引数量建议控制在5个以内，过多的索引会导致写入、更新、删除操作变慢，因为每次数据变更都需要维护对应的索引。
四、SQL编写规约
即使表结构和索引设计合理，不规范的SQL编写也会引发性能问题。
1. 查询语句规范
不要使用SELECT *查询所有字段，只查询需要的字段，减少数据传输量和数据库负载。比如只需要用户id和昵称，就写成SELECT id,name FROM user_info，而不是SELECT * FROM user_info。
2. 分页查询优化
大分页查询时避免使用LIMIT 100000,10这种写法，会导致全表扫描然后丢弃前面的数据，可以使用延迟关联优化：
-- 优化前的大分页查询
SELECT * FROM order_detail LIMIT 100000,10;
-- 优化后的延迟关联查询
SELECT t.* FROM order_detail t
INNER JOIN (
SELECT id FROM order_detail ORDER BY id LIMIT 100000,10
) tmp ON t.id = tmp.id;
3. 批量操作规范
批量插入数据时，尽量使用一条SQL语句插入多条数据，而不是循环执行单条插入语句，减少和数据库的交互次数。比如批量插入用户数据：
-- 批量插入写法
INSERT INTO user_info (name,age,phone) VALUES
('张三',20,'13800138000'),
('李四',22,'13800138001'),
('王五',25,'13800138002');
4. 事务使用规范
事务的范围尽量小，避免长事务，长事务会占用数据库连接，还可能导致锁等待甚至死锁。如果需要在事务中执行多个操作，尽量把耗时短的操作放在前面，减少事务的持有时间。
五、常见错误规避案例
下面通过一个实际案例说明不遵守设计规约会引发的问题：
某业务表product_info存储商品信息，最初设计时使用VARCHAR(255)存储商品价格，并且没有为category_id字段创建索引，查询某个分类下的商品时执行SELECT * FROM product_info WHERE category_id = 10，随着数据量增长到100万条，查询耗时超过5秒。
按照设计规约优化后：
- 将
price字段类型从VARCHAR(255)改为DECIMAL(10,2)，避免字符串比较和精度问题 - 为
category_id字段创建普通索引，查询时可以命中索引，不需要全表扫描 - 查询语句改为
SELECT id,name,price FROM product_info WHERE category_id = 10，只查询需要的字段
优化后同样的查询耗时降低到100毫秒以内，性能提升明显。
六、总结
MySQL设计规约的核心是在设计阶段就考虑性能、可维护性和扩展性，避免后期出现难以解决的问题。以上规约都是实际开发中总结的经验，不需要全部照搬，可以根据自身业务场景调整，但是核心原则是不变的：合适的结构、合适的类型、合适的索引、规范的SQL，这样才能让数据库稳定高效地支撑业务发展。