SQL 查询更新插入规范

Bertha 。 2022-12-11 03:28 174阅读 0赞

## 简要说明： ##

1.  `本篇主讲 查询（select）和更新（update）的规范，略微捎带 插入（insert）的规范。可能有人会问为什么不规范delete。首先 delete 本就不应该出现在生成代码上，delete 权限的开放意味着，如果开发人员如果编写了delete ，一但筛选不够准确，会导致数据丢失的严重事故。`

## 前提背景： ##

1.  `由于业务前期的不规范性，导致了开发/测试的表结构版本于生产不一致（此不一致不包含新开发内容），后续在不影响开发测试流程尽可能会将表结构于生产调整至一致。`

## OLTP 与 OLAP： ##

在互联网时代，海量数据的存储与访问成为系统设计与使用的瓶颈问题，对于海量数据处理，按照使用场景，  
主要分为两种类型：联机事务处理（OLTP）和联机分析处理（OLAP）。  
联机事务处理（OLTP）也称为面向交易的处理系统，其基本特征是原始数据可以立即传送到计算中心进行处  
理，并在很短的时间内给出处理结果。  
联机分析处理（OLAP）是指通过多维的方式对数据进行分析、查询和报表，可以同数据挖掘工具、统计分析  
工具配合使用，增强决策分析功能。  
对于两者的主要区别可以用下表来说明：

<table> 
 <thead> 
  <tr> 
   <th>&nbsp;</th> 
   <th>OLAP</th> 
   <th>OLTP</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>系统功能</td> 
   <td>日常交易处理</td> 
   <td>统计、分析、报表</td> 
  </tr> 
  <tr> 
   <td>DB 设计</td> 
   <td>面向实时交易类应用</td> 
   <td>面向统计分析类应用</td> 
  </tr> 
  <tr> 
   <td>数据处理</td> 
   <td>当前的, 最新的细节的,二维的分立的</td> 
   <td>历史的, 聚集的, 多维的集成的, 统一的</td> 
  </tr> 
  <tr> 
   <td>实时性</td> 
   <td>实时读写要求高</td> 
   <td>实时读写要求低</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>强一致性</td> 
   <td>弱事务</td> 
  </tr> 
  <tr> 
   <td>分析要求</td> 
   <td>低、简单</td> 
   <td>高、复杂</td> 
  </tr> 
 </tbody> 
</table>

## OLTP 业务 ##

本节不适用分析性业务。

### Select： ###

奥义：通过尽可能扫描少量的行，去获取数据。

1、尽可能使用最简单的查询语句 即 select columns from table_name where index_columns [=|in] ''  [limit 1];
    注意事项：
        1、where 筛选：
            意义：WHERE 筛选是为了能快速定位数据，主要通过索引（INDEX）。也是为了筛选出尽可能少的数据。
        1-1、where 的筛选条件 必须有索引中最左的列
            例如：
            create table student(
             sid int not null,
             name varchar(30) not null,
             classid int,
             sex tinyint not null,
             PRIMARY KEY (`sid`),
             key idx_classid_sex(classid,sex)
            );
        根据现有表结构只能满足：
        select * from student where sid =  xxx;
        select * from student where classid =  xxx;
        select * from student where classid =  xxx and sex = x;
        select * from student where  sex = x;（错误！）
        如果当业务需要通过名字定位数据时，应在表上添加名称索引，idx_name（name）
        若此后业务需要模糊的名称定位数据，只支持右模糊的查询方式 形如 like '张三%'; 所有左右全模糊，左模糊的单条件或者含有其余条件但不能精准定位的查询，不都应访问关系型数据库获取数据。（Elasticsearch 不属于数据库！Elasticsearch 不属于数据库！Elasticsearch 不属于数据库！）
        针对业务中只单独筛选 类如 sex，status ,isactive ,isdeleted 这种类型性字段 的 查询业务需求 不应查询 （除了 isactive = 0 , isdeleted = 1 该字段类型下的值，仅仅含有少量数据的情况下）。
        1-2、尽可能使用主键或者是unique key 的列作为筛选条件，如果非这两类索引查询，表结构将不重复的列定义为普通key ,则加上limit 1 。
        1-3、函数名不许出现等式左边
            例如 to_days(createtime) >|<|>=|<= |= 15315 。
        1-4、禁止隐式转换，即入参类型应与 字段类型相同
            例如 A 表中 有字段 value varchar(50) 却有index idx_va(value)。错误查询方式 select * from A where `value` = 001。 此类错误普遍发生于字段内容为全数字。
        1-5、 not in ,<>,!=,is not null 不走索引。
        1-6、 避免or 逻辑
        1-7、in 的list 长度
            in 的使用最好传入10个左右的参数。过多的参数应考虑join，exits, 或者 in 子查询的方式（p.s.不推荐exist 和in（子查询） exists 和 in(子查询) 适用于 select 返回结果集不需要 子表字段  ）
        1-8、避免 select * 的方式。
            虽然普遍select *  不会影响数据库查询速度，但是返回数据包比较大，也会影响数据传输效率
    后续规范，建议在 OLTP 不使用，可通过添加冗余字段，略微修改产品逻辑等方式避免。
    2、join
        1、最多三表join
            建议 1张大表，1-2小表（通常为使用in的写法要传入 很长长度的list，且返回数据集中需要小表的字段。）
        2、避免分区表与分区表的join
            由于表构建了分区，说明了表具有一定的体量，分区表与分区表的join 通常属于大表join 大表，经常会出选where 条件筛选的不够好，导致的大量io扫描
        3、on 的 链接字段
            至少为两张表的最左索引字段，最好是一个是主键字段（unique）与一个是索引字段。
            如果两边都是主键字段（unique key），说明表结构设计不合理，可通过修改表结构的方式 改造sql。
    3、group by & order by
        尽可能不用group by ,group by 的分组维度字段不建议超过3个。不允许用order by 。

### Update: ###

奥义：避免发生 死锁(互锁)，锁等待的情况

1、点跟新，使用主键进行跟新
        建议配合select   for update;update 的事务的方式进行更新，查询更新的主键，保证避免幻读。
    2、批量（小范围）更新。
        被更新表为冷表下使用。
    3、批量大范围更新
        只允许在夜间，才有可执行的可能。

### insert： ###

奥义：避免一次性插入过多数据。

1、一次性需要插入大量数据的情况，请分批插入。一般一次插入值的上限为4000；最多5000；

**OLAP:**

由于业务复杂程度高，难以定义较好的规范，但一定可以根据 select 的奥义 进行优化。