主表3W，子表5W条，查询效率太低了

Question

select cCusCode,CCusName,paytype,dVeriDate 
from rdrecords as rs left join rdrecord as r on rs.rdfk = r.rdpk  
where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0'  and r.cCusCode = '10077' 
and r.cVouchType in('xxck','xscj','fpck','fpckcj')  and r.bRdFlag='1'   索引已经用了 咋整 还是这么慢

Answer 1

贴出show index, explan 以供分析。

Answer 2

mysql> show index from rdrecords;
+-----------+------------+--------------+--------------+--------------+---------
--+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name  | Collatio
n | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------+--------------+--------------+---------
--+-------------+----------+--------+------+------------+---------+
| rdrecords |          0 | PRIMARY      |            1 | rdspk        | A
  |       54140 |     NULL | NULL   |      | BTREE      |         |
| rdrecords |          1 | rdfk         |            1 | rdfk         | A
  |       54140 |     NULL | NULL   |      | BTREE      |         |
| rdrecords |          1 | paytype      |            1 | paytype      | A
  |           3 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecords |          1 | uid          |            1 | uid          | A
  |           3 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecords |          1 | rdsrowstatus |            1 | rdsrowstatus | A
  |           3 |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+--------------+--------------+--------------+---------
--+-------------+----------+--------+------+------------+---------+
5 rows in set (0.20 sec)mysql> show index from rdrecord;
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Ca
rdinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| rdrecord |          0 | PRIMARY  |            1 | rdpk        | A         |
    26061 |     NULL | NULL   |      | BTREE      |         |
| rdrecord |          0 | rdpk     |            1 | rdpk        | A         |
    26061 |     NULL | NULL   |      | BTREE      |         |
| rdrecord |          1 | cCusCode |            1 | cCusCode    | A         |
      606 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | bRdFlag  |            1 | bRdFlag     | A         |
        1 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | cStatus  |            1 | cStatus     | A         |
        1 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
5 rows in set (0.36 sec)mysql> explain select cCusCode,CCusName,paytype,sum(iPrice) iPrice,dVeriDate
    -> from rdrecords as rs
    -> left join rdrecord as r on rs.rdfk = r.rdpk
    -> where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatu
s='0' and r.cCusCode = '10014'
    -> and (r.cVouchType ='xxck' or r.cVouchType ='xscj' or r.cVouchType ='fpck'
 or r.cVouchType ='fpckcj') and r.bRdFlag='1'
    -> group by rs.paytype;
+----+-------------+-------+------+---------------------------------------+-----
-----+---------+--------------------+------+------------------------------------
----------+
| id | select_type | table | type | possible_keys                         | key
     | key_len | ref                | rows | Extra
          |
+----+-------------+-------+------+---------------------------------------+-----
-----+---------+--------------------+------+------------------------------------
----------+
|  1 | SIMPLE      | r     | ref  | PRIMARY,rdpk,cCusCode,bRdFlag,cStatus | cCus
Code | 513     | const              | 1770 | Using where; Using temporary; Using
 filesort |
|  1 | SIMPLE      | rs    | ref  | rdfk,uid,rdsrowstatus                 | rdfk
     | 512     | seawind_sfa.r.rdpk |    1 | Using where
          |
+----+-------------+-------+------+---------------------------------------+-----
-----+---------+--------------------+------+------------------------------------
----------+
2 rows in set (0.09 sec)

Answer 3

create index xxxx1 on rdrecord(cCusCode,uid,cVouchType,bRdFlag,cStatus);

Answer 4

 【2011-10-14 09:08:33:500】
select paytype,sum(iPrice) iPrice from rdrecords as rs left join rdrecord as r on rs.rdfk = r.rdpk  where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0'  and r.cCusCode = '10014' and r.cVouchType in('xxck','xscj','fpck','fpckcj')  and r.bRdFlag='1'   group by paytype
  【2011-10-14 09:08:39:093】

Answer 5

mysql> create index xxxu1 on rdrecord(cCusCode,uid,cVouchType,bRdFlag,cStatus);
ERROR 1071 (42000): Specified key was too long; max key length is 1024 bytes
mysql>

Answer 6

select cCusCode,CCusName,paytype,dVeriDate
from
(
select rdpk,cCusCode,CCusName,dVeriDate
from rdrecord r
where r.uid = '1'
  and r.cStatus = '1'
  and r.cCusCode = '10077' 
  and r.bRdFlag='1'
  and r.cVouchType='xxck'
union all
select rdpk,cCusCode,CCusName,dVeriDate
from rdrecord r
where r.uid = '1'
  and r.cStatus = '1'
  and r.cCusCode = '10077' 
  and r.bRdFlag='1'
  and r.cVouchType='xscj'
union all
select rdpk,cCusCode,CCusName,dVeriDate
from rdrecord r
where r.uid = '1'
  and r.cStatus = '1'
  and r.cCusCode = '10077' 
  and r.bRdFlag='1'
  and r.cVouchType='fpck'
union all
select rdpk,cCusCode,CCusName,dVeriDate
from rdrecord r
where r.uid = '1'
  and r.cStatus = '1'
  and r.cCusCode = '10077' 
  and r.bRdFlag='1'
  and r.cVouchType='fpckcj'
) t1 
inner join
(
select rdpk,paytype
from rdrecords rs
where rs.uid = '1' 
  and rs.rdsrowstatus='0'
) t2
on t1.rdpk=t2.rdpk

Answer 7

汗~
42 rows in set (30.41 sec)

Answer 8

1 【2011-10-14 11:15:02:500】
select paytype,sum(iPrice) iPrice from ( select rdfk,paytype,iPrice,rdsrowstatus,uid from rdrecords   where rdsrowstatus='0' and (cInvCode like '10%' or cInvCode like '40%')  ) as rs left join ( select rdpk,cCusCode,cVouchType,bRdFlag,cStatus,uid from rdrecord where cCusCode = '10077' and cVouchType in('xxck','xscj','fpck','fpckcj') and bRdFlag='1' and cStatus = '1' ) as r on rs.rdfk = r.rdpk  where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0'  and r.cCusCode = '10077' and r.cVouchType in('xxck','xscj','fpck','fpckcj')  and r.bRdFlag='1'   group by paytype
 2 【2011-10-14 11:15:12:718】执行2个单语句发现时间用的很少，和联合查询，就非常慢了  真不知道如何优化了

Answer 9

加上索引呢：
create index idx_cVouchType on rdrecord(cVouchType)

Answer 10

数据量大时尽量不要使用表连接查询数据，可以将少量信息加入内存，配合适量的索引，用explain函数来测试你的sql性能！