MySQL 索引详解

MySQL 索引详解 - 分类与原理

1. 索引概述

1.1 什么是索引

索引（Index）是数据库管理系统中一种数据结构，用于提高数据检索的效率。它类似于书籍的目录，通过建立数据的快速访问路径来避免全表扫描。

-- 创建索引示例
CREATE INDEX idx_user_name ON users(name);
CREATE INDEX idx_order_date ON orders(created_date);

1.2 索引的作用

  • 提高查询速度：通过索引快速定位数据位置
  • 加速连接操作：优化 JOIN 查询性能
  • 加速排序和分组：ORDER BY 和 GROUP BY 操作更快
  • 唯一性约束：确保数据的唯一性

1.3 索引的代价

  • 存储空间：索引需要额外的磁盘空间
  • 维护开销：INSERT、UPDATE、DELETE 时需要维护索引
  • 内存占用：索引会被加载到内存中

2. 索引分类详解

2.1 按数据结构分类

2.1.1 B+Tree 索引（默认）

B+Tree 是 MySQL 中最常用的索引类型，特别适合范围查询。

特点：

  • 所有叶子节点在同一层
  • 非叶子节点只存储键值，不存储数据
  • 叶子节点存储完整数据并通过指针连接
  • 支持顺序访问和随机访问
-- B+Tree索引示例
CREATE TABLE users (
    id INT PRIMARY KEY,           -- 主键自动创建B+Tree索引
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    INDEX idx_name (name),        -- 普通B+Tree索引
    INDEX idx_age_name (age, name) -- 复合B+Tree索引
);

B+Tree 结构图：

                    [50]
                   /    \
              [20,30]    [70,80]
             /   |   \   /   |   \
        [10,15] [25] [35] [60] [75] [90]
           |     |    |    |    |    |
        (数据)  (数据)(数据)(数据)(数据)(数据)

适用场景：

  • 等值查询：WHERE id = 100
  • 范围查询：WHERE age BETWEEN 20 AND 30
  • 排序查询：ORDER BY name
  • 前缀查询：WHERE name LIKE 'John%'

2.1.2 Hash 索引

Hash 索引使用哈希表实现，适合等值查询但不支持范围查询。

特点：

  • 查找时间复杂度 O(1)
  • 只支持等值比较（=, IN）
  • 不支持排序和范围查询
  • 主要用于 Memory 存储引擎
-- Hash索引示例（Memory引擎）
CREATE TABLE temp_data (
    id INT,
    code VARCHAR(10),
    HASH INDEX idx_code (code)
) ENGINE=MEMORY;

Hash 索引结构：

Hash函数: hash(key) % bucket_size

Bucket 0: [key1] -> 数据地址1
Bucket 1: [key2] -> 数据地址2
Bucket 2: [key3] -> 数据地址3
...

2.1.3 R-Tree 索引

R-Tree 索引主要用于空间数据类型（几何数据）。

-- R-Tree索引示例
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    coordinates GEOMETRY NOT NULL,
    SPATIAL INDEX idx_coordinates (coordinates)
);

-- 空间查询示例
SELECT * FROM locations 
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), 
    coordinates
);

2.1.4 Full-Text 索引

全文索引用于文本搜索，支持自然语言搜索和布尔搜索。

-- 全文索引示例
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX idx_content (title, content)
);

-- 全文搜索查询
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST ('MySQL 索引' IN NATURAL LANGUAGE MODE);

SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST ('+MySQL +索引 -性能' IN BOOLEAN MODE);

2.2 按物理存储分类

2.2.1 聚簇索引（Clustered Index）

定义： 数据行的物理顺序与索引的顺序完全相同，每个表只能有一个聚簇索引。

InnoDB 中的聚簇索引：

  • 主键自动成为聚簇索引
  • 如果没有主键，选择第一个非空唯一索引
  • 如果都没有，InnoDB 会创建一个隐藏的 6 字节 rowid
-- 聚簇索引示例
CREATE TABLE orders (
    order_id INT PRIMARY KEY,     -- 聚簇索引
    user_id INT,
    product_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);

聚簇索引结构：

B+Tree 叶子节点直接存储完整的数据行

         [主键索引]
            |
    ┌───────┼───────┐
   [10]   [20]   [30]
    |      |      |
[完整行] [完整行] [完整行]

优势：

  • 范围查询效率高
  • 排序查询快速
  • 覆盖查询性能好

劣势：

  • 插入顺序影响性能
  • 更新主键代价大
  • 二级索引需要两次查找

2.2.2 非聚簇索引（Secondary Index）

定义： 索引的逻辑顺序与数据行的物理顺序不同，叶子节点存储主键值。

-- 非聚簇索引示例
CREATE TABLE users (
    user_id INT PRIMARY KEY,      -- 聚簇索引
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    INDEX idx_name (name),        -- 非聚簇索引
    INDEX idx_email (email),      -- 非聚簇索引
    INDEX idx_age (age)           -- 非聚簇索引
);

非聚簇索引结构：

二级索引 B+Tree 叶子节点存储主键值

    [name索引]           [主键索引]
        |                    |
   ┌────┼────┐         ┌─────┼─────┐
  [Alice] [Bob] [Cindy] [1]  [2]  [3]
    |      |     |       |    |    |
   [1]    [2]   [3] -> [行] [行] [行]
   
回表查询：先通过二级索引找到主键，再通过主键找到完整数据

2.3 按字段个数分类

2.3.1 单列索引

-- 单列索引示例
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    category_id INT,
    
    INDEX idx_name (name),        -- 单列索引
    INDEX idx_price (price),      -- 单列索引
    INDEX idx_category (category_id) -- 单列索引
);

-- 使用单列索引的查询
SELECT * FROM products WHERE name = 'iPhone 13';
SELECT * FROM products WHERE price > 1000;

2.3.2 复合索引（联合索引）

复合索引是建立在多个列上的索引，遵循最左前缀原则。

-- 复合索引示例
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    created_date DATE,
    amount DECIMAL(10,2),
    
    -- 复合索引，注意字段顺序很重要
    INDEX idx_user_status_date (user_id, status, created_date),
    INDEX idx_date_amount (created_date, amount)
);

最左前缀原则：

-- 索引：(user_id, status, created_date)

-- ✅ 可以使用索引的查询
SELECT * FROM orders WHERE user_id = 100;
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' AND created_date = '2024-01-01';
SELECT * FROM orders WHERE user_id = 100 AND created_date = '2024-01-01'; -- 部分使用

-- ❌ 不能使用索引的查询
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE created_date = '2024-01-01';
SELECT * FROM orders WHERE status = 'paid' AND created_date = '2024-01-01';

复合索引设计原则：

  1. 区分度高的字段放前面
  2. 等值查询的字段放前面
  3. 范围查询的字段放后面
  4. 考虑查询频率
-- 复合索引设计示例
-- 查询场景分析
-- 1. WHERE user_id = ? AND status = ? (高频)
-- 2. WHERE user_id = ? AND created_date BETWEEN ? AND ? (中频)
-- 3. WHERE user_id = ? (低频)

-- 推荐索引设计
INDEX idx_user_status (user_id, status),           -- 覆盖场景1和3
INDEX idx_user_date (user_id, created_date);       -- 覆盖场景2

2.4 按功能分类

2.4.1 普通索引

最基本的索引类型，没有任何限制。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    
    INDEX idx_name (name)         -- 普通索引
);

2.4.2 唯一索引

保证索引列的值唯一，但允许有空值。

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    
    UNIQUE INDEX idx_username (username),    -- 唯一索引
    UNIQUE INDEX idx_email (email)          -- 唯一索引
);

-- 尝试插入重复值会报错
-- INSERT INTO users (username, email) VALUES ('john', 'john@email.com');
-- INSERT INTO users (username, email) VALUES ('john', 'jane@email.com'); -- 错误

2.4.3 主键索引

特殊的唯一索引，不允许空值，每个表只能有一个主键。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,     -- 主键索引
    user_id INT NOT NULL,
    amount DECIMAL(10,2)
);

-- 等价于
CREATE TABLE orders (
    order_id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id)       -- 显式定义主键
);

2.4.4 前缀索引

对字符串列的前几个字符建立索引，节省存储空间。

-- 前缀索引示例
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    
    INDEX idx_title_prefix (title(20))     -- 只对前20个字符建索引
);

