MySQL 性能优化是一个优秀工程师的必备技能，性能优化做得好，能起到事半功倍的效果。

MySQL 性能调优包括：JVM 调优、索引优化、表优化、缓存、SQL 性能优化等。

大家好，我是爱分享的程序员宝妹儿。

本文，主要介绍 MySQL 的索引优化。

1. MySQL 查询处理过程

我们先来了解一下 MySQL 执行查询 SQL 的处理过程，这对于理解后续优化非常重要。

解析SQL语句：Mysql 会对查询语句进行解析，确定表名、列名以及查询条件。

查询优化器：优化器会根据查询语句、表结构和索引信息等，选择最佳的执行计划。

执行计划生成：根据优化器选择的执行计划，MySQL 生成具体的执行计划。

数据读取和过滤：MySQL 根据执行计划读取相应的数据，并进行过滤操作。

示例执行计划：使用 product_id 索引进行快速查找对应的商品信息。

结果返回：将过滤后的结果返回给应用程序，供进一步处理和展示。

2. SQL 执行顺序

再来看看我们写的 SQL 在 MySQL 的执行顺序，这对 SQL 的性能优化很有帮助，在建立复合索引时，我们需要考虑到这点。

示例：

在 tb_dept 中，建立一个复合索引 idx_parent_id_code。

两个 SQL 解释的结果如下：

第一条 SQL 索引的结果：

SQL 执行顺序：

先执行 where 后的 parent_id ，再执行 group by 后的 dept_code，这和索引的顺序一致，type 等级为 ref ，扫描行数 rows 是 4。

第二条 SQL 索引的结果：

SQL 执行顺序：

先执行 where 后的 dept_code，再执行 group by 后的 parent_id，和索引的顺序不一致，type 等级为 index，扫描行数 rows 是 19。

在当前索引下，第一条 SQL 索引的利用率比第二条更高。

如果我们之前使用的是第二个 SQL ，就要调整索引的顺序，使其和 SQL 的执行顺序一致。

如果我们之前两个 SQL 都有用到，就要再建一个复合索引 idx_code_parent_id 。

问题是，MySQL 是如何处理语句，并分析出查询或表结构的性能瓶颈的？

这里就要提到 explain 了，我们可以通过 expalin 来获得：

表的读取顺序表的读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询

上面示例中，在执行 explain 时，结果得到了一个表格，我们逐一解析下：

Id : MySQL QueryOptimizer 选定的执行计划中查询的序列号

Select_type:

SIMPLE: 简单的 select 查询,不使用 union 及子查询PRIMARY: 最外层的 select 查询UNION: UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集DERIVED: 用于 from 子句里有子查询的情况

Table : 输出行所引用的表

Type : 由优到差的顺序 system-->const-->eq_ref-->ref-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all，释义:

system: 表仅有一行const: const 用于用常数值比较 PRIMARY KEY 时eq_ref: 查询使用了索引为主键或唯一键的全部时使用。即：通过索引关键字可能查找到一个符合条件的行ref: 通过索引关键字可能查找到多个符合条件的行ref_or_null: 如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找index_merge: 说明索引合并优化被使用了unique_subquery: 在某些 IN 查询中使用此种类型,而不是常规的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)index_subquery: 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与unique_subquery 类似,但是查询的是非唯一 性索引range: 检索给定范围的行。当使用 <>、>、>=、<、<=、BETWEEN 或者 IN 操作符时,会使用到range。index: 全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。all: 最坏的情况,从头到尾全表扫描。

possible_keys : 哪些索引可能有助于查询，如果为空,说明没有可用的索引

key : 实际从 possible_key 选择使用的索引

key_len : 使用的索引的长度

ref : 显示索引的哪一列被使用了

rows : 请求数据返回的大概行数

extra : 其他信息，出现 Using filesort、Using temporary 意味着不能使用索引，严重影响效率，需要优化

Using filesort : 没有办法利用现有索引进行排序，需要额外排序

Using temporary : 需要用临时表存储结果集，通常是 group by 的列上没有索引。

Using index ： 利用覆盖索引，无需回表即可取得结果数据（即数据直接从索引文件中读取）

其中：

key、type 、rows、extra 最为重要，如果 key 为 null、all 、index，就需要调整、优化索引。

一般需要达到 ref、eq_ref 级别，范围查找需要达到 range，extra 有 Using filesort、Using temporary 的，就需要进行优化了。

根据 rows 可以很直观地了解到优化结果。

3. SQL 调优方法

3.1 SQL 查询性能优化

SQL 查询性能优化，包括：

禁止使用 select *用小表驱动大表join 关联表不宜过多禁止使用左模糊或者全模糊查询索引访问类型至少达到 range 级别更优雅的使用联合索引等。

点击蓝字查看详解篇：SQL查询性能优化（6 个常用技巧，效率提高80%） – Java面试题宝

3.2 表结构设计优化

表结构设计优化，包括：

深分页字段数量字段类型默认值主键设计记录判断in条件预留字段索引数量等

点击蓝字查看详解篇：9大表设计优化技巧（让你的MySQL性能飞升）  Java面试题宝

3.3 优化索引的使用

尽量使用主键查询，而非其他索引，因为主键查询不会触发回表查询。查询语句尽可能简单，大语句拆小语句，减少锁时间or 查询改写成 union 查询不做列运算，把计算都放入各个业务系统实现不用函数和触发器避免 %xx 查询，可使用：select * from t where reverse(f) like reverse('%abc');尽量少用 join 查询使用同类型比较，比如 '123' 和 '123'、123 和 123尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描列表数据使用分页查询，每页数据量不要太大避免在索引列上使用 is null 和 is not null【2023】MySQL 调优面试题

详见：MySQL 面试题精选大全及答案（最新整理，附备面路线）  Java面试题宝

总结

以上是关于 SQL 性能优化的一些经验总结，希望对大家有用。

我是爱分享的程序员宝妹儿，谢谢关注 Java面试题宝。

如果觉得不错，请【点赞+转发+关注】一键三连支持下哦。

---end---