专栏名称: java一日一条
主要是讲解编程语言java,并且每天都推送一条关于java编程语言的信息
目录
相关文章推荐
51好读  ›  专栏  ›  java一日一条

你知道这 8 种 SQL 错误用法吗?

java一日一条  · 公众号  · Java  · 2020-12-05 22:03

正文

请到「今天看啥」查看全文


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






请到「今天看啥」查看全文