慢sql优化和Explain解析

要想程序跑的快，sql优化不可懈怠！今日来总结一下常用的慢sql的分析和优化的方法。

1、慢sql的执行分析：

大家都知道分析一个sql语句执行效率的方法是用explain关键词：
举例：sql:select * from test where bussiness_date =‘2024-10-30’;
分析： explain select * from test where bussiness_date =‘2024-10-30’；
分析执行结果：

给business_date 加上一个索引，索引名称：test_bussiness_date_IDX
再执行分析：

对照着这两个分析结果下边来说下每一列都是什么意思

id：

在一个大的查询语句中，每个select关键字都对应一个id，代表多个表之间的查询顺序，或者包含子查询语句中的顺序，id顺序分为3中情况：
（1）id相同，执行顺序从上到下（因为小编举例都是单表，所以分析结果只有一行数据，如果sql中包括子查询或者多表联查，分析结果会有多行，感兴趣读者可以试试）
（2）id不同，如果是子查询，id值会递增，执行顺序是从大到小的。id越大优先级越高，越先被执行。
（3）id相同和不同的同时存在，此时id相同的为一组，id越大越优先执行，组内id相同，从上到下依次执行（比较复杂的sql会出现这种情况）。

select_type

select关键字对应的查询类型，示例中的 SIMPLE 表示简单子查询。不光这一种类型：具体类型解释如下：
（1）simple 简单子查询
（2）primary 最外层子查询
（3）subquery 第一层子查询，再select 或者where中包含了子查询。
（4）derived 派生表 再from中包含的子查询，会被标记为衍生查询，会把查询结果放到一个临时表中。
（5）union 出现在union后面的查询
（6）union result union联合查询获取结果的select，如果有两个select查询语句，他们之间用union连接起来查询，那么第二个select会被标记为union，union的结果被标记为union result。

table

表名，表示这一行的数据是那个表的，如果有别名，会显示别名。

partitions

访问的分区表信息。

type（优化重要信息字段）

针对单表的访问方法，一般来说保证查询至少达到range级别，最好达到ref。type其他类型详解：
性能从好–>差：依次是 system->const->eq_ref->ref->range->index->all.
如果出现index或者all就需要优化了，以下对每个值做详细的解释：
（1）system：表中只有一行记录，system是const的特例，几乎不会出现这种情况，可以忽略不计。
（2）const: 将主键索引或者唯一索引放到where条件中查询，mqsql可以将查询条件转变成一个常量，只匹配一行数据，索引依次就找到数据了。
（3）eq_ref: 同ref差不多，但返回结果只有一条记录。
（4）ref：不是主键索引，也不是唯一索引，就是普通的索引，可能会返回多个符合条件的行。
（5）range：只用一个索引来选择行，key列显示所用的索引名称。
（6）index：也是读取全表，但是是从索引中读取。
（7）all：全表查询，从磁盘中读取。效率最差。

possible_keys

可能用到的索引，查询中涉及字段上若存在索引，则会被列出来，表示可能用到的索引，但是并不是实际上一定会用到的索引。

key

实际用到的索引。

key_len

表示索引中使用的字节数。通过该属性可以知道在查询中使用的索引长度，这个长度是最大可能长度，并非实际使用长度，在不损失准确性的情况下，长度越短查询效率越高。

ref

关联id等信息。当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

rows：

预估的需要读取的记录条数。根据表信息统计及索引的使用情况，大致估算找到所需记录需要读取的行数，row越小越好。

filtered

查到到所需记录占总扫描记录数的比例。

Extra（优化重要信息字段）

Extra：一些额外的信息。
当此字段中出现以下的两个值时，意味着mqSql根本不能使用索引，效率会收到重大影响，要尽可能的对此进行优化。
Using fileSort(使用文件排序) 和 Using temporary（使用临时表）
下面对每个值进行详细的解释：
（1）Using index：使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现了Using where，表明索引被用来执行索引键值的查找；如果没有同时出现Using where表明索引用来读取数据而非执行查找动作。
索引覆盖有两种理解方式：（1）就是select的数据列，只用从索引中就能取得，不必读取数据行，Mqsql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说也就是查询列要被所建的索引列覆盖。（2）索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据；当然通过读取索引就可以得到想要的数据，那就不需要读取行了，一个索引包含了满足查询结果的数据就叫做覆盖索引。
（2）Using index condition：用了条件索引（索引下推）
（3）Using where：从索引查出来数据后继续使用where条件过滤。
（4）Using join buffer(Block Nested Loop)：join的时候利用了join buffer（优化方法：去掉外连接，增大join buffer的大小）
（5）Using fileSort（重点优化）：用了文件排序，俗称“文件排序”，排序的时候没有用到索引，在数据量大的时候几乎是“九死一生”，在order by 或者group by过程中，order by的字段不是索引字段，或者select查询字段存在不是索引字段，或者select 查询字段都是索引字段，但是order by的字段顺序和select的索引字段的顺序不一致，都会导致fileSort。
（6）Using temporary（重点优化）：用了临时表保存中间结果，常见于order by和group by中（优化方法：增加条件以减少结果集、怎讲索引，总之就是：要么减少待排序的数量，要么提前排好序）
（7）Start temporary,End temporary：子查询的时候，可以优化成半连接，但是使用的是通过临时表来去重。
（8）FirstMatch(tb1_name)：子查询的时候，可以优化成半连接，但是使用的是直接进行数据比较来去重。
（9）impossible where：表示where子句的值总是false，不能用来获取任何元素。
（10）select tables optimized away 在没有group by 子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化Count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
（11）distinct：优化distinct,在找到第一匹配的元组后即停止找同样值的工作。

2、常见的慢sql优化手段：

（1）select * 语句减少使用，会增加很多不必要的消耗（cpu,io,内存,网络带宽等）可以在select语句后指明具体的字段名称，增加了使用覆盖索引的可能性；
（2）排序时注意是否能用到索引，确保order by和group by涉及的列上有索引，尽量减少排序和分组操作的数据量。
（3）使用like模糊查询的时候，尽量使用最左匹配模式，即like ‘abc%’,这样可以使用索引。
（4）避免对where语句中字段使用函数或运算，会导致索引失效（高版本的mysql数据库，函数也可以使用索引）
（5） 在多条件查询的时候，最好创建联合索引，因为多个单列索引在多条件查询时，一般只会生效一个索引，mysql会选择其中一个限制最为严格的作为索引。
（6）对于联合索引，要遵守最左前缀法则（使用联合索引的一部分字段时需要考虑最左原则，否则会失效，如果使用全部字段就不需要考虑了，不会失效）
（7）使用合理的分页方式，以提高分页的效率。
（8）使用limit限制返回的行数，如只需要一条数据使用limit 1;
（9）对大表进行分区，可根据时间、范围、哈希等方式分区，查询时只扫描相关分区，减少不必要的数据扫描。
（10）避免使用临时表
（11）优化联合查询，在联合查询中尽量能使用UNION ALL。UNION会去除重复的记录，需要额外的排序和去重操作，性能较差。UNION ALL直接合并结果集，不进行去重，性能更好。如果业务逻辑允许，邮箱使用UNION。
（12）拆分大字段，将包含大字段（如：BLOB,TEXT）的表进行拆分，将大字段放在单独的表中，已减少主表的存储开销和查询压力。
（13）读写分离，将读操作和写操作分离，通过主从复制实现读写分离，减少主库压力，提高读写性能。
（14）根据业务需求，从业务逻辑入手，优化查询需求和频率，避免不必要的频繁查询。