关于索引线上bug记录

线上碰到一个奇怪问题，偶现查询不到数据问题

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS date, COUNT(CASE WHEN order_status != 404 THEN 1 ELSE NULL END) AS totalOrderQty FROM order_m WHERE is_delete = 0 AND order_source = 0 AND create_time > CONCAT(?,' 00:00:00') AND create_time <= CONCAT(?,' 23:59:59') AND tenant_id = ? AND warehouse_no = ? GROUP BY date 

执行这条sql查询出来的数据要比执行下面的多

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS date, COUNT(CASE WHEN order_status != 404 THEN 1 ELSE NULL END) AS totalOrderQty FROM order_m WHERE is_delete = 0 AND order_source = 0 AND tenant_id = ? AND warehouse_no = ? 

 

这个是因为create_time是范围查询，联合索引的话，如果索引中某个字段是范围查询，那么这个索引中该字段右边字段将不会被过滤，就回表查询了

所以扫描的就多

改成tenant_id, warehouse_no, create_time 这个顺序就可以了
不过还要看业务上有没有不加租户库房，单独用create_time做查询的，如果有create_time就需要单独建索引