T31项目笔记-mysql

1. 强制建表规约

1.1 表、字段名

1. 必须使用小写字母或数字
2. 禁止出现数字开头
3. 禁止两个下划线中间只出现数字
4. 不使用复数名词
5. 禁用保留字（ goto、const）
6. 是与否概念的字段，必须使用 is_xxx 的方式命名

1.2 数据类型

1. 小数类型为 decimal
2. 货币数据使用最小货币单位，数据类型为bigint
3. 字符串长度几乎相等使用 char
4. varchar长度不要超过 5000

1.3 表必备字段

Id 、creater_time 、update_time

1.4 建表注意

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

2. 索引规约

目的

	   1. 提高查询效率的有效手段 
       2. 合理的分析及使用索引

索引的特点

持久性、有序性

分类

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

索引的数据结构：

1. 二叉查找树

2.btree

3.b+tree

索引名称规约

1. 主键索引名为 pk_字段名
2. 唯一索引名为 uk_字段名
3. 普通索引名则为 idx_字段名

创建索引规约

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

创建索引避免如下极端误解

1.索引宁缺毋滥： 认为一个查询就需要建 一个索引
2. 吝啬索引创建： 认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度
3. 抵制惟一索引： 认为惟一索引一律需要在应用层通过“先查后插”方式解决
4. 超过三个表禁止 join

count

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

分页

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

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

注意！

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

Explain

Explain+覆盖索引

Explain+index

Explain+filesort

ORM映射规约

1. 优化SQL语句的要点
2. Explain的使用指南
3. ORM映射的避坑指南
   

数据库设计三大范式

项目要求

1. 乘客通过网站注册登录。
2. 实名认证通过mock实现
3. 铁路部门管理员有如下功能
   • 车次、车厢、经停站、时刻表的增删改查
   • 用户管理：用户增删改查，黑名单管理，为用户分配角色
   • 角色管理：角色增删改查，为角色分配相应的功能 - 菜单管理：菜单增删改查
4. 定时任务：生成火车票
5. 普通用户有如下功能
   • 修改信息：密码，住址，电话，邮箱等
   • 代购功能：录入其他乘客信息，合并下单支付
   • 余票查询：可以通过车次、出发站、到达站、日期等条件查询
   • 购买车票下单：选择车次，选择乘车人，座位类型后下单
   • 订单支付：第三方支付，支付宝支付、微信支付
6. 支付成功后系统通知用户

T31数据库设计E-R图

T31数据库设计E-R图-优化

T31数据库设计物理图