数据库SQL优化技巧

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 数据库SQL优化技巧

sql语句规范

MySQL在Linux系统下数据库名,表名,存储过程名,函数名称,触发器名称等区分大小写,列名不区分大小写,原因是这些操作系统下文件名称区分大小写。

MySQL在Windows系统下全部不区分大小写,公司一般为了统一规范,规定所有sql关键词全部大写,比如SELECT,UPDATE,FROM,ORDERBY等,保证大家写出来的代码都一样,便于后期维护。

如:

SELECTCOUNT(1) FROM`privilege_user` WHERE user_name = 'admin';

注释使用

在语句中多写注释,注释不影响SQL语句的执行效率,增加代码的可读性。

sql语句优化

数据库性能优化涉及到很多方面,在数据库开发时可以通过以下一些基本的sql优化技巧提高数据库的性能:

1.  查询用具体的字段代替“*”,不要返回用不到的任何字段。

 

2.对查询进行优化,应尽量避免全表扫描,首先应考虑在WHERE 及 ORDER BY涉及的列上建立索引。

 

3.应尽量避免在 WHERE 子句中对字段进行 NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

SELECT id FROM t WHEREnum IS NULL

可以在num上设置默认值0,确保表中num列没有NULL值,然后这样查询:

SELECT id FROM t WHEREnum=0

 

4.应尽量避免在 WHERE 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

 

5.应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

SELECT id FROM t WHEREnum=10 OR num=20

可以这样查询:

SELECT id FROM t WHEREnum=10

UNION ALL

SELECT id FROM t WHEREnum=20

 

6.IN 和 NOTIN也要慎用,否则会导致全表扫描,如:

SELECT id FROM t WHEREnum IN(1,2,3)

对于连续的数值,能用 BETWEEN 就不要用 IN 了:

SELECT id FROM t WHEREnum BETWEEN 1 AND 3

 

7.下面的查询也将导致全表扫描:

SELECT id FROM t WHEREname LIKE '%abc%'

若要提高效率,可以考虑全文检索。

 

8.如果在 WHERE 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

SELECT id FROM t WHEREnum=@num

可以改为强制查询使用索引:

SELECT id FROM t WITH(index(索引名))WHERE num=@num

 

9.应尽量避免在 WHERE 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描,尽量将结果先计算好。如:

SELECT id FROM t WHEREnum/2=100

应改为:

SELECT id FROM t WHEREnum=100*2

 

10.应尽量避免在WHERE子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

SELECT id FROM t WHERESUBSTRING(name,1,3)='abc'--name以abc开头的id

SELECT id FROM t WHEREDATEDIFF(day,createdate,'2015-7-30')=0--‘2015-7-30’生成的id

应改为:

SELECT id FROM t WHEREname LIKE 'abc%'

SELECT id FROM t WHEREcreatedate>='2015-8-1' AND createdate<'2015-9-1';

 

11.不要在 WHERE 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

 

12.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

 

13.不要写一些没有意义的查询,如需要生成一个空表结构:

SELECT col1,col2 INTO#t FROM t WHERE 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

CREATE TABLE #t(...)

 

14.很多时候用EXISTS 代替 IN 是一个好的选择:

SELECT num FROM a WHEREnum IN(SELECT num FROM b)

用下面的语句替换:

SELECT num FROM a WHEREEXISTS(SELECT1 FROM b WHERE num=a.num)

 

15.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

 

16.索引并不是越多越好,索引固然可以提高相应的SELECT 的效率,但同时也降低了 INSERT 及 UPDATE 的效率,因为 INSERT 或 UPDATE 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

 

17.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

 

18.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

 

19.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

 

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

 

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

 

22.在新建临时表时,如果一次性插入数据量很大,那么可以使用SELECTINTO 代替 CREATE TABLE,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先CREATETABLE,然后INSERT。

 

23.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先TRUNCATE TABLE,然后 DROP TABLE,这样可以避免系统表的较长时间锁定。

 

24.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

 

