提高MySQL索引策略一:隔离查询列

在mysql中执行查询时,如果没有将查询条件(条件列)隔离出来,那么查询引擎则无法利用建立在该列上的索引进行数据获取.这里的"隔离"意味着查询条件字段不能作为表达式的一部分出现,所以,如果在查询语句中指定表达式条件,就必须单独将条件列置于表达式的一边.举个简单例子,

 

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

 ,mysql无法对上述的actor_id进行索引查询,尽管从人们能一眼看出actor_id为4,但是mysql不会对该表达式进行计算.这种计算完全靠你来完成的,你应该养成简化查询语句的习惯(很多很多),对于这条sql,actor_id应该单独位于等号的一边.

 

这里有个很常见的例子:

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

 这里的sql将数据库中date_col到当前时间10天之内的数据检索出来,date_col上的索引在这个情况下完全是不可用的,因为TO_DAYS()函数式无法利用索引的,可以将检索条件更改为如下:

SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

 date_col完全可以利用索引来提高查询效率.除了这些,还能做的就是将CURRENT_DATE替换为当前时间,我相信上层调用都能获取到当前时间(如果你不要求服务器时间和mysql时间一致),还有一个提交CURRENT_DATE的原因是mysql无法对该条sql进行缓存,毕竟不同日期sql的执行时间都是不一致的.

 

上面是high-performance-mysql中的章节翻译,下面就进行一些简单的测试(数据50w+,innodb,timestamp索引,字段唯一度0.35),首先是不对查询列进行隔离.

1.通过条件控制获取所有数据(588951):

select sql_no_cache count(...) where to_days(current_date) - to_days(update_time) < 100;

 执行时间:1s

 

2.获取一半数据(276009):

select sql_no_cache count(...) where to_days(current_date) - to_days(update_time) < 60;

 执行时间:0.99s

 

3.获取极少部分数据(20161);

 

select sql_no_cache count(...) where to_days(current_date) - to_days(update_time) < 41;

 执行时间:0.97s

 

 

下面是对update_time进行列隔离.

1.通过条件控制获取所有数据(588951):

select sql_no_cache count(...) where update_time > date_sub(current_date ,interval 100 day);

 执行时间:1.18s

 

2.获取一半数据量(288821):

select sql_no_cache count(...) where update_time > date_sub(current_date ,interval 60 day);

 执行时间:0.58s

3.获取极少数据(35220):

select sql_no_cache count(...) where update_time > date_sub(current_date ,interval 41 day);

 执行时间:0.07s

最后把索引去除,进行数据的获取(不管是数据的多寡),未进行隔离的查询为1.00s,而进行隔离的查询为1.10s,所以

可以看出,在有索引的字段上进行条件查询,最好将该字段至于表达式的一边,否则索引对于查询的高效将无法得到发挥(以上的sql执行时间都通过100次计算的平均值得出)