03/31Day 孤尽训练营笔记分享

最新推荐文章于 2024-09-12 18:05:56 发布

Wen Liang

最新推荐文章于 2024-09-12 18:05:56 发布

阅读量184

点赞数 1

分类专栏： T31 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_44127763/article/details/121027138

版权

T31 专栏收录该内容

31 篇文章 1 订阅

订阅专栏

今天在训练营主要学习了Mysql设计规约，是行业内通用的一套规约，以及一些平常不太注意到的细节，在此做记录以备后续工作中用到方便回来查看。

建表规约

针对数据库内名称命名的一些规范，以及不同业务场景下选择合适的数据类型和长度。

表、字段命名

• 必须使用小写字母或数字（错误例子：@@……* 等特殊符号）
• 禁止出现数字开头（错误例子：5Month）
• 禁止两个下划线中间只出现数字（错误例子：mall_5_order）
• 不使用复数名词 • 禁用保留字（错误例子：desc、delete等mysql关键字）
• 是与否概念的字段，必须使用 is_xxx 的方式命名（错误例子：agree 表示是否同意）

数据类型

• 小数类型为 decimal （使用double、float 等浮点型时计算时会引起一些误差）
• 货币数据使用最小货币单位，数据类型为bigint （如：人民币精确到分在业务层使用时再除以100）
• 字符串长度几乎相等使用 char （如：身份证、手机号长度是固定的。如果遇到长度不一致char默认会加空格，再业务层还需要trim去除空格）
• varchar长度不要超过 5000 （建议长度不超过5000 否则会影响查询效率，如业务需要可使用text数据类型建表关联）

表必备三字段

 id  、create_time  、update_time

建表推荐规约

1>表的命名最好是遵循 “业务名称_表的作用”
2>库名与应用名称尽量一致
3>如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释
4>字段允许适当冗余，以提高查询性能，但必须考虑数据一致
5>单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

索引规约

索引是提高查询效率的有效手段，要根据实际业务合理的分析及使用索引

索引的两大特性持久性和有序性

索引的分类

分类形式	详情
存储形式	1.聚簇索引 2.非聚簇索引
数据约束	1.主键索引 2.唯一索引 3.非唯一索引
索引列的数量	1.单列索引 2.组合索引
innoDB可以创建的索引	1.主键索引 2.唯一索引 3.普通索引

补充：覆盖索引不是创建出来的，是指查询的字段恰好是索引内的字段不需要回表操作，在使用explain查看sql执行计划可通过Extra 是否为 Using index 查看如下图：
在这里插入图片描述

索引的数据结构

可分为：二叉查找树、btree、b+tree
mysql 默认选择Innodb存储引擎 ,索引默认就是b+tree

索引命名

• 主键索引名为 pk_字段名
• 唯一索引名为 uk_字段名
• 普通索引名则为 idx_字段名

创建索引规约

有唯一特性的字段必须建成唯一索引
在 varchar 字段上建立索引时，必须指定索引长度
建组合索引的时候，区分度最高的在最左边。

注意：在创建索引时避免犯以下3个错误

• 索引宁滥勿缺 认为一个查询就需要建一个索引
• 吝啬索引创建 认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度
• 抵制唯一索引 认为唯一索引一律需要在应用层通过“先查后插” 方式解决。

SQL与ORM映射规约

在SQL规约中主要是优化sql语句以及Explain的一些使用

SQL规约-索引优化

• 注意字段类型 防止因字段类型不同造成的隐式转换，导致索引失效
• 利用覆盖索引 利用覆盖索引来进行查询操作，避免回表
• 利用有序性 如果有 order by 的场景，请注意利用索引的有序性
• 禁模糊 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

三个表以上的查询禁止join

SQL规约-count

• 拒绝替代 不要使用 count(列名) 或 count(常量)来替代 count(*)
• 计算不重复行数 count(distinct col) 计算该列除 NULL 之外的不重复行数
• 当值全是NULL时 当某一列的值全是 NULL 时，count(col) 的返回结果为 0，但 sum(col)的返回结果为NULL

SQL规约-分页

• 若 count 为 0 分页查询逻辑时，若 count 为 0 应直接返回
• 优化超多分页场景 利用延迟关联或者子查询优化超多分页场景

SQL规约-null值

使用 ISNULL()来判断是否为 NULL 值

NULL< >NULL NULL< >NULL 的返回结果是 NULL，而不是 false
NULL=NULL NULL=NULL 的返回结果是 NULL，而不是 true
NULL< >1 NULL< >1 的返回结果是 NULL，而不是 true

SQL规约-避坑指南

• 不得使用外键与级联，一切外键概念必须在应用层解决
• 禁止使用存储过程，存储过程难以调试和扩展，更没有移植
• 数据订正时，要先 select，避免出现误删除，确认无误才能执行更新语句
• 只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定
• SQL 语句中表的别名前加 as，并且以 t1、t2、t3、…的顺序依次命名
• in 后边的集合元素数量，控制在 1000 个之内

ORM映射规约

• 在表查询中，一律不要使用 * 作为查询的字段列表
• POJO 类的布尔属性不能加 is，而数据库字段必须加“is_”
• 查询返回结果都需要使用ResultMap映射
• 不要使用${}
• 不要使用MyBatis 自带的 queryForList方法
• 不允许直接使用 HashMap 与 Hashtable 接收结果集
• 更新数据表记录时，必须同时更新 update_time
• 不要写一个大而全的数据更新接口