【mysql】5.7版本复杂单表索引选择方案&案例

复杂单表索引设计的核心挑战

复杂单表的典型特征

• 高字段数：表中包含大量字段，可能达到十几个甚至几十个。这使得索引设计变得复杂，因为需要考虑每个字段的查询需求。
• 多查询场景：单个表可能支持多种类型的查询，包括范围查询、排序、聚合等。这要求索引能够灵活地适应不同的查询模式。

MySQL 5.7 索引选择机制的核心局限性与优势

• 优势：

  • 支持多种索引类型，如 B-Tree 索引和哈希索引。
  • 可以通过 EXPLAIN 命令查看查询计划，帮助优化索引使用。

• 局限性：

  • 索引选择受限于统计信息的质量，可能导致次优查询计划。
  • EXPLAIN 分析出来的不一定是实际的执行结果，因为是基于统计信息的查询
  • ​​降序排序的缺陷​​：MySQL 5.7及更早版本中，索引​​默认按升序（ASC）存储数据​​，即使显式指定DESC（如INDEX(a, b DESC)）也会被忽略，实际仍按(a ASC, b ASC)存储，mysql8.0版本才支持降序索引
  • 5.7版本没有直方图统计信息,不能使用 ANALYZE TABLE ... UPDATE HISTOGRAM ON col_name ...
  • 5.7版本的mysql采用的是最严格的最左前置匹配原则，这点在设计中需要十分注意

理论基础

索引建立理论

过度设计 & 设计不足带来的问题

• 索引过度设计：

  • 创建过多索引会增加写操作的开销，影响插入和更新性能。
  • 可能导致额外的存储空间消耗。

• 假设每次插入单条数据，引擎为InnoDB，硬件配置中等：

• 索引数量建议
  通常经验来说:单表索引数量建议不超过5~8个，极端情况下不超过10个
  无论是阿里还是华为的数据库规范都是单表索引数<=5个
  同时从官方文档可以知道单个索引最长也不能超过16列
  

• 设计不足：

  • 缺乏必要的索引会导致查询性能下降，尤其是在大数据量下。
  • 可能导致数据库扫描过多数据，增加 CPU 和 I/O 负担。

高选择性优先原则

• 选择性计算公式：

  • 使用公式 :当前列的唯一值/表的总记录数
    
    上述公式结果越接近1，表面选择性越高，（比如唯一键，主键id都是为1的），优先用于索引，因为它们能显著减少扫描的行数。

• 低选择性字段的联合索引策略：

  • 对于低选择性字段（如状态标志），应与高选择性字段联合创建索引，以提高整体查询效率。

查询频率与数据分布分析

• 高频查询字段的索引权重调整：
  • 识别高频查询字段，并优先为这些字段创建索引，以优化最常用的查询路径。
  • 如果高频字段是低选择性的字段，最佳方案应与高选择性字段联合，
  • 但实际应该结合业务需求，少点教条式理论，比如高频字段a,实际与b,c字段联合使用，虽然d的选择性要更好，但是还是应该在(a,b,c)上建立，且联合索引一旦满足所有条件的命中是一定会比单列索引速度更快的

最左前缀原则的灵活运用

• 范围查询后置：
  • 在索引设计中，将范围查询的字段放在后面。例如，对于 WHERE a=1 AND b>10 AND c=2，索引设计为 (a, c, b) 可以更有效地利用索引。

冗余索引合并策略

• 识别并删除冗余索引：
  • 当存在 (a, b) 索引时，可以考虑删除单独的 a 索引，以减少不必要的维护开销。

查询语句注意点

避免回表

• 设计复合索引时，尽量包含所有在 SELECT 中使用的字段，以避免回表操作，从而提升查询速度。

• 回表概念
  当查询使用二级索引（非聚簇索引）定位数据时，若索引中未包含查询所需的全部列（例如 SELECT * 或包含非索引列），存储引擎需根据二级索引中的主键值回到聚簇索引（主键索引）中查找完整数据行。此过程称为回表，会增加额外的 I/O 开销。

示例

-- 表结构及数据：
CREATE TABLE `user` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50),
  `age` INT,
  INDEX `idx_name` (`name`)
);

-- 数据示例
INSERT INTO `user` VALUES 
(1, 'Tom', 25),
(2, 'Jerry', 30);


-- 执行查询
SELECT * FROM `user` WHERE `name` = 'Tom';

