【Mysql】复杂SQL调优一例

最新推荐文章于 2023-01-18 11:51:41 发布
最新推荐文章于 2023-01-18 11:51:41 发布
阅读量434 收藏
点赞数
分类专栏： Mysql Performance Tuning 文章标签： mysql 调优
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/AskTommorow/article/details/53645062
版权

*相关表已经脱敏处理

1.原SQL

SELECT tmp.PARTY_ID,
       s.TICKER_SYMBOL,
       tmp.SHAREHOLDER_NAME,
       tmp.END_DATE_NEW,
       tmp.SHARE_NUM_NEW,
       tmp.END_DATE_LAST,
       tmp.SHARE_NUM_LAST,
       tmp.PROPORTION,
       tmp.CHANGE_DIRECTION
  FROM (SELECT DISTINCT q.PARTY_ID,
                        q.SHAREHOLDER_NAME,
                        q.END_DATE_NEW,
                        q.SHARE_NUM_NEW,
                        q.END_DATE_LAST,
                        q.SHARE_NUM_LAST,
                        CASE
                          WHEN q.PROPORTION IS NULL THEN
                           s.PROPORTION
                          ELSE
                           q.PROPORTION
                        END PROPORTION,
                        CASE
                          WHEN s.CHANGE_DIRECTION IS NULL THEN
                           q.CHANGE_DIRECTION
                          ELSE
                           s.CHANGE_DIRECTION
                        END CHANGE_DIRECTION
          FROM (SELECT m.PARTY_ID,
                       m.END_DATE_NEW,
                       m.SHAREHOLDER_NAME,
                       m.SHARE_NUM_NEW,
                       m.END_DATE_LAST,
                       m.SHARE_NUM_LAST,
                       n.PROPORTION,
                       CASE
                         WHEN abs(m.SHARE_NUM_NEW -
                                  m.SHARE_NUM_LAST * (1 + n.PROPORTION)) < 0.01 THEN
                          '不变'
                         WHEN m.SHARE_NUM_NEW -
                              m.SHARE_NUM_LAST * (1 + n.PROPORTION) >= 0.01 THEN
                          '增持'
                         ELSE
                          '减持'
                       END CHANGE_DIRECTION
                  FROM (SELECT a.PARTY_ID         PARTY_ID,
                               a.END_DATE         END_DATE_NEW,
                               a.SHAREHOLDER_NAME SHAREHOLDER_NAME,
                               a.SHARE_NUM        SHARE_NUM_NEW,
                               b.END_DATE         END_DATE_LAST,
                               b.SHARE_NUM        SHARE_NUM_LAST
                          FROM (SELECT *
                                  FROM testtable1
                                 WHERE (END_DATE LIKE '%-03-31%' OR
                                       END_DATE LIKE '%-06-30' OR
                                       END_DATE LIKE '%-09-30' OR
                                       END_DATE LIKE '%-12-31')
                                   AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                       SHAREHOLDER_NAME LIKE '%汇金%' OR
                                       SHAREHOLDER_NAME = '中国证券金融股份有限公司')) a,
                               (SELECT *
                                  FROM testtable1
                                 WHERE (END_DATE LIKE '%-03-31%' OR
                                       END_DATE LIKE '%-06-30' OR
                                       END_DATE LIKE '%-09-30' OR
                                       END_DATE LIKE '%-12-31')
                                   AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                       SHAREHOLDER_NAME LIKE '%汇金%' OR
                                       SHAREHOLDER_NAME = '中国证券金融股份有限公司')) b
                         WHERE a.PARTY_ID = b.PARTY_ID
                           AND a.SHAREHOLDER_NAME = b.SHAREHOLDER_NAME
                           AND b.END_DATE =
                               (SELECT MAX(END_DATE)
                                  FROM (SELECT *
                                          FROM testtable1
                                         WHERE (END_DATE LIKE '%-03-31%' OR
                                               END_DATE LIKE '%-06-30' OR
                                               END_DATE LIKE '%-09-30' OR
                                               END_DATE LIKE '%-12-31')
                                           AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                               SHAREHOLDER_NAME LIKE '%汇金%' OR
                                               SHAREHOLDER_NAME =
                                               '中国证券金融股份有限公司')) c
                                 WHERE c.PARTY_ID = a.PARTY_ID
                                   AND c.SHAREHOLDER_NAME = a.SHAREHOLDER_NAME
                                   AND c.END_DATE < a.END_DATE)
                        UNION
                        SELECT PARTY_ID         PARTY_ID,
                               END_DATE         END_DATE_NEW,
                               SHAREHOLDER_NAME SHAREHOLDER_NAME,
                               SHARE_NUM        SHARE_NUM_NEW,
                               NULL             END_DATE_LAST,
                               NULL             SHARE_NUM_LAST
                          FROM testtable1 a,
                               (SELECT PARTY_ID dm,
                                       MIN(END_DATE) zxrq,
                                       SHAREHOLDER_NAME gdmc
                                  FROM testtable1
                                 WHERE (END_DATE LIKE '%-03-31%' OR
                                       END_DATE LIKE '%-06-30' OR
                                       END_DATE LIKE '%-09-30' OR
                                       END_DATE LIKE '%-12-31')
                                   AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                       SHAREHOLDER_NAME LIKE '%汇金%' OR
                                       SHAREHOLDER_NAME = '中国证券金融股份有限公司')
                                 GROUP BY PARTY_ID, SHAREHOLDER_NAME) b
                         WHERE a.PARTY_ID = b.dm
                           AND a.END_DATE = b.zxrq
                           AND a.SHAREHOLDER_NAME = b.gdmc) m,
                       (SELECT b.PARTY_ID        PARTY_ID,
                               a.LIST_DATE       除权日,
                               a.ALLOTMENT_RATIO PROPORTION
                          FROM equ_allot a, testtable3 b
                         WHERE a.SECURITY_ID = b.SECURITY_ID
                           AND b.TRANS_CURR_CD = 'CNY'
                           AND a.ALLOTMENT_RATIO IS NOT NULL
                           AND a.LIST_DATE IS NOT NULL
                        UNION
                        SELECT b.PARTY_ID PARTY_ID,
                               a.EX_DIV_DATE 除权日,
                               (CASE
                                 WHEN a.PER_SHARE_DIV_RATIO IS NULL THEN
                                  0
                                 ELSE
                                  a.PER_SHARE_DIV_RATIO
                               END) + (CASE
                                 WHEN a.PER_SHARE_TRANS_RATIO IS NULL THEN
                                  0
                                 ELSE
                                  a.PER_SHARE_TRANS_RATIO
                               END) PROPORTION
                          FROM testtable2 a, testtable3 b
                         WHERE a.EVENT_PROCESS_CD = '6'
                           AND a.IS_DIV IN ('05', '09', '15')
                           AND a.SECURITY_ID = b.SECURITY_ID
                           AND b.TRANS_CURR_CD = 'CNY'
                           AND (a.PER_SHARE_DIV_RATIO IS NOT NULL OR
                               a.PER_SHARE_TRANS_RATIO IS NOT NULL)) n
                 WHERE m.PARTY_ID = n.PARTY_ID
                   AND n.除权日 BETWEEN m.END_DATE_LAST AND m.END_DATE_NEW) s
         RIGHT JOIN (SELECT a.PARTY_ID PARTY_ID,
                           a.END_DATE END_DATE_NEW,
                           a.SHAREHOLDER_NAME SHAREHOLDER_NAME,
                           a.SHARE_NUM SHARE_NUM_NEW,
                           b.END_DATE END_DATE_LAST,
                           b.SHARE_NUM SHARE_NUM_LAST,
                           NULL PROPORTION,
                           CASE
                             WHEN abs(a.SHARE_NUM - b.SHARE_NUM) < 0.01 THEN
                              '不变'
                             WHEN a.SHARE_NUM - b.SHARE_NUM >= 0.01 THEN
                              '增持'
                             ELSE
                              '减持'
                           END CHANGE_DIRECTION
                      FROM (SELECT *
                              FROM testtable1
                             WHERE (END_DATE LIKE '%-03-31%' OR
                                   END_DATE LIKE '%-06-30' OR
                                   END_DATE LIKE '%-09-30' OR
                                   END_DATE LIKE '%-12-31')
                               AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                   SHAREHOLDER_NAME LIKE '%汇金%' OR
                                   SHAREHOLDER_NAME = '中国证券金融股份有限公司')) a,
                           (SELECT *
                              FROM testtable1
                             WHERE (END_DATE LIKE '%-03-31%' OR
                                   END_DATE LIKE '%-06-30' OR
                                   END_DATE LIKE '%-09-30' OR
                                   END_DATE LIKE '%-12-31')
                               AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                   SHAREHOLDER_NAME LIKE '%汇金%' OR
                                   SHAREHOLDER_NAME = '中国证券金融股份有限公司')) b
                     WHERE a.PARTY_ID = b.PARTY_ID
                       AND a.SHAREHOLDER_NAME = b.SHAREHOLDER_NAME
                       AND b.END_DATE =
                           (SELECT MAX(END_DATE)
                              FROM (SELECT *
                                      FROM testtable1
                                     WHERE (END_DATE LIKE '%-03-31%' OR
                                           END_DATE LIKE '%-06-30' OR
                                           END_DATE LIKE '%-09-30' OR
                                           END_DATE LIKE '%-12-31')
                                       AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                           SHAREHOLDER_NAME LIKE '%汇金%' OR
                                           SHAREHOLDER_NAME = '中国证券金融股份有限公司')) c
                             WHERE c.PARTY_ID = a.PARTY_ID
                               AND c.SHAREHOLDER_NAME = a.SHAREHOLDER_NAME
                               AND c.END_DATE < a.END_DATE)
                    UNION
                    SELECT PARTY_ID PARTY_ID,
                           END_DATE END_DATE_NEW,
                           SHAREHOLDER_NAME SHAREHOLDER_NAME,
                           SHARE_NUM SHARE_NUM_NEW,
                           NULL END_DATE_LAST,
                           NULL SHARE_NUM_LAST,
                           NULL PROPORTION,
                           '新进' CHANGE_DIRECTION
                      FROM testtable1 a,
                           (SELECT PARTY_ID dm,
                                   MIN(END_DATE) zxrq,
                                   SHAREHOLDER_NAME gdmc
                              FROM testtable1
                             WHERE (END_DATE LIKE '%-03-31%' OR
                                   END_DATE LIKE '%-06-30' OR
                                   END_DATE LIKE '%-09-30' OR
                                   END_DATE LIKE '%-12-31')
                               AND (SHAREHOLDER_NAME LIKE '%证金%' OR
                                   SHAREHOLDER_NAME LIKE '%汇金%' OR
                                   SHAREHOLDER_NAME = '中国证券金融股份有限公司')
                             GROUP BY PARTY_ID, SHAREHOLDER_NAME) b
                     WHERE a.PARTY_ID = b.dm
                       AND a.END_DATE = b.zxrq
                       AND a.SHAREHOLDER_NAME = b.gdmc) q
            ON s.PARTY_ID = q.PARTY_ID
           AND s.END_DATE_NEW = q.END_DATE_NEW
           AND s.SHAREHOLDER_NAME = q.SHAREHOLDER_NAME) tmp,
       testtable3 s
 WHERE tmp.PARTY_ID = s.PARTY_ID
   and s.ASSET_CLASS = 'e'
   and s.EXCHANGE_CD IN ('XSHE', 'XSHG')
   and s.TRANS_CURR_CD = 'CNY'
   and s.DY_USE_FLG = '1'
   and s.TICKER_SYMBOL not like '003%'
   and s.TICKER_SYMBOL not like '14%'
   and s.TICKER_SYMBOL not like '36%'
   and s.TICKER_SYMBOL <> '600849'
 ORDER BY tmp.PARTY_ID, tmp.END_DATE_NEW

