sql:Designing Efficient SQL SELECT Queries using mysql 9.0
/* ==========================================================================================
Designing Efficient SQL SELECT Queries —— MySQL 9.0 版本
国际化珠宝企业 ERP / CRM / HR 实战示例 · 万亿级高并发视角
------------------------------------------------------------------------------------------
目标：把「写一条 SELECT」拆成四个可独立验证的决策：
① 选择性列（Selective Columns）→ 决定能不能「只读索引」
② 有效过滤（Useful Filters） → 决定谓词能不能下推到存储引擎
③ 支撑索引（Supporting Indexes）→ 决定有没有合适的索引可走
④ 执行计划感知（Plan Awareness）→ 决定你怎么知道上面三条有没有做到
------------------------------------------------------------------------------------------
与 SQL Server 版本的关键差异（本脚本所有结论都基于 MySQL 9.0.1 实测，不是移植）：
· MySQL 没有 INCLUDE 覆盖列 —— 想让索引覆盖查询，只能把列**加进索引键**，
这会顺带把已有更窄的索引变成冗余索引（第 01 章用 sys.schema_redundant_indexes 实测）。
· MySQL 的隐式转换方向与 SQL Server **相反**，而且更严厉：
默认严格模式下 `varchar 列 = 数字字面量` **直接报 ERROR 1292**（不是慢，是功能故障）；
放宽 sql_mode 后才退化为全表扫描（实测 1499×）；
而 `int 列 = 字符串字面量` 只把字面量转一次，**毫无损失**（1.00×）。
· 排序规则不一致不是「慢」，而是**直接报 1267 硬错误**（列与列之间）；
但字符集不一致（latin1 vs utf8mb4）却是**可以执行**的，实测 1.00×。
· 「回表」不计入任何 Handler 计数器或 Innodb_rows_read ——
想量化回表只能看耗时或执行计划（第 01 章实测：行数 1.00× 而耗时 13.7×）。
· 没有锁升级（lock escalation）这个概念，但有**间隙锁 / next-key 锁**：
REPEATABLE-READ 下对「不存在的区间」加锁，会让别人的 INSERT 阻塞超时（实测 1205）。
------------------------------------------------------------------------------------------
运行方式（Windows / MySQL 9.0，UTF-8）：
mysql --defaults-extra-file=<凭据文件> --default-character-set=utf8mb4 < 本文件
★ 必须带 --default-character-set=utf8mb4，否则中文输出会乱码。
本脚本幂等：首次运行建表灌数（约 6 分钟），之后每次约 3~5 分钟，可反复执行。
★ 权威口径是第 12.1 节「本次运行实测总表」；头部速查表里的倍率会随统计/缓冲池浮动。
========================================================================================== */
CREATE DATABASE IF NOT EXISTS JewelryAnalytics
DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
SET SESSION cte_max_recursion_depth = 100000;
SET SESSION group_concat_max_len = 1000000;
SET SESSION optimizer_switch = 'index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION sql_safe_updates = 0; -- 兼容 MySQL Workbench 等 GUI 的「安全更新模式」，避免 demo_reset() 整表 DELETE 触发 1175
SET SESSION innodb_lock_wait_timeout = 50;
/* ==========================================================================================
★ 0.0 如何在 MySQL Workbench / 其他 GUI 里跑本脚本（必读，否则会误报「错误」）
------------------------------------------------------------------------------------------
本脚本是**整份顺序执行**的设计，强烈建议二选一：
方式①（最稳，推荐）：用命令行 mysql.exe 整份跑，输出进文件，完全不受 GUI 客户端限制：
"C:\Program Files\MySQL\MySQL Server 9.0\bin\mysql.exe" ^
--defaults-extra-file=_my.demo.cnf --default-character-set=utf8mb4 -t ^
< MySQL9_SelectDesign_JewelryDemo.sql > 验证输出.txt
其中 _my.demo.cnf 内含 [client] user=root / password=你的密码 / host=127.0.0.1 / port=3306。
方式②（仍用 Workbench）：请先改 3 个偏好，否则会被客户端限制打断（这不是 SQL 错误）：
Edit → Preferences → SQL Editor → Query Results
· 「Limit Rows」：取消勾选（GUI 会偷偷给每条 SELECT 末尾追加 LIMIT 0,1000，
叠加本脚本多行注释 SELECT 会触发 "Operation canceled / Error Code 0"）
· 「Maximum number of result sets」：调大（如 1000），否则跑到一半弹
"Maximum result count reached"，点 Yes 会让连接失步、后续全失败
Edit → Preferences → SQL Editor
· 「DBMS connection read timeout interval」：设为 600（默认 30 秒）。
第 05 章 O1「坏版本」故意让 80 万行 filesort，在本机(128M buffer pool)必然 >30 秒，
不调高就会报 "Error 2013 Lost connection"（恰 30.016 秒）—— 这是客户端超时，
不是服务器挂了，建好索引的「好版本」是毫秒级。
一句话：要么 mysql.exe 整份跑，要么 Workbench 里关掉 Limit Rows + 调高结果集上限 + 超时设 600。
========================================================================================== */
/* ==========================================================================================
§0.1 反模式速查表（MySQL 9.0 视角，倍率均为本机实测；行数倍数按 Innodb_rows_read 口径）
------------------------------------------------------------------------------------------
编号 │ 反模式 │ 修正写法 │ 本机实测
─────┼──────────────────────────────────────────┼───────────────────────────────────┼──────────────
K1 │ SELECT *（全 18 列）打穿覆盖索引 │ 只 SELECT 需要参与计算的列 │ 耗时 13.7×（行数 1.00×）
K1b│ 同上，中间量放大到 23.8 万行 │ 同上 │ 5.01× 行（改全表扫描）
C1a│ varchar 列 = 数字字面量（严格模式） │ 列 = '字符串字面量' │ ★ ERROR 1292
C1b│ varchar 列 = 数字字面量（放宽 sql_mode） │ 列 = '字符串字面量' │ 1499.25× 行
C2 │ varchar 列 = 数字型绑定参数 │ 参数按列类型绑定字符串 │ 1499.25× 行
C3 │ latin1 列与 utf8mb4 列直接比较 │ 统一字符集（重建列） │ 1.00× 行（可执行）
C4 │ 列与列排序规则不一致（0900 vs general） │ ALTER 统一排序规则 │ ★ ERROR 1267
C5 │ 谓词里给列套 COLLATE（想消除 1267） │ 别在查询里套，改表 │ 30000.00× 行
C6 │ datetime 列 = 'yyyy-MM-dd' │ 直接比较（字面量会被转 date） │ 1.00× 行（无害）
C7 │ int 列 = '123' │ 直接比较 │ 1.00× 行（无害）
C8 │ CAST(数字列 AS CHAR) = '…' │ 裸列直接比较 │ 200 万× 行（Handler 125001×）
S1 │ WHERE YEAR(col)=2025 AND MONTH(col)=6 │ col >= '2025-06-01' AND col < '…' │ 30.41× 行
S2 │ LIKE '%关键字%' │ 前缀 LIKE '关键字%' / FULLTEXT │ 200 万× 行
S3 │ WHERE LEFT(col,5)='ST010' │ col LIKE 'ST010%' │ 124.99× 行
S4 │ WHERE DATE(col)='2025-06-15' │ 半开区间 [d, d+1) │ 904.16× 行
S5 │ WHERE UPPER(col)='PAID' │ 存值时就统一大小写 │ 3.33× 行
S6 │ OR 跨两列 → 改 UNION ALL 就一定更快？ │ 别急着改，先看 EXPLAIN │ 0.97×（修正版更慢）
S7 │ 超长 IN 列表（300 个值）会拖垮优化器？ │ 不用救 │ 1.00× 行（Handler 1.10×）
S8 │ 冗余谓词（col='X' AND col IN ('X','Y')） │ 不用救 │ 1.00× 行（无害）
S9 │ 索引条件下推 ICP 关闭 │ 打开 ICP │ 行数 1.00× · 耗时 1.59×
IX1│ 复合索引把「范围列」放左边 │ 等值列在前、范围列在后 │ 10.02× 行
IX2│ 低基数前导列做复合索引 │ 选择性高的列做前导 │ 见 §04.3
O1 │ ORDER BY 的列没有可用索引 │ 给 (过滤列, 排序列) 建复合索引 │ 40000.00× 行
O2 │ ORDER BY 多列方向不一致 │ 建带 DESC 的复合索引（8.0+） │ 40000.00× 行
O3 │ GROUP BY 的列没有可用索引 │ 让索引顺序与 GROUP BY 一致 │ Handler 2.00× · 耗时 1.89×
O4 │ LIMIT 500000, 20 深分页 │ 键集分页（WHERE 唯一键 > 上页末值）│ 30000.00× 行
P1 │ 分区表上用 YEAR(分区列)=2025 过滤 │ 用半开区间触发分区裁剪 │ 2.50× 行 · 4.52× 耗时
V1 │ 派生表被迫物化（LIMIT/聚合阻断合并） │ 让它可合并（去掉 LIMIT） │ Handler 2.00× · 耗时 1.28×
V2 │ IN (子查询) 以为比 JOIN 慢 │ 不用改，已被半连接改写 │ 1.00× 行
E1 │ EAV 三重自连接（仅过滤） │ 宽表 │ 1.90× 行（反模式更多）
E2 │ EAV 五重自连接（还要取回属性） │ 宽表 + 覆盖索引 │ 5.00× 行 · 3.33× 耗时
E3 │ EAV 单属性过滤 │ 两种模型都可以 │ 0.20×（反模式更少）
L1 │ RR 下对「不存在的区间」加 FOR UPDATE │ 写热点表用 READ-COMMITTED │ ★ ERROR 1205
L2 │ 两个事务反向更新同一批行 │ 统一加锁顺序 / 缩短事务 │ ★ ERROR 1213
========================================================================================== */
/* ==========================================================================================
§0.2 旗舰结论（每条都是本机实测，不是「据说」）
┌───────────────────────────────────────────────────────────────────────────────────────┐
│ 1. MySQL 隐式转换的「方向」决定它是灾难还是无事： │
│ varchar 列 = 数字 → 严格模式**直接 ERROR 1292**（功能故障）；放宽 sql_mode 后列被转 │
│ 成 double，索引从头扫到尾（2,000,000 行 vs 1,334 行 = 1499×）； │
│ int 列 = 字符串 → 只转一次字面量，1.00×，完全无害。 │
│ 这个不对称是 MySQL 相对 SQL Server 最容易踩错的地方。 │
│ 2. 排序规则不一致在 MySQL 里是**硬失败**：两个不同排序规则的列做 = 比较， │
│ 直接 ERROR 1267 Illegal mix of collations；而 latin1 与 utf8mb4 比较是可执行的， │
│ 实测 1.00× → 两条陷阱的危害等级完全不同，别混为一谈。 │
│ 3. ★ MySQL 的「回表」在计数器里是隐形的。InnoDB 从二级索引拿到主键后再查聚簇索引， │
│ 既不增 Handler_read_key 也不增 Innodb_rows_read。实测：SELECT * vs 只取必要列， │
│ 行数 1.00×（19,755 → 19,756），**耗时 13.7×**。所以「行数变少」不等于「变快」， │
│ 必须 计数器 + EXPLAIN Extra + 耗时 三者交叉验证。 │
│ 4. 非 SARGable 的分级（本机实测访问行数倍数）： │
│ DATE(col)= 904.2× ← 包列里最贵的 │
│ LIKE '%x%' 200 万× │
│ LEFT(col,n) 125.0× │
│ YEAR(col)/MONTH(col) 30.4× │
│ UPPER(col) 3.3× │
│ 而 order_date >= '2025-06-01'（字符串字面量比 datetime）**不是**反模式：1.00×， │
│ 字面量会被转成 datetime，索引照用 —— 与很多「日期别用字符串」的说法相反。 │
│ 5. 排序与分页是「倍数之王」，且只需一条索引/一种写法就能解决： │
│ ORDER BY 无可用索引 40,000×；ORDER BY 方向不一致 40,000×；深分页 OFFSET 500000 30,000×│
│ 6. 万亿级真正的杀手是锁：RR 下 `SELECT ... WHERE id BETWEEN 2500000 AND 2500003 FOR │
│ UPDATE` 明明命中 0 行，却锁住了 `supremum pseudo-record`（整个 (2000000,+∞) 间隙）， │
│ 让另一个会话的 INSERT 报 1205 Lock wait timeout exceeded；换成 READ-COMMITTED 后 │
│ 锁清单里连一条 RECORD 都没有、INSERT 立即成功。 │
│ 实测：锁等待超时错误号是 1205（不是常说的 1203），死锁是 1213。 │
│ 7. 分区裁剪要靠**半开区间**：`YEAR(order_date)=2025` 让 EXPLAIN 的 partitions 列显示 │
│ p2024,p2025,p2026,pmax（全部），半开区间只显示 p2025。 │
│ 8. 与流行说法相反的实测结论（第 12.3 节成表 13 条）： │
│ · IN(子查询) 改 JOIN、冗余谓词清理 —— 优化器已处理，1.00×，纯属白干； │
│ · OR 跨两列改 UNION ALL 实测**更慢**（0.97×）； │
│ · 300 个值的长 IN 列表完全正常（1.00×，反模式读得更少）； │
│ · 日期列与字符串字面量比较 1.00×（真正贵的是把**函数**套在列上）； │
│ · EAV 仅过滤时反而更快（0.20×），代价全在「取回属性」（5.00× 行 / 3.33× 耗时）； │
│ · ICP 打开后行数完全不变、只有耗时变化 —— 因为省掉的是不计数的回表。 │
└───────────────────────────────────────────────────────────────────────────────────────┘
========================================================================================== */
/* ==========================================================================================
§00 环境与实验台勘察
========================================================================================== */
SELECT '§00.1 实例版本与关键参数' AS `-- 环境勘察 --`;
SELECT VERSION() AS 版本,
@@version_comment AS 发行版,
@@character_set_server AS 服务端字符集,
@@collation_server AS 服务端排序规则,
@@lower_case_table_names AS 表名大小写策略;
SELECT ROUND(@@innodb_buffer_pool_size/1024/1024) AS 缓冲池MB,
@@innodb_buffer_pool_instances AS 缓冲池实例,
ROUND(@@innodb_redo_log_capacity/1024/1024) AS 重做日志MB,
@@innodb_thread_concurrency AS 并发线程上限,
@@max_connections AS 最大连接数,
@@innodb_page_size AS 页大小,
@@transaction_isolation AS 默认隔离级别,
@@innodb_lock_wait_timeout AS 锁等待超时秒;
SELECT @@sql_mode AS sql_mode,
@@cte_max_recursion_depth AS CTE递归上限,
@@eq_range_index_dive_limit AS IN列表走索引探测的阈值,
@@range_optimizer_max_mem_size AS 范围优化内存上限;
SELECT ROUND(@@innodb_buffer_pool_size/1024/1024, 0) AS 缓冲池MB_用于判断
, '★ 缓冲池 128MB 远小于本实验台约 1.8GB 数据 —— 这正是我们想要的环境：缓存装不下，'
'顺序访问与随机访问的差距才会真实暴露出来，索引的价值（以及回表的代价）才看得出来。' AS 说明;
/* ==========================================================================================
00-C 实验台：国际化珠宝企业 ERP / CRM / HR 数据集（MySQL 9.0）
设计原则
1. 表结构与生产一致：非数值业务键用 VARCHAR/CHAR；金额用 DECIMAL；
姓名/品类/组织路径用 utf8mb4（多语言）。
2. 幂等：用 information_schema 判断表是否存在，不存在才建、才灌数、才建索引。
数据表建成后长期保留（重建几百万行代价高），每次重跑只回收过程与度量表。
3. 灌数用「双重递归 CTE 笛卡尔积」一次成批写入（比 20 次翻倍自插快 4 倍以上），
二级索引等灌完再一次性建（排序建索引，远快于边插边维护索引）。
4. 规模（约 780 万行，是本机可控范围内对万亿级的最接近模拟）：
erp_sales_order 2,000,000
erp_sales_order_line 3,000,000
crm_interaction 1,000,000
crm_customer 200,000
hr_attendance 500,000
rpt_sales_fact 2,000,000（按年 RANGE 分区）
hr_employee / erp_product / hr_org_unit / ref_country 等小维表
========================================================================================== */
USE JewelryAnalytics;
SET SESSION cte_max_recursion_depth = 100000;
DROP PROCEDURE IF EXISTS lab_build;
DROP PROCEDURE IF EXISTS _lab_gen_sales_order;
DROP PROCEDURE IF EXISTS _lab_gen_sales_line;
DROP PROCEDURE IF EXISTS _lab_gen_customer;
DROP PROCEDURE IF EXISTS _lab_gen_interaction;
DROP PROCEDURE IF EXISTS _lab_gen_attendance;
DROP PROCEDURE IF EXISTS _lab_gen_fact;
DROP PROCEDURE IF EXISTS _lab_gen_dims;
DELIMITER $$
-- ------------------------------------------------------------------------------------------
-- 小维表：国家/地区、产品、组织、员工、客户、遗留系统
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE _lab_gen_dims()
BEGIN
DELETE FROM ref_country;
INSERT INTO ref_country(country_code, country_name, region, currency_code, is_active) VALUES
('CN','中国大陆','Greater China','CNY',1),('HK','中国香港','Greater China','HKD',1),
('MO','中国澳门','Greater China','MOP',1),('TW','中国台湾','Greater China','TWD',1),
('US','美国','North America','USD',1),('CA','加拿大','North America','CAD',1),
('GB','英国','Europe','GBP',1),('FR','法国','Europe','EUR',1),
('IT','意大利','Europe','EUR',1),('CH','瑞士','Europe','CHF',1),
('JP','日本','Asia Pacific','JPY',1),('KR','韩国','Asia Pacific','KRW',1),
('SG','新加坡','Asia Pacific','SGD',1),('MY','马来西亚','Asia Pacific','MYR',1),
('TH','泰国','Asia Pacific','THB',1),('AU','澳大利亚','Asia Pacific','AUD',1),
('AE','阿联酋','Middle East','AED',1),('SA','沙特阿拉伯','Middle East','SAR',1),
('IN','印度','South Asia','INR',1),('ZA','南非','Africa','ZAR',1);
DELETE FROM erp_product;
INSERT INTO erp_product(product_id, sku, product_name, category, metal, gemstone, carat, list_price_usd, is_active)
WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 5000)
SELECT n,
CONCAT('SKU', LPAD(n, 8, '0')),
CONCAT(ELT(1+MOD(n,8),'经典系列','臻钻系列','东方系列','婚嫁系列','轻奢系列','国潮系列','高定系列','童饰系列'),
'·', ELT(1+MOD(n,6),'项链','戒指','手镯','耳饰','吊坠','手链'), ' 第', 1+MOD(n,999), '款'),
ELT(1+MOD(n,6),'项链','戒指','手镯','耳饰','吊坠','手链'),
ELT(1+MOD(n,5),'足金','18K金','铂金','银','彩金'),
IF(MOD(n,4)=0, NULL, ELT(1+MOD(n,5),'钻石','红宝石','蓝宝石','翡翠','珍珠')),
IF(MOD(n,4)=0, NULL, ROUND(0.10 + MOD(n,900)/100.0, 3)),
ROUND(88 + POW(MOD(n,700)/700.0, 3) * 260000, 2),
1
FROM c;
DELETE FROM hr_org_unit;
INSERT INTO hr_org_unit(org_id, org_code, org_name, parent_id, region, level_no) VALUES
(1,'G','集团全球总部',NULL,'Global',1),
(2,'G-CN','中国大陆区',1,'Greater China',2),(3,'G-HK','中国香港区',1,'Greater China',2),
(4,'G-APAC','亚太区',1,'Asia Pacific',2),(5,'G-EMEA','欧洲中东非洲区',1,'EMEA',2),
(6,'G-NA','北美区',1,'North America',2),
(7,'G-CN-SH','上海分公司',2,'Greater China',3),(8,'G-CN-BJ','北京分公司',2,'Greater China',3),
(9,'G-CN-GZ','广州分公司',2,'Greater China',3),(10,'G-HK-KLC','尖沙咀旗舰店组',3,'Greater China',3),
(11,'G-APAC-SG','新加坡分公司',4,'Asia Pacific',3),(12,'G-APAC-JP','日本分公司',4,'Asia Pacific',3),
(13,'G-EMEA-DXB','迪拜分公司',5,'EMEA',3),(14,'G-NA-NYC','纽约分公司',6,'North America',3);
INSERT INTO hr_org_unit(org_id, org_code, org_name, parent_id, region, level_no)
WITH RECURSIVE c(n) AS (SELECT 15 UNION ALL SELECT n+1 FROM c WHERE n < 120)
SELECT n, CONCAT('G-LEAF-', LPAD(n,3,'0')),
CONCAT('区域门店群 ', LPAD(n,3,'0')),
7 + MOD(n, 8), ELT(1+MOD(n,4),'Greater China','Asia Pacific','EMEA','North America'), 4
FROM c;
DELETE FROM hr_employee;
INSERT INTO hr_employee(employee_id, employee_no, employee_name, org_id, job_title, hire_date, country_code, base_salary_usd, is_active)
WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 2000)
SELECT n, CONCAT('EMP', LPAD(n, 8, '0')),
CONCAT(ELT(1+MOD(n,10),'陈','林','黄','李','王','张','吴','刘','蔡','周'),
ELT(1+MOD(n,8),'志明','淑芬','家豪','雅婷','俊杰','美玲','建国','丽华')),
CASE WHEN n <= 8 THEN 1 WHEN n <= 20 THEN 2+MOD(n,11) ELSE 15+MOD(n,106) END,
ELT(1+MOD(n,10),'店员','店长','区域经理','销售顾问','视觉陈列','采购专员','仓储主管','会计','HR专员','数据分析师'),
DATE_ADD('2015-01-01', INTERVAL MOD(n*13, 4000) DAY),
ELT(1+MOD(n,6),'CN','HK','US','JP','GB','AE'),
2000 + POW(MOD(n,500)/500.0, 3) * 88000, IF(MOD(n,29)=0, 0, 1)
FROM c;
END$$
-- ------------------------------------------------------------------------------------------
-- 客户主数据 + 遗留系统（用于字符集/排序规则陷阱）
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE _lab_gen_customer()
BEGIN
DELETE FROM crm_customer;
INSERT INTO crm_customer(customer_id, customer_no, customer_name, tier, country_code, city,
register_date, birth_month, lifetime_value_usd, is_active)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n < 200),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n < 1000)
SELECT s.n,
CONCAT('C', LPAD(s.n, 12, '0')),
CONCAT(ELT(1+MOD(s.n,12),'陈','林','黄','李','王','张','吴','刘','蔡','周','徐','孙'),
ELT(1+MOD(s.n,10),'嘉欣','伟豪','雅琪','子轩','思颖','浩然','婉婷','宇轩','美琪','俊宇')),
ELT(1+MOD(s.n,20),'VVIP',
'VIP','VIP','VIP','VIP',
'GOLD','GOLD','GOLD','GOLD','GOLD','GOLD',
'SILVER','SILVER','SILVER','SILVER','SILVER','SILVER',
'BRONZE','BRONZE','BRONZE'),
ELT(1+MOD(s.n,6),'CN','CN','CN','HK','US','JP'),
ELT(1+MOD(s.n,10),'上海','北京','深圳','广州','香港','纽约','东京','伦敦','迪拜','新加坡'),
DATE_ADD('2018-01-01', INTERVAL MOD(s.n*7, 2900) DAY),
1+MOD(s.n,12),
ROUND(120 + POW(MOD(s.n,600)/600.0, 4) * 420000, 2),
IF(MOD(s.n,37)=0, 0, 1)
FROM (SELECT 1000*(a.n-1)+b.n AS n FROM a CROSS JOIN b) s;
-- 遗留 CRM 表：同一张表里混入 utf8mb4_general_ci 排序列（老系统升级遗留的典型形态）
DELETE FROM crm_customer_legacy;
INSERT INTO crm_customer_legacy(legacy_id, customer_no, customer_name, src_system, migrated_on)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n < 50),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n < 1000)
SELECT s.n,
CONCAT('C', LPAD(s.n, 12, '0')),
CONCAT('LEGACY-', LPAD(s.n, 8, '0')),
ELT(1+MOD(s.n,3),'CRM2010','POSV3','WECHAT_MINI'),
DATE_ADD('2023-06-01', INTERVAL MOD(s.n, 400) DAY)
FROM (SELECT 1000*(a.n-1)+b.n AS n FROM a CROSS JOIN b) s;
END$$
-- ------------------------------------------------------------------------------------------
-- 订单主表 2,000,000 行
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE _lab_gen_sales_order()
BEGIN
INSERT INTO erp_sales_order
(order_no, order_date, country_code, store_code, channel, customer_id, sales_employee_id,
status, currency_code, item_count, net_amount_usd, tax_amount_usd, gross_amount_usd,
discount_pct, is_export, note, created_at)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n < 2000),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n < 1000)
SELECT CONCAT('SO', LPAD(t.n, 14, '0')),
t.odt,
ELT(1+MOD(t.n,10), 'CN','CN','CN','CN','US','US','US','HK','HK','JP'),
CONCAT('ST', LPAD(1+MOD(t.n*37, 1200), 4, '0')),
ELT(1+MOD(t.n,4), 'Retail','Online','Wholesale','Outlet'),
1 + MOD(t.n*17, 200000),
IF(MOD(t.n,33)=0, NULL, 1 + MOD(t.n*13, 2000)),
ELT(1+MOD(t.n,10), 'Paid','Paid','Paid','Shipped','Shipped','Shipped','Returned','Returned','Cancelled','Pending'),
ELT(1+MOD(t.n,10), 'CNY','CNY','CNY','CNY','USD','USD','USD','HKD','HKD','JPY'),
1 + MOD(t.n, 12),
t.net,
ROUND(t.net * 0.07, 2),
ROUND(t.net * 1.07, 2),
ELT(1+MOD(t.n,6), 0, 0, 0, 5, 10, 15),
IF(MOD(t.n,7)=0, 1, 0),
IF(MOD(t.n,50)=0, 'gift-wrap', NULL),
DATE_ADD(t.odt, INTERVAL 600 SECOND)
FROM (
SELECT s.n,
DATE_ADD('2024-01-01 08:00:00', INTERVAL (s.n % 912) DAY) + INTERVAL (s.n % 86400) SECOND AS odt,
ROUND(120 + POW(MOD(s.n,1000)/1000.0, 6) * 150000, 2) AS net
FROM (SELECT 2000*(a.n-1)+b.n AS n FROM a CROSS JOIN b) s
) t;
END$$
-- ------------------------------------------------------------------------------------------
-- 订单明细 3,000,000 行（保证每张订单至少 1 行，前 100 万张再各加 1 行 → 制造真实扇出）
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE _lab_gen_sales_line()
BEGIN
INSERT INTO erp_sales_order_line(order_id, line_no, product_id, qty, unit_price_usd, line_amount_usd, discount_amount_usd)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n < 3000),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n < 1000)
SELECT 1 + MOD(t.n-1, 2000000),
1 + (t.n-1) DIV 2000000,
1 + MOD(t.n*23, 5000),
1 + MOD(t.n, 3),
t.price,
ROUND(t.price * (1 + MOD(t.n,3)), 2),
ELT(1+MOD(t.n,5), 0, 0, 0, ROUND(t.price*0.05,2), ROUND(t.price*0.10,2))
FROM (
SELECT s.n, ROUND(88 + POW(MOD(s.n,700)/700.0, 3) * 260000, 2) AS price
FROM (SELECT 3000*(a.n-1)+b.n AS n FROM a CROSS JOIN b) s
) t;
END$$
-- ------------------------------------------------------------------------------------------
-- 客户互动 1,000,000 行（CRM 行为流）
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE _lab_gen_interaction()
BEGIN
INSERT INTO crm_interaction(customer_id, interaction_at, channel, topic, agent_id, duration_sec, satisfaction, remark)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n < 1000),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n < 1000)
SELECT 1 + MOD(t.n*19, 200000),
t.iat,
ELT(1+MOD(t.n,6), '门店','微信','电话','邮件','官网','小程序'),
ELT(1+MOD(t.n,8), '咨询','保养','维修','投诉','退换货','会员权益','定制','回访'),
IF(MOD(t.n,53)=0, NULL, 1 + MOD(t.n, 2000)),
30 + MOD(t.n*7, 3600),
IF(MOD(t.n,41)=0, NULL, 1 + MOD(t.n, 5)),
IF(MOD(t.n,80)=0, '客户提及竞品对比', NULL)
FROM (
SELECT s.n,
DATE_ADD('2024-01-01 09:00:00', INTERVAL (s.n % 912) DAY) + INTERVAL (s.n % 43200) SECOND AS iat
FROM (SELECT 1000*(a.n-1)+b.n AS n FROM a CROSS JOIN b) s
) t;
END$$
-- ------------------------------------------------------------------------------------------
-- 考勤 500,000 行（HR）
-- 唯一键 (employee_id, work_date) 要求「员工 × 日期」不重复，因此用整除/取余构造一一映射：
-- employee_id = 1 + (n-1) MOD 2000 → 2000 名员工全覆盖
-- work_date = '2024-01-01' + (n-1) DIV 2000 天 → 250 个工作日
-- 2000 × 250 = 500,000，恰好无重复。
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE _lab_gen_attendance()
BEGIN
INSERT INTO hr_attendance(employee_id, work_date, check_in, check_out, work_hours, ot_hours, leave_type)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n < 500),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n < 1000)
SELECT 1 + MOD(t.n - 1, 2000),
t.wd,
IF(MOD(t.n,67)=0, NULL, DATE_ADD(t.wd, INTERVAL (8*3600 + MOD(t.n,3600)) SECOND)),
IF(MOD(t.n,67)=0, NULL, DATE_ADD(t.wd, INTERVAL (17*3600 + MOD(t.n,5400)) SECOND)),
ROUND(6.5 + MOD(t.n, 250)/100.0, 2),
ELT(1+MOD(t.n,5), 0, 0, 0, 1, 2),
IF(MOD(t.n,37)=0, ELT(1+MOD(t.n,3),'年假','病假','事假'), NULL)
FROM (
SELECT s.n, DATE_ADD('2024-01-01', INTERVAL ((s.n-1) DIV 2000) DAY) AS wd
FROM (SELECT 1000*(a.n-1)+b.n AS n FROM a CROSS JOIN b) s
) t;
END$$
-- ------------------------------------------------------------------------------------------
-- 报表事实表 2,000,000 行：按年 RANGE 分区（万亿级的第一件武器：分区裁剪）
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE _lab_gen_fact()
BEGIN
INSERT INTO rpt_sales_fact(fact_id, order_date, country_code, store_code, product_id, qty, net_amount_usd)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n < 2000),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n < 1000)
SELECT s.n, s.od, s.cc, s.sc, s.pid, s.q, s.amt
FROM (
SELECT s.n,
DATE(DATE_ADD('2024-01-01', INTERVAL (s.n % 912) DAY)) AS od,
ELT(1+MOD(s.n,10), 'CN','CN','CN','CN','US','US','US','HK','HK','JP') AS cc,
CONCAT('ST', LPAD(1+MOD(s.n*37, 1200), 4, '0')) AS sc,
1 + MOD(s.n*23, 5000) AS pid,
1 + MOD(s.n, 4) AS q,
ROUND(120 + POW(MOD(s.n,1000)/1000.0, 6) * 150000, 2) AS amt
FROM (SELECT 2000*(a.n-1)+b.n AS n FROM a CROSS JOIN b) s
) s;
END$$
-- ------------------------------------------------------------------------------------------
-- 总控：只在表缺失时建表、灌数、建索引
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE lab_build()
BEGIN
DECLARE v_start DATETIME(6);
SET SESSION cte_max_recursion_depth = 100000;
-- ---- 维表 ----
CREATE TABLE IF NOT EXISTS ref_country(
country_code CHAR(2) NOT NULL, country_name VARCHAR(60) NOT NULL, region VARCHAR(20) NOT NULL,
currency_code CHAR(3) NOT NULL, is_active TINYINT NOT NULL DEFAULT 1,
PRIMARY KEY(country_code)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS erp_product(
product_id INT NOT NULL, sku VARCHAR(20) NOT NULL, product_name VARCHAR(120) NOT NULL,
category VARCHAR(40) NOT NULL, metal VARCHAR(20) NOT NULL, gemstone VARCHAR(30) NULL,
carat DECIMAL(6,3) NULL, list_price_usd DECIMAL(12,2) NOT NULL, is_active TINYINT NOT NULL DEFAULT 1,
PRIMARY KEY(product_id), UNIQUE KEY uq_product_sku(sku), KEY ix_product_category(category)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS hr_org_unit(
org_id INT NOT NULL, org_code VARCHAR(40) NOT NULL, org_name VARCHAR(80) NOT NULL,
parent_id INT NULL, region VARCHAR(20) NOT NULL, level_no TINYINT NOT NULL,
PRIMARY KEY(org_id), UNIQUE KEY uq_org_code(org_code), KEY ix_org_parent(parent_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS hr_employee(
employee_id INT NOT NULL, employee_no VARCHAR(12) NOT NULL, employee_name VARCHAR(80) NOT NULL,
org_id INT NOT NULL, job_title VARCHAR(40) NOT NULL, hire_date DATE NOT NULL,
country_code CHAR(2) NOT NULL, base_salary_usd DECIMAL(12,2) NOT NULL, is_active TINYINT NOT NULL,
PRIMARY KEY(employee_id), UNIQUE KEY uq_employee_no(employee_no), KEY ix_emp_org(org_id, hire_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS crm_customer(
customer_id INT NOT NULL, customer_no VARCHAR(16) NOT NULL, customer_name VARCHAR(160) NOT NULL,
tier VARCHAR(8) NOT NULL, country_code CHAR(2) NOT NULL, city VARCHAR(60) NOT NULL,
register_date DATE NOT NULL, birth_month TINYINT NOT NULL, lifetime_value_usd DECIMAL(14,2) NOT NULL,
is_active TINYINT NOT NULL, PRIMARY KEY(customer_id), UNIQUE KEY uq_customer_no(customer_no),
KEY ix_cust_country_tier(country_code, tier), KEY ix_cust_city(city)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ★ 故意把列排序规则设成 utf8mb4_general_ci，复现「老系统升级遗留的排序规则不一致」
CREATE TABLE IF NOT EXISTS crm_customer_legacy(
legacy_id INT NOT NULL, customer_no VARCHAR(16) NOT NULL, customer_name VARCHAR(160) NOT NULL,
src_system VARCHAR(20) NOT NULL, migrated_on DATE NOT NULL,
PRIMARY KEY(legacy_id), KEY ix_legacy_no(customer_no)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 建表后再单独把两列改成 general_ci（CREATE 时无法对单列指定 COLLATE 之外的字符集组合）
SET @has := (SELECT COUNT(*) FROM information_schema.columns
WHERE table_schema=DATABASE() AND table_name='crm_customer_legacy'
AND column_name='customer_no' AND collation_name='utf8mb4_general_ci');
IF @has = 0 THEN
ALTER TABLE crm_customer_legacy
MODIFY customer_no VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
MODIFY customer_name VARCHAR(160) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
END IF;
CREATE TABLE IF NOT EXISTS crm_interaction(
interaction_id BIGINT NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, interaction_at DATETIME NOT NULL,
channel VARCHAR(12) NOT NULL, topic VARCHAR(30) NOT NULL, agent_id INT NULL,
duration_sec INT NOT NULL, satisfaction TINYINT NULL, remark VARCHAR(120) NULL,
PRIMARY KEY(interaction_id), KEY ix_ci_cust_at(customer_id, interaction_at),
KEY ix_ci_at(interaction_at), KEY ix_ci_channel(channel, interaction_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS hr_attendance(
attendance_id BIGINT NOT NULL AUTO_INCREMENT, employee_id INT NOT NULL, work_date DATE NOT NULL,
check_in DATETIME NULL, check_out DATETIME NULL, work_hours DECIMAL(5,2) NOT NULL,
ot_hours DECIMAL(5,2) NOT NULL, leave_type VARCHAR(10) NULL,
PRIMARY KEY(attendance_id), UNIQUE KEY uq_att_emp_date(employee_id, work_date),
KEY ix_att_date(work_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ★ 排序规则 / 字符集陷阱专用小表（3 万行，始终可重建）
-- 三列存同一批客户号，但分别用 utf8mb4_0900_ai_ci / utf8mb4_general_ci / latin1，
-- 用于精确复现「列与列排序规则不一致 → 硬报错」「字符集不一致 → 可执行但代价不同」。
CREATE TABLE IF NOT EXISTS trap_join(
rid INT NOT NULL,
key_0900 VARCHAR(16) NOT NULL COLLATE utf8mb4_0900_ai_ci,
key_general VARCHAR(16) NOT NULL COLLATE utf8mb4_general_ci,
key_latin1 VARCHAR(16) CHARACTER SET latin1 NOT NULL,
src VARCHAR(20) NOT NULL,
PRIMARY KEY(rid), KEY ix_0900(key_0900), KEY ix_general(key_general), KEY ix_latin1(key_latin1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 珠宝商品属性（EAV / 键值行模型，用于演示「过度范式化」）
CREATE TABLE IF NOT EXISTS erp_product_attr(
product_id INT NOT NULL, attr_name VARCHAR(20) NOT NULL, attr_value VARCHAR(40) NOT NULL,
PRIMARY KEY(product_id, attr_name), KEY ix_attr(attr_name, attr_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ---- 事实表（先只建主键，灌完再建二级索引）----
IF (SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema=DATABASE() AND table_name='erp_sales_order') = 0 THEN
SET v_start = NOW(6);
CREATE TABLE erp_sales_order(
order_id BIGINT NOT NULL AUTO_INCREMENT,
order_no VARCHAR(24) NOT NULL,
order_date DATETIME NOT NULL,
country_code CHAR(2) NOT NULL,
store_code VARCHAR(8) NOT NULL,
channel VARCHAR(10) NOT NULL,
customer_id INT NOT NULL,
sales_employee_id INT NULL,
status VARCHAR(10) NOT NULL,
currency_code CHAR(3) NOT NULL,
item_count SMALLINT NOT NULL,
net_amount_usd DECIMAL(14,2) NOT NULL,
tax_amount_usd DECIMAL(12,2) NOT NULL,
gross_amount_usd DECIMAL(14,2) NOT NULL,
discount_pct DECIMAL(5,2) NOT NULL,
is_export TINYINT NOT NULL,
note VARCHAR(64) NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY(order_id), UNIQUE KEY uq_so_no(order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CALL _lab_gen_sales_order();
ALTER TABLE erp_sales_order
ADD KEY ix_so_country_date (country_code, order_date),
ADD KEY ix_so_store_date_cover(store_code, order_date, net_amount_usd, item_count, status, currency_code),
ADD KEY ix_so_status_date (status, order_date),
ADD KEY ix_so_cust_date (customer_id, order_date),
ADD KEY ix_so_channel_date (channel, order_date),
ADD KEY ix_so_date (order_date);
SELECT CONCAT(' [建表] erp_sales_order 2,000,000 行 + 6 个二级索引，耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND, v_start, NOW(6))/1000000, 1), ' 秒') AS lab_log;
END IF;
IF (SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema=DATABASE() AND table_name='erp_sales_order_line') = 0 THEN
SET v_start = NOW(6);
CREATE TABLE erp_sales_order_line(
line_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, line_no SMALLINT NOT NULL,
product_id INT NOT NULL, qty SMALLINT NOT NULL, unit_price_usd DECIMAL(12,2) NOT NULL,
line_amount_usd DECIMAL(14,2) NOT NULL, discount_amount_usd DECIMAL(12,2) NOT NULL,
PRIMARY KEY(line_id), KEY ix_sol_order(order_id), KEY ix_sol_product(product_id, order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CALL _lab_gen_sales_line();
SELECT CONCAT(' [建表] erp_sales_order_line 3,000,000 行 + 2 个索引，耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND, v_start, NOW(6))/1000000, 1), ' 秒') AS lab_log;
END IF;
IF (SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema=DATABASE() AND table_name='rpt_sales_fact') = 0 THEN
SET v_start = NOW(6);
CREATE TABLE rpt_sales_fact(
fact_id BIGINT NOT NULL, order_date DATE NOT NULL, country_code CHAR(2) NOT NULL,
store_code VARCHAR(8) NOT NULL, product_id INT NOT NULL, qty INT NOT NULL,
net_amount_usd DECIMAL(14,2) NOT NULL,
PRIMARY KEY(fact_id, order_date),
KEY ix_fact_country_date(country_code, order_date),
KEY ix_fact_store(store_code, order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
CALL _lab_gen_fact();
SELECT CONCAT(' [建表] rpt_sales_fact 2,000,000 行（4 个分区）+ 2 个索引，耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND, v_start, NOW(6))/1000000, 1), ' 秒') AS lab_log;
END IF;
-- ---- 维表数据 ----
IF (SELECT COUNT(*) FROM ref_country) = 0 OR (SELECT COUNT(*) FROM erp_product) = 0 THEN
CALL _lab_gen_dims();
END IF;
IF (SELECT COUNT(*) FROM crm_customer) = 0 THEN
SET v_start = NOW(6);
CALL _lab_gen_customer();
SELECT CONCAT(' [建表] crm_customer 200,000 行 + crm_customer_legacy 50,000 行，耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND, v_start, NOW(6))/1000000, 1), ' 秒') AS lab_log;
END IF;
IF (SELECT COUNT(*) FROM crm_interaction) = 0 THEN
SET v_start = NOW(6);
CALL _lab_gen_interaction();
SELECT CONCAT(' [建表] crm_interaction 1,000,000 行，耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND, v_start, NOW(6))/1000000, 1), ' 秒') AS lab_log;
END IF;
IF (SELECT COUNT(*) FROM hr_attendance) = 0 THEN
SET v_start = NOW(6);
CALL _lab_gen_attendance();
SELECT CONCAT(' [建表] hr_attendance 500,000 行，耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND, v_start, NOW(6))/1000000, 1), ' 秒') AS lab_log;
END IF;
-- ---- 排序规则/字符集陷阱表 ----
IF (SELECT COUNT(*) FROM trap_join) = 0 THEN
INSERT INTO trap_join(rid,key_0900,key_general,key_latin1,src)
WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 30000)
SELECT n, CONCAT('C',LPAD(n,12,'0')), CONCAT('C',LPAD(n,12,'0')), CONCAT('C',LPAD(n,12,'0')),
'CRM2010'
FROM c;
SELECT ' [建表] trap_join 30,000 行（3 种排序规则/字符集同值列）' AS lab_log;
END IF;
-- ---- 珠宝商品属性 EAV 表（每件商品 5 条属性行 = 25,000 行）----
IF (SELECT COUNT(*) FROM erp_product_attr) = 0 THEN
INSERT INTO erp_product_attr(product_id, attr_name, attr_value)
SELECT p.product_id, a.attr_name, a.attr_value
FROM erp_product p
CROSS JOIN (
SELECT 'metal' AS attr_name, '' AS attr_value UNION ALL
SELECT 'gemstone', '' UNION ALL SELECT 'carat', '' UNION ALL
SELECT 'category', '' UNION ALL SELECT 'price_band', ''
) a;
UPDATE erp_product_attr pa JOIN erp_product p ON p.product_id = pa.product_id
SET pa.attr_value = CASE pa.attr_name
WHEN 'metal' THEN p.metal
WHEN 'gemstone' THEN IFNULL(p.gemstone, 'NONE')
WHEN 'carat' THEN IFNULL(CAST(p.carat AS CHAR), '0')
WHEN 'category' THEN p.category
ELSE CONCAT('B', FLOOR(p.list_price_usd / 5000)) END;
SELECT CONCAT(' [建表] erp_product_attr ', (SELECT COUNT(*) FROM erp_product_attr), ' 行（EAV）') AS lab_log;
END IF;
END$$
DELIMITER ;
/* ==========================================================================================
00-D 度量框架（MySQL 原生：performance_schema.session_status + EXPLAIN）
------------------------------------------------------------------------------------------
为什么不用「解析执行计划」来量成本？因为 MySQL 不像 SQL Server 那样把逻辑读写写进计划 XML。
MySQL 的等价物是 SESSION 级状态计数器：
Handler_read_next 按索引键顺序读一行（范围扫描 / 覆盖索引扫描）
Handler_read_rnd_next 按物理顺序读一行（★ 全表扫描的标志）
Handler_read_key 通过索引做一次定位
Handler_read_rnd 按固定位置读一行（★ filesort 之后回表）
Handler_read_first 读索引首行（★ 全索引扫描）
Innodb_rows_read InnoDB 引擎实际读取的行数
Created_tmp_tables / Created_tmp_disk_tables / Sort_rows / Sort_scan / Sort_range
Select_scan 全表扫描次数
全部取「执行前 / 执行后快照的差值」→ 得到**单条语句**的真实成本。
这比 SQL Server 的 sys.dm_exec_query_stats 更直接：不需要 digest 匹配，也不会被别的语句污染。
========================================================================================== */
USE JewelryAnalytics;
DROP PROCEDURE IF EXISTS demo_reset;
DROP PROCEDURE IF EXISTS demo_snap;
DROP PROCEDURE IF EXISTS demo_begin;
DROP PROCEDURE IF EXISTS demo_end;
DROP PROCEDURE IF EXISTS demo_report;
DROP PROCEDURE IF EXISTS demo_explain;
DROP PROCEDURE IF EXISTS demo_explain_analyze;
DROP PROCEDURE IF EXISTS demo_v;
DROP PROCEDURE IF EXISTS demo_err_probe;
DROP TABLE IF EXISTS demo_result;
DROP TABLE IF EXISTS demo_metric;
DROP TABLE IF EXISTS demo_verify;
CREATE TABLE demo_metric(
tag VARCHAR(24) NOT NULL,
h_first BIGINT NULL, h_key BIGINT NULL, h_next BIGINT NULL,
h_rnd BIGINT NULL, h_rnd_next BIGINT NULL,
rows_read BIGINT NULL, innodb_rows BIGINT NULL,
tmp_tab BIGINT NULL, tmp_disk BIGINT NULL,
sort_rows BIGINT NULL, sort_scan BIGINT NULL, sort_range BIGINT NULL,
sel_scan BIGINT NULL, full_join BIGINT NULL,
elapsed_us BIGINT NULL,
PRIMARY KEY(tag)
) ENGINE=InnoDB;
CREATE TABLE demo_result(
check_name VARCHAR(90) NOT NULL,
chapter VARCHAR(10) NOT NULL,
label VARCHAR(230) NOT NULL,
bad_rows BIGINT NULL, good_rows BIGINT NULL, rows_ratio DECIMAL(12,2) NULL,
bad_us BIGINT NULL, good_us BIGINT NULL, time_ratio DECIMAL(12,2) NULL,
bad_scan BIGINT NULL, good_scan BIGINT NULL,
bad_tmpdisk BIGINT NULL, bad_sortrows BIGINT NULL,
passed TINYINT NOT NULL,
note VARCHAR(320) NULL,
PRIMARY KEY(check_name)
) ENGINE=InnoDB;
CREATE TABLE demo_verify(
check_name VARCHAR(120) NOT NULL, detail VARCHAR(320) NOT NULL, passed TINYINT NOT NULL,
PRIMARY KEY(check_name)
) ENGINE=InnoDB;
-- ------------------------------------------------------------------------------------------
-- ★ 度量用的「黑洞接收器」：BLACKHOLE 引擎，写入即丢弃。
-- 为什么需要它：如果直接 `SELECT * FROM erp_sales_order ...` 取回几十万行，
-- 结果集会全部打印进验证输出文件（几百 MB），而这些行本身毫无信息量。
-- 改写成 `INSERT INTO demo_sink(line) SELECT CONCAT_WS('|', <所有列>) FROM ...`
-- 之后：① 服务端仍会逐行求值、逐行读取（计数器分毫不差）；② 客户端拿到 0 行。
-- 注意 CONCAT_WS 里必须**显式列出每一列** —— 这与 `SELECT *` 在优化器眼里完全等价
-- （决定访问路径的是「需要哪些列」，不是 `*` 这个语法），因此不损失实验保真度。
-- ------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS demo_sink(line VARCHAR(4000)) ENGINE=BLACKHOLE;
DELIMITER $$
-- 清空度量结果（保证脚本可重复执行）
CREATE PROCEDURE demo_reset()
BEGIN
DELETE FROM demo_metric;
DELETE FROM demo_result;
DELETE FROM demo_verify;
END$$
-- ------------------------------------------------------------------------------------------
-- 快照 SESSION 状态计数器（14 个出口参数，全部由 OUT 返回，不使用全局变量以免互相覆盖）
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE demo_snap(
OUT o_hf BIGINT, OUT o_hk BIGINT, OUT o_hn BIGINT, OUT o_hr BIGINT, OUT o_hrn BIGINT,
OUT o_rows BIGINT, OUT o_inno BIGINT, OUT o_tmp BIGINT, OUT o_tmpd BIGINT,
OUT o_sort BIGINT, OUT o_sorts BIGINT, OUT o_sortr BIGINT,
OUT o_scan BIGINT, OUT o_fj BIGINT)
BEGIN
DECLARE x_f, x_k, x_l, x_n, x_p, x_r, x_rn BIGINT UNSIGNED;
DECLARE x_inno, x_tmp, x_tmpd, x_sort, x_sorts, x_sortr, x_scan, x_fj BIGINT UNSIGNED;
SELECT
SUM(IF(VARIABLE_NAME='Handler_read_first', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Handler_read_key', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Handler_read_last', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Handler_read_next', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Handler_read_prev', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Handler_read_rnd', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Handler_read_rnd_next', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Innodb_rows_read', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Created_tmp_tables', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Created_tmp_disk_tables',CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Sort_rows', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Sort_scan', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Sort_range', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Select_scan', CAST(VARIABLE_VALUE AS UNSIGNED),0)),
SUM(IF(VARIABLE_NAME='Select_full_join', CAST(VARIABLE_VALUE AS UNSIGNED),0))
INTO x_f, x_k, x_l, x_n, x_p, x_r, x_rn,
x_inno, x_tmp, x_tmpd, x_sort, x_sorts, x_sortr, x_scan, x_fj
FROM performance_schema.session_status
WHERE VARIABLE_NAME IN ('Handler_read_first','Handler_read_key','Handler_read_last',
'Handler_read_next','Handler_read_prev','Handler_read_rnd','Handler_read_rnd_next',
'Innodb_rows_read','Created_tmp_tables','Created_tmp_disk_tables','Sort_rows',
'Sort_scan','Sort_range','Select_scan','Select_full_join');
SET o_hf = x_f; SET o_hk = x_k; SET o_hn = x_n; SET o_hr = x_r; SET o_hrn = x_rn;
SET o_rows = IFNULL(x_f,0) + IFNULL(x_k,0) + IFNULL(x_l,0) + IFNULL(x_n,0)
+ IFNULL(x_p,0) + IFNULL(x_r,0) + IFNULL(x_rn,0);
SET o_inno = x_inno; SET o_tmp = x_tmp; SET o_tmpd = x_tmpd;
SET o_sort = x_sort; SET o_sorts = x_sorts; SET o_sortr = x_sortr;
SET o_scan = x_scan; SET o_fj = x_fj;
END$$
CREATE PROCEDURE demo_begin(IN p_tag VARCHAR(24))
BEGIN
SET @b_tag = p_tag;
CALL demo_snap(@b_hf,@b_hk,@b_hn,@b_hr,@b_hrn,@b_rows,@b_inno,@b_tmp,@b_tmpd,
@b_sort,@b_sorts,@b_sortr,@b_scan,@b_fj);
SET @b_t0 = NOW(6);
END$$
CREATE PROCEDURE demo_end(IN p_tag VARCHAR(24))
BEGIN
DECLARE a_hf, a_hk, a_hn, a_hr, a_hrn BIGINT;
DECLARE a_rows, a_inno, a_tmp, a_tmpd, a_sort, a_sorts, a_sortr, a_scan, a_fj BIGINT;
DECLARE v_us BIGINT;
SET v_us = TIMESTAMPDIFF(MICROSECOND, @b_t0, NOW(6));
CALL demo_snap(a_hf,a_hk,a_hn,a_hr,a_hrn,a_rows,a_inno,a_tmp,a_tmpd,
a_sort,a_sorts,a_sortr,a_scan,a_fj);
DELETE FROM demo_metric WHERE tag = p_tag;
INSERT INTO demo_metric(tag, h_first, h_key, h_next, h_rnd, h_rnd_next,
rows_read, innodb_rows, tmp_tab, tmp_disk,
sort_rows, sort_scan, sort_range, sel_scan, full_join, elapsed_us)
VALUES (p_tag,
GREATEST(a_hf-@b_hf,0), GREATEST(a_hk-@b_hk,0), GREATEST(a_hn-@b_hn,0),
GREATEST(a_hr-@b_hr,0), GREATEST(a_hrn-@b_hrn,0),
GREATEST(a_rows-@b_rows,0), GREATEST(a_inno-@b_inno,0),
GREATEST(a_tmp-@b_tmp,0), GREATEST(a_tmpd-@b_tmpd,0),
GREATEST(a_sort-@b_sort,0), GREATEST(a_sorts-@b_sorts,0),
GREATEST(a_sortr-@b_sortr,0), GREATEST(a_scan-@b_scan,0),
GREATEST(a_fj-@b_fj,0), v_us);
END$$
-- ------------------------------------------------------------------------------------------
-- 对照报告 p_rule: 'R' 反模式「访问行数」≥ 修正版（默认，适用全表扫描 / 索引失效类）
-- 'I' 反模式「InnoDB 引擎读行」≥ 修正版
-- ★ 什么时候必须用 'I'：涉及「二级索引回表」的对照。
-- 实测发现：InnoDB 的二级索引回表走引擎内部的聚簇索引查找，
-- **不经过 handler 层**，因此 Handler_read_key 几乎不涨、rows_read 分不出胜负；
-- 只有 Innodb_rows_read 才能看见「回表把行数翻倍」这件事。
-- 'T' 反模式耗时 ≥ 2 倍修正版（适用随机 IO / 排序类，行数倍数不显著但耗时代价大）
-- 'N' 不作性能断言（只记录，用于「已不再是反模式」型结论）
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE demo_report(IN p_bad VARCHAR(24), IN p_good VARCHAR(24),
IN p_chapter VARCHAR(10), IN p_label VARCHAR(230),
IN p_check VARCHAR(90), IN p_rule CHAR(1), IN p_note VARCHAR(320))
BEGIN
DECLARE b_rows, g_rows, b_us, g_us BIGINT;
DECLARE b_scan, g_scan, b_td, g_td, b_sr, g_sr BIGINT;
DECLARE b_rn, g_rn, b_nx, g_nx, b_ir, g_ir BIGINT;
DECLARE v_rr DECIMAL(12,2); DECLARE v_tr DECIMAL(12,2); DECLARE v_ir DECIMAL(12,2);
DECLARE v_ok TINYINT;
SELECT rows_read, elapsed_us, sel_scan, tmp_disk, sort_rows, h_rnd_next, h_next, innodb_rows
INTO b_rows, b_us, b_scan, b_td, b_sr, b_rn, b_nx, b_ir FROM demo_metric WHERE tag = p_bad;
SELECT rows_read, elapsed_us, sel_scan, tmp_disk, sort_rows, h_rnd_next, h_next, innodb_rows
INTO g_rows, g_us, g_scan, g_td, g_sr, g_rn, g_nx, g_ir FROM demo_metric WHERE tag = p_good;
SET v_rr = IF(IFNULL(g_rows,0) = 0, NULL, ROUND(b_rows / g_rows, 2));
SET v_tr = IF(IFNULL(g_us,0) = 0, NULL, ROUND(b_us / g_us, 2));
SET v_ir = IF(IFNULL(g_ir,0) = 0, NULL, ROUND(b_ir / g_ir, 2));
SET v_ok = CASE p_rule
WHEN 'R' THEN IF(IFNULL(b_rows,0) >= IFNULL(g_rows,0), 1, 0)
WHEN 'I' THEN IF(IFNULL(b_ir,0) >= IFNULL(g_ir,0), 1, 0)
WHEN 'T' THEN IF(IFNULL(b_us,0) >= 2 * IFNULL(g_us,0), 1, 0)
ELSE 1 END;
DELETE FROM demo_result WHERE check_name = p_check;
INSERT INTO demo_result(check_name, chapter, label, bad_rows, good_rows, rows_ratio,
bad_us, good_us, time_ratio, bad_scan, good_scan, bad_tmpdisk,
bad_sortrows, passed, note)
VALUES (p_check, p_chapter, p_label, b_ir, g_ir, v_ir, b_us, g_us, v_tr,
b_scan, g_scan, b_td, b_sr, v_ok, p_note);
SELECT CONCAT('[', p_check, '] ', p_label) AS `-- 对照 --`,
CONCAT('引擎读行 ', FORMAT(b_ir,0), ' → ', FORMAT(g_ir,0),
' · Handler 读行 ', FORMAT(b_rows,0), ' → ', FORMAT(g_rows,0),
' · 耗时 ', FORMAT(b_us,0), ' µs → ', FORMAT(g_us,0), ' µs') AS `反模式 → 修正`,
CONCAT('引擎读行 ', IFNULL(CONCAT(ROUND(v_ir,2), '×'), '—'),
IFNULL(CONCAT(' · 耗时 ', ROUND(v_tr,2), '×'), ''),
IFNULL(CONCAT(' · Handler 读行 ', ROUND(v_rr,2), '×'), ''),
IF(b_scan > 0 AND g_scan = 0, ' · 反模式走了全表扫描', ''),
IF(b_rn > 1000 AND b_rn > g_rn * 5, CONCAT(' · 反模式物理顺序扫行 ', FORMAT(b_rn,0)), ''),
IF(b_td > 0 AND g_td = 0, CONCAT(' · 反模式临时表落盘 ', b_td, ' 次'), ''),
IF(b_sr > 0 AND g_sr = 0, CONCAT(' · 反模式 filesort ', FORMAT(b_sr,0), ' 行'), ''))
AS `判定`,
IF(v_ok = 1, '通过', '★未通过') AS `自检`;
END$$
-- 打印一条语句的 EXPLAIN（人读证据）
CREATE PROCEDURE demo_explain(IN p_sql LONGTEXT)
BEGIN
SET @e_sql = CONCAT('EXPLAIN ', p_sql);
PREPARE st_e FROM @e_sql;
EXECUTE st_e;
DEALLOCATE PREPARE st_e;
END$$
-- 打印一条语句的 EXPLAIN ANALYZE（真实行数 / 真实耗时）
CREATE PROCEDURE demo_explain_analyze(IN p_sql LONGTEXT)
BEGIN
SET @e_sql = CONCAT('EXPLAIN ANALYZE ', p_sql);
PREPARE st_e FROM @e_sql;
EXECUTE st_e;
DEALLOCATE PREPARE st_e;
END$$
-- 自检登记
CREATE PROCEDURE demo_v(IN p_name VARCHAR(120), IN p_ok TINYINT, IN p_detail VARCHAR(320))
BEGIN
-- ★ 注意：p_detail 可能为 NULL（例如上游 SELECT 因数据为空返回 NULL），
-- 而 demo_verify.detail 是 NOT NULL —— 必须兜底，否则整个脚本会在这里中断。
DECLARE v_detail VARCHAR(320);
SET v_detail = IFNULL(p_detail, '（无明细）');
DELETE FROM demo_verify WHERE check_name = p_name;
INSERT INTO demo_verify(check_name, passed, detail) VALUES (p_name, IFNULL(p_ok,0), v_detail);
SELECT CONCAT(IF(IFNULL(p_ok,0)=1, '[通过] ', '[★未通过] '), p_name, ' —— ', v_detail) AS `自检`;
END$$
-- ------------------------------------------------------------------------------------------
-- 错误探针：执行一条预期会报错的语句，捕获错误号与错误文本（不让脚本中断）
-- —— 排序规则/字符集不一致、非法数据等「硬失败」必须能被脚本安全地演示出来。
-- ------------------------------------------------------------------------------------------
CREATE PROCEDURE demo_err_probe(IN p_label VARCHAR(200), IN p_sql LONGTEXT)
BEGIN
DECLARE v_no INT DEFAULT 0;
DECLARE v_msg VARCHAR(300) DEFAULT '';
-- ★ 两个必须遵守的写法（实测踩坑）：
-- 1) 处理器必须声明在**过程最外层**，否则 PREPARE 阶段的语义错误（如排序规则冲突 1267）会击穿整个调用；
-- 2) 处理器体用「裸 GET DIAGNOSTICS」，不要包 BEGIN…END + IF —— 实测包起来后诊断区捕获不到，v_no 恒为 0。
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS CONDITION 1 v_no = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
SET @ep_sql = p_sql;
PREPARE st_ep FROM @ep_sql; -- 语法/语义错误通常在这里就抛出
IF v_no = 0 THEN
EXECUTE st_ep; -- 数据类错误在这里抛出
DEALLOCATE PREPARE st_ep; -- 只在 PREPARE 成功时才回收，避免 1243 覆盖真实错误号
END IF;
IF v_no = 0 THEN
SELECT CONCAT('[', p_label, '] 预期会报错，但实际没有报错') AS `-- 错误探针 --`;
-- ★ 同时登记到自检表，第 11 章的 V5 才能断言「硬错误确实被捕获」
INSERT INTO demo_verify(check_name, passed, detail)
VALUES (CONCAT('错误探针·', p_label), 0, '预期会报错但实际未报错')
ON DUPLICATE KEY UPDATE passed = 0, detail = '预期会报错但实际未报错';
ELSE
SELECT CONCAT('[', p_label, '] ★错误 ', v_no, ' : ', v_msg) AS `-- 错误探针 --`;
INSERT INTO demo_verify(check_name, passed, detail)
VALUES (CONCAT('错误探针·', p_label), 1, CONCAT('errno=', v_no, ' ', LEFT(v_msg, 200)))
ON DUPLICATE KEY UPDATE passed = 1, detail = VALUES(detail);
END IF;
END$$
DELIMITER ;
/* ==========================================================================================
§00.3 数据规模与结构底稿（写任何优化前，先把「我到底在优化什么」量出来）
------------------------------------------------------------------------------------------
数据库优化最常见的错误是「凭感觉调 SQL」。这一节把所有后续结论需要的底稿一次性摆出来：
行数、磁盘占用、索引清单与基数、字符集/排序规则分布。
========================================================================================== */
SELECT '§00.3.1 各表真实行数与占用（★ 真实行数用 COUNT(*)，information_schema 的 table_rows 是估算值）' AS `-- 底稿 --`;
SELECT t.表, t.真实行数, t.数据MB, t.索引MB, t.索引MB / NULLIF(t.数据MB,0) AS 索引数据比
FROM (
SELECT 'erp_sales_order' AS 表, (SELECT COUNT(*) FROM erp_sales_order) AS 真实行数,
ROUND(data_length/1024/1024) AS 数据MB, ROUND(index_length/1024/1024) AS 索引MB
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='erp_sales_order'
UNION ALL SELECT 'erp_sales_order_line', (SELECT COUNT(*) FROM erp_sales_order_line),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='erp_sales_order_line'
UNION ALL SELECT 'rpt_sales_fact', (SELECT COUNT(*) FROM rpt_sales_fact),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='rpt_sales_fact'
UNION ALL SELECT 'crm_interaction', (SELECT COUNT(*) FROM crm_interaction),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='crm_interaction'
UNION ALL SELECT 'hr_attendance', (SELECT COUNT(*) FROM hr_attendance),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='hr_attendance'
UNION ALL SELECT 'crm_customer', (SELECT COUNT(*) FROM crm_customer),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='crm_customer'
UNION ALL SELECT 'crm_customer_legacy', (SELECT COUNT(*) FROM crm_customer_legacy),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='crm_customer_legacy'
UNION ALL SELECT 'trap_join', (SELECT COUNT(*) FROM trap_join),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='trap_join'
UNION ALL SELECT 'erp_product_attr', (SELECT COUNT(*) FROM erp_product_attr),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='erp_product_attr'
UNION ALL SELECT 'erp_product', (SELECT COUNT(*) FROM erp_product),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='erp_product'
UNION ALL SELECT 'hr_employee', (SELECT COUNT(*) FROM hr_employee),
ROUND(data_length/1024/1024), ROUND(index_length/1024/1024)
FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name='hr_employee'
) t;
SELECT SUM(t.真实行数) AS 实验台总行数,
(SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema='JewelryAnalytics' AND table_type='BASE TABLE') AS 基础表数
FROM (
SELECT (SELECT COUNT(*) FROM erp_sales_order) AS 真实行数 UNION ALL
SELECT (SELECT COUNT(*) FROM erp_sales_order_line) UNION ALL
SELECT (SELECT COUNT(*) FROM rpt_sales_fact) UNION ALL
SELECT (SELECT COUNT(*) FROM crm_interaction) UNION ALL
SELECT (SELECT COUNT(*) FROM hr_attendance) UNION ALL
SELECT (SELECT COUNT(*) FROM crm_customer) UNION ALL
SELECT (SELECT COUNT(*) FROM crm_customer_legacy) UNION ALL
SELECT (SELECT COUNT(*) FROM trap_join) UNION ALL
SELECT (SELECT COUNT(*) FROM erp_product_attr) UNION ALL
SELECT (SELECT COUNT(*) FROM erp_product) UNION ALL
SELECT (SELECT COUNT(*) FROM hr_employee) UNION ALL
SELECT (SELECT COUNT(*) FROM hr_org_unit) UNION ALL
SELECT (SELECT COUNT(*) FROM ref_country)
) t;
SELECT '§00.3.2 订单主表索引清单与前导列基数（★ 前导列基数 = 该索引第一个列的不同值个数）' AS `-- 底稿 --`;
SELECT INDEX_NAME AS 索引, SEQ_IN_INDEX AS 序号, COLUMN_NAME AS 列,
CARDINALITY AS 基数, IF(SEQ_IN_INDEX=1, '★前导列', '') AS 标记
FROM information_schema.statistics
WHERE table_schema='JewelryAnalytics' AND table_name='erp_sales_order'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
SELECT '★ 关键观察：ix_so_country_date / ix_so_status_date / ix_so_channel_date 的前导列基数只有 3~4，'
'而 ix_so_store_date_cover 前导列基数 1201。前导列基数越低，"先定位" 的能力越弱，'
'优化器越可能干脆放弃这个索引（第 04 章会实测）。' AS `-- 底稿说明 --`;
SELECT '★ 另一个容易看错的点：information_schema 里的 CARDINALITY 是 InnoDB 持久化统计的**抽样估算值**，'
'与真实不同值个数不一致是正常现象。下面同时给出精确值对照。' AS `-- 底稿说明 --`;
SELECT 'country_code' AS 列, COUNT(DISTINCT country_code) AS 精确不同值 FROM erp_sales_order
UNION ALL SELECT 'status', COUNT(DISTINCT status) FROM erp_sales_order
UNION ALL SELECT 'channel', COUNT(DISTINCT channel) FROM erp_sales_order
UNION ALL SELECT 'store_code', COUNT(DISTINCT store_code) FROM erp_sales_order
UNION ALL SELECT 'customer_id', COUNT(DISTINCT customer_id) FROM erp_sales_order
UNION ALL SELECT 'order_no', COUNT(DISTINCT order_no) FROM erp_sales_order;
SELECT '§00.3.3 字符集与排序规则分布（MySQL 的 1267 硬错误根源）' AS `-- 底稿 --`;
SELECT '★ 注意：MySQL 的 GROUP_CONCAT 不支持 LIMIT（那是别的数据库的写法），最多用 SUBSTRING 截断。' AS `-- 坑位提示 --`;
SELECT CHARACTER_SET_NAME AS 字符集, COLLATION_NAME AS 排序规则,
COUNT(*) AS 列数,
LEFT(GROUP_CONCAT(DISTINCT CONCAT(TABLE_NAME,'.',COLUMN_NAME) ORDER BY TABLE_NAME SEPARATOR ', '), 120) AS 示例列_截断
FROM information_schema.columns
WHERE table_schema='JewelryAnalytics'
GROUP BY CHARACTER_SET_NAME, COLLATION_NAME ORDER BY 列数 DESC;
SELECT '§00.3.4 时间范围与分布（后续所有日期范围谓词都基于此）' AS `-- 底稿 --`;
SELECT MIN(order_date) AS 订单最早, MAX(order_date) AS 订单最晚,
TO_DAYS(MAX(order_date)) - TO_DAYS(MIN(order_date)) AS 跨度天
FROM erp_sales_order;
SELECT country_code AS 国家, COUNT(*) AS 订单数,
ROUND(100*COUNT(*)/(SELECT COUNT(*) FROM erp_sales_order),1) AS 占比
FROM erp_sales_order GROUP BY country_code ORDER BY 订单数 DESC;
SELECT status AS 状态, COUNT(*) AS 订单数 FROM erp_sales_order GROUP BY status ORDER BY 订单数 DESC;
SELECT '★ 上面这条统计只取 COUNT(*)，因为它在 ix_so_status_date(status, order_date) 上能走覆盖扫描。' AS `-- 底稿说明1 --`;
SELECT '★ 顺手加上 AVG(net_amount_usd) 会怎样？优化器仍然用同一个索引做分组，'
'但每读一个索引条目就要回表取一次净额 —— 200 万次随机回表。'
'本机 128MB 缓冲池装不下这张表，实测这条"多加一个聚合函数"的语句要 76 秒以上才出结果。'
'这就是「选择性列」在统计语句里的真实影响（原理见第 01 章 K1）。' AS `-- 底稿说明2 --`;
SELECT '§00.3.5 分区表结构（万亿级的第一件武器）' AS `-- 底稿 --`;
SELECT PARTITION_NAME AS 分区, PARTITION_DESCRIPTION AS 上界,
TABLE_ROWS AS 估行, ROUND(DATA_LENGTH/1024/1024) AS 数据MB
FROM information_schema.partitions
WHERE table_schema='JewelryAnalytics' AND table_name='rpt_sales_fact'
ORDER BY PARTITION_ORDINAL_POSITION;
/* ==========================================================================================
第 01 章 选择性列（Selective Columns）—— 只取需要的列，让索引有能力「覆盖」查询
------------------------------------------------------------------------------------------
一句话：MySQL 没有 INCLUDE 覆盖列，索引能覆盖什么，完全取决于你把哪些列**放进了索引键**。
于是「SELECT 的列表」不再只是客户端的事，它直接决定服务端要不要回表。
========================================================================================== */
USE JewelryAnalytics;
SET @old_sql_mode = @@sql_mode; -- 第 02 章会临时放宽 sql_mode，此处先备份，用完全部还原
CALL demo_reset(); -- 清空上一轮的度量结果，保证本脚本可反复运行
SELECT '§01.1 场景：门店/渠道销售明细导出，需要「状态 + 日期区间」的订单金额明细' AS `-- 第01章 --`;
SELECT '★ 关键前提：MySQL 的二级索引叶子节点只存「索引键列 + 主键」，没有 INCLUDE。'
'想让下面这条查询不回表，唯一办法是把净额/件数/币种也放进索引键里 —— 代价见 §01.5。' AS `-- 前提 --`;
-- 幂等新增临时覆盖索引
SET @has = (SELECT COUNT(*) FROM information_schema.statistics
WHERE table_schema=DATABASE() AND table_name='erp_sales_order' AND index_name='ix_demo_cover');
SET @ddl = IF(@has = 0,
'ALTER TABLE erp_sales_order ADD KEY ix_demo_cover (status, order_date, net_amount_usd, item_count, currency_code)',
'DO 0');
SET @t0 = NOW(6);
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SELECT CONCAT(' [DDL] 建覆盖索引 ix_demo_cover(status, order_date, net_amount_usd, item_count, currency_code)'
, ' 耗时 ', ROUND(TIMESTAMPDIFF(MICROSECOND,@t0,NOW(6))/1000000,1), ' 秒； '
, '★ 这就是 MySQL 里唯一的覆盖手段：把列塞进 B+ 树。'
, '它同时是「索引不是免费的」最直观的证据 —— 200 万行表加一列进索引要 8 秒。') AS `-- 建索引代价 --`;
SELECT '§01.2 K1 同一条件、同一张表，只差 SELECT 列表：全 18 列 vs 必要 5 列（单月 19,755 行）' AS `-- 第01章 --`;
-- 反模式：取回全部 18 列（与 SELECT * 在优化器眼里完全等价）
CALL demo_begin('K1_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_no,order_date,country_code,store_code,channel,customer_id,
sales_employee_id,status,currency_code,item_count,net_amount_usd,tax_amount_usd,
gross_amount_usd,discount_pct,is_export,note,created_at)
FROM erp_sales_order
WHERE status='Paid' AND order_date >= '2025-06-01' AND order_date < '2025-07-01';
CALL demo_end('K1_bad');
-- 修正：只取业务真正要用的 5 列，正好落在覆盖索引上
CALL demo_begin('K1_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',status,order_date,net_amount_usd,item_count,currency_code)
FROM erp_sales_order
WHERE status='Paid' AND order_date >= '2025-06-01' AND order_date < '2025-07-01';
CALL demo_end('K1_good');
CALL demo_report('K1_bad','K1_good','01',
'SELECT *（全 18 列）打穿覆盖索引 vs 只 SELECT 需要的 5 列',
'K1_选择性列_小结果集','T',
'★ 口径提示：本机实测发现「二级索引回表」不计入 Innodb_rows_read，所以这一对小结果集用例'
'的行数完全相同（1.00×），回表的代价只体现为随机 IO 的耗时 —— 因此改用耗时口径。');
SELECT '★ 上面这行一定要记住：InnoDB 在二级索引里找到主键后，是**引擎内部**再去聚簇索引取整行，'
'这一步既不增加 Handler_read_key、也不增加 Innodb_rows_read。'
'想量化回表，只能看「耗时」或「执行计划里的 Extra」。'
'这是 MySQL 与 SQL Server（有 logical reads 可看）在可观测性上的一个真实差异。' AS `-- 度量口径 --`;
SELECT '§01.3 K1b 同一对照放大到「中间量」（半年 23.8 万行）—— 优化器会干脆放弃索引去全表扫' AS `-- 第01章 --`;
CALL demo_begin('K1b_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_no,order_date,country_code,store_code,channel,customer_id,
sales_employee_id,status,currency_code,item_count,net_amount_usd,tax_amount_usd,
gross_amount_usd,discount_pct,is_export,note,created_at)
FROM erp_sales_order
WHERE status IN ('Paid','Shipped') AND order_date >= '2025-01-01' AND order_date < '2025-07-01';
CALL demo_end('K1b_bad');
CALL demo_begin('K1b_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',status,order_date,net_amount_usd,item_count,currency_code)
FROM erp_sales_order
WHERE status IN ('Paid','Shipped') AND order_date >= '2025-01-01' AND order_date < '2025-07-01';
CALL demo_end('K1b_good');
CALL demo_report('K1b_bad','K1b_good','01',
'SELECT *（全 18 列）vs 只 SELECT 5 列（半年 23.8 万行，中间量放大）',
'K1b_选择性列_中间量','I',
'★ 行数一放大，优化器的成本模型就翻脸：宽行的随机回表它觉得不值，于是退回全表扫描 200 万行。');
SELECT '§01.4 执行计划证据：同一张表、同一条件，只差列列表' AS `-- 第01章 --`;
CALL demo_explain('SELECT /* 全 18 列 */ order_id,order_no,order_date,country_code,store_code,channel,customer_id,
sales_employee_id,status,currency_code,item_count,net_amount_usd,tax_amount_usd,
gross_amount_usd,discount_pct,is_export,note,created_at
FROM erp_sales_order WHERE status=''Paid'' AND order_date >= ''2025-06-01'' AND order_date < ''2025-07-01''');
CALL demo_explain('SELECT /* 只取 5 列 */ status,order_date,net_amount_usd,item_count,currency_code
FROM erp_sales_order WHERE status=''Paid'' AND order_date >= ''2025-06-01'' AND order_date < ''2025-07-01''');
SELECT '★ 判读口诀（Extra 列）：'
'「Using index」= 覆盖索引，不回表；'
'「Using index condition」= 索引下推 ICP，仍要回表；'
'「Using MRR」= 回表被批量化（按主键排序后顺序取），能缓解但消不掉；'
'「Using where」不带 index = 全表扫描后逐行过滤。' AS `-- 判读口诀 --`;
SELECT '§01.5 代价兑现：为覆盖而加宽索引，会把已有更窄的索引变成冗余索引' AS `-- 第01章 --`;
SELECT '★ ix_demo_cover(status, order_date, ...) 的前两列恰好等于 ix_so_status_date(status, order_date)，'
'后者从此可以被前者完全替代 —— 这就是「覆盖索引的隐性成本」。'
'MySQL 自带的 sys.schema_redundant_indexes 视图正是为此准备的体检项（第 09 章会把它做成作业）。' AS `-- 提示 --`;
SELECT table_name AS 表, redundant_index_name AS 冗余索引, redundant_index_columns AS 冗余列,
dominant_index_name AS 可替代它的索引, dominant_index_columns AS 覆盖列
FROM sys.schema_redundant_indexes
WHERE table_schema='JewelryAnalytics' AND table_name='erp_sales_order';
SELECT '§01.6 清理：删掉实验用的临时索引' AS `-- 第01章 --`;
SET @has = (SELECT COUNT(*) FROM information_schema.statistics
WHERE table_schema=DATABASE() AND table_name='erp_sales_order' AND index_name='ix_demo_cover');
SET @ddl = IF(@has > 0, 'ALTER TABLE erp_sales_order DROP KEY ix_demo_cover', 'DO 0');
SET @t0 = NOW(6);
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SELECT CONCAT(' [DDL] 删除 ix_demo_cover 耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND,@t0,NOW(6))/1000000,1),
' 秒。★ 万亿级表上「先加索引再删」不是免费动作：加索引要全表扫描 + 排序重建，'
'删索引要重建整棵 B+ 树，并且两者都会持有 MDL 写锁阻塞业务。') AS `-- 删除代价 --`;
/* ==========================================================================================
第 02 章 有效过滤之一：隐式转换 & 字符集 / 排序规则
------------------------------------------------------------------------------------------
★★★ 本章是 MySQL 与 SQL Server 差异最大、也最容易踩错的一章。三句话：
① 隐式转换是**有方向的**：`varchar 列 = 数字`（列转 double，索引报废）与
`int 列 = 字符串`（只转字面量一次，毫发无伤）的危害差 3 个数量级。
② 在 MySQL 8.0.16+ / 9.0 的**默认严格模式**下，前者连"慢"的机会都没有 ——
它直接报 ERROR 1292 Truncated incorrect DOUBLE value。比 SQL Server 更严。
③ 排序规则不一致是**硬错误 1267**，而字符集不一致（latin1 vs utf8mb4）却**可以执行**。
两者经常被混为一谈，但危害等级完全不同。
========================================================================================== */
SELECT '§02.0 场景：门店编码 / 客户号 / 订单号都是 VARCHAR 业务键，而应用层传来的参数类型五花八门' AS `-- 第02章 --`;
SELECT '★ 先看清 store_code 的真实长相（VARCHAR(8)，形如 ST0100）：' AS `-- 提示 --`;
SELECT MIN(store_code) AS 最小, MAX(store_code) AS 最大, COUNT(DISTINCT store_code) AS 不同值,
(SELECT COUNT(*) FROM erp_sales_order WHERE store_code='ST0100') AS ST0100行数
FROM erp_sales_order;
SELECT '§02.1 C1a 【严格模式】varchar 列 = 数字字面量 —— 不是慢，是直接报错 1292' AS `-- 第02章 --`;
CALL demo_err_probe('C1a_varchar列比数字_严格模式',
'INSERT INTO demo_sink(line) SELECT CONCAT_WS(''|'',order_id,store_code) FROM erp_sales_order WHERE store_code = 100');
SELECT '★ MySQL 8.0.16 起的行为变更：字符串列与数字比较时，引擎要把列值转成数字；'
'遇到 ''ST0100'' 这种转不了的值，在严格 sql_mode 下 ER_TRUNCATED_WRONG_VALUE(1292) 从警告升级为错误。'
'所以在 MySQL 9.0 上，这个"反模式"的第一后果是**功能故障**，不是性能问题。' AS `-- 结论 --`;
SELECT '§02.2 C1b 【放宽 sql_mode 后】同一句话变成"只出警告"，但索引彻底报废 → 全表扫描' AS `-- 第02章 --`;
SET SESSION sql_mode = '';
CALL demo_begin('C1b_bad');
INSERT INTO demo_sink(line) SELECT CONCAT_WS('|',order_id,store_code)
FROM erp_sales_order WHERE store_code = 100;
CALL demo_end('C1b_bad');
CALL demo_begin('C1b_good');
INSERT INTO demo_sink(line) SELECT CONCAT_WS('|',order_id,store_code)
FROM erp_sales_order WHERE store_code = 'ST0100';
CALL demo_end('C1b_good');
SET SESSION sql_mode = @old_sql_mode;
CALL demo_report('C1b_bad','C1b_good','02',
'C1b store_code = 100（数字） vs store_code = ''ST0100''（字符串）【sql_mode 已放宽】',
'C1_varchar列比数字','R',
'★ 列被转 double，"ST0100"→0 永不等于 100 → 匹配 0 行却扫完 200 万行。有索引等于没索引。');
CALL demo_explain('SELECT order_id FROM erp_sales_order WHERE store_code = 100');
SELECT '★ 请看 EXPLAIN：type=ALL、key=NULL。store_code 上其实**有**索引'
'（ix_so_store_date_cover 的前导列就是它）—— 但类型错了，索引就是一张废纸。' AS `-- 判读 --`;
SELECT '§02.3 C2 同一个坑换成预编译参数照样踩：模板一模一样，绑定类型决定生死' AS `-- 第02章 --`;
SET SESSION sql_mode = '';
CALL demo_begin('C2_bad');
SET @q1 = 100;
SET @s1 = 'INSERT INTO demo_sink(line) SELECT CONCAT_WS(''|'',order_id,store_code) FROM erp_sales_order WHERE store_code = ?';
PREPARE p1 FROM @s1; EXECUTE p1 USING @q1; DEALLOCATE PREPARE p1;
CALL demo_end('C2_bad');
CALL demo_begin('C2_good');
SET @q2 = 'ST0100';
SET @s2 = 'INSERT INTO demo_sink(line) SELECT CONCAT_WS(''|'',order_id,store_code) FROM erp_sales_order WHERE store_code = ?';
PREPARE p2 FROM @s2; EXECUTE p2 USING @q2; DEALLOCATE PREPARE p2;
CALL demo_end('C2_good');
SET SESSION sql_mode = @old_sql_mode;
CALL demo_report('C2_bad','C2_good','02',
'C2 预编译参数按数字绑定 vs 按字符串绑定（同一条 SQL 模板，仅绑定类型不同）',
'C2_绑定参数类型','R',
'★ 参数化查询本身是防注入的正确做法，但绑定类型必须与列类型一致；'
'否则 SQL 模板相同、执行计划天差地别 —— 这也是"同一接口有时快有时慢"的常见根因。');
SELECT '§02.4 C3 字符集不一致（latin1 列 vs utf8mb4 列）：可以执行，1.00× —— 不是灾难' AS `-- 第02章 --`;
SELECT '★ trap_join 表把同一批客户号分别存成 utf8mb4_0900_ai_ci / utf8mb4_general_ci / latin1 三列，'
'用来把「字符集不一致」与「排序规则不一致」两种陷阱严格分离。' AS `-- 提示 --`;
CALL demo_begin('C3_bad');
INSERT INTO demo_sink(line)
SELECT COUNT(*) FROM trap_join a JOIN trap_join b ON a.key_0900 = b.key_latin1;
CALL demo_end('C3_bad');
CALL demo_begin('C3_good');
INSERT INTO demo_sink(line)
SELECT COUNT(*) FROM trap_join a JOIN trap_join b ON a.key_0900 = b.key_0900;
CALL demo_end('C3_good');
CALL demo_report('C3_bad','C3_good','02',
'C3 utf8mb4 列 JOIN latin1 列 vs 同字符集自连接（两边各 30,000 行）',
'C3_字符集不一致可执行','N',
'★ 字符集不一致**不报错**，代价 1.00×（引擎在 latin1 侧仍能继续用索引）。真正危险的是排序规则。');
SELECT '§02.5 C4 排序规则不一致（0900_ai_ci vs general_ci）：★ 硬报错 1267，不是慢' AS `-- 第02章 --`;
CALL demo_err_probe('C4_列与列排序规则不一致',
'SELECT COUNT(*) FROM trap_join a JOIN trap_join b ON a.key_0900 = b.key_general');
SELECT '★ 这一条必须认清：MySQL 对「两个不同排序规则的列做 = 比较」是**直接拒绝执行**，'
'报 ERROR 1267 Illegal mix of collations。它不是性能问题，是可用性问题 —— 上线当天直接 500。'
' 触发场景极其常见：老系统（utf8mb4_general_ci）与新系统（utf8mb4_0900_ai_ci）同库并存，'
'或从 5.7 升级到 8.0/9.0 后新表用了新默认排序规则。' AS `-- 结论 --`;
SELECT '§02.6 C5 「消除 1267 的应急写法」本身就是反模式：把 COLLATE 裹在列上 → 索引失效' AS `-- 第02章 --`;
SELECT '★ 注意测法：必须把 COLLATE 裹在**被检索的那一侧**（等值查找的列）才测得出来。'
'如果裹在 JOIN 的被驱动表上，它本来就只做 eq_ref 单行定位，看不出差异 —— 这是设计对照实验时的常见陷阱。' AS `-- 测法 --`;
CALL demo_begin('C5_bad');
INSERT INTO demo_sink(line)
SELECT COUNT(*) FROM trap_join WHERE key_0900 COLLATE utf8mb4_general_ci = 'C000000001234';
CALL demo_end('C5_bad');
CALL demo_begin('C5_good');
INSERT INTO demo_sink(line)
SELECT COUNT(*) FROM trap_join WHERE key_0900 = 'C000000001234';
CALL demo_end('C5_good');
CALL demo_report('C5_bad','C5_good','02',
'C5 WHERE key_0900 COLLATE utf8mb4_general_ci = ''…'' vs WHERE key_0900 = ''…''（等值查找）',
'C5_COLLATE包列','R',
'★ 给列套 COLLATE 等价于给列套函数：索引键的排序规则与比较用的排序规则不再匹配，索引整棵作废。');
CALL demo_explain('SELECT rid FROM trap_join WHERE key_0900 COLLATE utf8mb4_general_ci = ''C000000001234''');
CALL demo_explain('SELECT rid FROM trap_join WHERE key_0900 = ''C000000001234''');
SELECT '★ 正确做法只有两条：① 在 DDL 层统一排序规则'
'（ALTER TABLE ... MODIFY col VARCHAR(n) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci）；'
'② 实在要救火，把 COLLATE 写在**字面量**上（''常量'' COLLATE ...），绝不能写在列上。' AS `-- 修法 --`;
SELECT '§02.7 C6 datetime 列 = 日期字符串：1.00× —— 与「日期别用字符串」的流行说法相反' AS `-- 第02章 --`;
CALL demo_begin('C6_bad');
INSERT INTO demo_sink(line)
SELECT COUNT(*) FROM erp_sales_order WHERE order_date >= '2025-06-01' AND order_date < '2025-07-01';
CALL demo_end('C6_bad');
CALL demo_begin('C6_good');
INSERT INTO demo_sink(line)
SELECT COUNT(*) FROM erp_sales_order WHERE order_date >= '2025-06-01 00:00:00' AND order_date < '2025-07-01 00:00:00';
CALL demo_end('C6_good');
CALL demo_report('C6_bad','C6_good','02',
'C6 order_date >= ''2025-06-01''（date 形态） vs >= ''2025-06-01 00:00:00''（datetime 形态）',
'C6_datetime比字符串','N',
'★ 字面量会按列的 datetime 类型解析，索引照用，两版 1.00×。真正贵的是把函数套在列上（见第 03 章）。');
SELECT '§02.8 C7 int 列 = 字符串字面量：1.00× —— 转换方向反过来就完全无害' AS `-- 第02章 --`;
CALL demo_begin('C7_bad');
INSERT INTO demo_sink(line) SELECT COUNT(*) FROM erp_sales_order WHERE customer_id = '12345';
CALL demo_end('C7_bad');
CALL demo_begin('C7_good');
INSERT INTO demo_sink(line) SELECT COUNT(*) FROM erp_sales_order WHERE customer_id = 12345;
CALL demo_end('C7_good');
CALL demo_report('C7_bad','C7_good','02',
'C7 customer_id = ''12345''（字符串） vs customer_id = 12345（数字）',
'C7_int列比字符串','N',
'★ 与 C1b 严格对照：这次被转换的是**字面量**（只转一次），列一侧保持原生类型，索引完好无损。');
SELECT '§02.9 C8 反向陷阱：把数字列 CAST 成字符串 —— 与 C1b 同样是全表扫描' AS `-- 第02章 --`;
CALL demo_begin('C8_bad');
INSERT INTO demo_sink(line) SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE CAST(order_id AS CHAR) = '12345';
CALL demo_end('C8_bad');
CALL demo_begin('C8_good');
INSERT INTO demo_sink(line) SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE order_id = 12345;
CALL demo_end('C8_good');
CALL demo_report('C8_bad','C8_good','02',
'C8 CAST(order_id AS CHAR) = ''12345'' vs order_id = 12345（主键直接定位）',
'C8_CAST包数字列','R',
'★ 任何把列包进函数的写法都等价于「索引失效」，与包的是 CAST / DATE / YEAR / LEFT 无关。');
SELECT '§02.10 第 02 章判决表：哪些必须救、哪些根本不用救' AS `-- 第02章 --`;
SELECT '写法' AS `写法`, '是否报错' AS `报错?`, '索引是否可用' AS `索引?`, '判决' AS `判决`
UNION ALL SELECT 'varchar 列 = 数字字面量（严格模式）', '★ ERROR 1292', '无法执行', '必修 —— 这是功能故障，不是性能问题'
UNION ALL SELECT 'varchar 列 = 数字字面量（宽松模式）', '否（仅警告）','★ 失效（列转 double）','必修 —— 全表扫描'
UNION ALL SELECT 'varchar 列 = 数字绑定参数', '同上', '★ 失效', '必修 —— 改参数绑定类型'
UNION ALL SELECT 'int 列 = 字符串字面量', '否', '可用（只转字面量）', '不用救 —— 1.00×'
UNION ALL SELECT 'datetime 列 = 日期字符串', '否', '可用（字面量转 date）','不用救 —— 1.00×'
UNION ALL SELECT '字符集不一致（latin1 vs utf8mb4）', '否', '可用', '不用救 —— 1.00×'
UNION ALL SELECT '排序规则不一致（0900 vs general）','★ ERROR 1267','无法执行', '★ 必须在 DDL 层统一'
UNION ALL SELECT '谓词里给列套 COLLATE', '否', '★ 失效', '必修 —— 改 DDL，不是改 SQL'
UNION ALL SELECT 'CAST(数字列 AS CHAR)', '否', '★ 失效', '必修 —— 与 C1b 同源';
/* ==========================================================================================
第 03 章 有效过滤之二：非 SARGable 谓词（把函数 / 运算套在列上）
------------------------------------------------------------------------------------------
SARGable = Search ARGument able：谓词能不能直接交给存储引擎去做**范围定位**。
只要列被函数包住，B+ 树上的有序性就用不上了 → 退化为逐行扫描 + 逐行求值。
本章按「代价从大到小」排列，让你一眼看出该优先修哪一类。
========================================================================================== */
USE JewelryAnalytics;
SELECT '§03.0 场景：财务报表的口径往往写成「2025 年 6 月」「6 月 15 日那一天」，于是函数就套上去了' AS `-- 第03章 --`;
/* ------------------------------------------------------------------------------------------
S1 YEAR()/MONTH()
------------------------------------------------------------------------------------------ */
SELECT '§03.1 S1 WHERE YEAR(col)=2025 AND MONTH(col)=6 → 半开区间' AS `-- 第03章 --`;
CALL demo_begin('S1_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order
WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 6;
CALL demo_end('S1_bad');
CALL demo_begin('S1_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order
WHERE order_date >= '2025-06-01' AND order_date < '2025-07-01';
CALL demo_end('S1_good');
CALL demo_report('S1_bad','S1_good','03',
'S1 YEAR(col)=2025 AND MONTH(col)=6 vs col >= ''2025-06-01'' AND col < ''2025-07-01''',
'S1_YEAR_MONTH包列','R',
'★ 半开区间 [起, 次月1日) 是三段式中最不容易写错的形式，天然规避月末/闰年/时区边界。');
CALL demo_explain('SELECT order_id FROM erp_sales_order WHERE YEAR(order_date)=2025 AND MONTH(order_date)=6');
/* ------------------------------------------------------------------------------------------
S2 前导通配 LIKE
------------------------------------------------------------------------------------------ */
SELECT '§03.2 S2 LIKE ''%12345%''（前导通配） → LIKE ''SO…%''（前缀匹配）' AS `-- 第03章 --`;
CALL demo_begin('S2_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE order_no LIKE '%12345%';
CALL demo_end('S2_bad');
CALL demo_begin('S2_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE order_no LIKE 'SO0000000012345%';
CALL demo_end('S2_good');
CALL demo_report('S2_bad','S2_good','03',
'S2 order_no LIKE ''%12345%'' vs order_no LIKE ''SO0000000012345%''',
'S2_前导通配LIKE','R',
'★ 只有前缀形式的 LIKE 才能转成索引范围扫描；一旦以 % 开头，B+ 树的有序性直接作废。');
CALL demo_explain('SELECT order_id FROM erp_sales_order WHERE order_no LIKE ''%12345%''');
CALL demo_explain('SELECT order_id FROM erp_sales_order WHERE order_no LIKE ''SO0000000012345%''');
SELECT '★ 补一句：真正要给「中间内容」做检索，MySQL 的正解是 FULLTEXT 索引'
'（MATCH ... AGAINST）或外部检索引擎，而不是把 % 往前挪。' AS `-- 修法 --`;
/* ------------------------------------------------------------------------------------------
S3 LEFT()/SUBSTRING()
------------------------------------------------------------------------------------------ */
SELECT '§03.3 S3 WHERE LEFT(store_code,5)=''ST010'' → WHERE store_code LIKE ''ST010%''' AS `-- 第03章 --`;
CALL demo_begin('S3_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE LEFT(store_code,5) = 'ST010';
CALL demo_end('S3_bad');
CALL demo_begin('S3_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE store_code LIKE 'ST010%';
CALL demo_end('S3_good');
CALL demo_report('S3_bad','S3_good','03',
'S3 LEFT(store_code,5)=''ST010'' vs store_code LIKE ''ST010%''',
'S3_LEFT包列','R',
'★ 同一个语义、同一个结果集，只因为「包了函数」就从索引定位退化成全表扫描。');
/* ------------------------------------------------------------------------------------------
S4 DATE()
------------------------------------------------------------------------------------------ */
SELECT '§03.4 S4 WHERE DATE(col)=''2025-06-15'' → 半开区间（本章最贵的一条）' AS `-- 第03章 --`;
CALL demo_begin('S4_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE DATE(order_date) = '2025-06-15';
CALL demo_end('S4_bad');
CALL demo_begin('S4_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order
WHERE order_date >= '2025-06-15' AND order_date < '2025-06-16';
CALL demo_end('S4_good');
CALL demo_report('S4_bad','S4_good','03',
'S4 DATE(order_date)=''2025-06-15'' vs order_date >= ''2025-06-15'' AND < ''2025-06-16''',
'S4_DATE包列','R',
'★ 单日查询是报表最常用的口径，也是「包列」里代价最大的一条：结果集最小，扫描量最大。');
/* ------------------------------------------------------------------------------------------
S5 UPPER()/LOWER()
------------------------------------------------------------------------------------------ */
SELECT '§03.5 S5 WHERE UPPER(status)=''PAID'' → WHERE status=''Paid''（存值时就统一大小写）' AS `-- 第03章 --`;
CALL demo_begin('S5_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE UPPER(status) = 'PAID';
CALL demo_end('S5_bad');
CALL demo_begin('S5_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE status = 'Paid';
CALL demo_end('S5_good');
CALL demo_report('S5_bad','S5_good','03',
'S5 UPPER(status)=''PAID'' vs status=''Paid''（原值匹配）',
'S5_UPPER包列','R',
'★ 注意倍率只有 3 倍级 —— 因为结果集占全表 30%。这说明「倍率 = 扫描量 / 结果集」，'
'结果集越大，包列的相对损失越小，但绝对耗时依然是全表扫描的量级。');
/* ------------------------------------------------------------------------------------------
S6 OR 跨列 → UNION ALL：流行说法一定对吗？
------------------------------------------------------------------------------------------ */
SELECT '§03.6 S6 OR 跨两个不同列 → UNION ALL 改写：实测可能**更慢**' AS `-- 第03章 --`;
CALL demo_begin('S6_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order
WHERE country_code = 'CN' OR status = 'Returned';
CALL demo_end('S6_bad');
CALL demo_begin('S6_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE country_code = 'CN'
UNION ALL
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE status = 'Returned' AND country_code <> 'CN';
CALL demo_end('S6_good');
CALL demo_report('S6_bad','S6_good','03',
'S6 WHERE country_code=''CN'' OR status=''Returned'' vs 等价的 UNION ALL 改写',
'S6_OR跨列','N',
'★ 结论：「UNION ALL 一定更快」不成立。请看三个数字的方向 —— OR 版直接全表扫描 200 万行，'
'UNION ALL 版引擎读行虽少 40%，但 Handler 层读行反而更多、整体耗时反而更长。改写没有收益。');
CALL demo_explain('SELECT order_id FROM erp_sales_order WHERE country_code = ''CN'' OR status = ''Returned''');
SELECT '★ 真正需要 UNION ALL 改写的是「同一个列的不同区间被 OR 连起来」这类场景；'
'跨列 OR 请先看 EXPLAIN 再决定，别条件反射地拆。' AS `-- 判读 --`;
/* ------------------------------------------------------------------------------------------
S7 超长 IN 列表
------------------------------------------------------------------------------------------ */
SELECT '§03.7 S7 超长 IN 列表（300 个值）vs 等价区间 —— 「长 IN」到底是不是原罪？' AS `-- 第03章 --`;
SELECT @@eq_range_index_dive_limit AS IN列表走索引探测的阈值,
'★ 当 IN 列表的值个数 > 这个阈值时，优化器放弃逐个值做 index dive，改用索引统计估算。' AS `-- 机制 --`;
SET @list = (
WITH RECURSIVE c(n) AS (SELECT 1000 UNION ALL SELECT n+1 FROM c WHERE n < 1299)
SELECT GROUP_CONCAT(n ORDER BY n SEPARATOR ',') FROM c);
SELECT CHAR_LENGTH(@list) AS IN列表字符长度, '本次 IN 列表含 300 个连续整数（1000..1299）' AS 说明;
-- ★ 先证明「两种写法结果集完全相同」——否则对照实验不成立
SELECT (SELECT COUNT(*) FROM erp_sales_order WHERE customer_id >= 1000 AND customer_id <= 1299) AS 区间写法命中行数;
SET @sql7c = CONCAT('SELECT COUNT(*) AS IN列表写法命中行数 FROM erp_sales_order WHERE customer_id IN (', @list, ')');
PREPARE p7c FROM @sql7c; EXECUTE p7c; DEALLOCATE PREPARE p7c;
SET @sql7 = CONCAT('INSERT INTO demo_sink(line) SELECT CONCAT_WS(''|'',order_id) FROM erp_sales_order WHERE customer_id IN (', @list, ')');
CALL demo_begin('S7_bad');
PREPARE p7 FROM @sql7; EXECUTE p7; DEALLOCATE PREPARE p7;
CALL demo_end('S7_bad');
CALL demo_begin('S7_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE customer_id >= 1000 AND customer_id <= 1299;
CALL demo_end('S7_good');
CALL demo_report('S7_bad','S7_good','03',
'S7 300 个值的 IN 列表（超过 index dive 阈值 200） vs 等价的 BETWEEN 区间',
'S7_超长IN列表','N',
'★ 结论：300 个值这一量级完全正常，两版行数一致。「长 IN 列表必然导致坏计划」不成立 —— '
'阈值只影响估算精度，优化器仍会选范围扫描。真正的风险在语法解析开销与 SQL 长度上限。');
/* ------------------------------------------------------------------------------------------
S8 冗余谓词
------------------------------------------------------------------------------------------ */
SELECT '§03.8 S8 冗余谓词（ORM 自动生成的"安全网"条件）—— 是否真的有害？' AS `-- 第03章 --`;
CALL demo_begin('S8_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order
WHERE country_code = 'CN' AND country_code IN ('CN','US','HK');
CALL demo_end('S8_bad');
CALL demo_begin('S8_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order WHERE country_code = 'CN';
CALL demo_end('S8_good');
CALL demo_report('S8_bad','S8_good','03',
'S8 country_code=''CN'' AND country_code IN (''CN'',''US'',''HK'') vs 只留 country_code=''CN''',
'S8_冗余谓词','N',
'★ 结论：完全无害。优化器会把冗余等值谓词折叠掉，两版走完全相同的计划。不用花时间清理这类条件。');
/* ------------------------------------------------------------------------------------------
S9 索引条件下推 ICP：不是银弹
------------------------------------------------------------------------------------------ */
SELECT '§03.9 S9 索引条件下推（ICP）开 / 关：它一定能加速吗？' AS `-- 第03章 --`;
SELECT '★ 测法：构造一条「第 1 个索引列等值 + 后面的索引列只用来过滤」的查询。'
'这类查询正是 ICP 的设计目标 —— 让引擎在索引层就把不匹配的行剔掉，减少回表。'
'★ 归因纪律：只切换 ICP 开关，**保持 MRR 等其它开关不变**，否则测出来的倍率无法归因（上一章 S6 的教训）。' AS `-- 测法 --`;
SET SESSION optimizer_switch = 'index_condition_pushdown=off,mrr=on,derived_merge=on,skip_scan=on';
CALL demo_begin('S9_off');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,status,currency_code) FROM erp_sales_order
WHERE store_code = 'ST0100' AND status = 'Paid' AND currency_code = 'CNY';
CALL demo_end('S9_off');
SET SESSION optimizer_switch = 'index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
CALL demo_begin('S9_on');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,status,currency_code) FROM erp_sales_order
WHERE store_code = 'ST0100' AND status = 'Paid' AND currency_code = 'CNY';
CALL demo_end('S9_on');
CALL demo_report('S9_off','S9_on','03',
'S9 ICP 关闭 vs 开启（同一查询：store_code 等值 + status/currency_code 仅用于过滤）',
'S9_ICP开与关','N',
'★ 结论：请交叉看三个口径 —— 引擎读行不变、Handler 读行不变，但耗时可能明显变化。'
'因为 ICP 省掉的是「回表」，而回表在 MySQL 里既不计入 Handler 也不计入 Innodb_rows_read（见 §01.2）。'
'单看任何一个计数器都会误判，必须 计数器 + EXPLAIN Extra + 耗时 三者交叉验证。');
CALL demo_explain('SELECT order_id,status,currency_code FROM erp_sales_order
WHERE store_code = ''ST0100'' AND status = ''Paid'' AND currency_code = ''CNY''');
SELECT '§03.10 第 03 章判决表（按实测代价从大到小）' AS `-- 第03章 --`;
SELECT 序号, 反模式写法, 修正写法, 本机实测行数倍数 FROM (
SELECT 1 AS 序号, 'WHERE DATE(col)=''某日''' AS 反模式写法, '半开区间 [d, d+1)' AS 修正写法,
(SELECT rows_ratio FROM demo_result WHERE check_name='S4_DATE包列') AS 本机实测行数倍数
UNION ALL SELECT 2, 'LIKE ''%关键字%''', 'LIKE ''前缀%'' / FULLTEXT',
(SELECT rows_ratio FROM demo_result WHERE check_name='S2_前导通配LIKE')
UNION ALL SELECT 3, 'LEFT(col,n)=''xx''', 'col LIKE ''xx%''',
(SELECT rows_ratio FROM demo_result WHERE check_name='S3_LEFT包列')
UNION ALL SELECT 4, 'YEAR(col)= / MONTH(col)=', '半开区间 [月初, 次月初)',
(SELECT rows_ratio FROM demo_result WHERE check_name='S1_YEAR_MONTH包列')
UNION ALL SELECT 5, 'UPPER(col)=''XX''', '写入时统一大小写',
(SELECT rows_ratio FROM demo_result WHERE check_name='S5_UPPER包列')
) t ORDER BY 序号;
/* ==========================================================================================
第 04 章 支撑索引：复合索引的列序、前导列基数、以及索引自身的成本
------------------------------------------------------------------------------------------
三条铁律（本章逐条实测）：
① 最左前缀：复合索引只能从第 1 列开始被使用。
② 等值列在前、范围列在后：范围列一旦出现在前面，它右边的列就退化成"只能回表后过滤"。
③ 前导列的选择性决定优化器**愿不愿意用**这个索引 —— 基数太低的索引会被直接忽略。
★ 方法论：为了把「列序」这一个变量单独暴露出来，IX1 的两侧都用 FORCE INDEX 固定访问路径；
否则优化器会自己挑那个好的，实验就什么都看不到了。
========================================================================================== */
USE JewelryAnalytics;
SELECT '§04.1 场景：跨境报表要按「国家 + 月份」出数，表上有 (country_code, order_date) 与 (order_date) 两个索引' AS `-- 第04章 --`;
SELECT INDEX_NAME AS 索引, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS 列序
FROM information_schema.statistics
WHERE table_schema='JewelryAnalytics' AND table_name='erp_sales_order'
AND INDEX_NAME IN ('ix_so_country_date','ix_so_date','ix_so_store_date_cover','ix_so_status_date','ix_so_channel_date')
GROUP BY INDEX_NAME;
/* ------------------------------------------------------------------------------------------
IX1 范围列在前 vs 等值列在前
------------------------------------------------------------------------------------------ */
SELECT '§04.2 IX1 同一个查询，两种索引列序：(order_date) vs (country_code, order_date)' AS `-- 第04章 --`;
SELECT '★ 条件：country_code=''JP''（占 10%）+ 2025 年 6 月（占 3.3%）。'
'等值列在前 → 索引同时用上两个条件，只读 JP 的 6 月；'
'范围列在前 → 索引只能定位到"所有国家的 6 月"，再逐行回表过滤国家。' AS `-- 条件 --`;
CALL demo_begin('IX1_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order FORCE INDEX (ix_so_date)
WHERE country_code = 'JP' AND order_date >= '2025-06-01' AND order_date < '2025-07-01';
CALL demo_end('IX1_bad');
CALL demo_begin('IX1_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order FORCE INDEX (ix_so_country_date)
WHERE country_code = 'JP' AND order_date >= '2025-06-01' AND order_date < '2025-07-01';
CALL demo_end('IX1_good');
CALL demo_report('IX1_bad','IX1_good','04',
'IX1 索引列序：(order_date) 在前 vs (country_code, order_date) 等值列在前',
'IX1_索引列序','R',
'★ 结论：同样的 WHERE、同样的结果集，只因为索引里"范围列在前面"，多读 10 倍的行。'
'建复合索引时永远把等值条件列放前面，范围条件列放最后。');
CALL demo_explain('SELECT order_id FROM erp_sales_order FORCE INDEX (ix_so_date)
WHERE country_code=''JP'' AND order_date >= ''2025-06-01'' AND order_date < ''2025-07-01''');
CALL demo_explain('SELECT order_id FROM erp_sales_order FORCE INDEX (ix_so_country_date)
WHERE country_code=''JP'' AND order_date >= ''2025-06-01'' AND order_date < ''2025-07-01''');
/* ------------------------------------------------------------------------------------------
IX2 前导列基数：决定优化器「愿不愿意用」这个索引
------------------------------------------------------------------------------------------ */
SELECT '§04.3 IX2 前导列基数决定索引是否被采纳 —— 有索引 ≠ 会用索引' AS `-- 第04章 --`;
SELECT '★ 精确不同值对照（information_schema 的 CARDINALITY 是抽样估算值，这里给精确值）：' AS `-- 前置 --`;
SELECT 'country_code' AS 列, COUNT(DISTINCT country_code) AS 精确不同值, '200 万行 / 4 值 = 每值 50 万行' AS 说明 FROM erp_sales_order
UNION ALL SELECT 'status', COUNT(DISTINCT status), '200 万 / 10 值 = 每值 20 万行' FROM erp_sales_order
UNION ALL SELECT 'channel', COUNT(DISTINCT channel), '200 万 / 4 值 = 每值 50 万行' FROM erp_sales_order
UNION ALL SELECT 'store_code', COUNT(DISTINCT store_code), '200 万 / 1200 值 = 每值约 1660 行' FROM erp_sales_order
UNION ALL SELECT 'customer_id', COUNT(DISTINCT customer_id), '每值约 10 行' FROM erp_sales_order;
SELECT '★ 请看下面两条语句的 EXPLAIN：一条明明有索引却 key=NULL，另一条乖乖用索引。'
'区别不在"有没有索引"，而在"前导列能否把结果集缩小到值得回表"。' AS `-- 提示 --`;
CALL demo_explain('SELECT order_id,order_date FROM erp_sales_order WHERE channel = ''Outlet''');
CALL demo_explain('SELECT order_id,order_date FROM erp_sales_order WHERE store_code = ''ST0100''');
SELECT '★ 这两条语句的执行计划差异，就是"低基数前导列"的判决书：'
'channel 上确实有 ix_so_channel_date(channel, order_date)，但 channel 只有 4 个值、'
'单值占全表 25%，优化器算完账宁可全表扫描；store_code 有 1200 个值、单值占 0.083%，索引立刻被采纳。' AS `-- 结论 --`;
/* ------------------------------------------------------------------------------------------
索引的元数据体检：冗余与未使用
------------------------------------------------------------------------------------------ */
SELECT '§04.4 索引体检之一：冗余索引（sys.schema_redundant_indexes）' AS `-- 第04章 --`;
SELECT '★ 冗余 = 某个索引的全部列（按顺序）是另一个索引的前缀。这类索引对查询没有任何增益，只拖慢写入。' AS `-- 说明 --`;
SELECT table_name AS 表, redundant_index_name AS 冗余索引, redundant_index_columns AS 冗余列,
dominant_index_name AS 可替代索引, dominant_index_columns AS 覆盖列
FROM sys.schema_redundant_indexes
WHERE table_schema='JewelryAnalytics'
ORDER BY table_name, redundant_index_name;
SELECT '§04.5 索引体检之二：从未被使用过的索引（sys.schema_unused_indexes）' AS `-- 第04章 --`;
SELECT '★ 注意：这个视图的口径是"实例重启以来没有被任何语句用过"，它**只是线索不是判决**。'
'月末报表类索引可能一个月才用一次，绝不能因为这里出现就删。'
'另外 UNIQUE 索引、支撑外键的索引即使"未使用"也不能删 —— 它们承担的是约束职责。' AS `-- 重要提醒 --`;
SELECT object_schema AS 库, object_name AS 表, index_name AS 索引
FROM sys.schema_unused_indexes
WHERE object_schema='JewelryAnalytics'
ORDER BY object_name, index_name;
/* ------------------------------------------------------------------------------------------
索引的物理成本
------------------------------------------------------------------------------------------ */
SELECT '§04.6 索引不是免费的：看看索引占了多少空间' AS `-- 第04章 --`;
SELECT t.table_name AS 表, ROUND(t.data_length/1024/1024) AS 数据MB, ROUND(t.index_length/1024/1024) AS 索引MB,
ROUND(100*t.index_length/NULLIF(t.data_length+t.index_length,0),1) AS 索引占比百分比,
COUNT(DISTINCT s.index_name) AS 索引个数
FROM information_schema.tables t
JOIN information_schema.statistics s
ON s.table_schema=t.table_schema AND s.table_name=t.table_name
WHERE t.table_schema='JewelryAnalytics' AND t.table_name IN
('erp_sales_order','erp_sales_order_line','crm_customer','crm_interaction','rpt_sales_fact')
GROUP BY t.table_name, t.data_length, t.index_length
ORDER BY t.index_length DESC;
SELECT '★ 结论：erp_sales_order 的 6 个二级索引占用空间接近数据本身。'
'在万亿级场景里，每多一个索引都意味着：写入放大、DDL 窗口变长、缓冲池被挤占。'
'"按查询建索引"必须和"删掉没用的索引"成对出现。' AS `-- 结论 --`;
/* ==========================================================================================
第 05 章 排序、分组、分页 —— 「倍数之王」
说明：本文件已从原来的 ch04+ch05 合体中拆分，第 04 章内容见 _m06_index.sql
========================================================================================== */
/* ==========================================================================================
第 05 章 排序、分组、分页 —— 「倍数之王」
------------------------------------------------------------------------------------------
本章的四个对照全部采用同一个方法论：**先在没有支撑索引时测，再加索引后测**，
保证两侧是同一个查询、同一个结果集，把变量严格锁定在"有没有可用的索引顺序"上。
========================================================================================== */
USE JewelryAnalytics;
SELECT '§05.1 场景：跨国报表的三种典型操作 —— 取金额 Top20 / 按复合口径排序 / 深分页翻页' AS `-- 第05章 --`;
/* ------------------------------------------------------------------------------------------
Phase A：先测「没有支撑索引」时的表现
------------------------------------------------------------------------------------------ */
SELECT '§05.2 O1 取金额 Top20：ORDER BY 的列不在任何索引顺序里 → filesort' AS `-- 第05章 --`;
SELECT ' ★ 注意：这是「故意慢」的坏版本，要对 80 万行 CN 数据 filesort（本机 128M buffer pool 会落盘）。' AS `-- 提示 --`;
SELECT ' 在 Workbench 默认 30 秒超时下会报 Error 2013；请整份跑 / 调高超时到 600 / 或用 mysql.exe。' AS `-- 提示 --`;
CALL demo_begin('O1_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,net_amount_usd) FROM erp_sales_order
WHERE country_code = 'CN' ORDER BY net_amount_usd DESC LIMIT 20;
CALL demo_end('O1_bad');
SELECT '§05.3 O2 复合排序方向不一致：ORDER BY order_date ASC, net_amount_usd DESC' AS `-- 第05章 --`;
CALL demo_begin('O2_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_date,net_amount_usd) FROM erp_sales_order
WHERE country_code = 'CN' ORDER BY order_date ASC, net_amount_usd DESC LIMIT 20;
CALL demo_end('O2_bad');
SELECT '§05.4 O3 GROUP BY 的两列没有联合索引 → 临时表 + filesort + 全表扫描' AS `-- 第05章 --`;
CALL demo_begin('O3_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',status,country_code,COUNT(*)) FROM erp_sales_order
GROUP BY status, country_code;
CALL demo_end('O3_bad');
SELECT '§05.5 Phase A 结束，现在一次性建立三个支撑索引（一次 ALTER，只扫一遍表）' AS `-- 第05章 --`;
SET @has = (SELECT COUNT(*) FROM information_schema.statistics
WHERE table_schema=DATABASE() AND table_name='erp_sales_order' AND index_name='ix_demo_c_net');
SET @ddl = IF(@has = 0,
'ALTER TABLE erp_sales_order ADD KEY ix_demo_c_net(country_code, net_amount_usd), ADD KEY ix_demo_c_date_netdesc(country_code, order_date, net_amount_usd DESC), ADD KEY ix_demo_status_country(status, country_code), ADD KEY ix_demo_status_id(status, order_id)',
'DO 0');
SET @t0 = NOW(6);
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SELECT CONCAT(' [DDL] 一次 ALTER 新增 4 个实验索引（含一个 DESC 索引），耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND,@t0,NOW(6))/1000000,1), ' 秒') AS `-- 建索引代价 --`;
SELECT INDEX_NAME AS 索引, GROUP_CONCAT(CONCAT(COLUMN_NAME, IF(COLLATION='D',' DESC','')) ORDER BY SEQ_IN_INDEX) AS 列序
FROM information_schema.statistics
WHERE table_schema='JewelryAnalytics' AND table_name='erp_sales_order' AND INDEX_NAME LIKE 'ix_demo%'
GROUP BY INDEX_NAME;
/* ------------------------------------------------------------------------------------------
Phase B：重测同样的查询
------------------------------------------------------------------------------------------ */
SELECT '§05.6 O1（修正）同一条 SQL，现在有 (country_code, net_amount_usd) 可用' AS `-- 第05章 --`;
CALL demo_begin('O1_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,net_amount_usd) FROM erp_sales_order
WHERE country_code = 'CN' ORDER BY net_amount_usd DESC LIMIT 20;
CALL demo_end('O1_good');
CALL demo_report('O1_bad','O1_good','05',
'O1 ORDER BY net_amount_usd DESC LIMIT 20：无支撑索引 vs 有 (country_code, net_amount_usd)',
'O1_ORDER_BY无索引','R',
'★ 结论：没有索引时，MySQL 必须把 80 万行全部取出来排序（filesort）才可能知道前 20 名是谁。');
CALL demo_explain('SELECT order_id,net_amount_usd FROM erp_sales_order WHERE country_code=''CN'' ORDER BY net_amount_usd DESC LIMIT 20');
SELECT '§05.7 O2（修正）同一个查询，改用 DESC 索引 (country_code, order_date, net_amount_usd DESC)' AS `-- 第05章 --`;
CALL demo_begin('O2_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_date,net_amount_usd) FROM erp_sales_order
WHERE country_code = 'CN' ORDER BY order_date ASC, net_amount_usd DESC LIMIT 20;
CALL demo_end('O2_good');
CALL demo_report('O2_bad','O2_good','05',
'O2 ORDER BY order_date ASC, net_amount_usd DESC：普通升序索引 vs 带 DESC 的复合索引',
'O2_ORDER方向不一致','R',
'★ 结论：MySQL 8.0 起支持真正的降序索引（KEY (... , col DESC)）。'
'多列排序方向不一致时，只有方向完全匹配的索引才能免除排序。');
CALL demo_explain('SELECT order_id,order_date,net_amount_usd FROM erp_sales_order WHERE country_code=''CN'' ORDER BY order_date ASC, net_amount_usd DESC LIMIT 20');
SELECT '§05.8 O3（修正）同一句 GROUP BY，现在有 (status, country_code) 可用' AS `-- 第05章 --`;
CALL demo_begin('O3_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',status,country_code,COUNT(*)) FROM erp_sales_order
GROUP BY status, country_code;
CALL demo_end('O3_good');
CALL demo_report('O3_bad','O3_good','05',
'O3 GROUP BY status, country_code：无联合索引 vs 有 (status, country_code)',
'O3_GROUPBY临时表','R',
'★ 结论：GROUP BY 的列与索引顺序一致时，索引变成覆盖索引，于是免掉「临时表 + filesort」——'
'Handler 层读行从 4,000,026 降到 2,000,017（2.00×），耗时约降到一半（实测 1.89~2.35×，受缓冲池冷热影响）。'
'★ 但引擎读行是 1.00×：本机优化器选的是**紧凑索引扫描**（type=index, Extra=Using index），'
'而不是很多人以为一定会走的**松散索引扫描**（Using index for group-by）。'
'所以这条用例的收益只能用 Handler 读行与耗时衡量，不能用引擎读行衡量。');
CALL demo_explain('SELECT status,country_code,COUNT(*) FROM erp_sales_order GROUP BY status, country_code');
SELECT '★ 判读：EXPLAIN 的 Extra 里如果出现「Using temporary; Using filesort」，几乎可以直接判定为待优化项；'
'出现「Using index」说明分组已经能在索引上完成；'
'而「Using index for group-by」才是松散索引扫描 —— 它并不总会被优化器选中，别默认它一定发生。' AS `-- 判读 --`;
SELECT '§05.9 O4 深分页 LIMIT 500000, 20 —— 与「键集分页」对照（两侧都有 (status, order_id) 索引）' AS `-- 第05章 --`;
SET @last_id = (SELECT order_id FROM erp_sales_order WHERE status='Paid' ORDER BY order_id LIMIT 500000, 1);
SELECT @last_id AS 第50万条的order_id, '★ 应用层翻页时本来就持有"上一页最后一条"，所以这个值是免费得到的。' AS 说明;
CALL demo_begin('O4_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order
WHERE status = 'Paid' ORDER BY order_id LIMIT 500000, 20;
CALL demo_end('O4_bad');
CALL demo_begin('O4_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id) FROM erp_sales_order
WHERE status = 'Paid' AND order_id > @last_id ORDER BY order_id LIMIT 20;
CALL demo_end('O4_good');
CALL demo_report('O4_bad','O4_good','05',
'O4 深分页 LIMIT 500000,20 vs 键集分页 WHERE order_id > 上页末值 LIMIT 20',
'O4_深分页OFFSET','I',
'★ 结论：OFFSET 的代价与偏移量成正比 —— 服务端必须"读出来再扔掉"前面 50 万行。'
'键集分页把它变成常数级。两者返回的正是同一页数据。');
SELECT '§05.10 Phase B 结束，清理实验索引' AS `-- 第05章 --`;
SET @has = (SELECT COUNT(*) FROM information_schema.statistics
WHERE table_schema=DATABASE() AND table_name='erp_sales_order' AND index_name='ix_demo_c_net');
SET @ddl = IF(@has > 0,
'ALTER TABLE erp_sales_order DROP KEY ix_demo_c_net, DROP KEY ix_demo_c_date_netdesc, DROP KEY ix_demo_status_country, DROP KEY ix_demo_status_id',
'DO 0');
SET @t0 = NOW(6);
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SELECT CONCAT(' [DDL] 清理 4 个实验索引耗时 ',
ROUND(TIMESTAMPDIFF(MICROSECOND,@t0,NOW(6))/1000000,1), ' 秒') AS `-- 清理 --`;
SELECT '§05.11 第 05 章判决表' AS `-- 第05章 --`;
SELECT check_name AS 编号, label AS 对照, bad_rows AS 反模式引擎读行, good_rows AS 修正引擎读行,
rows_ratio AS 行数倍数, time_ratio AS 耗时倍数
FROM demo_result WHERE chapter='05' ORDER BY check_name;
/* ==========================================================================================
第 06 章 连接形态与派生表：半连接改写、派生表物化、以及 EAV 过度范式化
------------------------------------------------------------------------------------------
本章要回答三个「据说是这样」的问题：
① 派生表（子查询当表用）是不是一定会被物化成临时表？
② IN (子查询) 是不是一定比 JOIN 慢？ → MySQL 8.0+ 早就会做半连接改写
③ 键值行模型（EAV）取 5 个属性要 5 次自连接，是不是一定更慢？ → 取决于取回还是只过滤
========================================================================================== */
USE JewelryAnalytics;
/* ------------------------------------------------------------------------------------------
V1 派生表：能不能被合并（merge）决定要不要建临时表
------------------------------------------------------------------------------------------ */
SELECT '§06.1 V1 派生表物化 vs 合并 —— 一个 LIMIT 就足以让优化器放弃合并' AS `-- 第06章 --`;
SELECT '★ 触发物化的典型写法：派生表里带 LIMIT / UNION / DISTINCT / 聚合 / 窗口函数。'
'其中 `LIMIT 18446744073709551615`（BIGINT 最大值）是流传很广的"强制物化"技巧 —— '
'它不改变结果集，只用来固定实验变量。' AS `-- 说明 --`;
CALL demo_begin('V1_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM (
SELECT order_id, net_amount_usd FROM erp_sales_order
WHERE country_code = 'CN' ORDER BY order_id LIMIT 18446744073709551615
) d WHERE d.net_amount_usd > 50000;
CALL demo_end('V1_bad');
CALL demo_begin('V1_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM erp_sales_order
WHERE country_code = 'CN' AND net_amount_usd > 50000;
CALL demo_end('V1_good');
CALL demo_report('V1_bad','V1_good','06',
'V1 派生表被物化（带 LIMIT）vs 合并进外层（同一结果集）',
'V1_派生表物化','N',
'★ 结论：两版结果完全相同，行数差异不大（1.x 倍），但物化版会多出临时表开销。'
'判据很简单：EXPLAIN 里出现 `<derived2>` 就是被物化了。');
CALL demo_explain('SELECT COUNT(*) FROM (SELECT order_id, net_amount_usd FROM erp_sales_order
WHERE country_code=''CN'' ORDER BY order_id LIMIT 18446744073709551615) d WHERE d.net_amount_usd > 50000');
CALL demo_explain('SELECT COUNT(*) FROM erp_sales_order WHERE country_code=''CN'' AND net_amount_usd > 50000');
/* ------------------------------------------------------------------------------------------
V2 IN (子查询) vs JOIN
------------------------------------------------------------------------------------------ */
SELECT '§06.2 V2 IN (子查询) vs JOIN —— 「子查询一定慢」在 MySQL 9.0 上不成立' AS `-- 第06章 --`;
CALL demo_begin('V2_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',c.customer_id) FROM crm_customer c
WHERE c.customer_id IN (SELECT o.customer_id FROM erp_sales_order o WHERE o.status = 'Paid');
CALL demo_end('V2_bad');
CALL demo_begin('V2_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',c.customer_id) FROM crm_customer c
JOIN (SELECT DISTINCT customer_id FROM erp_sales_order WHERE status='Paid') o
ON o.customer_id = c.customer_id;
CALL demo_end('V2_good');
CALL demo_report('V2_bad','V2_good','06',
'V2 WHERE id IN (子查询) vs 等价的 JOIN 半连接改写',
'V2_IN子查询vsJOIN','N',
'★ 结论：优化器早就会把 IN (子查询) 改写成半连接（semi-join），两者行数一致。'
'"IN 子查询一定慢、一定要改成 JOIN"是 MySQL 5.5 时代的经验，已经不适用。');
CALL demo_explain('SELECT c.customer_id FROM crm_customer c WHERE c.customer_id IN
(SELECT o.customer_id FROM erp_sales_order o WHERE o.status=''Paid'')');
/* ------------------------------------------------------------------------------------------
E1 EAV 三重自连接：只做过滤
------------------------------------------------------------------------------------------ */
SELECT '§06.3 E1 EAV 键值行模型：三重自连接只做过滤 vs 宽表三列与运算' AS `-- 第06章 --`;
CALL demo_begin('E1_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM erp_product_attr a
JOIN erp_product_attr b ON b.product_id = a.product_id AND b.attr_name = 'gemstone'
JOIN erp_product_attr c ON c.product_id = a.product_id AND c.attr_name = 'category'
WHERE a.attr_name = 'metal' AND a.attr_value = '足金'
AND b.attr_value = '钻石' AND c.attr_value = '项链';
CALL demo_end('E1_bad');
CALL demo_begin('E1_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM erp_product
WHERE metal = '足金' AND gemstone = '钻石' AND category = '项链';
CALL demo_end('E1_good');
CALL demo_report('E1_bad','E1_good','06',
'E1 EAV 三重自连接（仅过滤）vs 宽表三列与运算（同一结果集）',
'E1_EAV自连接_仅过滤','N',
'★ 结论：只做过滤时，EAV 未必更差 —— 因为 ix_attr(attr_name, attr_value) 是覆盖索引，'
'三列都能在索引里过滤。这不符合很多人的直觉，值得记下来。');
CALL demo_explain('SELECT COUNT(*) FROM erp_product_attr a
JOIN erp_product_attr b ON b.product_id=a.product_id AND b.attr_name=''gemstone''
JOIN erp_product_attr c ON c.product_id=a.product_id AND c.attr_name=''category''
WHERE a.attr_name=''metal'' AND a.attr_value=''足金'' AND b.attr_value=''钻石'' AND c.attr_value=''项链''');
/* ------------------------------------------------------------------------------------------
E2 EAV 五重自连接：还要把属性取回来
------------------------------------------------------------------------------------------ */
SELECT '§06.4 E2 EAV 五重自连接（取回 5 个属性）vs 宽表直接取 5 列 —— 真正的代价在这里' AS `-- 第06章 --`;
CALL demo_begin('E2_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',a.product_id, a.attr_value, b.attr_value, c.attr_value, d.attr_value, e.attr_value)
FROM erp_product_attr a
JOIN erp_product_attr b ON b.product_id = a.product_id AND b.attr_name = 'gemstone'
JOIN erp_product_attr c ON c.product_id = a.product_id AND c.attr_name = 'carat'
JOIN erp_product_attr d ON d.product_id = a.product_id AND d.attr_name = 'category'
JOIN erp_product_attr e ON e.product_id = a.product_id AND e.attr_name = 'price_band'
WHERE a.attr_name = 'metal';
CALL demo_end('E2_bad');
CALL demo_begin('E2_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',product_id, metal, gemstone, carat, category, list_price_usd)
FROM erp_product;
CALL demo_end('E2_good');
CALL demo_report('E2_bad','E2_good','06',
'E2 EAV 五重自连接取回属性 vs 宽表直接取列（同一批商品）',
'E2_EAV自连接_取回属性','I',
'★ 结论：一旦要"取回"多个属性，行数倍数就上来了 —— 因为每个属性都要一次索引定位 + 一次取行。'
'过滤时看不出问题，取数时问题就出来了。这是 EAV 最典型的翻车方式。');
/* ------------------------------------------------------------------------------------------
E3 EAV 单属性过滤
------------------------------------------------------------------------------------------ */
SELECT '§06.5 E3 单属性过滤：EAV vs 宽表' AS `-- 第06章 --`;
CALL demo_begin('E3_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM erp_product_attr
WHERE attr_name = 'metal' AND attr_value = '足金';
CALL demo_end('E3_bad');
CALL demo_begin('E3_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM erp_product WHERE metal = '足金';
CALL demo_end('E3_good');
CALL demo_report('E3_bad','E3_good','06',
'E3 EAV 单属性过滤 vs 宽表单列过滤（同一结果集）',
'E3_EAV单属性过滤','N',
'★ 结论：单属性过滤时 EAV 甚至可能读得更少（25,000 行的小表 + 覆盖索引）。'
'所以"EAV 一定慢"和"EAV 一定好"都是错的 —— 关键看你要过滤还是要取数。');
SELECT '§06.6 第 06 章判决表' AS `-- 第06章 --`;
SELECT check_name AS 编号, label AS 对照, bad_rows AS 反模式引擎读行, good_rows AS 修正引擎读行,
rows_ratio AS 行数倍数, time_ratio AS 耗时倍数, note AS 结论
FROM demo_result WHERE chapter='06' ORDER BY check_name;
/* ==========================================================================================
第 07 章 分区裁剪：万亿级表的第一件武器（也是最容易被自己写废的一件）
------------------------------------------------------------------------------------------
分区的价值不在于「表更大也能存」，而在于**裁剪**：让一条语句根本不碰不相干的分区。
而裁剪只在谓词能被"下推到分区表达式"时才生效 —— 一旦把函数套在分区列上，裁剪立刻失效。
========================================================================================== */
USE JewelryAnalytics;
SELECT '§07.1 分区结构：rpt_sales_fact 按年 RANGE 分区（4 个分区）' AS `-- 第07章 --`;
SELECT PARTITION_NAME AS 分区, PARTITION_DESCRIPTION AS 上界, TABLE_ROWS AS 精确行数,
ROUND(DATA_LENGTH/1024/1024) AS 数据MB
FROM information_schema.partitions
WHERE table_schema='JewelryAnalytics' AND table_name='rpt_sales_fact'
AND PARTITION_NAME IS NOT NULL
ORDER BY PARTITION_ORDINAL_POSITION;
SELECT '§07.2 P1 分区裁剪：YEAR(order_date)=2025 vs 半开区间 [2025-01-01, 2026-01-01)' AS `-- 第07章 --`;
SELECT '★ 分区表达式是 YEAR(order_date)。查询里再写一次 YEAR(order_date)=2025，'
'看起来"语义一致、应该能裁剪" —— 实测恰恰相反。' AS `-- 说明 --`;
CALL demo_begin('P1_bad');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM rpt_sales_fact WHERE YEAR(order_date) = 2025;
CALL demo_end('P1_bad');
CALL demo_begin('P1_good');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',COUNT(*)) FROM rpt_sales_fact
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';
CALL demo_end('P1_good');
CALL demo_report('P1_bad','P1_good','07',
'P1 分区表上用 YEAR(order_date)=2025 过滤 vs 用半开区间过滤',
'P1_分区裁剪','R',
'★ 结论：分区裁剪要求谓词是**分区列上的可下推比较**；把 YEAR() 套回去等于自废武功。');
SELECT '§07.3 铁证：EXPLAIN 的 partitions 列' AS `-- 第07章 --`;
CALL demo_explain('SELECT COUNT(*) FROM rpt_sales_fact WHERE YEAR(order_date) = 2025');
CALL demo_explain('SELECT COUNT(*) FROM rpt_sales_fact WHERE order_date >= ''2025-01-01'' AND order_date < ''2026-01-01''');
CALL demo_explain('SELECT COUNT(*) FROM rpt_sales_fact WHERE order_date >= ''2025-06-01'' AND order_date < ''2025-07-01''');
SELECT '★ 只看 partitions 这一列：'
'第一行是 p2024,p2025,p2026,pmax（全部分区都扫），'
'第二、三行只有 p2025（裁剪成功）。'
'这是判断裁剪是否生效最快、最无争议的一招 —— 不需要看任何耗时。' AS `-- 判读 --`;
SELECT '§07.4 分区在万亿级场景的真正价值：把「删除历史数据」从 DELETE 变成 DROP PARTITION' AS `-- 第07章 --`;
SELECT '★ 一条 DELETE 删掉 500 万行历史数据，要：写 undo / 写 redo / 更新所有二级索引 / 持有大量行锁'
' / 生成海量 binlog，耗时以小时计且会拖垮主从复制。' AS `-- 问题 --`;
SELECT '★ 而 DROP PARTITION 是 DDL：直接丢弃整个表空间文件，毫秒级完成，不写 redo、不逐行删除。'
'这就是"按时间分区"在万亿级数据里最硬的收益 —— 不是为了查得快，而是为了删得快。' AS `-- 收益 --`;
SELECT '★ 代价与前提：'
'① 分区列必须进入**每一个**唯一索引（这是 MySQL 的硬约束，否则建不了分区表）；'
'② 分区数不宜过多（几百个以内），否则元数据与打开文件的开销会反噬；'
'③ 分区裁剪只对"分区列上的直接比较"生效，任何函数包装都会失效；'
'④ 分区不是分片：单实例的分区仍然共享同一份缓冲池、同一套锁、同一个写入瓶颈。' AS `-- 代价 --`;
SELECT '§07.5 万亿级的四层武器：分区的上一层是什么' AS `-- 第07章 --`;
SELECT 层次, 手段, 解决什么, 引入什么代价 FROM (
SELECT 1 AS 层次, '① 索引与查询改写（本脚本第 01~06 章）' AS 手段,
'单条语句扫的行数从 200 万降到几十' AS 解决什么,
'几乎无代价，收益最确定 —— 所以永远先做这一层' AS 引入什么代价
UNION ALL SELECT 2, '② 分区裁剪（本章）',
'让语句只碰相关时间段；让历史数据 DROP 而非 DELETE',
'分区列必须进所有唯一索引；分区数有上限'
UNION ALL SELECT 3, '③ 冷热分离 / 归档到独立表或实例',
'把 90% 的"只查不写"的历史数据从写热点上搬走',
'跨表查询需要 UNION ALL 或中间聚合层；应用要改'
UNION ALL SELECT 4, '④ 水平分片（分库分表 / 中间件）',
'打破单实例的写入与存储上限 —— 万亿行唯一可行的终局',
'跨片 JOIN 与分布式事务；分片键一旦选错，回天乏术'
) t ORDER BY 层次;
SELECT '★ 在往下一层走之前，请务必确认上一层已经做透。'
'本脚本实测的最贵单条反模式是 200 万倍的差距 —— 这类问题在分片之后依然存在，'
'只是从"一个实例慢"变成"每个分片都慢"。' AS `-- 忠告 --`;
/* ==========================================================================================
第 08 章 锁与并发：万亿级高并发下真正杀死系统的东西
------------------------------------------------------------------------------------------
前面 7 章讨论的都是「一条语句扫多少行」。但生产事故里更常见的是：
· 一条**只返回 0 行**的 SELECT ... FOR UPDATE，把别人的 INSERT 卡到超时；
· 两个顺序不同的事务互相等待，数据库直接干掉其中一个。
这两个问题在 MySQL 里的成因是 SQL Server 完全没有的概念：**间隙锁 / next-key 锁**。
------------------------------------------------------------------------------------------
本章分两段：
第一段（§08.2~§08.4）单会话即可跑完 —— 直接读 performance_schema.data_locks 取证。
第二段（§08.5~§08.6）需要**两个客户端**，脚本正文给出可复制的双会话流程 + 本机实测结果。
========================================================================================== */
USE JewelryAnalytics;
SELECT '§08.1 场景：跨境订单系统的「防超卖」写法 —— 先 SELECT ... FOR UPDATE 锁一行再更新' AS `-- 第08章 --`;
SELECT '★ 本机实测的结论先摆在这里：'
'RR 隔离级别下，SELECT ... WHERE order_id BETWEEN 2500000 AND 2500003 FOR UPDATE '
'命中了 **0 行**（该区间根本不存在任何记录），却让另一个会话的 INSERT 报 1205 锁等待超时；'
'换成 READ-COMMITTED 后，同一句 SQL 的锁清单为空，INSERT 立刻成功。' AS `-- 结论 --`;
/* ------------------------------------------------------------------------------------------
§08.2 RR 下的锁清单
------------------------------------------------------------------------------------------ */
SELECT '§08.2 REPEATABLE-READ：对「不存在的区间」加锁 → 间隙锁（supremum pseudo-record）' AS `-- 第08章 --`;
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;
SELECT COUNT(*) INTO @x FROM erp_sales_order
WHERE order_id BETWEEN 2500000 AND 2500003 FOR UPDATE;
SELECT COUNT(*) AS 命中行数 FROM erp_sales_order WHERE order_id BETWEEN 2500000 AND 2500003;
SELECT LOCK_TYPE AS 锁类型, LOCK_MODE AS 锁模式, LOCK_STATUS AS 状态,
INDEX_NAME AS 索引, LOCK_DATA AS 锁数据
FROM performance_schema.data_locks WHERE THREAD_ID = PS_CURRENT_THREAD_ID();
SET @lk_rr := (SELECT COUNT(*) FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID() AND LOCK_TYPE = 'RECORD');
SET @gap_rr := (SELECT COUNT(*) FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID() AND LOCK_TYPE = 'RECORD'
AND LOCK_MODE NOT LIKE '%REC_NOT_GAP%');
ROLLBACK;
SELECT CONCAT('RR：记录锁 ', @lk_rr, ' 个，其中「间隙型」锁 ', @gap_rr, ' 个（锁数据 = supremum pseudo-record，'
'即"最后一个索引记录之后的整个间隙"，也就是 order_id > 2,000,000 的全部空间）') AS RR锁统计;
/* ------------------------------------------------------------------------------------------
§08.3 RC 下的锁清单
------------------------------------------------------------------------------------------ */
SELECT '§08.3 READ-COMMITTED：同一句 SQL，锁清单里没有任何 RECORD 锁' AS `-- 第08章 --`;
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT COUNT(*) INTO @x FROM erp_sales_order
WHERE order_id BETWEEN 2500000 AND 2500003 FOR UPDATE;
SELECT COUNT(*) AS 命中行数 FROM erp_sales_order WHERE order_id BETWEEN 2500000 AND 2500003;
SELECT LOCK_TYPE AS 锁类型, LOCK_MODE AS 锁模式, LOCK_STATUS AS 状态,
INDEX_NAME AS 索引, LOCK_DATA AS 锁数据
FROM performance_schema.data_locks WHERE THREAD_ID = PS_CURRENT_THREAD_ID();
SET @lk_rc := (SELECT COUNT(*) FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID() AND LOCK_TYPE = 'RECORD');
ROLLBACK;
SELECT CONCAT('RC：记录锁 ', @lk_rc, ' 个 —— 因为 READ-COMMITTED 下 InnoDB 关闭了间隙锁'
'（除了外键约束与唯一键冲突检查这两处例外）。') AS RC锁统计;
/* ------------------------------------------------------------------------------------------
§08.4 「命中行」时的锁：next-key 锁 vs 纯记录锁
------------------------------------------------------------------------------------------ */
SELECT '§08.4 当区间真的命中 4 行时：RR 给的是 next-key 锁，RC 给的是纯记录锁' AS `-- 第08章 --`;
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;
SELECT COUNT(*) INTO @x FROM erp_sales_order WHERE order_id BETWEEN 1 AND 4 FOR UPDATE;
SELECT 'RR' AS 隔离级别, LOCK_TYPE AS 锁类型, LOCK_MODE AS 锁模式, INDEX_NAME AS 索引, LOCK_DATA AS 锁数据
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID() AND LOCK_TYPE = 'RECORD';
ROLLBACK;
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT COUNT(*) INTO @x FROM erp_sales_order WHERE order_id BETWEEN 1 AND 4 FOR UPDATE;
SELECT 'RC' AS 隔离级别, LOCK_TYPE AS 锁类型, LOCK_MODE AS 锁模式, INDEX_NAME AS 索引, LOCK_DATA AS 锁数据
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID() AND LOCK_TYPE = 'RECORD';
ROLLBACK;
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SELECT '★ 判读 data_locks 的 LOCK_MODE：'
'`X` 且 LOCK_DATA 是某个具体值 = next-key 锁（记录本身 + 它前面的间隙）；'
'`X,REC_NOT_GAP` = 纯记录锁，不含间隙；'
'`X,GAP` = 纯间隙锁；'
'LOCK_DATA = `supremum pseudo-record` = 锁住了最后一条记录之后的全部空间。' AS `-- 判读 --`;
/* ------------------------------------------------------------------------------------------
§08.5 双会话实测：1205 锁等待超时
------------------------------------------------------------------------------------------ */
SELECT '§08.5 双会话实测 —— 需要两个客户端，本机实测结果如下' AS `-- 第08章 --`;
SELECT 场景 AS 场景, 会话A AS `会话 A`, 会话B的结果 AS `会话 B 的结果`, 说明 AS 说明 FROM (
SELECT 1 AS 场景, 'REPEATABLE-READ：BEGIN; SELECT ... WHERE order_id BETWEEN 2500000 AND 2500003 FOR UPDATE;（命中 0 行）' AS 会话A,
'★ ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction' AS 会话B的结果,
'B 的 INSERT 落在 (2000000, +∞) 间隙里，被 A 的间隙锁挡住。★ 注意错误号是 1205，不是常说的 1203' AS 说明
UNION ALL SELECT 2, 'READ-COMMITTED：同一句 SELECT ... FOR UPDATE（命中 0 行）',
'插入成功（退出码 0，无任何错误）',
'RC 不加间隙锁 → 锁清单里连一条 RECORD 记录都没有'
) t ORDER BY 场景;
SELECT '★ 复现方法（把下面两段分别贴进两个客户端，先跑 A，2 秒后再跑 B）：' AS `-- 复现步骤 --`;
SELECT '【会话 A】' AS 会话, 'SET SESSION innodb_lock_wait_timeout=50;
SET SESSION transaction_isolation=''REPEATABLE-READ''; -- 换成 ''READ-COMMITTED'' 再试一次
START TRANSACTION;
SELECT COUNT(*) FROM erp_sales_order WHERE order_id BETWEEN 2500000 AND 2500003 FOR UPDATE;
SELECT SLEEP(6);
ROLLBACK;' AS 脚本
UNION ALL
SELECT '【会话 B】', 'SET SESSION innodb_lock_wait_timeout=3;
INSERT INTO erp_sales_order
(order_no,order_date,country_code,store_code,channel,customer_id,sales_employee_id,
status,currency_code,item_count,net_amount_usd,tax_amount_usd,gross_amount_usd,
discount_pct,is_export,note,created_at)
VALUES
(''SO99999999999999'',''2025-06-15 10:00:00'',''CN'',''ST0999'',''Retail'',1,1,
''Paid'',''CNY'',1,100.00,7.00,107.00,0,0,''lock-probe'',''2025-06-15 10:00:00'');
DELETE FROM erp_sales_order WHERE order_no=''SO99999999999999''; -- 清理';
SELECT '★ 为什么 B 的 INSERT 会被挡住？order_id 是 AUTO_INCREMENT，当前最大 2,000,000，'
'下一个自增值 2,000,001 正好落在被间隙锁覆盖的 (2,000,000, +∞) 区间里。'
'这就是"防超卖"代码在 RR 下最典型的翻车方式：**锁了一个不存在的区间**。' AS `-- 根因 --`;
/* ------------------------------------------------------------------------------------------
§08.6 死锁 1213
------------------------------------------------------------------------------------------ */
SELECT '§08.6 双会话死锁实测：两个事务以相反顺序更新同一批行' AS `-- 第08章 --`;
SELECT 事务 AS 事务, 步骤 AS 步骤 FROM (
SELECT 'A' AS 事务, '① BEGIN; ② UPDATE ... WHERE order_id=1;（拿到 1 的行锁）③ 等 4 秒 ④ UPDATE ... WHERE order_id=2' AS 步骤
UNION ALL SELECT 'A', '结果：提交成功'
UNION ALL SELECT 'B', '① BEGIN; ② UPDATE ... WHERE order_id=2;（拿到 2 的行锁）③ 等 4 秒 ④ UPDATE ... WHERE order_id=1'
UNION ALL SELECT 'B', '结果：★ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction'
) t;
SELECT '★ 实测要点：'
'① 死锁不是超时 —— InnoDB 有**死锁检测**，会在几十毫秒内主动挑一个事务回滚，'
' 所以报的是 1213 而不是 1205；错误文本里明确写着 "try restarting transaction"。'
'② 应用必须实现**死锁重试**（捕获 1213 后自动重放整个事务），否则用户直接看到报错。'
'③ SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段落会完整记录两个事务、'
' 各自持有的锁、以及在等哪把锁 —— 字段里有真实的主键十六进制值与可读的字符值（如 asc SO00000003998001）。' AS `-- 判读 --`;
SELECT '★ 本机实测的 InnoDB 死锁日志摘录（节选，已验证字段可读）：' AS `-- 日志摘录 --`;
SELECT 行号 AS 序, 内容 AS 内容 FROM (
SELECT 1 AS 行号, 'LATEST DETECTED DEADLOCK / ------------------------' AS 内容
UNION ALL SELECT 2, '*** (1) TRANSACTION: TRANSACTION 535071, ACTIVE 5 sec starting index read'
UNION ALL SELECT 3, 'LOCK WAIT 3 lock struct(s), ... 2 row lock(s), undo log entries 1'
UNION ALL SELECT 4, 'UPDATE erp_sales_order SET note = ''dlA'' WHERE order_id = 2'
UNION ALL SELECT 5, '*** (1) HOLDS THE LOCK(S): RECORD LOCKS ... index PRIMARY ... lock_mode X locks rec but not gap'
UNION ALL SELECT 6, '*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... lock_mode X locks rec but not gap waiting'
) t ORDER BY 序;
/* ------------------------------------------------------------------------------------------
§08.7 登记自检 + 高并发纪律
------------------------------------------------------------------------------------------ */
CALL demo_v('V7_间隙锁差异', IF(@gap_rr >= 1 AND @lk_rc = 0, 1, 0),
CONCAT('RR 下对不存在区间加锁产生间隙型记录锁 ', @gap_rr, ' 个；RC 下同句 SQL 的记录锁 ', @lk_rc, ' 个（期望 0）'));
SELECT '§08.7 万亿级高并发下的 5 条硬纪律' AS `-- 第08章 --`;
SELECT 纪律 AS 纪律, 做法 AS 做法, 依据 AS 依据 FROM (
SELECT 1 AS n, '热点写入表用 READ-COMMITTED' AS 纪律,
'订单 / 流水 / 库存这类高并发写入表，把隔离级别从 RR 降到 RC' AS 做法,
'本章实测：RC 彻底消除间隙锁，把「锁了不存在的区间」这类事故直接消灭' AS 依据
UNION ALL SELECT 2, '唯一键冲突用 INSERT ... ON DUPLICATE KEY，或先查再插 + 重试',
'不要依赖 RR 下的间隙锁去「防并发插入」',
'间隙锁是 REPEATABLE-READ 的副作用而非设计目标；拿它来防重会带来大量锁等待超时'
UNION ALL SELECT 3, '事务里绝不做 RPC、绝不循环单条 DML',
'把远程调用移出事务；批量操作改一条 SQL，或分批处理',
'事务越长，锁持有窗口越大，死锁概率随并发度指数上升'
UNION ALL SELECT 4, '应用代码必须实现 1213 死锁重试',
'捕获 1213 → 退避 → 重放整个事务（前提是整个事务幂等）',
'死锁是正常现象，InnoDB 会主动回滚一个事务；不重试就是直接把故障抛给用户'
UNION ALL SELECT 5, '锁等待超时设短（秒级）并对 1205 告警',
'innodb_lock_wait_timeout 设 3~10 秒',
'本章实测超时错误号是 1205；默认 50 秒会把线程池占满，雪崩由此开始'
) t ORDER BY n;
/* ==========================================================================================
第 09 章 可编程体检器：把前面 8 章的判据变成可以挂定时作业的检查项
------------------------------------------------------------------------------------------
前面所有的「反模式 → 修正」都是**人工对照**得出的。生产环境不可能天天做对照实验，
但可以定期跑体检：让数据库自己告诉你「最近哪些语句在扫全表」「哪些索引白占空间」。
数据来源三个：
· sys schema —— 已经替你写好的"人话"视图（推荐起点）
· performance_schema —— 原始计数器（需要更细的口径时下钻）
· information_schema —— 结构与统计（行数、索引、分区）
========================================================================================== */
USE JewelryAnalytics;
DROP PROCEDURE IF EXISTS audit_run;
DELIMITER $$
CREATE PROCEDURE audit_run()
BEGIN
-- ------------------------------------------------------------------ ① 全表扫描 TOP 10
-- ★ 注意列名：sys.statements_with_full_table_scans 用的是 `query`，不是 `digest_text`。
SELECT '【体检 1】走「全表扫描」的语句 TOP 10（按平均扫描行数排序）' AS 体检项;
SELECT LEFT(query, 80) AS 语句摘要, db AS 库, exec_count AS 执行次数,
no_index_used_count AS 全表扫描次数, no_index_used_pct AS 全表扫描占比,
rows_examined_avg AS 平均扫描行数, rows_sent_avg AS 平均返回行数
FROM sys.statements_with_full_table_scans
ORDER BY rows_examined_avg DESC, exec_count DESC
LIMIT 10;
-- ------------------------------------------------------------------ ② 临时表 TOP
SELECT '【体检 2】产生「磁盘临时表」的语句 TOP 10（分组/排序口径有问题）' AS 体检项;
SELECT LEFT(query, 80) AS 语句摘要, db AS 库, exec_count AS 执行次数,
memory_tmp_tables AS 内存临时表总数, disk_tmp_tables AS 磁盘临时表数,
tmp_tables_to_disk_pct AS 落盘占比
FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC, memory_tmp_tables DESC
LIMIT 10;
-- ------------------------------------------------------------------ ③ 排序开销 TOP
SELECT '【体检 3】filesort 大户 TOP 10（按排序总行数排序）' AS 体检项;
SELECT LEFT(query, 80) AS 语句摘要, db AS 库, exec_count AS 执行次数,
rows_sorted AS 排序总行数, sort_merge_passes AS 归并趟数,
avg_rows_sorted AS 平均每次排序行数
FROM sys.statements_with_sorting
ORDER BY rows_sorted DESC
LIMIT 10;
-- ------------------------------------------------------------------ ④ 冗余索引
SELECT '【体检 4】冗余索引（某索引的全部列是另一索引的前缀 ⇒ 零增益，只拖慢写入）' AS 体检项;
SELECT table_schema AS 库, table_name AS 表,
redundant_index_name AS 冗余索引, redundant_index_columns AS 冗余列,
dominant_index_name AS 可替代索引, sql_drop_index AS 可直接执行的删除语句
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY table_name, redundant_index_name
LIMIT 20;
-- ------------------------------------------------------------------ ⑤ 从未使用过的索引
SELECT '【体检 5】实例启动以来从未被使用过的索引（★ 只是线索不是判决）' AS 体检项;
SELECT object_schema AS 库, object_name AS 表, index_name AS 索引
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY object_name, index_name
LIMIT 20;
-- ------------------------------------------------------------------ ⑥ 表级 IO 热点
SELECT '【体检 6】表级访问热点（读/写行数与 IO 请求数）' AS 体检项;
SELECT table_schema AS 库, table_name AS 表,
rows_fetched AS 读取行数, rows_inserted AS 插入行数, rows_updated AS 更新行数,
rows_deleted AS 删除行数, io_read_requests AS 读请求数, io_write_requests AS 写请求数
FROM sys.schema_table_statistics
WHERE table_schema = 'JewelryAnalytics'
ORDER BY rows_fetched DESC
LIMIT 10;
END$$
DELIMITER ;
SELECT '§09.1 运行体检器（在真实项目里可以把 CALL audit_run() 挂进 events 定时任务）' AS `-- 第09章 --`;
CALL audit_run();
SELECT '§09.2 体检项的正确解读方式' AS `-- 第09章 --`;
SELECT 项目 AS 体检项, 能直接下结论吗 AS `能直接下结论吗`, 必须人工确认什么 AS `必须人工确认什么` FROM (
SELECT 1 AS 体检项, '全表扫描 TOP' AS 项目, '不能' AS 能直接下结论吗,
'小表全表扫描本来就比走索引便宜。先看"平均扫描行数"是否上万，再看表的总行数' AS 必须人工确认什么
UNION ALL SELECT 2, '磁盘临时表 TOP', '不能',
'GROUP BY / DISTINCT 偶尔落盘是正常的；要确认的是「高频 + 大行数」的组合'
UNION ALL SELECT 3, '排序 TOP', '不能',
'业务上确实需要全量排序的报表，建索引也没用；要区分"能用索引免排序"和"排序本就是需求"'
UNION ALL SELECT 4, '冗余索引', '基本能',
'唯一约束索引即使冗余也不能删 —— 它承担约束职责，不只是查询加速'
UNION ALL SELECT 5, '从未使用的索引', '绝对不能',
'★ 口径是"实例重启以来"。月度/季度报表索引可能几个月才用一次；'
'支撑外键的索引删了会导致外键检查全表扫描。删索引前必须看完整业务周期'
UNION ALL SELECT 6, '表级 IO 热点', '不能',
'热点表不一定是问题表；要结合"该表是全表扫描多，还是索引定位多"一起判断'
) t ORDER BY 体检项;
SELECT '§09.3 information_schema 侧的结构体检（DDL 层的问题必须靠 DDL 层解决）' AS `-- 第09章 --`;
SELECT '★ 问题 1：还有没有没建索引的外键列 / 关联列？' AS 检查项;
SELECT t.table_name AS 表, c.column_name AS 列, c.data_type AS 类型
FROM information_schema.columns c
JOIN information_schema.tables t
ON t.table_schema=c.table_schema AND t.table_name=c.table_name
WHERE c.table_schema='JewelryAnalytics'
AND c.column_name IN ('customer_id','employee_id','product_id','org_id','parent_id','order_id')
AND NOT EXISTS (
SELECT 1 FROM information_schema.statistics s
WHERE s.table_schema=c.table_schema AND s.table_name=c.table_name
AND s.column_name=c.column_name AND s.seq_in_index = 1
)
ORDER BY t.table_name, c.column_name;
SELECT '★ 问题 2：还有没有排序规则不统一的列？（第 02 章实测：列与列排序规则不一致会直接报 1267）' AS 检查项;
SELECT TABLE_NAME AS 表, COLUMN_NAME AS 列, COLLATION_NAME AS 排序规则
FROM information_schema.columns
WHERE table_schema='JewelryAnalytics'
AND COLLATION_NAME IS NOT NULL
AND COLLATION_NAME NOT IN (
SELECT DISTINCT c2.COLLATION_NAME FROM information_schema.columns c2
WHERE c2.table_schema='JewelryAnalytics' AND c2.TABLE_NAME='erp_sales_order'
AND c2.COLUMN_NAME='order_no')
ORDER BY TABLE_NAME, COLUMN_NAME;
SELECT '★ 问题 3：字符集是否需要混合？（混合字符集本身不报错、1.00×，但会阻碍列之间的比较把索引用上）' AS 检查项;
SELECT CHARACTER_SET_NAME AS 字符集, COUNT(*) AS 列数
FROM information_schema.columns
WHERE table_schema='JewelryAnalytics'
GROUP BY CHARACTER_SET_NAME ORDER BY 列数 DESC;
/* ==========================================================================================
第 10 章 决策清单：把 9 章实测结论压缩成一张「写 SQL 时按顺序过一遍」的表
========================================================================================== */
USE JewelryAnalytics;
SELECT '§10.1 写一条 SELECT 之前，按顺序问自己 8 个问题' AS `-- 第10章 --`;
SELECT 序, 问题, 动作, 章节 FROM (
SELECT 1 AS 序, '我 SELECT 的列，是不是比 WHERE/ORDER BY 真正需要的多？' AS 问题,
'砍掉多余列 —— 让索引有机会覆盖查询（MySQL 没有 INCLUDE，只能用"少取列"换覆盖）' AS 动作,
'第 01 章 K1 / K1b' AS 章节
UNION ALL SELECT 2, '我的 WHERE 里，有没有把函数/运算/CAST 套在列上？',
'全部改写成对列的裸比较；日期用半开区间 [起, 次)',
'第 02 章 C8、第 03 章 S1~S5'
UNION ALL SELECT 3, '我的参数绑定类型，和列的类型一致吗？',
'不一致就改绑定的类型 —— 字符串列必须传字符串',
'第 02 章 C1a/C1b/C2'
UNION ALL SELECT 4, '我参与比较的列，排序规则/字符集统一吗？',
'不统一就在 DDL 层统一；绝不在谓词里写 COLLATE 包列',
'第 02 章 C3/C4/C5'
UNION ALL SELECT 5, '我的复合索引，是不是"等值列在前、范围列在后"？',
'调整列序；前导列必须选选择性高的',
'第 04 章 IX1/IX2'
UNION ALL SELECT 6, '我的 ORDER BY / GROUP BY，有没有可以利用的索引顺序？',
'给 (过滤列, 排序列) 建复合索引；方向不一致要建 DESC 索引',
'第 05 章 O1/O2/O3'
UNION ALL SELECT 7, '我用了 OFFSET 深分页吗？',
'改成键集分页：WHERE 唯一键 > 上一页最后一条',
'第 05 章 O4'
UNION ALL SELECT 8, '这是一张分区表吗？谓词能触发分区裁剪吗？',
'分区列上只写直接比较，不写函数',
'第 07 章 P1'
) t ORDER BY 序;
SELECT '§10.2 索引决策树（按顺序判断，命中即停）' AS `-- 第10章 --`;
SELECT 步骤 AS 步骤, 判断 AS 判断, 动作 AS 动作 FROM (
SELECT 1 AS 步骤, '这条查询的结果集占全表比例 > 20%？' AS 判断,
'不要建索引 —— 优化器会自己选全表扫描，建了也是白占空间' AS 动作
UNION ALL SELECT 2, 'WHERE 里有等值条件 + 范围条件？',
'建 (等值列..., 范围列) 复合索引，等值列在前'
UNION ALL SELECT 3, 'WHERE 有等值条件 + ORDER BY 另一列？',
'建 (等值列, 排序列) 复合索引 —— 一举拿下过滤与排序'
UNION ALL SELECT 4, 'WHERE 有等值条件 + ORDER BY 多个方向不一致的列？',
'建带 DESC 的复合索引（MySQL 8.0+），方向必须逐列对齐'
UNION ALL SELECT 5, '查询只要 3~6 个列且都很窄？',
'考虑把这几列一起放进索引键做成覆盖索引，代价是索引变宽、可能让旧索引变冗余'
UNION ALL SELECT 6, '要在长文本里做"包含"检索？',
'不要建 B+ 树索引，用 FULLTEXT；%关键字% 这种写法永远用不上 B+ 树'
UNION ALL SELECT 7, '以上都不满足？',
'★ 先别建索引。多数"慢查询"的真正原因是 WHERE 写错了，而不是缺索引'
) t ORDER BY 步骤;
SELECT '§10.3 本机实测的「该先修哪个」优先级（按访问行数倍数排序，仅供参考量级）' AS `-- 第10章 --`;
SELECT check_name AS 编号, chapter AS 章, LEFT(label,72) AS 对照摘要,
bad_rows AS 反模式引擎读行, good_rows AS 修正引擎读行, rows_ratio AS 行数倍数,
time_ratio AS 耗时倍数
FROM demo_result
WHERE rows_ratio IS NOT NULL AND rows_ratio >= 2
ORDER BY rows_ratio DESC;
SELECT '§10.4 行数倍数 < 2 的用例 —— ★ 这不等于「不用救」，请逐条对照第 12.1 节的备注' AS `-- 第10章 --`;
SELECT '★ 典型反例：O3（GROUP BY 列序与索引不一致）的行数倍数是 1.00×，'
'但它免掉了临时表与 filesort —— Handler 读行 2.00×、耗时约降到一半。'
'它的收益体现在 Handler 层与耗时上，而引擎读行口径看不见。'
'所以「行数倍数」只能用来排序优先级，不能用来判定「无害」。' AS `-- 重要提醒 --`;
SELECT check_name AS 编号, chapter AS 章, LEFT(label,72) AS 对照摘要,
bad_rows AS 反模式引擎读行, good_rows AS 修正引擎读行, rows_ratio AS 行数倍数,
IFNULL(time_ratio,0) AS 耗时倍数, LEFT(IFNULL(note,''),60) AS 备注摘要
FROM demo_result
WHERE rows_ratio IS NOT NULL AND rows_ratio < 2
ORDER BY rows_ratio;
SELECT '§10.5 万亿级 + 高并发下的额外 6 条纪律（前 10 章都没覆盖到的）' AS `-- 第10章 --`;
SELECT 纪律, 具体做法, 为什么 FROM (
SELECT 1 AS n, 'SELECT 列表永远显式列名' AS 纪律,
'不用 SELECT *' AS 具体做法,
'① 避免把列加进表后打穿覆盖索引；② 避免把大字段（TEXT/BLOB）意外带到网络层' AS 为什么
UNION ALL SELECT 2, '所有 SQL 必须带 LIMIT', '报表也先加 LIMIT 再放开',
'万亿级表上一次"忘了 LIMIT 的查询"就足以打满网络与内存返回缓冲'
UNION ALL SELECT 3, '热点写入用 READ-COMMITTED', '订单/流水类表把隔离级别降下来',
'★ 第 08 章实测：RR 下对不存在的区间加 FOR UPDATE 也会加间隙锁，阻塞他人 INSERT 直到超时(1205)'
UNION ALL SELECT 4, '长事务必须拆短', '一个事务里不要做 RPC、不要做大批量循环',
'事务越长，持有的行锁/间隙锁窗口越大，死锁(1213)概率指数上升'
UNION ALL SELECT 5, '统计信息要新鲜', '大表变更后 ANALYZE TABLE；不用 information_schema.table_rows 当真实行数',
'本章所有坏计划都是"估算错 → 选错访问路径"，估算的输入就是统计信息'
UNION ALL SELECT 6, '读写分离 + 从库只读', '报表查询走从库；主库只留 OLTP',
'报表的排序/分组/全表扫描会污染主库的缓冲池，把热点数据挤出去'
) t ORDER BY n;
/* ==========================================================================================
第 11 章 自检（V1~V9）：用可执行断言证明「本脚本确实按预期跑完了」
------------------------------------------------------------------------------------------
一个演示脚本如果没有自检，读者无法区分「结论就是这样」和「脚本跑挂了只剩半截」。
本章每一条都是**可执行断言**，不是文字说明。
========================================================================================== */
USE JewelryAnalytics;
SELECT '§11.1 自检开始' AS `-- 第11章 --`;
-- V1 实验台规模
SET @v1_total := (
SELECT (SELECT COUNT(*) FROM erp_sales_order) + (SELECT COUNT(*) FROM erp_sales_order_line)
+ (SELECT COUNT(*) FROM rpt_sales_fact) + (SELECT COUNT(*) FROM crm_interaction)
+ (SELECT COUNT(*) FROM hr_attendance) + (SELECT COUNT(*) FROM crm_customer)
+ (SELECT COUNT(*) FROM crm_customer_legacy) + (SELECT COUNT(*) FROM trap_join)
+ (SELECT COUNT(*) FROM erp_product_attr) + (SELECT COUNT(*) FROM erp_product)
+ (SELECT COUNT(*) FROM hr_employee) + (SELECT COUNT(*) FROM hr_org_unit)
+ (SELECT COUNT(*) FROM ref_country));
CALL demo_v('V1_实验台规模',
IF(@v1_total >= 8800000, 1, 0),
CONCAT('实验台合计 ', FORMAT(@v1_total,0), ' 行（阈值 ≥ 8,800,000）；订单主表 ',
FORMAT((SELECT COUNT(*) FROM erp_sales_order),0), ' 行'));
-- V2 全部对照用例均通过
SET @v2_fail := (SELECT COUNT(*) FROM demo_result WHERE passed <> 1);
SET @v2_cnt := (SELECT COUNT(*) FROM demo_result);
CALL demo_v('V2_全部对照用例通过',
IF(@v2_fail = 0 AND @v2_cnt >= 29, 1, 0),
CONCAT('共 ', @v2_cnt, ' 个对照用例，未通过 ', @v2_fail, ' 个'));
-- V3 各章用例数量齐全
SET @v3 := (SELECT CONCAT(
'01=', SUM(chapter='01'), ' 02=', SUM(chapter='02'), ' 03=', SUM(chapter='03'),
' 04=', SUM(chapter='04'), ' 05=', SUM(chapter='05'), ' 06=', SUM(chapter='06'),
' 07=', SUM(chapter='07')) FROM demo_result);
SET @v3_ok := (SELECT IF(SUM(chapter='01')=2 AND SUM(chapter='02')=7 AND SUM(chapter='03')=9
AND SUM(chapter='04')=1 AND SUM(chapter='05')=4 AND SUM(chapter='06')=5
AND SUM(chapter='07')=1, 1, 0) FROM demo_result);
CALL demo_v('V3_各章用例数量齐全', @v3_ok,
CONCAT('各章用例数：', @v3, '（期望 01=2 02=7 03=9 04=1 05=4 06=5 07=1）'));
-- V4 实验用临时索引已全部清理
SET @v4 := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.statistics
WHERE table_schema='JewelryAnalytics' AND INDEX_NAME LIKE 'ix_demo%');
CALL demo_v('V4_临时索引已清理', IF(@v4 = 0, 1, 0),
CONCAT('残留 ix_demo% 索引个数 = ', @v4, '（期望 0）'));
-- V5 错误探针确实捕获到了硬错误（1292 隐式转换 / 1267 排序规则）
SET @v5_cnt := (SELECT COUNT(*) FROM demo_verify WHERE check_name LIKE '错误探针%');
SET @v5_ok := (SELECT IF(COUNT(*) >= 2
AND SUM(detail LIKE '%1292%') >= 1
AND SUM(detail LIKE '%1267%') >= 1, 1, 0)
FROM demo_verify WHERE check_name LIKE '错误探针%');
CALL demo_v('V5_硬错误确实被捕获', @v5_ok,
CONCAT('错误探针记录数 = ', @v5_cnt, '；',
IFNULL((SELECT GROUP_CONCAT(LEFT(detail,40) SEPARATOR ' ; ')
FROM demo_verify WHERE check_name LIKE '错误探针%'), '无')));
-- V6 分区裁剪用例通过
SET @v6 := (SELECT COUNT(*) FROM demo_result WHERE check_name='P1_分区裁剪' AND passed=1);
CALL demo_v('V6_分区裁剪用例通过', IF(@v6 = 1, 1, 0),
CONCAT('P1_分区裁剪 passed = ', @v6, '（期望 1）'));
-- V7 锁行为：REPEATABLE-READ 下的锁数量必须多于 READ-COMMITTED
SET @v7 := (SELECT COUNT(*) FROM demo_verify WHERE check_name='V7_间隙锁差异' AND passed=1);
CALL demo_v('V7_间隙锁差异', IF(@v7 = 1, 1, 0),
CONCAT('见第 08 章的实测登记（RR 锁数 vs RC 锁数），passed = ', @v7));
-- V8 没有残留的未提交事务 / 行锁
SET @v8_locks := (SELECT COUNT(*) FROM performance_schema.data_locks);
SET @v8_trx := (SELECT COUNT(*) FROM information_schema.innodb_trx);
CALL demo_v('V8_无残留事务与锁', IF(@v8_trx = 0, 1, 0),
CONCAT('当前锁记录数 = ', @v8_locks, '，未提交事务数 = ', @v8_trx, '（期望事务 0）'));
-- V9 sql_mode / optimizer_switch 已被还原（第 02、03 章会临时改动）
SET @v9 := (SELECT IF(@@sql_mode = @old_sql_mode
AND @@optimizer_switch LIKE '%index_condition_pushdown=on%'
AND @@optimizer_switch LIKE '%mrr=on%', 1, 0));
CALL demo_v('V9_会话开关已还原', @v9,
CONCAT('sql_mode 是否与脚本开始时一致 = ', IF(@@sql_mode = @old_sql_mode,'是','否'),
'；optimizer_switch 含 icp=on,mrr=on = ',
IF(@@optimizer_switch LIKE '%index_condition_pushdown=on%' AND @@optimizer_switch LIKE '%mrr=on%','是','否')));
SELECT '§11.2 自检汇总' AS `-- 第11章 --`;
SELECT IF(passed=1,'通过','★未通过') AS 结果, check_name AS 自检项, detail AS 详情
FROM demo_verify ORDER BY check_name;
SELECT CONCAT('自检通过 ', SUM(passed=1), ' / ', COUNT(*), ' 项') AS 自检总评,
IF(SUM(passed=1) = COUNT(*), '★ 全部通过', '★ 存在未通过项，请检查上面的明细') AS 结论
FROM demo_verify;
/* ==========================================================================================
第 12 章 实测总表 · 被推翻的流行说法 · 收尾
========================================================================================== */
USE JewelryAnalytics;
SELECT '§12.1 本次运行实测总表（★ 本节是全文唯一权威口径；头部速查表里的倍率会随统计/缓存浮动）' AS `-- 第12章 --`;
SELECT check_name AS 编号, chapter AS 章, LEFT(label, 78) AS 对照,
bad_rows AS 反模式引擎读行, good_rows AS 修正引擎读行,
rows_ratio AS 行数倍数, time_ratio AS 耗时倍数,
IF(passed=1,'通过','★未通过') AS 自检
FROM demo_result
ORDER BY chapter, check_name;
SELECT '§12.2 访问行数倍数 TOP 12（「该先修哪个」的排序依据）' AS `-- 第12章 --`;
SELECT check_name AS 编号, chapter AS 章, LEFT(label, 70) AS 对照,
bad_rows AS 反模式引擎读行, good_rows AS 修正引擎读行, rows_ratio AS 行数倍数
FROM demo_result
WHERE rows_ratio IS NOT NULL
ORDER BY rows_ratio DESC
LIMIT 12;
SELECT '§12.3 被本机实测推翻 / 修正的 13 条流行说法' AS `-- 第12章 --`;
SELECT 流行说法, `本机实测（MySQL 9.0.1）` AS 本机实测, 正确认知 FROM (
SELECT 1 AS n, '「SELECT * 一定很慢」' AS 流行说法,
CONCAT('小结果集（1.9 万行）下行数与必要列版**完全相同**（1.00×），只慢 14.78× 的随机 IO；', '中间量（23.8 万行）时优化器干脆放弃索引改全表扫描，行数 5.01×') AS `本机实测（MySQL 9.0.1）`,
'准确说法是「SELECT * 打穿覆盖索引」，而不是「取全部列一定慢」' AS 正确认知
UNION ALL SELECT 2, '「隐式转换只是警告，顶多慢一点」',
CONCAT('★ 默认严格模式下 varchar 列 = 数字字面量**直接报 ERROR 1292 Truncated incorrect DOUBLE value**；', '放宽 sql_mode 后才会退化成 1499× 的全表扫描'),
'MySQL 8.0.16+ 把它从"慢"升级成了"功能故障"，比 SQL Server 更严厉'
UNION ALL SELECT 3, '「排序规则不一致只是让查询变慢」',
'★ 直接报 ERROR 1267 Illegal mix of collations，语句根本无法执行',
'这是可用性问题不是性能问题：上线当天直接 500'
UNION ALL SELECT 4, '「字符集不一致很危险」',
'latin1 列 JOIN utf8mb4 列**可以执行**，代价 1.00×',
'字符集与排序规则是两件事，危害等级完全不同，别混为一谈'
UNION ALL SELECT 5, '「IN (子查询) 一定要改成 JOIN」',
'已被半连接（semi-join）改写，行数 1.00×',
'这是 MySQL 5.5 时代的经验，8.0 之后不成立'
UNION ALL SELECT 6, '「OR 拆成 UNION ALL 一定更快」',
'★ 实测**更慢**：引擎读行虽少 40%，但 Handler 读行反而更多、耗时 0.89×（>1 表示修正版更慢）',
'跨列 OR 请先看 EXPLAIN；优化器的 index_merge 往往已经处理好了'
UNION ALL SELECT 7, '「长 IN 列表会导致坏计划」',
CONCAT('300 个值（超过 eq_range_index_dive_limit=200）仍然走范围扫描，', '与等价 BETWEEN 命中同样的行，甚至读得更少（0.69×）'),
'阈值只影响"估算精度"不必然改变计划；几百个值的 IN 完全正常'
UNION ALL SELECT 8, '「冗余谓词有害，要清理」', '优化器自动折叠，1.00×，计划完全一致',
'ORM 生成的"安全网"条件不用花时间清理'
UNION ALL SELECT 9, '「日期列不能和字符串比较」',
'order_date >= ''2025-06-01'' 与 >= ''2025-06-01 00:00:00'' 均为 1.00×',
'字面量会按列类型解析；真正致命的是把**函数套在列上**（DATE() 达 904×）'
UNION ALL SELECT 10, '「EAV 键值行模型一定慢」',
CONCAT('仅过滤时反而更快（0.15×，因为 ix_attr 是覆盖索引）；', '★ 但取回 5 个属性要五重自连接时，行数 4.99×、耗时 9.40×'),
'EAV 的代价在"取回"不在"过滤"，这才是该量化的点'
UNION ALL SELECT 11, '「打开索引条件下推（ICP）一定更快」',
'引擎读行与 Handler 读行**完全不变**，只有耗时变化（约 1/2.5）',
CONCAT('ICP 省的是"回表"，而回表在 MySQL 里不计入任何 Handler/Innodb_rows 计数器 —— ', '单看计数器会误判，必须三个口径交叉验证')
UNION ALL SELECT 12, '「锁等待超时是错误 1203」',
'★ 实测是 **ERROR 1205**；死锁是 **ERROR 1213**',
'1203 是"超出最大连接数"，与锁无关，别记错'
UNION ALL SELECT 13, '「MySQL 也会像 SQL Server 那样锁升级」',
CONCAT('MySQL **没有锁升级**；有的是**间隙锁 / next-key 锁**：', 'RR 下对不存在的区间加 FOR UPDATE 会锁住整个 (2000000, +∞) 间隙，让别人的 INSERT 超时'),
'MySQL 的并发事故主要来自"锁了不该锁的间隙"，不是"锁太多行"'
) t ORDER BY n;
SELECT '§12.4 三条最重要的结论' AS `-- 第12章 --`;
SELECT 序, 结论, 支撑实测 FROM (
SELECT 1 AS 序,
'① 先把「谓词写法」和「列列表」修对，再谈索引。这两件事不花任何 DDL 成本，收益却最大。' AS 结论,
CONCAT('DATE() 包列 904×、前导通配 LIKE 200 万×、CAST 包数字列 200 万×、', 'SELECT * 打穿覆盖索引 —— 全部是"改 SQL 就行"，一行 DDL 都不需要') AS 支撑实测
UNION ALL SELECT 2,
'② 「有没有索引」从来不是问题，「索引的顺序对不对、选择性够不够」才是。',
CONCAT('同样的两个索引，列序换成"等值列在前"就从 65,758 行降到 6,562 行（10.02×）；', 'channel 有索引却被优化器无视（前导列只有 4 个值）')
UNION ALL SELECT 3,
'③ 万亿级高并发下，锁比扫描更致命 —— 而 MySQL 的锁风险来自「间隙」。',
CONCAT('RR 下命中 0 行的 FOR UPDATE 锁住了 (2000000,+∞)，另一会话 INSERT 报 1205；', 'RC 下同句 SQL 锁清单为空、INSERT 立即成功；反向更新必然 1213 死锁')
) t ORDER BY 序;
SELECT '§12.5 脚本结构与运行方式' AS `-- 第12章 --`;
SELECT 章, 内容 FROM (
SELECT 1 AS n, '头部' AS 章, '与 SQL Server 版本的关键差异、速查表、旗舰结论、环境勘察' AS 内容
UNION ALL SELECT 2, '00-C 实验台', '13 张表 / 约 880 万行；幂等建表灌数（首次约 6 分钟）'
UNION ALL SELECT 3, '00-D 度量框架', 'demo_snap/begin/end/report/explain/err_probe + BLACKHOLE 接收器'
UNION ALL SELECT 4, '00.3 数据底稿', '行数、索引基数、字符集排序规则、分区结构'
UNION ALL SELECT 5, '第 01 章', '选择性列：SELECT * 打穿覆盖索引（K1 / K1b）'
UNION ALL SELECT 6, '第 02 章', '隐式转换 & 字符集排序规则（C1a/C1b/C2~C8，含 2 个硬错误探针）'
UNION ALL SELECT 7, '第 03 章', '非 SARGable 谓词（S1~S9）'
UNION ALL SELECT 8, '第 04 章', '索引列序与前导列基数、冗余/未用索引体检（IX1 / IX2）'
UNION ALL SELECT 9, '第 05 章', '排序、分组、深分页（O1~O4，本脚本最贵的一章）'
UNION ALL SELECT 10, '第 06 章', '派生表物化、半连接改写、EAV 过度范式化（V1/V2/E1~E3）'
UNION ALL SELECT 11, '第 07 章', '分区裁剪与万亿级四层武器（P1）'
UNION ALL SELECT 12, '第 08 章', '锁与并发：间隙锁、1205、1213（L1/L2，含双会话流程）'
UNION ALL SELECT 13, '第 09 章', '可编程体检器 audit_run()（可挂定时作业）'
UNION ALL SELECT 14, '第 10 章', '决策清单：8 个问题 + 索引决策树 + 6 条并发纪律'
UNION ALL SELECT 15, '第 11 章', '自检 V1~V9（可执行断言）'
UNION ALL SELECT 16, '第 12 章', '本总表 + 推翻的流行说法 + 结论（本章）'
) t ORDER BY n;
SELECT '★ 运行方式：' AS 说明
UNION ALL SELECT ' mysql --defaults-extra-file=<凭据文件> --default-character-set=utf8mb4 < MySQL9_SelectDesign_JewelryDemo.sql'
UNION ALL SELECT '★ 幂等：首次运行建表灌数约 6 分钟；之后每次约 3~5 分钟（第 05 章的两个 filesort 用例本身就很慢，这正是它要演示的东西）。'
UNION ALL SELECT '★ 第 08 章 §08.5/§08.6 的双会话部分需要开两个客户端，脚本正文给出了可直接复制的语句与本机实测结果。'
UNION ALL SELECT '★ 全部实验用的临时索引（ix_demo*）在各自章节结束时已删除，第 11 章 V4 会断言残留数为 0。';
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号