当我尝试使用索引优化查询时，有些事情我无法理解。
该表如下所示：
create table order_table (
id int not null primary key,
customer_id varchar(50) not null,
sale int not null,
key customer_sale_idx (customer_id, sale)
);
when I run explain select * from order_table where customer_id=2 order by sale limit 1;
表明
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_table
partitions: NULL
type: index
possible_keys: customer_sale_idx
key: customer_sale_idx
key_len: 206
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where; Using index; Using filesort
And then I add another index alter table order_table add key sale_customer_idx (sale, customer_id);
now the explain select * from order_table where customer_id=2 order by sale limit 1; query shows
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_table
partitions: NULL
type: index
possible_keys: customer_sale_idx
key: sale_customer_idx
key_len: 206
ref: NULL
rows: 1
filtered: 20.00
Extra: Using where; Using index
Q1: I suppose mysql should check the customer_id first, get those records that match the where, then do the sort by sale. It uses the customer_sale_idx index as I expected, but I don't konw why it use Using filesort rather than utilize the cover index
(customer_id, sale) to do the sort.
Q2: MySQL uses the new index sale_customer_idx (sale, customer_id) after I added it. But I don't understand how this index helps to get rid of the Using filesort.
Q3: The latter explain says there is only one possible_keys which is customer_sale_idx, but then it use sale_customer_idx. How?
BTW,I know it's strange that customer_id is varchar. But let's just focus on explaining this phenomenon.
MySQL has to cast the first column of your index
(customer_id, sale)to an integer. So it cannot use the second column to sort.will be true for
customer_ids with the varchar values'2','02','002','2 and a half men'and many many more. It cannot jump to2, read the first row and know it is the one with the lowestsale-value (as all entries which are '2' are ordered by the 2nd columnsale). Only this would allow MySQL to not sort.Instead it has to go through the whole table, cast every row, check if it
= 2after casting, take the ones that are, and sort whatever is left. That's why you have the filesort there. MySQL doesn't know you have nothing else there that would cast to 2.实际上，它可以对主键执行相同的操作。由于您的索引涵盖范围，因此它们是等效的。
You can verify this if you do the comparison with a varchar, which doesn't require casting, thus the index entries it finds are correctly ordered by the second column
sale:Now it can to exactly was expected: jump to
'2', take the first row, stop. No sort required.Your index
(sale, customer_id)works the following way: it reads all values, ordered by sale, until it reaches one that is (autocasted) equal to2. MySQL can be sure now that this is the one you wanted (as you checked all lower values ofsalealready, none of them fit your condition oncustomer_id. Thus, no ordering is required.看起来该表中只有很少的行。对于非常小的表，表扫描和排序操作可能比索引扫描便宜。一旦有几千行，执行计划将更加可预测。