📫作者简介：，网站建设定制开发专注于研究 Java/ Liunx内核/ C++及汇编/网站建设定制开发计算机底层原理/源码，网站建设定制开发就职于大型金融公司后网站建设定制开发端高级工程师，网站建设定制开发擅长交易领域的高安全/可用/并发/网站建设定制开发性能的架构设计与演进、网站建设定制开发系统优化与稳定性建设。
📫 热衷分享，喜欢原创~ 网站建设定制开发关注我会给你带来一些网站建设定制开发不一样的认知和成长。
🏆 InfoQ签约作者、CSDN专家博主/网站建设定制开发后端领域优质创作者/网站建设定制开发内容合伙人、网站建设定制开发阿里云专家/签约博主、51CTO专家 🏆
🔥网站建设定制开发如果此文还不错的话，还请👍关注、点赞、收藏三连支持👍一下博主~
本文目录
本文导读
网站建设定制开发本文详细介绍MySQL网站建设定制开发执行计划及其原理，并且对SQL网站建设定制开发调优提高查询效率，如何优化SQL网站建设定制开发做出指导性建议，同时本文也是前面16篇文章的一个优化与实战思路的指导综合。
可以学习本文之前，建议重点学习《》*、《》*、《》*、《》*、《》
一、MySQL 执行计划分析
首先确定SQL调优的都是什么，在我们工作中，不会什么SQL上来就是调优，当我们发现当慢查询或SQL执行遇到瓶颈时，首先我们要分析SQL的执行计划，找到导致慢SQL的原因。
为什么要关注sql执行计划？
因为sql执行计划可以告诉我们很多关于如何优化sql的信息。如何通过SQL计划访问中的数据（是使用全表扫描还是索引查找）一个表中的多个表中可能有不同的索引。表的类型是什么，它们是子查询、关联查询等等。
1、查看 SQL 执行计划
- -- 查看sql执行计划
- explain SQL;
- -- 查看表结构
- desc 表名;
- -- 查看建表语句
- show create table 表名;
1.1、explain详解
id：
每个select都会对应一个id，id值越大，执行优先级越高，相同的id值，从上往下依次执行
select_type：查询类型
下面给出 select_type 类型：
simple：简单查询，不包含子查询和 union；
primary：复杂查询的最外层查询；
subquary：包含在select中不包含在from的子查询；
derived：包含在from中的子查询，mysql会把这些数据放到临时表中，也称为派生查询；
union：在union中第二个和随后的select。
table：表示 explain 正在访问哪张表。
type：表示关系类型或者访问类型，即 mysql 如何查找表中的行。
下面给出 type 类型：
NULL：MySQL在优化阶段分解查询语句，在执行阶段不需要访问索引树或表的查询类型
system：这是常量的特殊情况。当表中只有一条数据时，常量查询为system
const：MySQL优化了查询的一部分，并将其转换为常量。将主键与常量进行比较时，表最多返回一条记录。
eq_ref：当主键或非主键索引的所有部分都被引用时，最多只返回一条记录。简单选择查询不会显示此类型。
ref：使用唯一索引是不实际的。如果使用公共索引或唯一索引的前缀部分，则与值进行比较时，索引可能会返回多行
range：范围扫描，in between
index：可以通过扫描整个索引获得结果。通常，扫描二级索引。此扫描不会从根节点开始快速搜索，而是直接扫描辅助索引的子节点。速度相对较慢。这种查询通常用于覆盖索引。次要索引通常较小，比所有索引都快
All：全表扫描，扫描聚集索引的所有单词节点。通常，这种情况需要优化。
possible_keys：这一列显示可能用到哪些索引。
key：mysql在查询过程中使用到的索引。
key_len：mysql在查询中使用索引的长度。
ref：显示在key列记录的索引中，表查找时所用到的列或常量，常见的有 const 常量，字段名 film.id。
extra：额外信息，常见的重要信息，例如 useing index 使用覆盖索引。
1.2、desc 以及 show create table 解析
2、通过配置文件（Profile ）查找查询成本消耗
- -- 开启性能分析
- set profiling=1;
-
- -- 执行 SQL
- select * from fork_business_detail where ODR_ID='123';
-
- -- 获取 Query_ID
- show profiles;
-
- -- 查看详细的 profile 信息
- show profile for query Query_ID;
3、通过 Optimizer Trace （优化器跟踪）表查看 SQL 执行计划树
- -- 开启 optimizer Trace 表查看 SQL 执行计划树
- set session optimizer_trace='enabled=on';
-
- -- 执行 SQL
- select * from fork_business_detail where ODR_ID='123';
-
- 查询 information_schema.optimizer_trace 表，获取 SQL 查询计划树
-
- -- 开启此项影响性能，记得用后关闭
- set session optimizer_trace='enabled=off';
二、MySQL 查询优化器
学习本节之前，建议重点学习《》、《》
1、客户端向服务器发送SELECT查询；
2、服务器首先检查查询缓存。如果缓存被命中，存储在缓存中的结果将立即返回。否则，进入下一阶段；
3、服务器执行SQL解析、预处理，查询优化器生成相应的执行计划；
4、MySQL调用存储引擎的API，根据优化器生成的执行计划执行查询；
5、结果将返回到客户端，并同时放入查询缓存。
三、MySQL 执行计划查询分析
1、key_len计算规则
字符串：
char(n) varchar(n)，n代表字符数，不是字节数，utf-8中；char(n) 就是3n字节；varchar(n)是3n+2个字节 2用来存储字符串的长度（因为varchar是可变长的）
数值类型：
tinyint 1字节；smallint 2字节；int 4字节；bigint 8字节
时间类型：
date 3字节；timestamp 4字节；datetime 8字节
如果字符串可以为null需要1字节记录字符串是否weinull，索引最大长度为768字节，当字符串过长，mysql会做一个类似最左前缀索引的处理，将前半部分字节提取出来做索引。
2、MySQL常见参数
参数 slow_query_log，是否启用慢速查询日志。ON或1表示启用，OFF或0表示禁用。
参数 long_query_time，设置慢速查询阈值。MySQL 5.7支持微秒。
参数 slow_query_log_file，慢查询文件存储路径。
参数 log_queries_not_using_indexes，指示是否将非索引查找SQL记录到慢速查询文件中。
参数 log_throttle_queries_not_using_indexes，表示每分钟记录到没有索引的慢速查询文件中的SQL语句的上限，0表示没有限制。
参数 max_execution_time，用于控制SELECT语句的最大执行时间，以毫秒为单位。如果超过此值，MySQL将自动终止查询。
四、如何优化 SQL
学习本节之前，建议重点学习 《》、《》、《》、《》
1、SQL索引篇
1.1、索引扫描
对于大表，如果扫描整个表，则返回查询需要很长时间，必须使用索引扫描来加快查询速度。但索引过多也会降低写入和修改的速度。而且，如果表数据和索引数据的比例失调，不利于后期的正常维护。
1.2、哪些列是适合业务需要的索引
创建具有高选择性的索引，也可以在状态列上创建索引（不是一定不可创建）。创建索引时，除了某些特殊情况外，应避免冗余索引。创建索引后，尽量不要过于频繁地修改它。对于可以在索引中完成的查询，不要返回到表。
与多个表关联的SQL必须在关联列上具有索引，并且具有一致的字段类型，以便MySQL在执行嵌套循环连接查找时可以使用索引，并且不会因为字段类型不匹配而发生隐式转换，从而导致无法使用索引。
当关联多个表时，请尝试使用具有小结果集的表作为驱动表，小结果集的表不是小表。
1.3、模糊匹配查询
当查询条件完全模糊时，例如“%**%”，则不能使用索引，在这种情况下，必须加具有高选择性的其他列或 where 条件，作为加快查询速度的补充。对于这种全模糊匹配场景，可以在 ES （elasticsearch）中求解。
2、排序篇
order by/group by SQL涉及排序，必须在索引中包含排序字段，并使排序字段的排序顺序与索引列中的排序顺序相同，这可以避免排序或减少排序次数。
MySQL不关心 from 中出现的表的顺序，也不关心 where 中的顺序。
3、查询选择篇
MySQL也非常擅长处理简短的SQL。总体时间将更短，并且不会产生臃肿的SQL，这很难理解和优化。
尽量不要使用子查询。重新扫描由子查询生成的临时表时，将没有要查询的索引，只能执行完整的表扫描。
4、MySQL 内部优化 SQL
1、重新定义表的关联顺序。当查询多个表时，MySQL 优化器将自动选择驱动器表和表的联接顺序，基于成本规则，SQL 执行时间将大大减少。
2、使用等效变化规则，MySQL 可以合并或减少一些比较，并删除一些总是正确或不正确的判断。
3、优化 count()、min() 和 max()。索引和列是否可以为空通常可以帮助 MySQL 优化此类表达式。例如，要查找最小值，只需查找索引树最左侧的第一条记录。
5、SQL 编写规范
1、SELECT只获取必需的字段，尽量避免 SELECT *，这可以减少网络带宽消耗，有效地使用覆盖指数，并且改变表结构对程序的影响很小。
2、用 in 替换 or，SQL语句中的 in 不应包含太多的值，该值应小于1000，过多的随机IO会增加并影响性能。
3、不要使用order by rand() ，因为会将向表中添加几个伪列，然后使用rand() 函数计算每行数据的 rand() 值。最后，根据行进行排序。这通常会在磁盘上生成一个临时表，因此效率很低。建议您首先使用 rand() 函数获取随机主键值，然后使用主键获取数据。
4、在具有不确定结果的SQL_函数中使用 now()、rand()、sysdate()、current_user()谨慎使用。在语句级复制场景中，主数据和从数据不一致；QUERY CACHE 查询缓存不能用于由值不确定的函数生成的SQL语句。
5、重要的SQL必须编制索引，其中条件列包括更新和删除、排序依据、分组依据、不同字段和多表联接字段。
6、禁止使用%前导查询，避免B+树索引失效。
7、禁用not in、!=、<>、not like否定查询。
8、EXPLAIN 判断 SQL 语句是否正确使用索引，并尽量避免 Using File Sort、Using Temporary 等 extra 列出现。
9、尝试使用批处理SQL语句，减少与数据库的交互次数。
10、在获取数量数据时，建议分批获取数据。每次获得的数据少于5000条，结果集应少于1M。
11、将复杂 SQL 拆分为多个小 SQL 以避免事务，减少锁表时间。简单 SQL 易于与 QUERY CACHE 查询缓存一起使用。
总结
本文详细介绍MySQL执行计划及其原理，并且对SQL调优提高查询效率，如何优化SQL做出指导性建议，同时本文也是前面16篇文章的一个优化与实战思路的指导综合。
学习本文之前，建议重点学习《》*、《》*、《》*、《》*、《》