创建索引的最佳实践

-- 1. WHERE条件列
SELECT * FROM orders WHERE user_id = 123;
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. ORDER BY排序列
SELECT * FROM products ORDER BY price DESC;
CREATE INDEX idx_price ON products(price);

-- 3. GROUP BY分组列
SELECT category, COUNT(*) FROM products GROUP BY category;
CREATE INDEX idx_category ON products(category);

-- 4. JOIN连接列
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
CREATE INDEX idx_user_id ON orders(user_id);

-- 5. DISTINCT去重列
SELECT DISTINCT category FROM products;
CREATE INDEX idx_category ON products(category);

-- 1. 小表（< 1000行）
-- 全表扫描更快

-- 2. 频繁更新的列
-- 维护索引代价高

-- 3. 区分度低的列（选择性 < 0.01）
SELECT * FROM users WHERE gender = '男';  -- 50%数据
-- 不如全表扫描

-- 4. 不在WHERE/ORDER BY/JOIN中使用的列
-- 纯粹浪费空间

-- 计算列的选择性
SELECT
    COUNT(DISTINCT column) / COUNT(*) AS selectivity
FROM table_name;

-- 选择性 > 0.1：适合索引
-- 选择性 < 0.01：不适合索引

-- 身份证号：选择性 ≈ 1（唯一）✅
-- 邮箱：选择性 ≈ 0.95 ✅
-- 年龄：选择性 ≈ 0.02 ❌
-- 性别：选择性 ≈ 0.005 ❌

-- 统计查询频率
SELECT
    query_text,
    COUNT(*) AS frequency
FROM slow_log
WHERE query_text LIKE '%WHERE user_id%'
GROUP BY query_text;

-- ✅ 好：INT类型（4字节）
CREATE INDEX idx_user_id ON orders(user_id);

-- ❌ 差：VARCHAR(500)（最大2000字节）
CREATE INDEX idx_description ON products(description);  -- 太长

-- 创建联合索引
CREATE INDEX idx_a_b_c ON table(a, b, c);

-- 可以使用索引
WHERE a = 1;
WHERE a = 1 AND b = 2;
WHERE a = 1 AND b = 2 AND c = 3;
WHERE a = 1 AND c = 3;  -- 只用到a

-- 不能使用索引
WHERE b = 2;
WHERE c = 3;
WHERE b = 2 AND c = 3;

-- ❌ 差
CREATE INDEX idx_date_user ON orders(created_at, user_id);
WHERE user_id = 123 AND created_at > '2024-01-01';
-- user_id是等值，应该在前

-- ✅ 好
CREATE INDEX idx_user_date ON orders(user_id, created_at);
WHERE user_id = 123 AND created_at > '2024-01-01';

-- ❌ 差：冗余
CREATE INDEX idx_a ON table(a);
CREATE INDEX idx_a_b ON table(a, b);  -- idx_a冗余

-- ✅ 好：只保留联合索引
DROP INDEX idx_a;
CREATE INDEX idx_a_b ON table(a, b);

-- 对长字符串列使用前缀索引
CREATE INDEX idx_email ON users(email(20));  -- 只索引前20个字符

-- 计算合适的前缀长度
SELECT
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel_20,
    COUNT(DISTINCT LEFT(email, 30)) / COUNT(*) AS sel_30,
    COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;
-- 选择选择性接近完整列的最短前缀

-- ❌ 差：太多列
CREATE INDEX idx_many ON table(a, b, c, d, e, f);  -- 6列

-- ✅ 好：通常不超过3-4列
CREATE INDEX idx_few ON table(a, b, c);

-- 单列索引：idx_列名
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);

-- 联合索引：idx_列1_列2_列3
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 唯一索引：uk_列名（unique key）
CREATE UNIQUE INDEX uk_email ON users(email);

-- 全文索引：ft_列名（fulltext）
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- ❌ 差
CREATE INDEX idx1 ON users(name);
CREATE INDEX index_temp ON products(category);

-- ✅ 好
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_category ON products(category);

-- 查看表的索引数量
SELECT
    TABLE_NAME,
    COUNT(*) AS index_count
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
GROUP BY TABLE_NAME
HAVING COUNT(*) > 5
ORDER BY index_count DESC;

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

-- 或重建表
ALTER TABLE users ENGINE=InnoDB;

-- 查找未使用的索引（MySQL 8.0+）
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 index_name != 'PRIMARY'
ORDER BY object_schema, object_name;

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50) UNIQUE,                    -- 唯一索引
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10,2),
    created_at DATETIME NOT NULL,
    paid_at DATETIME,

    INDEX idx_user_id (user_id),                    -- 单列索引：用户查询
    INDEX idx_status_created (status, created_at),  -- 联合索引：状态+时间查询
    INDEX idx_created_at (created_at)               -- 单列索引：时间范围查询
) ENGINE=InnoDB;

-- 场景1：用户订单列表
WHERE user_id = 123;  -- 使用idx_user_id

-- 场景2：待处理订单
WHERE status = 'pending' AND created_at > '2024-01-01';  -- 使用idx_status_created

-- 场景3：近7天订单
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);  -- 使用idx_created_at

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    sales INT DEFAULT 0,
    created_at DATETIME,

    INDEX idx_category_price (category, price),     -- 分类+价格排序
    INDEX idx_sales (sales),                        -- 销量排序
    FULLTEXT INDEX ft_name (name)                   -- 全文搜索
) ENGINE=InnoDB;