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等，这些都是可以定位慢查询日志的小工具。

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