一、什么是索引？
索引类似于书籍的目录，帮助MySQL快速定位数据，减少全表扫描。
索引类型：
- 主键索引： 数据行的物理存储顺序。
- 唯一索引： 允许NULL值，但不允许重复。
- 普通索引： 基本的索引类型。
- 组合索引： 多列组合形成的索引。
- 全文索引： 用于文本搜索。
二、B+树结构简介
MySQL采用B+树作为其索引结构：
- 非叶子节点仅存储键值和指针。
- 叶子节点包含所有数据，并形成双向链表以支持范围查询。
三、10个实用的索引优化技巧
技巧1：最左前缀原则
-- 组合索引 (x, y, z)
-- 有效查询
WHERE x = 1
WHERE x = 1 AND y = 2
WHERE x = 1 AND y = 2 AND z = 3
-- 无效查询
WHERE y = 2 -- 不满足最左前缀
WHERE z = 3
技巧2：避免在索引列上进行计算
-- 索引失效
WHERE YEAR(order_date) = 2024
-- 优化后
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
技巧3：避免对索引列使用函数
-- 索引失效
WHERE UPPER(username) = 'ZHANGSAN'
-- 优化后
WHERE username = 'zhangsan' OR username = 'ZHANGSAN'
技巧4：防止隐式类型转换
-- phone字段为VARCHAR类型，以下查询会导致索引失效
WHERE phone = 13800138000
-- 优化后
WHERE phone = '13800138000'
技巧5：避免使用SELECT *
-- 只选择需要的列，利用覆盖索引
SELECT user_id, username FROM users WHERE username = 'zhangsan'
技巧6：LIKE语句优化
-- 索引失效
WHERE name LIKE '%zhang%'
-- 使用索引
WHERE name LIKE 'zhang%'
技巧7：用UNION替换OR
-- 可能导致索引失效
WHERE a = 1 OR b = 2
-- 优化为UNION
SELECT * FROM table_a WHERE a = 1
UNION
SELECT * FROM table_b WHERE b = 2
技巧8：利用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan'
-- 关注点：type(ALL/const/ref/range/index), key(使用的索引), rows(扫描行数)
技巧9：小表驱动大表
-- 小表驱动大表策略
SELECT * FROM small_table s
INNER JOIN large_table l ON s.id = l.small_id
技巧10：定期维护和优化表
ANALYZE TABLE users;
OPTIMIZE TABLE users;
四、索引失效的常见情况
- 索引列参与了计算。
- 索引列使用了函数。
- LIKE模式以%开头。
- OR条件两边的列都有索引但类型不同。
- 隐式类型转换。
- != 或 <> 比较。
- IS NULL / IS NOT NULL（取决于数据分布）。
