了解MySQL 开发规范
-
尽量避免在数据库做运算,复杂运算应移至业务应用层
数据库的主要职责是存储和管理数据,而不是执行复杂的计算。尤其是涉及到大量数据时,数据库的负荷会变得沉重,影响系统性能。将复杂运算移到业务应用层,有助于分担数据库的压力。
-
优化影响性能的 SQMyL 语句
通过监控慢查询日志,找到对系统性能影响较大的 SQL 语句进行优化。可以选择优化频繁执行的语句,或者优化后带来最明显性能提升的语句。这有助于提高数据库的响应速度。
-
充分利用表上已存在的索引
合理使用索引是提高查询效率的关键。避免使用双百分号的查询条件,确保查询条件可以使用到列上的索引。对于复合索引,注意查询条件中使用范围查询时,将范围查询列放在索引的右侧。如:
a like '%123%'
,(如果无前置%,只有后置%,是可以用到列上的索引的)如:有 a,b,c 列的联合索引,在查询条件中有 a 列的范围查询,则在 b,c 列上的索引将不会被用到。 在定义联合索引时,如果 a 列要用到范围查找的话,就要把 a 列放到联合索引的右侧,使用 left join 或 not exists 来优化 not in 操作,因为 not in 也通常会使用索引失效。
-
禁止使用 SELECT *,必须明确指定字段列表
避免不必要的资源浪费,减少网络带宽消耗。同时,SELECT * 无法使用 MySQL 优化器覆盖索引,SELECT <字段列表> 可减少表结构变更带来的影响,因此明确指定字段列表能够提高性能。
-
禁止使用不含字段列表的 INSERT 语句
在 INSERT 语句中,明确指定字段列表有助于防止因表结构变更导致的错误。这种规范写法可以提高代码的健壮性。
如:
insert into t values ('a','b','c');
应使用:
insert into t(c1,c2,c3) values ('a','b','c');
-
使用预编译语句进行数据库操作
预编译语句具有重复使用的优势,可以减少 SQL 编译时间和提高处理效率。它只传递参数,相较于直接传递 SQL 语句,能够更高效地执行。
-
避免数据类型的隐式转换
隐式转换会导致索引失效,降低查询性能。确保查询条件中的数据类型与数据库字段类型一致,避免类型转换。
如:
select name,phone from customer where id = '111';
-
避免使用子查询,优先考虑优化为 JOIN 操作
子查询的性能通常较差,特别是在条件包含聚合、排序等复杂操作时。优先考虑将子查询优化为 JOIN 操作,以提高效率。
子查询性能差的原因: 子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大。由于子查询会产生大量的临时表也没有索引,所以会消耗过多的 CPU 和 IO 资源,产生大量的慢查询。
-
避免 JOIN 太多的表
在 MySQL 中,对于同一个 SQL 多关联(join)一个表,就会多分配一个关联缓存,如果在一个 SQL 中关联的表越多,所占用的内存也就越大,需要谨慎使用。推荐不超过 5 个表的 JOIN 操作,避免影响服务器性能稳定性。
-
减少同数据库的交互次数
数据库更适合处理批量操作,因此合并多个相同操作可以降低与数据库的交互次数,提高效率。
-
使用 IN 代替 OR 进行列判断
在同一列进行 OR 判断时,使用 IN 语句可以更有效地利用索引,提高查询性能。
-
禁止使用 order by rand() 进行随机排序
使用 order by rand() 会消耗大量 CPU 和 IO 资源,推荐在程序中生成随机值,然后通过 ORDER BY 操作来获取随机排序的数据。
-
WHERE 从句中禁止对列进行函数转换和计算
避免对列进行函数转换和计算,以确保索引的有效使用。推荐使用范围查询替代函数转换,提高查询效率。
不推荐:
where date(create_time)='20240101'
推荐:
sql where create_time >= '20240101' and create_time < '20240102'
-
使用 UNION ALL 而不是 UNION
在明确不会有重复值的情况下,使用 UNION ALL 而不是 UNION。UNION ALL 不进行结果集去重操作,更高效。
-
拆分复杂的大 SQL 为多个小 SQL
将大 SQL 拆分为多个小 SQL,有助于通过并行执行提高处理效率。这对于复杂逻辑的 SQL 查询尤其重要。
-
程序连接不同数据库使用不同账号,禁止跨库查询
使用不同的账号连接不同的数据库,有利于数据库迁移和分库分表的灵活性,同时降低了业务的耦合度,避免了跨库查询的安全风险。