-- ❌ 索引失效：对索引列使用函数
CREATE INDEX idx_create_time ON orders(create_time);

-- 失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
SELECT * FROM orders WHERE DATE(create_time) = '2024-11-10';

-- ✅ 优化：不使用函数
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2025-01-01';

SELECT * FROM orders 
WHERE create_time >= '2024-11-10 00:00:00' 
  AND create_time < '2024-11-10 23:59:59';

-- ❌ 索引失效：对索引列进行运算
CREATE INDEX idx_age ON users(age);

-- 失效
SELECT * FROM users WHERE age + 1 = 26;

-- ✅ 优化
SELECT * FROM users WHERE age = 25;

-- ❌ 索引失效：字符串拼接
CREATE INDEX idx_name ON users(name);

-- 失效
SELECT * FROM users WHERE CONCAT(name, '先生') = '张三先生';

-- ✅ 优化
SELECT * FROM users WHERE name = '张三';

-- ❌ 索引失效：类型不匹配
CREATE INDEX idx_phone ON users(phone);  -- phone是VARCHAR

-- 失效（字符串列与数字比较）
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 优化：使用字符串
SELECT * FROM users WHERE phone = '13800138000';

-- ❌ 索引失效：数字列与字符串比较
CREATE INDEX idx_user_id ON orders(user_id);  -- user_id是INT

-- 失效
SELECT * FROM orders WHERE user_id = '100';

-- ✅ 优化
SELECT * FROM orders WHERE user_id = 100;

-- 规则：
-- 字符串列 = 数字 → 索引失效
-- 数字列 = 字符串 → 索引有效（MySQL会转换字符串）

CREATE INDEX idx_name ON users(name);

-- ❌ 索引失效：以%开头
SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';

-- ✅ 索引有效：%在结尾
SELECT * FROM users WHERE name LIKE '张三%';

-- 解决方案：使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张三');

CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);

-- ❌ 索引失效：OR连接的列没有都建索引
SELECT * FROM users WHERE age = 25 OR email = 'test@example.com';
-- email没有索引，导致全表扫描

-- ✅ 优化1：都建索引
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE age = 25 OR email = 'test@example.com';

-- ✅ 优化2：改用UNION
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE email = 'test@example.com';

-- ✅ 优化3：改用IN（如果是同一列）
SELECT * FROM users WHERE age IN (25, 30, 35);

CREATE INDEX idx_status ON orders(status);

-- ❌ 索引失效：NOT、!=、<>
SELECT * FROM orders WHERE status != 1;
SELECT * FROM orders WHERE status <> 1;
SELECT * FROM orders WHERE NOT status = 1;

-- ✅ 优化：改用IN或范围查询
SELECT * FROM orders WHERE status IN (0, 2, 3);
SELECT * FROM orders WHERE status > 1 OR status < 1;

CREATE INDEX idx_deleted_at ON users(deleted_at);

-- ⚠️ 可能失效：IS NULL、IS NOT NULL
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- 是否使用索引取决于NULL值的比例
-- 如果NULL值很多，IS NOT NULL可能不走索引
-- 如果NULL值很少，IS NULL可能不走索引

-- ✅ 优化：避免NULL值
-- 使用默认值代替NULL
ALTER TABLE users MODIFY deleted_at DATETIME DEFAULT '1970-01-01 00:00:00';

CREATE INDEX idx_abc ON users(a, b, c);

-- ✅ 使用索引
SELECT * FROM users WHERE a = 1;
SELECT * FROM users WHERE a = 1 AND b = 2;
SELECT * FROM users WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM users WHERE a = 1 AND c = 3;  -- 只用到a

-- ❌ 不使用索引
SELECT * FROM users WHERE b = 2;
SELECT * FROM users WHERE c = 3;
SELECT * FROM users WHERE b = 2 AND c = 3;

-- 最左前缀原则：
-- 必须从最左边的列开始，不能跳过中间的列

-- 场景：经常查询 WHERE city = ? AND age = ?

-- 方案1：(city, age)
CREATE INDEX idx_city_age ON users(city, age);

-- 方案2：(age, city)
CREATE INDEX idx_age_city ON users(age, city);

-- 如何选择？
-- 原则1：区分度高的列放前面
-- 原则2：查询频率高的列放前面
-- 原则3：范围查询的列放后面

