本文为个人总结，如有错误请评论区指出。

索引

什么是索引

索引是 MySQL 对表中一个 / 多个字段构建的有序数据结构；

核心作用：加速 SELECT 查询，同时辅助唯一索引 / 主键索引保证字段唯一性；

核心特点：空间换时间 —— 索引会占用额外的磁盘存储空间，以此换取极致的查询速度；

两面性：只提速查询（SELECT），会减慢增删改（INSERT/UPDATE/DELETE） 速度（因为增删改时需要同步维护索引结构）。

B+树

B + 树是 MySQL 索引的核心底层数据结构 ，是有序多路平衡搜索树

B+树的结构分为 非叶子节点 和 叶子节点 两层

B+树的核心规则：

• 非叶子节点只存索引值和指针，不存真实数据
• 叶子节点只存真实有效数据，按照顺序串联成双向链表
• 非叶子节点的索引值，都能在叶子节点中找到对应的完整数据

例如：

InnoDB用B+树为索引结构的原因：

1. 相比二叉树，B+树的层级更少，搜索效率更高
2. 相比B树，B+树只在叶子节点存储数据，而B树无论是叶子节点还是非叶子节点，都会保存数据，这样就使一页中存储的键值减少，指针跟着减少，要保存和B+树一样多的数据就不得不增加树的高度，导致性能下降。

B 树（也叫 B - 树）是平衡多路搜索树，核心特点是：

• 非叶子节点和叶子节点都存真实数据；
• 是 “多路 + 平衡” 结构，层级少，减少磁盘 IO；
• 不支持叶子节点串联，范围查询效率低。

B树是 B + 树的前身，缺点是节点存数据导致体积大、范围查询差，所以 MySQL 不用 B 树，而用优化后的 B + 树。

索引的分类

按物理存储结构分类

聚集索引

本质：将索引和数据物理存储在一起，索引的叶子节点就是数据表的真实数据行。
核心特点：

1. InnoDB中主键默认是聚集索引。若表无主键，会选非空唯一列（即会选唯一索引作为聚集索引）；若无此类列，MySQL会隐式生成6字节自增的rowid为聚集索引
2. 一张表只能有一个聚集索引，因为数据的物理存储顺序只能有一种
3. 查询效率极高：通过聚集索引查询时，找到索引节点就能直接拿到完整数据，无需回表查询（什么是回表查询下面会讲）

二级索引（也叫辅助索引）

本质： 索引和数据物理分离，叶子节点仅存储索引字段值+聚集索引的主键值 ， 需要通过回表查询获取完整的行数据
核心特点：

1. 范围定义：除了聚集索引索引之外，所有手动创建的所有（普通索引、唯一索引、联合索引、全文索引等）都属于二级索引
2. 一张表可以有多个二级索引
3. 查询逻辑：
   • 第一步：在二级索引树中找到目标数据的主键值
   • 第二步：用主键值到聚集索引树中查询完整的行数据
     这个过程就叫回表查询
4. 优化方案：若查询的字段全部包含在二级索引中（即覆盖索引），可以不进行回表查询，直接从二级索引返回数据。

按逻辑功能分类

主键索引（primary）

定义：基于主键字段创建的索引，用于唯一标识数据表的每一行记录

核心特点：

1. 字段必须满足 NOT NULL + UNIQUE；
2. InnoDB 默认作为聚簇索引；
3. 一张表仅能有 1 个

优点：

1. 查询无需回表，效率最高；
2. 数据物理有序，范围查询速度极快；
3. 数据唯一

缺点：

1. 插入 / 更新时需维护数据物理排序，写入性能略差；
2. 易产生数据碎片，需定期优化

唯一索引(unique)

定义： 基于唯一字段创建的索引，保证索引字段的值不重复

核心特点：

1. 字段值唯一，允许存储多个 NULL；
2. 一张表可创建多个；
3. 可用于防止业务字段重复

优点：

1. 兼具唯一性约束 + 查询加速双重作用；
2. 比主键索引灵活，支持多字段创建

缺点：
写入时需校验唯一性，性能略低于普通索引

普通索引(index)

定义： 基于普通字段创建的索引，无任何业务约束，纯提升查询效率

核心特点：

1. 无约束，字段值可重复、可 NULL；
2. 一张表可创建多个；
3. 开发中使用频率最高

优点：

1. 创建 / 删除开销最小，对写入性能影响最低；
2. 针对性优化高频查询字段

缺点：
无业务约束能力，无法防止数据重复

联合索引(Composite Index)

定义： 基于多个字段组合创建的索引，也称复合索引

核心特点：

