代码变量拆分大SQL

徐冬丽

　　关于xxx的慢查改进建议：
　　1、注意不要使用 *，查表时应具体指明列名
　　2、避免使用子查询
　　3、联表/分步查询时，先将范围缩小
　　4、可以在应用内存中完成的运算，不要交给数据库
　　这是两个比较急需解决的，后续下次再沟通
　　一、
　　select
　　t.*,
　　CASE c.card_type
　　WHEN 'debit' THEN 'xxx'
　　WHEN 'prepaid' THEN 'xxx'
　　WHEN 'credit' THEN 'xxx'
　　WHEN 'semiCredit' THEN 'xxx' END 'card_type',
　　c.cardbin_name, c.issuer_name, a.name acquirer_name, m.merchant_name,am.merchant_name
　　from ((((
　　trans_history t
　　join dict_cardbin c on t.cardbin_id = c.id )
　　left join cm_merchant m on t.merchant_id=m.id)
　　left join acquirer a on t.acquirer_id = a.id )
　　left join acq_merchant am on t.acq_merchant_id = am.id )
　　left join agency ag on m.agency_id = ag.id
　　order by t.id desc
　　limit 0, 20;
　　trans_history这个表非常大，有5G, 整表扫描代价也是非常大的。可否根据这个表的访问需求，将老数据分出来一部分。
　　其他几个表比较小。但是，这个查询只需要trans_history中最新的20条id，而联表动作会将整表连接，再排序。所需的时间代价是整扫trans_history的基础上再翻数倍，类似于O(M*N*T...)
　　在dict_cardbin、cm_merchant、acquirer、acq_merchant、agency的id均为主键的情况下，我们每个表只检索20条记录是非常快的。相当于O(M+N+T...)
　　建议改为:
　　第一步查主表，20条记录

　　sql = select id, col1, col2,..., cardbin_id, merchant_id, acquirer_id, acquirer_id, acq_merchant_id from trans_history order by>　　rowset = sql_query(sql)
　　组合下一步查询所需id字符串

　　cardbin_id_str = ',',join(str(row.cardbin_id) for row in rowset) //形式为 "id1,>　　第二步查分支表：

　　cardbin_sql = select>　　cardbin_rowset = sql_query(cardbin_sql)
　　依次类推
　　card_type比较少，直接放在程序内存
　　card_type_map = {"debit":"借记卡", ...}
　　最终结果：
　　for row in rowset:
　　print row.id, row.col1, row.col2..., card_type_map[cardbin_rowset[id].card_type], cardbin_rowset[id].cardbin_name, cardbin_rowset[id].issuer_name, acquirer_rowset[id].name, cm_merchant_rowset[id].merchant_name , acq_merchant_rowset[id].merchant_name
　　二、
　　SELECT temp.id, temp.acq_merchant_id, temp.enabled, temp.batch_no, temp.trace_no,temp.terminal_no,temp.merchant_no
　　from (
　　SELECT c.*,b.merchant_no,ks.key_alias
　　from acq_merchant_terminal c
　　LEFT JOIN acq_merchant b on b.id=c.acq_merchant_id
　　JOIN acquirer a on a.id=b.acquirer_id
　　LEFT JOIN key_store ks on ks.key_alias = CONCAT(%s,c.terminal_no,%s) OR ks.key_alias = CONCAT(%s,c.terminal_no,%s)
　　WHERE a.enabled=%n and b.enabled=%n and and a.`code`= %s) temp
　　WHERE temp.key_alias is NULL;
　　这几个表数据量都不算太大，可以联表
　　我们在联表/分步时，应注意先使用限制条件，将检索范围缩小，因此，将限制主表行数的条件提前。
　　select c.id, c.acq_merchant_id, c.enabled , c.batch_no c.trace_no, c.terminal_no, b.merchant_no, b.key_alias //注意不要使用*，明确列出列名
　　from acq_merchant_terminal c
　　left join acq_merchant b on b.id=c.acq_merchant_id
　　join acquirer a on a.id=b.acquirer_id
　　left join key_store ks on ks.key_alias in (concat(%s,c.terminal_no,%s)) //为什么原sql写了两遍这个条件？注意应尽量避免使用OR连接检索条件，如果有多个值，使用 in (值列表)
　　where c.enabled=%n and a.enabled=%n and a.code= %s and b.enabled=%n
　　mysql> select count(c.id) from acq_merchant_terminal c left join acq_merchant b on b.id=c.acq_merchant_id  join acquirer a on a.id=b.acquirer_id left join key_store ks on ks.key_alias in ("acq.ryxpay.88779g648160001.79g60001.zmk")  where c.enabled=1 and a.enabled=1 and a.code= "ryxpay" and b.enabled=1;
　　+-------------+
　　| count(c.id) |
　　+-------------+
　　|       90617 |
　　+-------------+
　　1 row in set (0.33 sec)
　　外层检索逻辑key_alias是否为null，可用程序完成：
　　for row in rowset:
　　if row.key_alias != NULL:
　　......
　　--同事写的。