MYSQL- 索引分析与优化

MYSQL- 索引分析与优化

EXPLAIN

MySQL 提供一个EXPLAIN 命令，它可以对SELECT 语句进行分析，并输出SELECT 执行的详细信息，供开发人员有针对性的优化。
例如：

EXPLAIN SELECT * from user WHERE id < 3;

EXPLAIN 命令的输出内容大致如下：

select_type

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

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

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

案例 1

案例 2

案例 3

TYPE

all

这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。

# 这是因为no列既不是主键也不是索引，因此只能采用全表扫描来查找目标no。
mysql> explain select * from employee where `no` = '20150001';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |

index

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。

mysql> explain select * from employee order by `no` ;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
mysql> explain select * from employee order by rec_id ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | employee | index | NULL          | PRIMARY | 4       | NULL |    5 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

上面可以看出，根据no列排序的连接类型是all型的，但是注意extra列是用到了排序（Using filesort），而根据rec_id列排序的连接类型是index，而且得到的结果自然是有序的，不许额外的排序。可能正是因为这个缘故，index的效率比all高，但注意这需要相同的条件才成立（既需要排序）。

如果连接类型为index，而且extra列中的值为‘Using index’，那么称这种情况为 索引覆盖；

mysql> explain select rec_id from employee ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | employee | index | NULL          | PRIMARY | 4       | NULL |    5 | Using index |

上例获取的rec_id刚好为索引列，因此无需回表取数据。

range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。

ref

出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）

 alter table employee add key I_EMPLOYEE_NAME(`name`); 

接下来，在employee表中根据name查找数据的时候，mysql优化器便选择了ref的连接类型。

mysql> explain select * from employee where `name` = '张三';
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table    | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | employee | ref  | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62      | const |    1 | Using index condition |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+

ref_eq

ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！
那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。

创建一张表

CREATE TABLE `score` (
  `rec_id` INT(11) NOT NULL AUTO_INCREMENT,
  `stu_id` INT(11) NOT NULL,
  `mark` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`rec_id`),
  UNIQUE KEY `UK_SCORE_STU_ID` (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

employee表中有五条数据，score表中有对应的五条数据，其中employee的rec_id 和score的stu_id 是一一对应的。

mysql> explain select ep.name,sc.mark from employee ep,score sc where ep.rec_id = sc.stu_id;
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref             | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
|  1 | SIMPLE      | sc    | ALL    | UK_SCORE_STU_ID | NULL    | NULL    | NULL            |    5 | NULL  |
|  1 | SIMPLE      | ep    | eq_ref | PRIMARY         | PRIMARY | 4       | my_db.sc.stu_id |    1 | NULL  |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+

上面就可以看到score表是全表扫描的类型，rows=5代表外层表循环了五次（因为有五条数据），但是employee表的rows怎么是1，怎么可能？刚开始也是很疑惑，这与mysql的查询原理息息相关，rows实际反映的是查询的内循环数，针对外层的每一条数据匹配，employee的确一枪就可以命中，因此rows为1。

const

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

possible keys

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

key

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

rows

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

key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引，或只用到索引的最左部分的部分字段值。

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个字节

  时间类型
  DATA：3个字节
  TIMESTAMP：4个字节
  DATETIME：8个字节

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

Extra

Extra 表示有很多额外信息，各种操作会在Extra 提示相关信息，常见几种如下：

• Using where
  表示查询需要通过索引回表查询数据。
• Using Index
  表示查询需要通过索引，索引就可以满足所需数据。
• Using filesort
  表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。
• Using temprorary
  查询使用了临时表，一般出现于去重，分组操作。