SQL查询索引优化之EXPLAIN

最新推荐文章于 2024-06-09 22:32:32 发布

拯救世界的派大星

最新推荐文章于 2024-06-09 22:32:32 发布

阅读量523

点赞数 1

mtdg

本文链接：https://blog.csdn.net/m0_46266407/article/details/107222171

版权

精选30+云产品，助力企业轻松上云！>>>

目录
1. 前言
2.explain命令详解
2.1 SQL举例及结果
2.2结果结构分析
2.2.1 id：SELECT识别符
2.2.2 select_type：SELECT类型
2.2.3 table：表名（别名）
2.2.4 type：联接类型
2.2.5 possible_keys：显示可能应用在这张表中的索引
2.2.6 key：显示MySQL实际决定使用的键(索引)
2.2.7 key_len：显示MySQL决定使用的键长度
2.2.8 ref：显示使用哪个列或常数与key一起从表中选择行
2.2.9 rows：显示MySQL认为它执行查询时必须检查的行数
2.2.10 extra：包含MySQL解决查询的详细信息
3.Navicat for MySQL创建索引注意事项
3.1如何选取索引字段
3.2 索引类型
3.3 索引方法
4.总结

1.前言

几个月前负责二次开发的运营平台项目，某些列表页面打开太慢、加载时间去到20-25秒，数据量大概几万条，功能需求开发完了，也是时候现在对其进行优化阶段，这里主要记录一下，对索引的查看、添加和优化。

2.explain命令详解

使用explain命令能够显示mysql中sql如何使用索引来处理select语句以及连接表。可以帮助我们选择更好的索引和写出更优化的查询语句。

2.1 SQL举例及结果

EXPLAIN SELECT

p1.id,

p1.team_name,

p2.department_name

FROM

`t_partner_team` AS p1

LEFT JOIN `t_partner_department` AS p2 ON p1.department_id = p2.id

WHERE

p2.partnerid = 2

可以看到explain命令返回了id、select_type、table、type、possible_keys、key、key_len、ref、rows以及Extra共是个参数数据给我们，下面一一分解之。

2.2结果结构分析

2.2.1 id：SELECT识别符

SELECT的查询序列号。

2.2.2 select_type：SELECT类型

其中共有如下八种类型

标识	备注
SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	最外面的SELECT
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询
DERIVED	导出表的SELECT(FROM子句的子查询)

2.2.3 table：表名（别名）

2.2.4 type：联接类型

标识	备注
all	对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
range	只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
index	该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
index_merge	该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
index_subquery	联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
unique_subquery	该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
ref	对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
ref_or_null	该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
eq_ref	对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
const	表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
system	表仅有一行(=系统表)。这是const联接类型的一个特例。

2.2.5 possible_keys：显示可能应用在这张表中的索引

possible_keys列指出MySQL能使用哪个索引在该表中找到行。

注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

2.2.6 key：显示MySQL实际决定使用的键(索引)

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

标识	备注
USE INDEX	用来提供你希望MySQL去参考的索引列表，就可以让MySQL不再考虑使用其他可用的索引。
INGNORE INDEX	让MySQL忽略一个或者多个索引。
FORCE INDEX	让MySQL忽略一个或者多个索引。

举例如下：

EXPLAIN SELECT

team_id

FROM

`t_partner_user` 

IGNORE INDEX  (PRIMARY,admin_index_id) 

WHERE

id = 1

AND departmentid = 3

2.2.7 key_len：显示MySQL决定使用的键长度

如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

2.2.8 ref：显示使用哪个列或常数与key一起从表中选择行

2.2.9 rows：显示MySQL认为它执行查询时必须检查的行数

2.2.10 extra：包含MySQL解决查询的详细信息

标识	备注
Distinct	MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
Not exists	MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
range checked for each record	MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
Using filesort	MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
Using index	从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
Using temporary	为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
Using where	WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
Using sort_union(...) Using union(...) Using intersect(...)	这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by	类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。
NULL	暂时不知道，待解答

3.Navicat for MySQL创建索引注意事项

3.1如何选取索引字段

选择唯一性索引
为经常需要排序、分组和联合操作的字段建立索引
为常作为查询条件的字段建立索引
限制索引的数目
尽量使用数据量少的索引
尽量使用前缀来索引
删除不再使用或者很少使用的索引

3.2 索引类型

normal：表示普通索引。
unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique。
full text : 表示全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

总结，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

3.3 索引方法

hash 索引：结构的特殊性，其检索效率非常高，索引的检索可以一次定位。
B-Tree 索引：需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。

hash 索引和B-Tree 索引的区别：

Hash 索引的查询效率要远高于 B-Tree 索引，但通常B-Tree最常见，原因如下，虽然 Hash 索引效率高，但是 Hash索引本身由于其特殊性也带来了很多限制和弊端，比如：

1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

4）Hash 索引在任何时候都不能避免表扫描。

Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

4.总结

这次优化页面速度让我成长良多，我终于对mysql数据库索引有了一个较全面笔记整理，也知道要怎么添加索引才是最合适的，虽然最后页面速度神奇的飞起来（上次优化不见好转，下午加个计时器想分块测一下各区域查询的速度的时候，就快的飞起，起初以为是数据库缓存问题，但是被组长否定了，后面估计是网络问题导致的），但也收获满满。