-- 主键索引（PRIMARY KEY）
-- 唯一且非空，一张表只能有一个
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));

-- 唯一索引（UNIQUE）
-- 允许 NULL（可多个NULL），保证列值唯一
CREATE TABLE t2 (
    id INT PRIMARY KEY,
    phone VARCHAR(20) UNIQUE,
    email VARCHAR(100),
    UNIQUE KEY uk_email (email)
);

-- 普通索引（INDEX/KEY）
-- 仅加速查询，无唯一性约束
CREATE TABLE t3 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

-- 联合索引（复合索引）
-- 多个字段组合成一个索引，遵循最左前缀原则
CREATE TABLE t4 (
    id INT PRIMARY KEY,
    a INT,
    b INT,
    c INT,
    INDEX idx_a_b_c (a, b, c)
);

-- 建表时创建
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    dept_id INT,
    INDEX idx_name (name),
    UNIQUE INDEX idx_email (email),
    INDEX idx_dept_name (dept_id, name)
);

-- 建表后创建
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_dept_name ON users(dept_id, name);

-- 用 ALTER 创建
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
ALTER TABLE users ADD INDEX idx_dept_name (dept_id, name);

DROP INDEX idx_name ON users;
ALTER TABLE users DROP INDEX idx_name;

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

-- 或使用
SHOW CREATE TABLE users;

-- 1. WHERE 条件字段
-- 频繁作为查询条件的字段应该加索引
SELECT * FROM orders WHERE status = 1;  -- status 建索引

-- 2. JOIN 关联字段
-- 多表 JOIN 时，ON 后面的关联字段应建索引
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;  -- o.user_id, u.id 建索引

-- 3. ORDER BY 排序字段
-- 利用索引有序性避免文件排序
SELECT * FROM products ORDER BY create_time DESC;  -- create_time 建索引

-- 4. 覆盖索引字段
-- 如果查询的字段都在索引中，无需回表
-- 建联合索引 idx_a_b，查询 a,b 无需回表
SELECT a, b FROM t WHERE a = 1;  -- 覆盖索引

-- 5. 分组字段
SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id;  -- dept_id 建索引

-- 1. 数据量小的表（几千行以内），全表扫描更快
-- 2. 频繁更新的字段，索引维护开销大
-- 3. 区分度低的字段（如 gender：男/女），过滤效果差
-- 4. 长文本字段（如 TEXT、超长 VARCHAR），索引体积大且效率低

-- 准备大量数据
CREATE TABLE t_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    create_time DATETIME
);

-- 插入 100 万条数据（存储过程）
DELIMITER $$
CREATE PROCEDURE init_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000000 DO
        INSERT INTO t_user(name, age, email, create_time)
        VALUES (CONCAT('user', i), FLOOR(RAND()*100),
                CONCAT(i, '@email.com'), NOW());
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
CALL init_data();

-- 无索引查询：全表扫描
SELECT * FROM t_user WHERE name = 'user500000';  -- 耗时 800ms+

-- 创建索引后
CREATE INDEX idx_name ON t_user(name);

-- 再次查询：索引查找
SELECT * FROM t_user WHERE name = 'user500000';  -- 耗时 < 10ms

-- 业务查询模式
-- 场景1：按状态和创建时间查订单
SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC;

-- 场景2：按用户查订单
SELECT * FROM orders WHERE user_id = 100;

-- 场景3：按状态、用户、时间组合查
SELECT * FROM orders
WHERE status = 1 AND user_id = 100
ORDER BY create_time DESC;

-- 索引设计方案
-- 根据查询频率，优先为高频查询设计索引
CREATE INDEX idx_status_create ON orders(status, create_time);  -- 覆盖场景1
CREATE INDEX idx_user_id ON orders(user_id);                     -- 覆盖场景2
-- 场景3可以通过 idx_status_create 部分利用

-- 原始查询：需要回表
SELECT id, name, age FROM t_user WHERE name = 'user500000';
-- 索引 idx_name 找到 id，再回表查 age

-- 优化：创建覆盖索引
CREATE INDEX idx_name_age ON t_user(name, age);

-- 再次查询：无需回表，直接在索引中拿到 name 和 age
SELECT id, name, age FROM t_user WHERE name = 'user500000';

-- MySQL 5.6+ 支持索引下推
-- 联合索引 idx_name_age
SELECT * FROM t_user WHERE name LIKE 'user5%' AND age = 30;

-- 没有ICP：通过 name 条件找到主键，回表后再过滤 age
-- 启用ICP：在索引遍历时就同时过滤 age，减少回表次数
-- 默认启用，无需特殊配置

-- 写入变慢：INSERT 一条记录需要同时维护所有索引的 B+ 树
-- 占用空间：每个索引都是一棵 B+ 树
-- 优化器困扰：过多的索引会让优化器选错执行计划

-- 查看索引使用情况
SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key：索引读取次数（越高越好）
-- Handler_read_rnd_next：全表扫描次数（越低越好）

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

-- 选择性 = COUNT(DISTINCT col) / COUNT(*)
-- 选择性越高（接近1），索引效果越好
-- 选择性 < 20% 的字段不太适合单独建索引

-- 计算选择性
SELECT COUNT(DISTINCT name) / COUNT(*) AS selectivity FROM t_user;

CREATE INDEX idx_status_create ON orders(status, create_time);

CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create ON orders(create_time);