-- 分析区分度
SELECT 
  COUNT(DISTINCT city) / COUNT(*) AS city_selectivity,
  COUNT(DISTINCT age) / COUNT(*) AS age_selectivity
FROM users;

-- 假设结果：
-- city_selectivity: 0.05 (100个城市/2000条记录)
-- age_selectivity: 0.02 (40个年龄/2000条记录)

-- 结论：city区分度更高，应该放前面
CREATE INDEX idx_city_age ON users(city, age);

-- 覆盖索引：索引包含了查询需要的所有列，不需要回表

-- ❌ 需要回表
CREATE INDEX idx_age ON users(age);
SELECT id, name, age FROM users WHERE age = 25;
-- 执行过程：
-- 1. 通过idx_age找到age=25的记录的主键id
-- 2. 回表查询name列

-- ✅ 覆盖索引（不需要回表）
CREATE INDEX idx_age_name ON users(age, name);
SELECT id, name, age FROM users WHERE age = 25;
-- 执行过程：
-- 1. 通过idx_age_name直接获取id、age、name
-- 2. 不需要回表

-- EXPLAIN中的Extra显示"Using index"表示使用了覆盖索引
EXPLAIN SELECT id, name, age FROM users WHERE age = 25;

-- 覆盖索引的优势：
-- ✅ 减少I/O（不需要回表）
-- ✅ 提高查询速度
-- ✅ 减少随机I/O

-- 覆盖索引的应用场景：
-- 1. 分页查询
-- 2. 统计查询
-- 3. 只查询索引列

-- 索引下推（Index Condition Pushdown）：
-- MySQL 5.6引入，将WHERE条件下推到存储引擎层

CREATE INDEX idx_city_age ON users(city, age);

-- 查询
SELECT * FROM users WHERE city = '北京' AND age > 25 AND name LIKE '张%';

-- 没有ICP：
-- 1. 存储引擎返回city='北京'的所有记录
-- 2. Server层过滤age>25和name LIKE '张%'

-- 有ICP：
-- 1. 存储引擎过滤city='北京' AND age>25
-- 2. Server层只过滤name LIKE '张%'
-- 3. 减少回表次数

-- EXPLAIN中的Extra显示"Using index condition"表示使用了ICP
EXPLAIN SELECT * FROM users WHERE city = '北京' AND age > 25 AND name LIKE '张%';

-- 开启/关闭ICP
SET optimizer_switch='index_condition_pushdown=on';
SET optimizer_switch='index_condition_pushdown=off';

-- 场景：分页查询，LIMIT offset很大时性能差

-- ❌ 性能差：offset很大
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 需要扫描1000020行，然后丢弃前1000000行

-- ✅ 优化1：使用覆盖索引 + 延迟关联
SELECT * FROM orders o
INNER JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;
-- 子查询使用覆盖索引，只返回id，减少回表

-- ✅ 优化2：使用WHERE id > ?（推荐）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 前提：记录上一页的最大id

-- ✅ 优化3：使用游标（适合导出数据）
-- 第一次查询
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 1000;
-- 记录最后一条的id，下次查询
SELECT * FROM orders WHERE id > last_id ORDER BY id LIMIT 1000;

-- 性能对比：
-- LIMIT 1000000, 20：扫描1000020行
-- WHERE id > 1000000 LIMIT 20：扫描20行

-- 场景：ORDER BY优化

-- ❌ 不使用索引：filesort
SELECT * FROM users ORDER BY age;
-- Extra: Using filesort（性能差）

-- ✅ 使用索引
CREATE INDEX idx_age ON users(age);
SELECT * FROM users ORDER BY age;
-- Extra: Using index（性能好）

-- 联合索引的排序
CREATE INDEX idx_city_age ON users(city, age);

-- ✅ 使用索引
SELECT * FROM users WHERE city = '北京' ORDER BY age;

-- ❌ 不使用索引：排序列不连续
SELECT * FROM users WHERE city = '北京' ORDER BY name;

-- ❌ 不使用索引：排序方向不一致
SELECT * FROM users ORDER BY city ASC, age DESC;

-- ✅ MySQL 8.0支持降序索引
CREATE INDEX idx_city_age_desc ON users(city ASC, age DESC);
SELECT * FROM users ORDER BY city ASC, age DESC;

