关于数据库操作的规范对于很多人都不陌生，其实大家强调的内容也差不多，我也做了个总结，这些tips不止在工作和学习中会用到，在面试中也是要必须知道的。对MySQL等数据库的使用做一个统一的约定和规范；以便使大家更明确、更有效地用好数据库，减少沟通成本、维护成本。#数据库##MySQL##面试程序员##程序员那些事#一、基础规范1、【强制】禁止使用存储过程、视图、触发器、Event。2、【强制】使用统一的字符集(utf8mb4)。3、【强制】表、字段必须加入中文注释，注释要言简意赅。4、【强制】使用InnoDB存储引擎。5、【强制】禁止存储大文件或者图片。6、【强制】所有存储相同数据的列名和列类型必须一致。二、数据库设计规范1.命名规范【强制】库名、表名、字段名：必须使用小写字母，下划线风格，名称要简洁明了，长度不超过32个字符。禁止数字开头，禁止两个下划线中间只出现数字，禁止驼峰命名，禁止出现大写和中文，禁止使用中划线。解读：tcompanyorganizationscopeemployee 可以采用缩写改成tcomorgscopeemp【强制】索引命名格式为：索引类型字段名缩写。普通索引名idxxxx，唯一索引名unxxx。【强制】命名中不允许出现MYSQL数据库中的保留字。请参考MySQL官方保留字:https://dev.mysql.com/doc/refman/5.7/en/keywords.html【建议】对同一业务模块或关联功能的表应当使用相同前缀来区分。正例：如comxxx，empxxx,userxxx；前缀通常为表对应模块名字；2.表设计规范【强制】表中必须明确指定主键，无特殊情况则要使用自增的UNSIGNED BIGINT型主键。但是由于公司如果做异地多活，所以主键只能有UUID(不建议)或者雪花算法(建议)生成的数值！！解读：主键的递增可以使数据行在物理文件中按顺存放，可以避免page分裂，减少表碎片的产生提升空间和内存的使用，继而提高写入、查询的性能。尽管服务是异地多活的，主键建议使用雪花算法，雪花算法其实也算是有序的，UUID是完全无序的。【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制。【推荐】单表中列的数量必须小于30，单库中表的数量要控制在500个以内。解读：通常来说列数越多，物理文件越大，表的效率越差。库中表越多，物理文件就越多，空间消耗就越大。3.字段设计规范【强制】表的各个字段必须要设置NOT NULL约束，特别是作为过滤条件的列！解读：1)所有NULL值在索引中对于一个KEY；2)NULL字段的查询语句无优化空间，对NULL的处理只能采用IS NULL或IS NOT NULL，而且无法使用索引；【强制】为字段设置默认值。解读：如果需要设置默认值约束请参考以下默认值:TINYINT/SMALLINT/INT/BIGINT 整数类型默认值：0; 如果是分数这类场景可以使用-1CHAR/VARCHAR 字符类型默认值：(空字符串); 如果无法设置空字符串，可以使用其他字符串代替，例如python的ORM中UUID不可为空，那可以使用32个零的特殊UUID值代替DATE 类型默认值：1900-01-01，也可以是一个其他的业务上无效的日期TIME 类型默认值：00:00:00DATETIME 类型默认值：1900-01-0100:00:00也可以是一个其他的业务上无效的日期时间【强制】使用UNSIGNED存储非负整数。【强制】小数类型用DECIMAL或者对数值扩大后使用int/bigint 类型来存储，禁止使用FLOAT和DOUBLE。【强制】枚举类型禁止使用ENUM，可使用TINYINT代替。【强制】如果存储的字符串长度几乎相等，请使用定长字符串CHAR类型。【强制】禁止在数据库中存储明文密码，把密码加密后存储。【强制】禁止使用TEXT、BLOB类型。【强制】网络IP一律用INT UNSIGNED来记录(IP可通过INETATON函数转换为数值)。【强制】优先选择符合存储需要的最小的数据类型。【建议】VARCHAR是可变长字符串，一定要根据实际情况按需设置长度;长度最好不要超过250个字符。解读：当VARCHAR存放的字符过多时，在物理文件中存放时会产生行溢出现象。【建议】使用VARCHAR存储电话号码。解读：a)涉及到区号或者国家代号，可能出现+-()；b)电话号码不会做数学运算；c)VARCHAR可以支持模糊查询，例如：LIKE 138%；【建议】字段允许适当冗余，以提高性能，但是必须考虑冗余数据的同步情况。4、索引设计规范说明:区分度是指列中存放的数据值中唯一值个数占中总值个数的比例。【强制】业务上具有唯一特性的字段(即使是组合字段的唯一)，必须建立唯一索引。【强制】在VARCHAR字段上建立索引时，必须指定索引长度，禁止对全字段建立索引。解读：可以使用COUNT(DISTINCT LEFT(列名,索引长度))/COUNT(主键)的区分度来确定。COUNT(DISTINCT LEFT(tname,10))， COUNT(DISTINCT LEFT(tname,50))，COUNT(DISTINCT LEFT(tname,100)) from t1;Create index idxname on t1(tname(10));【强制】在排序、分组、取唯一的字段上创建索引，表关联使用的字段、WHERE子句中的字段要创建索引。【建议】索引数量应不超过列总数的40%,索引太多说明表设计很可能不合理。【建议】建立组合索引时必须把区分度高的、使用频率高的字段放在左边，索引中字段数不允许超过3个。【建议】禁止在更新十分频繁、区分度不高的属性上建立索引。三、SQL使用规范1.SQL书写规则【强制】SQL语句的大小写风格要统一。【强制】SQL语句中表的别名应简短明了，反映表名的实际意义。【强制】禁止使用MySQL特有的非标准SQL语法，所有SQL都必须使用标准写法。解读:MySQL支持的非标准INSERT语法:1)INSERT INTO employees SET employeename=John,date=2018-06-15,mployeeage=30;2)INSERT INTO employees(employeename,date,mployeeage)VALUES (John,2018-06-15,30）ON DUPLICATE KEY UPDATE ;标准写法:INSERT INTO employees(employeename,date,mployeeage)VALUES (John,2018-06-15,30);2、可读性规则【强制】不允许使用SELECT *，必须指定列名；需要什么就索取什么。【强制】INSERT必须明确指定插入的字段名。【强制】应避免写复杂的SQL语句。【强制】在表连接时要对表设置别名，别名要简洁明了，控制在5个英文字符内，不易过长。【强制】不等于统一使用”!=”。SQL认为”<>”和”!=”是等价的，都代表不等于的意义。为了统一，不等于一律使用”!=”表示。【建议】不用now(),uuid()等函数来填充SQL，在代码层面来做。3、性能相关规则【强制】避免在数据库中进[gf]2f8f[/gf]数学运算(MySQL不擅长数学运算和逻辑判断)【强制】每个SQL返回结果的行数不能太多，用多少取多少，要控制在500行以内。统计分析除外。【强制】对大量数据插入时不要使用逐条的INSERT语句进行插入，要使用合并插入的方式(每次批量插入的行数要控制,避免大事务)。解读：批量插入INSERT INTO emp (empno,ename,deptno)Values (1,a,10)，(2,b,20)，(3,c,30),(4,d,10),(5,e,10),(6,f,10);【强制】应按照业务需要使用事务，同时应保持事务简短，避免大事务，确保整个事务涉及的数据库对象不要超过5个，执行时间不要超过3秒。【强制】禁止在WHERE条件的列上使用函数或者表达式，要将其改写到等号右边。解读：在过滤条件的列上使用函数，会导致列上的索引无法被使用；错误：SELECT uid FROM tuser WHERE FROMUNIXTIME(day)>=2017-02-15;==>会导致全表扫描正确：SELECT uid FROM tuser WHERE day>= UNIXTIMESTAMP(2017-02-1500:00:00)【强制】禁止负向查询，以及%或开头的模糊查询。【强制】减少子查询的使用。解读：子查询除了可读性差之外，通常会在一定程度上影响了SQL运行效率.应尽量减少子查询的使用，采用关联或其他效率更高、可读性更好的方式实现。【强制】使用OR条件，如果是同一列的不同值的OR语句可以改为IN查询。错误：SELECT c1,c2 FROM tab WHERE c3=1 OR c3= 2;正确：SELECT c1,c2 FROM tab WHERE c3 IN (1,2);【强制】使用OR条件，如果是不同列的OR语句可以考虑用UNION替换OR。低效：SELECT locid , locdesc , regionFROM locationWHERE locid =10 OR region =MELBOURNE;高效:SELECT locid , locdesc , regionFROM locationWHERE locid =10UNIONSELECT locid , locdesc , regionFROM locationWHERE region =MELBOURNE;【强制】SQL语句应避免对大表的全表扫描操作，对大表的操作要使用索引。【强制】IN里包含的值的个数建议控制在100以内，过多IN的效率不高。【强制】在使用union时优先考虑使用union all，少使用union。解读：union all不去重，少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all。【强制】SQL语句应避免不必要的分组和排序。【强制】注意过滤条件中字段类型和传入值类型不匹配问题，会导致的隐式转换。【建议】禁止对大表进行关联查询，禁止大表使用子查询【建议】SQL语句尽可能避免多表联合的复杂查询。【建议】用>=替代>，用<=代替<，明确下限和上限。4、其他建议【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。【强制】应用程序必须捕获SQL异常，并有相应处理.【强制】应用程序要合理配置重连、链接过期时间，数据库对空闲链接默认超时时间是8小时(超过这个时间的会被杀掉)，但对于被频繁连接的前端业务库，通常线上往往设置在30分，而新建连接不频繁后端通常会相应调长一点，大概1-3小时；【强制】不同业务模块必须使用不同的账号来连接数据库，便于问题排查和权限管理，如果没有可以向DBA索取。【强制】数据库脚本不得通过邮件内容发送，必须在代码库中提交。【强制】不同程序/应用使用不同的数据库账号访问，禁止跨库查询。觉得还不错，点个赞支持一下吧