1. 遵循最左前缀匹配原则（后面会讲什么是最左前缀原则）；
2. 可设为联合唯一索引 / 联合普通索引；
3. 一个索引覆盖多字段查询场景

优点：

1. 替代多个单列索引，减少索引数量和磁盘占用；
2. 优化多字段组合查询效率

缺点：
字段顺序直接影响索引有效性，顺序错误会导致索引失效

前缀索引(Prefix Index)

定义： 对字符串字段的前 N 个字符创建的索引，是普通索引的特殊形式

核心特点：

1. 仅适用于CHAR/VARCHAR/TEXT 类型；
2. 需指定前缀长度 N；
3. 本质是普通索引的优化版

优点：

1. 大幅减小索引文件体积，提升索引查询 / 写入效率；
2. 解决长字符串字段索引效率低的问题

缺点：

1. 前缀长度过短会降低索引区分度，导致查询效率下降；
2. 无法用于排序和分组（前缀重复时）

全文索引(fulltext)

定义： 专为长文本字段设计的索引，支持分词检索

核心特点：

1. 仅支持CHAR/VARCHAR/TEXT 类型；
2. 底层是倒排索引，非 B + 树；
3. 替代 like '%关键词%' 全模糊查询

优点：

1. 支持分词检索、权重排序，全模糊查询效率远超 like；
2. 适用于文章、评论等长文本场景

缺点：

1. 不支持短词检索（默认最小 4 字符）；
2. 维护成本高，写入性能影响较大

什么是倒排索引

**定义：**倒排索引也叫反向索引，它不按记录行存储索引，而是先对文本内容做分词处理，再建立关键词 → 包含该关键词的记录 ID 列表的映射关系。

结构：
倒排索引主要由 词典 和 倒排列表 组成：

• 词典：存储文本中所有去重后的关键词；
• 倒排列表：存储每个关键词对应的记录 ID 集合

工作原理：
假如有三条文章记录：

记录id	文章内容
1	无籽西瓜
2	MySQL
3	有籽西瓜

1. 构建倒排索引

• 分词处理：对每篇文章内容拆分关键词，得到无籽、西瓜、MySQL、有籽
• 建立映射：

关键词	倒排列表（记录ID）
西瓜	1,3
无籽	1
有籽	3
MySQL	2

2.执行查询过程：
例如：在名为水果的字段中，检索包含西瓜这个关键词的所有记录

• 第一步：去词典中找到关键词西瓜
• 第二步：获取对应的倒排列表1,3
• 第三步：直接返回记录ID 1 和 3 的完整数据，无需全表扫描

match("水果") against ("西瓜");

• match(字段名)：指定要检索的字段，必须是已经创建了全文索引的字段；
• against(‘搜索词’)：指定要搜索的关键词 / 语句；
  整体作用：在指定的全文索引字段中，检索包含目标关键词的记录，返回匹配结果。

索引的语法

• 创建索引

create [unique|...索引类型] index 索引名称 on table 表名 (字段名，...)

• 查看索引

show index from 表名

• 删除索引

drop index 索引名 on 表名

性能分析

查看执行频次

通过如下指令，可以查看当前数据库的insert、update、delete、select的访问频次

show global status like"Com_%"

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位:秒，默认10秒）的sql语句的日志
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置下面的信息：

#开启MySQL慢查询日志开关
slow_query_log = 1 #默认是0关闭，1为开启
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢日志
long_query_time = 2

show profiles

show profiles 可以在做sql优化时了解到时间在哪耗费了。

开启profile操作

通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling;

profiling默认是关闭的,可以通过set语句在session/global级别开启：

• 先通过如下语句查看profiling操作是否开启：

select @@profiling

如图为0表示未开启

• 再通过如下语句开启

set profiling = 1;

设置后再查询可以看到已经为1开启

使用profile

• 查看每一条sql耗时的基本情况

show profiles;

• 查看指定query_id的sql语句各个阶段的耗时情况

show profile for query query_id;

• 查看指定query_id的sql语句CPU的使用情况

show profile cpu for query query_id;

explain查看执行计划

explain或者desc命令可以获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接到顺序。

#直接在语句前面加上explain/desc
explain select 字段列表 from 表名 where 条件;

explain执行计划各字段的含义

1. id(查询执行的顺序)

是sql执行的编号，MySQL会依照id的规则决定执行顺序
执行规则：

1. id不同时，id的数值大的先执行
2. id相同时，执行顺序是从上而下

2. select_type(查询的类型)

表示这一行的查询时什么类型的查询语句，用来区分：普通查询、子查询、联表查询、聚合查询、union查询等

