MySQL查询说明:查询何时具有“ WHERE”和“ ORDER BY”的索引选择

当我尝试使用索引优化查询时,有些事情我无法理解。

该表如下所示:

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.

... where customer_id=2 ...

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 to 2, read the first row and know it is the one with the lowest sale-value (as all entries which are '2' are ordered by the 2nd column sale). Only this would allow MySQL to not sort.

Instead it has to go through the whole table, cast every row, check if it = 2 after 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:

... where customer_id='2' ...

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 to 2. MySQL can be sure now that this is the one you wanted (as you checked all lower values of sale already, none of them fit your condition on customer_id. Thus, no ordering is required.

点赞
评论
没文化
没文化

看起来该表中只有很少的行。对于非常小的表,表扫描和排序操作可能比索引扫描便宜。一旦有几千行,执行计划将更加可预测。

点赞
评论