背景：负责的项目中慢查询数量报警，通过数据库分析诊断平台可以看出有一条sql命令慢查询数量超过阈值，是一个分页查询，需要查询某个范围内的数据，使用limit，深度分页导致慢查询。
select * from flow where group_id=3290 limit 150000,100;
分析：这条查询语句一直都没有问题，最近突然有问题，发现最近几次使用的数据集合都是很大的，导致每次分页limit偏移量较大，平均查询时间>2s。explain分析执行计划,使用了非唯一索引。即使有索引，索引只能加速定位，但无法直接“跳转到第 N 条”（因为索引存储的是键值，而不是行号）。
解决：采用游标分页，将查询上一页的最大id返回，下一页的查询基于此id
select * from flow where group_id=3290 AND id>150000 limit 100;
深度分页的本质：
第一条查询实际加载了 5000600 行数据，再丢弃前 5000000 行。LIMIT offset, size的工作原理：
数据库会先读取offset + size条数据，再丢弃前offset条。
一、索引是什么，作用是什么
MySQL 索引是一种数据结构，对表中一列或多列的值进行排序和存储。类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。
核心原则：索引是 以空间换时间 的优化手段，需根据查询需求和数据特点权衡利弊。
索引虽然能够提高查询性能，但也需要注意以下几点：
- 索引需要占用额外的存储空间。
- 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
- 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。
作用：
| 作用 | 说明 | 示例 |
|---|---|---|
| 加速查询 | 减少全表扫描，快速定位数据 | SELECT * FROM users WHERE id = 100（主键索引） |
| 优化排序 | 避免 ORDER BY 时的文件排序（Using filesort） |
SELECT * FROM products ORDER BY price（price 有索引） |
| 提高连接效率 | 加速 JOIN 操作 |
SELECT * FROM orders JOIN users ON orders.user_id = users.id |
| 实现唯一约束 | 唯一索引保证列值的唯一性 | ALTER TABLE users ADD UNIQUE (email) |
| 覆盖索引 | 直接从索引获取数据，避免回表 | SELECT user_id FROM orders WHERE status = 'paid'（(status, user_id) 索引） |
二、索引分类
| 索引类型 | 特点 | 适用场景 |
|---|---|---|
| 主键索引（PRIMARY） | 唯一且非空，表只能有一个 | 主键列（如 id） |
| 唯一索引（UNIQUE） | 值必须唯一，允许 NULL | 邮箱、手机号等唯一字段 |
| 普通索引（INDEX） | 无唯一性约束 | 高频查询的非唯一字段 |
| 联合索引（复合索引） | 多列组合的索引 | 多条件查询（如 (city, age)） |
| 全文索引（FULLTEXT） | 对文本内容分词搜索 | 文章关键词搜索 |
| 哈希索引 | 精确匹配快，不支持范围查询（仅 Memory 引擎支持） | 等值查询（如 WHERE key = 'value'） |
三、索引失效情况
| 场景 | 示例 |
|---|---|
索引列使用函数或运算 |
|
隐式类型转换 |
|
|
|
联合索引未遵循最左前缀原则 |
SELECT * FROM table WHERE a = 1 AND c = 3; -- 跳过中间列 b |
OR 条件未全覆盖 |
|
索引列使用 |
SELECT * FROM orders WHERE status != 'paid'; |
全表扫描比索引更快时 |
当表中数据量很少（如 <1000 行）或索引区分度极低时，优化器可能放弃索引 |
使用 |
SELECT * FROM users WHERE age IS NULL; -- NULL 值较多时可能失效 |
数据分布不均匀 |
当某个值占比过高时（如 |
四、索引创建注意事项
4.1 适合创建索引的情况
| 场景 | 场景 |
|---|---|
主键列（PRIMARY KEY） |
唯一且非空，天然适合作为聚簇索引 |
外键列（FOREIGN KEY） |
加速表连接（JOIN）操作。 |
高频查询条件列 |
频繁出现在 |
排序或分组列 |
|
联合查询的列 |
多列组合查询时，建立联合索引（注意最左前缀原则）。 |
覆盖索引的列 |
查询列全部包含在索引中，避免回表。 |
4.2 不适合创建索引的列
| 场景 | 原因 | 示例 |
|---|---|---|
| 低区分度列 | 索引效果差（如性别、布尔值） | gender ENUM('M','F') |
| 频繁更新的列 | 索引维护成本高 | last_login_time TIMESTAMP |
| 大文本/BLOB 列 | 索引占用空间大 | content TEXT |
| 很少使用的列 | 增加存储和写入开销，无实际收益 | remark VARCHAR(255) |
五、如何验证使用索引
5.1 Explain命令
EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的sql语句，分析你的查询语句或是表结构的性能瓶颈。一条执行结果如下：
重要字段说明：
id:
查询序列号，id相同时，从上到下顺序执行；id不同时，id越大优先级越高越先被执行
type:
| 类型值 | 说明 | 示例 SQL 查询 | 性能分析 |
|---|---|---|---|
system |
表中只有一行数据（系统表或衍生表）。 | EXPLAIN SELECT * FROM (SELECT 1 AS id) AS t; |
最优，直接命中单行。 |
const |
通过主键或唯一索引查找单行（常量查询）。 | EXPLAIN SELECT * FROM users WHERE id = 1;（id 是主键） |
极高效，只需一次索引查找。 |
eq_ref |
唯一索引关联（JOIN 时使用主键或唯一索引匹配）。 | EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id; |
高性能，每行关联仅匹配一次。 |
ref |
非唯一索引查找（返回匹配某个索引值的所有行）。 | EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';（email 是普通索引） |
较高效，但可能返回多行。 |
fulltext |
使用全文索引检索。 | EXPLAIN SELECT * FROM articles WHERE MATCH(content) AGAINST('database'); |
依赖全文索引配置。 |
range |
索引范围扫描（如 BETWEEN、IN、>）。 |
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;（age 有索引） |
中等，需扫描索引范围。 |
index |
全索引扫描（遍历索引树，但无需回表查数据）。 | EXPLAIN SELECT id FROM users;（id 是主键） |
比 ALL 好，但仍有开销。 |
ALL |
全表扫描（未使用索引，性能最差）。 | EXPLAIN SELECT * FROM users WHERE name LIKE '%John%';（name 无索引） |
需优化，避免大数据表。 |
关键类型区别：
constvseq_refconst用于单表主键查询（如WHERE id=1）。eq_ref用于多表 JOIN 时主键关联（如orders.user_id = users.id）。
refvsrangeref是精确匹配索引（如WHERE email='x'）。range是范围匹配索引（如WHERE age > 20）。
indexvsALLindex只扫描索引树（如SELECT id FROM users）。ALL扫描全部数据行（如SELECT * FROM users）。
例如：
-- 示例1: type=const（主键查询）
EXPLAIN SELECT * FROM products WHERE product_id = 100;
-- 示例2: type=ref（普通索引查询）
EXPLAIN SELECT * FROM customers WHERE last_name = 'Smith';
-- 示例3: type=range（范围查询）
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
-- 示例4: type=ALL（全表扫描）
EXPLAIN SELECT * FROM logs WHERE message LIKE '%error%';possible_keys与key
possible_keys:查询可能使用的索引列表。数据库优化器根据查询条件（如 WHERE、JOIN）分析后，理论上适合用于加速查询的索引
key:查询实际使用的索引,优化器最终选择的索引（可能从 possible_keys 中选出，或完全不同的索引）。若为 NULL，表示未使用任何索引（全表扫描 type=ALL）。
场景1:possible_keys 有值，但 key 为 NULL
- 数据量小，优化器认为全表扫描比索引更快。
- 索引选择性差（如索引列值重复率高）。
ref:
索引查找时使用的列或常量，即数据库通过哪些值来匹配索引
| 值 | 说明 | 示例场景 |
|---|---|---|
const |
使用常量（如主键或唯一索引的固定值） | WHERE id = 1（id 是主键） |
列名 |
使用其他表的列进行关联（常见于 JOIN） | JOIN orders ON users.id = orders.user_id（user_id 是索引） |
func |
使用函数计算结果匹配索引（可能降低性能） | WHERE YEAR(create_time) = 2023（create_time 有索引） |
NULL |
未使用索引或全表扫描（type=ALL） |
WHERE name LIKE '%John%'（name 无索引） |
Extra
提供 查询执行的附加信息，尤其是性能相关的关键提示。
| 值 | 说明 | 优化建议 |
|---|---|---|
Using where |
存储引擎返回数据后，需在 Server 层进一步过滤（可能未用索引） | 检查 WHERE 条件是否命中索引 |
Using index |
使用覆盖索引（无需回表查数据） | 优先选择覆盖索引（查询列全在索引中） |
Using filesort |
需要额外排序（如无索引的 ORDER BY） |
为排序字段添加索引 |
Using temporary |
使用临时表（常见于 GROUP BY、DISTINCT） |
优化复杂聚合查询或增加内存配置 |
Using join buffer |
使用连接缓冲（JOIN 时未走索引） | 检查 JOIN 条件是否命中索引 |
Select tables optimized away |
查询被优化（如 MIN()/MAX() 直接读索引） |
无需优化，已是高效执行 |