未记录事实核验 · 状态：维护中

记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。

📊 数据库索引技术详解

🔍 索引技术定义

数据库索引（Database Index）是一种独立的数据结构，用于加速数据检索操作，通过维护索引键到数据记录的访问结构来减少检索成本；树索引通常维护有序键，Hash索引则采用哈希组织，是否避免全表扫描取决于查询与执行计划。以平衡树索引的键定位为例，查找通常可达到O(log n)复杂度；完整查询成本还取决于匹配行数、回表、排序及执行计划，并非所有索引或查询都具有这一复杂度。

核心工作机制

B-Tree索引结构原理

       [50]
      /    \
   [25]    [75]
   /  \    /  \
[10][35][60][90]

索引内部工作流程：

1. 索引构建阶段：

   • 扫描表数据，提取索引列值
   • 按列值排序并构建B-Tree结构
   • 叶子节点内容取决于存储引擎和索引类型：可能包含行定位符、InnoDB二级索引中的主键值，或聚簇索引中的数据行

2. 查询执行阶段：

   • 解析WHERE条件中的索引列
   • 从根节点开始进行二分查找
   • 定位到叶子节点获取行指针
   • 根据行指针回表获取完整数据

3. 存储层面：

   • 非聚簇索引通常使用独立索引页；聚簇索引的叶子页本身存储数据行
   • 使用页分裂和合并维护平衡性
   • 通过缓冲池管理热点索引页

🏗️ 索引类型与特性

1. 聚簇索引（Clustered Index）

技术特性：数据行按索引键顺序物理存储，索引的叶子节点直接包含数据行，每个表只能有一个聚簇索引

-- MySQL InnoDB 主键自动创建聚簇索引
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,  -- 聚簇索引
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看聚簇索引信息
SHOW INDEX FROM users WHERE Key_name = 'PRIMARY';

性能特点：

• 范围查询性能优异（ORDER BY主键字段）
• 插入数据可能产生页分裂
• 主键查询无需回表操作

2. 非聚簇索引（Non-Clustered Index）

技术特性：非聚簇索引的行定位方式依赖存储引擎；例如MySQL InnoDB二级索引的叶子记录包含索引键值和主键值，通过主键访问聚簇索引获取未覆盖的列。

-- 创建非聚簇索引用于高频查询字段
CREATE INDEX idx_username ON users(username) 
USING BTREE 
COMMENT '用户名查询索引';

CREATE INDEX idx_email ON users(email)
USING BTREE
COMMENT '邮箱查询索引';

-- 查看索引统计信息
ANALYZE TABLE users;
SHOW INDEX FROM users WHERE Key_name = 'idx_username';

回表查询示例：

-- 需要回表的查询（索引覆盖不了所有字段）
EXPLAIN SELECT user_id, username, email, created_at 
FROM users WHERE username = 'john_doe';

3. 复合索引（Composite Index）

技术特性：多个字段组合的索引，遵循最左前缀匹配原则，索引列顺序影响查询优化效果

-- 复合索引设计：高选择性字段在前
CREATE INDEX idx_user_query ON users(status, created_at, username)
COMMENT '用户查询复合索引';

-- 支持的查询模式
SELECT * FROM users WHERE status = 'active';                    -- ✅ 使用索引
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';  -- ✅ 使用索引
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01' AND username LIKE 'john%';  -- 可利用status和created_at定位范围；username能否用于索引条件过滤等优化需结合执行计划判断，不能据此认定所有列都参与范围定位

-- 不支持的查询模式
SELECT * FROM users WHERE created_at > '2024-01-01';            -- 缺少最左列条件，通常不能按常规最左前缀进行范围定位；是否采用索引扫描或其他访问方式需查看执行计划
SELECT * FROM users WHERE username = 'john';                    -- 跳过前缀字段，通常不能按常规最左前缀进行范围定位；是否使用索引仍由优化器结合数据和查询决定

4. 唯一索引（Unique Index）

技术特性：在提供查询优化的同时，在索引层面强制字段值唯一性约束

-- 业务唯一性约束索引
CREATE UNIQUE INDEX idx_unique_email ON users(email)
COMMENT '邮箱唯一性索引';

CREATE UNIQUE INDEX idx_unique_phone ON users(phone)
COMMENT '手机号唯一性索引';

-- 复合唯一索引
CREATE UNIQUE INDEX idx_unique_user_tenant ON users(username, tenant_id)
COMMENT '多租户用户名唯一性索引';

5. 部分索引（Partial Index）

技术特性：仅对满足特定WHERE条件的行建立索引，减少索引大小和维护开销

-- PostgreSQL 部分索引
CREATE INDEX idx_active_users ON users(username, email)
WHERE status = 'active' AND deleted_at IS NULL;
COMMENT ON INDEX idx_active_users IS '活跃用户部分索引';

