查询结果按in集合顺序显示_Mysql_脚本之家,性能优化

MySQL 查询in操作,查询结果按in集合顺序显示 复制代码 代码如下:select * from test where id
in order by find_in_set; select * from test where id in order by
substring_index;偶尔看到的。。。或许有人会注意过,但我以前真不知道 SQL:
select * from table where id IN ;
这样的情况取出来后,其实,id还是按1,2,3,4,5,6,7,8,9,排序的,但如果我们真要按IN里面的顺序排序怎么办?SQL能不能完成?是否需要取回来后再foreach一下?其实mysql就有这个方法
sql: select * from table where id IN order by field;
出来的顺序就是指定的顺序了。。。。这个,以前还真的从来没用过,偶尔看到,所以就记录了一下。一是做个笔记,二是希望可以给更多的人看到
MySQL中NOT IN语句对NULL值的处理 mysql> SELECT COUNT FROM CVE
WHERE name NOT IN (‘CVE-1999-0001’, ‘CVE-1999-0002’); +————-+ |
count | +————-+ | 17629 | +————-+ 1 row in set mysql>
SELECT COUNT FROM CVE WHERE name NOT IN (‘CVE-1999-0001’,
‘CVE-1999-0002’, NULL); +————-+ | count | +————-+ | 0 |
+————-+ 1 row in set
当在子查询中出现NULL的时候,结果就一定是0了。查了一下手册,确实有这样的说法。所以最后实际采用了这样的查询:
SELECT COUNT FROM CVE WHERE name NOT IN (SELECT cveID FROM cve_sig
WHERE cveID IS NOT NULL) 顺便提一下MySQL中正则表达式匹配的简单使用:
SELECT COUNT FROM Alarm WHERE (CVE NOT RLIKE
‘^CVE-[0-9]{4}-[0-9]{4}$’ OR CVE IS NULL)
当然,RLIKE也可以写作REGEXP,我个人倾向于使用RLIKE,因为拼写接近LIKE,可以见名知义。
mysql – not in table:info primary key id, info_type_id,
programme_id, episode_id 3, 4, 382, 100034 3, 8, 382, 100034 4, 8,
382, 100034 6, 8, 382, 100034 7, 8, 382, 100034 8, 8, 382, 100034 9, 8,
382, 100034 10, 8, 382, 100034 11, 8, 382, 100034 12, 8, 382, 100034 13,
8, 382, 100034 100001, 4, 382, 100034 100002, 4, 382, 100034 排除(id=3
&& info_type_id=8) and (id=4 &&
info_type_id=8)这两条记录,即找出其它记录 error: select * from info
where episode_id=100034 and id not in and info_type_id not in ; error
result: id, info_type_id, programme_id, episode_id 100001, 4, 382,
100034 100002, 4, 382, 100034 correct: select * from info where
episode_id=100034 and (id<>3 or info_type_id<>8) and
(id<>4 or info_type_id<>8); correct result: id,
info_type_id, programme_id, episode_id 3, 4, 382, 100034 6, 8, 382,
100034 7, 8, 382, 100034 8, 8, 382, 100034 9, 8, 382, 100034 10, 8, 382,
100034 11, 8, 382, 100034 12, 8, 382, 100034 13, 8, 382, 100034 100001,
4, 382, 100034 100002, 4, 382, 100034 理解:id<>3 or
info_type_id<>8排除掉id=3 &&
info_type_id=8这条记录,当表中主键多于一个时,不能简单地使用key1 NOT IN
AND key2 NOT IN ..

前言

MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇到一些比如响应时间慢,CPU打满等情况。阿里云RDS专家服务团队帮助云上客户解决过很多紧急问题。现将《ApsaraDB专家诊断报告》中出现的部分常见SQL问题总结如下,供大家参考。

常见SQL错误用法

  1. LIMIT 语句

分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,
name,
create_time字段上加组合索引。这样条件排序都能有效的利用到索引,性能迅速提升。

