SQL语句优化

Question

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.rdsrowstatus='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
这个SQL语句第一次执行的时候 需要3-4秒，因为表中的数据量已经比较多了，但是再次执行的时候，就非常快，
这个SQL语句放到程序里执行，总是需要3-4秒的时候才能得出结果，请问如何优化，谢谢！

Answer 1

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.rdsrowstatus='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.paytypeshow index from 连接表名
贴结果
在连接字段上建立索引没有

Answer 2

rdrecords 这个表中的主键 建立了索引

Answer 3

create index xx on rdrecords(paytype,uid,rdsrowstatus)

Answer 4

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,r,ALL,PRIMARY,rdpk,,,,26061,Using where; Using temporary; Using filesort
1,SIMPLE,rs,ref,rdfk,rdfk,512,seawind_sfa.r.rdpk,1,Using where

Answer 5

SQL语句不是标准的
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.rdsrowstatus='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  cCusCode,CCusName,paytype将OR->UNION ALL

Answer 6

create index xx2 on rdrecord(cStatus,uid,cCusCode,bRdFlag)

Answer 7

索引已经全部建立完，依然是：getqueryPickMoney13183017700622011-10-11 10:56:10
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.rdsrowstatus='0'  and r.cCusCode = '10014' and (r.
cVouchType ='xxck' or r.cVouchType ='xscj' or r.cVouchType ='fpck' or r.cVouchTy
pe ='fpckcj')  and r.bRdFlag='1'  group by  rs.paytype
getqueryckMoney13183017745932011-10-11 10:56:14

Answer 8

UNION ALL 语句该如何改，
写成4个SQL语句用UNION ALL链接吗

Answer 9

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.rdsrowstatus='0' and r.cCusCode = '10014'  
and r.cVouchType ='xxck'and r.bRdFlag='1'   
group by cCusCode,CCusName,paytype
union all
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.rdsrowstatus='0' and r.cCusCode = '10014'  
and r.cVouchType ='xscj'and r.bRdFlag='1'   
group by cCusCode,CCusName,paytype
....

Answer 10

贴出你的show index from rdrecords;
show index from rdrecord;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.rdsrowstatus='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

Answer 11

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)

Answer 12

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 13

你的索引在哪儿？！create index xx2 on rdrecord(uid,cCusCode,cStatus,bRdFlag)
先创建复合索引再试。

Answer 14