-- 设计设想：仅索引近30天订单。
-- 以下表达式包含NOW()，不能用于MySQL函数索引，也不能形成自动滚动的时间窗口：
-- CREATE INDEX idx_recent_orders ON orders((CASE WHEN created_at > DATE_SUB(NOW(), INTERVAL 30 DAY) THEN user_id END));
-- MySQL函数索引要求8.0.13+；近30天查询应另行评估普通复合索引、分区或由业务维护的标记字段。

6. Hash索引

技术特性：基于哈希算法，等值查询性能O(1)，不支持范围查询

-- MySQL Memory引擎支持Hash索引
CREATE TABLE user_sessions (
    session_id VARCHAR(64) NOT NULL,
    user_id INT NOT NULL,
    data VARCHAR(1024),
    INDEX idx_session_hash (session_id) USING HASH
) ENGINE=MEMORY;

7. 全文索引（Full-Text Index）

技术特性：专门用于文本搜索的倒排索引结构

-- 创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT idx_content_search (title, content) WITH PARSER ngram
);

-- 全文搜索查询
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库索引优化' IN BOOLEAN MODE);

⚡ 索引管理与操作

企业级索引创建策略

1. 索引选择性分析

-- 计算字段选择性（不同值占比）
SELECT 
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,
    COUNT(DISTINCT created_at) / COUNT(*) AS date_selectivity
FROM users;

-- 不同值占比不能单独决定是否建索引；需结合值分布、查询过滤比例、排序需求、查询频率及执行计划评估

2. 基于查询模式的索引设计

-- 分析慢查询日志确定索引需求
-- 高频等值查询：单字段索引
CREATE INDEX idx_user_status ON users(status) 
WHERE status IN ('active', 'pending', 'suspended');

-- 多条件复合查询：复合索引（选择性高的字段在前）
CREATE INDEX idx_order_query ON orders(status, user_id, created_date)
COMMENT '订单查询复合索引：状态+用户+时间';

-- 覆盖索引：避免回表查询
CREATE INDEX idx_user_profile_cover ON users(user_id, username, email, status)
COMMENT '用户资料覆盖索引';

3. 在线索引创建（生产环境）

-- MySQL 8.0+ 在线DDL操作
CREATE INDEX idx_username ON users(username)
COMMENT '用户名查询索引'
ALGORITHM=INPLACE LOCK=NONE;

-- PostgreSQL 并发索引创建
CREATE INDEX CONCURRENTLY idx_email ON users(email);

-- SQL Server 在线索引重建
ALTER INDEX idx_username ON users REBUILD WITH (ONLINE = ON);

索引监控与维护

1. 索引使用统计查询

-- MySQL 索引使用情况
SELECT 
    t.table_name,
    t.index_name,
    t.non_unique,
    t.cardinality,
    s.table_rows,
    ROUND(t.cardinality / s.table_rows * 100, 2) AS selectivity_percent
FROM information_schema.statistics t
JOIN information_schema.tables s ON t.table_name = s.table_name AND t.table_schema = s.table_schema
WHERE t.table_schema = 'your_database'
AND t.index_name != 'PRIMARY'
ORDER BY selectivity_percent DESC;

-- 查看未使用的索引
SELECT 
    object_schema AS database_name,
    object_name AS table_name,
    index_name,
    count_star AS index_usage_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema = 'your_database'
ORDER BY object_name;

2. 索引碎片分析和维护

-- MySQL InnoDB 索引碎片查询
SELECT 
    table_name,
    data_length,
    index_length,
    data_free,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS fragmentation_percent
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND data_free > 0
ORDER BY fragmentation_percent DESC;

-- 索引重建和优化
OPTIMIZE TABLE users;  -- MySQL
REINDEX INDEX idx_username;  -- PostgreSQL
ALTER INDEX idx_username REBUILD;  -- SQL Server

3. 索引大小和性能监控

-- 查看索引占用空间
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
AND database_name = 'your_database'
ORDER BY size_mb DESC;

-- 查看索引扫描统计
SHOW ENGINE INNODB STATUS\G

索引命名和文档规范

-- 企业级索引命名规范
-- 格式：idx_[table]_[columns]_[type]

-- 单字段索引
CREATE INDEX idx_users_email_btree ON users(email) USING BTREE
COMMENT '用户邮箱B-Tree索引，用于登录验证';

-- 复合索引
CREATE INDEX idx_orders_userid_status_date ON orders(user_id, status, created_date)
COMMENT '订单查询复合索引：用户ID+状态+创建时间，支持用户订单列表查询';

-- 唯一索引
CREATE UNIQUE INDEX uniq_users_phone ON users(phone)
COMMENT '用户手机号唯一索引，用于注册验证和快速登录';

-- 部分索引（PostgreSQL）
CREATE INDEX idx_orders_recent_processing ON orders(user_id, created_date)
WHERE status = 'processing' AND created_date >= CURRENT_DATE - INTERVAL '30 days'
COMMENT '近30天处理中订单索引，用于运营报表查询';