-- 避免filesort的方法：
-- 1. 为ORDER BY的列建索引
-- 2. 联合索引的顺序要匹配ORDER BY
-- 3. WHERE和ORDER BY使用同一个索引

-- 场景：GROUP BY优化

-- ❌ 不使用索引：Using temporary
SELECT city, COUNT(*) FROM users GROUP BY city;
-- Extra: Using temporary; Using filesort

-- ✅ 使用索引
CREATE INDEX idx_city ON users(city);
SELECT city, COUNT(*) FROM users GROUP BY city;
-- Extra: Using index

-- 联合索引的分组
CREATE INDEX idx_city_age ON users(city, age);

-- ✅ 使用索引
SELECT city, age, COUNT(*) FROM users GROUP BY city, age;

-- ❌ 不使用索引：分组列不连续
SELECT city, age, COUNT(*) FROM users GROUP BY age, city;

-- 优化技巧：
-- 1. 为GROUP BY的列建索引
-- 2. GROUP BY的顺序要匹配索引顺序
-- 3. 使用覆盖索引

-- 场景：表连接优化

-- ❌ 没有索引
SELECT * FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.city = '北京';
-- 全表扫描，性能差

-- ✅ 建立索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_city ON users(city);

SELECT * FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.city = '北京';

-- JOIN优化原则：
-- 1. 为JOIN的列建索引
-- 2. 小表驱动大表
-- 3. 使用STRAIGHT_JOIN强制连接顺序

-- 小表驱动大表
-- ✅ 正确：小表在前
SELECT * FROM small_table s
INNER JOIN large_table l ON s.id = l.small_id;

-- ❌ 错误：大表在前
SELECT * FROM large_table l
INNER JOIN small_table s ON l.small_id = s.id;

-- 强制连接顺序
SELECT * FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.small_id;

-- 场景：子查询优化

-- IN vs EXISTS
-- 规则：小表驱动大表

-- 场景1：外表小，内表大 → 使用IN
SELECT * FROM small_table
WHERE id IN (SELECT small_id FROM large_table);

-- 场景2：外表大，内表小 → 使用EXISTS
SELECT * FROM large_table l
WHERE EXISTS (SELECT 1 FROM small_table s WHERE s.id = l.small_id);

-- 原理：
-- IN：先执行子查询，再遍历外表
-- EXISTS：遍历外表，每行执行子查询

-- 优化：改用JOIN
SELECT s.* FROM small_table s
INNER JOIN large_table l ON s.id = l.small_id;

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

-- 查看索引统计信息
SELECT
  TABLE_NAME,
  INDEX_NAME,
  SEQ_IN_INDEX,
  COLUMN_NAME,
  CARDINALITY,
  INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';

-- 查看未使用的索引（MySQL 5.7+）
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 object_schema = 'mydb'
ORDER BY object_schema, object_name;

-- 查看索引大小
SELECT
  TABLE_NAME,
  INDEX_NAME,
  ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE DATABASE_NAME = 'mydb' AND STAT_NAME = 'size';

-- 查看表碎片
SELECT
  TABLE_NAME,
  ENGINE,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
  ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
  ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb'
  AND DATA_FREE > 0
ORDER BY frag_pct DESC;

-- 整理碎片
-- 方法1：OPTIMIZE TABLE（会锁表）
OPTIMIZE TABLE users;

-- 方法2：ALTER TABLE（重建表）
ALTER TABLE users ENGINE=InnoDB;

-- 方法3：pt-online-schema-change（不锁表，推荐）
pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=users --execute

-- 定期整理碎片（建议每月一次）

-- 索引统计信息影响优化器的选择

-- 查看统计信息
SHOW INDEX FROM users;
-- 关注Cardinality列（索引基数）

-- 更新统计信息
ANALYZE TABLE users;

-- 查看统计信息更新时间
SELECT
  TABLE_NAME,
  UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

-- 配置自动更新统计信息
[mysqld]
innodb_stats_auto_recalc = 1  -- 自动更新
innodb_stats_persistent = 1   -- 持久化统计信息

-- 1. 选择性高的列建索引
-- 选择性 = COUNT(DISTINCT column) / COUNT(*)
-- 选择性越高，索引效果越好

SELECT
  COUNT(DISTINCT city) / COUNT(*) AS city_selectivity,
  COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM users;