执行流程：

1. 通过二级索引 idx_name 找到 name=‘Tom’ 对应的主键 id=1。
2. 根据 id=1 回表到聚簇索引中获取完整行数据（包含 age=25）。

性能问题：若二级索引匹配大量记录（如 name 值重复率高），频繁回表会导致 I/O 激增。

空间代价权衡

• 对于大字段，需权衡其加入索引的空间代价，避免不必要的存储开销。

索引下推

• 索引下推概念
  MySQL 5.6+ 引入的优化技术，允许存储引擎在二级索引层直接过滤部分查询条件（即使该条件不满足最左前缀原则），减少回表次数。需满足：

1. 使用联合索引（如 (name, age)）。
2. 查询条件包含索引中的列（即使是非前缀列）。

示例对比（MySQL 5.5 vs 5.7）

表结构：
CREATE TABLE `user2` (
  `id` INT PRIMARY KEY,
  `username` VARCHAR(255),
  `age` INT,
  INDEX `idx_username_age` (`username`, `age`)
);


查询语句：
SELECT * FROM `user2` WHERE `username` LIKE 'j%' AND `age` = 30;

• 场景 1：无 ICP（如 MySQL 5.5）

  • 1. 存储引擎使用索引前缀 username LIKE ‘j%’ 定位所有匹配记录（如 100 条）。
  • 2. 对这 100 条记录逐条回表，获取完整数据行。
  • 3. Server 层再过滤 age=30，最终保留 10 条。
  • 👉 回表 100 次，效率低下。

• 场景 2：有 ICP（MySQL 5.7+）

  • 1. 存储引擎使用索引前缀 username LIKE ‘j%’ 定位记录。
  • 2. 在索引层直接过滤 age=30（利用联合索引中的 age 列）。
  • 3. 仅对同时满足 username LIKE ‘j%’ 和 age=30 的记录回表（如 10 条）。
  • 👉 回表 10 次，I/O 减少 90%。

执行计划验证（使用 EXPLAIN）：
EXPLAIN SELECT * FROM user2 WHERE username LIKE ‘j%’ AND age = 30;

• Extra 列显示 Using index condition → ICP 生效。

执行计划分析与优化验证

复杂单表需要关注的EXPLAIN参数

特别说明 :

• 同样的查询字段，查询值不同，索引选择的也会不同，mysql5.7索引优化器的explain没有特别的直观，执行计划和抽样统计出来的数据分布和选择性也有很大关系，比如 where a= 1 and b=2 和 where a=1 and b=3 通过explain分析出来的结果可能是完全不一样的两套执行计划
• 在mysql5.7中经过千万级的数据测试，possible_keys中的索引是会参与查询优化的，也就是说可能key、Extra、filtered、rows通过EXPLAIN分析出来都相同，但是possible_keys不同执行速度就会有区别，这点本人实操下来只能通过数据实验来判断优劣性，没有一步到位的分析方式分析最优索引

type 列

• type 字段反映了MySQL 查询优化器选择的表访问方式，也叫“连接类型”。
  它是衡量 SQL 性能优劣的核心指标之一，代表了查询从表中检索数据的效率高低。
• 了解查询执行类型，从 ALL 到 range/ref 的优化路径。ALL 表示全表扫描，而 range 和 ref 表示使用了索引，性能更高。

• 常见场景举例：

• const

EXPLAIN SELECT * FROM users WHERE id = 5;

• type: const

• 说明：主键等值查找，仅返回一行，效率极高。

• ref

EXPLAIN SELECT * FROM orders WHERE user_id = 10;

• type: ref

• 说明：user_id 有索引，但不是唯一索引，可能匹配多行。

• range

EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

• type: range

• 说明：order_date 有索引，走了范围扫描。

• ALL

EXPLAIN SELECT * FROM users WHERE name LIKE '%abc%';

• type: ALL
• 说明：无法使用索引，执行全表扫描，效率最低。

key_len：索引利用长度

• 计算索引利用长度。key_len 显示查询中使用的索引部分长度，帮助判断索引是否被充分利用。

• EXPLAIN中的 rows=400是优化器基于统计信息的​​估算值​​。若实际数据分布不均（如 status='PENDING’的记录远超预估）

possible_keys ：可用索引分析

• 基本含义：