常见取值：

1. simple：最简单的查询，不包含子查询、union、联表
2. primary：查询中最外层的主查询，只要sql包含子查询/union，外层查询就会标记为primary
3. subquery：select/where中独立的子查询，执行一次即可
4. derived：表示派生表查询，即from后面的子查询（会生成临时表）

3. table （当前行查询的表名）

表示当前这一行的执行计划，正在访问那一张表。

取值说明：

1. 直接显示表名：比如 user、order_info，表示查询这张表；
2. 显示derived+数字：比如 derived2，表示这是 id=2 的查询生成的「派生临时表」；
3. 显示 union+数字1,数字2：比如 union1,3，表示这是 id=1 和 id=3 的查询通过 UNION 生成的临时表；
4. 显示 <subquery+数字>：表示这是 id = 数字的子查询生成的临时表。

4. partitions（匹配的分区）

表示当前查询命中了表的哪个分区，仅对分区表生效。

取值说明：

1. 显示具体的分区名：表示查询命中了这个分区；
2. 显示 NULL：如果表不是分区表，这个字段永远是 NULL → 99% 的业务场景都是 NULL，非核心字段。

5. type （访问类型）

表示 MySQL 在表中 找到所需行的扫描方」这个字段直接决定了这条 SQL 的查询性能好坏，type的取值是判断 SQL 是否高效的核心标准！

取值含义：

1. system：极致最优，表里只有 1 条数据，且是系统表，业务中几乎遇不到；
2. const：通过 主键 / 唯一索引 做等值查询，只匹配 1 条结果，比如 where id=100（id 是主键），查询速度极快，一次命中
3. eq_ref：多表联查时，被联表通过 主键 / 唯一索引 做等值匹配，联表查询的最优级别，比如 a left join b on b.id=a.b_id（b.id 是主键），每次联表只匹配 1 条
4. ref：通过 普通非唯一索引 做等值查询，可能匹配多条结果，比如 where name=‘张三’（name 是普通索引），这是单表查询最常见的优秀级别，绝大多数业务的最优目标就是这个
5. range：索引的范围查询，只扫描索引的某一段，比如 where id between 100 and 200、where id>100、where id in (1,2,3)、like ‘张%’（前缀匹配），索引生效，性能很好。
6. index：全索引扫描，会扫描整个索引树，但不会扫描物理表数据，比 ALL 好，但依然是全量扫描，比如查询的字段都是索引字段（覆盖索引），但无查询条件；
7. ALL：全表扫描，MySQL 会遍历整张表的所有数据行，找符合条件的记录，表数据量越大，查询越慢，这是慢查询的头号元凶，看到type=ALL，第一件事就是加索引优化。

优化准则：
至少要达到 range 级别，最好能达到 ref/eq_ref/const 级别，杜绝 index/ALL 级别

6. possible_keys（可能会用到的索引）

MySQL 的查询优化器，预判当前查询有可能匹配上、可以使用的索引列表。

核心特点：

1. 这个字段是候选名单，只是 MySQL 觉得这些索引有机会生效，不代表一定会用；
2. 取值为NULL：表示当前查询「没有任何可用的索引」，大概率会走全表扫描（type=ALL）；
3. 这个字段的数量不影响性能，只是罗列候选，没有参考优先级。

7. key（实际真正用到的索引）

表示 MySQL 在执行这条 SQL 时，实际最终选择并使用的索引名称，这是索引是否真正生效的核心判断依据

核心特点：

1. key 是 possible_keys 的子集：实际用的索引，一定在候选索引列表里；
2. possible_keys有值，但key=NULL：表示索引存在，但是 MySQL 优化器判定用索引不如全表扫描快，放弃使用索引；
3. key=NULL：表示完全没有使用任何索引，这是性能差的核心原因；
4. 显示索引名：比如idx_user_name，表示这条 SQL成功用上了该索引，索引生效

8. key_len（实际使用的索引长度）

表示 MySQL 在执行查询时，实际用到的索引字段的字节长度，单位是字节

核心特点：

1. key_len 越长，代表用到的索引字段越多、字段越长

2. key_len 是索引生效的精准判断依据
   例如：
   创建联合索引 idx_name_age_sex(name,age,sex)，如果key_len只包含name的长度 → 只有第一个字段生效，索引失效一半；如果key_len包含name+age+sex的长度 → 联合索引全字段生效。

3. key_len 是预估的最大长度，是精确值，越小越好

9. ref（索引匹配的关联条件）

表示 MySQL 在使用索引查询时，与索引字段进行等值匹配的内容是什么

常见取值：

