几个必须掌握的SQL优化技巧(六):针对SQL语句的优化

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 在应用的开发过程中,由于开发初期的数据量一般都比较小,所以开发过程中一般都比较注重功能上的实现,但是当完成了一个应用或者系统之后,随着生产数据量的急剧增长,那么之前的很多sql语句的写法就会显现出一定的性能问题,对生产的影响也会越来越大,这些不恰当的sql语句就会成为整个系统性能的瓶颈,为了追求系统的极致性能,必须要对它们进行优化。

1 前言


在应用的开发过程中,由于开发初期的数据量一般都比较小,所以开发过程中一般都比较注重功能上的实现,但是当完成了一个应用或者系统之后,随着生产数据量的急剧增长,那么之前的很多sql语句的写法就会显现出一定的性能问题,对生产的影响也会越来越大,这些不恰当的sql语句就会成为整个系统性能的瓶颈,为了追求系统的极致性能,必须要对它们进行优化。


2 正文


前面几篇文章主要介绍了如何通过一些命令来分析sql语句的执行频率、执行计划以及时间消耗等等情况,也就是分析和定位一些执行效率比较低的SQL语句,然后后面针对这些效率比较低的SQL语句再进行针对性的SQL优化。


对于SQL语句和索引的优化可以分为以下几个方面:


SQL语句的优化:


1、批量导入数据的优化:


因为InnoDB引擎的底层的索引是B+树索引,而B+树索引中的数据是有顺序的,所以对于InnobDB类型的表有以下几种方式可以提高导入的效率:


1)、主键顺序插入


因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率,如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键,就可以利用这点,提高插入数据的效率,对于无序数据的也可以插入,但是效率会比较低


大批量的导入数据可以使用以下的语法:


load data local infile +文件路径和文件名 into table + 表名 fields terminated by +字段以什么分割 lines terminated by +每行数据以什么区分
复制代码


比如:


load data local infile '/root/sql.log' into table users fields terminated by ',' lines terminated by '\n'
复制代码


2、关闭唯一性校验


在创建mysql表的时候,有这样的唯一性索引


unique key 'unique_user_username' ('username')
复制代码


mysql在插入数据的时候对数据的唯一性索引进行校验,符合条件的数据存在的话不插入,不存在的话插入数据,唯一性校验也会影响导入数据的效率,所以可以在批量导入数据前执行


SET UNIQUE_CHECKS=0
复制代码


关闭唯一性校验,在导入结束后再执行


SET UNIQUE_CHECKS=1
复制代码


可以恢复唯一性校验,这样可以提高导入的效率。


e47daac033b941cdaa77ee357d3c3263~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


3、关闭自动提交改为手动提交事务


如果应用使用自动提交的方式,建议在导入前执行


SET AUTOCOMMIT=0
复制代码


关闭自动提交,导入结束后再执行


SET AUTOCOMMIT=1  
复制代码


重新开启自动提交,这样可以提高数据导入的效率。


2、insert语句的优化


前面说到了如何对于文件的数据进行批量导入的时候进行优化,那么在使用insert语句进行插入数据操作的时候如何对其优化呢?


1)、如果需要同时对一张表插入很多行数据的时候,应该尽量使用多个值表的insert语句,这种方式会大大减少客户端和数据库之前的连接、关闭等消耗,使得效率比分开执行单个insert语句快。


比如:


insert into user values (1,'张三');
insert into user values (2,'张四');
insert into user values (3,'张五');
复制代码


这样的插入语句效率比较低,可以进行优化为:


insert into user values (1,'张三'),(2,'张四'),(3,'张五');
复制代码


2、改为手动提交事务进行数据的插入,并且如果数据量比较大时,最好还是改成分段的插入方式,比如:


start transaction;
insert into user values (1,'张三');
insert into user values (2,'张四');
insert into user values (3,'张五');
commit;
复制代码


3、插入有序数据


insert into user values (2,'张三');
insert into user values (1,'张四');
insert into user values (3,'张五');
复制代码


这样的插入数据的方式比


insert into user values (1,'张三');
insert into user values (2,'张四');
insert into user values (3,'张五');
复制代码


这样的插入数据的方式效率低很多。


3、order by 语句的优化


ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。在mysql中有两种排序方式:


1)、通过对返回数据进行排序,也就是通常说的filesort排序(通过文件系统进行排序),所有不是通过索引直接返回排序结果的都是filesort排序。


2)、第二种排序方式是有索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,这种排序方式操作效率高。


首先创建一个emp表,然后创建一个复合索引,如下:


72190ca56de547ef87799c41258c2c83~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


对这张表创建一个复合索引:


微信截图_20220520104819.png


5de037595fc049caaa2973a2f269182d~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


