SQL 查询更新插入规范

简要说明：

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

前提背景：

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

OLTP 与 OLAP：

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

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

 

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 的奥义 进行优化。