mysql基础（十一）索引及SQL优化(上)

索引介绍：

1. 索引是什么

索引是一种用于提高数据查询效率的数据结构。

没有索引时，MySQL 查询数据往往需要：

从第一行开始 ↓ 逐行扫描 ↓ 找到符合条件的数据

这种方式通常称为：

全表扫描

当数据量很大时，全表扫描需要检查大量数据，查询效率会明显下降。

建立索引后，可以通过索引快速缩小查找范围：

查询条件 ↓ 索引 ↓ 快速定位数据范围 ↓ 读取目标记录

对于 MySQL 8 默认的 InnoDB 存储引擎，普通索引和主键索引主要采用：

B+Tree

结构。

表中存在大量数据如果没有索引的时候，要寻找一条数据得进行全表扫描直到找到为上。而有索引的情况下，利用 B+树 的数据结构，只需要查找部分数据即可定位目标。
此外，MySQL也支持其他类型的索引，如用于地理空间数据的 R-树索引、用于内存表的哈希索引，以及用于全文搜索的倒排索引等。
索引有两大优势：
其一检索快——索引相当于书本的目录，提高检索效率的同时降低数据库IO成本。
其二排序方便——用索引排序，降低数据排序成本和CPU消耗。
也有两大劣势：
其一占空间——索引本身也是一张逻辑上的表结构（物理上以B+树存储），需要占用额外的磁盘空间。特别是 InnoDB 的二级索引，其叶子节点存储的是索引列的值 + 主键值，在查询时需要回表获取完整记录，这既是索引的空间成本，也可能带来一定的查询开销。
其二更新慢——当执行 DML 操作时，InnoDB 不仅需要维护聚簇索引（即数据本身），还需要同步更新该表上的所有二级索引。每多一个索引，就多一棵 B+树需要维护，这会显著降低 INSERT、UPDATE、DELETE 的执行速度。

索引大致分三大类：单值——一个索引只包含单个列，唯一——索引列的值必须唯一（主键约束和唯一约束就属于此类）并允许有空值，复合索引——一个索引包含多个列。

需要注意：

索引并不是另一张独立的数据表，而是数据库为了提高数据访问效率而维护的一种数据结构。

2. B+Tree

InnoDB 的 B+Tree 索引具有以下特点：

非叶子节点 → 主要保存索引键和指向下一层节点的指针 叶子节点 → 保存真正需要的数据或主键信息 叶子节点之间 → 按顺序形成链表

B+Tree 相比普通二叉树更适合数据库索引，主要原因是：

树的高度较低 ↓ 一次节点可以保存更多索引项 ↓ 查询时需要访问的页面更少 ↓ 减少磁盘I/O

同时，叶子节点按照索引顺序排列，因此非常适合：

范围查询 排序 最左前缀查询

3. 聚簇索引与二级索引

InnoDB 的索引可以重点理解为：

聚簇索引 二级索引

聚簇索引

InnoDB 通常使用主键作为聚簇索引。

聚簇索引 B+Tree 的叶子节点保存：

完整的一行数据

可以简单理解：

主键 ↓ B+Tree ↓ 叶子节点 ↓ 完整行记录

因此根据主键查询：

SELECT*FROMuserWHEREid=100;

通常可以直接通过聚簇索引找到完整数据。

二级索引

普通索引、唯一索引、联合索引等非聚簇索引，通常都属于二级索引。

二级索引的叶子节点一般保存：

索引列值 + 主键值

例如：

KEYidx_name(name)

对应的二级索引可以理解为：

name + id

如果查询：

SELECT*FROMuserWHEREname='张三';

MySQL 可能先通过：

idx_name

找到：

主键id

然后再根据主键访问聚簇索引，读取完整记录。

这个过程称为：

回表

4. 回表与覆盖索引

回表

例如存在：

KEYidx_name(name)

查询：

SELECT*FROMuserWHEREname='张三';

执行过程可能是：

二级索引 idx_name ↓ 找到 name='张三' ↓ 得到对应主键id ↓ 根据id查询聚簇索引 ↓ 读取完整行数据

其中：

二级索引 → 聚簇索引

这个过程就是：

回表

覆盖索引

如果查询需要的字段全部都可以从索引中取得，就不需要再次访问聚簇索引。