🎯 企业级业务场景索引设计

1. 电商用户系统完整索引方案

-- 用户表结构
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'suspended', 'deleted') DEFAULT 'active',
    registration_source VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    region VARCHAR(10),
    age TINYINT
);

-- 核心业务索引设计
-- 1. 用户登录验证（高频等值查询）
CREATE UNIQUE INDEX uniq_users_email ON users(email)
COMMENT '邮箱登录唯一索引，支持用户邮箱登录验证';

CREATE UNIQUE INDEX uniq_users_phone ON users(phone)
COMMENT '手机号唯一索引，支持手机验证码登录';

-- 2. 用户管理后台查询
CREATE INDEX idx_users_status_created ON users(status, created_at DESC)
COMMENT '用户状态管理索引，支持按状态筛选和注册时间排序';

-- 3. 用户行为分析
CREATE INDEX idx_users_region_age_status ON users(region, age, status)
COMMENT '用户画像分析索引，支持地区年龄段用户统计';

-- 4. 用户活跃度分析
CREATE INDEX idx_users_lastlogin_status ON users(last_login_at DESC, status)
WHERE status = 'active'
COMMENT '活跃用户分析索引，支持用户活跃度统计';

2. 订单系统高性能索引方案

-- 订单表结构
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_number VARCHAR(32) NOT NULL UNIQUE,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded'),
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20),
    order_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    shipped_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL
);

-- 业务场景索引设计
-- 1. 用户订单查询（C端我的订单页面）
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC)
COMMENT '用户订单列表索引，支持按时间倒序查询用户订单';

-- 2. 订单状态流转管理（B端订单管理）
CREATE INDEX idx_orders_status_updated ON orders(status, updated_at ASC)
COMMENT '订单状态管理索引，支持按状态批量处理订单';

-- 3. 财务报表统计查询
CREATE INDEX idx_orders_date_status_amount ON orders(order_date, status, total_amount)
COMMENT '财务统计索引，支持按日期和状态统计订单金额';

-- 4. 物流配送查询
CREATE INDEX idx_orders_shipping_date ON orders(status, shipped_at)
WHERE status IN ('shipped', 'delivered')
COMMENT '物流状态索引，支持配送进度查询';

-- 5. 覆盖索引避免回表查询
CREATE INDEX idx_orders_user_summary ON orders(user_id, order_id, status, total_amount, created_at)
COMMENT '用户订单摘要覆盖索引，避免回表查询提升性能';

3. 商品系统搜索优化索引

