MySQL中各种索引的创建和使用方法

1. 索引的基本概念

索引是帮助MySQL高效获取数据的数据结构，类似于书籍的目录，可以加快查询速度。

2. 创建索引的基本语法

2.1 创建表时创建索引

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 普通索引
    INDEX idx_username (username),
    
    -- 唯一索引
    UNIQUE INDEX idx_email (email),
    
    -- 复合索引
    INDEX idx_age_created (age, created_at),
    
    -- 全文索引（仅适用于MyISAM和InnoDB，MySQL 5.6+）
    FULLTEXT INDEX idx_username_fulltext (username)
) ENGINE=InnoDB;

2.2 使用ALTER TABLE添加索引

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_age_created (age, created_at);

-- 添加全文索引
ALTER TABLE users ADD FULLTEXT INDEX idx_username_fulltext (username);

-- 添加复合唯一索引
ALTER TABLE users 
ADD UNIQUE INDEX idx_unique_name (first_name, last_name);

-- 添加包含NULL值的复合唯一索引
ALTER TABLE users 
ADD UNIQUE INDEX idx_unique_email_department (email, department_id);

2.3 使用CREATE INDEX语句

-- 创建普通索引
CREATE INDEX idx_username ON users (username);

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

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

-- 创建复合唯一索引
CREATE UNIQUE INDEX idx_unique_name ON users (first_name, last_name);

-- 创建包含NULL值的复合唯一索引
CREATE UNIQUE INDEX idx_unique_email_department ON users (email, department_id);