MySql数据库规范与SQL优化 (小结)

建表规约

表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段修改的代价很大,因为无法进行预发布,所以字段名需要慎重考虑。

  • 表名 :不能使用复数名词,最好是加上“业务名称_表的作用
  • 字段:禁用保留字
  • 表名、字段不可太长
  • 表创建固定字段: create_by,create_time,update_time,last_update_time;
  • 尽量不使用外键:外键用来保护参照完整性,可在业务端实现对父表和子表的操作会相互影响,降低可用性
  • 主键索引名为pk_字段名;唯一索引名为uk_字段名;普通索引名则为idx_字段名
  • 禁止使用select * :

原因:

  • ​ 读取不需要的字段会增加CPU,IO,NET消耗
  • ​ 不能有效利用所以覆盖(即一定会回表)
  • ​ 使用SELECT *容易在增加或者删除字段后出现程序BUG
  • 不要使用count(列名)或count(常量)来代替count(*),count( * )是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关

说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行

  • 使用ISNULL()来判断是否为NULL值
  • NULL与任何值的直接比较都为NULL。
  • NULL<>NULL的返回结果是NULL,而不是false。
  • NULL=NULL的返回结果是NULL,而不是true。
  • NULL<>1的返回结果是NULL,而不是true

三大范式

1NF:确保每一列的原子性,即不可再分

2NF:非主键字段必须依赖于主键字段

3NF:不允许出现可传递的依赖关系(实际中允许违反,因为维护一个字段的成本远小于表关联)

sql执行顺序

  1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  2. ON: 对虚表VT1进行ON筛选,只有那些符合的行才会被记录在虚表VT2中。
  3. JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, rug from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。
  4. WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
  5. GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.(从此开始使用select中的别名,后面的语句中都可以使用)
  6. CUBE|ROLLUP|SUM|AVG: 对表VT5进行sum或者avg操作,产生表VT6.
  7. HAVING: 对虚拟表VT6应用having过滤,只有符合的记录才会被 插入到虚拟表VT7中。
  8. SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中。
  9. DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9.
  10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作,产生虚拟表VT10. LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。

image-20210409195432463

Sql优化

慢查询优化步骤:

  1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
  2. 了解业务方使用场景
  3. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
  4. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  5. order by limit 形式的sql语句让排序的表优先查
  6. 加索引时参照建索引的几大原则
  7. 观察结果,不符合预期继续从3分析

explain ——执行计划

image-20210409200213009

  1. **id:**id是一组数字,表示查询中执行select子句或操作表的顺序,如果id相同,则执行顺序从上至下,如果是子查询,id的序号会递增,id越大则优先级越高,越先会被执行。
  2. select_type:查询类型,有simple、primary、subquery、dependent subquery、derived、union、dependent union、union result等
  3. table:显示的查询表名,如果查询使用了别名,那么这里显示的是别名。如果不涉及对数据表的操作,那么这显示为null。如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。
  4. type依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL。除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引。
  5. possible_keys:查询可能使用到的索引都会在这里列出来。
  6. key:查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。
  7. key_len:用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。
  8. ref:如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func。
  9. rows:这里是执行计划中估算的扫描行数,不是精确值。
  10. extra:这个列可以显示的信息非常多,有几十种,常用的有:distinct、using filesort、using index、using temporary

on与where查询顺序

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。 所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行

count 与 limit

COUNT(*) 计算满足条件的记录行数,COUNT(col)计算满足条件的且col非空的记录行数 LIMTI n 在满足条件的记录中查询n条,发现n条后停止扫描 需要判断记录是否存在时可以用LIMIT取代COUNT,LIMIT 1 找到记录就会停止

禁止使 ORDER BY RAND()

ORDER BY 子句中会被执行多次,自然效率很低

索引列不要为NULL

如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串

单独的order by 用不了索引,需要加上where或limit

UNION、UNION ALL

union 对两个结果集进行并集操作,重复数据只显示一次 Union All,对两个结果集进行并集操作,重复数据全部显示,当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高

end

评论