一个复杂的sql语句,在数据多的时候可能会卡住吗?

Question

我有个SQL语句 是多个条件查询重复的语句,语句如下:
    select
        fubook0_.id as id229_,
        fubook0_1_.productName as productN2_229_,
        fubook0_1_.description as descript3_229_,
        fubook0_1_.addTime as addTime229_,
        fubook0_1_.fixedPrice as fixedPrice229_,
        fubook0_1_.keyWords as keyWords229_,
        fubook0_1_.productPic as productPic229_,
        fubook0_1_.productPicLittle as productP8_229_,
        fubook0_1_.hasDelete as hasDelete229_,
        fubook0_.author as author231_,
        fubook0_.publishing as publishing231_,
        fubook0_.publishTime as publishT4_231_,
        fubook0_.wordNumber as wordNumber231_,
        fubook0_.witchEdition as witchEdi6_231_,
        fubook0_.totalPage as totalPage231_,
        fubook0_.printTime as printTime231_,
        fubook0_.printNumber as printNum9_231_,
        fubook0_.isbn as isbn231_,
        fubook0_.authorSummary as authorS11_231_,
        fubook0_.catalogue as catalogue231_ 
    from
        f_fuBook fubook0_ 
    inner join
        f_fuProduct fubook0_1_ 
            on fubook0_.id=fubook0_1_.id 
    where
        (
            (
                fubook0_.author , fubook0_1_.productName , fubook0_.publishing
            ) in (
                select
                    fubook1_.author,
                    fubook1_1_.productName,
                    fubook1_.publishing 
                from
                    f_fuBook fubook1_ 
                inner join
                    f_fuProduct fubook1_1_ 
                        on fubook1_.id=fubook1_1_.id 
                group by
                    fubook1_.author ,
                    fubook1_1_.productName ,
                    fubook1_.publishing 
                having
                    count(*)>1
            )
        ) 
        and (
            fubook0_.id not in  (
                select
                    min(fubook2_.id) 
                from
                    f_fuBook fubook2_ 
                inner join
                    f_fuProduct fubook2_1_ 
                        on fubook2_.id=fubook2_1_.id 
                group by
                    fubook2_.author ,
                    fubook2_1_.productName ,
                    fubook2_.publishing 
                having
                    count(*)>1
            )
        )
如果数据少的话,能查询出来...但数据多达10000多条的时候...这sql语句执行不了...
大家能提点意见吗?  我该怎么解决...?

Answer 1

fubook0_.author , fubook0_1_.productName , fubook0_.publishing
            ) in (
                select
                    fubook1_.author,
                    fubook1_1_.productName,
                    fubook1_.publishing 
.....mysql不支持 你这个写法吧简化来说 就是
select * from tb where col1 ,col2 in(select col1,col2 from /..)
这样的语法不支持~

Answer 2

我那些语句是hibernate自动生成出来的...但程序要求找出重复的数据并把多余的数据给删除掉...

Answer 3

总之来说，这个查询有些看得头晕，不容易维护。
你可以显示一下它的执行计划。explain select ...

Answer 4

贴出你的
explain select ...

Answer 5

+----+--------------------+------------+--------+---------------+---------+-----
----+--------------------+------+---------------------------------+
| id | select_type        | table      | type   | possible_keys | key     | key_
len | ref                | rows | Extra                           |
+----+--------------------+------------+--------+---------------+---------+-----
----+--------------------+------+---------------------------------+
|  1 | PRIMARY            | fubook0_   | ALL    | PRIMARY       | NULL    | NULL
    | NULL               |   10 | Using where                     |
|  1 | PRIMARY            | fubook0_1_ | eq_ref | PRIMARY       | PRIMARY | 4
    | fuwang.fubook0_.id |    1 | Using where                     |
|  3 | DEPENDENT SUBQUERY | fubook2_   | ALL    | PRIMARY       | NULL    | NULL
    | NULL               |   10 | Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | fubook2_1_ | eq_ref | PRIMARY       | PRIMARY | 4
    | fuwang.fubook2_.id |    1 |                                 |
|  2 | DEPENDENT SUBQUERY | fubook1_   | ALL    | PRIMARY       | NULL    | NULL
    | NULL               |   10 | Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | fubook1_1_ | eq_ref | PRIMARY       | PRIMARY | 4
    | fuwang.fubook1_.id |    1 |                                 |
+----+--------------------+------------+--------+---------------+---------+-----
----+--------------------+------+---------------------------------+这个是执行计划么....

Answer 6

是
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

Answer 7

你现在表中才不过10行记录，速度应该不会慢啊。
注意对红包部分的优化处理。+----+--------------------+------------+--------+---------------+---------+---------+--------------------+------+---------------------------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref                | rows | Extra                           |
+----+--------------------+------------+--------+---------------+---------+---------+--------------------+------+---------------------------------+
|  1 | PRIMARY            | fubook0_   | ALL    | PRIMARY       | NULL    | NULL    | NULL               |   10 | Using where                     |
|  1 | PRIMARY            | fubook0_1_ | eq_ref | PRIMARY       | PRIMARY | 4       | fuwang.fubook0_.id |    1 | Using where                     |
|  3 | DEPENDENT SUBQUERY | fubook2_   | ALL    | PRIMARY       | NULL    | NULL    | NULL               |   10 | Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | fubook2_1_ | eq_ref | PRIMARY       | PRIMARY | 4       | fuwang.fubook2_.id |    1 |                                 |
|  2 | DEPENDENT SUBQUERY | fubook1_   | ALL    | PRIMARY       | NULL    | NULL    | NULL               |   10 | Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | fubook1_1_ | eq_ref | PRIMARY       | PRIMARY | 4       | fuwang.fubook1_.id |    1 |                                 |
+----+--------------------+------------+--------+---------------+---------+---------+--------------------+------+---------------------------------+

Answer 8

哦  那是我把表删了  现在只有10条记录 
如果是很多 比如有 一万多条的时候 sql语句都执行不了...

Answer 9

Using temporary; Using filesort 
这句话又是什么意思...

Answer 10

这个是查询一万多条记录的执行计划....
就是查这么多的时候就会卡住...
+----+--------------------+------------+--------+---------------+---------+-----
----+----------------------+-------+---------------------------------+
| id | select_type        | table      | type   | possible_keys | key     | key_
len | ref                  | rows  | Extra                           |
+----+--------------------+------------+--------+---------------+---------+-----
----+----------------------+-------+---------------------------------+
|  1 | PRIMARY            | fubook0_1_ | ALL    | PRIMARY       | NULL    | NULL
    | NULL                 | 13601 | Using where                     |
|  1 | PRIMARY            | fubook0_   | eq_ref | PRIMARY       | PRIMARY | 4
    | fuwang.fubook0_1_.id |     1 | Using where                     |
|  3 | DEPENDENT SUBQUERY | fubook2_1_ | ALL    | PRIMARY       | NULL    | NULL
    | NULL                 | 13601 | Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | fubook2_   | eq_ref | PRIMARY       | PRIMARY | 4
    | fuwang.fubook2_1_.id |     1 |                                 |
|  2 | DEPENDENT SUBQUERY | fubook1_1_ | ALL    | PRIMARY       | NULL    | NULL
    | NULL                 | 13601 | Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | fubook1_   | eq_ref | PRIMARY       | PRIMARY | 4
    | fuwang.fubook1_1_.id |     1 |                                 |
+----+--------------------+------------+--------+---------------+---------+-----
----+----------------------+-------+---------------------------------+
6 rows in set (0.00 sec)