关于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 id desc limit 20;

rowset = sql_query(sql)

组合下一步查询所需id字符串

cardbin_id_str = ',',join(str(row.cardbin_id) for row in rowset) //形式为 "id1, id2, id3...., id20"

第二步查分支表：

cardbin_sql = select id, card_type, cardbin_name, issuer_name from dict_cardbin where id in ($cardbin_id_st)

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:

          ......



--同事写的。