explain all 怎么优化_面试 | 巧用 explain 优化 MySQL 语句

点击上方蓝字设为星标

每周一、三、五上午 8:30 准时推送

下面开始今天的学习～

在 Web 应用的开发中，有一个数据库的支持必不可少，对于一些简单的 CRUD 操作来说，对应的语句可能写起来比较随意，但是在实际业务运行的时候会导致数据库的瓶颈，也导致了一些萌新开发人员在遇到数据库「卡住」或者「反应特别慢」的时候无从下手，只能试图「升级服务器配置」来短期缓解问题。 在涉及数据库相关知识的面试中，考过这样一道题：

说说看 MySQL 的 Explain 语句是用来做什么的？

MySQL 中的 explain

其实数据库的操作无非读和写，而出现运行时的异常在不考虑系统整体问题的情况下很有可能就是我们给数据库的语句有一些不可靠的地方，在日常的开发中，我们可能会开慢查询去记录一些执行时间比较久的 SQL 语句，不过找出这些 SQL 语句并不意味着完事了，我们常常用到 explain 这个命令来查看这些 SQL 语句的执行计划，查看该 SQL 语句有没有使用上了索引，有没有做全表扫描，这都可以通过 explain 命令来查看，总结一下就是：

1. 表的读取顺序

2. 表的读取操作的操作类型

3. 哪些索引可以使用

4. 哪些索引被实际使用

5. 表之间的引用

6. 每张表有多少行被优化器查询

比如以下简单的语句：select * from search_search ，如果在前方加入 explain ，表结构如下：

在我的运行环境下执行 explain select * from search_search 是如下输出：

对于不同的列，有如下解释：

有了对于以上信息的理解，我们就可以针对我们的语句进行分析了。 例如，在上述语句中，type 的值为 ALL，即表示全表扫描，由于我们的 select  语 句没有任何的优化，每一次查询都会遍历整个表。 那么如果加入一个条件的呢？

要求我们需要找到搜索数为 83 的公司的其中 5 个

explain select * from search_search where search_hit = 83 limit 5;

可以看出，虽然加入了一个 where 条件(在 Extra 中也有所标明)，不过依然使用了全表扫描。 通常来说，我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难，如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免。 不过这里需要注意，虽然 Explain 可以有这些特性，但是在使用上还是需要注意一些点，比如：

• 它不会考虑各种 cache

• 只能解释 select 操作

• 不能显示 MySQL 在执行查询时所作的优化工作

SQL 查询优化

对于查询语句来说，我们优化的方向可以从以下几个思路着手：

• 首先打开慢查询记录，并找到慢查询记录所在的位置：

set global log_queries_not_using_indexes=on;

• 使用 explain 分析语句时若发现 rows 非常大的查询语句可以考虑在对应的关键词上加入索引

• 子查询尽量修改为 JOIN 查询

• 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，即使其中有条件带索引也不会使用

• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引

• 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描

• 在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的，通过换为 >= 可以有效提升速度

例如：SELECT * FROM books WHERE name like "MySQL%" 和 SELECT  * FROM books WHERE name ＞=  "MySQL" and name ＜"MySQM"

最后，以下关于 MySQL 的面试题也经常会被问到，同学们不妨准备一下：

在数据库中 Explain 语句执行后，会有许多 items，请解释一下 item 分别含义是什么？ select_type，type，possible_keys，key_len，Extra

本文作者：Nova Kwok

编辑&版式：霍霍

声明：本文归 “力扣” 版权所有，如需转载请联系。

点个在看，少个 bug?