MySQL 索引
索引为什么快、怎么建、什么时候失效。
Key Points索引是排好序的查找结构（B+ 树），让查询从全表扫描变成几步定位。
建索引
SQL
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX uk_email ON users(email);
-- 联合索引
CREATE INDEX idx_age_name ON users(age, username);Output
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0什么时候用不上索引
- 对列做函数/运算：
WHERE YEAR(created_at)=2026 - 前模糊：
LIKE '%abc' - 隐式类型转换：
WHERE phone = 13800000000（phone 是字符串） - 联合索引不遵守最左前缀：
idx(a,b)却只查b
最左前缀原则
A composite index (age, username) sorts by age first, then username. WHERE age=? and WHERE age=? AND username=? use it, but WHERE username=? alone cannot — matching must start from the leftmost column.
More indexes is not better: each slows writes and costs space. Index the columns in hot WHERE/JOIN/ORDER BY, and verify with
EXPLAIN.建索引
SQL
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX uk_email ON users(email);
-- 联合索引
CREATE INDEX idx_age_name ON users(age, username);Output
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0什么时候用不上索引
- 对列做函数/运算：
WHERE YEAR(created_at)=2026 - 前模糊：
LIKE '%abc' - 隐式类型转换：
WHERE phone = 13800000000（phone 是字符串） - 联合索引不遵守最左前缀：
idx(a,b)却只查b
最左前缀原则
联合索引 (age, username) 像电话簿先按 age 排、再按 username 排。所以 WHERE age=? 能用索引，WHERE age=? AND username=? 能用，但 WHERE username=? 用不上——必须从最左列开始连续匹配。
索引不是越多越好：每个索引都拖慢写入、多吃空间。优先给
WHERE / JOIN / ORDER BY 高频列建索引，用 EXPLAIN 验证是否真的命中。