SELECT ri.id, ri.report_id, ri.reason, ri.topic, ri.online_at
, ri.offline_at, ri.online_time, ri.gmt_create, ri.gmt_modified
FROM report_info ri
LEFT JOIN report r ON r.id = ri.report_id
WHERE r.device_name = ？
ORDER BY ri.id DESC
LIMIT 1
uj5u.com熱心網友回復：
想根據device_name獲得report_info表里offline_at時間為最新的一條記錄uj5u.com熱心網友回復：
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for report-- ----------------------------DROP TABLE IF EXISTS `report`;CREATE TABLE `report` ( `id` int(11) NOT NULL AUTO_INCREMENT, `event` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `product_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `device_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `report_date` date NOT NULL, `gmt_create` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `gmt_modified` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_default`(`product_id`, `device_name`, `report_date`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 35630 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;
-- ------------------------------ Table structure for report_info-- ----------------------------DROP TABLE IF EXISTS `report_info`;CREATE TABLE `report_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `report_id` int(11) NOT NULL, `reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `topic` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `online_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `offline_at` timestamp(0) NULL DEFAULT NULL, `online_time` int(11) NULL DEFAULT NULL, `gmt_create` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `gmt_modified` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_default`(`offline_at`, `online_time`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 161001 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
uj5u.com熱心網友回復：
現在并發高越高的情況下耗時就越久，30個并發就走了5秒了uj5u.com熱心網友回復：
select ri.* from report_info ri inner join(
select id from report where device_name = ?
) r on ri.report_id=r.id
order by ri.id desc limit 1
uj5u.com熱心網友回復：
你的目的是：根據device_name獲得report_info表里offline_at時間為最新的一條記錄。 如果不差分sql陳述句的話，把deviceName和reportId加索引，查詢的資訊只查詢自己用到的。但是我不建議這么做，我建議，你把整個sql用業務拆分下，你先單獨通過查詢條件device_name查詢A表，拿到reportId，之后通過reportid查詢表，拿到最新資料。分兩步走，在這兩步中，如果業務量很大，可以加入快取等技術uj5u.com熱心網友回復：
如果可以的話，這倆表資料落庫的時候，也順便往redis快取一下最新資料uj5u.com熱心網友回復：
explain sql 先查查原因轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/232694.html
標籤：Java EE
下一篇：redis分布式鎖加鎖失敗