MySQL索引详解-高效查询的秘密

-- 创建表时指定主键索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 单独创建主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 特点：
-- ✅ 唯一且非空
-- ✅ 一个表只能有一个
-- ✅ 自动创建B+树结构

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 特点：
-- ✅ 允许NULL值
-- ✅ 值不能重复
-- ✅ 一个表可以有多个

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 特点：
-- ✅ 无唯一性限制
-- ✅ 可以重复
-- ✅ 最常用的索引类型

-- 创建复合索引
CREATE INDEX idx_age_name ON users(age, name);

-- 特点：
-- ✅ 多个列组合的索引
-- ✅ 遵循最左前缀原则
-- ✅ 可以Covering Index（覆盖索引）

-- 创建全文索引（MySQL 5.7+）
ALTER TABLE articles ADD FULLTEXT(title, content);

-- 使用全文索引
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

-- 特点：
-- ✅ 用于文本搜索
-- ✅ 支持中文需要配置分词器
-- ✅ 适用于MATCH...AGAINST语法

-- 创建空间索引
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    point POINT NOT NULL,
    SPATIAL INDEX idx_point (point)
);

-- 使用空间索引
SELECT * FROM locations 
WHERE ST_Distance(point, ST_GeomFromText('POINT(116 40)', 0)) < 1000;

-- 方法1：CREATE INDEX
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_age_name ON users(age, name);

-- 方法2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_name(name);
ALTER TABLE users ADD UNIQUE INDEX idx_email(email);
ALTER TABLE users ADD INDEX idx_age_name(age, name);

-- 方法3：创建表时创建
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_name (name),
    UNIQUE INDEX idx_email (email)
);

-- 查看表的所有索引
SHOW INDEX FROM users;

-- 或
SHOW KEYS FROM users;

-- 结果字段说明：
-- Non_unique: 0=唯一索引, 1=普通索引
-- Key_name: 索引名称
-- Seq_in_index: 索引中的序列号
-- Column_name: 列名
-- Cardinality: 基数（估算值）
-- Index_type: 索引类型（BTREE/HASH）

-- 方法1：DROP INDEX
DROP INDEX idx_name ON users;

-- 方法2：ALTER TABLE
ALTER TABLE users DROP INDEX idx_name;

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

-- 注意：删除主键需要先删除自增
ALTER TABLE users MODIFY id INT;
ALTER TABLE users DROP PRIMARY KEY;

-- MySQL不支持直接重命名，需要先删除再创建
DROP INDEX idx_old_name ON users;
CREATE INDEX idx_new_name ON users(name);

-- ✅ 适合建索引的列
-- 1. WHERE条件中频繁使用的列
WHERE status = 1

-- 2. ORDER BY排序的列
ORDER BY created_at DESC

-- 3. JOIN连接的列
ON user_id = id

-- 4. 区分度高的列（重复值少）
-- 区分度 = COUNT(DISTINCT col) / COUNT(*)
-- 越接近1越好

-- ❌ 不适合建索引的列
-- 1. 区分度低的列（性别、状态）
-- 2. 很少查询的列
-- 3. 表数据量小（<1000行）

-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_status_date (status, created_at);

-- ✅ 使用索引的情况
WHERE status = 1
WHERE status = 1 AND created_at > '2026-01-01'
WHERE created_at > '2026-01-01' AND status = 1  -- MySQL优化器会重排

-- ❌ 不使用索引的情况
WHERE created_at > '2026-01-01'
WHERE name = 'zhangsan'

-- 覆盖索引：查询的列都在索引中，无需回表

-- 创建复合索引（包含查询的列）
ALTER TABLE users ADD INDEX idx_age_name (age, name);

-- 使用覆盖索引
SELECT name, age FROM users WHERE age = 25;
-- Extra: Using index

-- ❌ 无法使用覆盖索引（需要id）
SELECT id, name, age FROM users WHERE age = 25;

-- 索引下推：在索引层过滤数据，减少回表

