mysql 索引范围扫描_全索引扫描和覆盖索引范围扫描

今天在review 一个SQL的时候，发现即使在列前面有函数操作，查询也能够使用到索引，如下：(OLD)

1 mysql> desc SELECT sql_no_cache COUNT(1) FROM Appointment WHERE YEAR(ScheduledDate)=YEAR(NOW()) AND MONTH(ScheduledDate)=MONTH(NOW());

+----+-------------+-------------+-------+---------------+---------------+---------+------+---------+--------------------------+

2 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

3 +----+-------------+-------------+-------+---------------+---------------+---------+------+---------+--------------------------+

4 | 1 | SIMPLE | Appointment | index | NULL | ScheduledDate | 9 | NULL | 1812978 | Using where; Using index |

5 +----+-------------+-------------+-------+---------------+---------------+---------+------+---------+--------------------------+

6 1 row in set (0.00 sec)

用的是全索引扫描(type=index)，用到了索引(key=ScheduledDate),并且用的是覆盖索引(Using index).看起来这个SQL没有问题，唯一不足之处在1)type列，INDEX是效率很差的连接类型，尽优于全表扫描(full). 2)扫描的行数太多(row=1812978).

按照最常见的优化方法改写SQL后的执行计划如下：(NEW)

1 mysql> desc SELECT sql_no_cache COUNT(1) FROM Appointment WHERE ScheduledDate > DATE_FORMAT(NOW(),'%Y-%m-01 00:00:00') AND ScheduledDate < DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),'%Y-%m-01 00:00:01');2 +----+-------------+-------------+-------+---------------+---------------+---------+------+--------+--------------------------+

3 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

4 +----+-------------+-------------+-------+---------------+---------------+---------+------+--------+--------------------------+

5 | 1 | SIMPLE | Appointment | range | ScheduledDate | ScheduledDate | 9 | NULL | 727166 | Using where; Using index |

6 +----+-------------+-------------+-------+---------------+---------------+---------+------+--------+--------------------------+

7 1 row in set (0.00 sec)

连接方式变成了范围查找(type=range),扫描的行数也减少了很多(rows=727166) 。

2个SQL的实际执行效率，在结果集很大的时候，还是存在比较大的差别

1

2

3

4

5

OLD

2.75

2.51

2.35

2.56

2.77

NEW

0.49

0.65

0.50

0.54

0.60

比较两个查询的平均执行时间OLD=(2.75+2.51+2.35+2.56+2.77)/5=2.588，NEW=0.556，效率相差 2.588/0.556=4.65.

分析一下1)为什么使用到函数了，还会用到索引？ 2)同样是使用了覆盖索引，为什么会出现严重的性能偏差？

这里举的例子有特殊性，select count(1),获取全部满足条件的总记录数，只需要扫描符合条件的索引页，就能够得到正确的结果集，不需要回表取数据。这是问题1的解释。在OLD里面，需要扫描了全部的索引页，然后对里面的数据进行计算比较。在NEW里面，只需要扫描少数的索引页，就能够得到结果集。这些差别在row里面体现了出来。这是问题2的解释。

在非覆盖索引下，一般的情况查询应该是这样的：

1 mysql> desc SELECT sql_no_cache * FROM Appointment WHERE YEAR(ScheduledDate)=YEAR(NOW()) AND MONTH(ScheduledDate)=MONTH(NOW());2 +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

3 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

4 +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

5 | 1 | SIMPLE | Appointment | ALL | NULL | NULL | NULL | NULL | 1541817 | Using where |

6 +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

7 1 row in set (0.00 sec)

此时只通过索引是无法检索出结果集，所以使用了全表扫描。

优化后的执行计划是这样的：

1 mysql> desc SELECT sql_no_cache * FROM Appointment WHERE ScheduledDate > DATE_FORMAT(NOW(),'%Y-%m-01 00:00:00') AND ScheduledDate < DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),'%Y-%m-01 00:00:01');2 +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

3 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

4 +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

5 | 1 | SIMPLE | Appointment | ALL | ScheduledDate | NULL | NULL | NULL | 1541817 | Using where |

6 +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

7 1 row in set (0.00 sec)

这里还是使用了全表扫描。MYSQL中查询返回的结果行占表总行数超过15%(测试下来是15%左右是优化器走索引还是全部扫描的分割线，手册里写的是30%)时，就会选择全表扫描。在这个案例里，上述条件返回337711，总记录数1698272，337711/1698272=0.1989，超过门限值，所以用了全表扫描。缩小结果集

1 mysql> SELECT sql_no_cache count(*) FROM Appointment WHERE ScheduledDate > DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND ScheduledDate < DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 day),'%Y-%m-%d 00:00:01');2 +----------+

3 | count(*) |

4 +----------+

5 | 10169 |

6 +----------+

7 1 row in set (0.01sec)8

9 mysql> desc SELECT sql_no_cache * FROM Appointment WHERE ScheduledDate > DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND ScheduledDate < DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 day),'%Y-%m-%d 00:00:01');10 +----+-------------+-------------+-------+---------------+---------------+---------+------+-------+-------------+

11 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

12 +----+-------------+-------------+-------+---------------+---------------+---------+------+-------+-------------+

13 | 1 | SIMPLE | Appointment | range | ScheduledDate | ScheduledDate | 9 | NULL | 20924 | Using where |

14 +----+-------------+-------------+-------+---------------+---------------+---------+------+-------+-------------+

15 1 row in set (0.00 sec)

这里执行计划和预期相同。