欢迎来到天天爱彩票安装_天天爱彩票安装到手机_天天爱彩票安装到手机客户端! 联系我们 网站地图

天天爱彩票安装_天天爱彩票安装到手机_天天爱彩票安装到手机客户端

0379-65557469

天天爱彩票官网
全国服务热线
0379-65557469

电话: 0379-65557469
0379-63930906
0379-63900388 
0379-63253525   
传真: 0379-65557469
地址:洛阳市洛龙区开元大道219号2幢1-2522、2501、2502、2503、2504、2505室 

天天爱彩票官网

8种常见SQL过错用法

作者:admin 发布时间:2019-07-11 21:23:33 浏览次数:226
打印 收藏 关闭
字体【
视力保护色

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;

在新规划下查询时刻根本固定,不会跟着数据量的增加而发生变化。

2、隐式转化

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 的战略是将字符串转化为数字之后再比较。函数效果于表字段,索引失效。

上述状况或许是运用程序结构主动填入的参数,而不是程序员的本意。现在运用结构许多很冗杂,运用方便的一同也当心它或许给自己挖坑。

3、相关更新、删去

尽管 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 |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+

4、混合排序

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;

5、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 |
| 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | Using where |
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+

6、条件下推

外部查询条件不能够下推到杂乱的视图或子查询的状况有:

聚合子查询;

含有 LIMIT 的子查询;

UNION 或 UNION ALL 子查询;

输出字段中的子查询;

如下面的句子,从履行计划能够看出其条件效果于聚合子查询之后:

SELECT * 
FROM (SELECT target,
Count(*)
FROM operation
GROUP BY target) t
WHERE target = 'rm-xxxx'
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| 1 | PRIMARY | | ref | | | 514 | const | 2 | Using where |
| 2 | DERIVED | operation | index | idx_4 | idx_4 | 519 | NULL | 20 | Using index |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+

确认从语义上查询条件能够直接下推后,重写如下:

SELECT target, 
Count(*)
FROM operation
WHERE target = 'rm-xxxx'
GROUP BY target

履行计划变为:

+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+

7、提早缩小规模

先上初始 SQL 句子:

SELECT * 
FROM my_order o
LEFT JOIN my_userinfo u
ON o.uid = u.uid
LEFT JOIN my_productinfo p
ON o.pid = p.pid
WHERE ( o.display = 0 )
AND ( o.ostaus = 1 )
ORDER BY o.selltime DESC
LIMIT 0, 15

该SQL句子本意是:先做一系列的左衔接,然后排序取前15条记载。从履行计划也能够看出,最终一步预算排序记载数为90万,时刻耗费为12秒。

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id | select_type | table |8种常见SQL过错用法 type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------------------------8种常见SQL过错用法---------------------+

因为最终 WHERE 条件以及排序均针对最左主表,因而能够先对 my_order 排序提早缩小数据量再做左衔接。SQL 重写后如下,履行时刻缩小为1毫秒左右。

SELECT * 
FROM (
SELECT *
FROM my_order o
WHERE ( o.display = 0 )
AND ( o.ostaus = 1 )
ORDER BY o.selltime DESC
LIMIT 0, 15
) o
LEFT JOIN my_userinfo u
ON o.uid = u.uid
LEFT JOIN my_productinfo p
ON o.pid = p.pid
ORDER BY o.selltime DESC
limit 0, 15

再查看履行计划:子查询物化后(select_type=DERIVED)参加 JOIN。尽管预算行扫描依然为90万,可是运用了索引以及 LIMIT 子句后,实践履行时刻变得很小。

+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 15 | Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
| 1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+

8、中心成果集下推

再来看下面这个现已开始优化过的比如(左衔接中的主表优先效果查询条件):

SELECT a.*, 
c.allocated
FROM (
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20) a
LEFT JOIN
(
SELECT resourcesid, sum(ifnull(allocation, 0) * 12345) allocated
FROM my_resources
GROUP BY resourcesid) c
ON a.resourceid = c.resourcesid

那么该句子还存在其它问题吗?不难看出子查询 c 是全表聚合查询,在表数量特别大的状况下会导致整个句子的功能下降。

其实关于子查询 c,左衔接最终成果集只关怀能和主表 resourceid 能匹配的数据。因而咱们能够重写句子如下,履行时刻从本来的2秒下降到2毫秒。

SELECT a.*, 
c.allocated
FROM (
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20) a
LEFT JOIN
(
SELECT resourcesid, sum(ifnull(allocation, 0) * 12345) allocated
FROM my_resources r,
(
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20) a
WHERE r.resourcesid8种常见SQL过错用法 = a.resourcesid
GROUP BY resourcesid) c
ON a.resourceid = c.resourcesid

可是子查询 a 在咱们的SQL句子中呈现了屡次。这种写法不只存在额定的开支,还使得整个句子显的冗杂。运用 WITH 句子再次重写:

WITH a AS 
(
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20)
SELECT a.*,
c.allocated
FROM a
LEFT JOIN
(
SELECT resourcesid, sum(ifnull(allocation, 0) * 12345) allocated
FROM my_resources r,
a
WHERE r.resourcesid = a.resourcesid
GROUP BY resourcesid) c
ON a.resourceid = c.resourcesid

总结

数据库编译器发生履行计划,决议着SQL的实践履行方法。可是编译器仅仅极力服务,一切数据库的编译器都不是一无是处的。

上述说到的大都场景,在其它数据库中也存在功能问题。了解数据库编译器的特性,才干避规其矮处,写出高功能的SQL句子。

程序员在规划数据模型以及编写SQL句子时,要把算法的思维或认识带进来。

编写杂乱SQL句子要养成运用 WITH 句子的习气。简练且思路清晰的SQL句子也能减小数据库的担负 。


我现在在某互联网公司做架构师,现已有5年经历,每天都会写架构师系列的文章,感兴趣的朋友能够重视我和我一同讨论,重视我,免费共享Java基础教程,以及进阶的高档Java架构师教程,悉数免费送

原文:https://zhuanlan.zhihu.com/p/72182919

来历:知乎

作者:雁高飞

版权所有:洛阳市建设工程咨询有限责任公司 联系人:李经理 电话: 地址:洛阳市洛龙区开元大道219号2幢1-2522、2501、2502、2503、2504、2505室
版权所有 天天爱彩票安装 陕ICP备120271451号-10