mysql索引 - MySQL 执行计划(Using where,Using index 和 Using index condition)

Question

关于执行计划的 Extra 字段，对这几个取值有一些疑惑，我说一下我的大致理解。

1. Using where：表示优化器需要通过索引回表查询数据；

2. Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；

3. Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）;
   
   Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

4. Using where && Using index：这个确实不了解它和 Using index condition 的区别。

然后，我在 MySQL 的示例数据库 Sakila 中做了一些测试，但是结果却让我感到非常疑惑：

测试使用 Sakila.rental 表，表中的 customer_id 建立了名为 idx_fk_customer_id 索引。

-- 第一个 SQL 语句
EXPLAIN SELECT customer_id FROM rental WHERE customer_id>=300;

结果是使用了 idx_fk_customer_id 索引，但是 Extra 信息竟然为 Using where;Using index;
在这个 SQL 语句中，SELECT 子句 和 WHERE 子句不是都是可以从索引中过滤并取得的吗，为什么Extra 的值不是 Using index 呢？

-- 第二个 SQL 语句
EXPLAIN SELECT * FROM rental WHERE customer_id>=373;

这回更奇怪了，因为 SELECT 语句中包含索引中不存在的数据，所以需要通过索引回表查询数据，所以 Extra 为 Using where 我可以理解，但是这里竟然 type 竟然为 ALL，也就说执行计划中使用的是全表扫描！这又是为什么呢？

-- 第三个 SQL 语句
EXPLAIN SELECT customer_id FROM rental WHERE customer_id>=373 AND customer_id<400;

这个语句的 Extra 值同样为 Using where;Using index

-- 第四个 SQL 语句
EXPLAIN SELECT * FROM rental WHERE customer_id>=373 AND customer_id<400;

这个语句的执行计划就比较好理解了，先使用 cusomter_id>373 或者 customer_id<400 中的一个条件过滤索引，过滤完索引后，通过索引回表扫描并再次过滤掉一部分信息，随后返回最终的结果，Extra 为 Using index condition.

还望各位大神能不吝解答，或者您可以指点我最关心的三个问题：
1.Using where && Using index 和 Using index condition 的区别；

2.为什么EXPLAIN SELECT customer_id FROM rental WHERE customer_id>=300;会使用索引，
而 EXPLAIN SELECT * FROM rental WHERE customer_id>=300; 则不会使用索引呢？

EXPLAIN SELECT * FROM rental WHERE customer_id>=300 AND customer_id<=350;会使用索引
EXPLAIN SELECT * FROM rental WHERE customer_id>=300 AND customer_id<=476;则不会使用索引
索引对 RANGE 值范围有要求吗？
customer_id 是 SMALLINT(5) 类型的

Answer 1

• My understanding of Using index condition is, er, first of all, mysql server and storage engine are two components. The server is responsible for the parse and execution of sql; the storage engine actually does the reading of data/index. Fetch/write. This used to be like this: the server commands the storage engine to read the corresponding data from the data table according to index, and passes it to the server, and the server makes selections based on where conditions; now ICP lets the storage engine when possible. Make judgments based on index. If the conditions are not met, there is no need to read the data table. This saves disk IO.
  https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization. html

• does not use index because you are select *, and your where is >=, if mysql uses index to search, there will be too much random disk IO. So it chooses full table reading.
  https://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html

You are using a key with low cardinality (many rows match the key
value) through another column. In this case, MySQL assumes that by
using the key it probably will do many key lookups and that a table
scan would be faster.

• Check how much data customer_id>=300 AND customer_id<=350; and customer_id>=300 AND customer_id<=476; have respectively. I think the first one may have less data.

Answer 2

3Q, I almost figured it out, but I still have some questions about Using where; Using index and Using index condition. I will check the manual later. .

1.The difference between Using index condition and Using where;Using index
http://stackoverflow.com/questions/28759576/mysql-using-index-condition-vs-using-where-using-index

The difference between Using index and Using where;Using index;
http://stackoverflow.com/questions/25672552/whats-the-difference-between-using-index-and-using -where-using-index-in-the

2. This is similar to what you said, but it is not because where is >= ;

If the index exists, the optimizer will choose whether to use the index or perform a full table traversal based on the ratio of the number of items in the RANGE range to the total number

For example, in the rental table, the total number of rows in the table is 16044;

-- 不使用索引
EXPLAIN SELECT * FROM rental WHERE customer_id>492;
-- 使用索引
EXPLAIN SELECT * FROM rental WHERE customer_id>493;
-- 其中 id > 492 的行数为 2772, id > 493 的行数为 2749

-- 不使用索引
EXPLAIN SELECT * FROM rental WHERE customer_id<103;
-- 使用索引
EXPLAIN SELECT * FROM rental WHERE customer_id<102;
-- 其中 id < 103 的行数为 2767, id < 102 的行数为 2734

-- 不使用索引，count(*) 为 2758 条
EXPLAIN SELECT * FROM rental WHERE customer_id>100 AND customer_id < 202;
-- 使用索引, count(*) 为 2733 条
EXPLAIN SELECT * FROM rental WHERE customer_id>100 AND customer_id < 201;

Conclusion: When the data to be read exceeds a critical value, the optimizer will give up reading from the index and perform a full table scan instead. This is to avoid too many random disks.