SQL通用规范 -V0.4.docx

《SQL通用规范 -V0.4.docx》由会员分享，可在线阅读，更多相关《SQL通用规范 -V0.4.docx（12页珍藏版）》请在优知文库上搜索。

1、SQL通用规范一、建表规约1、【强制】数据库表名由多段组成，即类别词、基本词和限定词。1）表名命名须使用有意义的英文词汇,见名知意,禁止使用拼音及拼音英文混用方式,特别是不要用拼音缩写。2）表名必须要有类别词和基本词，限定词可以缺省。3）表名禁止出现数字开头。4）表名禁止两个下划线中间只出现数字。5）表名总长度不能超过30字符。6）表名必须有备注并及时更新。7）表名不使用复数名词。表名应该仅仅表示表里面的实体内容,不应该表示实体数量,对应于DO类名也是单数形式，符合表达习惯。8）建议使用名词不是动词。2、【强制】字段名由多段组成，即类别词、基本词和限定词。，禁止出现数字开头，说明：数据库字段名

2、的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。D字段名命名须使用有意义的英文词汇，见名知意，禁止使用拼音及拼音英文混用方式，特别是不要用拼音缩写。2）字段名必须使用大写字母或数字0）说明：便于维护与访问，使用小写在不加双引号是下会出现无法匹配到表的情况。3）表名禁止出现数字开头。4）表名禁止两个下划线中间只出现数字。5）字段名要保证同名同意，异名异意。6）字段名必须有备注并及时更新。7）字段名总长度不能超过30字符。8）尽量不使用LONG,TEXT,BLOB,CLOB,NCLOB,LONGRAW这一类的数据类型，而是使用其他可以替代的数据类型。说明：大字段会导致行外存储且无法建

3、聚集索引，对查询效率有影响。9）建议使用名词不是动词。3、【强制】数据库应用表必备五个字段：ID,CREATED_BY；CREATED_DATE,LAST_MODIFIED_BYLAST_MODIFIED_DATEo说明：其市D为主键，类型为长整型，使用雪花算法保证全局唯一。CREATED_DATE,LAST_MODIFIED_DATE的类型均为date_time类型，CREATED_BYLAST_MODIFIED_BY的类型均为VarChar类型。所有基表都要有记录创建者、记录创建时间、记录最后修改者、记录最后修改时间等四个字段，用于记载每条记录的创建者、创建时间和最后修改者、最后修改时间。

4、这些字段不能用于其它功能。每条记录作任何修改操作时，都要修改最后修改者、最后修改时间。4、【强制】Sequence命名由SEQ表名列名组成。5、【强制】禁用保留字，如desc、rangematch、delayed等。（达梦数据库保留字详见附录B）说明：使用数据库保留字会导致语句解析异常，达梦的完整的保留字列表可在数据库中查询select*fromv$reserved_words6、【强制】表达“是”与“否”概念的字段，必须使用is_xxx的方式命名，数据类型根据实际使用数据库定义（1表示“是”，0表示“否”）。7、【强制】VarChar是可变长字符串，不预先分配存储空间，长度不要超过5000,

5、如果存储长度大于此值，定义字段类型为text,独立出来一张表，用主键来对应，避免影响其它字段索引效率。说明：大字段会导致行外存储且无法建聚集索引，对查询效率有影响。8、【推荐】字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循：D不是频繁修改的字段。2）不是VarChar超长字段，更不能是text字段。正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。14、【推荐】单表行数超过500万行或者单表容量超过2GB,才推荐进行分库/分表。说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。15、【

6、参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。说明：无符号值可以避免误存负数，且扩大了表示范围。尽可能避免使用VarChar也会对运行效率有提升015、【推荐】时序数据优先考虑存入时序数据库。说明：时序数据存储主要考虑采样点或者设备数、采样周期、数据存储时间以及写入方式和存储性能等因素，如果分钟级采样采样点在百万内，采用大批入库方式关系数据库一般也可以满足要求,对于秒级采样等周期短且规模大的场景一般建议采用专用的时序库进行存储为优；另外也应考虑入库后的数据应用场景问题。16、【强制】二进制文件不允许写入关系数据库，应存入对象存储。二、索引规约1、【强制】

7、索引命名IDX_表名一字段名（如果存在多字段索引，取每字段前三个字符加下划线组合）。说明：IDX即index的简写。1）唯一索引命名：UK_表名一字段名（如果存在多字段唯一索引，取每字段前三个字符加下划线组合）。说明：UK即UniqUekey的简写。2）主键索引命名：PK_表名一字段名（如果存在多字段主键，取每字段前三个字符加下划线组合）。2、【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的，另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