-- 分析前缀长度的选择
SELECT 
    ROUND(COUNT(DISTINCT LEFT(title, 10)) / COUNT(*), 4) AS prefix_10,
    ROUND(COUNT(DISTINCT LEFT(title, 20)) / COUNT(*), 4) AS prefix_20,
    ROUND(COUNT(DISTINCT LEFT(title, 30)) / COUNT(*), 4) AS prefix_30,
    ROUND(COUNT(DISTINCT title) / COUNT(*), 4) AS full_column
FROM articles;

前缀长度选择原则：

  • 前缀的区分度接近完整列的区分度
  • 一般前缀区分度达到 0.31 以上就比较合适

3. 索引实现原理深入

3.1 B+Tree 详细原理

3.1.1 B+Tree 的特性

相比 B-Tree 的优势：

  1. 所有数据都在叶子节点：非叶子节点只存储键值，可以存储更多的键
  2. 叶子节点有链表连接：支持范围查询
  3. 查询性能稳定：所有查询都需要到达叶子节点
-- B+Tree 结构演示
-- 假设每个节点最多存储3个键值

-- 插入顺序：10, 20, 30, 40, 50, 60, 70, 80, 90

-- 最终B+Tree结构：
--                    [40]
--                   /    \
--              [20,30]    [60,80]
--             /   |   \   /   |   \
--        [10] [20] [30] [40,50] [60,70] [80,90]
--         |    |    |     |      |       |
--      (数据)(数据)(数据)(数据)  (数据)  (数据)

3.1.2 B+Tree 的查找过程

-- 查找 key = 65 的过程

-- 1. 从根节点开始：[40]
--    65 > 40，走右子树

-- 2. 到达节点：[60,80]  
--    60 <= 65 < 80，走中间子树

-- 3. 到达叶子节点：[60,70]
--    在叶子节点中查找 65，未找到

-- 时间复杂度：O(log n)

3.1.3 B+Tree 的插入和删除

插入操作：

-- 插入 key = 65

-- 1. 找到应该插入的叶子节点：[60,70]
-- 2. 插入后：[60,65,70]
-- 3. 如果节点满了，需要分裂
-- 4. 分裂可能向上传播到父节点

删除操作：

-- 删除 key = 30

-- 1. 找到叶子节点并删除
-- 2. 如果导致节点元素过少，需要合并或重新分布
-- 3. 合并可能向上传播

3.2 索引存储和缓存机制

3.2.1 InnoDB 索引存储

-- 查看索引信息
SHOW INDEX FROM users;

-- 查看表空间信息
SELECT 
    table_name,
    index_name,
    stat_value * @@innodb_page_size / 1024 / 1024 AS size_mb
FROM mysql.innodb_index_stats 
WHERE stat_name = 'size' AND table_name = 'users';

InnoDB 存储结构：

  • 页（Page）：最小的 I/O 单位，默认 16KB
  • 区（Extent）：连续的 64 个页，1MB
  • 段（Segment）：由多个区组成
┌─────────────────────────────────────────┐
│                  表空间                  │
├─────────────────────────────────────────┤
│  段1   │  段2   │  段3   │  段4   │ ... │
├─────────────────────────────────────────┤
│     区1     │     区2     │ ...  │     │
├─────────────────────────────────────────┤
│ 页1│页2│...│页64│页65│...│页128│...│   │
└─────────────────────────────────────────┘

3.2.2 索引缓存机制

Buffer Pool：

-- 查看 Buffer Pool 状态
SHOW ENGINE INNODB STATUS\G