-- 商品表结构
CREATE TABLE products (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INT NOT NULL,
    brand_id INT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    sales_count INT DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT 0,
    status ENUM('active', 'inactive', 'out_of_stock') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 商品搜索和推荐索引
-- 1. 分类浏览索引
CREATE INDEX idx_products_category_price_sales ON products(category_id, price ASC, sales_count DESC)
WHERE status = 'active'
COMMENT '商品分类浏览索引，支持按价格和销量排序';

-- 2. 全文搜索索引
CREATE FULLTEXT INDEX idx_products_search ON products(name, description) 
WITH PARSER ngram
COMMENT '商品全文搜索索引，支持商品名称和描述搜索';

-- 3. 热销商品推荐
CREATE INDEX idx_products_hot_sales ON products(sales_count DESC, rating DESC, created_at DESC)
WHERE status = 'active' AND stock_quantity > 0
COMMENT '热销商品索引，支持销量和评分排序推荐';

-- 4. 品牌商品索引
CREATE INDEX idx_products_brand_category ON products(brand_id, category_id, price)
WHERE status = 'active'
COMMENT '品牌商品索引，支持品牌下分类商品查询';

-- 5. 库存管理索引
CREATE INDEX idx_products_stock_alert ON products(stock_quantity ASC, updated_at DESC)
WHERE status = 'active' AND stock_quantity <= 10
COMMENT '库存预警索引，支持低库存商品管理';

4. 内容管理系统索引设计

-- 文章内容表
CREATE TABLE articles (
    article_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,
    author_id BIGINT,
    category_id INT,
    tags JSON,
    status ENUM('draft', 'published', 'archived'),
    view_count BIGINT DEFAULT 0,
    like_count INT DEFAULT 0,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 内容检索和推荐索引
-- 1. 文章发布和管理
CREATE INDEX idx_articles_status_published ON articles(status, published_at DESC)
COMMENT '文章发布管理索引，支持按发布状态和时间查询';

-- 2. 分类文章浏览
CREATE INDEX idx_articles_category_published ON articles(category_id, published_at DESC)
WHERE status = 'published'
COMMENT '分类文章索引，支持分类下文章按时间排序';

-- 3. 作者文章管理
CREATE INDEX idx_articles_author_status ON articles(author_id, status, created_at DESC)
COMMENT '作者文章管理索引，支持作者查看自己的文章';

-- 4. 热门文章推荐
CREATE INDEX idx_articles_popularity ON articles(view_count DESC, like_count DESC, published_at DESC)
WHERE status = 'published'
COMMENT '热门文章索引，支持按浏览量和点赞数推荐';

-- 5. 全文搜索
CREATE FULLTEXT INDEX idx_articles_content_search ON articles(title, content)
WITH PARSER ngram
COMMENT '文章全文搜索索引';

⚠️ 索引设计最佳实践与注意事项

索引设计核心原则

1. 查询驱动索引设计

-- 基于实际查询模式设计索引，而非盲目创建
-- 分析慢查询日志，识别高频查询模式

-- 示例：电商订单查询分析
-- 查询1：用户查看自己的订单列表
SELECT order_id, status, total_amount, created_at 
FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 20;

-- 对应索引设计
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC)
COMMENT '支持用户订单列表查询的复合索引';

2. 索引选择性评估

-- 计算字段选择性，选择性 = 不同值数量 / 总行数
SELECT 
    'gender' AS field_name,
    COUNT(DISTINCT gender) AS distinct_values,
    COUNT(*) AS total_rows,
    ROUND(COUNT(DISTINCT gender) / COUNT(*), 4) AS selectivity
FROM users
UNION ALL
SELECT 
    'email' AS field_name,
    COUNT(DISTINCT email),
    COUNT(*),
    ROUND(COUNT(DISTINCT email) / COUNT(*), 4)
FROM users;

-- 选择性评估说明：
-- 0.1和0.01不是通用建索引阈值；不同值占比需与实际值分布和查询条件结合分析
-- 低不同值占比字段也可能因稀有值过滤、排序或覆盖查询而具有索引价值

3. 复合索引字段排序策略

-- 复合索引字段排序原则：
-- 1. 等值查询字段在前
-- 2. 范围查询字段在后
-- 3. 高选择性字段在前
-- 4. 考虑ORDER BY字段

-- 优化前：低效的索引设计
CREATE INDEX idx_orders_bad ON orders(created_at, user_id, status);

-- 优化后：基于查询模式的索引设计
-- 查询：SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_optimized ON orders(user_id, status, created_at DESC);

避免索引设计误区

1. 过度索引问题

-- 待评估：不能仅因字段不同值数量少就认定建索引错误
CREATE INDEX idx_users_gender ON users(gender);        -- 选择性 ~0.5
CREATE INDEX idx_users_status ON users(status);        -- 四值字段：按本文定义，选择性为不同值数量/总行数；各值的实际分布需另行统计
CREATE INDEX idx_orders_is_paid ON orders(is_paid);    -- 选择性 ~0.5

-- ❌ 错误：创建过多重复功能的索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_email_status ON users(email, status);  -- email已有唯一索引

-- ✅ 正确：基于实际查询需求合并索引
CREATE INDEX idx_users_profile_query ON users(status, region, age)
WHERE status != 'deleted'
COMMENT '用户画像分析复合索引';

2. 索引字段数据类型优化

-- ❌ 避免在大字段上建索引
CREATE INDEX idx_bad_text ON articles(content);  -- TEXT字段不适合

-- ✅ 使用前缀索引或全文索引
CREATE INDEX idx_title_prefix ON articles(title(50));  -- 前缀索引
CREATE FULLTEXT INDEX idx_content_search ON articles(content);  -- 全文索引

-- ❌ 避免在计算字段上频繁查询
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✅ 使用函数索引或冗余字段
-- MySQL 8.0.13+ 函数索引
CREATE INDEX idx_orders_year ON orders((YEAR(created_at)));
-- 或添加冗余字段
ALTER TABLE orders ADD COLUMN created_year YEAR GENERATED ALWAYS AS (YEAR(created_at));
CREATE INDEX idx_orders_created_year ON orders(created_year);

索引维护成本分析

1. 存储成本评估

-- 查看表和索引占用空间
SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND(index_length / data_length * 100, 2) AS index_ratio_percent
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND table_type = 'BASE TABLE'
ORDER BY index_length DESC;

-- 索引空间应结合工作负载、容量和维护成本评估，50%不是通用上限

2. 写入性能影响评估

-- 监控索引对写入性能的影响
-- 查看索引维护开销
SELECT 
    table_schema,
    table_name,
    COUNT(DISTINCT index_name) as index_count,
    COUNT(DISTINCT CASE WHEN non_unique = 0 THEN index_name END) as unique_indexes,
    COUNT(DISTINCT CASE WHEN non_unique = 1 THEN index_name END) as non_unique_indexes
FROM information_schema.statistics
WHERE table_schema = 'your_database'
GROUP BY table_schema, table_name
HAVING index_count > 8  -- 索引数量过多的表
ORDER BY index_count DESC;

3. 内存使用监控

-- 查看InnoDB缓冲池中索引页占用情况
SELECT 
    ROUND(
        (SELECT SUM(data_length + index_length) FROM information_schema.tables WHERE engine='InnoDB') / 1024 / 1024, 2
    ) AS total_innodb_mb,
    ROUND(
        (SELECT COUNT(*) FROM information_schema.innodb_buffer_page WHERE page_type LIKE 'INDEX') * 16 / 1024, 2
    ) AS buffer_index_mb;

企业级索引管理规范

1. 索引生命周期管理

-- 建立索引评估和清理机制
-- 1. 创建索引使用记录表
CREATE TABLE index_usage_tracking (
    table_name VARCHAR(64),
    index_name VARCHAR(64),
    creation_date DATE,
    last_used_date DATE,
    usage_count BIGINT DEFAULT 0,
    review_date DATE,
    status ENUM('active', 'monitoring', 'deprecated') DEFAULT 'active'
);

-- 2. 定期清理未使用索引的存储过程
DELIMITER //
CREATE PROCEDURE CleanupUnusedIndexes()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(64);
    DECLARE index_name VARCHAR(64);
    
    DECLARE unused_cursor CURSOR FOR 
        SELECT object_name, index_name
        FROM performance_schema.table_io_waits_summary_by_index_usage
        WHERE count_star = 0 
        AND index_name IS NOT NULL 
        AND object_schema = DATABASE();
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN unused_cursor;
    cleanup_loop: LOOP
        FETCH unused_cursor INTO table_name, index_name;
        IF done THEN
            LEAVE cleanup_loop;
        END IF;
        
        -- 记录待删除的索引
        INSERT INTO index_cleanup_log (table_name, index_name, cleanup_date)
        VALUES (table_name, index_name, CURDATE());
        
    END LOOP;
    CLOSE unused_cursor;
END //
DELIMITER ;

🚀 性能优化与查询执行计划分析

1. 查询执行计划深度分析

EXPLAIN详细解读

-- 基础EXPLAIN分析
EXPLAIN SELECT u.username, o.total_amount 
FROM users u 
JOIN orders o ON u.user_id = o.user_id 
WHERE u.status = 'active' 
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC 
LIMIT 10;

-- MySQL 8.0+ 格式化输出
EXPLAIN FORMAT=JSON 
SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'shipped' 
ORDER BY created_at DESC;

-- 执行计划关键指标解读：
-- type: ALL(全表扫描) < index(索引扫描) < range(范围查询) < ref(引用) < eq_ref(唯一引用) < const(常量)
-- key: 实际使用的索引名称
-- rows: 预计扫描的行数
-- filtered: 通过WHERE条件筛选后的行数百分比
-- Extra: 额外的执行信息

索引提示和强制使用

-- 使用索引提示
SELECT * FROM orders USE INDEX (idx_orders_user_created)
WHERE user_id = 12345 
ORDER BY created_at DESC;

-- 强制使用索引
SELECT * FROM orders FORCE INDEX (idx_orders_status_date)
WHERE status = 'pending' 
AND created_at > '2024-01-01';

-- 忽略特定索引
SELECT * FROM orders IGNORE INDEX (idx_orders_amount)
WHERE total_amount > 1000;

2. 索引性能监控体系

索引效率分析

-- MySQL 索引使用效率统计
SELECT 
    t.table_name,
    t.index_name,
    t.cardinality,
    s.table_rows,
    ROUND(t.cardinality / s.table_rows, 4) AS selectivity,
    tio.count_read,
    tio.sum_timer_read / 1000000000000 AS read_time_seconds,
    CASE 
        WHEN tio.count_read > 0 THEN 
            ROUND((tio.sum_timer_read / tio.count_read) / 1000000000, 2) 
        ELSE 0 
    END AS avg_read_time_ms
FROM information_schema.statistics t
JOIN information_schema.tables s ON t.table_name = s.table_name AND t.table_schema = s.table_schema
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage tio 
    ON tio.object_name = t.table_name AND tio.index_name = t.index_name
WHERE t.table_schema = 'your_database'
AND t.index_name != 'PRIMARY'
ORDER BY tio.count_read DESC;

慢查询与索引关联分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 分析慢查询中的索引使用情况
SELECT 
    digest_text,
    count_star AS execution_count,
    avg_timer_wait / 1000000000000 AS avg_seconds,
    sum_rows_examined / count_star AS avg_rows_examined,
    sum_rows_sent / count_star AS avg_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%SELECT%'
AND avg_timer_wait / 1000000000000 > 1
ORDER BY avg_timer_wait DESC
LIMIT 20;

3. 大数据表索引优化策略

分区表索引设计

-- 基于时间的分区表
CREATE TABLE orders_partitioned (
    order_id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled'),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 在分区表上创建索引，各分区均维护对应索引；索引名称中的p2024不限定索引作用范围
CREATE INDEX idx_orders_p2024_user ON orders_partitioned(user_id, created_at) 
COMMENT '2024分区用户订单索引';

-- 分区剪枝及分区内索引访问验证
EXPLAIN PARTITIONS 
SELECT * FROM orders_partitioned 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' 
AND user_id = 12345;

归档表索引策略

-- 热数据表（最近3个月）
CREATE TABLE orders_hot (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    -- 其他字段...
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 热数据表索引：覆盖常用查询
    INDEX idx_hot_user_created (user_id, created_at DESC),
    INDEX idx_hot_status_date (status, created_at),
    INDEX idx_hot_amount_range (total_amount, created_at)
) COMMENT='订单热数据表，最近3个月数据';

-- 冷数据归档表（历史数据）
CREATE TABLE orders_archive (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 其他字段...
    created_at TIMESTAMP,
    
    -- 归档表索引：仅保留必要索引
    INDEX idx_archive_user (user_id),
    INDEX idx_archive_date (created_at)
) COMMENT='订单归档表，历史数据存储';

-- 数据归档自动化脚本
DELIMITER //
CREATE EVENT evt_archive_old_orders
ON SCHEDULE EVERY 1 DAY
DO 
BEGIN
    -- 归档3个月前的数据
    INSERT INTO orders_archive 
    SELECT * FROM orders_hot 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);
    
    -- 删除已归档的数据
    DELETE FROM orders_hot 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);