2.优化SQL1

主要把共同部分的SQL找出来了，因为mysql不支持with子句的用法，先做成了一个视图。

--   创建子视图
use backup;

create view testbzm as
SELECT *
  FROM datayesdb.testtable1
 WHERE (END_DATE LIKE '%-03-31%' OR END_DATE LIKE '%-06-30' OR
       END_DATE LIKE '%-09-30' OR END_DATE LIKE '%-12-31')
   AND (SHAREHOLDER_NAME LIKE '%证金%' OR SHAREHOLDER_NAME LIKE '%汇金%' OR
       SHAREHOLDER_NAME = '中国证券金融股份有限公司')

-- 执行查询
SELECT tmp.PARTY_ID,
       s.TICKER_SYMBOL,
       tmp.SHAREHOLDER_NAME,
       tmp.END_DATE_NEW,
       tmp.SHARE_NUM_NEW,
       tmp.END_DATE_LAST,
       tmp.SHARE_NUM_LAST,
       tmp.PROPORTION,
       tmp.CHANGE_DIRECTION
  FROM (SELECT DISTINCT q.PARTY_ID,
                        q.SHAREHOLDER_NAME,
                        q.END_DATE_NEW,
                        q.SHARE_NUM_NEW,
                        q.END_DATE_LAST,
                        q.SHARE_NUM_LAST,
                        CASE
                          WHEN q.PROPORTION IS NULL THEN
                           s.PROPORTION
                          ELSE
                           q.PROPORTION
                        END PROPORTION,
                        CASE
                          WHEN s.CHANGE_DIRECTION IS NULL THEN
                           q.CHANGE_DIRECTION
                          ELSE
                           s.CHANGE_DIRECTION
                        END CHANGE_DIRECTION
          FROM (SELECT m.PARTY_ID,
                       m.END_DATE_NEW,
                       m.SHAREHOLDER_NAME,
                       m.SHARE_NUM_NEW,
                       m.END_DATE_LAST,
                       m.SHARE_NUM_LAST,
                       n.PROPORTION,
                       CASE
                         WHEN abs(m.SHARE_NUM_NEW -
                                  m.SHARE_NUM_LAST * (1 + n.PROPORTION)) < 0.01 THEN
                          '不变'
                         WHEN m.SHARE_NUM_NEW -
                              m.SHARE_NUM_LAST * (1 + n.PROPORTION) >= 0.01 THEN
                          '增持'
                         ELSE
                          '减持'
                       END CHANGE_DIRECTION
                  FROM (SELECT a.PARTY_ID         PARTY_ID,
                               a.END_DATE         END_DATE_NEW,
                               a.SHAREHOLDER_NAME SHAREHOLDER_NAME,
                               a.SHARE_NUM        SHARE_NUM_NEW,
                               b.END_DATE         END_DATE_LAST,
                               b.SHARE_NUM        SHARE_NUM_LAST
                          FROM backup.testbzm a, backup.testbzm b
                         WHERE a.PARTY_ID = b.PARTY_ID
                           AND a.SHAREHOLDER_NAME = b.SHAREHOLDER_NAME
                           AND b.END_DATE =
                               (SELECT MAX(END_DATE)
                                  FROM backup.testbzm c
                                 WHERE c.PARTY_ID = a.PARTY_ID
                                   AND c.SHAREHOLDER_NAME = a.SHAREHOLDER_NAME
                                   AND c.END_DATE < a.END_DATE)
                        UNION
                        SELECT PARTY_ID         PARTY_ID,
                               END_DATE         END_DATE_NEW,
                               SHAREHOLDER_NAME SHAREHOLDER_NAME,
                               SHARE_NUM        SHARE_NUM_NEW,
                               NULL             END_DATE_LAST,
                               NULL             SHARE_NUM_LAST
                          FROM testtable1 a,
                               (SELECT PARTY_ID dm,
                                       MIN(END_DATE) zxrq,
                                       SHAREHOLDER_NAME gdmc
                                  FROM backup.testbzm
                                 GROUP BY PARTY_ID, SHAREHOLDER_NAME) b
                         WHERE a.PARTY_ID = b.dm
                           AND a.END_DATE = b.zxrq
                           AND a.SHAREHOLDER_NAME = b.gdmc) m,
                       (SELECT b.PARTY_ID        PARTY_ID,
                               a.LIST_DATE       除权日,
                               a.ALLOTMENT_RATIO PROPORTION
                          FROM equ_allot a, testtable3 b
                         WHERE a.SECURITY_ID = b.SECURITY_ID
                           AND b.TRANS_CURR_CD = 'CNY'
                           AND a.ALLOTMENT_RATIO IS NOT NULL
                           AND a.LIST_DATE IS NOT NULL
                        UNION
                        SELECT b.PARTY_ID PARTY_ID,
                               a.EX_DIV_DATE 除权日,
                               (CASE
                                 WHEN a.PER_SHARE_DIV_RATIO IS NULL THEN
                                  0
                                 ELSE
                                  a.PER_SHARE_DIV_RATIO
                               END) + (CASE
                                 WHEN a.PER_SHARE_TRANS_RATIO IS NULL THEN
                                  0
                                 ELSE
                                  a.PER_SHARE_TRANS_RATIO
                               END) PROPORTION
                          FROM testtable2 a, testtable3 b
                         WHERE a.EVENT_PROCESS_CD = '6'
                           AND a.IS_DIV IN ('05', '09', '15')
                           AND a.SECURITY_ID = b.SECURITY_ID
                           AND b.TRANS_CURR_CD = 'CNY'
                           AND (a.PER_SHARE_DIV_RATIO IS NOT NULL OR
                               a.PER_SHARE_TRANS_RATIO IS NOT NULL)) n
                 WHERE m.PARTY_ID = n.PARTY_ID
                   AND n.除权日 BETWEEN m.END_DATE_LAST AND m.END_DATE_NEW) s
         RIGHT JOIN (SELECT a.PARTY_ID PARTY_ID,
                           a.END_DATE END_DATE_NEW,
                           a.SHAREHOLDER_NAME SHAREHOLDER_NAME,
                           a.SHARE_NUM SHARE_NUM_NEW,
                           b.END_DATE END_DATE_LAST,
                           b.SHARE_NUM SHARE_NUM_LAST,
                           NULL PROPORTION,
                           CASE
                             WHEN abs(a.SHARE_NUM - b.SHARE_NUM) < 0.01 THEN
                              '不变'
                             WHEN a.SHARE_NUM - b.SHARE_NUM >= 0.01 THEN
                              '增持'
                             ELSE
                              '减持'
                           END CHANGE_DIRECTION
                      FROM backup.testbzm a, backup.testbzm b
                     WHERE a.PARTY_ID = b.PARTY_ID
                       AND a.SHAREHOLDER_NAME = b.SHAREHOLDER_NAME
                       AND b.END_DATE =
                           (SELECT MAX(END_DATE)
                              FROM backup.testbzm c
                             WHERE c.PARTY_ID = a.PARTY_ID
                               AND c.SHAREHOLDER_NAME = a.SHAREHOLDER_NAME
                               AND c.END_DATE < a.END_DATE)
                    UNION
                    SELECT PARTY_ID PARTY_ID,
                           END_DATE END_DATE_NEW,
                           SHAREHOLDER_NAME SHAREHOLDER_NAME,
                           SHARE_NUM SHARE_NUM_NEW,
                           NULL END_DATE_LAST,
                           NULL SHARE_NUM_LAST,
                           NULL PROPORTION,
                           '新进' CHANGE_DIRECTION
                      FROM testtable1 a,
                           (SELECT PARTY_ID dm,
                                   MIN(END_DATE) zxrq,
                                   SHAREHOLDER_NAME gdmc
                              FROM backup.testbzm
                             GROUP BY PARTY_ID, SHAREHOLDER_NAME) b
                     WHERE a.PARTY_ID = b.dm
                       AND a.END_DATE = b.zxrq
                       AND a.SHAREHOLDER_NAME = b.gdmc) q
            ON s.PARTY_ID = q.PARTY_ID
           AND s.END_DATE_NEW = q.END_DATE_NEW
           AND s.SHAREHOLDER_NAME = q.SHAREHOLDER_NAME) tmp,
       testtable3 s
 WHERE tmp.PARTY_ID = s.PARTY_ID
   and s.ASSET_CLASS = 'e'
   and s.EXCHANGE_CD IN ('XSHE', 'XSHG')
   and s.TRANS_CURR_CD = 'CNY'
   and s.DY_USE_FLG = '1'
   and s.TICKER_SYMBOL not like '003%'
   and s.TICKER_SYMBOL not like '14%'
   and s.TICKER_SYMBOL not like '36%'
   and s.TICKER_SYMBOL <> '600849'
 ORDER BY tmp.PARTY_ID, tmp.END_DATE_NEW