-- 假设有索引 (name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;

-- MySQL 5.6+ 自动使用索引下推
-- 在索引层过滤 name='张%' AND age=25
-- 只回表查询匹配的行

-- ❌ 1. 使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- ✅ 优化
SELECT * FROM users WHERE created_at >= '2026-01-01';

-- ❌ 2. 类型转换
SELECT * FROM users WHERE id = '1';  -- id是INT

-- ✅ 优化
SELECT * FROM users WHERE id = 1;

-- ❌ 3. 模糊查询以%开头
SELECT * FROM users WHERE name LIKE '%张';

-- ✅ 优化
SELECT * FROM users WHERE name LIKE '张%';

-- ❌ 4. OR导致索引失效
SELECT * FROM users WHERE name = 'zhangsan' OR age = 25;

-- ✅ 优化
SELECT * FROM users WHERE name = 'zhangsan'
UNION ALL
SELECT * FROM users WHERE age = 25;

-- ❌ 5. NOT条件
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age NOT IN (18, 25);

-- ✅ 优化（尽量使用正向条件）
SELECT * FROM users WHERE age > 25 OR age < 25;

-- ✅ 正确使用
SELECT * FROM users WHERE status = 1 AND age > 20;
-- 使用索引：idx_status_age (status, age)

-- ❌ 错误顺序
SELECT * FROM users WHERE age > 20 AND status = 1;
-- 可能不使用索引

-- 建议：保持SQL顺序与索引顺序一致

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

-- 查看慢查询中的索引使用
SHOW GLOBAL STATUS LIKE 'Handler_read%';

-- 说明：
-- Handler_read_key: 索引被读取次数（越高越好）
-- Handler_read_next: 索引顺序读取次数
-- Handler_read_prev: 索引逆序读取次数
-- Handler_read_rnd: 随机读取次数（越低越好）

-- 使用EXPLAIN分析
EXPLAIN SELECT * FROM users WHERE age = 25;

-- 查看索引基数（Cardinality）
SHOW INDEX FROM users;

-- 重建索引统计
ANALYZE TABLE users;

-- 优化表（重建索引）
OPTIMIZE TABLE users;

┌─────────────────────────────────────────────────────────────────┐
│                      索引设计原则                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  1️⃣ 数量适度 → 索引不是越多越好                                  │
│  2️⃣ 区分度优先 → 选择区分度高的列                                │
│  3️⃣ 复合索引 → 考虑查询组合                                      │
│  4️⃣ 覆盖索引 → 减少回表查询                                     │
│  5️⃣ 短索引 → 减少IO提升性能                                     │
│  6️⃣ 顺序一致 → SQL顺序与索引顺序匹配                             │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

-- 原始表结构（无索引）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status INT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    paid_at DATETIME
);

-- 添加索引
-- 1. 用户查询自己的订单
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 2. 状态筛选 + 时间排序
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 3. 用户 + 状态查询
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 4. 金额统计（覆盖索引）
ALTER TABLE orders ADD INDEX idx_amount (amount);

-- 优化后的查询
SELECT * FROM orders 
WHERE user_id = 123 
AND status = 1 
ORDER BY created_at DESC 
LIMIT 20;

-- 原始查询（慢）
SELECT * FROM users 
WHERE email = 'test@example.com' 
AND status = 1;

-- 分析：email唯一，status需要建索引
-- 添加复合索引
ALTER TABLE users ADD INDEX idx_email_status (email, status);

-- 覆盖索引优化（不需要回表）
SELECT id, name, email, status FROM users 
WHERE email = 'test@example.com' 
AND status = 1;

-- ❌ 错误：每个列都建索引
ALTER TABLE orders ADD INDEX idx_id (id);
ALTER TABLE orders ADD INDEX idx_created (created_at);
ALTER TABLE orders ADD INDEX idx_amount (amount);

-- ✅ 正确：按需创建，合理规划
-- 分析慢查询，定位高频查询场景
-- 创建复合索引覆盖查询

-- 1. 统计信息过期
ANALYZE TABLE table_name;

-- 2. 数据量太少
-- MySQL优化器可能选择全表扫描

-- 3. 索引选择错误
-- 使用 FORCE INDEX 强制使用索引
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'zhangsan';

-- 4. 参数配置问题
-- 检查 optimizer_switch