ORDER BY ID导致慢查询

SQL

SELECT id AS history_id, serial_number, expiry_id, print_count, print_by
 , repeated_print, expiry_type_id, expiry_type_name, tag_start_time AS print_time, created_time
 , channel_id, expiry_duration, expiry_duration_unit_id, expiry_duration_unit_name, expiry_time
FROM pd_xq_expiry_print_history
WHERE store_id = 11023
 AND stock_print_name LIKE concat('%', '嘻嘻', '%')
 AND print_time BETWEEN '2021-06-12 00:00' AND '2021-07-12 23:59'
ORDER BY history_id DESC
limit 20;

索引

KEY `idx_store_id` (`store_id`),
KEY `idx_print_store` (`store_id`, `print_time`, `expiry_time`),

explain结果

优化

order by print_time 或者 强制索引

原因

order by id，mysql 的优化器会选择主键索引，但是 where 条件里又没有主键条件，导致全表扫描。
order by gmt_create，结合 where 条件里 gmt_create ，mysq 优化器会选择 gmt_create 索引，扫描的记录数少，效果更好。
详见 详见