.ent做的网站有哪些,网站建设多少钱一个,ftp转换wordpress,商洛网站建设电话来自公众号#xff1a;谭小谭这篇文章将给大家介绍如何使用 explain 来分析一条 sql 。网上其实已经有非常多的文章都很详细的介绍了 explain 的使用#xff0c;这篇文章将实例和原理结合起来#xff0c;尽量让你有更好的理解#xff0c;相信我#xff0c;认真看完你应该会… 来自公众号谭小谭这篇文章将给大家介绍如何使用 explain 来分析一条 sql 。网上其实已经有非常多的文章都很详细的介绍了 explain 的使用这篇文章将实例和原理结合起来尽量让你有更好的理解相信我认真看完你应该会有特别的收获。explain 翻译过来就是解释的意思 在 mysql 里被称作执行计划即可以通过该命令看出 mysql 在经过优化器分析后决定要如何执行该条 sql 。说到优化器再多说一句mysql 内置了一个强大的优化器优化器的主要任务就是把你写的 sql 再给优化一下尽可能以更低成本去执行比如扫描更少的行数避免排序等。执行一条sql语句都经历了什么 我在前面的文章中有介绍过优化器相关的。你可能会问一般在什么时候会要用 explain 呢大多数情况下都是从 mysql 的慢查询日志中揪出来一些查询效率比较慢的 sql 来使用 explain 分析也有的是就是在对 mysql 进行优化的时候比如添加索引通过 explain 来分析添加的索引能否被命中还有的就是在业务开发的时候在满足需求的情况下你可能需要通过 explain 来选择一个更高效的 sql。那么 explain 该怎么用呢很简单直接在 sql 前面加上 explain 就行了如下所示。mysql explain select * from t;---------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------------------------------------------------------------------------| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |---------------------------------------------------------------------------------1 row in set (0.04 sec)可以看到explain 会返回约 10 个字段不同版本返回的字段有些许差异每个字段都代表着具体的意义这篇文章我不打算把每个字段都详细的介绍一遍东西比较多怕你也不容易记住不如先把几个重要的字段好好理解了。其中 type、key、rows、Extra 这几个字段我认为是比较重要的我们接下来通过具体的实例来帮你更好的理解这几个字段的含义。首先有必要简单介绍下这几个字段的字面意思。type 表示 mysql 访问数据的方式常见的有全表扫描(all)、遍历索引(index)、区间查询(range)、常量或等值查询(ref、eq_ref)、主键等值查询(const)、当表中只有一条记录时(system)。下面是效率从最好到最差的一个排序。system const eq_ref ref range index allkey 表示查询过程实际会用到的索引名称。rows 表示查询过程中可能需要扫描的行数这个数据不一定准确是mysql 抽样统计的一个数据。Extra 表示一些额外的信息通常会显示是否使用了索引是否需要排序是否会用到临时表等。好了接下来正式开始实例分析。还是沿用前面文章中创建的存储引擎创建一个测试表我们这里插入 10 w 条测试数据表结构如下CREATE TABLE t ( id int(11) NOT NULL, a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, PRIMARY KEY (id)) ENGINEInnoDB;然后看下面这条查询语句注意这个表目前只有一个主键索引还没有创建普通索引。mysql explain select * from t;---------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------------------------------------------------------------------------| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |---------------------------------------------------------------------------------1 row in set (0.04 sec)其中 type 值为 ALL表示全表扫描了大家注意看到 rows 这个字段显示有 100332 条实际上我们一共才 10w 条数据所以这个字段只是 mysql 的一个预估并不一定准确。这种全表扫描的效率非常低是需要重点被优化的。接下来我们分别给字段 a 和 b 添加普通索引然后再看下添加索引后的几条 sql 。mysql alter table t add index a_index(a);Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql alter table t add index b_index(b);Query OK, 0 rows affected (0.20 sec)Records: 0 Duplicates: 0 Warnings: 0mysql show index from t;--------------------------------------------------------------------------------------------------------------------------------------------| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |--------------------------------------------------------------------------------------------------------------------------------------------| t | 0 | PRIMARY | 1 | id | A | 100332 | NULL | NULL | | BTREE | | || t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | YES | BTREE | | || t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | YES | BTREE | | |--------------------------------------------------------------------------------------------------------------------------------------------3 rows in set (0.00 sec)mysql explain select * from t where a 1000;---------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------------------------------------------------------------------------------| 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | Using where |---------------------------------------------------------------------------------------1 row in set (0.00 sec)上面这条 sql 看起来是不是有点疑惑呢type 竟然显示刚刚不是给字段 a 添加索引了么而且 possible_keys 也显示了有 a_index 可用但是 key 显示 null表示 mysql 实际上并不会使用 a 索引这是为啥这里是因为 select * 的话还需要回到主键索引上查找 b 字段这个过程叫回表这条语句会筛选出 9w 条满足条件的数据也就是说这 9w 条数据都需要回表操作全表扫描都才 10w 条数据所以在 mysql 的优化器看来还不如直接全表扫描得了至少还免去了回表过程了。当然也不是说只要有回表操作就不会命中索引用不用索引关键还在于 mysql 认为哪种查询代价更低我们把上面的 sql 中 where 条件再稍微改造一下。mysql explain select * from t where a 99000;---------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------------------------------------------------------------------------------------------| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition |---------------------------------------------------------------------------------------------------1 row in set (0.00 sec)这回 type 值为 range 了key 为 a_index 表示命中了 a 索引是一个不错的选择是因为满足这条 sql 条件的只有 1000 条数据mysql 认为 1000 条数据就算回表也要比全表扫描的代价低所以说 mysql 其实是个很聪明的家伙。我们还可以看到 Extra 字段中值为 Using index condition这个意思是指用到了索引但是需要回表再看下面这个语句。mysql explain select a from t where a 99000;------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |------------------------------------------------------------------------------------------------------| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using where; Using index |------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)这个 Extra 中的值为 Using where; Using index 表示查询用到了索引且要查询的字段在索引中就能拿到不需要回表显然这种效率比上面的要高所以不要轻易写 select * 只查询业务需要的字段即可这样可以尽可能避免回表。再来看一个需要排序的。mysql explain select a from t where a 99000 order by b;-------------------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-------------------------------------------------------------------------------------------------------------------| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition; Using filesort |-------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)这个 Extra 中返回了一个 Using filesort意味着需要排序这种是需要重点优化的的也就是说查到数据后还需要 mysql 在内存中对其进行排序你要知道索引本身就是有序的所以一般来讲要尽量利用索引的有序性比如像下面这样写。mysql explain select a from t where a 99990 order by a;---------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------------------------------------------------------------------------------------------------| 1 | SIMPLE | t | range | a_index,ab_index | a_index | 5 | NULL | 10 | Using where; Using index |---------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)我们再创建一个复合索引看看。mysql alter table t add index ab_index(a,b);Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql explain select * from t where a 1000;-----------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-----------------------------------------------------------------------------------------------------------| 1 | SIMPLE | t | range | a_index,ab_index | ab_index | 5 | NULL | 50166 | Using where; Using index |-----------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)这条 sql 刚刚在上面也有讲到过在没有创建复合索引的时候是走的全表扫描现在其实是利用了覆盖索引同样是免去了回表过程即在 (ab_index) 索引上就能找出要查询的字段。这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划也提到了一些常见的索引优化事实上还有更多的可能性你也可以自己去写一个 sql 然后使用 explain 分析看看有哪些是可以被优化的。●编号592输入编号直达本文●输入m获取文章目录推荐↓↓↓Web开发更多推荐《25个技术类公众微信》涵盖程序人生、算法与数据结构、黑客技术与网络安全、大数据技术、前端开发、Java、Python、Web开发、安卓开发、iOS开发、C/C、.NET、Linux、数据库、运维等。
