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

Question

• 现有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

Answer 1

你这个有必要吗 - -