MySQL 5.7慢查询优化求助：Key_reads/Key_read_requests比值异常

环境与问题概述 #

• 运行环境：Windows Server 2012 + MySQL 5.7，强制使用MyISAM引擎（引擎及表的列数结构均无法修改）
• 关联表信息：
  • h_cell：存储cell每日统计数据，1.1亿行、800+列，表大小256GB
  • clusters_cust：存储集群定义，3列（cell、集群名称、备注），共6.2万行；包含两个集群：Cluster1（7千个cell）、Cluster2（5.5万个cell）
• 核心问题：原key_buffer_size默认8MB时，Key_reads/Key_read_requests比值约0.14（可接受值0.01，最优0.001）；逐步调至16GB、24GB（服务器物理内存64GB）后，查询速度及该比值均无改善

表结构详情 #

h_cell表（MyISAM） #

CREATE TABLE `h_cell` (
    `Time` DATE NOT NULL,
    `eNodeB` INT(11) NOT NULL,
    `Cell` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
    `LocalCI` INT(11) NOT NULL,
    `Site` INT(11) NOT NULL,
    `Integrity` VARCHAR(10) NOT NULL COLLATE 'utf8_general_ci',
    counter1 INT(11) NULL DEFAULT NULL,
    counter2 INT(11) NULL DEFAULT NULL,
    ........
    PRIMARY KEY (`Cell`, `Time`) USING BTREE,
    INDEX `Index 2` (`Cell`, `Time`) USING BTREE,
    INDEX `eNodeB` (`eNodeB`, `LocalCI`, `Time`) USING BTREE,
    INDEX `Index 4` (`Time`, `LocalCI`) USING BTREE,
    INDEX `server` (`server`, `Time`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

clusters_cust表（原InnoDB，改为MyISAM无改善） #

CREATE TABLE `clusters_cust` (
    `Cell` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
    `Cluster` VARCHAR(80) NOT NULL COLLATE 'utf8_general_ci',
    `Comment` VARCHAR(200) NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`Cell`, `Cluster`) USING BTREE,
    INDEX `Cell` (`Cell`) USING BTREE,
    INDEX `Cluster` (`Cluster`) USING BTREE,
    INDEX `Index 4` (`Cluster`, `Comment`) USING BTREE,
    INDEX `Index 5` (`Comment`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
;

查询语句与执行耗时 #

执行的查询语句：

select cluster,time,
round(sum(counter1)/sum(counter1+counter2)*100,2) as Avail,
...................
from h_cell
left join clusters_cust on clusters_cust.cell = h_cell.cell
where cluster='Cluster1' and  Time>='2023-08-01 00:00:00' and Time<='2023-10-02 00:00:00'
group by time

• 结果集：含19列（使用h_cell中43个原始计数器），返回63行（对应报告期内每日数据）
• 耗时：Cluster1查询约22分钟，Cluster2约34分钟

执行计划 #

id  select_type table               type    possible_keys                           key         key_len     ref                 rows    filtered    Extra
1   SIMPLE      clusters_cust       ref     PRIMARY,Cell,Cluster,Index 4            Cluster     242         const               12232   100         Using where; Using index; Using temporary; Using filesort
1   SIMPLE      h_cell              ref     PRIMARY,Index 2,eNodeB,Index 4,server   Index 2     62          clusters_cust.Cell  1055    11.15       Using index condition

系统监控与异常现象 #

• 内存监控：mysqld服务提交内存为25GB（对应24GB的key_buffer_size），但私有内存仅约5GB，查询前后无变化
• 服务启动异常：MySQL服务无法通过Windows正常启动，需手动用mysqld启动、mysqladmin关闭；启动时提示ibdata1需可写，已给datadir目录Everyone权限

求助方向 #

恳请提供配置问题排查方向及系统变量调整建议。

内容的提问来源于stack exchange，提问作者Ivaylo