MySQL联合索引：深度解析与最佳实践指南

索引结构示例：INDEX idx_name (col1, col2, col3)

B+树排序规则：
第一层：按col1排序
第二层：在col1相同的情况下，按col2排序
第三层：在col1、col2都相同的情况下，按col3排序

-- MySQL 8.0.13+ 可能使用索引
SELECT * FROM table WHERE col2 = 'value' AND col3 = 'value';
-- 优化器可能将查询重写为：
-- SELECT * FROM table WHERE col1 IN (distinct_values) AND col2 = 'value' AND col3 = 'value';

1. 识别所有等值条件列（=, IN）
2. 识别范围条件列（>, <, BETWEEN, LIKE前缀）
3. 识别排序需求列（ORDER BY）
4. 识别分组需求列（GROUP BY）
5. 识别查询覆盖列（SELECT中的列）

优先级规则：
1. 等值条件列在前
2. 范围条件列在等值条件列后
3. 排序/分组列紧随其后
4. 覆盖列放在最后

-- 场景：查询某个部门某个时间段的员工
SELECT * FROM employees 
WHERE department_id = 10 
  AND hire_date BETWEEN '2020-01-01' AND '2020-12-31'
  AND status = 'ACTIVE'
ORDER BY salary DESC;

-- 方案A：错误的顺序（范围列在前）
INDEX idx_wrong (hire_date, department_id, status, salary)
-- hire_date范围查询后，department_id和status无法有效使用索引

-- 方案B：正确的顺序（等值列在前）
INDEX idx_correct (department_id, status, hire_date, salary)
-- 先精确定位department和status，再范围查hire_date，最后排序用salary

-- MySQL 5.6+ 自动启用
SELECT * FROM users 
WHERE age > 20 
  AND name LIKE '张%'
  AND city = '北京';

-- 索引设计：INDEX idx_city_age_name (city, age, name)
-- 即使name是LIKE条件，ICP允许在索引层过滤，减少回表

-- 回表查询
SELECT user_id, user_name, email FROM users WHERE age > 25;
-- 需要回表获取user_name和email

-- 覆盖索引优化
CREATE INDEX idx_age_cover ON users(age, user_id, user_name, email);
-- 查询时只需扫描索引，无需访问数据行

覆盖指数 = 索引大小 / 表数据大小
理想值应小于0.3，否则索引本身成为负担

-- 查询条件
WHERE a = 1 OR b = 2

-- 索引设计
INDEX idx_a (a)
INDEX idx_b (b)

-- 执行计划可能显示：Using union(idx_a, idx_b)

列选择性 = COUNT(DISTINCT column) / COUNT(*)
索引选择性 = COUNT(DISTINCT (col1, col2, ...)) / COUNT(*)

-- 1. 计算不同前缀长度的选择性
SELECT 
  COUNT(DISTINCT LEFT(column, 10)) / COUNT(*) as selectivity_10,
  COUNT(DISTINCT LEFT(column, 20)) / COUNT(*) as selectivity_20,
  COUNT(DISTINCT LEFT(column, 30)) / COUNT(*) as selectivity_30
FROM table;

-- 2. 选择选择性接近完整列的最小长度
-- 通常选择选择性 > 0.9 的最小前缀长度

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,          -- 订单ID，主键
    user_id BIGINT NOT NULL,              -- 用户ID，基数：1000万
    shop_id INT NOT NULL,                 -- 店铺ID，基数：10万
    status TINYINT NOT NULL,              -- 状态，基数：10
    amount DECIMAL(10,2) NOT NULL,        -- 订单金额
    create_time DATETIME NOT NULL,        -- 创建时间
    update_time DATETIME NOT NULL,        -- 更新时间
    INDEX idx_user (user_id),             -- 单列索引
    INDEX idx_shop (shop_id),             -- 单列索引
    INDEX idx_status (status),            -- 单列索引
    INDEX idx_create_time (create_time)   -- 单列索引
) ENGINE=InnoDB;

-- 查询1：用户订单列表（高频）
SELECT * FROM orders 
WHERE user_id = 12345 
  AND create_time >= '2023-01-01'
ORDER BY create_time DESC 
LIMIT 20;

-- 查询2：店铺订单管理（高频）
SELECT * FROM orders 
WHERE shop_id = 1001 
  AND status IN (1,2,3) 
  AND create_time BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY update_time DESC;

-- 查询3：订单精确查询（中频）
SELECT * FROM orders WHERE order_id = 100000001;

-- 查询4：运营统计（低频）
SELECT status, COUNT(*) 
FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY status;

-- 删除原有单列索引
DROP INDEX idx_user ON orders;
DROP INDEX idx_shop ON orders;
DROP INDEX idx_status ON orders;
DROP INDEX idx_create_time ON orders;

-- 创建优化后的联合索引
-- 索引1：覆盖用户查询 + 覆盖查询优化
CREATE INDEX idx_user_create_time ON orders(user_id, create_time DESC, order_id);

-- 索引2：覆盖店铺查询
CREATE INDEX idx_shop_status_time ON orders(shop_id, status, create_time DESC, update_time DESC);

-- 索引3：运营统计优化（覆盖索引）
CREATE INDEX idx_time_status_cover ON orders(create_time, status);

-- 主键订单号已存在，不需要额外索引

-- 使用EXPLAIN验证查询1
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND create_time >= '2023-01-01'
ORDER BY create_time DESC 
LIMIT 20;
-- 预期：使用idx_user_create_time，Using index condition

-- 使用EXPLAIN验证查询2
EXPLAIN SELECT * FROM orders 
WHERE shop_id = 1001 
  AND status IN (1,2,3) 
  AND create_time BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY update_time DESC;
-- 预期：使用idx_shop_status_time，Using index condition

-- 1. 创建测试索引
CREATE INDEX idx_test ON table(col1, col2, col3) ALGORITHM=INPLACE, LOCK=NONE;

-- 2. 分析查询计划
EXPLAIN FORMAT=JSON 
SELECT * FROM table WHERE col1 = ? AND col2 > ? ORDER BY col3;

-- 3. 查看索引使用统计
SELECT 
  index_name,
  rows_selected = rows_read / rows_selected_efficiency,
  avg_fetch_time_ms
FROM sys.schema_index_statistics
WHERE table_name = 'table';

-- 4. 压力测试对比
-- 运行前：记录QPS、平均响应时间、CPU使用率
-- 运行后：相同指标对比

-- 定期检查索引使用情况
SELECT 
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  ROWS_READ,
  ROWS_INSERTED,
  ROWS_UPDATED,
  ROWS_DELETED,
  LAST_USED
FROM sys.schema_index_statistics
WHERE LAST_USED < DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND INDEX_NAME != 'PRIMARY';

-- 重建碎片化索引
ALTER TABLE table_name ENGINE=InnoDB;  -- 重建表，包括索引
-- 或
ALTER TABLE table_name DROP INDEX idx_name, ADD INDEX idx_name (columns);

-- 自调节索引特性
SET adaptive_hash_index = ON;  -- 自适应哈希索引
-- InnoDB自动根据负载调整索引缓存

-- 使用MySQL Shell的索引建议功能
mysqlsh> util.checkForServerUpgrade()
mysqlsh> util.analyzeIndexes(schema, table)