EXPLAIN进行索引分析和优化

阿粉昨天读者朋友们说了关于MySQL的索引的类型还有索引的实现原理，今天，阿粉就来给大家说说这个索引分析和索引优化是应该怎么做的。

查看命中索引

MySQL 里面我们都知道一个指令， EXPLAIN，这个指令我们都知道，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。

比如我们的 SQL 是这样的：

​​EXPLAIN SELECT * from user WHERE id < 10;​​

就会吧所有的信息都给我们列出来，我们来分析一下这个每个字段内容的含义：

第一个参数：

select_type

表示查询的类型。常用的值如下：

• SIMPLE ： 表示查询语句不包含子查询或union
• PRIMARY：表示此查询是最外层的查询
• UNION：表示此查询是UNION的第二个或后续的查询
• DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
• UNION RESULT：UNION的结果
• SUBQUERY：SELECT子查询语句
• DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还 是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

type 参数也有很多的值，每个值的不同代表这不同的含义；

• ALL：表示全表扫描，性能最差。
• index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
• range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
• ref：表示使用非唯一索引进行单值查询。
• eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果。
• const：表示使用主键或唯一索引做等值查询，常量查询。
• NULL：表示不用访问表，速度最快。

possible_keys

表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

注意这个只是可能用到的索引，而不是实际上用到的索引。

key

表示查询时真正使用到的索引，显示的是索引名称。

rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是 越少效率越高，可以直观的了解到SQL效率高低。

key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

但是这个时候，我们知道了索引使用的字节数量的时候，我们就得看看他是怎么出来的这个数据

key_len的计算规则如下：

• 字符串类型

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n)：n*字符集长度

varchar(n)：n * 字符集长度 + 2字节

• 数值类型

TINYINT：1个字节

SMALLINT：2个字节

MEDIUMINT：3个字节

INT、FLOAT：4个字节

BIGINT、DOUBLE：8个字节

• 时间类型

DATE：3个字节

TIMESTAMP：4个字节

DATETIME：8个字节

• 字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息

比如你会在其中看到​​Using where​​​,​​Using index​​​,​​Using filesort​​​,​​Using temprorary​​,

​​Using where​​表示查询需要通过索引回表查询数据。

​​Using index​​表示查询需要通过索引，索引就可以满足所需数据。

​​Using filesort​​表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。

​​Using temprorary​​

查询使用到了临时表，一般出现于去重、分组等操作。

既然我们都知道了这个 关键字，那么肯定得想办法处理我们的 SQL 然后达到我们想要优化的目的。

那么查询优化需要我们做到哪些呢？

我们可以通过查看慢查询日志 来定位我们的慢业务问题。

我们应该怎么开启慢查询日志和怎么取查看慢查询的日志呢？阿粉在这里给大家讲述一波：

开启慢查询日志：

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

• long_query_time 指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询 记录到日志文件中。
• log_queries_not_using_indexes

表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。

查看慢查询日志:

使用mysqldumpslow查看:

这个可是非常经典的一个慢查询日志分析工具，可以通过该工具分析慢查询日志内容。

工具其实还有很多，并不限制只有这一种，还有pt-query-digest、mysqlsla等，这些都是可以定位慢查询日志的小工具。

至于如何来进项慢查询的优化呢？阿粉讲在下个文章来科普一下，毕竟今天已经我们吧分析索引的方法讲给了大家，大家也需要消化一下，不是么？

文章转载自公众号：  Java极客技术