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 之后才会使用索引
