MySQL性能优化之表设计优化

上一篇 <<<MySQL性能优化之慢查询定位
下一篇 >>>

---

通用设计

1、所有表和字段都需要添加中文注释
2、不要使用关键字保留字
3、尽量全英文或全中文
4、建议字符存储均以utf-8编码，如果需要存储表情，则选择utf8mb4编码。

表设计

1、使用小写字母、数组和下划线组合，禁止出现数字开头
2、不使用复数名词
3、命名长度不超过30个字符（官方限制64）
4、前缀定义：临时-tmp/temp、统计-stat/tj
5、后缀定义：备份加bak、删除加delete、归档加完成日期(如20210101)
6、索引定义：唯一索引(uk字段名)、普通索引(idx字段名)
7、单表行数超过500万行或单表容量超过2GB，才推荐进行分表分库。【阿里巴巴JAVA开发手册】
如果预计三年后的数据量达不到这个级别，请不要在创建时就分表分库

字段设计

1、字段偏移量不要超过50%，尽可能不用text、Blob、Clob等类型，它会增加存储空间的占用，读取速度较慢

2、能用数字型字段就不要设计为字符型，因为字符型锁占的存储空间更大。
a、能用tinyint的不用int，int占4字节，tinyint占1字节
b、int有符号最大值2147483647，无符号位是4294967295，所以主键用int足以

3、尽量使用varchar/nvarchar代替char/nchar，因为变长字段空间小，可以节省存储空间。
长度固定的话，建议用char。
varchar长度不要超过5000，超过的话考虑用text，独立一张表。【阿里巴巴JAVA开发手册】
不在数据库中存储图片、文件等大数据。
不固定的文本类型，用关联表，并先压缩，不要写到核心表中

4、时间类型尽量用datetime和timestamp，查询频率大的用int。

5、金额用decimal，注意长度和精度。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小树分开存储。【阿里巴巴JAVA开发手册】
禁止使用float和double，会存在精度损失的问题。

mysql> CREATE TABLE t3 (c1 float(10,2),c2 decimal(10,2));       
Query OK, 0 rows affected (0.05 sec)
>mysql> insert into t3 values (999998.02, 999998.02);    
Query OK, 1 row affected (0.01 sec)
>mysql> select * from t3;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 999998.00 | 999998.02 |
+-----------+-----------+
1 row in set (0.00 sec)

6、尽可能不要给数据库留null值，尤其是时间、整数等类型。
**a、可避免where子句中对null值的判断，null值有的时候会统计不到

b、null值很难进行查询优化，索引统计更加复杂，还需要mysql内部进行特殊处理
**

其他

1、禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。【阿里巴巴JAVA开发手册】