mysql> 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.rdsrowstatu
s='0'
    -> and r.cCusCode = '10029' and r.cVouchType in('xxck','xscj','fpck','fpckcj
')  and r.bRdFlag='1'
    -> group by  rs.paytype;
+----------+----------------------+---------+---------------------+
| cCusCode | CCusName             | paytype | dVeriDate           |
+----------+----------------------+---------+---------------------+
| 10029    | 南京钢锋实业有限公司 | 1       | 2011-09-29 07:55:36 |
| 10029    | 南京钢锋实业有限公司 | 2       | 2011-09-28 17:20:13 |
+----------+----------------------+---------+---------------------+
2 rows in set (0.02 sec)mysql> create index xx2 on rdrecord(uid,cCusCode,cStatus,bRdFlag);
Query OK, 26181 rows affected (2.33 sec)
Records: 26181  Duplicates: 0  Warnings: 0mysql> create index xx on rdrecords(paytype,uid,rdsrowstatus);
Query OK, 54167 rows affected (5.58 sec)
Records: 54167  Duplicates: 0  Warnings: 0mysql> 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.rdsrowstatu
s='0'
    -> and r.cCusCode = '10029' and r.cVouchType in('xxck','xscj','fpck','fpckcj
')  and r.bRdFlag='1'
    -> group by  rs.paytype;
+----------+----------------------+---------+---------------------+
| cCusCode | CCusName             | paytype | dVeriDate           |
+----------+----------------------+---------+---------------------+
| 10029    | 南京钢锋实业有限公司 | 1       | 2011-09-29 07:55:36 |
| 10029    | 南京钢锋实业有限公司 | 2       | 2011-09-28 17:20:13 |
+----------+----------------------+---------+---------------------+
2 rows in set (1.56 sec)mysql> 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.rdsrowstatu
s='0'
    -> and r.cCusCode = '10014' and r.cVouchType in('xxck','xscj','fpck','fpckcj
')  and r.bRdFlag='1'
    -> group by  rs.paytype;
+----------+------------------------+---------+---------------------+
| cCusCode | CCusName               | paytype | dVeriDate           |
+----------+------------------------+---------+---------------------+
| 10014    | 泰安市贵和物资有限公司 | 1       | 2011-10-06 16:24:48 |
+----------+------------------------+---------+---------------------+
1 row in set (2.59 sec)mysql> 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.rdsrowstatu
s='0'
    -> and r.cCusCode = '10014' and r.cVouchType in('xxck','xscj','fpck','fpckcj
')  and r.bRdFlag='1'
    -> group by  rs.paytype;
+----------+------------------------+---------+---------------------+
| cCusCode | CCusName               | paytype | dVeriDate           |
+----------+------------------------+---------+---------------------+
| 10014    | 泰安市贵和物资有限公司 | 1       | 2011-10-06 16:24:48 |
+----------+------------------------+---------+---------------------+
1 row in set (0.06 sec)

Answer 15

1）  #11楼 上的信息反应 根本没有创建需要的索引。rdrecord(uid,cCusCode,cStatus,bRdFlag) 
2）MYSQL在第一次执行完一个SQL语句后，后进行缓存。这样第二次执行相同语句的时候，则可以直接从缓存中取数据。

Answer 16

有没有办法写出一个更快的SQL语句呢，
查询单个客户需要3-4秒才出结果，多个客户查询，根本没办法接受

Answer 17

再贴一下你的 show index 和 explain 

Answer 18

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      |         |
| rdrecords |          1 | xx           |            1 | paytype      | A
  |           5 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecords |          1 | xx           |            2 | uid          | A
  |           5 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecords |          1 | xx           |            3 | rdsrowstatus | A
  |           5 |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+--------------+--------------+--------------+---------
--+-------------+----------+--------+------+------------+---------+
8 rows in set (0.58 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         |
    26627 |     NULL | NULL   |      | BTREE      |         |
| rdrecord |          0 | rdpk     |            1 | rdpk        | A         |
    26627 |     NULL | NULL   |      | BTREE      |         |
| rdrecord |          1 | cCusCode |            1 | cCusCode    | A         |
        4 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | bRdFlag  |            1 | bRdFlag     | A         |
        2 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | cStatus  |            1 | cStatus     | A         |
        2 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | xx2      |            1 | uid         | A         |
        4 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | xx2      |            2 | cCusCode    | A         |
     2218 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | xx2      |            3 | cStatus     | A         |
     2218 |     NULL | NULL   | YES  | BTREE      |         |
| rdrecord |          1 | xx2      |            4 | bRdFlag     | A         |
     2218 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
9 rows in set (0.27 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,xx2 |
cCusCode | 513     | const              | 1770 | Using where; Using temporary; U
sing filesort |
|  1 | SIMPLE      | rs    | ref  | rdfk,uid,rdsrowstatus                     |
rdfk     | 512     | seawind_sfa.r.rdpk |    1 | Using where
              |
+----+-------------+-------+------+-------------------------------------------+-
---------+---------+--------------------+------+--------------------------------
--------------+
2 rows in set (0.03 sec)mysql>

Answer 19

select 
    cCusCode, CCusName, paytype, iPrice, dVeriDate  
  from 
(select rdfk, paytype, sum(iPrice) iPrice
   from rdrecords 
  where uid='1'
    and rdsrowstatus='0'
  group by rdfk, paytype) a
inner join
(select rdfk, cCusCode, CCusName, dVeriDate 
   from rdrecord 
  where uid='1'
    and cStatus='1'
    and cCusCode='10014'
    and bRdFlag='1'
    and (cVouchType='xxck' or cVouchType='xscj' or cVouchType='fpck' or cVouchType ='fpckcj')
) b
on a.rdfk=b.rdfk
大概是这个思路吧
mysql group by语句，结果列中非聚集列不全部在group by语句中出现时 结果可能不是你想要的