possible_keys 字段显示本次查询可能会用到的所有索引，即优化器根据查询条件判断哪些索引是“可选项”。
这些索引未必最终被使用，仅表示“有可能”被用来优化检索。

key字段：实际使用的索引

• 基本含义：

key 字段显示了本次查询实际被优化器选择使用的索引。

rows字段：预估扫描的行数

• 基本含义：

rows 字段表示MySQL 优化器预估本次查询需要扫描的行数。
这个数字是基于表的统计信息、索引分布等进行估算的，不是实际扫描的行数，但通常能反映查询的性能开销。

filtered字段：行过滤比例

• 基本含义：

filtered 字段表示MySQL 优化器预估本层查询条件过滤后，剩余记录所占的百分比。
它的单位是百分比（%），显示为浮点数。例如 100.00 表示 100%。

• 作用：

• 反映了本层扫描的行经过 WHERE 等条件过滤后，预计还有多少比例能“存活”下来。

• 用于帮助判断 SQL 的过滤效率和条件选择性。

• 典型场景举例：

• 高过滤率（选择性高）

EXPLAIN SELECT * FROM users WHERE id = 5;

• filtered=100.00，表示扫描的 1 行全部符合条件。

• 低过滤率（选择性低）

EXPLAIN SELECT * FROM users WHERE gender = 'M';

（假设 users 表有 10000 行，gender = ‘M’ 占 50%）

• filtered=50.00，表示大约 50% 行能通过过滤条件。

Extra字段：额外信息与常见输出

• 基本含义：

Extra 字段用于显示MySQL 优化器对本条查询计划的额外说明，包括执行过程中用到的特殊操作、优化手段、未利用索引的情况等。
这是 EXPLAIN 输出中最灵活、信息量最大的一个字段。

• 作用：

• 帮助定位查询的潜在性能问题。

• 反映 MySQL 是否采用了高效的执行策略。

• 指出哪些查询操作可能影响性能。

mysql5.7优化器陷阱规避

• 统计信息不准​​：

  • 优化器认为复合索引需回表读取完整行，代价高于主键扫描（实际因数据分布偏差，此估算错误）

• 隐式类型转换导致索引失效：

  • 避免在查询中进行隐式类型转换，例如将 varchar 与数字直接比较，这会导致索引失效。确保数据类型匹配以利用索引。

• 函数操作导致的索引失效：

  • 使用函数如 WHERE DATE(create_time)=... 可能导致索引失效。考虑使用 WHERE create_time BETWEEN ... AND ... 来避免函数操作对索引的影响。

实战演示示例案例&数据

以下是一个包含 20 个字段的 financial_transactions 表的索引设计示例。包括了数据插入，方便大家直接学习实操。

表结构与索引设计