比如对上面的这张表按照age和 age,salary进行排序,并使用expalin进行分析:


expalin select * from emp order by age
复制代码


fb45e02a467543ddbbefd2b033b2f70a~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


expalin select * from emp order by age,salary
复制代码


bc8a12ea30194aba9ee714464a8062c2~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


根据前面几个必须掌握的SQL优化技巧(三):Explain分析执行计划说到的explain命令的介绍,extra的结果是文件排序,另外可以看到type为all,所以查询没有走索引,而是通过全表扫描。


如果执行这样的查询:


expalin select id from emp order by age
复制代码


b86b01cd9106497b870cac329afa96ee~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


这时候便走到了索引,并且排序方式是索引顺序。


e57b32f122ba464cb9a61db89756f0e2~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


只要查询返回的字段是覆盖索引,那么排序方式就是索引排序。


因为name字段没有索引,所以如果查询返回的字段有name(并没有索引),那么排序就是using filesort。


761f3e6a6f5046da9ef2a89a7ad83b43~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


在using index排序方式中如果涉及到多字段排序,并且如果一个是降序,一个是升序,那么会出现using filesort


75645232d3bc49c48081b0241e300fcd~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


所以如果是多字段排序,最好统一排序方式,要么多字段都是降序,要么都是升序排序。


a251458cfbb943fbaec019976b93bf82~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


并且排序字段的顺序要和索引的顺序保持一致,比如上面的索引顺序是age、salary,如果排序字段的顺序是slary、age就算都是统一升序,那么也会出现using filesort,影响效率。


0bab8239629548db92c5357058ce91a2~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


所以尽量减少额外的排序,通过索引直接返回有序数据,where条件和order by使用相同的索引,并且order by的顺序和索引顺序相同,并且order by的字段都是升序或者降序,否则肯定会有额外的操作,这样就会出现using filesort。


但是在日常开发中通过创建合适的索引也只能尽量减少using filesort的出现,但避免不了它的出现,那么就需要对using filesort进行优化加快排序操作。在mysql中有两种排序算法:


1)、两次扫描法:在mysql4.1之前,使用该排序方式,首先根据条件取出排序字段和行指针信息,然后再排序中sort buffer中排序,如果sort buffer不够,则再临时表中存储排序结果,完成排序之后,再根据行指针回表读取记录,该操作可能会导致大量随机I/O操作。


2)、一次扫描法:一次性取出满足条件的所有字段,然后再排序区中排序后直接取出结果集,排序时内存开销大,但是排序效率比两次扫描法高。


在mysql通过比较系统变量max_length_for_sort_data的大小和query语句取出的字段总大小,来判定是否使用哪种排序,如果max_length_for_sort_data更大,那么使用第二种优化之后的算法;否则则使用第一种算法。


所以可以适当提高sort_buffer_size和max_length_for_sort_data系统变量,来增大排序区的大小,来提高排序的效率。


可以通过下面的语句来查看sort_buffer_size和max_length_for_sort_data的值:


show variables like 'sort_buffer_size'
show variables like 'max_length_for_sort_data'
复制代码


4、group by语句的优化


group by实际上也同样会进行排序操作,而且与order by相比,group by主要只是多了排序之后的分组操作,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算,所以在group by的过程中,与order by一样也可以利用索引。


如果查询中包含了group by,但是用户想要避免排序结果的消耗,则可以执行order by null 禁止排序。比如:


-- 先删除复合索引
drop index idx_emp_age_salary on emp;
expalin select age,count(*) from group by age;
复制代码


优化后:


-- 只想对age进行分组,不对其进行排序
expalin select age,count(*) from emp group by age order by null;
复制代码


e5dd2668f077433a8078979e758a565e~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


通过上面可以看到第一个sql语句使用到了using filesort,而使用了 order by null禁止排序后,没有了using filesort,但是还有using temporary,使用了临时表, 这个也是比较影响性能的, 在group by的过程中,与order by一样也可以利用索引:


d97e3123e52a4b30a289e2cedbbd862a~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


创建索引后,则没有了临时表,只有using index了。,则这样效率也提高了。


5、子查询的优化


mysql4.1版本之后,开始支持sql的子查询。子查询就是可以使用select 语句创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的sql操作,同时也可以避免完成事务和表锁死,并且写起来比较容易,在某些情况下,子查询是了哟被更高效的连接(join)替代。


比如:


expalin select * from t_user where id = (select user_id from user_role)
复制代码


17abfc4f7a534db5ac0339ce04fb7d65~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


通过explain命令分析SQL语句的执行计划我们可以知道子查询在mysql5.5版本中,内部的执行计划是:先查询子查询的user_role表,再查询一个子查询临时表,最后查询t_user表,而不是先查t_user表,所以当user_role的数据很大时,那么查询速度会非常慢。