例如：

SELECTname,idFROMuserWHEREname='张三';

如果存在：

KEYidx_name(name)

由于 InnoDB 二级索引的叶子节点中通常已经包含：

name + 主键id

因此查询结果可以直接从二级索引得到：

二级索引 ↓ 直接返回数据 ↓ 无需回表

这种情况称为：

覆盖索引

在EXPLAIN中经常可以看到：

Using index

表示查询可以利用覆盖索引取得所需数据。

Using index不代表“只有一个索引”或者“必须是等值查询”，它主要表示查询所需要的列能够直接从索引中取得。

5. 索引的创建、查看与删除

下面继续使用原文中的实际操作截图和案例。

常见创建方式：

CREATEINDEXidx_nameONuser(name);

创建唯一索引：

CREATEUNIQUEINDEXuk_nameONuser(name);

创建联合索引：

CREATEINDEXidx_name_ageONuser(name,age);

查看索引：

SHOWINDEXFROMuser;

删除索引：

DROPINDEXidx_nameONuser;

也可以通过：

ALTERTABLE

添加索引。

索引案例：

索引的设计原则：

索引并不是越多越好。

索引能够提高查询效率，但同时也会带来：

占用磁盘空间 ↓ INSERT需要维护索引 UPDATE可能需要维护索引 DELETE需要维护索引

因此应该根据实际查询场景合理设计。主要原则：
1.查多量大——查询频率高且数据量较大。
2.最佳条件——索引字段就当从where子句条件中选择，如果有多组条件，选择最常用且过滤效果最好的。
3.唯一索引——使用唯一索引，区分度越高效率也越高。
4.控制数量——索引不是越多越好，索引越多维护代价也越高，会带来降低DML效率且占据空间的负面效果。
5.短索引——索引也占空间，所以索引字段长度尽量简短。
6.最左前缀——由多个字段组成的联合索引本质是一棵复合 B+Tree，使用中依情况使用最前面几个(最左边)，不能跳过字段。
7.很少修改——做为索引的字段应当很少进行DML操作。

SQL优化流程：

1. 定位需要优化的SQL

常用方式包括：

SHOW STATUS SHOW PROCESSLIST 慢查询日志 Performance Schema

1. SHOW STATUS

可以查看 MySQL 各类 SQL 的执行情况，例如：

2. SHOW PROCESSLIST

可以查看当前正在执行的连接和SQL，定位低效SQL语句：

也可以：

SHOWFULLPROCESSLIST;

重点关注：

运行时间很长 状态异常 长期未结束

的 SQL。

3. 慢查询日志

慢查询日志用于记录：

执行时间超过阈值的SQL

是生产环境定位慢 SQL 的重要方式之一。

4. Performance Schema

Performance Schema 可以从整个 MySQL 运行情况中统计：

哪些SQL执行次数最多 哪些SQL累计耗时最高 哪些SQL平均耗时较高 哪些SQL扫描行数异常

后面会单独介绍。

2. EXPLAIN分析执行计划

找到需要优化的 SQL 后，可以使用：

EXPLAINSELECT...

查看 MySQL 优化器为 SQL 生成的执行计划。

EXPLAIN主要帮助我们判断：

表的访问顺序 ↓ 访问方式 ↓ 可能使用哪些索引 ↓ 最终使用哪个索引 ↓ 预计检查多少行 ↓ 是否需要额外过滤、排序、临时表

分析时通常重点关注：

type possible_keys key key_len rows filtered Extra

常用字段：

需要注意：

普通EXPLAIN展示的是优化器生成的估算执行计划，并不代表 SQL 真正执行后的实际耗时。

分析执行计划演示：

接下来通过对执行情况表的字段做演示说明：

id字段：


select_type(查询类型)字段：

3. EXPLAIN ANALYZE：估算 vs 实际

普通：

EXPLAIN

主要回答：

MySQL准备怎么执行？

从 MySQL 8.0.18 开始，还可以使用：

EXPLAINANALYZESELECT...

它会真正执行 SQL，然后同时显示：

优化器估算 + 真实执行情况

例如：

EXPLAINANALYZESELECT*FROMuserWHEREname='张三';

结果中重点关注：

cost rows actual time actual rows loops

简单理解：

例如：