1. const：常量匹配，比如 where id=100，用常量 100 匹配主键索引；
2. 具体字段名：比如 a join b on a.id = b.user_id，则 b 表的 ref 字段显示test.a.id，表示用 a 表的 id 字段匹配 b 表的索引；
3. NULL：非等值匹配（比如范围查询> < between）、全索引扫描、全表扫描时，该字段为 NULL。

10. rows（预估扫描的行数）

MySQL 查询优化器预估为了找到符合条件的记录，需要扫描的表 / 索引的行数
核心特点：

1. 这个值是预估数值，不是精确值，但误差极小，具备绝对的参考价值；
2. rows的数值越小越好，代表 MySQL 只需要扫描少量行就能找到结果，性能越好；
3. 如果rows数值接近表的总数据量 → 说明走了全表扫描，性能极差。

11. filtered（过滤后的行占比）

表示经过查询条件过滤后，符合条件的记录数占扫描行数 (rows) 的百分比，取值范围：0 ~ 100。

核心规则：

1. filtered 的值 越大越好，百分比越高，代表「过滤效果越好」，扫描的行中大部分都是符合条件的，无用扫描少；
2. filtered = 100：表示扫描的所有行都符合条件，无过滤；
3. filtered < 10：表示扫描的行中只有极少部分符合条件，过滤效果极差，大概率是查询条件写的不合理（比如没有索引、用了函数导致索引失效）

12. Extra（额外执行信息、优化提示）

这是explain的补充字段，包含了无法用其他字段表达的、极其重要的执行细节，记录了 MySQL 执行查询时的特殊逻辑、优化策略、性能隐患，很多时候看 Extra 字段，就能直接判定 SQL 是否需要优化，以及如何优化

常见取值：

1. Using index （覆盖索引）：查询的所有字段（select 后的字段 + where 条件字段）都在同一个索引中，MySQL 只需要扫描索引就能返回结果，不需要回表查询物理数据，这是单表查询的极致优化，说明这条 SQL 的索引设计完美，性能拉满

2. Using where（临时表）：查询有WHERE条件，MySQL 通过索引找到数据后，再做条件过滤，索引生效 + 条件过滤，正常场景，无需优化；

3. Using temporary（文件排序）：MySQL 需要创建临时表来存储中间结果，临时表是内存 / 磁盘表，数据量大时会严重拖慢性能，看到这个值，必须优化
   优化方向：给分组 / 去重的字段加索引。

4. Using filesort ：MySQL 无法利用索引完成排序，需要把数据加载到内存后手动排序，排序的数据量越大，性能越差，慢查询高频元凶
   优化方向：给排序字段创建索引，让排序通过索引完成（默认是升序asc）

索引的使用规则

最左前缀法则

对联合索引idx(字段1,字段2,字段3)，只有查询条件从索引的最左侧字段开始、连续匹配时，索引才会生效。
例如：

• 有效匹配：字段1、字段1+字段2、字段1+字段2+字段3（从左到右连续使用索引字段）；
• 无效匹配：字段2、字段3、字段2+字段3（跳过左侧字段，索引完全失效）。
  即：联合索引必须从第一个字段开始用，中间不能断

索引失效情况

1. 违反最左前缀法则
联合索引idx(a,b,c)，查询条件用b/b,c/c，跳过左侧字段a，索引失效。

2. 字段类型不匹配
例如：where phone = 13800138000（phone是字符串类型，传入数字），触发隐式转换，索引失效。
也就是条件是字符串必须用引号’ ’

3. 模糊查询使用%xxx或%xxx%
like'%关键词'或like '%关键词%'，索引失效（仅like '关键词%'能命中前缀索引）。

4. 条件中使用函数操作或运算
对索引字段做函数操作（where left(name,2) = '张'）或运算（where id + 1 = 10），索引失效。

5. or条件中存在无索引字段
where 索引字段=1 or 无索引字段=2，or会导致所有条件的索引失效。

6. 范围查询后字段失效
联合索引idx(a,b)，where a>10 and b=2，a的范围查询后，b的索引失效。

7. 数据区分度过低
例如：性别字段（只有 2 个值）建索引，MySQL 会直接选择全表扫描，索引失效。

8. 查询结果占表数据比例过高
若查询结果超过表数据的 20%，MySQL 认为全表扫描比走索引更快，主动放弃索引。

SQL提示

SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

1. use index :建议 MySQL 优先使用指定的索引，给优化器一个优先级建议，如果这个索引完全无法匹配条件，优化器会再选其他方案
   例如：

