建表规约

  • 表白是与否概念的字段,必须应用is_xxx命名,数据类型是unsigned tinyint(1-是,0-否)

    • 任何字段如果是非正数,必须是unsigned
    • POJO类中的任何布尔型变量,都不要加is前缀
    • 须要在< resultMap >设置从is_xxx到Xxx的映射关系
    • 数据库示意是与否的值,应用tinyint类型
    • 保持is_ xxx的命名形式是为了明确取值含意和取值范畴

  • 表名,字段名必须应用小写字母(或数字),禁止呈现数字结尾,禁止两个下划线两头只呈现数字.数据库字段名的批改代价很大,因为无奈进行预公布,所以字段名称须要慎重考虑

    • MySQL在windows下不辨别大小写,但在Linux下默认是辨别大小写的
    • 因而,数据库名,表名,字段名,都不容许呈现任何大写字母

  • 表名不应用复数名词

    • 表名应该仅仅示意表外面的实体内容,不应该示意实体数量
    • 对于DAO类名也是复数模式,合乎表白习惯

  • 禁止应用MySQL的官网保留字命名:

    • desc
    • range
    • match
    • delayed

  • 索引命名:

    • pk_字段名: 主键primary key索引
    • uk_字段名: 惟一unique key索引名
    • idx_字段名: 一般index索引名

  • 小数类型为decimal, 禁止应用float,double

    • float和double在存储的时候,存在精度损失的问题,很可能在值比拟时,失去不正确的后果
    • 如果存储的数据范畴超过decimal的范畴,倡议将数据拆分成整数和小数离开存储
  • 如果存储的字符串长度简直相等,应用char定长字符串类型

  • varchar是可变长字符串,不事后调配存储空间,长度不要超过5000

    • 如果长度大于此值,定义字符串类型为text, 独立进去一张表,用主键来对应,防止影响其它字段索引效率

  • 表必备的三个字段:

    • id: 主键,类型为bigint,unsigned,单表时自增,步长为1
    • gmt_create: 类型为datetime,当初时示意被动创立
    • gmt_modified 类型为datetime,过去分词示意被动更新
  • 表的命名最好加上[业务名称_表的作用]
  • 库名与利用名称尽量统一
  • 如果批改字段含意或者对字段的示意状态追加时,须要及时更新字段正文

  • 字段容许适当冗余以进步查问性能,但必须思考数据统一.冗余的字段应遵循:

    • 不是频繁批改的字段

    • 不是varchar超长字段,更不能是text字段

      • 商品类目名称应用频率高,字段长度短,名称根本变化无穷,可在相关联的表中冗余存储类目名称,防止关联查问

  • 单表行数超过500万行或者单表容量超过2GB, 才举荐进行分库分表

    • 如果预计三年后的数据量基本达不到这个级别,不要在创立表时就分库分表

  • 适合的字符存储长度,岂但节约数据库表空间,节约索引存储,更重要的是晋升检索速度

    索引规约

  • 业务上具备惟一个性的字段,即便是多个字段的组合,也必须建成惟一索引

    • 索引不会影响insert的速度,这个速度能够疏忽,但进步查找速度是显著的
    • 即便在应用层做了十分欠缺的校验管制,只有没有惟一索引,必然有脏数据产生
  • 超过三个表禁止join, 须要join的字段 ,数据类型必须相对统一. 多表关联查问时,保障被关联的字段须要有索引

  • 在varchar字段上建设索引时,必须指定索引长度,没必要对全字段建设索引,依据理论文本区分度决定索引长度即可

    • 索引长度与区分度是一对矛盾体

      • 个别对字符串类型数据,长度为20的索引,区分度会高达90%以上
      • 能够应用count(distinct left(列名, 索引长度)) / count(*) 的区分度来确定

  • 页面搜寻严禁左含糊或者全含糊,如果须要要应用搜索引擎来解决

    • 索引文件具备B-Tree的最左前缀匹配个性,如果右边的值未确定,无奈应用此索引

  • 如果有order by的场景,要留神利用索引的有序性 .order by最初的字段是组合索引的一部分,并且放在索引组合程序的最初,避免出现file_sort的状况,影响查问性能

    where a=? and b=? order by c;索引: a_b_c

    要是在索引中有范畴查找,那么索引有序性就无奈利用(WHERE a>10 ORDER BY b; 索引:a_b无奈排序)

  • 利用笼罩索引来进行查问操作,防止回表

    • 比方一本书须要晓得第11章是什么题目,只须要目录浏览一下就更好,这个目录就起到笼罩索引的作用
    • 可能建设索引的品种分为主键索引,惟一索引,一般索引三种,而笼罩索引只是一种查问的成果
    • 用explain的后果,extra列会呈现: using index

  • 利用提早关联或者子查问优化超多分页场景:

    • MySQL不是跳过offset行,而是取offset+N行,而后返回放弃前offset行,返回N行

    • 当offset特地大的时候,效率就十分低下,要么管制返回的总页数,要么对超过特定阈值的页数进行SQL改写

      • 先疾速定位须要获取的id字段,而后再关联:
      SELECT a.* FROM table1 a,(select id from table1 where condition LIMIT 100000,20) b where a.id=b.id

  • SQL性能优化的指标: 至多要达到range级别,要求是ref级别,最好是consts级别

    • consts: 单表中最多只有一个匹配行(主键或者惟一索引),在优化阶段即可读取到数据
    • ref: 指的是应用一般的索引(normal index)

    • range: 指对索引进行范畴检索

      • explain表的后果,type=index,索引物理文件全扫描,速度十分慢
      • 这个index级别比range还低,但比全表扫描要好的多

  • 建设组合索引的时候,区分度最高的在最右边

    • 如果 where a=? and b=?;如果a列简直靠近于惟一值,只须要单建idx_a索引即可

    • 存在非等号和等号混合时,在建设索引时,等号条件列前置

      • 比方 where c>? and d=?; 即便c的区分度更高,也必须要将d放在索引的最前列,即索引idx_d_c
  • 要留神避免因为字段类型不同造成隐式转换,导致索引生效

  • 创立索引有以下谬误的观点:

    • 认为一个查问就须要建一个索引
    • 认为索引会耗费空间,重大拖慢更新和新增速度

    • 抵制惟一索引,认为业务的唯一性须要在应用层通过"先查后插"的形式解决

      SQL语句规约

  • 不要应用count(列名) 或count(常量) 来代替count(), count()是SQL92定义的规范统计行数的办法 ,跟数据库无关,跟NULL和非NULL无关

    • count(*) 会统计只为NULL的行
  • count(distinct col) 计算该列出NULL之外的不反复行数,留神 count(distinct col1, col2) 如果其中一列全为NULL, 那么即便另一列有不同的值,也返回0

  • 当某一列的值全是NULL时, count(NULL)的返回后果为0,但sum(col)返回后果为NULL, 因而应用sum要留神NPE问题

    • 应用以下形式来躲避sum的NPE问题:

      SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM TABLE;

  • 应用ISNULL来判断是否为NULL值

    • NULL与任何值的间接比拟都为NULL:

      • NULL<>NULL的返回后果是NULL,而不是false
      • NULL==NULL的返回后果是NULL,而不是true
      • NULL<>1的返回后果是NULL,而不是true
  • 在代码中写分页逻辑时,若count为0应间接返回,防止执行前面的分页语句

  • 不得应用外键与级联,所有外间的概念必须在应用层解决

    • 比方学生和问题的关系:

      • 学生表中的student_id是主键,那么成绩表中的student_id则为外键
      • 如果更新学生表中的student_id,同时触发成绩表中的student_id更新,即为级联更新
    • 外键与级联更新实用于单机低并发,不适宜分布式,高并发集群
    • 级联更新是强阻塞,存在数据库更新风暴的危险
    • 外键影响数据库的插入速度
  • 禁止应用存储过程,存储过程难以调试和扩大,更没有移植性
  • 数据勘误(数据删除,批改记录操作)时,要先select, 避免出现误删除,确认无误能力执行更新语句
  • in操作能防止就防止,若切实防止不了,须要认真评估in前面汇合元素数量,管制在1000个之内
  • 如果有国际化须要,所有的字符存储与示意,都要以UTF-8编码

  • TRUNCATE TABLE比DELETE速度快,且应用的零碎和事务日志资源少,但TRUNCATE无事务且不触发trigger, 有可能造成事变,所以不要应用TRUNCATE语句

    ORM映射规约

  • 在表查问中,一律不要应用 * 作为查问字段列表,须要哪些字段必须明确写明

    • 减少查问分析器的解析老本
    • 增减字段容易与resultMap配置不统一
    • 无用字段减少网络耗费,尤其是text类型字段

  • POJO类的布尔属性不能加is, 而数据库字段必须加is_, 要求在resultMap中进行字段与属性之间的映射

    • 定义POJO类以及数据库字段定义规定,在<resultMap>中减少映射,是必须的
    • 在MyBatis Generator生成的代码中,须要进行对于的批改

  • 不要应用resultClass当返回参数,即便所有类属性名与数据库字段一一对应,也须要定义,每一个表肯定有一个POJO类对应

    • 配置映射关系,使字段与DAO类解耦,方面保护
  • Sql.xml配置参数应用 #{ } 或者 #param#. 不容许应用 ${ }, 这种形式容易呈现SQL注入

  • 不要应用iBATIS自带的queryForList(String statementName, int start, int size)

    • 这个办法的实现形式是在数据库取到statementName对应的SQL语句的所有记录,再通过subList取start,size的子集合

  • 不容许间接应用HashMap与HashTable作为查问后果集的输入

    • resultClass="HashTable",会置入字段名和属性值,然而值的类型不可控
  • 更新数据表记录时,必须同时更新记录对应的gmt_modified字段值为以后工夫

  • 不要写一个大而全的数据更新接口:

    • 不要传入一个POJO类进行更新
    • 执行SQL时,不要更新无改变的字段.一是易出错,二是效率低,三是减少binlog存储

  • @Transactional事务不要滥用:

    • 事务会影响数据库的QPS
    • 应用事务须要思考各方面的回滚计划,包含缓存回滚,搜索引擎回滚,音讯弥补,统计修改
  • < isEqual > 中的compareValue是与属性值比照的常量,个别是数字,示意相等时带上此条件
  • < isNotEmpty > 示意不为空且不为null时执行
  • < isNotNull > 示意不为null时执行