数据量大了以后，默认的 index 方法里，mysql count 很卡，有解决方案吗

YANG001

这家伙很懒，什么也没写~

1周前

多大，多卡

千万级

赞同(0)

钥匙回复YANG001

这家伙很懒，什么也没写~

1周前

大概率是多条件.没优化索引吧...不然不会卡..应该是他自己索引问题..

赞同(0)

mmm回复YANG001

这家伙很懒，什么也没写~

16小时前

千万级，试试关联查询,要这样测试，都加了索引了

language 复制代码

SELECT
  COUNT( * ) AS think_count 
FROM
  (
    SELECT
      count( * ) AS think_count 
    FROM
      `info`
      LEFT JOIN `detail` ON `detail`.`f_info_id` = `info`.`f_info_id`
      LEFT JOIN `site` ON `info`.`f_site_id` = `site`.`f_site_id`
      LEFT JOIN `node` ON `node`.`f_node_id` = `info`.`f_node_id`
      LEFT JOIN `info_tag` ON `info_tag`.`f_info_id` = `info`.`f_info_id`
      LEFT JOIN `tag` ON `tag`.`f_tag_id` = `info_tag`.`f_tag_id`
      LEFT JOIN `info_clob` ON `info_clob`.`f_info_id` = `info`.`f_info_id` 
    WHERE
      `site`.`f_site_id` NOT IN ( '50', '51', '53' ) 
      AND `info`.`f_status` = 'A' 
      AND `info_clob`.`f_key` = 'text' 
    GROUP BY
      `info`.`f_info_id` 
  ) `_group_count_`

赞同(0)

YANG001回复mmm

这家伙很懒，什么也没写~

16小时前

数据量大+多个join，还是自己手搓index方法吧，你想咋写就咋写，原生sql都行

赞同(0)

萧月生

这家伙很懒，什么也没写~

1周前

count查询如果不需要事务myslam相对快一点

赞同(0)

fg188

这家伙很懒，什么也没写~

1天前

那你把数据放到内存里吧

赞同(0)

mmm

这家伙很懒，什么也没写~

16小时前

千万级，试试关联查询,要这样测试，都加了索引了

language 复制代码

SELECT
  COUNT( * ) AS think_count 
FROM
  (
    SELECT
      count( * ) AS think_count 
    FROM
      `info`
      LEFT JOIN `detail` ON `detail`.`f_info_id` = `info`.`f_info_id`
      LEFT JOIN `site` ON `info`.`f_site_id` = `site`.`f_site_id`
      LEFT JOIN `node` ON `node`.`f_node_id` = `info`.`f_node_id`
      LEFT JOIN `info_tag` ON `info_tag`.`f_info_id` = `info`.`f_info_id`
      LEFT JOIN `tag` ON `tag`.`f_tag_id` = `info_tag`.`f_tag_id`
      LEFT JOIN `info_clob` ON `info_clob`.`f_info_id` = `info`.`f_info_id` 
    WHERE
      `site`.`f_site_id` NOT IN ( '50', '51', '53' ) 
      AND `info`.`f_status` = 'A' 
      AND `info_clob`.`f_key` = 'text' 
    GROUP BY
      `info`.`f_info_id` 
  ) `_group_count_`