3最终优化

经过第二步之后差不多能看了，但是仔细看业务还是又可以合并的内容，结合和业务人员的沟通最终优化如下：

--   创建子视图
use backup;

create view testbzm as
SELECT *
  FROM datayesdb.testtable1
 WHERE (END_DATE LIKE '%-03-31%' OR END_DATE LIKE '%-06-30' OR
       END_DATE LIKE '%-09-30' OR END_DATE LIKE '%-12-31')
   AND (SHAREHOLDER_NAME LIKE '%证金%' OR SHAREHOLDER_NAME LIKE '%汇金%' OR
       SHAREHOLDER_NAME = '中国证券金融股份有限公司')

use backup;

create view chuquanpg as
SELECT -- 除权日期和配股比例
 b.PARTY_ID PARTY_ID, a.LIST_DATE cqr, a.ALLOTMENT_RATIO PROPORTION
  FROM datayesdb.equ_allot a, datayesdb.testtable3 b
 WHERE a.SECURITY_ID = b.SECURITY_ID
   AND b.TRANS_CURR_CD = 'CNY'
   AND a.ALLOTMENT_RATIO IS NOT NULL
   AND a.LIST_DATE IS NOT NULL
   and a.EQU_TYPE_CD = '0201010201' -- 核实增加条件
