我的本地A股数据库查询优化过程！

admin • 3小时前 • SQL技巧

我的本地A股数据库查询优化过程！

建立分区表

CREATE DATABASE IF NOT EXISTS stock;
USE stock;

CREATE TABLE `historical` (
`日期` date NOT NULL,
`代码` varchar(10) NOT NULL,
`名称` varchar(40) DEFAULT NULL,
`所属行业` varchar(40) DEFAULT NULL,
`开盘价` decimal(12,3) DEFAULT NULL,
`最高价` decimal(12,3) DEFAULT NULL,
`最低价` decimal(12,3) DEFAULT NULL,
`收盘价` decimal(12,3) DEFAULT NULL,
`前收盘价` decimal(12,3) DEFAULT NULL,
`成交量（股）` bigint DEFAULT NULL,
`成交额（元）` decimal(20,2) DEFAULT NULL,
`换手率` decimal(8,4) DEFAULT NULL,
`涨幅%` decimal(8,4) DEFAULT NULL,
`振幅%` decimal(8,4) DEFAULT NULL,
`是否ST` varchar(4) DEFAULT NULL,
`量比` decimal(8,3) DEFAULT NULL,
`3日涨幅%` decimal(8,4) DEFAULT NULL,
`6日涨幅%` decimal(8,4) DEFAULT NULL,
`10日涨幅%` decimal(8,4) DEFAULT NULL,
`25日涨幅%` decimal(8,4) DEFAULT NULL,
`是否涨停` varchar(4) DEFAULT NULL,
`总股本（股）` bigint DEFAULT NULL,
`流通股本（股）` bigint DEFAULT NULL,
`总市值（元）` decimal(22,2) DEFAULT NULL,
`流通市值（元）` decimal(22,2) DEFAULT NULL,
`滚动市盈率` decimal(12,4) DEFAULT NULL,
`市净率` decimal(12,4) DEFAULT NULL,
`滚动市销率` decimal(12,4) DEFAULT NULL,
`5日线` decimal(12,3) DEFAULT NULL,
`10日线` decimal(12,3) DEFAULT NULL,
`20日线` decimal(12,3) DEFAULT NULL,
`30日线` decimal(12,3) DEFAULT NULL,
`60日线` decimal(12,3) DEFAULT NULL,
`120日线` decimal(12,3) DEFAULT NULL,
`250日线` decimal(12,3) DEFAULT NULL,
`上市时间` date DEFAULT NULL,
`退市时间` date DEFAULT NULL,
`年` int NOT NULL,
`月` int DEFAULT NULL,
PRIMARY KEY (`代码`,`日期`,`年`),
KEY `idx_ym` (`年`,`月`),
KEY `idx_name` (`名称`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`年`)
(PARTITION p1990 VALUES LESS THAN (1991) ENGINE = InnoDB,
PARTITION p1991 VALUES LESS THAN (1992) ENGINE = InnoDB,
PARTITION p1992 VALUES LESS THAN (1993) ENGINE = InnoDB,
PARTITION p1993 VALUES LESS THAN (1994) ENGINE = InnoDB,
PARTITION p1994 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p1995 VALUES LESS THAN (1996) ENGINE = InnoDB,
PARTITION p1996 VALUES LESS THAN (1997) ENGINE = InnoDB,
PARTITION p1997 VALUES LESS THAN (1998) ENGINE = InnoDB,
PARTITION p1998 VALUES LESS THAN (1999) ENGINE = InnoDB,
PARTITION p1999 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p2000 VALUES LESS THAN (2001) ENGINE = InnoDB,
PARTITION p2001 VALUES LESS THAN (2002) ENGINE = InnoDB,
PARTITION p2002 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p2003 VALUES LESS THAN (2004) ENGINE = InnoDB,
PARTITION p2004 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p2005 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p2006 VALUES LESS THAN (2007) ENGINE = InnoDB,
PARTITION p2007 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p2008 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p2009 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p2010 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION p2011 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p2012 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2013 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
PARTITION p2023 VALUES LESS THAN (2024) ENGINE = InnoDB,
PARTITION p2024 VALUES LESS THAN (2025) ENGINE = InnoDB,
PARTITION p2025 VALUES LESS THAN (2026) ENGINE = InnoDB,
PARTITION p2026 VALUES LESS THAN (2027) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

-- ============================================================================

-- historical 表查询调优记录 (2026-09-23)

-- 库: stock @ 127.0.0.1:3306 表: historical

-- 规模: 17,282,032 行 / 9.33 GB / 38 个 RANGE(年) 分区 / MySQL 8.0.45

-- ============================================================================

-- ---------------------------------------------------------------------------

-- 【已完成 1】在线扩大 InnoDB 缓冲池 128MB -> 16GB（立即生效，无需重启）

-- 机器 64GB 内存，整库 9.33GB，原 128MB 导致查询几乎全部走磁盘

-- ---------------------------------------------------------------------------

SET GLOBAL innodb_buffer_pool_size=16*1024*1024*1024;

-- 验证

SHOWVARIABLESLIKE'innodb_buffer_pool_size';

SELECT POOL_ID, POOL_SIZE, DATABASE_PAGES FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- ⚠️ 以上是临时生效，重启 MySQL 会回到 128MB。

-- 永久生效：编辑 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

-- 把 [mysqld] 段里的

-- innodb_buffer_pool_size=128M

-- 改成

-- innodb_buffer_pool_size=16G

-- 然后重启服务（管理员 CMD）：

-- net stop MySQL80 && net start MySQL80

-- 重启后 innodb_buffer_pool_instances=8（my.ini 里已有）才会真正生效，

-- 热改时它仍为 1（该参数只能在启动时确定）。

-- ---------------------------------------------------------------------------

-- 【已完成 2】加"日期优先"索引：支撑"某天/某区间全市场"查询

-- 原 27.73s -> 0.072s

-- 根因：分区键是 `年`，WHERE 日期=... 无法分区裁剪（命中全部 38 个分区），

-- 且没有以 `日期` 开头的索引 -> 只能全表扫 1852 万行

-- ---------------------------------------------------------------------------

ALTER TABLE historical ADD INDEX idx_date_code (日期, 代码);

-- 注：MySQL 8.0.45 分区表上写 ", ALGORITHM=INPLACE, LOCK=NONE" 会报 1064 语法错，

-- 直接省略即可（加二级索引默认就是 INPLACE、不阻塞读写）。1728 万行约 90 秒。

-- ---------------------------------------------------------------------------

-- 【已完成 3】清理冗余索引（前缀重复）

-- idx_code(代码) 是主键 (代码,日期,年) 的前缀 -> 完全冗余

-- idx_year(年) 是 idx_ym(年,月) 的前缀 -> 完全冗余

-- ---------------------------------------------------------------------------

ALTER TABLE historical DROP INDEX idx_code;

ALTER TABLE historical DROP INDEX idx_year;

-- ---------------------------------------------------------------------------

-- 【已完成 4】刷新统计信息（InnoDB 行数估算会漂移，影响优化器选计划）

-- ---------------------------------------------------------------------------

ANALYZE TABLE historical;

-- ---------------------------------------------------------------------------

-- 【当前索引状态】

-- PRIMARY (代码, 日期, 年) -- 单股历史查询走这个，0.015s

-- idx_date_code (日期, 代码) -- 某天/区间全市场，0.072s

-- idx_ym (年, 月) -- 某年某月，0.529s

-- idx_name (名称) -- 按名称查

-- idx_year_code (年, 代码) -- 可选保留

-- ---------------------------------------------------------------------------

-- 【写查询时的两条纪律】

-- 1) 尽量带上分区键 `年`，才能分区裁剪：

-- 差： WHERE 日期 BETWEEN '2025-09-01' AND '2025-09-22'

-- 好： WHERE 日期 BETWEEN '2025-09-01' AND '2025-09-22' AND 年=2025

-- （现在有 idx_date_code 兜着，"差"也能跑 0.75s，但带上 `年` 会更快）

-- 2) 别 SELECT *（39 列含大量 decimal），只取需要的列，可减少回表与网络传输。

