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

• 我對Using index condition 的理解是, er, 首先mysql server 和storage engine 是兩個元件, server 負責sql的parse, 執行; storage engine 去真正的做資料/index的讀取/寫入. 以前是這樣: server 指令storage engine 按index 把對應的資料從資料表讀出, 傳給server, server來按where條件做選擇; 現在ICP則是在可能的情況下, 讓storage engine根據index 做判斷, 如果不符合條件則無須讀數據表. 這樣節省了disk IO.
  https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization. html

• 不用index 因為你是select *, 而且你的where 是>=, mysql 如果用index查找則會有太多的random disk IO. 所以它選擇了全表讀.
  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 wly amSQL 是and that a table
scan would be faster.

• 查以下 customer_id>=300 AND customer_id<=350; 和 customer_id>=300 AND customer_id<=476; 分別有多少數據. 我 覺得 第一個的數據 可能 比較少.

Answer 2

3Q，差不多搞清楚啦，只是 Using where;Using index 和 Using index condition 還有一些疑問，待我再查一次手冊。 。

1.Using index condition 和Using where;Using index 的區別
http://stackoverflow.com/questions/28759576/mysql-using-index-condition-vs-using-where-using-index

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

2.這跟您說得差不多，但是並不是由於 where 是 >= ；

最佳化器會在索引存在的情況下，透過符合 RANGE 範圍的條數和總數的比例來選擇是使用索引還是進行全表遍歷

例如，在 rental 表中，表格的總行數為 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;

結論：當需要讀取的資料超過一個臨界值時，最佳化器會放棄從索引中讀取而改為進行全表掃描，這是為了避免過多的random disk.