IFS_OLD=$IFS IFS=$'\n' for i in `cat /tmp/select_arri.log `;doecho${i#*'from'}; done | less IFS=$IFS_OLD
arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=17 and arrivalrec0_.station_no='56742' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='S7100' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='V4631' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='S9466' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='V4205' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='V4105' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='V4506' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='V4617' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='S8356' arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='S8356'
select 该表 where 条件中有 product_id,station_no,sequence 字段,可以使用到复合索引 IXFK_arrival_record 的前三个字段。
du -sh /datas/mysql/data/3316/cq_new_cimiss/arrival_record* 12K /datas/mysql/data/3316/cq_new_cimiss/arrival_record.frm 48G /datas/mysql/data/3316/cq_new_cimiss/arrival_record.ibd
selectcount() from cq_new_cimiss.arrival_record; +-----------+ |count() | +-----------+ |112294946| +-----------+ 1亿多记录数
SELECT table_name, CONCAT(FORMAT(SUM(data_length) /1024/1024,2),'M') AS dbdata_size, CONCAT(FORMAT(SUM(index_length) /1024/1024,2),'M') AS dbindex_size, CONCAT(FORMAT(SUM(data_length + index_length) /1024/1024/1024,2),'G') AS table_size(G), AVG_ROW_LENGTH,table_rows,update_time FROM information_schema.tables WHERE table_schema ='cq_new_cimiss'and table_name='arrival_record';
2019-04-08-11:17:36 stop slave mysql: [Warning] Using a password on the command line interface can be insecure. ddl_start 2019-04-08 11:17:36 ddl_stop 2019-04-08 11:45:13 2019-04-08-11:45:13 onlie ddl stop 2019-04-08-11:45:13 add foreign key mysql: [Warning] Using a password on the command line interface can be insecure. 2019-04-08-12:33:48 add foreign key stop 2019-04-08-12:33:48 start slave
explain select count(*) from arrival_record where receive_time < STR_TO_DATE('2019-03-10', '%Y-%m-%d')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: arrival_record partitions: NULL type: range possible_keys: idx_receive_time key: idx_receive_time key_len: 6 ref: NULL rows: 7540948 filtered: 100.00 Extra: Using where; Using index
explain select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00' and receive_spend_ms>=0\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: arrival_record partitions: NULL type: range possible_keys: idx_product_id_sequence_station_no,idx_receive_time key: idx_receive_time key_len: 6 ref: NULL rows: 291448 filtered: 16.66 Extra: Using index condition; Using where
都使用到了 idx_receive_time 索引,扫描的行数大大降低。
索引优化后
delete 还是花费了 77s 时间:
1
deletefrom arrival_record where receive_time < STR_TO_DATE('2019-03-10', '%Y-%m-%d')\G