Mysql使用规范

最新推荐文章于 2024-07-01 11:49:52 发布

东东先生。

最新推荐文章于 2024-07-01 11:49:52 发布

阅读量93

点赞数

分类专栏：开发须知

本文链接：https://blog.csdn.net/weixin_38521984/article/details/118105570

版权

开发须知专栏收录该内容

11 篇文章 0 订阅

订阅专栏

开发规范

建议使用预编译语句进行数据库操作
1. 预编译语句可以重复使用，相同的SQL语句可以一次解析，多次使用，减少SQL编译所需要的时间，提高处理效率；此外，还可以有效解决动态SQL带来的SQL注入问题。
避免数据类型的隐式转换
1. 隐式转换如：SELECT 1 + "1"；数值型 + 字符型的隐式转换有可能会导致索引失效，以及一些意想不到的结果等。
充分利用表中存在的索引
1. 避免使用双%号的查询条件
  1. 如 WHERE first_name like '%James%'，若无前置%，只有后置%，则执行SQL语句时会用到列上的索引，双%号则不会使用列上的索引。
2. 一条SQL语句只能使用复合索引中的一列进行范围查询
  1. 例如有weight、age、sex三列的联合索引，在查询条件中有weight列的范围查询，则在age和sex列上的索引将不会被使用；因此，在定义联合索引时，若某列需要用到范围查询，则将该列放到联合索引的右侧。
3. 使用not exists 代替not in
  1. 因为not in 在SQL语句中执行时会导致索引失效。
杜绝使用SELECT * ，必须使用SELECT <字段列表> 查询
1. 因为使用SELECT * 查询会消耗更多的CPU、IO和网络宽带资源，并且查询时无法使用覆盖索引。
禁止使用不含字段列表的INSERT 语句
1. 如：INSERT into table_name values ('1','2','3'); 改为带字段列表的INSERT 语句：INSERT into table_name('c1','c2','c3') values ('1','2','3');
避免使用子查询，可以把子查询优化为join 关联操作
1. 但是，通常子查询在in 子句中，且子查询中为简单SQL(即不包含union、group by、order by、limit从句)时，才可以把子查询转化为join关联查询进行优化；
2. 子查询性能差的原因：
  1. 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
  2. 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。
避免使用JOIN 关联太多表
1. 在Mysql中，对于同一个SQL关联（join）多个表，每个join 就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大；
2. 如果程序中大量的使用了多表关联的操作，同时join_buffer_size(MySQL允许关联缓存的个数)设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响服务器数据库性能的稳定性；
3. 此外，对于关联操作来说，会产生临时表影响查询效率，而Mysql最多允许关联61个表，建议不超过5个；
对同一列对象进行or 判断时，使用in 替代or
1. in 的值只要涉及不超过500个，则in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。
禁止使用order by rand() 进行随机排序
1禁止在WHERE 从句中对列进行函数转换和计算
1. 因为在WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。
  1. 不推荐：where date(end_time)='20190101'
  2. 推荐： where end_time >= '20190101' and end_time < '20190102'
在明显不会有重复值时使用UNION ALL 而不是UNION
1. UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；
2. UNION ALL 不会再对结果集进行去重操作；
把复杂、较长的SQL 拆分为为多个小SQL 执行
1. 大SQL在逻辑上比较复杂，是需要占用大量CPU 进行计算一条SQL语句；
2. 在MySQL中，一条SQL 语句只能使用一个CPU 进行计算；
3. SQL拆分后可以通过并行执行来提高处理效率。
不要使用视图实现业务
不要使用触发器去实现业务
如果无法避免使用复杂SQL，请在对应SQL代码中写明SQL逻辑。

行为规范

超过100万行数据的批量操作(update delete insert)，分多次进行
1. 大批量操作可能回造成严重的主从延迟；
2. binlog日志为row格式时会产生大量的日志；
3. 避免产生大事物操作。
对于大表使用pt-online-schema-change 修改表结构
1. 避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表；
2. pt-online-schema-change 它首先会建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器；然后，把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉，其是把原来一个DDL操作，分解成多个小的批次执行。
禁止给程序使用的账号授予super 权限
1. 当达到最大连接数限制时，还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用。
对于程序连接数据库账号，遵循权限最小原则
1. 程序使用数据库账号只能在一个数据库下使用，且程序使用的账号原则上不授予drop 权限。
修改生产环境数据库数据或表结构，请走DMS审批流程。
生产环境相应查询操作权限申请，请走DMS审批流程。