nber1994



mysql-查询优化器&sql军规

August 9, 2019

查询优化器

  1. 使用具体的字段名来取代*,因为会将*转化为表的所有字段
  2. 使用具体的表的字段名取代字段名。table.field取代field,因为在查询中,特别是多表联合查询时,如果不指定表名,则会查询所有表的字段
  3. 使用group条件替代distinct条件,查询优化器会将distinct条件转化为group条件
  4. 使用limit条件会增加查询时间
  5. ,join inner join cross join在mysql中是等价的,都会转化为多表的联合查询,对于内连接查询来看,on和where是等价的
  6. left join会将左边的表进行全表扫描,右边的表没有匹配的话会有null代替。当 数据表不能为NULL时,则左边不会列出所有的记录,并将外链接转化为多表联合查询
  7. outer join的where和on条件有一些区别,on是在生产查询结果之前,进行过滤。where是在查询结果输出过程中的过滤,因此过滤条件写在where中,left不会显示所有的记录
  8. 转化子查询为多表联合查询。子查询会创建临时表,并且临时表没有任何索引导致性能过低。多表联合查询不会建立临时表,并且可以有效地利用查询索引

sql军规

基础规范

  1. 必须使用innoDB

支持事务,行级锁,并发性能更好,cpu以及内存资源利用率高
?? innoDB和MyIsAm引擎的区别

  1. 必须使用utf8mb4字符集

万国码,无需转码,无乱码风险,节省空间

  1. 数据表,字段必须加入中文注释
  1. 禁止使用存储过程,视图,触发器,event

?? 什么是存储过程,视图,触发器和事件
存储过程不适合快速迭代,修改表结构之后可能无法使用
同时存过有很多逻辑判断,应该讲逻辑上移到应用程序
视图也是一样 高并发的网络架构思路是 解放数据库CPU,将计算转移到服务层

对于触发器来说,十分耗费资源,例如一个insert语句,每次都会触发触发器 因为在并发量大的情况下,这些功能可能会把数据库拖慢,而把这些逻辑放在应用层,可以轻易实现加机器就能加性能

  1. 禁止存储大文件或大照片

数据库并不适合存储大文件和大图片,何不存入uri

命名规范

  1. 只允许使用内网域名,而非IP连接
  2. 线上,测试,开发环境数据库命名规范 业务名 xxx
    线上环境 dj.xxx.db
    测试环境 dj.xxx.tdb
    开发环境 dj.xxx.rdb
    从库-s标识 备库-ss
  3. 库名,表名,字段名:小写,下划线
  4. 表名t_xxx,索引名idx_xxx,唯一索引uniq_xxx

表设计规范

  1. 单实例表数目必须小于500
  2. 单表列数目必须小于30
  3. 表必须有主键
  1. 禁止使用外键,若有外键完整性约束,需要应用程序控制

外键会造成表之间的耦合,update和delete操作会涉及相关联的表

??外键完整性约束

字段设计规范

  1. 必须把字段定义为not null并提供默认值
  1. null列会使索引/索引统计/值比较变得更加复杂
  2. null类型数据库会单独处理,会拖慢数据库的处理性能
  3. null值需要更多地存储空间,无论是表还是索引都需要在每行中null的列增加标识
  4. null处理只能采用is null或is not null,对于=,in,<等操作都不会涉及null
  1. 禁止使用text,blob类型

浪费更多的磁盘和内存空间,非必要的打字单查询会淘汰掉热数据,导致内存名字下降,影响性能

  1. 禁止使用小数存储货币

小数精度问题

  1. 必须使用varchar(20)存储手机号
  1. 会有+-()
  2. 手机号不会做数学运算
  3. 可以做模糊匹配
  1. 禁止使用enum,可使用tinyint替代
  1. 新增enum值要做DDL操作
  2. enum的内部实际存储得是整数

索引设计规范

  1. 单表索引建议控制在5个以内
  1. 单索引字段数不超过5个
  1. 禁止在更新十分频繁,区分度不高的属性上简历索引

  1. 建立组合索引,必须把区分度高的字段放在前面

sql使用

  1. 禁止使用select * 是获取需要的字段,显示说名列属性

  1. 禁止使用insert into t_xxx values(),必须显示指定插入的列属性

容易在增加或者删除字段后出现bug

  1. 禁止使用属性的隐式转换

  1. 禁止在where条件上使用函数或者表达式

  1. 禁止负向查询,以及%开头的模糊查询

  1. 禁止大表join查询,禁止大表使用子查询

子查询会产生临时表,消耗较多的CPU,极大影响性能

  1. 禁止使用OR条件,必须改为in查询

  1. 应用程序必须捕捉sql异常并做相应处理