针对这样的语句可以采用join关联方式对其进行优化:


select u.* from t_user u join user_role i on u.id=i.userid 
复制代码


6、用in来代替or


比如:


select * from user where id=1 or id=2 or id=3
复制代码


这样的SQL语句就是比较低效的,所以可以这样进行优化:


select * from user where id in (1,2,3)
复制代码


另外,MySQL对in也做了相应的优化,即将in中的常量全部存储再一个数据里面,并且在这个数组中是已经排好序的,所以查询效率会提高,但是如果数值比较多,那么产生的消耗也是比较大的,而且对于上面的SQL语句,in(1,2,3)这样连续的数值,那么最好可以使用between来代替in。


7、分页查询优化


mysql时一般limit进行分页查询,比如这样的查询:


select * from tb_item limit 2000000,10
复制代码


起始查询的数据是第2000000条数据,获取其后面的十条数据。此时需要mysql对前面的2000010条记录进行排序,但是仅仅返回2000000开始后面的十条数据(2000000-2000010),而将其他的记录舍弃,这样的查询排序的代价非常大。


那么可以对其进行优化,而使用一般分页查询时,通过创建覆盖索引能够比较好的提高性能:


1)、在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。比如:


select * from tb_item limit t,(select id from tb_item order by id 2000000,10) a where t.id=a.id 
复制代码


2)、对于主键自增的表,可以把limit查询转换成某个位置的查询,比如:


select * from tb_item where id>2000000 limit 10
复制代码


这种方式使用具有局限性,只能适用于主键自增的表,而且主键需要有连续性,不能出现断层,比如删除了其中的某个数据,那么就不适用了。


8、使用索引提示


SQL使用索引提示也是优化数据库的一个重要手段,简单来说,就是SQL语句中加入一些人为的提示来达到优化操作的目的,索引提示有以下三种:


1:use index:在查询语句表名的后面,添加use index来提供你希望mysql去参考的索引列表,就可以让mysql不再考虑其他可用的索引。如:


select * from table use index(name);
复制代码


2:IGNORE INDEX : 提示会禁止查询优化器使用指定的索引。在具有多个索引的查询时,可以用来指定不需要优化器使用的那个索引,还可以在删除不必要的索引之前在查询中禁止使用该索引。如:


select * from table ignore index(name);
复制代码


3:force index:强制mysql使用一个特定的索引。一般情况下mysql会根据统计信息选择正确的索引,但是当查询优化器选择了错误的索引或根本没有使用索引的时候,这个提示将非常有用。


select * from table force index(name);
复制代码


需要注意的是use/ignore/force index(index)这里括号里的index是索引名,而不是列名。而且后面必须要加上where条件。


3 总结


对于SQL语句的优化是日常开发中避免不了的内容,优化后的SQL语句能够提高系统和应用的性能,改善用户体验。这篇文章主要介绍了MySQL语句的几种优化方式,并结合expalin命令分析其性能,这里的几种优化方式只是针对MySQL数据库,但是其他的数据库某些是殊途同归,某些还是存在差异。sql的优化方式肯定也不仅仅这几种,我总结的也是MySQL普遍的几种方式,对于某些特殊情况得特殊对待,最重要的还是结合工作中的经验然后在书写SQL语句的时候要养成良好的习惯。


根据前面的介绍在SQL优化中最主要的方式是关于索引的使用,后面的文章将会介绍MYSQL索引的使用和优化。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
20天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
34 0
|
27天前
|
SQL 存储 数据库
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
|
20天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
34 0
|
1月前
|
SQL 关系型数据库 MySQL
Mysql系列-3.Mysql的SQL优化和锁(中)
Mysql系列-3.Mysql的SQL优化和锁
36 0
|
8天前
|
SQL 数据库 索引
sql深度优化
sql深度优化
14 1
|
10天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
46 0
|
22天前
|
SQL 存储 关系型数据库
【MySQL】七种SQL优化方式 你知道几条
【MySQL】七种SQL优化方式 你知道几条
28 0
|
26天前
|
SQL 存储 关系型数据库
MySQL(终结篇二)- SQL 语句分析与优化
MySQL(终结篇二)- SQL 语句分析与优化
75 0
|
27天前
|
SQL 存储 缓存
如何通过优化SQL查询提升数据库性能
SQL查询是数据库的核心功能之一,对于大型数据量的应用程序来说,优化SQL查询可以显著提升数据库的性能。本文将介绍如何通过优化SQL查询语句来提升数据库的性能,包括索引优化、查询语句优化以及其他一些技巧。
|
1月前
|
SQL 存储 关系型数据库
MySQL SQL优化 【建议熟读并背诵】
MySQL SQL优化 【建议熟读并背诵】
38 0