UNION
SELECT -- 分红除权日期，分红的送转比
 b.PARTY_ID PARTY_ID,
 a.EX_DIV_DATE cqr,
 (CASE
   WHEN a.PER_SHARE_DIV_RATIO IS NULL THEN
    0
   ELSE
    a.PER_SHARE_DIV_RATIO
 END) + (CASE
   WHEN a.PER_SHARE_TRANS_RATIO IS NULL THEN
    0
   ELSE
    a.PER_SHARE_TRANS_RATIO
 END) PROPORTION
  FROM datayesdb.testtable2 a, datayesdb.testtable3 b
 WHERE a.EVENT_PROCESS_CD = '6'
   AND a.IS_DIV IN ('05', '09', '15')
   AND a.SECURITY_ID = b.SECURITY_ID
   AND b.TRANS_CURR_CD = 'CNY'
   AND (a.PER_SHARE_DIV_RATIO IS NOT NULL OR
       a.PER_SHARE_TRANS_RATIO IS NOT NULL)


-- 执行查询
SELECT tmp.PARTY_ID,
       s.TICKER_SYMBOL,
       tmp.SHAREHOLDER_NAME,
       tmp.END_DATE_NEW,
       tmp.SHARE_NUM_NEW,
       tmp.END_DATE_LAST,
       tmp.SHARE_NUM_LAST,
       tmp.PROPORTION,
       tmp.CHANGE_DIRECTION
  FROM (SELECT a.PARTY_ID PARTY_ID,
               a.END_DATE END_DATE_NEW,
               a.SHAREHOLDER_NAME SHAREHOLDER_NAME,
               a.SHARE_NUM SHARE_NUM_NEW,
               b.END_DATE END_DATE_LAST,
               b.SHARE_NUM SHARE_NUM_LAST,
               (SELECT PROPORTION
                  FROM backup.chuquanpg z
                 WHERE z.PARTY_ID = a.PARTY_ID
                   AND z.cqr BETWEEN b.END_DATE AND a.END_DATE LIMIT 1) PROPORTION,
               CASE
                 WHEN abs(a.SHARE_NUM - b.SHARE_NUM) < 0.01 THEN
                  '不变'
                 WHEN a.SHARE_NUM - b.SHARE_NUM >= 0.01 THEN
                  '增持'
                 ELSE
                  '减持'
               END CHANGE_DIRECTION
          FROM backup.testbzm a, backup.testbzm b
         WHERE a.PARTY_ID = b.PARTY_ID
           AND a.SHAREHOLDER_NAME = b.SHAREHOLDER_NAME
           AND b.END_DATE = (SELECT MAX(END_DATE)
                               FROM backup.testbzm c
                              WHERE c.PARTY_ID = a.PARTY_ID
                                AND c.SHAREHOLDER_NAME = a.SHAREHOLDER_NAME
                                AND c.END_DATE < a.END_DATE)
        UNION ALL
        SELECT PARTY_ID PARTY_ID,
               END_DATE END_DATE_NEW,
               SHAREHOLDER_NAME SHAREHOLDER_NAME,
               SHARE_NUM SHARE_NUM_NEW,
               NULL END_DATE_LAST,
               NULL SHARE_NUM_LAST,
               NULL PROPORTION,
               '新进' CHANGE_DIRECTION
          FROM testtable1 a,
               (SELECT PARTY_ID dm, MIN(END_DATE) zxrq, SHAREHOLDER_NAME gdmc
                  FROM backup.testbzm
                 GROUP BY PARTY_ID, SHAREHOLDER_NAME) b
         WHERE a.PARTY_ID = b.dm
           AND a.END_DATE = b.zxrq
           AND a.SHAREHOLDER_NAME = b.gdmc) tmp,
       testtable3 s
 WHERE tmp.PARTY_ID = s.PARTY_ID
   and s.ASSET_CLASS = 'e'
   and s.EXCHANGE_CD IN ('XSHE', 'XSHG')
   and s.TRANS_CURR_CD = 'CNY'
   and s.DY_USE_FLG = '1'
   and s.TICKER_SYMBOL not like '003%'
   and s.TICKER_SYMBOL not like '14%'
   and s.TICKER_SYMBOL not like '36%'
   and s.TICKER_SYMBOL <> '600849'
 ORDER BY tmp.PARTY_ID, tmp.END_DATE_NEW