-- 查看缓存命中率
SELECT 
    ROUND((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2) 
    AS buffer_pool_hit_rate
FROM (
    SELECT variable_value AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_reads'
) t1,
(
    SELECT variable_value AS Innodb_buffer_pool_read_requests  
    FROM performance_schema.global_status
    WHERE variable_name = 'Innodb_buffer_pool_read_requests'
) t2;

3.3 索引选择和优化

3.3.1 MySQL 优化器如何选择索引

-- 分析查询执行计划
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 AND status = 'paid' AND created_date > '2024-01-01';

-- 详细执行计划
EXPLAIN FORMAT=JSON SELECT * FROM orders 
WHERE user_id = 100 AND status = 'paid';

-- 查看索引使用统计
SELECT 
    table_schema,
    table_name, 
    index_name,
    cardinality,
    seq_in_index,
    column_name
FROM information_schema.statistics 
WHERE table_name = 'orders';

优化器选择索引的因素：

  1. 索引的选择性（Selectivity）
  2. 索引的基数（Cardinality）
  3. 查询的过滤条件
  4. 索引的覆盖程度

3.3.2 强制使用索引

-- 强制使用指定索引
SELECT * FROM orders 
USE INDEX (idx_user_status_date)
WHERE user_id = 100 AND status = 'paid';

-- 忽略指定索引
SELECT * FROM orders 
IGNORE INDEX (idx_user_status_date)
WHERE user_id = 100 AND status = 'paid';

-- 强制使用指定索引
SELECT * FROM orders 
FORCE INDEX (idx_user_status_date)
WHERE user_id = 100 AND status = 'paid';

4. 索引优化策略

4.1 索引设计原则

4.1.1 选择合适的列建索引

-- ✅ 适合建索引的列
CREATE TABLE user_logs (
    id INT PRIMARY KEY,
    user_id INT,                    -- 经常作为WHERE条件
    action VARCHAR(50),             -- 有限的几种值，区分度一般
    ip_address VARCHAR(15),         -- 区分度高
    created_at TIMESTAMP,           -- 经常用于排序和范围查询
    
    INDEX idx_user_id (user_id),
    INDEX idx_ip_address (ip_address),
    INDEX idx_created_at (created_at),
    INDEX idx_user_action (user_id, action)  -- 复合索引
);

-- ❌ 不适合建索引的列
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,                   -- 太大，不适合建普通索引
    is_published BOOLEAN,           -- 区分度太低（只有true/false）
    view_count INT,                 -- 频繁更新的列
    
    -- 不推荐
    -- INDEX idx_content (content),     -- 太大
    -- INDEX idx_is_published (is_published), -- 区分度低
    
    -- 推荐
    FULLTEXT INDEX idx_content_fulltext (content), -- 使用全文索引
    INDEX idx_published_views (is_published, view_count) -- 复合索引
);

4.1.2 索引长度优化

-- 分析字符串列的前缀分布
SELECT 
    LEFT(email, 5) AS prefix_5,
    COUNT(*) AS count,
    COUNT(*) / (SELECT COUNT(*) FROM users) AS percentage
FROM users 
GROUP BY LEFT(email, 5)
ORDER BY count DESC
LIMIT 10;

-- 选择合适的前缀长度
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

4.2 覆盖索引优化

覆盖索引是指查询所需的所有列都包含在索引中，避免回表查询。

-- 覆盖索引示例
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    amount DECIMAL(10,2),
    created_date DATE,
    
    -- 覆盖索引：包含查询所需的所有列
    INDEX idx_cover_user_status (user_id, status, amount, created_date)
);

-- 这个查询可以完全使用覆盖索引，不需要回表
SELECT user_id, status, amount, created_date 
FROM orders 
WHERE user_id = 100 AND status = 'paid';

-- 使用 EXPLAIN 验证
EXPLAIN SELECT user_id, status, amount, created_date 
FROM orders 
WHERE user_id = 100 AND status = 'paid';
-- Extra 列显示 "Using index" 表示使用了覆盖索引

4.3 索引合并优化

MySQL 可以使用多个索引来优化查询。

-- 索引合并示例
CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    brand_id INT,
    price DECIMAL(10,2),
    status VARCHAR(20),
    
    INDEX idx_category (category_id),
    INDEX idx_brand (brand_id),
    INDEX idx_price (price),
    INDEX idx_status (status)
);

-- MySQL 可能使用索引合并
SELECT * FROM products 
WHERE category_id = 1 AND brand_id = 2;

-- 查看执行计划
EXPLAIN SELECT * FROM products 
WHERE category_id = 1 AND brand_id = 2;
-- type 可能显示 index_merge
-- Extra 可能显示 Using intersect(idx_category,idx_brand)

4.4 分区表索引

