时间戳

首页 > 解决方案 > MyISAM 表中的 count(*) 太慢了

mysql - MyISAM 表中的 count(*) 太慢了

问题描述

我有一个 5GB 的大数据库，它的大多数数据库引擎都是 MyISAM，服务器 RAM 约为 20GB，但 query_cache 和其他配置设置为默认值（16MB）。MySQL 版本是 5.7.27，我有一个 3.5GB 的旧数据库备份，当我在本地运行查询时它比生产快得多。

FLUSH QUERY CACHE;
RESET QUERY CACHE;
SELECT SQL_NO_CACHE 
    count(*)
FROM
    company_reports rep
    LEFT JOIN system_users usr ON rep.user_id = usr.id
    LEFT JOIN company_rep_subjects sbj ON rep.subject_id = sbj.id
    INNER JOIN company_partners cus ON rep.partner_id = cus.id
    LEFT JOIN project_con_messages mes ON rep.message_id = mes.id
    LEFT JOIN company_par_user_settings pus ON cus.id = pus.partner_id 
    AND 1 = pus.user_id 
WHERE
    1=1 -- or other where and will generate by ACL and advanced search

我禁用了缓存

 query_cache_limit = 0
 query_cache_size = 0

我不知道为什么这个查询在生产中需要大约 13 秒才能获得计数。有什么优化建议吗？我的 RAM 是空闲的，所以我分配给 query_cache 的缓存设置数量比较好。

重要提示：迁移到 InnoDB 是我的想法，但我不知道为什么这个查询在我的本地速度如此之快，是否有任何锁定系统可能阻止 MySQL 在生产中更快地计算记录？即使我们的系统没有运行并且处于工作时间之外，它也进行了测试。

我的本地系统不是 MYSQL 5.7.27，我使用的是 10.1.38-MariaDB。

这是我当地的解释

1   SIMPLE  cus index   PRIMARY PRIMARY 3       62293   Using index
1   SIMPLE  rep ref partner_id  partner_id  3   MYDBNAME.cus.id 13

生产中：

“为什么他们不一样！” 本地是 Mariadb 10（2 行），生产是 MYSQL 5.7.27（6 行）

在此处输入图像描述

编辑：在将本地索引与生产同步后，现在本地速度很慢！这很好，我发现了为什么生产速度很慢以及哪些索引丢失了。我应该在生产中添加两个索引，阅读评论。

本地新解释：

1   SIMPLE  cus index   PRIMARY companyname 362     63490   Using index
1   SIMPLE  rep ref partner_id  partner_id  3   ebrahim.cus.id  5   
1   SIMPLE  pus ref partner_id_2,user_id    partner_id_2    3   ebrahim.cus.id  2   Using where

我不知道为什么我的本地使用公司名称作为索引，如果我强制它使用 PRIMARY，速度没有变化，但不知道为什么当前的解释是不同的，尽管索引是同步的。

编辑：在我将 buffer_key_size 增加到 4G 并添加 partner_id 和 user_id 作为复合索引和唯一并在所有查询表上运行 ANALYZE 后，速度增加到 6.7 秒。

在此处输入图像描述

join_buffer_size=>262144
key_buffer_size=>4294967296
myisam_sort_buffer_size=>8388608
net_buffer_length=>16384
read_buffer_size=>131072
preload_buffer_size=>32768

query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 16777216

显示变量，如 '%buffer%'

bulk_insert_buffer_size
8388608
innodb_buffer_pool_chunk_size
134217728
innodb_buffer_pool_dump_at_shutdown
ON
innodb_buffer_pool_dump_now
OFF
innodb_buffer_pool_dump_pct
25
innodb_buffer_pool_filename
ib_buffer_pool
innodb_buffer_pool_instances
1
innodb_buffer_pool_load_abort
OFF
innodb_buffer_pool_load_at_startup
ON
innodb_buffer_pool_load_now
OFF
innodb_buffer_pool_size
134217728
innodb_change_buffer_max_size
25
innodb_change_buffering
all
innodb_log_buffer_size
16777216
innodb_sort_buffer_size
1048576
join_buffer_size
262144
key_buffer_size
4294967296
myisam_sort_buffer_size
8388608
net_buffer_length
16384
preload_buffer_size
32768
read_buffer_size
131072
read_rnd_buffer_size
262144
sort_buffer_size
262144
sql_buffer_result

提前感谢，对不起我的英语

标签： mysqlperformancemariadb

解决方案

  • 拥有一个大的不一定更好query_cache_size。当对表进行任何修改时，将删除该表的所有条目（在 QC 中）。QC 越大，所需的时间越长。 50M是一个合理的最大值。
  • 所需的非 QC 缓存涉及查询中的所有表——索引块需要进入key_buffer，数据块需要由操作系统缓存。请SHOW VARIABLES LIKE '%buffer%';在两台机器上提供。
  • 繁忙的生产系统上的 13 秒可能涉及在不太繁忙的其他机器上没有发生的缓存未命中。
  • 计数是否正确？有很多看似无用的JOINs。这些可能会COUNT(*)出乎意料地膨胀。

答案大概是这样的：

MariaDB 和 MySQL 中的优化器代码出现了分歧。看起来 MariaDB 决定（希望是正确的）大多数表与查询无关。

我建议您将查询重写为仅使用cus并rep在 MySQL 生产机器上运行它。然后看看是否得到与当前 6 表查询相同的结果。并查看查询是否运行得更快。

（附注...）从长远来看，MyISAM 正在消失。您应该计划迁移到 InnoDB。

推荐阅读