侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 352 篇文章
  • 累计创建 135 个标签
  • 累计收到 10 条评论

目 录CONTENT

文章目录

SQL中IN语句性能优化方式及常用的SQL优化技巧

孔子说JAVA
2022-11-02 / 0 评论 / 0 点赞 / 61 阅读 / 8,434 字 / 正在检测是否收录...
广告 广告

SQL 中的 IN 操作符允许我们在 WHERE 子句中规定多个值,用于查询匹配字段的多个值。in的用法是 where xx in (‘aa’,‘bb’,‘cc’),相当于 where xx=‘aa’ or xx=‘bb’ or xx=‘cc’。对于索引字段 or 或者 in 的效率基本一致,非索引字段 in 的效率优于or。or的效率为O(n),in的效率为O(logn),当n越大的时候效率相差越明显。在一些场景下如子表数据量大的时候,in的效率会很低,故我们需要对in进行相关优化。

13

1、IN 的优化方式

1.1 union all方式

如果in后面的子查询条件不多时,可以考虑主表建索引,或用union all 代替。示例如下:

原始SQL(执行时间80s+)

SELECT count(id) as id FROM task WHERE cid=100015	AND  sid IN(112310,112316,106959,110780,112324,112331,112317)  	AND  flag='9'

优化后SQL(平均执行时间100ms)

select sum(id) as id  from (

  SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112310  AND  flag=9
  union all
  SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112316  AND  flag=9
  union all
  SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=106959  AND  flag=9
  union all
  SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=110780  AND  flag=9
  union all
  SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112324  AND  flag=9
  union all
  SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112331  AND  flag=9
  union all
  SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112317  AND  flag=9

) t

说明:

  • mysql版本5.6
  • 表总的数据量60万+
  • cid和flag为int类型,参数去掉引号,避免隐式转换
  • (cid sid flag)是组合索引,使用uinon all拆分后,遍历的表多了,使用组合查询更高效

1.2 exists方式

in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。

  • 其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了,另外IN时不对NULL进行处理。

子查询 select flag from B where B_id<100 结果集99条

-- in方式
select * from A where flag in (select flag from B where B_id<100 );

198 rows in set (0.00 sec)

-- exists方式
select * from A where exists (select * from B where B_id<100 and A.flag=B.flag);

198 rows in set (0.10 sec)

可以看到IN效率高于EXISTS。

子查询 select flag from B where B_id>100 结果集 299899条

-- in方式
select * from A where flag in (select flag from B where B_id>100 );

19798 rows in set (0.09 sec)

-- exists方式
select * from A where exists (select * from B where B_id>100 and A.flag=B.flag);

19798 rows in set (0.06 sec)

可以看到EXISTS效率这次比IN高。

1.3 between方式

对于连续的数值,能用 between 就不要用 in 了,如下语句:

select * from table1 where id in (1,2,3,4,...,8000) and NAME = 'best' 

如果in的数值是连续的,用between替换in:

select * from table1 where id between 1 and 8000 and NAME = 'best' 

1.4 关联查询

对于子查询对象是数据表,且条件可以关联查询的语句,可以使用关联查询。

in 语句如下,耗时:0.58s。

SELECT
	V.ID,
	V.P_ID,
	V.NO,
	V.NAME,
	V.TYPE,
	V.DIS_LEVEL 
FROM V_MES_DEVICE_TREE_FFI V WHERE V.P_ID IN
	( SELECT v.ID FROM V_MES_DEVICE_TREE_FFI v
	 WHERE v.P_ID = '21601_T01**5a640d296c262391e053070ca80a3842' )

优化后关联查询语句如下,耗时:0.05s。

SELECT
	V.ID,
	V.P_ID,
	V.NO,
	V.NAME,
	V.TYPE,
	V.DIS_LEVEL 
FROM
	( SELECT v.ID FROM V_MES_DEVICE_TREE_FFI v
	 WHERE v.P_ID = '21601_T01**5a640d296c262391e053070ca80a3842' ) t
LEFT JOIN V_MES_DEVICE_TREE_FFI V ON t.ID = V.P_ID

1.5 临时表关联查询

对于子查询对象是逗号分隔的值,无法直接关联查询的语句,可以使用临时表关联查询。

如果in后接几百几千或几万的条件(条件不连续),可以把in里的条件录入临时表,给临时表加索引,用表连接代替in查询。例如:

select * from table1 where id in (1,2,3,4,...,8000) and NAME = 'best' 
  1. 将括号的条件做成变量
DECLARE @str VARCHAR(4000) 
  SET @str = '1,2,3,4,5.......'
  1. 将@str拆分后插入临时表
CREATE TABLE #t
    (
        id VARCHAR(10)
    ) 
    DECLARE @i    INT 
    DECLARE @len  INT 
    SET @i = 1 
    WHILE @i < LEN(@str + ',')
    BEGIN
        INSERT #t
        SELECT SUBSTRING(@str + ',', @i, CHARINDEX(',', @str + ',', @i) -@i)
        
        SET @i = CHARINDEX(',', @str + ',', @i) + 1
    END 
  1. 利用临时表和原表进行连接取值
SELECT k.* 
    FROM tb k INNER JOIN #t p
    ON  p.id = k.id
    WHERE  NAME = 'best'   

1.6 FIND_IN_SET查询

