2022-09-14 mysql列存储引擎-POC-Q2-问题分析

摘要:

mysql存储引擎-POC-Q2-问题分析

Q2的SQL:

SELECT
sale.usid AS usid,
cus.corpname AS corpname,
sale.iscenicid AS iscenicid,
salde.itickettypeid AS itickettypeid,
price.icrowdkindid AS icrowdkindid,
salde.mactualsaleprice AS mactualsaleprice,
sum(salde.iamount) AS numb,
sum(salde.meventmoney) AS mont,
coalesce(sum(salde.mderatemoney),
0) AS mderatemoney,
coalesce(sum(salde.ideratenums),
0) AS ideratenums,
coalesce(sum(salde.mhandcharge),
0) AS mhandcharge,
saletype.isettlementid AS isettlementid,
sale.bysalesvouchertype AS bysalesvouchertype
FROM
STSSALESVOUCHERTAB sale,
STSSALESVOUCHERDETAILSTAB salde,
(
SELECT FIND_TOP_USID(C.USID) AS SUSID,
C2.CORPNAME AS SCORPNAME,
C2.BNAME,
C.USID,
C.CORPNAME,
C.IBUSINESSID,
C.TTLB
FROM
CUSTOM C
LEFT JOIN CUSTOM C2 ON
C2.USID = C.SUSID
WHERE
C.LGTP = '02'
AND C.USTP = '01'
AND C.IBUSINESSID = 2) cus,
EDMTICKETTYPETAB ti,
EDMCROWDKINDPRICETAB price,
STSSALESSETTLEMENTTAB saletype
WHERE
substr(sale.dtmakedate,
1,
10) >= '2022-08-18'
AND substr(sale.dtmakedate,
1,
10) <= '2022-08-18'
AND sale.isalesvoucherid = saletype.isalesvoucherid
AND sale.iticketstationid = saletype.iticketstationid
AND price.icrowdkindpriceid = salde.icrowdkindpriceid
AND sale.isalesvoucherid = salde.isalesvoucherid
AND sale.iticketstationid = salde.iticketstationid
AND cus.usid = sale.usid
AND salde.itickettypeid = ti.itickettypeid
AND sale.iscenicid IN (1)
AND cus.ibusinessid <> 1
GROUP BY
sale.usid,
cus.corpname,
saletype.isettlementid,
salde.mactualsaleprice,
sale.iscenicid,
salde.itickettypeid,
price.icrowdkindid,
sale.bysalesvouchertype
UNION ALL SELECT
sale.usid AS usid,
cus.corpname AS corpname,
sale.iscenicid AS iscenicid,
salde.itickettypeid AS itickettypeid,
price.icrowdkindid AS icrowdkindid,
salde.mactualsaleprice AS mactualsaleprice,
sum(salde.iamount) AS numb,
sum(salde.meventmoney) AS mont,
coalesce(sum(salde.mderatemoney),
0) AS mderatemoney,
coalesce(sum(salde.ideratenums),
0) AS ideratenums,
coalesce(sum(salde.mhandcharge),
0) AS mhandcharge,
saletype.isettlementid AS isettlementid,
sale.bysalesvouchertype AS bysalesvouchertype
FROM
STSSALESVOUCHERTABLIST sale,
STSSALESVOUCHERDETAILSTABLIST salde,
(
SELECT USID,
CORPNAME,
IBUSINESSID,
TTLB,
FIND_TOP_USID(USID) AS SUSID
FROM
CUSTOM
WHERE
LGTP = '02'
AND USTP = '01') cus,
EDMTICKETTYPETAB ti,
EDMCROWDKINDPRICETAB price,
STSSALESSETTLEMENTTABLIST saletype
WHERE
substr(sale.dtmakedate,
1,
10) >= '2022-08-18'
AND substr(sale.dtmakedate,
1,
10) <= '2022-08-18'
AND sale.isalesvoucherid = saletype.isalesvoucherid
AND sale.iticketstationid = saletype.iticketstationid
AND price.icrowdkindpriceid = salde.icrowdkindpriceid
AND sale.isalesvoucherid = salde.isalesvoucherid
AND sale.iticketstationid = salde.iticketstationid
AND cus.usid = sale.usid
AND salde.itickettypeid = ti.itickettypeid
AND sale.iscenicid IN (1)
AND cus.ibusinessid <> 1
GROUP BY
sale.usid,
cus.susid,
saletype.isettlementid,
salde.mactualsaleprice,
sale.iscenicid,
salde.itickettypeid,
price.icrowdkindid,
sale.bysalesvouchertype;

SQL分析:

语句可以分为两个select, 由union all将两个select的结果合并起来

上半部select