25.在所有的存储过程和触发器的开始处设置 SET NOCOUNTON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

 

26. 尽量使用COUNT(1)

COUNT函数只有在统计表中所有行数时使用,而且COUNT(1)比COUNT(*)更有效率。

 

27. 多表联合查询:

a. 连接查询效率高,但是会导致重复数据。如果确定连接查询不会重复,那么用连接查询会比较好。

b. 子查询效率低,但是可以避免重复数据。如果要到子查询,尽量使用索引提高效率。有些情况必须避免重复数据,那就只能用子查询。

但是具体问题还是要具体分析。如果主表很小,子表很大,并且有索引,是可以使用子查询的,如果主表很大,那就尽量避免子查询,而用JOIN, 如:

INSERTINTO t1(a1) SELECTb1 FROM t2 WHERENOT EXISTS(SELECT 1 FROM t1 WHERE t1.id = t2.id);

改写后:

INSERT INTO t1(a1)

SELECT b1 FROM t2

LEFT JOIN (SELECT distinctt1.id FROM t1 ) t1 ON t1.id = t2.id;

c. 显示(explicit)inner join VS 隐式(implicit) inner join性能基本相同:

如:

SELECT * FROM tablea INNER JOIN table bON a.id = b.id;

VS

SELECT a.*, b.*FROM tablea, table bWHERE a.id = b.id;

 

28. WHERE子句中的执行顺序:

MySQL采用自上而下的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的首位.

 

29.根据ON与WHERE的执行顺序先过滤掉最大数量记录的条件。

SELECT col1, col2FROM a

INNER JOINbONb.name= a.name

LEFT JOINcONc.name =b.name

LEFT JOINdONd.id = c.id

WHERE c.status>1and d.status=1;

改写后:

SELECT col1, col2FROMa

INNER JOINbONb.name= a.name

LEFT JOINcONc.name =b.name and c.status>1

LEFT JOINdONd.id = c.idand d.status=1

 

30.MySQL的语句执行顺序图:

image.png

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
15天前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
82 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
2天前
|
SQL 存储 移动开发
HTML5 Web SQL 数据库详解
Web SQL 数据库是 HTML5 中的一种本地存储技术,允许在浏览器中使用 SQL 语言操作本地数据,支持离线访问和事务处理,适用于缓存数据和小型应用。然而,其存储容量有限且仅部分现代浏览器支持,标准已不再积极维护,未来可能被 IndexedDB 和 localStorage 等技术取代。使用时需谨慎考虑兼容性和发展前景。
|
25天前
|
SQL 存储 数据管理
SQL Server数据库
SQL Server数据库
41 11
|
20天前
|
SQL 监控 关系型数据库
MySQL数据库中如何检查一条SQL语句是否被回滚
检查MySQL中的SQL语句是否被回滚需要综合使用日志分析、事务状态监控和事务控制语句。理解和应用这些工具和命令,可以有效地管理和验证数据库事务的执行情况,确保数据的一致性和系统的稳定性。此外,熟悉事务的ACID属性和正确设置事务隔离级别对于预防数据问题和解决事务冲突同样重要。
30 2
|
1月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
59 0
|
2月前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
58 0
|
2月前
|
SQL 数据处理 数据库
|
2月前
|
SQL 存储 调度
|
2月前
|
SQL 安全 数据库
|
2月前
|
Java 数据库连接 数据库
告别繁琐 SQL!Hibernate 入门指南带你轻松玩转 ORM,解锁高效数据库操作新姿势
【8月更文挑战第31天】Hibernate 是一款流行的 Java 持久层框架,简化了对象关系映射(ORM)过程,使开发者能以面向对象的方式进行数据持久化操作而无需直接编写 SQL 语句。本文提供 Hibernate 入门指南,介绍核心概念及示例代码,涵盖依赖引入、配置文件设置、实体类定义、工具类构建及基本 CRUD 操作。通过学习,你将掌握使用 Hibernate 简化数据持久化的技巧,为实际项目应用打下基础。
66 0
下一篇
无影云桌面