MYSQL中有个特殊的函数 FIND_IN_SET(str,strlist),str 为要查询的字符串,strlist 是需查询的字段,参数以”,”分隔,形式如 (1,2,6,8,10,22);该函数的作用是查询字段(strlist)中是否包含(str)的结果,返回结果为null或记录。

  • 要注意:第二个参数 strlist 无论是字符串还是数字,都必须用单引号 ' 括起来,并且列表中用逗号隔开,这个字符串列表是一个整体。
  • MYSQL中 find_in_set() 函数用法详解

mysql官方文档描述如下:

  • 假如字符串str 在由N 子链组成的字符串列表strlist 中, 则返回值的范围在 1 到 N 之间 。一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。如果第一个参数是一个常数字符串,而第二个是type SET列,则 FIND_IN_SET() 函数被优化,使用比特计算。如果str不在strlist 或strlist 为空字符串,则返回值为 0 。如任意一个参数为NULL,则返回值为 NULL。 这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

一般情况下 IN 比 FIND_IN_SET 性能高。如果要查询的字段是主键,使用IN时会使用索引,只会查询表中部分数据。FIND_IN_SET则会查询表中全部数据,由于数据量比较大,性能肯定不高,所以需要替换为IN。

也有特殊情况,如要查询的字段非主键,也没有索引时,使用之后性能优于IN,如下:

  1. 原sql语句
SELECT
	count(1) AS article_count,
	b.col_name AS column_name,
	a.a_columnID AS column_id,
	sum(a.a_countClick) AS total_click,
	sum(a.a_countClick0) AS web_click,
	sum(a.a_countClick1) AS wap_click,
	sum(a.a_countClick2) AS app_click
FROM article a
INNER JOIN column b ON a.a_columnID = b.columnID
WHERE a.a_status = 1
AND a.a_siteID = 1
AND a_sourceType <= 1
AND a.a_orgID > 0
AND a.a_columnID in(columnstr)
GROUP BY
	a.a_columnID,
	b.col_name
ORDER BY
	a.a_columnID ASC

注意条件中的 a.a_columnID in(columnstr),其中 columnstr 的值数量很大,有几千个之多,形式如:

282,284,283,114,115,116,1356,423,386,1355,370,276,285,307,338,702,805,835,836,1519,852,699,955,1401,1342,1352,1403,1410,1474,1476,1480,1499,1512,1522,338,710,711,712,713,935,936,936,937,282,542......

该语句在生产环境中执行时间需要1分钟之多。

  1. 优化语句1

使用 FIND_IN_SET 优化如下:

SELECT
	count(1) AS article_count,
	b.col_name AS column_name,
	a.a_columnID AS column_id,
	sum(a.a_countClick) AS total_click,
	sum(a.a_countClick0) AS web_click,
	sum(a.a_countClick1) AS wap_click,
	sum(a.a_countClick2) AS app_click
FROM article a
INNER JOIN column b ON a.a_columnID = b.columnID
WHERE a.a_status = 1
AND a.a_siteID = 1
AND a_sourceType <= 1
AND a.a_orgID > 0
and FIND_IN_SET(a.a_columnID, '282,284,283,114,115,116,1356,423,386....')
GROUP BY
	a.a_columnID,
	b.col_name
ORDER BY
	a.a_columnID ASC

使用 FIND_IN_SET 优化后,该语句的执行时间为900多毫秒(不到1秒),再次查询时间变为400毫秒左右(有了查询缓存)。

  1. 优化语句2

使用 FIND_IN_SET 同时使用关联查询,优化如下:

SELECT
	count(1) AS article_count,
	b.col_name AS column_name,
	a.a_columnID AS column_id,
	sum(a.a_countClick) AS total_click,
	sum(a.a_countClick0) AS web_click,
	sum(a.a_countClick1) AS wap_click,
	sum(a.a_countClick2) AS app_click
FROM article a
INNER JOIN column b ON a.a_columnID = b.columnID
INNER JOIN (SELECT '282,284,283,...' AS COLUMNS) c ON FIND_IN_SET(a.a_columnID,c.COLUMNS)
WHERE a.a_status = 1
AND a.a_siteID = 1
AND a_sourceType <= 1
AND a.a_orgID > 0
and FIND_IN_SET(a.a_columnID, '282,284,283,114,115,116,1356,423,386....')
GROUP BY
	a.a_columnID,
	b.col_name
ORDER BY
	a.a_columnID ASC

使用上述语句优化后,该语句第一次的执行时间为300多毫秒。

2、Sql语句常见的优化方法

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

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

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

select id from t where num is null 

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

select id from t where num=0
  1. 应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=100 or num=200

可以这样查询:

select id from t where num=100

union all

select id from t where num=200
  1. 下面的查询也将导致全表扫描:
select id from t where name like ‘%abc%’

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

  1. in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)

对于连续的数值,能用 between 就不要用 in 了:

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

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

select id from t with(index(索引名)) where num=@num
  1. 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100

应改为:

select id from t where num=100*2
  1. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<‘2005-12-1’
  1. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

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

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

select col1,col2 into #t from t where 1=0

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

create table #t(…)
  1. 很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)

用下面的语句替换:

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

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

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

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

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

  6. 任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

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

  8. 避免频繁创建和删除临时表,以减少系统表资源的消耗。

  9. 临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

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

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

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

  13. 使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

  14. 与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

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

  16. 尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

  17. 尽量避免大事务操作,提高系统并发能力。

0

评论区