mysql中添加索引的3种方法及使用注意事项详解

一、三种添加索引的方法

1. CREATE INDEX语句

-- 创建普通索引
CREATE INDEX idx_name ON table_name(column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_name ON table_name(column_name);

-- 创建前缀索引（针对文本字段）
CREATE INDEX idx_name ON table_name(column_name(10));

-- 创建多列组合索引
CREATE INDEX idx_name ON table_name(col1, col2, col3);

2. ALTER TABLE语句

-- 添加普通索引
ALTER TABLE table_name ADD INDEX idx_name(column_name);

-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE idx_name(column_name);

-- 添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY(column_name);

-- 添加全文索引（仅MyISAM和InnoDB支持）
ALTER TABLE table_name ADD FULLTEXT idx_name(column_name);

3. 建表时创建索引

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 主键索引
    PRIMARY KEY (id),

    -- 唯一索引
    UNIQUE KEY uk_username (username),

    -- 普通索引
    KEY idx_email (email),

    -- 组合索引
    KEY idx_created_email (created_at, email(20)),

    -- 全文索引
    FULLTEXT idx_content (content)
) ENGINE=InnoDB;

二、索引类型详解

1. 主键索引 (PRIMARY KEY)

每个表只能有一个
值不能为NULL且必须唯一

2. 唯一索引 (UNIQUE)

确保列值的唯一性
允许NULL值（但只能有一个NULL）

3. 普通索引 (INDEX/KEY)

最基本的索引类型
仅用于加速查询

4. 组合索引 (Composite Index)

-- 正确的组合索引顺序应考虑查询条件和选择性
CREATE INDEX idx_user_date ON orders(user_id, order_date);

5. 全文索引 (FULLTEXT)

用于文本内容的全文搜索
仅支持CHAR、VARCHAR、TEXT类型

三、使用注意事项

1. 选择合适的列创建索引

✅ 适合创建索引的列：

WHERE子句中的列
JOIN连接条件的列
ORDER BY、GROUP BY涉及的列
选择性高的列（不同值多）

❌ 不适合创建索引的列：

数据量小的表（< 1000行）
频繁更新的列（索引维护成本高）
选择性低的列（如性别、状态标志）
BLOB/TEXT大字段（考虑前缀索引）

2. 组合索引的最佳实践

-- 最佳实践：遵循最左前缀原则
CREATE INDEX idx_a_b_c ON table(a, b, c);

-- 有效使用索引的查询：
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM table WHERE a = 1 ORDER BY b, c;

-- 无法使用索引的查询：
SELECT * FROM table WHERE b = 2;  -- 缺少最左列a
SELECT * FROM table WHERE a = 1 ORDER BY c;  -- 跳过了b

3. 避免索引失效的场景

-- 1. 对索引列进行运算或函数处理
SELECT * FROM users WHERE YEAR(created_at) = 2023;  -- ❌
SELECT * FROM users WHERE created_at >= '2023-01-01';  -- ✅

-- 2. 使用NOT、!=、<>操作符
SELECT * FROM users WHERE status != 'active';  -- ❌

-- 3. 使用OR条件连接（除非所有列都有索引）
SELECT * FROM users WHERE id = 1 OR username = 'john';  -- ❌

-- 4. LIKE以通配符开头
SELECT * FROM users WHERE username LIKE '%john%';  -- ❌
SELECT * FROM users WHERE username LIKE 'john%';   -- ✅

-- 5. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- ❌ phone是varchar
SELECT * FROM users WHERE phone = '13800138000';  -- ✅

4. 索引维护和管理

-- 查看表索引
SHOW INDEX FROM table_name;

-- 查看索引使用情况（需要开启性能模式）
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 删除索引
DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;

-- 重建索引（优化索引碎片）
ALTER TABLE table_name ENGINE=InnoDB;
OPTIMIZE TABLE table_name;

5. 性能监控

-- 查看索引使用统计
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_database';

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

四、实际应用建议

案例：电商订单表优化

-- 原始表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    product_id BIGINT,
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME
);

-- 优化后的索引策略
CREATE INDEX idx_user_status ON orders(user_id, status);
CREATE INDEX idx_created_user ON orders(created_at, user_id);
CREATE INDEX idx_product_status ON orders(product_id, status);

-- 分区表（针对超大表）
ALTER TABLE orders 
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);