mysql优化

Question

order表记录订单信息，fundflow表记录支付流水信息（一个订单使用多种支付方式的话会生成多条记录，通过pay_type标识）
两个表通过ordersn关联
现在要选择所有通过（微信 现金券）两种支付方式支付的订单数量
SQL如下：

select count(*) from order 
where orldersn in
    (select ordersn from fundflow where pay_type=2 and ordersn in 
        (select ordersn from fundflow where pay_type=1 )
    )

order表大概200万数据，fundflow大概250万数据，sql执行时间大概8秒，如何优化？

Answer 1

将 fundflow 表中的 ordersn, pay_type 设置为索引

create index ix_fundflow_osn on fundflow(ordersn);
create index ix_fundflow_pay_type on fundflow(pay_type);

将 order 表中的 ordersn 设置为索引

create index ix_order_sn on order(ordersn);

尝试使用如下语句查询

select count(*) as c from (
select o.order_id from order as o
right join fundflow as f on o.ordersn=f.ordersn
where f.pay_type in (1, 2)
group by o.order_id
) as t

稍微评价下你所写的 SQL, 如 statuspage 所说 in 会导致全表扫描,
在来看你写的语句

where orldersn in (
  select ordersn from fundflow where pay_type=2 and ordersn in(
    select ordersn from fundflow where pay_type=1 
  )
)

按照你所说的数据量: order表大概200万数据，fundflow大概250万数据
来分析下的话, 那么第一次 in

select ordersn from fundflow where pay_type=2 and ordersn in(
        select ordersn from fundflow where pay_type=1 
      )

先全表扫描 fundflow 中的没一条记录 条件是 pay_type=2 的数据 也就是扫描了 250w 条数据, 然后一个 and 条件 ordersn in (select ordersn from fundflow where pay_type=1) , 也就是说, 扫描这 250w 条数据的没一条时 又扫描了一次 fundflow 表条件是 pay_type=1 的记录, 那么第一次扫描的总行数就是 250w*250w .
然后是第二次 in
也就是从 order 表中查出 所有在 fundflow 条件是 pay_type 是 1, 2 的数据, 也就是上面说的 250w250w 中检索出来的数据, 假如在 fundlow 表中通过这次搜索检索出 pay_type 是 1 2 的数据共有 1000 行, 那么就会全表扫描 order 表 1000200w 行记录.
因此最终得到的结果是通过扫描 250w250w+1000200w 行数据.

Answer 2

1. pay_type、ordersn 加组合索引

2. 把 in 换用 exists，in会导致全表扫描！

-- 以下内容为赠送

1. 若表类型为 myisam 请将其换为 innodb ，因为 myisam 会随着数据的增长会变得越来越慢，具体原因请 google 或百度，这里不展开

2. 优化 mysql 配置，如加大 query buffer 大小

Answer 3

可以先用explain分析一下这条语句的瓶颈在哪

#关闭缓存
FLUSH QUERY CACHE;
SET SESSION query_cache_type = OFF;
#测试
explain select count(*) from order 
where orldersn in
（select ordersn from fundflow where pay_type=2 and ordersn in(
select ordersn from fundflow where pay_type=1 
)
)

根据分析结果再有针对的优化