END //
DELIMITER ;

4. 索引优化工具与监控

自动化索引建议工具

-- MySQL 8.0+ 索引建议查询
-- 基于performance_schema分析缺失的索引
SELECT 
    object_schema AS db_name,
    object_name AS table_name,
    COUNT(*) AS full_scan_count,
    GROUP_CONCAT(DISTINCT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(digest_text, 'WHERE', -1), ' ', 3)
    ) AS common_where_conditions
FROM performance_schema.events_statements_summary_by_digest pssbd
JOIN performance_schema.events_statements_current psc ON pssbd.digest = psc.digest
WHERE pssbd.sum_no_index_used > 0
AND pssbd.object_schema = 'your_database'
GROUP BY object_schema, object_name
ORDER BY full_scan_count DESC;

索引性能报告生成

-- 创建索引性能监控视图
CREATE VIEW v_index_performance_report AS
SELECT 
    t.table_name,
    t.index_name,
    t.cardinality,
    ROUND(t.cardinality / tab.table_rows * 100, 2) AS selectivity_percent,
    tio.count_read,
    tio.count_write,
    ROUND(tio.sum_timer_read / 1000000000000, 3) AS total_read_seconds,
    ROUND(tio.sum_timer_write / 1000000000000, 3) AS total_write_seconds,
    ROUND(
        (SELECT SUM(stat_value * @@innodb_page_size) 
         FROM mysql.innodb_index_stats 
         WHERE database_name = t.table_schema 
         AND table_name = t.table_name 
         AND index_name = t.index_name 
         AND stat_name = 'size') / 1024 / 1024, 2
    ) AS index_size_mb,
    CASE 
        WHEN tio.count_read = 0 THEN 'UNUSED'
        WHEN tio.count_read < 100 THEN 'LOW_USAGE'  
        WHEN tio.count_read < 1000 THEN 'MEDIUM_USAGE'
        ELSE 'HIGH_USAGE'
    END AS usage_category
