mysql联表后count查询特别慢怎么优化

秦羽
  • 333
  • 现有X,Y表,和一个视图view1
  • X表数据有30万条,Y表有1000条
    视图view1是这样的

    SELECT
      `a`.`id` AS `id`,
      `a`.`seq_no` AS `seq_no`,
      `a`.`verify_user_id` AS `verify_user_id`,
      `a`.`state` AS `state`
    FROM
      (
          (
              SELECT
                  `X`.`id` AS `id`,
                  `X`.`seq_no` AS `seq_no`,
                  `X`.`verify_user_id` AS `verify_user_id`,
                  `X`.`state` AS `state`
              FROM
                  `X`
              WHERE
                  (
                      `X`.`state` = '2'
                  )
          ) `a`
          LEFT JOIN (
              SELECT
                  `Y`.`user_id` AS `admin_user_id`
              FROM
                  `Y`
          ) `b` ON (
              (
                  `a`.`verify_user_id` = `b`.`admin_user_id`
              )
          )
      )
  • 测试1

    select count(*) from X;
  • 测试2

    select count(*) from view1;

    测试1查询用时0.5秒,测试2查询用时2.5秒。

其中A.verify_user_id,A.state,B.admin_user_id都加过索引了(由于业务原因必须要联表查然后count统计)

执行explain

  • 测试1
1    SIMPLE    X        ref    state,plate_type_state,verify_user_id_state    state    5    const    258173    100    Using where; Using index
  • 测试2
1    SIMPLE    X        ref    state    state    5    const    258168    100    Using index condition
1    SIMPLE    Y        ref    user_id_index    user_id_index    5    db.X.verify_user_id    1    100    Using index

问题

  • 为什么两个差距会这么大?
  • 测试2必须用视图联表查询的情况该怎么优化查询速度呢?

补充下,有人说是视图sql写麻烦了,我又新测试的,还是一样的

更新成直接联表再次测试

select count(*) from X A left join Y B on A.verify_user_id=B.user_id where A.state='2'

上边sql用时2.4秒(如果不加where条件只需要1秒,但是还是比不上直接查A表用时0.5秒)

explain select count(*) from X A left join Y B on A.verify_user_id=B.user_id where A.state='2'

结果

1    SIMPLE    A        ref    state    state    5    const    258460    100    Using index condition
1    SIMPLE    B        ref    user_id_index    user_id_index    5    db.A.verify_user_id    1    100    Using index

补充

再次测试,用笛卡尔积查询统计反而特别快,神奇

select count(*) from X A, Y B where A.verify_user_id=B.user_id and A.state='2'

上边sql查询用时0.6秒

分析sql

1    SIMPLE    B        index    user_id_index    user_id_index    5        1833    100    Using where; Using index
1    SIMPLE    A        ref    verify_user_id,state,verify_user_id_state    verify_user_id_state    10    db.B.user_id,const    264    100    Using where; Using index
回复
阅读 836
宣传栏