孤尽31天之Mysql规约设计

一、规约

2.1建表规约

       为了在所有成员之间建立一种共识的约定，以便大家一起沟通在共同的基础上。

     2.1.1表、字段命名

1.名字规范：

• 必须使用小写字母或者数字

所有的字段使用字母时应都使用小写的，不要用大写。

       禁止出现数字开头

• 禁止2个下划线中间只出现数字
• 不使用复数名词
• 禁止保留字
• 是与否的概念的字段必须使用is_xxx的方式命名

2.数据类型选择

• 小数类型为：decimal
• 货币数据：使用最小货币单位比如人民币最小单位为分则数据库存储的单       位为分，例如如果存储1块钱的应该存：100分，数据类型为bigint
• 字符串长度几乎相等：使用char
• 长度不超过5000时：使用varchar

如果是超过5000使用text类型存储并新建一张表关联

3.表的必备字段：

       id

       create_time

       update_time

4.其他规约

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

2.2索引规约

1.索引目的

为了提高查询效率来建立的数据结构，提高查询效率的有效手段，但是需要合理的分析使用索引。

2.索引的特性

索引的持久性和有序性。

持久性：索引占的存储基本与数据1:1的，所以占用空间较大。

有序性：查询数据时进行方向性指引，减少查询时间。

3.索引的分类

       索引可以分4种分类，存储形式、数据约束、列的数量、innoDB可以创建的索引。

       1.索引的存储形式：

       1）聚簇索引

2）主键类型

       3)非聚簇索引

       2.根据数据约束

       1)主键索引

2)唯一索引

3)非唯一索引（普通索引）

       3.索引列的数量

              1)单列索引

              2)组合索引

       4.innoDB可以创建的索引

              1)主键索引

              2)唯一索引

              3)普通索引

              覆盖索引是利用innoDB索引的特性，是一种效果不用回表。

4.索引的数据结构

       索引的数据结构可能使用

       1.二叉查找树，时间复杂度为logN的复杂度。

       2.平衡二叉查找树：左右2个子树的层级最多相差1，如果超过1则进行一次平衡，使得树的效率始终不会太差。mysql的所有数据是保存在磁盘上的，每查询一个节点都会访问一次1磁盘IO，IO次数是降低数据库的有效手段，所以二叉树需要访问的磁盘次数还是比较多。

       3.btree树：在二叉树基础上进行优化得到N叉树，并且每个节点保存多个元素，左右都保存了比本节点的大和小的数据索引类似如下

，

 

mysql的每次读写磁盘最小单元为16k，所以每个数据块大小就是16k，因为Btree的层级比较少，这样查询速度快，它的特点是：数据和索引都保存在节点上，树的层级小（矮胖）查询速度快。btree对应等值查询很快，但是对于范围查询并不是很理想

       4.B+tree

       在Btree基础上升级，节点不在保存数据，只保存键值，这样在节点上可以存储更多的键值地址，所以的数据都保存在叶子节点，并且每个叶子保存了前后叶子数据块的指针，这样叶子构成一个双向链表，这样相邻的磁盘块可以快速读取范围数据，所以B+tree的数据结构既适合等值查询又适合范围查询，当前的关系型数据库使用B+tree结构保存。B+tree的在插入数据时是如何构建树的。美国网站上有所有数据结构的构建过程。

       索引命名

       主键索引：命名：pk_字段名

       唯一索引：uk_字段名

       创建索引

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

创建索引时内容尽可能短，这样同一个节点里可以存储更多的索引，b+tree的高度也会更少，查询速度会更大。

索引的错误认知：

一个查询就需要建一个索引；

吝啬创建索引

抵制唯一索引：认为需要先查询后创建是错误的。

2.3sql与orm映射规约

sql规约

1. 注意字段类型

防止因字段类型不同造成的隐方式转换导致索引失效

使用explain select * from a

查看结果里的type如果是all则代表索引失效

1. 利用覆盖索引

利用覆盖索引查询，避免回表，在innoDB主索引和数据是聚簇存储在一起的

