Java架构师条记丨常见的错误 SQL 用法，你中招了吗？

去掉 exists 变动为 join，可以或许停止嵌套子查询，将执行时刻从1.93秒低落为1毫秒。

新的执行打算：

+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+

| 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 | Usingwhere|

| 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | Usingwhere|

+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+

6、前提下推

外部查询前提不可以或许下推到伟大的视图或子查询的环境有：

聚合子查询;

含有 LIMIT 的子查询;

UNION 或 UNION ALL 子查询;

输出字段中的子查询;

如下面的语句，从执行打算可以看出其前提浸染于聚合子查询之后：

确定从语义上查询前提可以直接下推后，重写如下：

执行打算变为：

+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+

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

+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+

| 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Usingwhere; Using index |

+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+

7、提前缩小范畴

先上初始 SQL 语句：

该SQL语句原意是：先做一系列的左毗连，然后排序取前15笔记录。从执行打算也可以看出，最后一步估算排序记录数为90万，时刻耗损为12秒。

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

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

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Usingwhere; 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 | Usingwhere; Using join buffer (Block Nested Loop) |

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

因为最后 WHERE 前提以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左毗连。SQL 重写后如下，执行时刻缩小为1毫秒阁下。

再搜查执行打算：子查询物化后(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 | Usingwhere; Using join buffer (Block Nested Loop) |

| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Usingwhere|

+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+

8、中间功效集下推

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!