MySQL 性能调优是一个优秀工程师的必备技能，性能优化做得好，可以起到事半功倍的效果。
MySQL 性能调优包括：JVM 调优、索引优化、表优化、缓存、SQL 性能优化等。
本文，主要介绍 MySQL 的索引优化。
文末附：MySQL 面试题精选大全及答案（最新整理，附备面路线） – Java面试题宝
1. MySQL 查询处理过程
我们先来了解一下 MySQL 执行查询 SQL 的处理过程，这对于理解后续优化非常重要。
解析SQL语句
首先，Mysql 会对查询语句进行解析，确定表名、列名以及查询条件。
SQL 语句是 SELECT * FROM products WHERE product_id = 123;
查询优化器
优化器会根据查询语句、表结构和索引信息等，选择最佳的执行计划。
执行计划生成
根据优化器选择的执行计划，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%）
3.2 表结构设计优化
表结构设计优化，包括：
- 深分页
- 字段数量
- 字段类型
- 默认值
- 主键设计
- 记录判断
- in条件
- 预留字段
- 索引数量等
点击蓝字查看详解篇：MySQL 表设计太糟糕了？9大优化细节，让性能飞升！
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—