-- 建议MySQL使用联合索引 idx_bookname_author，查询书名
select book_name from book use index(index_bookname_author);

2. ignore index:强制 MySQL 忽略指定的索引，告诉优化器这个索引绝对不能用

-- 忽略掉低效的单列索引 idx_name，让MySQL自己选其他更优的索引
select * from user ignore index(idx_name) where name='张三' and age=20;

3. force index:强制 MySQL 使用指定的索引，不管优化器认为走索引成本高 / 区分度低 / 查询占比高，必须走这个索引

-- 性别字段有索引 idx_gender，MySQL默认不走索引，强制让它走索引
select * from user force index(idx_gender) where gender = '男';

SQL优化

插入数据优化

核心是提升批量插入效率
常用手段：

1. 用insert into 表 values(...)批量插入（少用单条循环插入）；
   例如：

insert into user(name,age) values('张三',20),('李四',21),('王五',22);

2. 关闭自动提交事务（set autocommit=0），批量插入后统一提交；
   例如：

set autocommit = 0; -- 关闭自动提交
insert into user(name,age) values('张三',20),('李四',21),('王五',22); -- 批量插入N条数据
commit; -- 统一提交事务
set autocommit = 1; -- 恢复默认自动提交

主键优化

1. 用自增主键（避免 UUID 等无序主键导致的页分裂）；
2. 主键字段尽量小（如int代替bigint），减少索引占用空间；
3. 避免主键更新（会导致聚集索引重建，性能损耗大）。

order by优化

目标是让排序走索引，避免using filesort：

1. 把排序字段加入联合索引（遵循最左前缀）；
   例如：联合索引idx_name_age(name,age)，查询按 age 排序
   索引失效写法： 触发Using filesort：跳过左侧字段，直接排序

explain select name,age from user order by age; 
-- type=ALL，Extra=Using filesort

索引生效写法：

explain select name,age from user where name='张三' order by age;
 -- type=ref，无Using filesort

2. 避免select *（只查索引包含的字段，触发覆盖索引）；
   还是上面那个例子：
   低效排序： 查所有字段，即使排序字段有索引，也会回表后再排序

explain select * from user where name='张三' order by age;
 -- Extra=Using filesort

高效排序： 只查索引内字段，触发覆盖索引，直接用索引有序性返回结果

explain select name,age from user where name='张三' order by age; -- Extra=Using index

group by优化

核心是减少临时表与文件排序：

1. 把分组字段加入索引（让分组基于有序索引完成）；
   例：按 age 分组统计

无索引： 先全表扫描再排序分组

explain select age,count(*) from user group by age;
 -- Extra=Using temporary; Using filesort

创建索引idx_age再分组
有索引： 索引本身有序，直接按索引分组，无需额外排序

explain select age,count(*) from user force index(idx_age) group by age;
 -- Extra=Using index

2. 先过滤再分组（用where提前缩小数据范围）；
   还是上面那个例子：

低效写法： 先全表分组，再过滤结果，扫描数据量大

select age,count(*) from user group by age having age > 20;

高效写法： 先 where 过滤，再分组，扫描数据量大幅减少

select age,count(*) from user where age > 20 group by age;

limit优化

解决大偏移量分页慢：
用主键 / 唯一索引定位
低效写法： limit 偏移量,条数，偏移量越大越慢

explain select * from user limit 100000,10;
 -- rows=100010，扫描10万行只返回10行

高效写法： 通过主键过滤偏移量，只扫描 10 行

explain select * from user where id > 100000 limit 10;
 -- rows=10，精准扫描目标数据

count优化

不同count写法性能差异大：

1. count(字段) ：最慢，需取值 + 判空（无 not null 约束时），尽量不用；
2. count(主键id) ：较慢，需遍历取出主键值累加，主键非空无需判空；
3. count(1) ：很快，遍历表不取任何值，每行赋值常量 1 直接累加；
4. count(*) ：最快最优，遍历表不取任何值，仅统计存在行，语义最贴合统计总行数。
   效率排序：
   count(无约束字段) < count(有约束字段) < count(主键id) < count(1) ≈ count(*)

update优化

InnoDB 行锁基于索引，若update条件未命中索引，会升级为表锁：

1. 确保update的where条件命中索引；
   错误写法： 无索引条件，表锁，并发时所有 update 排队等待

-- age无索引，锁整张表
update user set name='张三' where age=20;

正确写法：

create index idx_age on user(age); -- 建索引
update user set name='张三' where age=20; 
-- 命中索引，行锁生效

2. 避免更新主键（会重建聚集索引）
   更新主键会导致聚集索引全量重建，锁表 + 性能损耗极大