索引

-- 没有索引：全表扫描
SELECT * FROM students WHERE name = '张三';  -- 需要扫描所有数据

-- 有索引：索引查找
-- 通过索引快速定位到数据位置

-- 性别只有两个值，重复度 50%
-- 使用索引反而更慢：先访问 50 万次索引，再访问 50 万次数据表
SELECT * FROM users WHERE gender = '男';  -- 100 万数据中 50 万条

-- 如果男性只占 0.001%（100 万人中只有 10 个男性）
-- 这时创建索引就有意义了
SELECT * FROM users WHERE gender = '男';  -- 只返回 10 条数据

-- 普通索引
CREATE INDEX idx_name ON students(name);

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

-- 主键索引（自动创建）
ALTER TABLE students ADD PRIMARY KEY (id);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 聚集索引查询（主键）
SELECT * FROM users WHERE id = 900001;  -- 0.043s

-- 非聚集索引查询（普通字段）
SELECT * FROM users WHERE name = 'student_890001';  -- 无索引: 0.961s

-- 创建非聚集索引后
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = 'student_890001';  -- 有索引: 0.050s

-- 单列索引
CREATE INDEX idx_name ON students(name);

-- 联合索引
CREATE INDEX idx_name_age ON students(name, age);

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

-- 可以使用索引的查询
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 不能使用索引的查询
WHERE b = 2           -- 跳过了 a
WHERE c = 3           -- 跳过了 a 和 b
WHERE b = 2 AND c = 3 -- 跳过了 a

-- 创建联合索引 (user_id, user_name)
CREATE INDEX idx_user ON users(user_id, user_name);

-- 可以使用索引
SELECT * FROM users WHERE user_id = 900001;                    -- 0.046s
SELECT * FROM users WHERE user_id = 900001 AND user_name = 'x'; -- 0.046s

-- 不能使用索引
SELECT * FROM users WHERE user_name = 'student_890001';        -- 0.943s

-- 覆盖索引示例
CREATE INDEX idx_covering ON orders(user_id, status, create_time);

-- 查询字段都在索引中，无需回表
SELECT status, create_time FROM orders WHERE user_id = 1;

-- 创建表时创建索引
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name),
    INDEX idx_age (age)
);

-- 在已有表上创建索引
CREATE INDEX idx_name ON students(name);
ALTER TABLE students ADD INDEX idx_name (name);

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

-- 创建联合索引
CREATE INDEX idx_name_age ON students(name, age);

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

-- 查看索引使用情况
EXPLAIN SELECT * FROM students WHERE name = '张三';

DROP INDEX idx_name ON students;
ALTER TABLE students DROP INDEX idx_name;

-- 索引失效
SELECT * FROM students WHERE YEAR(create_time) = 2024;

-- 索引生效
SELECT * FROM students WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 索引失效
SELECT * FROM students WHERE SUBSTRING(name, 1, 3) = '张三';

-- 索引生效
SELECT * FROM students WHERE name LIKE '张三%';

-- 索引失效（name 是字符串类型，但使用了数字）
SELECT * FROM students WHERE name = 123;

-- 索引生效
SELECT * FROM students WHERE name = '123';

-- 索引失效
SELECT * FROM students WHERE name LIKE '%张三';

-- 索引生效
SELECT * FROM students WHERE name LIKE '张三%';

-- 如果 OR 两边的字段都有索引，索引生效
-- 如果 OR 一边的字段没有索引，索引失效
SELECT * FROM students WHERE name = '张三' OR age = 20;

-- 索引可能失效
SELECT * FROM students WHERE age NOT IN (18, 19, 20);
SELECT * FROM students WHERE age <> 18;

-- 等值查询多：B+ 树索引
-- 范围查询多：B+ 树索引
-- 全文搜索：全文索引或搜索引擎

-- 索引不是越多越好
-- 每个索引都需要存储空间和维护成本
-- 建议：单表索引数量不超过 5 个

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

-- 查询字段都在索引中，避免回表
SELECT status, amount FROM orders WHERE user_id = 1;

-- 分析索引使用情况
SELECT * FROM sys.schema_index_statistics;

-- 更新优化器统计信息（不重建索引）
ANALYZE TABLE students;

-- 重组表并重建索引（回收空间）
OPTIMIZE TABLE students;