请教一个SQL查询效率的问题

Question

有两条一模一样的SQL，索引都做好了，唯一的区别是：

1. 第一条效率块where条件中order_id是字符型
2. 第二条效率慢where条件中order_id是数字型

请问这是什么原因呢？

第一条查询块只要36毫秒

select `type`, `order_id`, `before_num`, `jieyu` from `ju_user_money_log` where ((`type` = 'cz' and `order_id` = '15865273775255') or (`type` = 'cz' and `order_id` = '15865262845255') or (`type` = 'cz' and `order_id` = '158640257116323') or (`type` = 'cz' and `order_id` = '158635011616753') or (`type` = 'cz' and `order_id` = '158630991614232') or (`type` = 'cz' and `order_id` = '158623956816323') or (`type` = 'cz' and `order_id` = '15860989964888') or (`type` = 'cz' and `order_id` = '158605717216323') or (`type` = 'cz' and `order_id` = '15859317865255') or (`type` = 'cz' and `order_id` = '158588094316323') or (`type` = 'cz' and `order_id` = '15858422715255') or (`type` = 'cz' and `order_id` = '15858327803013') or (`type` = 'cz' and `order_id` = '15857539392292') or (`type` = 'cz' and `order_id` = '158571796916753') or (`type` = 'cz' and `order_id` = '158570810516323') or (`type` = 'cz' and `order_id` = '158553459916323') or (`type` = 'cz' and `order_id` = '15854954054888') or (`type` = 'cz' and `order_id` = '158545668816753') or (`type` = 'cz' and `order_id` = '158536708516323') or (`type` = 'cz' and `order_id` = '15853239205255') or (`type` = 'cz' and `order_id` = '15852679396177') or (`type` = 'cz' and `order_id` = '15852375795255') or (`type` = 'cz' and `order_id` = '15849791735255') or (`type` = 'cz' and `order_id` = '158493632415589') or (`type` = 'cz' and `order_id` = '15848888154888') or (`type` = 'cz' and `order_id` = '15848657253013') or (`type` = 'cz' and `order_id` = '15847182755255') or (`type` = 'cz' and `order_id` = '15846334685255') or (`type` = 'cz' and `order_id` = '15845941001825') or (`type` = 'cz' and `order_id` = '158451999516683') or (`type` = 'give' and `order_id` = '7779264') or (`type` = 'give' and `order_id` = '7797841') or (`type` = 'give' and `order_id` = '7820602') or (`type` = 'give' and `order_id` = '7827031')) and `key` = 'gold';

第二条慢查询，和上面一样，只是order_id是数字型

select `type`, `order_id`, `before_num`, `jieyu` from `ju_user_money_log` where ((`type` = 'cz' and `order_id` = 15865273775255) or (`type` = 'cz' and `order_id` = 15865262845255) or (`type` = 'cz' and `order_id` = 158640257116323) or (`type` = 'cz' and `order_id` = 158635011616753) or (`type` = 'cz' and `order_id` = 158630991614232) or (`type` = 'cz' and `order_id` = 158623956816323) or (`type` = 'cz' and `order_id` = 15860989964888) or (`type` = 'cz' and `order_id` = 158605717216323) or (`type` = 'cz' and `order_id` = 15859317865255) or (`type` = 'cz' and `order_id` = 158588094316323) or (`type` = 'cz' and `order_id` = 15858422715255) or (`type` = 'cz' and `order_id` = 15858327803013) or (`type` = 'cz' and `order_id` = 15857539392292) or (`type` = 'cz' and `order_id` = 158571796916753) or (`type` = 'cz' and `order_id` = 158570810516323) or (`type` = 'cz' and `order_id` = 158553459916323) or (`type` = 'cz' and `order_id` = 15854954054888) or (`type` = 'cz' and `order_id` = 158545668816753) or (`type` = 'cz' and `order_id` = 158536708516323) or (`type` = 'cz' and `order_id` = 15853239205255) or (`type` = 'cz' and `order_id` = 15852679396177) or (`type` = 'cz' and `order_id` = 15852375795255) or (`type` = 'cz' and `order_id` = 15849791735255) or (`type` = 'cz' and `order_id` = 158493632415589) or (`type` = 'cz' and `order_id` = 15848888154888) or (`type` = 'cz' and `order_id` = 15848657253013) or (`type` = 'cz' and `order_id` = 15847182755255) or (`type` = 'cz' and `order_id` = 15846334685255) or (`type` = 'cz' and `order_id` = 15845941001825) or (`type` = 'cz' and `order_id` = 158451999516683) or (`type` = 'give' and `order_id` = 7779264) or (`type` = 'give' and `order_id` = 7797841) or (`type` = 'give' and `order_id` = 7820602) or (`type` = 'give' and `order_id` = 7827031)) and `key` = 'gold';

Answer 1

是这样的，Mysql中字段类型不一致会导致索引无效。

Answer 2

补充一下，字段类型不一致导致索引无效的原因是：字段不一致会导致类型转换，而类型转换使用的是mysql函数，mysql函数无法使用索引字段。