8、3、【强制】建组合索引的时候，区分度最高的在最左边。正例：如果wherea=?andb=?,a列的几乎接近于唯一值，那么只需要单建idxa索引即可。说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：wherea?andb=?那么即使a的区分度更高，也必须把b放在索引的最前列。4、【强制】主键索引在建立的时候一定要明确指定名称,不能使用系统默认建立主键索引。5、【强制】超过三个表禁止join,需要join的字段，数据类型必须绝对一致;多表关联查询时，保证被关联的字段需要有索引。说明：视图当中出现多表关联同样无法保证执行效率，如果业务逻辑确实无法简化，对于基表变动不多的情

9、况可以考虑建为物化视图.说明：即使双表join也要注意表索引、SQL性能。6、【强制】在VarChar字段上建立索引时，必须指定索引长度，不能对全字段建立索引，根据实际文本区分度决定索引长度。说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引，区分度会高达90%以上，可以使用CoUnt(distinctleft(列名，索引长度)count(*)的区分度来确定。7、新增：【强制】如果索引不能提高任何应用中的查询速度，应删除该索引。说明：每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE.UPDATE将为此多付出4、5次

10、的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。8、【推荐】如果有Orderby的场景，请注意利用索引的有序性。说明：Orderby最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现filesort的情况，影响查询性能。正例：wherea=?andb=?orderbyc;索：abc反例：索引中有范围查找，那么索引有序性无法利用，如：WHEREa10ORDERBYb：由于过滤b的记录顺序己经打乱，需要重新排序，无法利用索引ab原有顺序进行排序。9、【推荐】利用覆盖索引来进行查询操作，避免回表。说明：如果一本书需要知道第11章是什么标题，会

11、翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。正例：能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种效果，用explain的结果，extra列会出现：usingindex.10、【推荐】利用延迟关联或者子查询优化超多分页场景。说明：MySQL并不是跳过OffSet行，而是取OffSet+N行，然后返回放弃前OffSet行，返回N行，那当OffSet特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定闯值的页数进行SQL改写。尽量在关联前缩减入口表数据量能够减小运算状，提高查询效率。正例：先快速定位需要获取的id段，然后

12、再关联：SELECTa.*FROMla,(selectidfrom表1where条件LIMIT100000,20)bwherea.id=b.id11、【推荐】SQL性能优化的目标：至少要达到Qnge级别，要求是ref级别，如果可以是consts最好。说明：达梦中索引概念有所区别，不分级别，但原理是一致的，等值最理想，其次是范围。Dconsts单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据.2) ref指的是使用普通的索引(normalindex)。3) range对索引进行范围检索。反例：explain表的结果，type=index,索引物理文件全扫描，速度非常慢，这个

13、index级别比较Qnge还低，与全表扫描是小巫见大巫。12、【推荐】当数据量比较大时，可以考虑建立相应的索引来提高查询速度。索引类型的选择要根据数据分布及应用来决定。13、【推荐】控制一个表的索引数量，尽量使得一个表的索引数量小于五个。14、【推荐】索引列的选择，在检索条件包含多列的情况下，创建联合主键或者联合索引，把最常用于检索条件的列放在最前端，其他的列排在后面。15、【推荐】需要定期重建索引。说明：虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护。16、【参考】创建索引时避免有如下极端误解：D误认为一个查询就需要建一个索引。2)误认为索

14、引会消耗空间、严重拖慢更新和新增速度。3)误认为唯一索引一律需要在应用层通过“先查后插”方式解决。三、SQL规约1、【强制】不要使用COUnt(列名)或CoUnt(常量)来替代count(*),COUnt(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。说明：COUnt(*)会统计值为NULL的行，而COUnt(列名)不会统计此列为NULL值的行。2、【强制】count(distinctcol)计算该列除NULL之外的不重复行数，注意count(distinctcoll,col2)如果其中一列全为NULL,那么即使另一列有不同的值，也返回为Oo3、【强制】当

15、某一列的值全是NULL时，count(col)的返回结果为0,但SUm(COI)的返回结果为NULL,因此使用SumO时需注意NPE问题。正例：可以使用如下方式来避免SUm的NPE问题：SELECTIF(ISNULL(SUM(g)ASUM(g)FROMtable;4、【强制】使用ISNULL()来判断是否为NULL值。注意：NULL与任何值的直接比较都为NULLo说明：NULLNULL的返回结果是NULL,而不是false。NULL=NULL的返回结果是NULL,而不是true。NULLol的返回结果是NULL,而不是true。5、【强制】在代码中写分页查询逻辑时，若CoUnt为。应直接返回，避免执行后面的分页语句。6、【强制】不得使用外键与级联，一切外键概念必须在应用层解决。说明：(概念解释)学生表中的studentid是主键，那么成绩表中的studentid则为外键如果更新学生表中的StUdentid,同时触发成绩表中的StUdemid更新，则为级联更新外键与级联更新适用于单机低并发，不适合分布式、高并发集群，级联更新是强阻塞，存在数据库更新风暴的风险：外键影响数据库的插入速度。7、【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。8、【强制】数据订正时，删除和修改记录时，要先select,避免出现误删除，确认无误才能执行更新语