mysql 组合索引查询的时候没有使用到

[steely_chen]

以下是表的关键数据，其中包括了索引的表的属性
KEY `unit_building_floor_room_index` (`unit_id`,`building_code`,`floor`,`room_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


全表总共5条数据

分别执行sql执行计划

mysql> explain select * from room_meter_data where `unit_id`=2004 and `building_code`='0121' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: room_meter_data
         type: ALL
possible_keys: unit_building_floor_room_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from room_meter_data where `unit_id`=2004 and `building_code`='0121' and floor=13 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: room_meter_data
         type: ref
possible_keys: unit_building_floor_room_index
          key: unit_building_floor_room_index
      key_len: 74
          ref: const,const,const
         rows: 2
        Extra: Using index condition
1 row in set (0.01 sec)

ERROR: 
No query specified

我很奇怪为什么加了floor 之后才会使用索引

[rucypli]

select (*) from room_meter_data where `unit_id`=2004 and `building_code`='0121' 多少 如果和整个表的数据差不多，mysql则认为全表扫描代价更小

[steely_chen]

引用 1 楼 rucypli 的回复:

select (*) from room_meter_data where `unit_id`=2004 and `building_code`='0121' 多少 如果和整个表的数据差不多，mysql则认为全表扫描代价更小

总共6条数据。 select count(*) from room_meter_data where `unit_id`=2004 and `building_code`='0121'; 返回4条，where 条件没用到索引。 select count(*) from room_meter_data where `unit_id`=2004 and `building_code`='0121' and floor=13; 返回 2条，where 条件用到了索引。 我的疑问是为什么加了floor之后就会使用索引了。我的索引顺序是 KEY `unit_building_floor_room_index` (`unit_id`,`building_code`,`floor`,`room_code`)

[steely_chen]

引用 4 楼 wmxcn2000 的回复:

楼主多造点数据，比较 1W 行，这个数据量太小了。

为什么加了floor 就会使用索引呢？ 数据量相同

[zjcxc]

看到两个执行计划中的 rows 了么？ 总共 5 条 第一个查询的 Rows=5，也就是第1个查询返回5条，从 5 条中拿5条数据TV，很明显索引是累赘 第2个查询的 Rows=2，从 5 条选2条，可以用索引

[zjcxc]

第1个查询，如果你把 select * 改成 select (`unit_id`,`building_code`,`floor`,`room_code`，也就是只查索引中有的列，应该也会走索引，在这种女顾客规划，查完索引就得到所有数据了，走索引也是有意义的

[steely_chen]

知道今天大伙都开工了，先把贴子顶上去。

[卖水果的net]

楼主多造点数据，比较 1W 行，这个数据量太小了。

[steely_chen]

引用 7 楼 zjcxc 的回复:

第1个查询，如果你把 select * 改成 select (`unit_id`,`building_code`,`floor`,`room_code`，也就是只查索引中有的列，应该也会走索引，在这种女顾客规划，查完索引就得到所有数据了，走索引也是有意义的

事实证明修改了select中的例是可行的。 “第一个查询的 Rows=5，也就是第1个查询返回5条，从 5 条中拿5条数据TV，很明显索引是累赘” 不过第一个查询返回的结果集并不是5 ，只有3条。总共5条，返回了3条，但没使用索引。 我理解索引有单独的一个地方保存着。并且索引指向了具体的记录。

[ACMAIN_CHM]

当数据库认为表中30%以上记录符合条件时，就不会再使用索引。 而当SELECT中的字段如果恰巧都包含在索引时，数据库则认为直接从索引文件中读取比从数据文件中读取更为高效。 这些在MYSQL的官方免费手册或者 《数据库系统概论（第四版）》 均有详细介绍。

[zjcxc]

explain 显示的 rows 是评估数，不是实际数，这个是在查询前评估可能满足条件的数据，用于制定执行计划（在这里可以视为评估是否使用索引的标准） 评估数和实际数有差异是正常的，总不能实际做一次查询之后，再给你弄个准确的执行谟再执行一次吧