-- ---------------------------------------------------------------------------

-- 【自查用的诊断 SQL】

-- ---------------------------------------------------------------------------

-- 看执行计划是否用上索引、是否裁剪分区（重点看 partitions 列，命中 38 个=没裁剪）

-- EXPLAIN SELECT 代码,收盘价 FROM historical WHERE 日期='2025-09-22';

-- 看命中率：reads/read_requests 比值高 = 缓冲池不够

-- SHOW GLOBAL STATUS WHERE Variable_name IN

-- ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');

-- 看各分区行数分布

-- SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS

-- FROM information_schema.partitions

-- WHERE table_schema='stock' AND table_name='historical'

-- ORDER BY PARTITION_ORDINAL_POSITION;

-- 慢查询（阈值 10 秒，日志在 datadir 下的 DESKTOP-NFFHG32-slow.log）

-- SHOW VARIABLES LIKE 'slow_query_log_file';

-- SET GLOBAL long_query_time = 1; -- 想抓更多就把阈值降到 1 秒

-- ---------------------------------------------------------------------------

-- 【后续可选：还能再快的地方】

-- 1) innodb_io_capacity：默认 200 是机械盘值。C 盘若是 SSD，改 my.ini 加

-- innodb_io_capacity=2000

-- （需重启）

-- 2) idx_year_code (年,代码) 大部分场景被主键覆盖，可考虑一并删掉。

-- 3) 高频固定报表（如"每月每只股票涨跌幅汇总"）可建汇总表，历史数据不变，

-- 预计算收益最大。

-- ============================================================================

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！

更多技巧， www.excelbook.cn

欢迎加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END

二维码

专业报告，杭州六小龙全景深度分析（70页PPT）！

< <上一篇

我的本地A股数据库查询优化过程！

下一篇>>