FROM information_schema.statistics t
JOIN information_schema.tables tab ON t.table_name = tab.table_name AND t.table_schema = tab.table_schema
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage tio 
    ON tio.object_name = t.table_name AND tio.index_name = t.index_name
WHERE t.table_schema = 'your_database'
AND t.index_name != 'PRIMARY';

-- 生成索引优化建议报告
SELECT 
    table_name,
    index_name,
    index_size_mb,
    usage_category,
    CASE 
        WHEN usage_category = 'UNUSED' THEN 'Consider dropping this index'
        WHEN usage_category = 'LOW_USAGE' AND index_size_mb > 100 THEN 'Review index necessity'
        WHEN selectivity_percent < 1 THEN 'Low selectivity, consider composite index'
        ELSE 'Index performing well'
    END AS optimization_suggestion
FROM v_index_performance_report
WHERE usage_category IN ('UNUSED', 'LOW_USAGE') OR selectivity_percent < 1
ORDER BY index_size_mb DESC;

🛠️ 综合实战练习

练习1：电商平台索引架构设计

场景描述

为电商平台设计完整的索引体系，包括用户管理、订单处理、商品展示等核心功能。

-- 1. 创建基础表结构
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'suspended', 'deleted') DEFAULT 'active',
    registration_source VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL
);

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    sales_count INT DEFAULT 0,
    status ENUM('active', 'inactive', 'out_of_stock') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_number VARCHAR(32) NOT NULL UNIQUE,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded'),
    total_amount DECIMAL(10,2) NOT NULL,
    order_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. 索引设计任务
-- 任务A：为以下查询设计最优索引
-- 查询1：用户登录验证 (email/phone + password)
-- 查询2：用户管理后台 (按状态筛选，按注册时间排序)
-- 查询3：商品分类浏览 (category + price排序 + 有库存)
-- 查询4：我的订单页面 (user_id + 时间倒序)
-- 查询5：订单管理后台 (status + 更新时间)

