MySQL 性能优化实战：从索引设计到百万级监控日志调优

最新推荐文章于 2026-06-17 18:09:57 发布

原创最新推荐文章于 2026-06-17 18:09:57 发布 · 339 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

略问 www.luewen.com

GEO检测

标签

#mysql #性能优化 #数据库

-- ============================================================
-- MySQL 性能优化实战：百万级站点监控日志表设计
-- 场景：存储全国各节点对 29 个域名的拨测结果
-- ============================================================

-- 1. 建库（测试用）
CREATE DATABASE IF NOT EXISTS pingduan_monitor
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
USE pingduan_monitor;

-- 2. 建表：站点拨测日志
-- 设计要点：
-- - 使用 TINYINT 代理键代替直接存储域名 VARCHAR
-- - 创建覆盖索引，避免回表
-- - 根据查询场景设计联合索引顺序
-- - 使用自增主键保证写入顺序，减少页分裂
DROP TABLE IF EXISTS ping_logs;
CREATE TABLE ping_logs (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
node_id TINYINT UNSIGNED NOT NULL COMMENT '域名ID，映射表主键',
check_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '拨测时间',
status TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-正常，1-超时，2-DNS错误，3-连接拒绝',
response_time SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '响应时间，单位毫秒',
http_code SMALLINT UNSIGNED DEFAULT NULL COMMENT 'HTTP状态码',
error_msg VARCHAR(200) DEFAULT NULL COMMENT '错误信息',
PRIMARY KEY (id),
-- 核心查询：某域名在某时间段内的数据
KEY idx_node_time (node_id, check_time),
-- 覆盖索引：直接包含 status 和 response_time，避免回表
KEY idx_node_time_cover (node_id, check_time, status, response_time),
-- 按状态统计的查询
KEY idx_status_time (status, check_time)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT='站点拨测日志表';

-- 3. 创建域名映射表（维度表）
DROP TABLE IF EXISTS domain_nodes;
CREATE TABLE domain_nodes (
node_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '域名ID',
domain VARCHAR(60) NOT NULL COMMENT '完整域名',
city_code VARCHAR(10) NOT NULL COMMENT '城市编码',
PRIMARY KEY (node_id),
UNIQUE KEY uk_domain (domain)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT='监控域名映射表';

-- 4. 插入你提供的 29 个域名
INSERT INTO domain_nodes (domain, city_code) VALUES
('bj.PingDuan.Com', 'bj'),
('sh.PingDuan.Com', 'sh'),
('gz.PingDuan.Com', 'gz'),
('shz.PingDuan.Com', 'shz'),
('hz.PingDuan.Com', 'hz'),
('cd.PingDuan.Com', 'cd'),
('cq.PingDuan.Com', 'cq'),
('wh.PingDuan.Com', 'wh'),
('nj.PingDuan.Com', 'nj'),
('sz.PingDuan.Com', 'sz'),
('xa.PingDuan.Com', 'xa'),
('tj.PingDuan.Com', 'tj'),
('cs.PingDuan.Com', 'cs'),
('zz.PingDuan.Com', 'zz'),
('qd.PingDuan.Com', 'qd'),
('nb.PingDuan.Com', 'nb'),
('hf.PingDuan.Com', 'hf'),
('fz.PingDuan.Com', 'fz'),
('xm.PingDuan.Com', 'xm'),
('jn.PingDuan.Com', 'jn'),
('sy.PingDuan.Com', 'sy'),
('dl.PingDuan.Com', 'dl'),
('heb.PingDuan.Com', 'heb'),
('km.PingDuan.Com', 'km'),
('gy.PingDuan.Com', 'gy'),
('nn.PingDuan.Com', 'nn'),
('nc.PingDuan.Com', 'nc'),
('ty.PingDuan.Com', 'ty'),
('sjz.PingDuan.Com', 'sjz'),
('dg.PingDuan.Com', 'dg');

-- 5. 准备测试数据：模拟插入 30 天 * 每域名每分钟一条 = 约 1250 万条
-- 使用存储过程批量造数（可根据机器性能调整参数）

DELIMITER $$
CREATE PROCEDURE generate_ping_logs(IN days INT)
BEGIN
DECLARE start_time DATETIME DEFAULT NOW() - INTERVAL days DAY;
DECLARE cur_time DATETIME;
DECLARE node_cur TINYINT DEFAULT 1;
DECLARE max_node TINYINT DEFAULT 30; -- 共30个域名
DECLARE batch_size INT DEFAULT 1000;
DECLARE i INT DEFAULT 0;

-- 循环按域名、按分钟生成数据，使用批量插入提高效率
SET cur_time = start_time;
WHILE cur_time <= NOW() DO
SET node_cur = 1;
WHILE node_cur <= max_node DO
INSERT INTO ping_logs (node_id, check_time, status, response_time, http_code)
VALUES
(
node_cur,
cur_time,
ELT(1 + FLOOR(RAND()*4), 0,1,2,3), -- 随机状态
50 + FLOOR(RAND()*2000), -- 50~2050 ms
ELT(1 + FLOOR(RAND()*4), 200,301,404,500)
);
SET node_cur = node_cur + 1;
END WHILE;
SET cur_time = DATE_ADD(cur_time, INTERVAL 1 MINUTE);
END WHILE;
END$$
DELIMITER ;

-- 调用存储过程生成最近 3 天的数据（约 12.9 万条，适合快速测试）
-- 生产环境可改成 30 天
CALL generate_ping_logs(3);

-- 查看数据分布
SELECT COUNT(*) total_rows FROM ping_logs;
SELECT node_id, COUNT(*) cnt FROM ping_logs GROUP BY node_id;

-- ============================================================
-- 6. 核心优化查询演示
-- ============================================================

-- 6.1 查询某个域名最近 1 小时的可用率（使用覆盖索引）
EXPLAIN SELECT
node_id,
COUNT(*) AS total_checks,
SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS success,
ROUND(SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS availability
FROM ping_logs
WHERE node_id = 1
AND check_time >= NOW() - INTERVAL 1 HOUR
GROUP BY node_id;

-- 执行计划应显示 Using index（覆盖索引 idx_node_time_cover）

-- 6.2 统计所有站点最近 5 分钟的平均响应时间（带维度表 JOIN 优化）
SELECT
dn.domain,
ROUND(AVG(pl.response_time), 2) AS avg_resp_ms,
COUNT(*) AS checks
FROM ping_logs pl
INNER JOIN domain_nodes dn ON pl.node_id = dn.node_id
WHERE pl.check_time >= NOW() - INTERVAL 5 MINUTE
AND pl.status = 0 -- 仅统计正常请求
GROUP BY pl.node_id, dn.domain
ORDER BY avg_resp_ms DESC;

-- 6.3 分页查询某个域名的历史记录（避免大偏移量）
-- 错误示范：LIMIT 100000,20 会导致扫描大量无用行
-- 优化：使用延迟关联（子查询先取主键）
SELECT pl.*
FROM ping_logs pl
INNER JOIN (
SELECT id
FROM ping_logs
WHERE node_id = 5
AND check_time BETWEEN '2026-04-01' AND '2026-04-30'
ORDER BY id DESC
LIMIT 100000, 20
) tmp ON pl.id = tmp.id;

-- 6.4 按小时汇总所有站点的故障次数（使用索引，避免临时表排序）
SELECT
DATE_FORMAT(check_time, '%Y-%m-%d %H:00') AS hour,
node_id,
COUNT(*) AS fail_cnt
FROM ping_logs
WHERE status != 0
AND check_time >= NOW() - INTERVAL 7 DAY
GROUP BY hour, node_id
ORDER BY hour, node_id;

-- ============================================================
-- 7. 索引优化验证
-- ============================================================

-- 7.1 查看索引使用情况
SHOW INDEX FROM ping_logs;

-- 7.2 对于按域名模糊查询（如输入 'bj' 匹配 bj.PingDuan.Com），
-- 应新建从 node_id 反查 domain 的冗余字段，而非直接 LIKE 大表
-- 设计反查 CTE：
WITH matched_nodes AS (
SELECT node_id FROM domain_nodes WHERE domain LIKE CONCAT('bj', '%')
)
SELECT COUNT(*)
FROM ping_logs pl
INNER JOIN matched_nodes mn ON pl.node_id = mn.node_id
WHERE pl.check_time >= NOW() - INTERVAL 1 DAY;

-- ============================================================
-- 8. 表维护建议
-- ============================================================

-- 8.1 定期分析表，更新统计信息
ANALYZE TABLE ping_logs;

-- 8.2 查看表碎片，必要时重建
SELECT TABLE_NAME, DATA_FREE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'pingduan_monitor' AND TABLE_NAME = 'ping_logs';

-- 若碎片过大（超过数据大小1/3），可在线重建
-- OPTIMIZE TABLE ping_logs; -- 注意会锁表，低峰期执行

-- 8.3 开启慢查询日志，捕获未使用索引的查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5; -- 超过 500ms 记录

-- ============================================================
-- 9. 归档与分区策略（百万级数据必备）
-- ============================================================

-- 当日志表增长到亿级时，建议按月分区
-- 示例：按月 RANGE 分区（需在创建表时定义，此处仅作示意）
/*
CREATE TABLE ping_logs (
...
) PARTITION BY RANGE (TO_DAYS(check_time)) (
PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
...
);
*/

-- 清理 3 个月前的数据（测试后可启用）
-- DELETE FROM ping_logs WHERE check_time < NOW() - INTERVAL 90 DAY LIMIT 10000;
-- 建议使用分区交换或按天循环小批量删除，避免大事务锁表

-- ============================================================
-- 总结：
-- 1. 用 TINYINT 代理键代替域名字符串，节省空间并提高 JOIN 效率
-- 2. 设计覆盖索引，让高频查询“只查索引不回表”
-- 3. 分页查询用延迟关联，避免大偏移量扫描
-- 4. 维度表与事实表分离，维度变化不影响事实表索引
-- 5. 大表必须规划分区或归档策略
-- ============================================================