建表规约

• 表白是与否概念的字段, 必须应用 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 时执行