# 公司sql规范

**Repository Path**: sflowers/sql_specification

## Basic Information

- **Project Name**: 公司sql规范
- **Description**: 公司sql规范
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 3
- **Forks**: 0
- **Created**: 2019-01-21
- **Last Updated**: 2024-06-21

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 公司sql规范

#### 介绍
公司sql规范


#### 使用说明


•设计原则

1.任何语句使用前explain看执行计划是否用到索引
2.不要从明细表查统计结果，定期统计插入到汇总表
3.禁止使用SELECT *，必须指定字段名称，包括insert table后边加字段列表
4.明细统计时，只统计编码，不要关联名称等冗余字段
5.联合查询时，每个表必须加别名，如 SELECT T1.BM FROM GS T1 LEFT JOIN GSJJ T2 ON T1.BM=T2.BM
6.每个查询结果集使用的内存量不要超过256M，可以通过时间范围控制，如 RK BETWEEN A AND B，建议大表按可小时操作
7.页面查询在10秒内要返回结果，服务器超时限制默认为65秒
8.show variables like '%cache%' 查看 query cache 是否足够大和命中率
9.show full processlist 查看 qcache wait, 过大禁止全局qcache
10.语句中避免子查询，子查询无法使用索引(部分子查询可以使用索引，以执行计划为准)
11.语句中避免使用 GROUP BY, 可通过批量程序定期汇总


• 字段设计

1.尽可能使用更小的数据类型，如 TINYINT、smallint,MEDIUMINT、INT、BIGINT(如int(11)的11代表客户端显示宽度，并不是取值范围,tinyint -2^8-2^8-1,smallint -2^15-2^15-1 int -2^31-2^31-1 bigint -2^63-2^63-1
2.尽量少用 TEXT、BLOB 等专有类型   (用链接代替)
3.相同属性对应的数据类型，如字符型，数值型不能混合使用，依赖后期转换
4.相同字段不同表中的类型和长度要一致
5.字段名称不能使用关键字
6.不要指定字段级编码，建议全库统一
7.默认值要规范，例如日期不要使用 0000-00-00
8.不要用自增ID做主键，无法同步，无约束意义
9.不要使用外键和触发器
10.事务相关记录保留时间戳，建议只增不改；在必须对记录进行修改的时候，保留更改时间戳
11.精度要求高的字段使用 decimal(number, numeric) 代替 double, float(real)
12.禁止非英文字段名称


• 索引使用

1.一般情况下，一次查询只会用到一个索引 (特定情况出现merge index的情况,如下可能出现(a=1 or b=2)会合并a和b的索引,或者使用union all)
2.每个表索引越少越好，建议1-3个，最多5个 (oltp 1-5,olap 5以上)
3.每个查询必须用到索引 (小表可能全表更好,视数据量决定)如果没有索引，即使加了 rownum = 1，也会全表扫描
4.建立组合索引时，WHERE 条件中用到等于的字段放前边，用到范围的字段放后边，如 DD=100000 AND SJ BETWEEN A AND B 例子(见以上)
5.删除重复字段的索引，减少dml IO
6.除了主键外，避免建立其他唯一性索引
7.索引中重复的记录数越少，效率越高，效率最高的是主键 如果同一记录超过50%,全表扫描定期analyze table收集统计信息和直方图,如果可以加not null或者unique的最好加上
8.索引字段最好不要存在 NULL，NULL可用 0 替代，建议把默认值设置为 0也可以myisam_stats_method和innodb_stats_method取值nulls_equal,在null远多于非null的情况下,建议表设计 default 0禁止使用 nulls_equal, 设计时可定义默认值，如 default = 0
9.组合索引可以只使用第一个，或者前两个，或者前几个，不能从第二个开始用，也不能跳着使用索引使用从前缀开始，多字段索引到between或者<,>等以后字段不会使用索引,排序最好在索引中实现
10.关联查询用到的索引的编码必须一致，建议都采用UTF-8
11.如果编码不一致，索引无效


• 查询条件

1.SQL 语句的 WHERE 条件避免无效条件和无效括号，如 SELECT BM FROM GS WHERE (1=1) 、 order by
2.SQL语句中不要加用不到的排序
3.控制临时结果集，包括中间结果和中间排序，查询select * from v$sort_usage,show status like %temp%;
4.WHERE 条件中 最好不要用 IN 和 LIKE使用 exists 代替 in, 减少 like
5.WHERE 条件中不要使用 NOW() 等进行判断，影响执行计划
6.禁止使用未经认证的 hint,如 select sql_no_cache * from ?
7.索引要使用的字段不要使用函数或者进行运算，如 field1 + 1 = field2、adddate(field1,…、CAST
8.禁止字段格式转换，如 SELECT x FROM GS WHERE BM=200000，数值两边不要加引号
9.大多数字段使用函数不会使用索引，只有形如left(BM)='200000'等可以使用
10.函数索引只有确定性 function 可以使用函数索引


•存储过程

1.存储过程中操作的记录数超过1000条时不能使用游标 (禁止游标，用临时表代替，禁止使用触发器)
2.在存储过程的关键步骤开始和结束都要记录信息到日志表，用于监控和调试
3.字符变量使用单引号，不要使用双引号，【"2012-09-23 00:00:00"】 可改为 【'2012-09-23 00:00:00'】
4.存储过程要能够重复执行，执行时需要清空历史冲突记录
5.使用自制事务(autonomous transaction)控制写日志，独立日志操作存储过程


•远程表

1.远程表结构要与原始表一致，尤其是索引
2.远程表数据不要大于256M，远程表的 WHERE 无效
3.远程表一般用来全表小数据全量同步
4.远程表操作完毕提交操作


•查询技巧

1.SQL语句不要太长，如果 IN 列表太多必须改为 LEFT JOIN , 且关联字段主键索引
2.避免使用 LIKE，【lrsj like "2012-09-23%"】 可改为 【LRSJ BETWEEN '2012-09-23 00:00:00' AND '2012-09-23 23:59:59'】或者left,right函数
3.WHERE 多个OR条件不走一个索引时可通过 UNION如【bm1=953016 or bm2=953016】改为【SELECT … WHERE BM1=953016 UNION ALL SELECT … WHERE BM2=953016】merge index,explain 的结果是 using union(idx_name,idx_name)


•性能优化

1.文件格式改为XFS可以提升5%，增加1/6磁盘可以提升1/6，优化索引和结构一般可以提升100-1000倍
2.使用type=heap的临时表


•引擎使用

1.innodb引擎,在过程结尾提交，避免过度commit
2.避免跨引擎操作，如表分别为innodb,myisam


•权限控制

1.PHP连接MYSQL的用户只分配SIUD权限
2.所有提交变量经过 mysql_real_escape_string 进行转义，防止注入可通过工具 统一检测