-- 分区表索引示例
CREATE TABLE sales_data (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10,2),
    region VARCHAR(50),
    
    PRIMARY KEY (id, sale_date),
    INDEX idx_customer (customer_id),
    INDEX idx_region (region)
) 
PARTITION BY RANGE (YEAR(sale_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
);

-- 分区裁剪查询
SELECT * FROM sales_data 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
AND customer_id = 100;

5. 索引监控和维护

5.1 索引使用情况监控

-- 查看索引使用统计
SELECT 
    t.table_schema,
    t.table_name,
    s.index_name,
    s.cardinality,
    s.sub_part,
    s.nullable,
    s.index_type,
    CASE 
        WHEN s.index_name = 'PRIMARY' THEN '主键索引'
        WHEN s.non_unique = 0 THEN '唯一索引'
        ELSE '普通索引'
    END AS index_category
FROM information_schema.tables t
JOIN information_schema.statistics s ON t.table_name = s.table_name 
WHERE t.table_schema = 'your_database'
ORDER BY t.table_name, s.seq_in_index;

-- 查看未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
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_schema, object_name;

-- 查看索引效率
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    sum_timer_read,
    avg_timer_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY sum_timer_read DESC;

5.2 索引碎片分析

-- 查看表和索引的碎片情况
SELECT 
    table_schema,
    table_name,
    data_length,
    index_length,
    data_free,
    (data_free / (data_length + index_length)) AS fragmentation_ratio
FROM information_schema.tables 
WHERE table_schema = 'your_database'
AND data_free > 0
ORDER BY fragmentation_ratio DESC;

-- 重建索引减少碎片
ALTER TABLE table_name ENGINE=InnoDB;

-- 或者使用 OPTIMIZE TABLE
OPTIMIZE TABLE table_name;

5.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;

-- 分析表的存储占用
SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY total_mb DESC;

6. 索引常见问题和解决方案

6.1 索引失效场景

-- 索引失效的常见情况

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    status TINYINT,
    
    INDEX idx_name (name),
    INDEX idx_age (age),
    INDEX idx_email (email),
    INDEX idx_name_age (name, age)
);

-- ❌ 使用函数导致索引失效
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
-- ✅ 正确写法
SELECT * FROM users WHERE name = 'John';

-- ❌ 隐式类型转换导致索引失效
SELECT * FROM users WHERE age = '25';  -- age是INT类型
-- ✅ 正确写法  
SELECT * FROM users WHERE age = 25;

-- ❌ 使用 OR 连接不同列
SELECT * FROM users WHERE name = 'John' OR age = 25;
-- ✅ 使用 UNION
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE age = 25;

-- ❌ LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%john%';
-- ✅ 前缀匹配可以使用索引
SELECT * FROM users WHERE name LIKE 'john%';

-- ❌ 在 WHERE 中对索引列进行计算
SELECT * FROM users WHERE age + 1 = 26;
-- ✅ 正确写法
SELECT * FROM users WHERE age = 25;

-- ❌ 复合索引不遵循最左前缀
-- 索引：(name, age)
SELECT * FROM users WHERE age = 25;  -- 不能使用索引
-- ✅ 遵循最左前缀
SELECT * FROM users WHERE name = 'John' AND age = 25;

6.2 慢查询优化

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询
-- 使用 mysqldumpslow 工具
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 优化示例：从慢查询到快查询
-- 原始慢查询
SELECT o.*, u.name, p.name as product_name 
FROM orders o
JOIN users u ON o.user_id = u.id  
JOIN products p ON o.product_id = p.id
WHERE o.created_date >= '2024-01-01'
AND o.status = 'paid'
ORDER BY o.created_date DESC
LIMIT 20;

-- 分析执行计划
EXPLAIN SELECT o.*, u.name, p.name as product_name 
FROM orders o
JOIN users u ON o.user_id = u.id  
JOIN products p ON o.product_id = p.id
WHERE o.created_date >= '2024-01-01'
AND o.status = 'paid'
ORDER BY o.created_date DESC
LIMIT 20;

-- 添加合适的索引
ALTER TABLE orders ADD INDEX idx_status_date (status, created_date);
ALTER TABLE users ADD INDEX idx_id_name (id, name);
ALTER TABLE products ADD INDEX idx_id_name (id, name);

-- 优化后的查询性能提升明显

6.3 索引设计最佳实践

