公司要开发一个新项目，之前是 .NET Framework + Sql Server ,这次要用 .NET Core + Mysql ,没怎么接触过 mysql ,所以做数据库设计之前，结合网上的资料，以及项目的使用，总结了一下Mysql设计规范。按照规范设计了数据库，目前项目开始正常开发，没遇到因为数据库设计产生的问题，记录一下。ps.随着开发，应该还会记录下mysql与SQL server的差异。
命名规范
基本命名原则
- 使用有意义的英文词汇，不要使用拼音或拼音缩写。
- 只能使用英文字母，数字，下划线，并以英文字母开头。
- 表名库名全部小写用_分隔，列名
Pascal(避免代码中太多attribute) - 避免用
MySQL的保留字，如desc，关键字如index。 - 命名禁止超过32个字符，须见名之意，建议使用名词不是动词
- 数据库，数据表一律使用前缀
- 临时库、表名必须以
tmp为前缀，并以日期为后缀 - 备份库、表必须以
bak为前缀，并以日期为后缀
- 临时库、表名必须以
表命名
- 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。所有日志表均以 log_ 开头
字段命名
- 表达其实际含义的英文单词或简写。布尔意义的字段以“Is”作为前缀，后接动词过去分词。
- 各表之间相同意义的字段应同名。各表之间相同意义的字段，以去掉模块前缀的表名+字段名命名。
- 外键字段用表名+字段名表示其关联关系。
- 表的主键一般都约定成为Id，自增类型，是别的表的外键均使用xxxId的方式来表明。
索引命名
- 非唯一索引必须按照
idx_字段名称_字段名称[_字段名]进行命名 - 唯一索引必须按照
uniq_字段名称_字段名称[_字段名]进行命名
约束命名
- 主键约束：
pk_表名称。 - 唯一约束：
uk_表名称_字段名。（应用中需要同时有唯一性检查逻辑。） - 外键约束：
fk_从表名称_主表名称。
触发器命名
trg_表名_操作。
函数过程命名
- 采用动词+名词的形式表达其含义。
序列命名
- seq_表名
表设计规范
- 表引擎取决于实际应用场景；日志及报表类表建议用
myisam，与交易，审核，金额相关的表建议用innodb引擎。如无说明，建表时一律采用innodb引擎 - 默认使用
utf8mb4字符集，数据库排序规则使用utf8mb4_general_ci，（由于数据库定义使用了默认，数据表可以不再定义，但为保险起见，建议都写上）。 - 所有表、字段均应用
comment列属性来描述此表、字段所代表的真正含义，如枚举值则建议将该字段中使用的内容都定义出来。 - 如无说明，表中的第一个id字段一定是主键且为自动增长，禁止在非事务内作为上下文作为条件进行数据传递。禁止使用varchar类型作为主键语句设计。
- 如无说明，表必须包含
CreateTime和ModifyTime字段，即表必须包含记录创建时间和修改时间的字段 - 如无说明，表必须包含
IsDel，用来标示数据是否被删除，原则上数据库数据不允许物理删除。 - 用尽量少的存储空间来存数一个字段的数据
- 能用int的就不用char或者varchar
- 能用tinyint的就不用int
- 使用UNSIGNED存储非负数值。
- 不建议使用ENUM、SET类型，使用TINYINT来代替
- 使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED
- 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE
- 时间字段
- 存储年使用YEAR类型。
- 存储日期使用DATE类型。
- 存储时间（精确到秒） 统一使用 DATETIME
- 尽可能不使用TEXT（mysql磁盘读取检索）、BLOB类型
- 禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。建议使用其他方式存储（TFS/SFS），MySQL只保存指针信息。
- 如无备注，所有字段都设置NOT NULL，并设置默认值；
- 禁止在数据库中存储明文密码
- 如无备注，所有的布尔值字段，如
IsHot、IsDel，都必须设置一个默认值，并设为0； - 整形定义中不添加长度，比如使用INT，而不是INT[4]
索引设计规范
MySQL的查询速度依赖良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度（包括UPDATE和DELETE的速度，MySQL会将包含该行的page加载到内存中，然后进行UPDATE或者DELETE操作），不合理的索引会降低速度。MySQL索引查找类似于新华字典的拼音和部首查找，当拼音和部首索引不存在时，只能通过一页一页的翻页来查找。当MySQL查询不能使用索引时，MySQL会进行全表扫描，会消耗大量的IO。索引的用途：去重、加速定位、避免排序、覆盖索引。
索引的基本规范
索引数量控制，单张表中索引数量不超过5个，单个索引中的字段数不超过5个。
- 综合评估数据密度和分布
- 考虑查询和更新比例
对字符串使用前缀索引，前缀索引长度不超过8个字符，建议优先考虑前缀索引，必要时可添加伪列并建立索引。
- 不要索引blob/text等字段,不要索引大型字段,这样做会让索引占用太多的存储空间
什么是前缀索引？
前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。
建立前缀索引的语法：
ALTER TABLE table_name ADD KEY(column_name(prefix_length));主键准则
- 表必须有主键
- 不使用更新频繁的列
- 尽量不选择字符串列
- 不使用
UUID MD5 HASH - 默认使用非空的唯一键
- 建议选择自增或发号器
索引禁忌
- 不在低基数列上建立索引，例如“性别”
- 不在索引列进行数学运算和函数运算
- 不要索引常用的小型表
尽量不使用外键
- 外键用来保护参照完整性，可在业务端实现
- 对父表和子表的操作会相互影响，降低可用性
- INNODB本身对online DDL的限制
MYSQL 中索引的限制
- MYISAM 存储引擎索引长度的总和不能超过 1000 字节
- BLOB 和 TEXT 类型的列只能创建前缀索引
- MYSQL 目前不支持函数索引
- 使用不等于 (
!=或者<>) 的时候, MYSQL无法使用索引。 - 过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引。
- join语句中join条件字段类型不一致的时候MYSQL无法使用索引
- 使用 LIKE 操作的时候如果条件以通配符开始 (如 '%abc…')时, MYSQL无法使用索引。
- 使用非等值查询的时候, MYSQL 无法使用 Hash 索引。
语句设计规范
- 使用预编译语句
- 只传参数，比传递SQL语句更高效
- 一次解析，多次使用
- 降低SQL注入概率
- 避免隐式转换
- 会导致索引失效
- 充分利用前缀索引
- 必须是最左前缀
- 不可能同时用到两个范围条件
- 不使用%前导的查询，如like "%ab"
- 不使用负向查询，如not in/like
- 无法使用索引，导致全表扫描
- 全表扫描导致buffer pool利用率降低
- 避免使用存储过程、触发器、UDF、events等
- 让数据库做最擅长的事
- 降低业务耦合度，为sacle out、sharding留有余地
- 避开BUG
- 避免使用大表的JOIN
- MySQL最擅长的是单表的主键/二级索引查询
- JOIN消耗较多内存，产生临时表
- 避免在数据库中进行数学运算
- MySQL不擅长数学运算和逻辑判断
- 无法使用索引
- 视情况使用以下语句，减少与数据库的交互次数
INSERT … ON DUPLICATE KEY UPDATEREPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()UPDATE … WHERE ID IN(10,20,50,…)
- 合理的使用分页
- 限制分页展示的页数
- 只能点击上一页、下一页
- 采用延迟关联
- 拒绝大SQL，拆分成小SQL
- 充分利用QUERY CACHE
- 充分利用多核CPU
- 使用in代替or，in的值不超过1000个
- 禁止使用order by rand()
- 使用EXPLAIN诊断，避免生成临时表
- 果可以确认合并的两个结果集中不包含重复的数据,用
union all而不是union - 程序应有捕获
SQL异常的处理机制 - 不使用
select *，SELECT语句只获取需要的字段 UPDATE、DELETE语句不使用LIMITINSERT语句必须显式的指明字段名称，不使用INSERT INTO table()INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不超过500- 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 备注：仅针对
Myisam - 数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新
- 禁止使用跨库查询
- 禁止使用子查询，建议将子查询转换成关联查询
- 针对varchar类型字段的程序处理，请验证用户输入，不要超出其预设的长度；