CREATE TABLE parking_record (
id bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ‘自增ID’,
upload_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘岗亭上传的id’,
record_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘停车记录类型76系统77临停’,
card_car_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘卡id’,
temp_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘临时ID’,
entrance_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘入场时间’,
entrance_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘入场图片’,
entrance_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场岗亭id’,
exit_time datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘出场时间’,
exit_car_image varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘出场图片’,
exit_parking_box_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场岗亭id’,
receivable_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘应收金额’,
actual_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实收金额’,
admin_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘管理员ID’,
ontime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘停车记录岗亭创建时间’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘停车记录岗亭更新时间’,
parking_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘车场ID’,
park_name varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘车场名称’,
area_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘区域ID’,
group_id varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘集团ID’,
is_fixed int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘449=特殊放行’,
state int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘111含义不明,449=特殊放行,452=特殊金额放行，0=默认，其他=cartypeid’,
car_owner_parking_fixed_location_id int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘长租费率ID’,
exit_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场路口’,
entrance_road int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场路口’,
synid varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘主键synid’,
free_time int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘免费时长’,
plate_number varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘车牌号’,
upload_insert datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘云端创建时间’,
upload_update datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘云端更新时间’,
remarks varchar(500) NOT NULL DEFAULT ‘’ COMMENT ‘备注’,
online_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘电子支付金额’,
entrance_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场置信度’,
exit_car_image_believe int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场置信度’,
entrance_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘入场车牌颜色 0-无 1-蓝 2-黑 3-黄 4-白 5-绿’,
exit_car_plate_color int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘出场车牌颜色’,
operate_user_id int(11) NOT NULL DEFAULT ‘-1’,
is_finish int(11) NOT NULL DEFAULT ‘-1’ COMMENT ‘是否终结0为未终结1为终结’,
motor_type tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘0-非机动车，1-机动车 停车记录表’,
motor_type_detail tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘机动车详细类型’,
brand varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘汽车品牌’,
character_code varchar(250) NOT NULL DEFAULT ‘’ COMMENT ‘特征码’,
status tinyint(4) NOT NULL DEFAULT ‘1’ COMMENT ‘状态（1：有效；2：无效）’,
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=‘停车记录’
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)];
收集后执行计划：
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
收集前执行计划：
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 |
±---------------------------------±--------±----------±--------------------------------------------------------------------------------±-------------------------------------------------------------+
这个表有40亿+的数据量