-- 3. 索引创建参考答案
-- 用户登录索引
CREATE UNIQUE INDEX uniq_users_email ON users(email);
CREATE UNIQUE INDEX uniq_users_phone ON users(phone);

-- 用户管理索引
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

-- 商品浏览索引
CREATE INDEX idx_products_category_price ON products(category_id, price ASC)
WHERE status = 'active' AND stock_quantity > 0;

-- 订单查询索引
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
CREATE INDEX idx_orders_status_updated ON orders(status, updated_at ASC);

练习2：索引性能分析与优化

场景描述

基于合成测试数据进行索引效果验证和性能分析；测试规模与数据分布不代表真实业务结果。

-- 1. 测试数据目标：10万用户、100万订单；当前用户编号生成范围过滤后仅有99999个编号，插入仍缺password_hash，且未提供订单数据生成脚本
-- 用户数据生成
INSERT INTO users (username, email, phone, status, created_at)
SELECT 
    CONCAT('user_', n) AS username,
    CONCAT('user_', n, '@example.com') AS email,
    CONCAT('138', LPAD(n, 8, '0')) AS phone,
    CASE WHEN n % 10 = 0 THEN 'inactive' ELSE 'active' END AS status,
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY) AS created_at
FROM (
    SELECT a.n + b.n * 10 + c.n * 100 + d.n * 1000 + e.n * 10000 AS n
    FROM 
        (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN 
        (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN 
        (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN 
        (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
    CROSS JOIN 
        (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e
) nums
WHERE n BETWEEN 1 AND 100000;

-- 2. 性能测试对比
-- 测试1：无索引 vs 有索引的查询性能
SET profiling = 1;

-- 无索引查询（删除索引后测试）
SELECT * FROM users WHERE email = 'user_50000@example.com';

-- 有索引查询
SELECT * FROM users WHERE email = 'user_50000@example.com';

-- 查看性能对比
SHOW PROFILES;

-- 3. 执行计划分析任务
-- 分析以下查询的执行计划，并提出索引优化建议
EXPLAIN FORMAT=JSON 
SELECT u.username, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.status = 'active' 
AND u.created_at > '2024-01-01'
GROUP BY u.user_id, u.username
HAVING order_count > 5
ORDER BY order_count DESC
LIMIT 100;

练习3：复合索引最左前缀优化实战

场景描述

深度理解复合索引的最左前缀原理，并进行实际优化。

-- 1. 创建测试表和复合索引
CREATE TABLE order_items (
    item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    status ENUM('normal', 'refunded', 'exchanged') DEFAULT 'normal',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建复合索引（注意字段顺序）
CREATE INDEX idx_order_items_composite ON order_items(order_id, product_id, status, created_at);

-- 2. 测试不同查询模式的索引使用情况
-- 测试A：完全匹配最左前缀
EXPLAIN SELECT * FROM order_items WHERE order_id = 12345;
EXPLAIN SELECT * FROM order_items WHERE order_id = 12345 AND product_id = 100;
EXPLAIN SELECT * FROM order_items WHERE order_id = 12345 AND product_id = 100 AND status = 'normal';

-- 测试B：违反最左前缀原则
EXPLAIN SELECT * FROM order_items WHERE product_id = 100;
EXPLAIN SELECT * FROM order_items WHERE status = 'refunded';
EXPLAIN SELECT * FROM order_items WHERE product_id = 100 AND status = 'normal';

-- 测试C：范围查询对索引的影响
EXPLAIN SELECT * FROM order_items WHERE order_id = 12345 AND created_at > '2024-01-01';
EXPLAIN SELECT * FROM order_items WHERE order_id BETWEEN 10000 AND 20000 AND product_id = 100;

-- 3. 索引优化任务
-- 基于以下查询模式，重新设计索引策略：
-- 查询1：查看订单详情 (order_id) - 高频
-- 查询2：商品销售统计 (product_id + status) - 中频  
-- 查询3：退换货管理 (status + created_at) - 低频
-- 查询4：订单商品列表 (order_id + created_at DESC) - 高频

-- 优化方案：
-- 方案1：单一复合索引
CREATE INDEX idx_order_items_optimized ON order_items(order_id, created_at DESC, product_id, status);

-- 方案2：多个专用索引
CREATE INDEX idx_order_items_order ON order_items(order_id, created_at DESC);
CREATE INDEX idx_order_items_product_status ON order_items(product_id, status);
CREATE INDEX idx_order_items_status_date ON order_items(status, created_at);

练习4：大数据表分区索引实战

场景描述

为千万级订单表设计分区策略和索引方案。

-- 1. 创建分区表
CREATE TABLE orders_large (
    order_id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled'),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, order_date)
) 
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024), 
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 2. 分区索引设计任务
-- 为以下查询场景设计索引：
-- 场景1：用户查看近期订单 (user_id + order_date范围)
-- 场景2：财务日报统计 (order_date + status + total_amount)
-- 场景3：订单状态批量更新 (status + created_at)

-- 索引创建
CREATE INDEX idx_orders_user_date ON orders_large(user_id, order_date DESC);
CREATE INDEX idx_orders_daily_report ON orders_large(order_date, status, total_amount);
CREATE INDEX idx_orders_status_created ON orders_large(status, created_at);

-- 3. 分区剪枝验证
-- 验证查询是否能正确利用分区剪枝
EXPLAIN PARTITIONS 
SELECT COUNT(*), SUM(total_amount) 
FROM orders_large 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
AND status IN ('paid', 'delivered');

-- 4. 分区维护脚本
-- 分区扩展思路示例：下列ADD PARTITION过程不适用于已有MAXVALUE分区的orders_large；该表需重组p_future分区后才能增加具体年份分区
DELIMITER //
CREATE PROCEDURE CreateNewPartition(IN partition_year INT)
BEGIN
    SET @sql = CONCAT(
        'ALTER TABLE orders_large ADD PARTITION (',
        'PARTITION p', partition_year, ' VALUES LESS THAN (', partition_year + 1, '))'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

📊 技能检查清单

基础技能 ✅

• [ ] 理解B-Tree等索引数据结构的工作原理和时间复杂度
• [ ] 掌握聚簇索引、非聚簇索引、唯一索引等类型特性
• [ ] 能够使用SQL语句创建、修改、删除各种索引
• [ ] 理解索引对查询性能提升的机制（避免全表扫描）
• [ ] 掌握索引选择性的概念和计算方法

进阶技能 🚀

• [ ] 能够设计复合索引并熟练应用最左前缀匹配原则
• [ ] 掌握EXPLAIN执行计划分析，理解type、key、rows等关键指标
• [ ] 能够使用performance_schema监控索引使用情况和性能
• [ ] 理解索引对INSERT/UPDATE/DELETE操作的性能影响
• [ ] 掌握覆盖索引、部分索引、函数索引等高级索引技术
• [ ] 能够识别和避免索引设计的常见误区

专家技能 🎯

• [ ] 能够基于业务查询模式设计企业级索引架构体系
• [ ] 掌握分区表索引设计和大数据表索引优化策略
• [ ] 熟悉MySQL、PostgreSQL、SQL Server等不同数据库的索引特性差异
• [ ] 能够建立索引生命周期管理和自动化监控体系
• [ ] 掌握索引碎片分析、重建优化等维护技能
• [ ] 能够进行索引成本效益分析和容量规划

企业级应用技能 🏢

• [ ] 能够制定索引设计规范和命名标准
• [ ] 掌握生产环境在线索引创建和维护技术
• [ ] 能够进行慢查询分析和索引优化建议
• [ ] 理解索引与数据库集群、读写分离等架构的关系
• [ ] 掌握索引监控告警和性能报告生成技术

🔗 相关学习资源

核心概念链接

• SELECT语句结构：理解查询语句与索引的关系
• WHERE条件筛选：掌握索引优化的查询条件
• MySQL/慢查询优化：学习查询性能调优技术
• 事务管理：理解事务对索引的影响

架构设计相关

• PostgreSQL/PostgreSQL扩展与高级特性：了解PostgreSQL扩展与高级特性
• 场景实战案例/目录导览：浏览数据分析与数据运营场景实战案例
• 数据库架构设计：系统级别的索引规划

性能优化相关

• 存储优化：海量数据索引策略
• 数据库监控与调优：索引性能监控技术

---

📝 索引设计最佳实践总结

设计原则

1. 查询驱动设计：基于实际查询模式设计索引，避免盲目创建
2. 选择性评估：结合过滤比例、值分布、查询频率和执行计划评估索引；选择性 > 0.1不能作为通用建索引门槛
3. 复合索引排序：等值查询字段在前，范围查询字段在后
4. 覆盖索引应用：包含查询所需字段，减少回表操作

维护策略

1. 定期监控：使用performance_schema监控索引使用情况
2. 清理无效索引：定期清理未使用或低效的索引
3. 在线维护：在数据库、版本和操作支持的条件下使用在线DDL降低影响；仍需评估元数据锁、资源占用和失败风险，不能保证业务不中断
4. 碎片整理：定期重建高碎片率的索引

性能权衡

1. 读写平衡：权衡查询性能提升与写入性能损失
2. 存储成本：按工作负载、容量和维护成本评估索引空间；索引总大小不超过表数据大小的50%不是通用限制
3. 内存使用：关注索引在缓冲池中的内存占用
4. 维护开销：避免创建过多或过复杂的索引

专家建议 💡

• 生产环境索引变更必须经过充分测试和性能评估
• 建立索引设计评审机制，避免重复或冲突的索引
• 使用索引监控工具建立自动化的性能报告体系
• 制定明确的索引命名规范和生命周期管理流程