回表：需要先查找主键，通过主键再去查找数据会进行2次查询。利用覆盖索引时可以减少1次查表操作，

如果在结果中的extra中显示using index则表示使用覆盖索引，这种情况下不会回表

1. 利用有序性

如果有order by 的场景，可以利用索引的有序性进行查询。

4.禁止模糊

        页面搜索时严禁使用左模糊和全模糊，右边模糊可以使用索引。

超过3张表时禁止join，如果超过3个要怎样写sql

5.不要使用count（列名）或count（常量）来替代count（*），官方推荐说明使用        count（*）。

6.计算不重复行数

        count（distinct 列）计算该列除null之外的不重复的行数

7.null参与运算的结果都是null

8.若count为0

分页查询逻辑时，若count为0直接返回

9.优化超多分页场景：利用延迟关联或者子查询优化超多分页场景。

        比如：知道当前页的id，翻到下一页的id一定大于当前页的最大id可以增加条件：where id>当前最大id limit 1；

10.在关联查询时，

        可以使用：select * from （select * from a where id >1000 limit 10） as t1 join b t2 where t1.列=t2.列

在join查询时应该在join之前先将表进行一次过滤再使用过滤结果进行join。

join时应该使用小表驱动大表就是左边是小表右边连接大表，这样效率高点。

sql规约-指南

1. 禁用外键
2. 禁止使用存储过程
3. 数据订正是要先select 避免出现误删除，确认无误后更新，update之前先查询，
4. 只要涉及多个表，表要增加别名
5. 别名前使用as 以t1，t2....
6. in后面的集合元素数量控制在1000内

查询的级别起码到range，

使用explain 的技巧mysql的性能优化使用技巧

orm映射规约

1. 优化sql语句的要点

对象关系映射，就是数据库与对象的字段的jpa和hibernate是一个完整的orm映射。

使用mybatis时

1.在查询表时不要使用*作为查询字段列表

 2.pojo类的布尔属性不能加is，而数据库需要加is

 3.查询返回结果都需要使用resultMap映射

 4.不要使用${},容易引起sql注入

 5.不要使用mybatis自带的queryForList方法

因为它的分页是取出所有数据然后截取子数组，这样慢，应该直接指定limit

1. 不允许直接使用hashMpa和Hashtable接收结果集
2. 更新数据记录时必须同步更新update_time

数据库设计实战

第一范式：每列属性不可拆分

       比如家庭住址其实可以拆分：包括xx市xx区xx街道xx号

第二范式：表中的每列都和主键相关

       比如用户表里主键身份证号码，与姓名，地址

第三范式：每列都和主键列直接相关，而不是间接相关

必须强相关例如：用户信息表中，人的信息包括住址中的小区里的x楼，而楼的相关属性如：楼的颜色，建造时间等则应该单独。

T31业务需求

1. 乘客通过网站注册登录

用户表

1. 实名认证通过mock实现
2. 铁路部门管理员功能

-车次、车厢、经停站、时刻表的crud

-用户管理：

角色管理

菜单管理

1. 定时生成火车票

火车票信息表

1. 普通用户功能

修改信息：密码，住址，电话，有效

代购功能：录入其他乘客信息，合并支付

余票查询：可以通过车次、出发站、到达站等条件查询

购买车票下单：选择车次，座位类型、乘客信息

订单支付：第三方支付

通常需要拆分名词为实体，动词为关系

T31数据库设计E-R草图

 

每个实体是一张表，每个N-N的关系也是一张表，

当前对e-r图进行优化如下

 

其中灰色的是不需要的对象表被优化去掉的。将他们的信息冗余到了相关其他表。其中乘车出行-生成车票表，并将车票与车次进行关联，但是车票信息中进行了简化没有精确到座位号，只记录到了车次。

定时任务每天生成：当天的车票与车次信息，生成的信息就是商品信息可以售卖。

这样生成的数据库的实际表如下

 

车票表中的数据是每天定时生成的，按天记录。

e-r图建议使用powerdesigner，