主页 / 问题 / 1557794
Accepted
Дима Й
Asked:2023-12-18 06:58:11 +0800 CST2023-12-18 06:58:11 +0800 CST

优化选择mysql

  • 772

有3张桌子。数据、调制解调器信息、数据响应。
data - 来自调制解调器的所有常规每分钟信息 +100 线/分钟（到目前为止总共约 150 万条），
modemsInfo - 有关调制解调器的信息、谁拥有它们等（目前约 10 条。超过 3000 条正在发布）未计划），
dataResponse - 调制解调器响应。本质上与data相同，但只有几行（到目前为止大约 10 行），
每 5 秒我收集一次所有所有者调制解调器的最新信息（下文中在idCompany代码中）。在某一时刻，请求时间花费了6秒，并且尚未传输任何数据和数据响应信息的调制解调器越多，采样时间就越差。+1与一个新的调制解调器。我使用各种文章和答案对其进行了修改和优化。变成了1.4秒。容易一点，但在我看来，这需要很长时间。请告诉我，谁不懒得鼓捣这个，还能做什么？它们通过serialNum和typeReceipt
相互连接，与**modeDialog** 填充数据表 相同：

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227124, '9faec1317eb285f9f20254b6d2e20670', 111111111111, 0, 83206, 76105, 10553, 25226, 80, 0, 411, 74, 22, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227122, '931a1e61b06b2fbcbdea07f6d8fe59ed', 111111111111, 0, 12809, 99159, 42986, 1461, 75, 0, 575, 838, 42, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227121, '393fd8d56d1338b6e5742a95bdd17a58', 822917000001, 0, 24478, 14257, 73864, 43259, 34, 31, 673, 936, 45, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227120, '6592908f518c9b43c0dfc6bcc798bfbd', 111111111111, 0, 55502, 40960, 80864, 26944, 81, 63, 876, 152, 80, 0, 0, 1, 0, 0, 0, 0, 1370562830, 0, '2023-12-17 14:00:10');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227119, 'b3f8f973c5f3ccabfc5f58be74249c9b', 822125000000, 0, 18944, 89632, 75158, 46181, 58, 87, 812, 368, 65, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:10');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227118, '08d986357608dd939184cfbdc1041a81', 822125000000, 0, 13852, 61660, 99013, 51948, 77, 78, 848, 558, 35, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:10');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227117, 'eaf4a664592b29aba94f5aefe4519c6e', 822917000001, 0, 19293, 14457, 81638, 5465, 26, 3, 818, 951, 95, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:10');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227116, 'e54394b113c5d91c77eb068772c234e7', 822917000001, 0, 56479, 56457, 32901, 75286, 69, 75, 456, 205, 38, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227115, 'a410df8e915d78d4e65020e16a9061e7', 111111111111, 0, 25941, 33667, 78140, 83016, 36, 83, 67, 314, 14, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227111, '43a8f3d4dbcf7af684d7ec31183f7c2c', 111111111111, 0, 28033, 69911, 41437, 97644, 20, 42, 742, 384, 88, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227110, '1e084b1a8028754ef6a6a6113f686638', 822917000001, 0, 58290, 28145, 8922, 54037, 12, 53, 135, 951, 29, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227109, 'bd5ab82e1aee708f560805983724568e', 822125000000, 0, 70906, 3568, 53202, 41062, 28, 28, 914, 527, 53, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227108, 'ea2247e979a8062840c650c0f5254036', 111111111111, 0, 81361, 46912, 87251, 11789, 9, 89, 452, 971, 75, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:09');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227104, '9642c393186c731193c51828490d76d3', 111111111111, 0, 94653, 222, 37479, 78288, 78, 89, 134, 96, 79, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227103, '7d5780e49716d92c33c2e6f500021026', 111111111111, 0, 76185, 85751, 46847, 66784, 97, 66, 725, 19, 59, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227102, 'dac779c303bcf599174685a4d629b657', 822125000000, 0, 8978, 23016, 82799, 36104, 66, 64, 550, 817, 26, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227101, 'f0db8b14b9357a5d0db62110939994e3', 822125000000, 0, 39960, 69244, 26330, 24935, 38, 43, 197, 554, 13, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227100, '8c9712df7f264efeb934753d64cbe232', 822917000001, 0, 78323, 9031, 9834, 23217, 94, 45, 461, 856, 58, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227099, '05c9d72b9f7dc9995c1c5d2981b1fb9d', 822125000000, 0, 25667, 74187, 25871, 42459, 59, 83, 858, 455, 45, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227098, 'adb125ba523e789c43b280bf23572ac4', 822917000001, 0, 42359, 72061, 36836, 29225, 40, 68, 677, 695, 40, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227097, 'f6c062d3b84362ec93a509a44f199f0f', 111111111111, 0, 40311, 80755, 36442, 33862, 6, 44, 452, 746, 52, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227096, '8b4c70bb37860e457170c7d57eb64b99', 822125000000, 0, 6601, 98527, 1455, 11081, 30, 57, 986, 818, 89, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227095, 'b6eef858ec15886c113510c970f64e23', 822917000001, 0, 99418, 31939, 52525, 90310, 80, 40, 970, 955, 20, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:08');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227090, 'b8c3812632b8582d874a2e89546e07d7', 822917000001, 0, 64064, 40638, 17418, 24982, 77, 30, 541, 585, 38, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227089, '26532d9cb67ded61bad390675ea4f552', 822917000001, 0, 22571, 41643, 52396, 62944, 54, 73, 448, 93, 22, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');


INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227082, '268405297d3e3fcccb2a17c0d3173dd0', 111111111111, 0, 73953, 44671, 71077, 64229, 39, 23, 358, 474, 83, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227081, '405d9b0bf00bd68fa256b094843dcee6', 111111111111, 0, 63800, 68218, 58017, 81928, 17, 79, 919, 195, 45, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227080, '81786eb3a525e88f8251e760f2bdaa0b', 822125000000, 0, 39770, 10714, 39325, 61061, 62, 69, 656, 316, 91, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227079, '8893945f6a00932f278011122e44558b', 822917000001, 0, 90452, 70936, 44922, 51558, 55, 95, 63, 50, 98, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');

INSERT INTO u2378356_ledMonitor.data (id, md5receipt, serialNum, mode, Ub, Ib, Up, IL, Ta, Tc, tL, tN, PL, Hand, Fire, Night, `Load`, Disch, OverIL, ShortL, ip, typeReceipt, timeReceipt) VALUES (1227078, 'b8f9b8c17c51ef0dca852868b144a67b', 111111111111, 0, 22306, 56306, 26453, 36391, 63, 32, 623, 93, 90, 0, 0, 1, 0, 0, 0, 0, 1370562830, 1, '2023-12-17 14:00:07');

填充dataResponse表：

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('1', null, '822917000001', 11212, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('2', null, '822917000000', 1212, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('3', null, '822917000001', null, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('4', null, '822125000000', 11313, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('5', null, '822125000000', 4114, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('6', null, '822917000001', 1212, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('9', null, '822917000001', 1212, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('10', null, '822917000001', null, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('11', null, '822917000001', 1212, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('12', null, '822917000001', 241515, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('13', null, '822917000000', 1535132, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('14', null, '822917000001', 4361, '2', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('15', null, '822125000000', 346, '1', null, null);

INSERT INTO u2378356_ledMonitor.dataResponse (md5receipt, serialNum, text, ip, commandResponse, timeReceipt,
                                          typeReceipt)
VALUES ('16', null, '822125000000', 13464, '2', null, null);

填充modemsInfo表：

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (21, 822917000001, 1, '1', '79803400001', 1, '?', null, null, null, null, null, null, '2023-12-17 16:02:58',
    '2023-12-17 16:02:58', 0, 0, 0, null, null, '?', '01', '00:00:00', 000001);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (22, 822125000000, 1, '2', '79803400000', 1, '?', null, null, null, null, null, null, '2023-12-17 16:04:08',
    '2023-12-17 16:04:08', 0, 0, 0, null, null, '?', '01', '00:00:00', 000000);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (44, 111111111111, 1, '3', '79802410000', 1, '?', null, null, null, null, null, null, '2023-12-17 21:21:57',
    '2023-12-17 21:20:37', 0, 0, 0, null, null, '?', '01', '00:00:00', 111111);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (45, null, 1, '4', '71112111111', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:16:13', 0, 0,
    0, null, null, '?', '01', '00:00:00', 112211);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (46, null, 1, '5', '73333333333', 0, '?', null, null, null, null, null, null, null, '2023-12-17 22:16:39', 0, 0,
    0, null, null, '?', '01', '00:00:00', 333333);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (47, null, 3, '6', '71333333333', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:16:57', 0, 0,
    0, null, null, '?', '01', '00:00:00', 121312);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (49, null, 1, '8', '73332223333', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:17:50', 16, 0,
    0, null, null, '?', '01', '00:00:00', 212211);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (50, null, 4, '9', '79802412222', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:18:14', 17, 0,
    0, null, null, '?', '01', '00:00:00', 112312);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (51, null, 1, '10', '71231231331', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:18:29', 24,
    0, 0, null, null, '?', '01', '00:00:00', 312312);

INSERT INTO u2378356_ledMonitor.modemsInfo (id, serialNum, idCompany, nameModem, phoneNumber, modeDialog,
                                        commandStatusGPRS, region, city, street, structure, latitude, longitude,
                                        timeActivation, timeRegister, idGroup, idIcon, model, keyDelete,
                                        endTimeKeyDelete, commandStatusSMS, billingPeriodDay, billingPeriodTime,
                                        identifySerialNum)
VALUES (52, null, 1, '11', '71231333331', 1, '?', null, null, null, null, null, null, null, '2023-12-17 22:18:50', 0, 0,
    0, null, null, '?', '01', '00:00:00', 123123);

采样结果：
在此输入图像描述

表格：

 create table if not exists data
(
id          bigint unsigned auto_increment
    primary key,
md5receipt  char(32)                            not null,
serialNum   bigint unsigned                     not null,
mode        tinyint unsigned                    not null,
Ub          mediumint unsigned                  not null,
Ib          mediumint unsigned                  not null,
Up          mediumint unsigned                  not null,
IL          mediumint unsigned                  not null,
Ta          tinyint                             not null,
Tc          tinyint                             not null,
tL          smallint unsigned                   not null,
tN          smallint unsigned                   not null,
PL          tinyint unsigned                    not null,
Hand        tinyint(1)                          not null,
Fire        tinyint(1)                          not null,
Night       tinyint(1)                          not null,
`Load`      tinyint(1)                          not null,
Disch       tinyint(1)                          not null,
OverIL      tinyint(1)                          not null,
ShortL      tinyint(1)                          not null,
ip          int unsigned                        not null,
typeReceipt tinyint(1)                          not null ,
timeReceipt timestamp default CURRENT_TIMESTAMP not null,
constraint md5receipt
    unique (md5receipt)
);

create index DsNtR
    on data (serialNum, typeReceipt);


create table if not exists dataResponse
(
id              bigint unsigned auto_increment
    primary key,
md5receipt      char(32)                            not null,
serialNum       bigint unsigned                     not null,
text            tinytext                            not null,
ip              int unsigned                        not null,
commandResponse char                                not null,
timeReceipt     timestamp default CURRENT_TIMESTAMP not null,
typeReceipt     tinyint(1)                          not null,
constraint md5receipt
    unique (md5receipt)
)
comment 'ответы устройств';

create index DRsNtR
    on dataResponse (serialNum, typeReceipt);

create table if not exists modemsInfo
(
id                bigint unsigned auto_increment
    primary key,
serialNum         bigint(12) unsigned zerofill               null,
idCompany         bigint unsigned                            not null,
nameModem         varchar(20)                                not null,
phoneNumber       char(11)                                   not null,
modeDialog        tinyint(1)       default 1                 not null,
commandStatusGPRS char             default '?'               not null,
region            tinytext                                   null,
city              tinytext                                   null,
street            tinytext                                   null,
structure         tinytext                                   null,
latitude          char(11)                                   null,
longitude         char(11)                                   null,
timeActivation    timestamp                                  null,
timeRegister      timestamp        default CURRENT_TIMESTAMP not null,
idGroup           bigint unsigned  default 0                 not null,
idIcon            tinyint unsigned default 0                 not null,
model             tinyint(1)                                 not null,
keyDelete         char(32)                                   null,
endTimeKeyDelete  timestamp                                  null,
commandStatusSMS  char             default '?'               not null,
billingPeriodDay  char(2)          default '01'              not null,
billingPeriodTime time             default '00:00:00'        not null,
identifySerialNum mediumint(6) unsigned zerofill             null,
constraint serialNum
    unique (serialNum)
);

create index MIsNmD
on modemsInfo (serialNum, modeDialog);

create index idCompany_2
on modemsInfo (idCompany);

create index idGroup
on modemsInfo (idGroup);

解释请求。我并不完全理解他给出的表格的所有复杂性。根据作者的说法，有很多值都是不好的（使用 where；使用 filesort、100% 过滤等......）：
在此输入图像描述

以及请求本身。

SELECT mI.idGroup, mI.id as idModem , mI.commandStatusGPRS, 
    mI.nameModem, mI.commandStatusSMS,
    mI.phoneNumber, mI.region, mI.city ,mI.street, mI.serialNum,
    mI.timeActivation as timeActivation, mI.structure, mI.model, 
    mI.modeDialog,d.timeReceipt as timeReceiptData , 
    dR.timeReceipt as timeReceiptResponse , d.Hand, d.PL, 
     d.`Load`, d.Mode, d.Disch, d.OverIL, d.ShortL, d.typeReceipt
FROM modemsInfo mI
left outer join data d on d.id = (SELECT MAX(da.id)
                                  FROM data da
                                  WHERE mI.serialNum = da.serialNum 
                                  AND mI.modeDialog=da.typeReceipt)
left outer join dataResponse dR on dR.id = (SELECT MAX(dR.id)
                                            FROM dataResponse dR
                                            WHERE mI.serialNum = 
                                                  dR.serialNum 
                                            AND mI.modeDialog = 
                                                dR.typeReceipt)
WHERE mI.idCompany = :idCompany
order by mI.idGroup DESC

表结构、文本查询、创建表的文本查询以及任何其他信息（如果需要）。

mysql

1 个回答

  1. Best Answer

    我使用评论中的模板提出了此请求。

    SELECT  mI.idGroup, mI.id as idModem , mI.commandStatusGPRS, mI.nameModem, mI.commandStatusSMS,
                    mI.phoneNumber, mI.region, mI.city ,mI.street, mI.serialNum,
                    mI.timeActivation as timeActivation, mI.structure, mI.model, mI.modeDialog,
                    d.timeReceipt as timeReceiptData , dR.timeReceipt as timeReceiptResponse , d.Hand, d.PL, d.`Load`, d.Mode, d.Disch,
                    d.OverIL, d.ShortL, d.typeReceipt
            FROM modemsInfo mI
            LEFT JOIN (data d  JOIN (SELECT MAX(id) as id, serialNum, typeReceipt FROM data d2 GROUP BY serialNum, typeReceipt) t21 ON d.id = t21.id)
                ON mI.serialNum = d.serialNum  AND mI.modeDialog = d.typeReceipt
            LEFT JOIN (dataResponse dR  JOIN (SELECT MAX(id) as id,serialNum, typeReceipt FROM dataResponse dR2 GROUP BY serialNum, typeReceipt) t31 ON dR.id = t31.id)
                ON mI.serialNum = dR.serialNum  AND mI.modeDialog = dR.typeReceipt
            WHERE mI.idCompany = :idCompany
            order by mI.idGroup DESC

    执行速度从 1.4s 降低至 ~0.0016s
    模板：
    SELECT .. FROM t1 LEFT JOIN (t2 JOIN (SELECT MAX(id) FROM t2 GROUP BY .. ) t21) ON .. LEFT JOIN (t3 JOIN (SELECT MAX(id) FROM t3 GROUP BY .. ) t31) ON ..

    • 0

相关问题