CREATE TABLE parking_record (
id bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ‘Auto-increment ID’,
upload_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘ID uploaded by the booth’,
record_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘Parking record type 76 system 77 temporary’,
card_car_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Card ID’,
temp_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Temporary ID’,
entrance_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘Entrance time’,
entrance_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Entrance image’,
entrance_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance booth ID’,
exit_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘Exit time’,
exit_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Exit image’,
exit_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit booth ID’,
receivable_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Receivable amount’,
actual_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Actual amount received’,
admin_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Admin ID’,
ontime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Parking record booth creation time’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Parking record booth update time’,
parking_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Parking lot ID’,
park_name varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘Parking lot name’,
area_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Area ID’,
group_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Group ID’,
is_fixed int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘449=Special release’,
state int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘111 meaning unclear, 449=Special release, 452=Special amount release, 0=Default, others=cartypeid’,
car_owner_parking_fixed_location_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Long-term rental rate ID’,
exit_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit road’,
entrance_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance road’,
synid varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Primary key synid’,
free_time int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Free duration’,
plate_number varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘License plate number’,
upload_insert datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Cloud creation time’,
upload_update datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘Cloud update time’,
remarks varchar(500) NOT NULL DEFAULT ‘’ COMMENT ‘Remarks’,
online_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘Electronic payment amount’,
entrance_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance image confidence’,
exit_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit image confidence’,
entrance_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Entrance license plate color 0-None 1-Blue 2-Black 3-Yellow 4-White 5-Green’,
exit_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Exit license plate color’,
operate_user_id int(11) NOT NULL DEFAULT ‘-1’,
is_finish int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘Whether finished 0=Not finished 1=Finished’,
motor_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘0-Non-motor vehicle, 1-Motor vehicle Parking record table’,
motor_type_detail tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘Detailed type of motor vehicle’,
brand varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘Car brand’,
character_code varchar(250) NOT NULL DEFAULT ‘’ COMMENT ‘Feature code’,
status tinyint(4) NOT NULL DEFAULT ‘1’ COMMENT ‘Status (1: Valid; 2: Invalid)’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY idx_synid (synid),
KEY idx_plate_number_color (plate_number,entrance_car_plate_color),
KEY idx_entrance_time (entrance_time),
KEY idx_exit_time (exit_time),
KEY idx_upload_insert (upload_insert),
KEY idx_upload_update (upload_update),
KEY idx_parkingid_exittime (parking_id,exit_time),
KEY idx_parkingid_entrancetime (parking_id,entrance_time),
KEY idx_parkingid_platenumber (parking_id,plate_number),
KEY idx_parking_id_update_time_status_exit_time (parking_id,update_time,status,exit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=4110058290 */ COMMENT=‘Parking record’
sql:
SELECT
upload_id,
record_type,
card_car_id,
temp_id,
entrance_time,
entrance_car_image,
entrance_parking_box_id,
exit_time,
exit_car_image,
exit_parking_box_id,
receivable_fee,
actual_fee,
admin_id,
ontime,
update_time,
parking_id,
park_name,
area_id,
group_id,
is_fixed,
state,
car_owner_parking_fixed_location_id,
exit_road,
entrance_road,
synid,
free_time,
plate_number,
upload_insert,
upload_update,
remarks,
online_fee,
entrance_car_image_believe,
exit_car_image_believe,
entrance_car_plate_color,
exit_car_plate_color,
operate_user_id,
is_finish,
motor_type,
motor_type_detail,
brand,
STATUS
FROM
parking_record
WHERE
parking_id = ?
AND plate_number = ?
AND STATUS = ?
ORDER BY
entrance_time DESC
LIMIT
? [arguments: (2699, “6M2255”, 1, 1)];
Execution plan after collection:
id task estRows operator info actRows execution info memory disk
Limit_12 root 1 offset:0, count:1 0 time:1m0.2s, loops:1 N/A N/A
└─Projection_37 root 1 parking.parking_record.upload_id, parking.parking_record.record_type, parking.parking_record.card_car_id, parking.parking_record.temp_id, parking.parking_record.entrance_time, parking.parking_record.entrance_car_image, parking.parking_record.entrance_parking_box_id, parking.parking_record.exit_time, parking.parking_record.exit_car_image, parking.parking_record.exit_parking_box_id, parking.parking_record.receivable_fee, parking.parking_record.actual_fee, parking.parking_record.admin_id, parking.parking_record.ontime, parking.parking_record.update_time, parking.parking_record.parking_id, parking.parking_record.park_name, parking.parking_record.area_id, parking.parking_record.group_id, parking.parking_record.is_fixed, parking.parking_record.state, parking.parking_record.car_owner_parking_fixed_location_id, parking.parking_record.exit_road, parking.parking_record.entrance_road, parking.parking_record.synid, parking.parking_record.free_time, parking.parking_record.plate_number, parking.parking_record.upload_insert, parking.parking_record.upload_update, parking.parking_record.remarks, parking.parking_record.online_fee, parking.parking_record.entrance_car_image_believe, parking.parking_record.exit_car_image_believe, parking.parking_record.entrance_car_plate_color, parking.parking_record.exit_car_plate_color, parking.parking_record.operate_user_id, parking.parking_record.is_finish, parking.parking_record.motor_type, parking.parking_record.motor_type_detail, parking.parking_record.brand, parking.parking_record.status 0 time:1m0.2s, loops:1, Concurrency:OFF 20.9 KB N/A
└─IndexLookUp_36 root 1 0 time:1m0.2s, loops:1, index_task: {total_time: 59.9s, fetch_handle: 1.29s, build: 775.5ms, wait: 57.8s}, table_task: {total_time: 4m55.5s, num: 214, concurrency: 5} 29.6 MB N/A
├─IndexRangeScan_33 cop[tikv] 1.25 table:parking_record, index:idx_parkingid_entrancetime(parking_id, entrance_time), range:[2699,2699], keep order:true, desc 4300376 time:1.08s, loops:4042, cop_task: {num: 5, max: 32.7s, min: 946.9µs, avg: 6.88s, p95: 32.7s, max_proc_keys: 1263935, p95_proc_keys: 1263935, tot_proc: 33.9s, tot_wait: 91ms, rpc_num: 6, rpc_time: 34.4s, copr_cache_hit_ratio: 0.40}, ResolveLock:{num_rpc:1, total_time:278.7µs}, backoff{txnLockFast: 8ms}, tikv_task:{proc max:1.41s, min:337ms, p80:1.41s, p95:1.41s, iters:4222, tasks:5}, scan_detail: {total_process_keys: 2850258, total_process_keys_size: 156764190, total_keys: 2850317, rocksdb: {delete_skipped_count: 2, key_skipped_count: 2850317, block: {cache_hit_count: 33, read_count: 2256, read_byte: 45.7 MB}}} N/A N/A
└─Selection_35 cop[tikv] 1 eq(parking.parking_record.plate_number, "6M2255"), eq(parking.parking_record.status, 1) 0 time:4m52.2s, loops:214, cop_task: {num: 130140, max: 1.58s, min: 389.7µs, avg: 85.6ms, p95: 359.5ms, max_proc_keys: 475, p95_proc_keys: 92, tot_proc: 38m18.6s, tot_wait: 2h21m12.8s, rpc_num: 130140, rpc_time: 3h5m37.8s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:140ms, min:0s, p80:16ms, p95:30ms, iters:186185, tasks:130140}, scan_detail: {total_process_keys: 4057141, total_process_keys_size: 2636038582, total_keys: 4152524, rocksdb: {delete_skipped_count: 0, key_skipped_count: 105745, block: {cache_hit_count: 56196063, read_count: 4125712, read_byte: 42.3 GB}}} N/A N/A
└─TableRowIDScan_34 cop[tikv] 1.25 table:parking_record, keep order:false 4057141 tikv_task:{proc max:140ms, min:0s, p80:16ms, p95:30ms, iters:186185, tasks:130140} N/A N/A
Execution plan before collection:
id | estRows | task | access object | operator info |
±---------------------------------±--------±----------±--------------------------------------------------------------------------------±-------------------------------------------------------------+
| TopN_9 | 1.00 | root | | parking.parking_record.entrance_time:desc, offset:0, count:1 |
| └─IndexLookUp_17 | 1.00 | root | | |
| ├─IndexRangeScan_13(Build) | 11.00 | cop[tikv] | table:parking_record, index:idx_parkingid_platenumber(parking_id, plate_number) | range:[2699 “6M2255”,2699 “6M2255”], keep order:false |
| └─TopN_16(Probe) | 1.00 | cop[tikv] | | parking.parking_record.entrance_time:desc, offset:0, count:1 |
| └─Selection_15 | 10.31 | cop[tikv] | | eq(parking.parking_record.status, 1) |
| └─TableRowIDScan_14 | 11.00 | cop[tikv] | table:parking_record | keep order:false |
±---------------------------------±--------±----------±--------------------------------------------------------------------------------±-------------------------------------------------------------+
This table has more than 4 billion records.
|
username: hygame
|
username: 
|
username: