工程开发之MySQL设计规约

        数据库作为整个工程数据存储和利用的重要工具，在设计过程中务必遵循相应的规范（规约），以保证后期运行的稳定性和用户使用体验（包括管理后台用户、终端用户使用体验等）。

一、建表规约

        建表规约的好处是解决数据库相关名称的纠结，以便选择恰当的表格命名、合适的数据类型和长度。

1. 表、字段命名

必须使用小写字母或数字

禁止出现数字开头（eg. 字段名：7day_sales）

禁止两个下划线中间只出现数字（eg. 字段名：update_1_time）

不使用复数名词（eg. 字段名：members）

禁用保留字（eg. 字段名：order）

是与否概念的字段，必须使用is_xxx的方式命名

2. 数据类型选择

小数类型为decimal

货币数据使用最小货币单位，数据类型为bigint

字符串长度几乎相等使用char

varchar长度不要超过5000

3. 强制规约（！！）

表必备三字段：id / create_time / update_time

4. 推荐规约（⭐）

表的命名最好是遵循“业务名称_表的作用“

库名与应用名称尽量一致

如果修改字段含义或对字段表示状态追加时，需及时更新字段注释

字段允许适当冗余，以提高查询性能，但必须考虑数据一致

单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表

二、索引规约

        表格中利用索引是提高查询效率的有效手段，因此有必要合理地分析数据库表，并适当添加索引。

1. 索引分类

按存储形式：聚簇索引 / 非聚簇索引

按数据约束：主键索引 / 唯一索引 / 非唯一索引

按索引列的数量：单列索引 / 组合索引

innoDB可以创建的索引：主键索引 / 唯一索引 / 普通索引（不可以创建覆盖索引）

2. 索引的数据结构（理想的选择：即适合等值查询，也适合范围查询的数据结构）

①二叉查找树（平衡二叉查找树更优，可避免二叉树退化成链表这类极端情况）

注：MySQL创建的索引需持久化到本地磁盘，随着数据量增大，可预见根节点的数量也会越多，即树高度很高，查询时IO的次数也会增多，故可考虑更佳的数据结构——btree

②btree（可理解为n叉树；MySQL最小读取单元是16K，因此每个节点放入多个数据，提高效率同时减少资源浪费）

注：btree在范围查询上仍不是最理想，故使用进一步优化的数据结构——b+tree

③b+tree（中间节点只保存键值，不保存数据；磁盘块之间还有双向指针，叶子节点构成双向链表）

当前，MySQL使用的索引都是基于b+tree的数据结构！

3. 索引名称规约

主键索引名为pk_字段名

唯一索引名为uk_字段名

普通索引名为idx_字段名

4. 创建索引规约

有唯一特性的字段必须建成唯一索引

在varchar字段上建立索引时，必须指定索引长度

建组合索引时，区分度最高的在最左边

5. 需避免的误解

索引宁滥勿缺（即嫌索引太少）

吝啬索引创建（认为索引太多影响性能）

抵制唯一索引（认为唯一索引一律需要在应用层通过“先查后插“方式解决）

三、SQL与ORM映射规约

SQL规约-索引：注意字段类型；利用覆盖索引；利用有序性；禁模糊

SQL规约-count：拒绝替代（列名/常量替代*）；针对NULL值的分析与处理

SQL规约-分页：分页查询逻辑时count为0直接返回；利用延迟关联或子查询优化超多分页场景

SQL规约-NULL值：使用ISNULL()来判断是否为NULL值

eg. NULL<>NULL  NULL=NULL  NULL<>1   这三种返回的结果均为NULL

SQL规约-避坑指南：

不使用外键与级联；禁止使用存储过程；数据订正前先select，确认无误再执行更新；涉及多表时，列名前需加表(别)名；别名前加as；in后边集合元素数量，控制在1000个之内

ORM映射规约：

查询时勿用*；POLO类的布尔属性不加is，而数据库字段必须加”is_”；

查询返回结果需使用ResultMap映射；不使用${}；不使用MyBatis自带的queryForList方法；不允许直接使用HashMap与Hashtable接收结果集；更新数据表记录时必须同时更新update_time；不写大而全的数据更新接口