insert 执行耗时太长 —— 没用的索引太多了，写数据的同时，还要写index #291

New Issue

2025-12-27T21:22:19+08:00

bigtran commented

2025-12-27 21:22:19 +08:00

清空记录数，果断时间再继续看

``` insert into opm_mw_info_data(organ_name,sort_name,dept_name,collection_name,recl_name,recl_idcard,recl_time,hand_name,hand_idcard,hand_time,weight,ribbon_code,waste_type,waste_status,scale,created_at,is_push) values('巩留县明奎中医医院','本院','康复科','康复科','沙哈依',' ','2025-12-24 19:00:54','衡永生',' ','2025-12-24 19:00:54','0.16','3136544011644502','感染性废物','收集','s00000a7bo','2025-12-24','1');

insert into opm_mw_raw_data(organ_id,device_id,type,code,url,link,created_at,depart) values('140','2730','0000','MssOrange','c=api&a=MssOrange&hosp=11314042730&time=20251223165115&code=314273050165045346%20A&type=01&weig=6500&info=9679121-314273050166045345%20A-8-34100&depa=t9190000113395&hand=r160114448&rece=r170011748&acts=0000','收集','2025-12-23 16:51:15','');


# 清空记录数，果断时间再继续看

TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'opm_mw_medical_waste' and (OBJECT_NAME='opm_mw_info_data' or OBJECT_NAME='opm_mw_raw_data')
order by OBJECT_NAME,COUNT_FETCH DESC;

# 清空记录数，果断时间再继续看 <img width="1302" alt="1283462329b2406b9ce2d5f572694dbb.png" src="attachments/00409222-d36d-4b2e-9321-f70c05f975c8"> ``` insert into opm_mw_info_data(organ_name,sort_name,dept_name,collection_name,recl_name,recl_idcard,recl_time,hand_name,hand_idcard,hand_time,weight,ribbon_code,waste_type,waste_status,scale,created_at,is_push) values('巩留县明奎中医医院','本院','康复科','康复科','沙哈依',' ','2025-12-24 19:00:54','衡永生',' ','2025-12-24 19:00:54','0.16','3136544011644502','感染性废物','收集','s00000a7bo','2025-12-24','1'); insert into opm_mw_raw_data(organ_id,device_id,type,code,url,link,created_at,depart) values('140','2730','0000','MssOrange','c=api&a=MssOrange&hosp=11314042730&time=20251223165115&code=314273050165045346%20A&type=01&weig=6500&info=9679121-314273050166045345%20A-8-34100&depa=t9190000113395&hand=r160114448&rece=r170011748&acts=0000','收集','2025-12-23 16:51:15',''); ``` # 清空记录数，果断时间再继续看 ``` TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage; SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA = 'opm_mw_medical_waste' and (OBJECT_NAME='opm_mw_info_data' or OBJECT_NAME='opm_mw_raw_data') order by OBJECT_NAME,COUNT_FETCH DESC; ```

image.png

415 KiB

1283462329b2406b9ce2d5f572694dbb.png

318 KiB

bigtran commented

2025-12-27 21:22:53 +08:00

查看哪些索引没用过，然后把这些索引删除掉

SELECT @@global.performance_schema; 
SELECT @@global.setup_instruments;

SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'statement/%'
   OR NAME LIKE 'wait/io/table%';
	 
-- 官方 MySQL 看“索引使用次数”
SELECT OBJECT_SCHEMA,
       OBJECT_NAME,
       INDEX_NAME,
       COUNT_FETCH,
       COUNT_INSERT,
       COUNT_UPDATE,
       COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'opm_mw_medical_waste'
  AND COUNT_FETCH = 0
  AND COUNT_INSERT = 0
  AND COUNT_UPDATE = 0
  AND COUNT_DELETE = 0;

# 查看哪些索引没用过，然后把这些索引删除掉 ``` SELECT @@global.performance_schema; SELECT @@global.setup_instruments; SELECT NAME, ENABLED, TIMED FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/%' OR NAME LIKE 'wait/io/table%'; -- 官方 MySQL 看“索引使用次数” SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA = 'opm_mw_medical_waste' AND COUNT_FETCH = 0 AND COUNT_INSERT = 0 AND COUNT_UPDATE = 0 AND COUNT_DELETE = 0; ```

bigtran commented

2025-12-27 21:28:47 +08:00

像是没有用的索引

# 像是没有用的索引 ![image.png](/attachments/6b929ce8-16c0-4809-9b78-3e7cae087ac6) ![image.png](/attachments/9a30d0d6-7f37-421d-a087-4f5f9c4d01e3) ![image.png](/attachments/79c6c89d-da42-4512-8a25-6f9602e41e55)

image.png

117 KiB

image.png

192 KiB

image.png

235 KiB

bigtran added the 6-now-commit-to-fix label 2025-12-27 21:28:53 +08:00

Sign in to join this conversation.