RError.com

RError.com Logo RError.com Logo

RError.com Navigation

  • 主页

Mobile menu

Close
  • 主页
  • 系统&网络
    • 热门问题
    • 最新问题
    • 标签
  • Ubuntu
    • 热门问题
    • 最新问题
    • 标签
  • 帮助
主页 / 问题 / 1557794
Accepted
Дима Й
Дима Й
Asked:2023-12-18 06:58:11 +0000 UTC2023-12-18 06:58:11 +0000 UTC 2023-12-18 06:58:11 +0000 UTC

优化选择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 个回答
  • 75 Views

1 个回答

  • Voted
  1. Best Answer
    Дима Й
    2023-12-22T19:10:08Z2023-12-22T19:10:08Z

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

    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

相关问题

  • MySQL - 将一列中单元格的每个值与另一列中的值匹配,并将其替换为相邻列中的值

  • 帮助编写 MySQL 查询

  • SELECT 只增加 MYSQL 值

  • 创建 MySQL 表时的字段

  • 如何连接到远程mysql数据库?

  • MySQL单元格中的随机值

Sidebar

Stats

  • 问题 10021
  • Answers 30001
  • 最佳答案 8000
  • 用户 6900
  • 常问
  • 回答
  • Marko Smith

    我看不懂措辞

    • 1 个回答
  • Marko Smith

    请求的模块“del”不提供名为“default”的导出

    • 3 个回答
  • Marko Smith

    "!+tab" 在 HTML 的 vs 代码中不起作用

    • 5 个回答
  • Marko Smith

    我正在尝试解决“猜词”的问题。Python

    • 2 个回答
  • Marko Smith

    可以使用哪些命令将当前指针移动到指定的提交而不更改工作目录中的文件?

    • 1 个回答
  • Marko Smith

    Python解析野莓

    • 1 个回答
  • Marko Smith

    问题:“警告:检查最新版本的 pip 时出错。”

    • 2 个回答
  • Marko Smith

    帮助编写一个用值填充变量的循环。解决这个问题

    • 2 个回答
  • Marko Smith

    尽管依赖数组为空,但在渲染上调用了 2 次 useEffect

    • 2 个回答
  • Marko Smith

    数据不通过 Telegram.WebApp.sendData 发送

    • 1 个回答
  • Martin Hope
    Alexandr_TT 2020年新年大赛! 2020-12-20 18:20:21 +0000 UTC
  • Martin Hope
    Alexandr_TT 圣诞树动画 2020-12-23 00:38:08 +0000 UTC
  • Martin Hope
    Air 究竟是什么标识了网站访问者? 2020-11-03 15:49:20 +0000 UTC
  • Martin Hope
    Qwertiy 号码显示 9223372036854775807 2020-07-11 18:16:49 +0000 UTC
  • Martin Hope
    user216109 如何为黑客设下陷阱,或充分击退攻击? 2020-05-10 02:22:52 +0000 UTC
  • Martin Hope
    Qwertiy 并变成3个无穷大 2020-11-06 07:15:57 +0000 UTC
  • Martin Hope
    koks_rs 什么是样板代码? 2020-10-27 15:43:19 +0000 UTC
  • Martin Hope
    Sirop4ik 向 git 提交发布的正确方法是什么? 2020-10-05 00:02:00 +0000 UTC
  • Martin Hope
    faoxis 为什么在这么多示例中函数都称为 foo? 2020-08-15 04:42:49 +0000 UTC
  • Martin Hope
    Pavel Mayorov 如何从事件或回调函数中返回值?或者至少等他们完成。 2020-08-11 16:49:28 +0000 UTC

热门标签

javascript python java php c# c++ html android jquery mysql

Explore

  • 主页
  • 问题
    • 热门问题
    • 最新问题
  • 标签
  • 帮助

Footer

RError.com

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

帮助

© 2023 RError.com All Rights Reserve   沪ICP备12040472号-5