实用MySQL优化思路

青旅半醒 2022-12-24 13:57 201阅读 0赞

最近在开发过程中优化了不少SQL，在此记录一下我的优化思路，希望帮助到有缘人，若有大佬请多多 指教哈！

### 一、EXPLAIN 含义介绍 ###

查看执行计划：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70][]

结果字段含义**：**

<table> 
 <tbody> 
  <tr> 
   <td style="width:92px;">id</td> 
   <td style="width:92px;">select_type</td> 
   <td style="width:132px;">table</td> 
   <td style="width:114px;">partitions</td> 
   <td style="width:15px;">type</td> 
   <td style="width:86px;">possible_keys</td> 
   <td style="width:128px;">key</td> 
   <td style="width:130px;">key_len</td> 
   <td style="width:139px;">ref</td> 
   <td style="width:123px;">rows</td> 
   <td style="width:67px;">filtered</td> 
   <td style="width:15px;">Extra</td> 
  </tr> 
  <tr> 
   <td style="width:92px;">选择标识符</td> 
   <td style="width:92px;">查询的类型</td> 
   <td style="width:132px;">输出结果集的表</td> 
   <td style="width:114px;">匹配的分区</td> 
   <td style="width:15px;">表的连接类型</td> 
   <td style="width:86px;">可用索引</td> 
   <td style="width:128px;">实际使用的索引</td> 
   <td style="width:130px;">索引字段的长度</td> 
   <td style="width:139px;">列与索引的比较</td> 
   <td style="width:123px;">扫描出的行数</td> 
   <td style="width:67px;">按表条件过滤的行百分比</td> 
   <td style="width:15px;">执行情况的描述和说明</td> 
  </tr> 
 </tbody> 
</table>

（一）**id列**：值越大表示越先被执行；

（二）**select\_type列**：有两种分别是简单查询和复杂查询，其中简单查询：simple ；复杂查询：primary：表示复杂查询中最外层的 select；subquery：一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询；derived：包含在 from 子句中的子查询；union：位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived；

（三）**table列**：表示explain的一行正在访问哪个表。当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。当有union时，UNION RESULT的table列的值为<union 1,2>，1和2表示参与union的select行id；

（四）**type列**：依次从最优到最差分别为：system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL；

（五）**key列**：实际查询时用到的索引；

（六）**Extra列**：该列是很重要的参数，展示了MySQL在查询过程中的一些详细信息；常见值如下：

1、Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序；

2、Using temporary：用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用;

**　**　3、Not exists：MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了;

4、Using index: 说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录;

5、Using index condition: MySQL 5.6加入的新特性，。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上；

6、Using where: 表明使用了where 过滤。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤；

7、Using join buffer: 表示使用了连接缓存；

8、impossible where: where子句的值总是false，不能选择任何行；

9、select tables optimized away：在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(\*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化；

10、Distinct：优化distinct操作，在找到第一匹配的行后它将停止找更多的行；

### 二、思路分析 ###

（一）、通过explain查看执行计划，分析参数，使查询走索引，避免使用临时表；若没有索引则增加。

（二）、优化查询顺序，有时候查询顺序也会影响查询性能；

（三）、避免使用临时表保存中间结果。

### **三、使索引失效的情况** ###

1、like的左%；

2、in、not in、not exist；

3、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走；

4、!=  <>，若是连续的可以用between；

5、对索引列进行函数计算，类型转换都不会走索引；

6、or ，可以使用union all 来代替；

[ 记录一次MySQL优化过程遇到的坑\_伟哥-CSDN博客][MySQL_-CSDN]

[Oracle的临时表和MySQL的临时表\_伟哥-CSDN博客][Oracle_MySQL_-CSDN]

[ MySQL和Oracle数据库区别、高并发数据库优化\_伟哥-CSDN博客][MySQL_Oracle_-CSDN]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIyODk1MA_size_16_color_FFFFFF_t_70]: /images/20221120/ea0b374a091d4a52b6f8f9d6a7fc701e.png
[MySQL_-CSDN]: https://blog.csdn.net/weixin_42228950/article/details/109686629
[Oracle_MySQL_-CSDN]: https://blog.csdn.net/weixin_42228950/article/details/108501849
[MySQL_Oracle_-CSDN]: https://blog.csdn.net/weixin_42228950/article/details/105699709