【MySQL】 SQL优化讲解

一、优化前的思考

在定位到慢查询后，面试官常问如何优化或分析慢查询的SQL语句。若存在聚合查询、多表查询，可尝试优化SQL语句结构，如多表查询可新增临时表；若表数据量过大，可添加索引，但添加索引后仍慢则需进一步分析。

二、SQL执行计划分析

（一）工具使用

MySQL提供了explain命令，在SQL语句前添加它们可获取查询语句的执行计划信息，语法为在正常SQL语句前加explain。例如：explain select * from user where id = 1。执行计划展示的是SQL执行情况，包含多个字段，其中部分字段对优化很重要。

（二）重要字段含义

1. possible key：表示SQL执行时可能使用的索引，可能有多个，如示例中可能使用的索引为primary，但不确定是否实际使用，需结合其他字段判断。

2. key：实际命中的索引，如上述示例中实际用到的索引是primary（主键索引），通过key和possible key字段可最终确定SQL是否命中索引，若已添加索引，还可判断索引是否失效。

3. key_len：当前使用索引实际占用的大小，与key字段共同用于判断索引相关情况。

4. Extra：提供额外优化建议，例如可通过它判断SQL在使用索引过程中是否进行了回表操作。

   • 其中using where和using index表示使用索引且不回表，
   • using index condition表示使用索引但需回表，若出现回表情况，索引存在优化空间。

5. type：表示SQL连接类型，常见值有system、const、eq_ref、ref、range、index、on等，从左到右性能越来越好。

   • system：查询的表是MySQL中内置的表，性能较好，但平时开发中较少查询自带表，使用不多。
   • const：根据主键索引查询时为const，如示例中的查询。
   • eq_ref：根据主键索引或唯一索引查询，且只能返回一条数据。
   • ref：使用索引查询，但与eq_ref不同，它根据其他索引查询，可能返回多条数据，如根据用户表中的地域字段（有索引）查询。
   • range：执行时走索引，但为范围查询。
   • index：走全索引查询，遍历整个索引树检索结果，效率不高。
   • all：效率最低，不走索引，全盘扫描数据。在实际开发中，应尽量使用前面性能好的连接类型，最低要求限制在range，若为index或all则需优化。

三、优化方法总结

1. 通过MySQL分析工具explain，掌握key、key_len字段可判断SQL是否命中索引及索引是否失效，若有问题可通过添加索引或检查SQL编写修复。
2. type字段可检查SQL是否有优化空间，如是否存在全索引扫描或全盘扫描情况。
3. extra可判断是否回表，若存在可通过添加索引或修改返回值优化。