SELECT *

FROM  operation

WHERE  type = ‘SQLStats’

AND name = ‘SlowLog’

ORDER  BY create_time

LIMIT  1000, 10;

好吧,可能90%以上的DBA解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT
1000000,10” 时,程序员仍然会抱怨:我只取10条记录为什么还是慢?

要知道数据库也并不知道第1000000条记录从什么地方开始,即使有索引也需要从头计算一次。出现这种性能问题,多数情形下是程序员偷懒了。在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值当成参数作为查询条件的。SQL重新设计如下:

SELECT  *

FROM    operation

WHERE    type = ‘SQLStats’

AND      name = ‘SlowLog’

AND      create_time > ‘2017-03-16 14:00:00’

ORDER BY create_time limit 10;

在新设计下查询时间基本固定,不会随着数据量的增长而发生变化。

  1. 隐式转换

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句:

mysql> explain extended SELECT *

> FROM  my_balance b

> WHERE  b.bpn = 14000000123

>      AND b.isverified IS NULL ;

mysql> show warnings;

| Warning | 1739 | Cannot use ref access on index ‘bpn’ due to type or
collation conversion on field ‘bpn’

其中字段bpn的定义为varchar(20),MySQL的策略是将字符串转换为数字之后再比较。函数作用于表字段,索引失效。

上述情况可能是应用程序框架自动填入的参数,而不是程序员的原意。现在应用框架很多很繁杂,使用方便的同时也小心它可能给自己挖坑。

  1. 关联更新、删除

虽然MySQL5.6引入了物化特性,但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。

比如下面UPDATE语句,MySQL实际执行的是循环/嵌套子查询(DEPENDENT
SUBQUERY),其执行时间可想而知。

UPDATE operation o

SET    status = ‘applying’

WHERE  o.id IN (SELECT id

FROM  (SELECT o.id,

o.status

FROM  operation o

WHERE  o.group = 123

AND o.status NOT IN ( ‘done’ )

ORDER  BY o.parent,

o.id

LIMIT  1) t);

执行计划:

+—-+——————–+——-+——-+—————+———+———+——-+——+—————————————————–+

| id | select_type        | table | type  | possible_keys | key    |
key_len | ref  | rows | Extra                                         
    |

+—-+——————–+——-+——-+—————+———+———+——-+——+—————————————————–+

| 1  | PRIMARY            | o    | index |              | PRIMARY | 8   
  |      | 24  | Using where; Using temporary                        |

| 2  | DEPENDENT SUBQUERY |      |      |              |        |       
|      |      | Impossible WHERE noticed after reading const tables |

| 3  | DERIVED            | o    | ref  | idx_2,idx_5  | idx_5  | 8 
    | const | 1    | Using where; Using filesort                       
|

+—-+——————–+——-+——-+—————+———+———+——-+——+—————————————————–+

重写为JOIN之后,子查询的选择模式从DEPENDENT
SUBQUERY变成DERIVED,执行速度大大加快,从7秒降低到2毫秒。

UPDATE operation o

JOIN  (SELECT o.id,

o.status

FROM  operation o

WHERE  o.group = 123

AND o.status NOT IN ( ‘done’ )

ORDER  BY o.parent,

o.id

LIMIT  1) t

ON o.id = t.id

SET    status = ‘applying’

执行计划简化为:

+—-+————-+——-+——+—————+——-+———+——-+——+—————————————————–+

| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows | Extra                                              |

+—-+————-+——-+——+—————+——-+———+——-+——+—————————————————–+

| 1  | PRIMARY    |      |      |              |      |        |      | 
    | Impossible WHERE noticed after reading const tables |

| 2  | DERIVED    | o    | ref  | idx_2,idx_5  | idx_5 | 8      |
const | 1    | Using where; Using filesort                        |

+—-+————-+——-+——+—————+——-+———+——-+——+—————————————————–+

  1. 混合排序

MySQL不能利用索引进行混合排序。但在某些场景,还是有机会使用特殊方法提升性能的。