CREATE TABLE financial_transactions (
    trans_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '交易ID',
    trans_no VARCHAR(32) NOT NULL COMMENT '交易流水号',
    user_id INT NOT NULL COMMENT '用户ID',
    account_no CHAR(20) NOT NULL COMMENT '银行账号',
    trans_type ENUM('IN','OUT') NOT NULL COMMENT '交易类型',
    amount DECIMAL(15,2) NOT NULL COMMENT '金额',
    currency CHAR(3) NOT NULL DEFAULT 'CNY' COMMENT '币种',
    status TINYINT NOT NULL COMMENT '状态:0-待处理,1-成功,2-失败',
    channel VARCHAR(20) NOT NULL COMMENT '支付渠道',
    merchant_id INT NOT NULL COMMENT '商户ID',
    product_code VARCHAR(20) NOT NULL COMMENT '产品编码',
    province_code SMALLINT NOT NULL COMMENT '省份编码',
    city_code INT NOT NULL COMMENT '城市编码',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    approve_time DATETIME COMMENT '审核时间',
    audit_user INT COMMENT '审核人',
    is_auto_approve TINYINT DEFAULT 0 COMMENT '是否自动审核',
    risk_level TINYINT COMMENT '风控等级',
    attach_info JSON COMMENT '附加信息',
    INDEX idx_user_trans (user_id, trans_type, status) COMMENT '用户交易查询索引',
    INDEX idx_merchant_time (merchant_id, create_time) COMMENT '商户时效索引',
    INDEX idx_auto_approve (is_auto_approve, status, risk_level) COMMENT '自动审核索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据插入

INSERT INTO financial_transactions (
    trans_no, user_id, account_no, trans_type, amount, currency, status, 
    channel, merchant_id, product_code, province_code, city_code, 
    create_time, approve_time, audit_user, is_auto_approve, risk_level, attach_info
) VALUES
-- 成功入账交易（自动审核）
('TR202508091200001', 1001, '6228480012345678901', 'IN', 5000.00, 'CNY', 1, 'BankTransfer', 201, 'PROD_LOAN', 11, 1101, '2025-08-09 09:15:22', '2025-08-09 09:16:30', 9001, 1, 1, '{"loan_id": "LN20250809001", "purpose": "salary"}'),
('TR202508091200002', 1002, '6228480023456789012', 'IN', 12000.50, 'CNY', 1, 'Alipay', 202, 'PROD_INVEST', 31, 3101, '2025-08-09 10:03:44', '2025-08-09 10:05:10', 9002, 1, 2, '{"investment_id": "INV20250809005", "maturity": "2026-12-31"}'),

-- 成功支出交易（人工审核）
('TR202508091200003', 1003, '6228480034567890123', 'OUT', -800.00, 'CNY', 1, 'WeChatPay', 203, 'PROD_UTILITY', 44, 4403, '2025-08-09 11:20:15', '2025-08-09 11:25:47', 9003, 0, 3, '{"payee": "Power Company", "invoice_no": "INV20250809123"}'),
('TR202508091200004', 1001, '6228480012345678901', 'OUT', -2000.00, 'USD', 1, 'CreditCard', 204, 'PROD_SHOPPING', 11, 1101, '2025-08-09 13:45:30', '2025-08-09 13:50:22', 9001, 0, 4, '{"merchant": "Amazon", "fx_rate": 7.25}'),

-- 待处理交易（自动审核标记）
('TR202508091200005', 1004, '6228480045678901234', 'IN', 300.00, 'CNY', 0, 'BankTransfer', 205, 'PROD_REFUND', 32, 3201, '2025-08-09 14:10:05', NULL, NULL, 1, NULL, '{"order_id": "ORD20250809987"}'),
('TR202508091200006', 1005, '6228480056789012345', 'OUT', -150.00, 'CNY', 0, 'Alipay', 206, 'PROD_GAMING', 44, 4401, '2025-08-09 14:30:18', NULL, NULL, 1, NULL, '{"game_id": "GAME20250808A1"}'),

-- 高风险交易（人工审核拒绝）
('TR202508091200007', 1006, '6228480067890123456', 'IN', 50000.00, 'CNY', 2, 'BankTransfer', 207, 'PROD_UNKNOWN', 50, 5001, '2025-08-09 15:05:33', '2025-08-09 15:10:44', 9004, 0, 5, '{"reject_reason": "Suspicious source"}'),
('TR202508091200008', 1002, '6228480023456789012', 'OUT', -100000.00, 'HKD', 2, 'WireTransfer', 208, 'PROD_INVEST', 31, 3101, '2025-08-09 15:40:12', '2025-08-09 15:45:01', 9005, 0, 5, '{"reject_reason": "Exceeds limit", "fx_rate": 0.92}'),

-- 多币种交易
('TR202508091200009', 1007, '6228480078901234567', 'IN', 2000.00, 'EUR', 1, 'PayPal', 209, 'PROD_FREELANCE', 11, 1101, '2025-08-09 16:20:55', '2025-08-09 16:25:30', 9006, 1, 2, '{"client": "Client_EU", "fx_rate": 7.88}'),
('TR202508091200010', 1008, '6228480089012345678', 'OUT', -500.00, 'JPY', 1, 'RakutenPay', 210, 'PROD_SUBSCRIPTION', 13, 1301, '2025-08-09 17:15:20', '2025-08-09 17:18:40', 9007, 0, 3, '{"service": "Premium_Membership", "fx_rate": 0.049}'),

-- 同商户高频交易
('TR202508091200011', 1009, '6228480090123456789', 'IN', 3200.00, 'CNY', 1, 'WeChatPay', 211, 'PROD_SALES', 33, 3301, '2025-08-09 09:05:11', '2025-08-09 09:06:20', 9008, 1, 1, '{"order_id": "SALE20250809A"}'),
('TR202508091200012', 1009, '6228480090123456789', 'IN', 2800.50, 'CNY', 1, 'WeChatPay', 211, 'PROD_SALES', 33, 3301, '2025-08-09 14:20:33', '2025-08-09 14:21:45', 9008, 1, 1, '{"order_id": "SALE20250809B"}'),

-- 风控低等级交易
('TR202508091200013', 1010, '6228480101234567890', 'OUT', -120.00, 'CNY', 1, 'Alipay', 212, 'PROD_FOOD', 44, 4403, '2025-08-09 12:30:44', '2025-08-09 12:31:50', 9009, 1, 1, '{"store": "Restaurant_A"}'),
('TR202508091200014', 1011, '6228480112345678901', 'OUT', -65.80, 'CNY', 1, 'CreditCard', 213, 'PROD_TRANSPORT', 31, 3101, '2025-08-09 18:05:22', '2025-08-09 18:06:15', 9010, 1, 1, '{"vehicle": "DiDi_Ride"}'),

-- 大额跨省交易
('TR202508091200015', 1012, '6228480123456789012', 'IN', 150000.00, 'CNY', 1, 'BankTransfer', 214, 'PROD_REALESTATE', 51, 5101, '2025-08-09 10:45:30', '2025-08-09 10:50:22', 9011, 0, 4, '{"property_id": "PROP202508CQ01", "contract_no": "CT2025080912"}'),
('TR202508091200016', 1013, '6228480134567890123', 'OUT', -95000.00, 'CNY', 1, 'WireTransfer', 215, 'PROD_REALESTATE', 44, 4401, '2025-08-09 11:30:15', '2025-08-09 11:35:40', 9012, 0, 4, '{"receiver_account": "623052*****6789"}'),

-- 失败自动审核案例
('TR202508091200017', 1014, '6228480145678901234', 'IN', 800.00, 'CNY', 2, 'Alipay', 216, 'PROD_REFUND', 32, 3202, '2025-08-09 13:15:18', '2025-08-09 13:16:05', NULL, 1, 3, '{"error_code": "BALANCE_INSUFFICIENT"}'),
('TR202508091200018', 1015, '6228480156789012345', 'OUT', -200.00, 'CNY', 2, 'WeChatPay', 217, 'PROD_GAMING', 50, 5001, '2025-08-09 19:20:30', '2025-08-09 19:21:10', NULL, 1, 3, '{"error_code": "RISK_REJECT"}'),

-- 特殊场景（零值交易）
('TR202508091200019', 1016, '6228480167890123456', 'IN', 0.00, 'CNY', 1, 'System', 218, 'PROD_ADJUST', 11, 1101, '2025-08-09 08:00:01', '2025-08-09 08:00:01', 9999, 1, NULL, '{"note": "Balance correction"}'),
('TR202508091200020', 1017, '6228480178901234567', 'OUT', -0.01, 'CNY', 1, 'System', 219, 'PROD_FEE', 44, 4401, '2025-08-09 23:59:59', '2025-08-09 23:59:59', 9999, 1, 1, '{"fee_type": "ACCOUNT_MAINTENANCE"}');

-- 复制数据（金额+1元，多次复制快速达到百万级数据量）
INSERT INTO financial_transactions (
    trans_no, 
    user_id,
    account_no,
    trans_type,
    amount, 
    currency,
    status,
    channel,
    merchant_id,
    product_code,
    province_code,
    city_code,
    create_time,
    approve_time,
    audit_user,
    is_auto_approve,
    risk_level,
    attach_info
)
SELECT 
    trans_no, 
    user_id,
    account_no,
    trans_type,
    amount + 1.00 AS amount,  -- 金额增加1元（区分原数据）
    currency,
    status,
    channel,
    merchant_id,
    product_code,
    province_code,
    city_code,
    create_time,  
    approve_time, 
    audit_user,
    is_auto_approve,
    risk_level,
    attach_info 
FROM financial_transactions;

索引设计说明

1. **用户交易查询索引 (`idx_user_

2. trans`)**：

   • 适用于查询条件 WHERE user_id=? AND trans_type=? AND status=?。
   • 通过组合高选择性字段和状态字段，提高查询效率。

3. 商户时效索引 (idx_merchant_time)：

   • 适用于按商户和时间查询的场景，如 WHERE merchant_id=? ORDER BY create_time DESC。
   • 优化了商户的时间范围查询和排序。

4. 自动审核索引 (idx_auto_approve)：

   • 适用于自动审核相关查询，如 WHERE is_auto_approve=? AND status=? AND risk_level=?。
   • 结合审核状态和风险等级，提供快速查询路径。