查询优化器
- 使用具体的字段名来取代*,因为会将*转化为表的所有字段
- 使用具体的表的字段名取代字段名。table.field取代field,因为在查询中,特别是多表联合查询时,如果不指定表名,则会查询所有表的字段
- 使用group条件替代distinct条件,查询优化器会将distinct条件转化为group条件
- 使用limit条件会增加查询时间
- ,join inner join cross join在mysql中是等价的,都会转化为多表的联合查询,对于内连接查询来看,on和where是等价的
- left join会将左边的表进行全表扫描,右边的表没有匹配的话会有null代替。当 数据表不能为NULL时,则左边不会列出所有的记录,并将外链接转化为多表联合查询
- outer join的where和on条件有一些区别,on是在生产查询结果之前,进行过滤。where是在查询结果输出过程中的过滤,因此过滤条件写在where中,left不会显示所有的记录
- 转化子查询为多表联合查询。子查询会创建临时表,并且临时表没有任何索引导致性能过低。多表联合查询不会建立临时表,并且可以有效地利用查询索引
sql军规
基础规范
- 必须使用innoDB
支持事务,行级锁,并发性能更好,cpu以及内存资源利用率高
?? innoDB和MyIsAm引擎的区别
- 必须使用utf8mb4字符集
万国码,无需转码,无乱码风险,节省空间
- 数据表,字段必须加入中文注释
- 禁止使用存储过程,视图,触发器,event
?? 什么是存储过程,视图,触发器和事件
存储过程不适合快速迭代,修改表结构之后可能无法使用
同时存过有很多逻辑判断,应该讲逻辑上移到应用程序
视图也是一样
高并发的网络架构思路是 解放数据库CPU,将计算转移到服务层
对于触发器来说,十分耗费资源,例如一个insert语句,每次都会触发触发器 因为在并发量大的情况下,这些功能可能会把数据库拖慢,而把这些逻辑放在应用层,可以轻易实现加机器就能加性能
- 禁止存储大文件或大照片
数据库并不适合存储大文件和大图片,何不存入uri
命名规范
- 只允许使用内网域名,而非IP连接
- 线上,测试,开发环境数据库命名规范 业务名 xxx
线上环境 dj.xxx.db
测试环境 dj.xxx.tdb
开发环境 dj.xxx.rdb
从库-s标识 备库-ss- 库名,表名,字段名:小写,下划线
- 表名t_xxx,索引名idx_xxx,唯一索引uniq_xxx
表设计规范
- 单实例表数目必须小于500
- 单表列数目必须小于30
- 表必须有主键
- ?? 主键的作用, 索引,内存管理 *
- 禁止使用外键,若有外键完整性约束,需要应用程序控制
外键会造成表之间的耦合,update和delete操作会涉及相关联的表
??外键完整性约束
字段设计规范
- 必须把字段定义为not null并提供默认值
- null列会使索引/索引统计/值比较变得更加复杂
- null类型数据库会单独处理,会拖慢数据库的处理性能
- null值需要更多地存储空间,无论是表还是索引都需要在每行中null的列增加标识
- null处理只能采用is null或is not null,对于=,in,<等操作都不会涉及null
- 禁止使用text,blob类型
浪费更多的磁盘和内存空间,非必要的打字单查询会淘汰掉热数据,导致内存名字下降,影响性能
- 禁止使用小数存储货币
小数精度问题
- 必须使用varchar(20)存储手机号
- 会有+-()
- 手机号不会做数学运算
- 可以做模糊匹配
- 禁止使用enum,可使用tinyint替代
- 新增enum值要做DDL操作
- enum的内部实际存储得是整数
索引设计规范
- 单表索引建议控制在5个以内
- 单索引字段数不超过5个
- 禁止在更新十分频繁,区分度不高的属性上简历索引
- 建立组合索引,必须把区分度高的字段放在前面
sql使用
- 禁止使用select * 是获取需要的字段,显示说名列属性
- 禁止使用insert into t_xxx values(),必须显示指定插入的列属性
容易在增加或者删除字段后出现bug
- 禁止使用属性的隐式转换
- 禁止在where条件上使用函数或者表达式
- 禁止负向查询,以及%开头的模糊查询
- 禁止大表join查询,禁止大表使用子查询
子查询会产生临时表,消耗较多的CPU,极大影响性能
- 禁止使用OR条件,必须改为in查询
- 应用程序必须捕捉sql异常并做相应处理