-- city_selectivity: 0.05（建议建索引）
-- gender_selectivity: 0.5（不建议建索引）

-- 2. 频繁查询的列建索引
-- WHERE、ORDER BY、GROUP BY、JOIN的列

-- 3. 小表不需要索引
-- 表数据少于1000行，全表扫描更快

-- 4. 更新频繁的列不建索引
-- 每次UPDATE都要更新索引

-- 5. 不在索引列上使用函数
-- 会导致索引失效

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

-- 7. 联合索引优于多个单列索引
-- ✅ 推荐
CREATE INDEX idx_city_age ON users(city, age);

-- ❌ 不推荐
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_age ON users(age);

-- 8. 不要创建重复索引
-- ❌ 重复
CREATE INDEX idx_a ON users(a);
CREATE INDEX idx_ab ON users(a, b);  -- 包含了idx_a

-- 9. 删除未使用的索引
-- 定期检查并删除

-- 10. 控制索引数量
-- 每个表不超过5个索引（建议）

-- 场景：字符串列很长，索引占用空间大

-- ❌ 索引整个列
CREATE INDEX idx_email ON users(email);  -- email最长100字符

-- ✅ 前缀索引
CREATE INDEX idx_email ON users(email(20));  -- 只索引前20个字符

-- 如何选择前缀长度？
-- 目标：前缀的选择性接近完整列的选择性

-- 1. 查看完整列的选择性
SELECT COUNT(DISTINCT email) / COUNT(*) AS full_selectivity FROM users;
-- 结果：0.95

-- 2. 测试不同前缀长度的选择性
SELECT
  COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix_5,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
  COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS prefix_15,
  COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS prefix_20
FROM users;
-- 结果：
-- prefix_5: 0.60
-- prefix_10: 0.85
-- prefix_15: 0.92
-- prefix_20: 0.95

-- 3. 选择接近完整列选择性的最短前缀
CREATE INDEX idx_email ON users(email(15));

-- 前缀索引的缺点：
-- ❌ 不能用于ORDER BY
-- ❌ 不能用于GROUP BY
-- ❌ 不能用于覆盖索引

-- 索引合并：MySQL使用多个索引，然后合并结果

CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);

-- 查询
SELECT * FROM users WHERE age = 25 AND city = '北京';

-- MySQL可能使用：
-- 1. idx_age
-- 2. idx_city
-- 3. 索引合并（同时使用idx_age和idx_city）

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age = 25 AND city = '北京';
-- type: index_merge
-- Extra: Using intersect(idx_age,idx_city)

-- 索引合并的类型：
-- 1. intersect：AND条件，取交集
-- 2. union：OR条件，取并集
-- 3. sort_union：复杂OR条件

-- 优化建议：
-- 索引合并性能不如联合索引
-- ✅ 推荐：创建联合索引
CREATE INDEX idx_age_city ON users(age, city);

-- 慢查询
SELECT * FROM orders
WHERE user_id = 100
  AND status = 1
  AND create_time >= '2024-01-01'
ORDER BY create_time DESC
LIMIT 10;

-- 执行时间：2.5秒

-- 分析执行计划
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
  AND status = 1
  AND create_time >= '2024-01-01'
ORDER BY create_time DESC
LIMIT 10;

-- 结果：
-- type: ALL（全表扫描）
-- rows: 1000000
-- Extra: Using where; Using filesort

-- 优化方案：
-- 1. 创建联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

-- 2. 再次执行
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
  AND status = 1
  AND create_time >= '2024-01-01'
ORDER BY create_time DESC
LIMIT 10;

-- 结果：
-- type: range
-- key: idx_user_status_time
-- rows: 50
-- Extra: Using index condition

-- 执行时间：0.01秒
-- 性能提升：250倍

-- 慢查询：深度分页
SELECT * FROM orders ORDER BY id LIMIT 500000, 20;
-- 执行时间：5秒

-- 优化方案1：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 500000, 20
) t ON o.id = t.id;
-- 执行时间：1秒

-- 优化方案2：记录上次位置
SELECT * FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 执行时间：0.01秒

-- 优化方案3：使用覆盖索引
-- 如果只需要部分列
SELECT id, user_id, total FROM orders ORDER BY id LIMIT 500000, 20;
-- 如果(id, user_id, total)有索引，不需要回表