where 中使用转换函数导致索引失效

读过《高性能 mysql》 会了解到，where 后面如果使用函数对某一列转换，后面再跟条件，这个索引会失效，实际则是全表扫描。
今天开发给我一条 sql，就是典型一例：

space> select create_at from setting limit 10;

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

| create_at           |

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

| 2012-05-07 14:43:04 |

| 2012-05-07 14:59:15 |

| 2012-05-07 14:59:22 |

| 2012-05-07 15:11:22 |

| 2012-05-07 16:22:54 |

| 2012-05-07 16:25:44 |

| 2012-05-07 16:33:26 |

| 2012-05-07 16:58:46 |

| 2012-05-07 17:04:23 |

| 2012-05-07 17:08:03 |

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

10 rows in set (0.01 sec)

space> select unix_timestamp(create_at) from setting limit 10;                               

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

| unix_timestamp(create_at) |

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

|                1336372984 |

|                1336373955 |

|                1336373962 |

|                1336374682 |

|                1336378974 |

|                1336379144 |

|                1336379606 |

|                1336381126 |

|                1336381463 |

|                1336381683 |

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

10 rows in set (0.00 sec)

space> desc select create_at from setting where UNIX_TIMESTAMP(create_at) between 1336372984 and 1336381683;

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

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

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

|  1 | SIMPLE      | setting | index | NULL          | create_at | 4       | NULL | 30534426 | Using where; Using index |

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

1 row in set (0.20 sec)

用没用索引，可以从返回的rows 看的很清楚喔：

space> desc select create_at from setting where create_at between '2012-05-07 14:43:04' and '2012-05-07 17:08:03';                  

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

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

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

|  1 | SIMPLE      | setting | range | create_at     | create_at | 4       | NULL |   10 | Using where; Using index |

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

1 row in set (0.00 sec)

同样一条sql，使用转换函数 unix_timestamp 后，导致索引失效，返回的rows 3kw 之多。而没有使用转换函数，直接取，返回的rows 只有 10行。