SELECT
sale.usid AS usid,
cus.corpname AS corpname,
sale.iscenicid AS iscenicid,
salde.itickettypeid AS itickettypeid,
price.icrowdkindid AS icrowdkindid,
salde.mactualsaleprice AS mactualsaleprice,
sum(salde.iamount) AS numb,
sum(salde.meventmoney) AS mont,
coalesce(sum(salde.mderatemoney),
0) AS mderatemoney,
coalesce(sum(salde.ideratenums),
0) AS ideratenums,
coalesce(sum(salde.mhandcharge),
0) AS mhandcharge,
saletype.isettlementid AS isettlementid,
sale.bysalesvouchertype AS bysalesvouchertype
FROM
STSSALESVOUCHERTAB sale,
STSSALESVOUCHERDETAILSTAB salde,
(
SELECT FIND_TOP_USID(C.USID) AS SUSID,
C2.CORPNAME AS SCORPNAME,
C2.BNAME,
C.USID,
C.CORPNAME,
C.IBUSINESSID,
C.TTLB
FROM
CUSTOM C
LEFT JOIN CUSTOM C2 ON
C2.USID = C.SUSID
WHERE
C.LGTP = '02'
AND C.USTP = '01'
AND C.IBUSINESSID = 2) cus,
EDMTICKETTYPETAB ti,
EDMCROWDKINDPRICETAB price,
STSSALESSETTLEMENTTAB saletype
WHERE
substr(sale.dtmakedate,
1,
10) >= '2022-08-18'
AND substr(sale.dtmakedate,
1,
10) <= '2022-08-18'
AND sale.isalesvoucherid = saletype.isalesvoucherid
AND sale.iticketstationid = saletype.iticketstationid
AND price.icrowdkindpriceid = salde.icrowdkindpriceid
AND sale.isalesvoucherid = salde.isalesvoucherid
AND sale.iticketstationid = salde.iticketstationid
AND cus.usid = sale.usid
AND salde.itickettypeid = ti.itickettypeid
AND sale.iscenicid IN (1)
AND cus.ibusinessid <> 1
GROUP BY
sale.usid,
cus.corpname,
saletype.isettlementid,
salde.mactualsaleprice,
sale.iscenicid,
salde.itickettypeid,
price.icrowdkindid,
sale.bysalesvouchertype;

下半部select

SELECT
sale.usid AS usid,
cus.corpname AS corpname,
sale.iscenicid AS iscenicid,
salde.itickettypeid AS itickettypeid,
price.icrowdkindid AS icrowdkindid,
salde.mactualsaleprice AS mactualsaleprice,
sum(salde.iamount) AS numb,
sum(salde.meventmoney) AS mont,
coalesce(sum(salde.mderatemoney),
0) AS mderatemoney,
coalesce(sum(salde.ideratenums),
0) AS ideratenums,
coalesce(sum(salde.mhandcharge),
0) AS mhandcharge,
saletype.isettlementid AS isettlementid,
sale.bysalesvouchertype AS bysalesvouchertype
FROM
STSSALESVOUCHERTABLIST sale,
STSSALESVOUCHERDETAILSTABLIST salde,
(
SELECT USID,
CORPNAME,
IBUSINESSID,
TTLB,
FIND_TOP_USID(USID) AS SUSID
FROM
CUSTOM
WHERE
LGTP = '02'
AND USTP = '01') cus,
EDMTICKETTYPETAB ti,
EDMCROWDKINDPRICETAB price,
STSSALESSETTLEMENTTABLIST saletype
WHERE
substr(sale.dtmakedate,
1,
10) >= '2022-08-18'
AND substr(sale.dtmakedate,
1,
10) <= '2022-08-18'
AND sale.isalesvoucherid = saletype.isalesvoucherid
AND sale.iticketstationid = saletype.iticketstationid
AND price.icrowdkindpriceid = salde.icrowdkindpriceid
AND sale.isalesvoucherid = salde.isalesvoucherid
AND sale.iticketstationid = salde.iticketstationid
AND cus.usid = sale.usid
AND salde.itickettypeid = ti.itickettypeid
AND sale.iscenicid IN (1)
AND cus.ibusinessid <> 1
GROUP BY
sale.usid,
cus.susid,
saletype.isettlementid,
salde.mactualsaleprice,
sale.iscenicid,
salde.itickettypeid,
price.icrowdkindid,
sale.bysalesvouchertype;

存储引擎处理:

一. 保留索引

  1. 上半部select查询正确
  2. 下半部select查询正确
  3. 但是整个SQL,出现语法错误


二. 删除索引

  1. 整个SQL执行时没有出现语法错误
  2. 但是上半部select查询时,icrowdkindid 列为NULL,导致结果比innodb少一行
  3. 经过union all联合下半部select后, 在上半部有结果集的情况下,整体返回empty