SELECT *

FROM  my_order o

INNER JOIN my_appraise a ON a.orderid = o.id

ORDER  BY a.is_reply ASC,

a.appraise_time DESC

LIMIT  0, 20

执行计划显示为全表扫描:

+—-+————-+——-+——–+————-+———+———+—————+———+-+

| id | select_type | table | type  | possible_keys    | key    |
key_len | ref      | rows    | Extra

+—-+————-+——-+——–+————-+———+———+—————+———+-+

|  1 | SIMPLE      | a    | ALL    | idx_orderid | NULL    | NULL    |
NULL    | 1967647 | Using filesort |

|  1 | SIMPLE      | o    | eq_ref | PRIMARY    | PRIMARY | 122    |
a.orderid |      1 | NULL          |

+—-+————-+——-+——–+———+———+———+—————–+———+-+

由于is_reply只有0和1两种状态,我们按照下面的方法重写后,执行时间从1.58秒降低到2毫秒。

SELECT *

FROM  ((SELECT *

FROM  my_order o

INNER JOIN my_appraise a

ON a.orderid = o.id

AND is_reply = 0

ORDER  BY appraise_time DESC

LIMIT  0, 20)

UNION ALL

(SELECT *

FROM  my_order o

INNER JOIN my_appraise a

ON a.orderid = o.id

AND is_reply = 1

ORDER  BY appraise_time DESC

LIMIT  0, 20)) t

ORDER  BY  is_reply ASC,

appraisetime DESC

LIMIT  20;

  1. EXISTS语句

MySQL对待EXISTS子句时,仍然采用嵌套子查询的执行方式。如下面的SQL语句:

SELECT *

FROM  my_neighbor n

LEFT JOIN my_neighbor_apply sra

ON n.id = sra.neighbor_id

AND sra.user_id = ‘xxx’

WHERE  n.topic_status < 4

AND EXISTS(SELECT 1

FROM  message_info m

WHERE  n.id = m.neighbor_id

AND m.inuser = ‘xxx’)

AND n.topic_type <> 5

执行计划为:

+—-+——————–+——-+——+—–+——————————————+———+——-+———+
—–+

| id | select_type        | table | type | possible_keys    | key  |
key_len | ref  | rows    | Extra  |

+—-+——————–+——-+——+
—–+——————————————+———+——-+———+
—–+

|  1 | PRIMARY            | n    | ALL  |  | NULL    | NULL    | NULL  |
1086041 | Using where                  |

|  1 | PRIMARY            | sra  | ref  |  | idx_user_id | 123    |
const |      1 | Using where          |

|  2 | DEPENDENT SUBQUERY | m    | ref  |  | idx_message_info  | 122 
  | const |      1 | Using index condition; Using where |

+—-+——————–+——-+——+
—–+——————————————+———+——-+———+
—–+

去掉exists更改为join,能够避免嵌套子查询,将执行时间从1.93秒降低为1毫秒。

SELECT *

FROM  my_neighbor n

INNER JOIN message_info m

ON n.id = m.neighbor_id

AND m.inuser = ‘xxx’

LEFT JOIN my_neighbor_apply sra

ON n.id = sra.neighbor_id

AND sra.user_id = ‘xxx’

WHERE  n.topic_status < 4

AND n.topic_type <> 5

新的执行计划:

+—-+————-+——-+——–+
—–+——————————————+———+
—–+——+ —–+

| id | select_type | table | type  | possible_keys    | key      |
key_len | ref  | rows | Extra                |

+—-+————-+——-+——–+
—–+——————————————+———+
—–+——+ —–+

|  1 | SIMPLE      | m    | ref    | | idx_message_info  | 122    |
const    |    1 | Using index condition |

|  1 | SIMPLE      | n    | eq_ref | | PRIMARY  | 122    | ighbor_id
|    1 | Using where      |

发表评论

电子邮件地址不会被公开。 必填项已用*标注