-- 1. 为经常查询的列建立索引
CREATE TABLE user_activities (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    ip_address VARCHAR(15),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 基于查询模式设计索引
    INDEX idx_user_id (user_id),                          -- 单用户查询
    INDEX idx_activity_type (activity_type),              -- 按活动类型查询
    INDEX idx_created_at (created_at),                    -- 时间范围查询
    INDEX idx_user_activity (user_id, activity_type),     -- 用户活动类型查询
    INDEX idx_user_time (user_id, created_at),            -- 用户时间范围查询
    INDEX idx_ip_time (ip_address, created_at)            -- IP时间查询
);

-- 2. 考虑查询的完整场景
-- 场景1：查看用户最近的活动
SELECT * FROM user_activities 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;
-- 使用索引：idx_user_time

-- 场景2：统计某类活动的数量
SELECT activity_type, COUNT(*) 
FROM user_activities 
WHERE created_at >= '2024-01-01'
GROUP BY activity_type;
-- 使用索引：idx_created_at

-- 场景3：查找可疑IP的活动
SELECT user_id, activity_type, created_at
FROM user_activities 
WHERE ip_address = '192.168.1.100'
AND created_at >= '2024-01-01';
-- 使用索引：idx_ip_time

7. 面试常见问题

7.1 基础概念题

Q1: 什么是索引？索引的优缺点是什么？

答案要点：

  • 定义：索引是数据库表中一列或多列的排序数据结构，用于快速定位数据
  • 优点：提高查询速度、加速连接和排序、支持唯一性约束
  • 缺点：占用额外存储空间、降低写操作性能、需要维护成本

Q2: B+Tree 和 Hash 索引的区别？

特性 B+Tree Hash
等值查询 O(log n) O(1)
范围查询 支持 不支持
排序 支持 不支持
模糊查询 支持前缀 不支持
存储引擎 InnoDB, MyISAM Memory

7.2 原理深入题

Q3: 为什么 InnoDB 选择 B+Tree 而不是 B-Tree？

答案要点：

  1. 更高的扇出度：非叶子节点只存储键值，可以存储更多索引项
  2. 稳定的查询性能：所有数据都在叶子节点，查询路径长度一致
  3. 更好的范围查询：叶子节点通过链表连接，支持高效的范围扫描
  4. 更好的缓存效果：非叶子节点不存储数据，可以缓存更多的索引项

Q4: 聚簇索引和非聚簇索引的区别？

答案要点：

  • 聚簇索引：数据和索引存储在一起，叶子节点存储完整行数据
  • 非聚簇索引：索引和数据分离，叶子节点存储主键值，需要回表查询
  • 性能影响：聚簇索引减少 I/O，但非聚簇索引可能需要两次查询

7.3 优化实践题

Q5: 如何分析和优化慢查询？

答案步骤：

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

-- 2. 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- 3. 查看索引使用情况
SHOW INDEX FROM orders;

-- 4. 添加合适的索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 5. 重新分析性能
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

Q6: 什么情况下索引会失效？

答案要点：

  1. 使用函数或表达式
  2. 隐式类型转换
  3. 使用 OR 连接不同列
  4. LIKE 以通配符开头
  5. 复合索引不遵循最左前缀原则

8. 总结

8.1 索引设计原则总结

  1. 选择性原则：为区分度高的列建立索引
  2. 最左前缀原则：复合索引要考虑查询模式
  3. 覆盖索引原则：尽量使用覆盖索引避免回表
  4. 适度原则：不要过度创建索引，影响写性能

8.2 性能优化要点

  1. 定期分析索引使用情况
  2. 删除未使用的索引
  3. 监控索引碎片情况
  4. 合理设计复合索引
  5. 使用前缀索引优化长字符串

8.3 监控指标

指标 说明 监控方法
索引使用率 索引被使用的频率 performance_schema
索引大小 索引占用的存储空间 information_schema
碎片率 索引的碎片程度 OPTIMIZE TABLE
查询性能 查询响应时间 慢查询日志

掌握这些索引知识点，能够帮助你在数据库设计和优化中做出正确的决策，显著提升数据库查询性能。

posted @ 2025-08-25 12:50  MadLongTom  阅读(28)  评论(0)    收藏  举报