索引使用，分析初探。(explain分析执行计划，以及强制使用force index)

促使这次探索的初衷还是因为要对一个定时脚本性能进行优化。

脚本有两个指定状态分别是status, latest_process_status,和一个超期时间expire_time进行限制。

 

按照我以前的习惯，直接给这一组字段建了一个联合索引。写成了 expire_time_status_latest_process_status (expire_time, status, latest_process_status)

以前只知道，联合索引的第一个值会做单独的索引，但是不知道先后顺序不同也会导致索引检查的顺序不同。

建完之后想来查看一下语句究竟会按照何种套路执行，去搜了一下如何分析，看到了执行计划。使用了之后发现大概能知道有没有用索引，有哪些备选索引，需要怎么扫描之类。 先来看个例子。

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ec_customer_service
partitions: NULL
type: range
possible_keys: expire_time_latest_process_status_status
key: expire_time_latest_process_status_status
key_len: 6
ref: NULL
rows: 95172
filtered: 25.00
Extra: Using index condition

select_type: 查询的类型，这歌例子里面是一个简单查询的列子，对应的还有复杂查询，拥有自查询的复杂查询会显示不止一列。

table: 预计会操作的表

type: 表示mysql 执行计划预测会如何去访问表中的数据，这列有all, index, ref, const range, eq_ref, null 这7种。在这里显示的ref代表的是索引扫描，而且会与某些值进行参照。经常见到的type还有 all 和 range，一个代表全表扫描，一个代表范围查找，范围查找一边会用到><=类似这种范围型的条件。

possible_keys: 可能会选择的索引，如果有多个索引可用，这里会全部列出来。

key: 执行计划会使用的索引。

key_len: 索引长度。

ref: 这个我没太明白。。应该是参照key有个什么关系。

rows: 预计会扫描多少行。

Extra: 特别需要提到的信息：

这一列包含的是不适合在其他列显示的额外信息。mysql用户手册里记录了大多数可以在这里出现的值。
常见的最重要的值如下。
“Using index”
    此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
    这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
    这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
    这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
    这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

上面提到了一个索引覆盖，这里说下，索引覆盖无需访问数据文件，直接读取索引文件进行返回。速度快的飞起,这一点可以从查找行数rows字段看出来。

通过上面的例子我们可以看出来，这里我需要用到的信息是行数rows和key 看一下是用到了哪个索引，并且同时看下执行需要扫描多少行才可以。说一下，这里数据一共是20w行，可以看到在使用这个索引的情况下竟然执行计划要扫描近10w行数据，可见效率是不高的。 

 

为什么我在建立了看似不错的联合索引，效率才这么低。分析上面的sql语句不难发现，其实索引起最大作用的不应该是expire_time这个只规划了一个范围的字段，而是在status和latest_process_status上面，特别是status状态，在判断了多个状态的情况，是非常消耗查询时间的。我们不妨使用status做一个单索引来看一下开销

mysql> explain select * from ec_customer_service where expire_time<'2017-01-11' and status=1 and latest_process_status in (1000, 1001, 1002, 1003, 1004,1005);
+----+-------------+---------------------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table
| type | possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+---------------------+------+---------------+--------+---------+-------+------+-------------+
|
1 | SIMPLE
| ec_customer_service | ref
| status
| status | 4
| const | 1003 | Using where |
+----+-------------+---------------------+------+---------------+--------+---------+-------+------+-------------+

不难发现，居然只需要 扫描1003行就可以解决问题？快了近90倍。。 可见索引并不是把要查询的字段都包含进去就可以了这么简单，而是要更详细的分析自己使用的语句到底哪个字段或者哪几个字段做成目录可以提升查询效率。

如果我们同时建立好几个相关字段索引，在查看执行计划的时候，在possible_keys字段可以看到这些相关索引被罗列出来。 我们还可以通过在where前使用force index (索引字段) 来强制语句使用该索引，因为mysql语句在执行的时候，引擎并不总是会做出最优判断，你可以自己通过调整和查看执行计划，或者别的参数设置再结合自己的经验强制使用某个字段做搜索索引，从而提升搜索速度。

 

在这个例子中，我也尝试建立了类似 status_latest_process_status_expire_time这种联合索引，效果其实和status差不多，同样需要扫描近1000个字段，提升不大。而且filtered字段的值还会提升到达接近50%，key_lenth字段也变得很长，因为数据量并非 特别大，我还没有测试出filtered字段增大对性能带来的影响，但是如果能使用一个或两个索引字段就能搞定的事情，为什么要使用多个呢？

 

 

Reference:

http://blog.itpub.net/29773961/viewspace-1767044/   MySQL 执行计划explain详解