更新:我已经删除了重复的连接，这将时间减少到18秒。其他改进也将受到赞赏!
我觉得我有一些巨大的疏忽，导致它花费的时间比我在数据库中拥有的极少数项目的时间要长得多。现在，我的数据库中只有大约85个实体，但是运行Java程序需要51秒。对于40个实体，花了35秒，所以很明显我做错了什么。该数据库最终将包含数百万个实体，这将花费数天时间来完成一次运行。有人能告诉我应该做什么优化吗?请注意，当涉及到MySQL，特别是Java MySQL实现时，我是一个完全的初学者，所以请对我宽容。
问题代码:
public static void SQL() {
int handleqty = -1;
String url = "jdbc:mysql://MYURL:3306/MYDB";
String username = "username";
String password = "password";
try{
Connection conn1 = DriverManager.getConnection(url, username, password);
for(Entry<String, String> entry: passToSQL.entrySet()) {
//System.out.println(entry);
String cardinfo = entry.getValue();
String Handle = cardinfo.split(",")[0];
String Title = cardinfo.split(",")[1];
String Vendor = cardinfo.split(",")[2];
String Tags = cardinfo.split(",")[3];
String Published = cardinfo.split(",")[4];
String Option1Name = cardinfo.split(",")[5];
String Option1Value = cardinfo.split(",")[6];
int VariantGrams = Integer.parseInt(cardinfo.split(",")[7]);
String VariantInventoryTracker = cardinfo.split(",")[8];
String VariantInventoryQty = cardinfo.split(",")[9];
String VariantInventoryPolicy = cardinfo.split(",")[10];
String VariantFufillmentService = cardinfo.split(",")[11];
float VariantPrice = Float.parseFloat(cardinfo.split(",")[12]);
String VariantRequiresShipping = cardinfo.split(",")[13];
String VariantTaxable = cardinfo.split(",")[14];
String ImgSrcfront = cardinfo.split(",")[15];
String ImgSrcback = cardinfo.split(",")[16];
String GiftCard = cardinfo.split(",")[17];
String Status = cardinfo.split(",")[18];
String Description = cardinfo.split(",")[19];
String checkexists = "SELECT EXISTS(SELECT 1 FROM MasterCardRecord WHERE Handle = ? LIMIT 1)";
PreparedStatement statement1 = conn1.prepareStatement(checkexists);
statement1.setString(1, Handle);
ResultSet exists = statement1.executeQuery();
exists.next();
//System.out.println(exists.getString(1));
boolean handleexists = false;
if(exists.getString(1).equals("1")) {
handleexists = true;
//REMOVED: Connection conn2 = DriverManager.getConnection(url, username, password);
String sql2 = "SELECT `Variant Inventory Qty` FROM MasterCardRecord WHERE Handle = ? LIMIT 1";
PreparedStatement getqty = conn1.prepareStatement(sql2);
getqty.setString(1, Handle);
ResultSet handleqtyrs = getqty.executeQuery();
handleqtyrs.next();
handleqty = handleqtyrs.getInt(1);
}
if(exists.getString(1).equals("0")) {
handleexists = false;
}
statement1.close();
// REMOVED: conn1.close();
//NEW CODE --------------------------------------------------------------------------------
if (handleexists == true) {
//REMOVED: Connection connInvUpdate= DriverManager.getConnection(url, username, password);
String InvUpdate = "INSERT INTO InventoryUpdate (Handle, `Variant Inventory Qty`) VALUES (?,?) ON DUPLICATE KEY UPDATE `Variant Inventory Qty` = ?";
PreparedStatement InvStatement = conn1.prepareStatement(InvUpdate);
InvStatement.setString(1,Handle);
InvStatement.setInt(2, handleqty + 1);
InvStatement.setInt(3, handleqty + 1);
int updatedInv = InvStatement.executeUpdate();
//REMOVED: connInvUpdate.close();
InvStatement.close();
}
//-----------------------------------------------------------------------------------------
if(handleexists == false) {
// Handle doesnt exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "INSERT INTO MasterCardRecord (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement statement2 = conn1.prepareStatement(sql);
statement2.setString(1, Handle);
statement2.setString(2, Title);
statement2.setString(3, Vendor);
statement2.setString(4, Tags);
statement2.setString(5, Published);
statement2.setString(6, Option1Name);
statement2.setString(7, Option1Value);
statement2.setInt(8, VariantGrams);
statement2.setString(9, VariantInventoryTracker);
statement2.setString(10, VariantInventoryQty);
statement2.setString(11, VariantInventoryPolicy);
statement2.setString(12, VariantFufillmentService);
statement2.setFloat(13, VariantPrice);
statement2.setString(14, VariantRequiresShipping);
statement2.setString(15, VariantTaxable);
statement2.setString(16, ImgSrcfront);
statement2.setString(17, ImgSrcback);
statement2.setString(18, GiftCard);
statement2.setString(19, Status);
statement2.setString(20, Description);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Item Added: " + Handle);
}
statement2.close();
//REMOVED: conn3.close();
// Put into AddNewCards as well, for export and adding ONLY those cards that dont exist yet.
Connection AddNewCardConn = DriverManager.getConnection(url, username, password);
String AddNewCardSQL = "INSERT INTO AddNewCards (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement AddNewCardStatement = conn1.prepareStatement(AddNewCardSQL);
AddNewCardStatement.setString(1, Handle);
AddNewCardStatement.setString(2, Title);
AddNewCardStatement.setString(3, Vendor);
AddNewCardStatement.setString(4, Tags);
AddNewCardStatement.setString(5, Published);
AddNewCardStatement.setString(6, Option1Name);
AddNewCardStatement.setString(7, Option1Value);
AddNewCardStatement.setInt(8, VariantGrams);
AddNewCardStatement.setString(9, VariantInventoryTracker);
AddNewCardStatement.setString(10, VariantInventoryQty);
AddNewCardStatement.setString(11, VariantInventoryPolicy);
AddNewCardStatement.setString(12, VariantFufillmentService);
AddNewCardStatement.setFloat(13, VariantPrice);
AddNewCardStatement.setString(14, VariantRequiresShipping);
AddNewCardStatement.setString(15, VariantTaxable);
AddNewCardStatement.setString(16, ImgSrcfront);
AddNewCardStatement.setString(17, ImgSrcback);
AddNewCardStatement.setString(18, GiftCard);
AddNewCardStatement.setString(19, Status);
AddNewCardStatement.setString(20, Description);
int AddCardRows = AddNewCardStatement.executeUpdate();
AddNewCardStatement.close();
//REMOVED: AddNewCardConn.close();
}
if (handleexists == true) {
// Handle DOES exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "UPDATE MasterCardRecord SET `Variant Inventory Qty` = ? WHERE Handle = ?";
PreparedStatement statement2 = conn3.prepareStatement(sql);
statement2.setInt(1,handleqty + 1);
statement2.setString(2, Handle);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Inventory of: " + Handle + "has been updated from " + handleqty + " to %d".formatted(handleqty + 1));
}
statement2.close();
//REMOVED: conn3.close();
}
}
}
catch(Exception e){
System.out.println("Database connection failed!n" + e);
}
}
//ADDED:
conn1.close();
}
这段代码的目的是从CSV文件中收集映射中的一些数据，然后遍历该映射并将数据输入3个不同的表。
- MasterCardRecord:该表保存每张卡及其总数量和其他值
- AddNewCards:该表只包含新卡的信息，允许将新卡导入到另一个网站 数据库
- 库存更新:该表包含已经在数据库中的卡片的句柄和数量，以便快速更新这些卡片的数量
我试图明确的问题，但如果需要更多的信息，这里是完整的代码粘贴:https://pastebin.com/TQ54f6gB
数据库结构的图像:
- https://i.stack.imgur.com/hcwMa.png
- https://i.stack.imgur.com/PgpJB.png
- https://i.stack.imgur.com/EpYdh.png
- https://i.stack.imgur.com/RkwFK.png
按要求，以下是照片的文本格式:
CREATE TABLE `AddNewCards` (
`Handle` varchar(255) NOT NULL,
`Title` varchar(255) NOT NULL,
`Vendor` varchar(45) DEFAULT 'the10thinningva.com',
`Tags` varchar(255) DEFAULT NULL,
`Published` varchar(5) DEFAULT 'FALSE',
`Option1Name` varchar(45) DEFAULT 'Title',
`Option1Value` varchar(45) DEFAULT 'Default Title',
`VariantGrams` decimal(4,0) DEFAULT '45',
`Variant Inventory Tracker` varchar(45) DEFAULT 'shopify',
`Variant Inventory Qty` int DEFAULT '1',
`Variant Inventory Policy` varchar(45) DEFAULT 'deny',
`Variant Fufillment Service` varchar(45) DEFAULT 'manual',
`Variant Price` decimal(5,2) NOT NULL,
`Variant Requires Shipping` varchar(5) DEFAULT 'TRUE',
`Variant Taxable` varchar(5) DEFAULT 'TRUE',
`Img Src(front)` varchar(255) DEFAULT NULL,
`Img Src(back)` varchar(255) DEFAULT NULL,
`Gift Card` varchar(5) DEFAULT 'FALSE',
`Status` varchar(45) DEFAULT 'active',
`Description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Handle`),
UNIQUE KEY `handle_UNIQUE` (`Handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `InventoryUpdate` (
`Handle` varchar(255) NOT NULL,
`Variant Inventory Qty` int NOT NULL,
PRIMARY KEY (`Handle`),
UNIQUE KEY `Handle_UNIQUE` (`Handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `MasterCardRecord` (
`Handle` varchar(255) NOT NULL,
`Title` varchar(255) NOT NULL,
`Vendor` varchar(45) DEFAULT 'the10thinningva.com',
`Tags` varchar(255) DEFAULT NULL,
`Published` varchar(5) DEFAULT 'FALSE',
`Option1Name` varchar(45) DEFAULT 'Title',
`Option1Value` varchar(45) DEFAULT 'Default Title',
`VariantGrams` decimal(4,0) DEFAULT '45',
`Variant Inventory Tracker` varchar(45) DEFAULT 'shopify',
`Variant Inventory Qty` int DEFAULT '1',
`Variant Inventory Policy` varchar(45) DEFAULT 'deny',
`Variant Fufillment Service` varchar(45) DEFAULT 'manual',
`Variant Price` decimal(5,2) NOT NULL,
`Variant Requires Shipping` varchar(5) DEFAULT 'TRUE',
`Variant Taxable` varchar(5) DEFAULT 'TRUE',
`Img Src(front)` varchar(255) DEFAULT NULL,
`Img Src(back)` varchar(255) DEFAULT NULL,
`Gift Card` varchar(5) DEFAULT 'FALSE',
`Status` varchar(45) DEFAULT 'active',
`Description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Handle`),
UNIQUE KEY `handle_UNIQUE` (`Handle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
条目的示例如下:
'1989-fleer-#nno-california-angels-team-stickers-(one-logo)', '1989 Fleer #NNO California Angels Team Stickers (One Logo)', 'websitelink.com', 'collectable', 'TRUE', 'Title', 'Default Title', '45', 'shopify', '12', 'deny', 'manual', '2.00', 'TRUE', 'TRUE', 'https://linktoajpg_front.jpg', 'https://linktoajpg_back.jpg', 'FALSE', 'active', '1989 Fleer #NNO California Angels Team Stickers (One Logo)'
尝试使用单个连接，打开大量连接是消耗资源的，请参见此post stack post，至少关闭已打开的连接。
也尽量把你的代码分成函数，比如update, retrieve, add都必须有自己的函数。
编辑:
public static void SQL() {
int handleqty = -1;
String url = "jdbc:mysql://MYURL:3306/MYDB";
String username = "username";
String password = "password";
try{
Connection conn1 = DriverManager.getConnection(url, username, password);
String cardinfo = "";
String Handle = "";
String Title = "";
String Vendor = "";
String Tags = "";
String Published = "";
String Option1Name = "";
String Option1Value = "";
int VariantGrams = "";
String VariantInventoryTracker = "";
String VariantInventoryQty = "";
String VariantInventoryPolicy = "";
String VariantFufillmentService = "";
float VariantPrice = "";
String VariantRequiresShipping = "";
String VariantTaxable = "";
String ImgSrcfront = "";
String ImgSrcback = "";
String GiftCard = "";
String Status = "";
String Description = "";
for(Entry<String, String> entry: passToSQL.entrySet()) {
//System.out.println(entry);
cardinfo = entry.getValue();
Handle = cardinfo.split(",")[0];
Title = cardinfo.split(",")[1];
Vendor = cardinfo.split(",")[2];
Tags = cardinfo.split(",")[3];
Published = cardinfo.split(",")[4];
Option1Name = cardinfo.split(",")[5];
Option1Value = cardinfo.split(",")[6];
VariantGrams = Integer.parseInt(cardinfo.split(",")[7]);
VariantInventoryTracker = cardinfo.split(",")[8];
VariantInventoryQty = cardinfo.split(",")[9];
VariantInventoryPolicy = cardinfo.split(",")[10];
VariantFufillmentService = cardinfo.split(",")[11];
VariantPrice = Float.parseFloat(cardinfo.split(",")[12]);
VariantRequiresShipping = cardinfo.split(",")[13];
VariantTaxable = cardinfo.split(",")[14];
ImgSrcfront = cardinfo.split(",")[15];
ImgSrcback = cardinfo.split(",")[16];
GiftCard = cardinfo.split(",")[17];
Status = cardinfo.split(",")[18];
Description = cardinfo.split(",")[19];
String checkexists = "SELECT EXISTS(SELECT 1 FROM MasterCardRecord WHERE Handle = ? LIMIT 1)";
PreparedStatement statement1 = conn1.prepareStatement(checkexists);
statement1.setString(1, Handle);
ResultSet exists = statement1.executeQuery();
exists.next();
//System.out.println(exists.getString(1));
boolean handleexists = false;
if(exists.getString(1).equals("1")) {
handleexists = true;
//REMOVED: Connection conn2 = DriverManager.getConnection(url, username, password);
String sql2 = "SELECT `Variant Inventory Qty` FROM MasterCardRecord WHERE Handle = ? LIMIT 1";
PreparedStatement getqty = conn1.prepareStatement(sql2);
getqty.setString(1, Handle);
ResultSet handleqtyrs = getqty.executeQuery();
handleqtyrs.next();
handleqty = handleqtyrs.getInt(1);
}
if(exists.getString(1).equals("0")) {
handleexists = false;
}
statement1.close();
// REMOVED: conn1.close();
//NEW CODE --------------------------------------------------------------------------------
if (handleexists == true) {
//REMOVED: Connection connInvUpdate= DriverManager.getConnection(url, username, password);
String InvUpdate = "INSERT INTO InventoryUpdate (Handle, `Variant Inventory Qty`) VALUES (?,?) ON DUPLICATE KEY UPDATE `Variant Inventory Qty` = ?";
PreparedStatement InvStatement = conn1.prepareStatement(InvUpdate);
InvStatement.setString(1,Handle);
InvStatement.setInt(2, handleqty + 1);
InvStatement.setInt(3, handleqty + 1);
int updatedInv = InvStatement.executeUpdate();
//REMOVED: connInvUpdate.close();
InvStatement.close();
}
//-----------------------------------------------------------------------------------------
if(handleexists == false) {
// Handle doesnt exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "INSERT INTO MasterCardRecord (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement statement2 = conn1.prepareStatement(sql);
statement2.setString(1, Handle);
statement2.setString(2, Title);
statement2.setString(3, Vendor);
statement2.setString(4, Tags);
statement2.setString(5, Published);
statement2.setString(6, Option1Name);
statement2.setString(7, Option1Value);
statement2.setInt(8, VariantGrams);
statement2.setString(9, VariantInventoryTracker);
statement2.setString(10, VariantInventoryQty);
statement2.setString(11, VariantInventoryPolicy);
statement2.setString(12, VariantFufillmentService);
statement2.setFloat(13, VariantPrice);
statement2.setString(14, VariantRequiresShipping);
statement2.setString(15, VariantTaxable);
statement2.setString(16, ImgSrcfront);
statement2.setString(17, ImgSrcback);
statement2.setString(18, GiftCard);
statement2.setString(19, Status);
statement2.setString(20, Description);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Item Added: " + Handle);
}
statement2.close();
//REMOVED: conn3.close();
// Put into AddNewCards as well, for export and adding ONLY those cards that dont exist yet.
//-----> remove thisConnection AddNewCardConn = DriverManager.getConnection(url, username, password);
String AddNewCardSQL = "INSERT INTO AddNewCards (Handle, Title, Vendor, Tags, Published, Option1Name, Option1Value, VariantGrams, `Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, `Variant Fufillment Service`, `Variant Price`, `Variant Requires Shipping`, `Variant Taxable`, `Img Src(front)`, `Img Src(back)`, `Gift Card`, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement AddNewCardStatement = conn1.prepareStatement(AddNewCardSQL);
AddNewCardStatement.setString(1, Handle);
AddNewCardStatement.setString(2, Title);
AddNewCardStatement.setString(3, Vendor);
AddNewCardStatement.setString(4, Tags);
AddNewCardStatement.setString(5, Published);
AddNewCardStatement.setString(6, Option1Name);
AddNewCardStatement.setString(7, Option1Value);
AddNewCardStatement.setInt(8, VariantGrams);
AddNewCardStatement.setString(9, VariantInventoryTracker);
AddNewCardStatement.setString(10, VariantInventoryQty);
AddNewCardStatement.setString(11, VariantInventoryPolicy);
AddNewCardStatement.setString(12, VariantFufillmentService);
AddNewCardStatement.setFloat(13, VariantPrice);
AddNewCardStatement.setString(14, VariantRequiresShipping);
AddNewCardStatement.setString(15, VariantTaxable);
AddNewCardStatement.setString(16, ImgSrcfront);
AddNewCardStatement.setString(17, ImgSrcback);
AddNewCardStatement.setString(18, GiftCard);
AddNewCardStatement.setString(19, Status);
AddNewCardStatement.setString(20, Description);
int AddCardRows = AddNewCardStatement.executeUpdate();
AddNewCardStatement.close();
//REMOVED: AddNewCardConn.close();
}
if (handleexists == true) {
// Handle DOES exist within database
//REMOVED: Connection conn3 = DriverManager.getConnection(url, username, password);
String sql = "UPDATE MasterCardRecord SET `Variant Inventory Qty` = ? WHERE Handle = ?";
PreparedStatement statement2 = conn3.prepareStatement(sql);
statement2.setInt(1,handleqty + 1);
statement2.setString(2, Handle);
int rows = statement2.executeUpdate();
if (rows > 0 && showupdates == true) {
System.out.println("Inventory of: " + Handle + "has been updated from " + handleqty + " to %d".formatted(handleqty + 1));
}
statement2.close();
//REMOVED: conn3.close();
}
}
}
catch(Exception e){
System.out.println("Database connection failed!n" + e);
}
}
//ADDED:
conn1.close();
}
PRIMARY KEY(HANDLE)在HANDLE上提供唯一索引，因此删除
UNIQUE KEY `handle_UNIQUE` (`Handle`)
看
SELECT EXISTS(SELECT 1 FROM MasterCardRecord WHERE Handle = ? LIMIT 1)
去除LIMIT 1。EXISTS成功后停止。
看
SELECT `Variant Inventory Qty` FROM MasterCardRecord WHERE Handle = ? LIMIT 1
这可以作为存在性检查。也就是说，看看是否可以去掉前面的查询。
没有ORDER BY的LIMIT会导致得到一些随机结果。如果你不能有多行，那么LIMIT是误导;如果您关心您得到了多少行中的哪一行，那么考虑添加一个ORDER BY。
SQL在一次做许多行比从应用程序来回要好得多。你的SELECT能获取所有相关行吗?更好的是，IODKU可以一次对所有必要的行执行操作吗?
如果您需要更多帮助，请尝试将代码压缩到一个屏幕。我不需要看到所有的应用程序代码构建和检查值——除非确实存在于代码中!