mysql varchar类型条件查询不加引号

一张160w数据量的表

 

select * from order_promotion where order_no='15441913435665186' 

select * from order_promotion where order_no=15441913435665186

 

使用两条sql的查询时间查了一百多倍。

orde_no存在索引。

通过explain select * from order_promotion where order_no=15441913435665186

可以发现 并没有使用索引，而加引号之后才会使用索引。

 

参数类型与数据库类型不同，虽然能自动转换，但无法走索引

You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster.

https://dev.mysql.com/doc/refman/8.0/en/table-scan-avoidance.html