小结：
1. 遇到很复杂的SQL（特别是没有任何备注的），最快的处理方法是拉业务人员过来沟通。
2. SQL分解看逻辑，然后逐步理解整个逻辑。
3. mysql的执行计划中，视图是作为设备插入的，类似于临时表，本案例因为最终要提供视图给API来调用，所以必须拆分子视图（mysql视图不支持子查询，只能拆成子视图），这样正好提高了系统的效率（原SQL执行计划走乱，效率很差）

AskTOMmorow
关注
专栏目录
4.Mysql Explain sql调优
qq_44605317的博客
06-06 389
这一点，也是针对联合索引来说的，前面我们强调过，联合索引里面，所有的记录都是按照索引列的值从小到大的顺序排序好的，而联合索引则是按创建索引的顺序进行排序。根据system>const>eq_ref>ref>range>index>ALL 的原则，看起来在联合索引中，is not null的表现会更好（如果列可为null的话），但是key_len的长度增加了1。所以总的来说，在设计表时列尽可能的不要声明为null。
SQL调优实战总结
xieyinghao_bupt的博客
02-21 982
SQL调优实战总结 前言 作为开发人员，我们免不了与sql打交道。有些sql可能在业务的最开始，执行是毫无问题的。但是随着业务量的提升以及业务复杂度的加深，可能之前的sql就会逐渐展现出疲惫之势了。这时就会面临sql调优。 那么调优到底如何调？不同的人有不同的姿势。可能大部分人首先想到的就是加索引。 没错，加索引是一种比较典型，也是一种比较廉价的手段。 但是，索引怎么加？加在哪里？加之后是否会对已有的其他sql产生影响？这些问题都是需要考虑的。 同时也应该意识到，索引是一把双刃剑，就像硬币的两面，加快查询
一个跨库复杂查询的SQL优化的案例
荒原狼的专栏
07-31 7021
导读 为了帮客户出一个报表，需要跨三个库进行一个复杂的查询，用到了 in，union all，left join等关键字， 其中几个字段还需要进行SUM，Count等计算。未优化前查询耗时368秒。 待优化场景 先看一下客户程序员写的这个复杂的SQL语句吧，看了都头大。虽说辉哥见多识广，还是被这个SQL吓到了。 select distinct a.cn,e.chinese_nam
MySQL 性能调优——SQL 查询优化
weixin_30486037的博客
06-27 1156
如何设计最优的数据库表结构，如何建立最好的索引，以及如何扩展数据库的查询，这些对于高性能来说都是必不可少的。但是只有这些还不够，要获得良好的数据库性能，我们还要设计合理的数据库查询，如果查询设计的很糟糕，即使增加再多的只读从库，表结构设计的再合理，索引再合适，只要查询不能使用到这些东西，也无法实现高性能的查询。所以说查询优化，索引优化，库表结构优化需要齐头并进。 在进行库表结构设计时，我们要考虑...
mysql 复杂sql 面试_面试：聊聊sql优化（1）
weixin_35828357的博客
02-18 156
mysql优化我一般遵从五个原则：减少数据访问： 设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘io及网络io减少交互次数： 批量DML操作，函数存储等减少数据连接次数减少服务器CPU开销： 尽量减少数据库排序操作，和全表查询 减少cpu 内存占用利用更多资源： 使用表分区，可以增加并行操作，更大限度利用cpu资源sql语句常见的...
mysql优化复杂查询语句_mysql-sql语句的查询优化，各位看看可以怎么优化，新人初学，SQL有点复杂，压力山大。。。。。...
weixin_33099015的博客
02-02 187
如下 项目用的是 mysql 是项目的查询SQL语句， 查询结果为 88 的竟然要1分40秒， 结果为5的要 2秒，SQL语句不是我写的，大家看看有什么地方可以优化的，想要优化查询速度的话，可以从哪方面入手修改。SELECT COUNT(DISTINCT ttsp.id) AS postpone_count, ttp.task_document_id AS task_document_id,...
mysql数据库复杂_mysql数据库复杂sql优化问题 - 墨天轮问答
weixin_30801745的博客
02-08 255
生产环境有条慢sql需优化，耗时609s,如下：sum( fsl_unrelease_pack_ob_lines.qty ) AS qty_sum,sum( fsl_unrelease_pack_ob_lines.box_qty ) AS box_qty_sum,sum( fsl_unrelease_pack_ob_lines.release_qty ) AS release_qty_sum,su...
mysql sql语句性能调优简单实例
09-09
本文将通过一个简单的实例介绍如何进行MySQL SQL语句的性能调优。 首先，我们要开启MySQL的Profiling功能。Profiling是MySQL提供的一种内置工具，可以用来分析SQL语句的执行细节，包括每个步骤的耗时，帮助我们定位...
基于索引的SQL调优
私议
12-27 226
基于索引的SQL调优
MySQL复杂语句如何优化
qq_45283095的博客
12-17 950
1.使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈，就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的，是否有全表扫描等； 2.查询的条件尽量使用索引字段，如某一个表有多个条件，就尽量使用复合索引查询，复合索引使用要注意字段的先后顺序。 3.多表关联尽量用join，减少子查询的使用。表的关联字段如果能用主键就用主键，也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。 4.尽量使用limi
MySQL性能优化 SQL优化方法技巧
05-23
MySQL性能优化 SQL优化方法技巧
sql 调优整理
2.wa
02-20 936
很好，很全面的一篇关于oracle sql调优的文章。文章非常长，大家可一有空就看一点。  1. 选用适合的ORACLE优化器      ORACLE的优化器共有3种:     a.  RULE (基于规则)   b. COST (基于成本)  c. CHOOSE (选择性)      设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RU
一个复杂SQL的调优过程
02-27 344
在oracle10g开发环境，同事找我说有个SQL执行的特别的慢，可否给优化一下。 照例，先在pl/sql 中看执行计划，先看全表扫描的那些表，发现了一个全表扫描的大表，有160万条记录，和其它的表做了merge join。 看这个大表没有索引，给它加上索引，再执行，发现执行计划还是走全表扫描，没有用到新建的索引。 给sql先加上hint，看看建立索引的效果， select /*+ IND
浅谈MySQL SQL优化
weixin_33729196的博客
01-30 229
2019独角兽企业重金招聘Python工程师标准>>> ...
数据库SQL调优的几种方式
热门推荐
lss0555的博客
07-23 15万+
在项目中,SQL的调优对项目的性能来讲至关重要,所有掌握常见的SQL调优方式是必不可少的,下面介绍几种常见的SQL的调优方式,供借鉴. 创建索引 在经常需要进行检索的字段上创建索引，比如要按照表字段username进行检索，那么就应该在姓名字段上创建索引，如果经常要按照员工部门和员工岗位级别进行检索，那么就应该在员工部门和员工岗位级别这两个字段上创建索引。创 建索引给检索带来的性能...
Mysql高级--SQL优化
m0_63526467的博客
04-13 267
文章目录5. SQL优化5.1 大批量插入数据5.2 优化insert语句5.3 优化order by语句5.3.1 环境准备5.3.2 两种排序方式5.3.3 Filesort 的优化5.4 优化group by 语句5.5 优化嵌套查询5.6 优化OR条件5.7 优化分页查询5.7.1 优化思路一5.7.2 优化思路二5.8 使用SQL提示5.8.1 USE INDEX5.8.2 IGNORE INDEX5.8.3 FORCE INDEX 5. SQL优化 5.1 大批量插入数据 环境准备 ： CREA
一次复杂查询引发 的 sql 优化问题
还要我怎样的博客
09-09 464
场景：从blob 中 读取gps信息 数据量达到几百万，需要在一张表中查询出每台车的最新的 gps 信息； 复杂原因：表里边的数据每个 vin 有多条数据 ，time 字段不唯一。 SELECT * FROM car_gps a INNER JOIN (SELECT MAX(id) as id FROM car_gps GROUP BY vin) as b ON a.id = b.i...
多步骤复杂 SQL 优化实例
最新发布
Raqsoft
01-18 812
稍微复杂点的查询需求，写SQL就会是个烧脑的过程，除了证明我们人脑很聪明，逻辑思维能力强之外，剩余的就全是缺点，每个人经常用不同于其他人思路的方式绕行到同一个结果上，个性化这么强的编程方式，导致编写SQL、阅读SQL、调试SQL都很困难，维护成本也大大增高。在程序员编程描述计算这件事上，集算器 SPL 语言通过创新的数学理论模型《离散数据集》，大大改善《关系代数》（SQL背后的数学模型）在描述计算时的困难。简单的说是对有序计算更彻底的集合运算提倡分步等多方面创新，达到。
MYSQL --SQL优化流程
寻找兮悲、
03-31 1104
常去想想以前的东西，怀旧不是用感伤的，是温故知新，加油！！！一、分析一个SQL：1.先看表结构，美化一下，一个好看的结构会让人心旷神怡2.对复杂的SQL进行分析 （1）尽量将复杂的SQL拆成几个简单的查询 （2）尽量使用表连接，减少使用过 in/not in （3）尽量减少子查询的使用或者将其合并出来 （4）尽量将 in/ not in /exists/not ex
MySQL集群高可用与性能调优实践
在配置MySQLCluster时，通常会涉及多台服务器，如本例中的管理节点（mgm，IP：192.168.0.118）、数据节点/SQL节点1（node1，IP：192.168.0.146）和数据节点/SQL节点2（node2，IP：192.168.0.221）。为了通信的准确性...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值