估算 rows = 10 实际 rows = 100000

说明：

优化器估算 和 真实数据分布

可能存在较大偏差。

这时可以进一步排查：

统计信息是否准确 索引基数是否合理 数据分布是否严重倾斜 执行计划是否选择合理

必要时可以：

ANALYZETABLE表名;

更新统计信息。

可以简单记忆：

EXPLAIN → MySQL准备怎么执行？ EXPLAIN ANALYZE → MySQL真正执行后怎么样？

注意：EXPLAIN ANALYZE会真正执行 SQL，因此对于高成本 SQL，尤其在生产环境中，使用前需要先评估影响。

4. Performance Schema性能分析

Performance Schema 是 MySQL 内置的性能监控体系。

它可以持续收集：

SQL执行 线程 等待事件 锁 I/O 事务 Stage

等运行信息。

查看是否启用：

SHOWVARIABLESLIKE'performance_schema';

MySQL 8 中一般为：

ON

查看耗时较高的SQL

Performance Schema 会将结构相同、参数不同的 SQL 进行归一化统计。

例如：

SELECT*FROMuserWHEREid=1;SELECT*FROMuserWHEREid=2;SELECT*FROMuserWHEREid=100;

会归一化为类似：

SELECT*FROMuserWHEREid=?

这种归一化后的 SQL 称为：

Statement Digest

可以查询：

SELECTSCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,ROUND(SUM_TIMER_WAIT/1000000000000,3)AStotal_seconds,ROUND(AVG_TIMER_WAIT/1000000000000,6)ASavg_seconds,SUM_ROWS_EXAMINED,SUM_ROWS_SENTFROMperformance_schema.events_statements_summary_by_digestWHERESCHEMA_NAMEISNOTNULLORDERBYSUM_TIMER_WAITDESCLIMIT10;

重点字段：

适合找：

执行次数特别高的SQL 累计耗时特别高的SQL 平均耗时较高的SQL 扫描行数明显过多的SQL

sys Schema

MySQL 还提供：

sys

Schema。

它主要是在 Performance Schema 的基础上提供更加容易阅读的视图。

例如：

SELECTquery,db,exec_count,total_latency,avg_latency,rows_examined,rows_sentFROMsys.statement_analysisORDERBYtotal_latencyDESCLIMIT10;

可以直接看到：

SQL 执行次数 累计耗时 平均耗时 扫描行数 返回行数

可以简单理解：

Performance Schema → 底层、全面 sys Schema → 对Performance Schema进一步封装，使用更加方便

Performance Schema 与 EXPLAIN 的关系：

Performance Schema ↓ 从整个系统中找到问题SQL ↓ EXPLAIN ↓ 分析这条SQL的执行计划

5. Profiling性能分析（旧版，已弃用）

注意：SHOW PROFILE和SHOW PROFILES已经被 MySQL 官方标记为弃用，未来版本可能移除。现代 MySQL 更推荐使用 Performance Schema 进行性能分析。

本节保留 Profiling 的实际操作案例，主要用于理解旧版本 MySQL 对一条 SQL 各个执行阶段耗时的分析方法。

6. optimizer_trace分析优化器选择

EXPLAIN可以告诉我们：

优化器最终选择了什么执行计划

但有时候还需要继续回答：

明明有索引，为什么没有使用？ 存在多个索引，为什么选择了这个？ 优化器比较过哪些执行路径？ 为什么放弃另外一个索引？

这时可以使用：

optimizer_trace

查看优化器生成执行计划时的决策过程。

简单理解：

EXPLAIN → 最终选了什么 optimizer_trace → 为什么这么选

开启optimizer_trace

在当前 Session 中执行：

SEToptimizer_trace='enabled=on';

然后执行需要分析的 SQL，再查看：

SELECT*FROMinformation_schema.OPTIMIZER_TRACE;

分析结束后关闭：

SEToptimizer_trace='enabled=off';

OPTIMIZER_TRACE主要内容

结果中的：

TRACE

是一段 JSON 数据。

典型结构可以大致理解为：

join_preparation ↓ 查询准备、SQL改写 join_optimization ↓ 条件处理、索引分析、成本计算、执行计划选择 join_execution ↓ 执行阶段相关信息

其中：

join_optimization

通常最值得关注。

索引的使用：