Làm cách nào để chèn số lượng lớn vào MySQL?
|
Hãy để chúng tôi xem một ví dụ. Đầu tiên, chúng ta sẽ tạo một bảng. Sau đây là lệnh CREATE để tạo bảng Show mysql> CREATE table MultipleRecordWithValues - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.88 sec) Sau đây là cú pháp chèn hàng loạt INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N; Áp dụng cú pháp trên để chèn bản ghi hàng loạt mysql> insert into MultipleRecordWithValues values(1,'John'),(2,'Carol'),(3,'Bob'),(4,'Smith'); Query OK, 4 rows affected (0.16 sec) Records: 4 Duplicates: 0 Warnings: 0 Vì 4 hàng bị ảnh hưởng, điều đó có nghĩa là chúng tôi đã chèn bản ghi thành công. Để kiểm tra xem tất cả các bản ghi có trong bảng hay không, hãy sử dụng lệnh SELECT Gần đây tôi đã viết một nút. js để lặp lại hàng triệu tệp mỗi ngày và chèn nội dung của chúng vào cơ sở dữ liệu MySQL. Thay vì xử lý một bản ghi tại một thời điểm, tập lệnh lưu trữ nội dung tệp trong bộ nhớ và sau đó chạy câu lệnh INSERT cứ sau 1000 tệp. Để làm điều đó, tôi đã sử dụng dạng chèn hàng loạt của câu lệnh INSERT. Tùy thuộc vào yêu cầu cụ thể của bạn, bạn có thể chọn sử dụng một giải pháp khác. Trong blog hôm nay, chúng ta sẽ xem xét một vài lựa chọn thay thế Câu lệnh INSERT hỗ trợ một số biến thể cú pháp, một trong số đó là để chèn nhiều hàng cùng một lúc. Để làm điều đó, chúng ta chỉ cần đặt từng danh sách giá trị trong ngoặc đơn và phân tách chúng bằng dấu phẩy INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
..
(value_list_n);
Đủ đơn giản. Đây là một tuyên bố mẫu được hiển thị trong Mặc dù câu lệnh trên được định dạng để dễ đọc, nhưng bạn không cần phải lo lắng về điều đó khi tạo SQL động. Miễn là cú pháp đúng về mặt ngữ nghĩa, nó sẽ hoạt động tốt. Cuối cùng, lưu ý rằng 1000 là số hàng tối đa có thể được chèn cùng một lúc bằng cách sử dụng câu lệnh INSERT Một tùy chọn khác, dành cho những người không hứng thú với việc viết mã kịch bản, là sử dụng thứ gì đó như LOAD DATA INFILE. Đó là một lệnh dành riêng cho MySQL, nhưng hầu hết các hệ thống cơ sở dữ liệu (DBMS) khác đều hỗ trợ một cái gì đó tương tự. Nó có thể nhập nhiều định dạng tệp được phân tách, bao gồm dấu phẩy (CSV), Tab (TDV) và các định dạng khác Đây là câu lệnh nhập dữ liệu từ "c. \tmp\giảm giá. csv" vào bảng giảm giá LOAD DATA INFILE 'c:/tmp/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; Trong câu lệnh trên, tùy chọn IGNORE 1 ROWS được sử dụng để bỏ qua các tiêu đề Tôi muốn sử dụng phương pháp này để nhập dữ liệu, nhưng các tệp mà chúng tôi đang nhập từ đó sử dụng định dạng phức tạp và chuyên biệt cao, đòi hỏi nhiều logic giao diện người dùng Vẫn còn một cách tiếp cận khác là sử dụng tiện ích nhập chẳng hạn như Trình hướng dẫn nhập của Navicat. Nó hỗ trợ hầu hết mọi định dạng mà bạn có thể tưởng tượng, bao gồm CSV, Excel, HTML, XML, JSON và nhiều định dạng khác Có một màn hình để chọn dấu phân cách bản ghi, dấu phân cách trường và vòng loại văn bản Navicat cho bạn thấy sự tiến bộ trong thời gian thực Sau khi hoàn tất, bạn có thể lưu tất cả các cài đặt của mình để sử dụng sau này, điều này không chỉ hữu ích để chạy cùng một cách thường xuyên mà còn cho phép bạn tự động hóa nó, để quá trình nhập diễn ra mà không cần bất kỳ sự can thiệp bổ sung nào từ phía bạn Trong blog hôm nay, chúng tôi đã đề cập đến một số lựa chọn thay thế để thực hiện thao tác chèn hàng loạt vào MySQL và các DBMS khác Khi bạn cần chèn hàng triệu bản ghi vào cơ sở dữ liệu MySQL, bạn sẽ sớm nhận ra rằng việc gửi từng câu lệnh INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;4 không phải là một giải pháp khả thi Tài liệu MySQL có một số mẹo tối ưu hóa INSERT đáng để bắt đầu đọc Tôi sẽ cố gắng tóm tắt ở đây hai kỹ thuật chính để tải dữ liệu vào cơ sở dữ liệu MySQL một cách hiệu quả TẢI DỮ LIỆU VÀO TẬP TINNếu bạn đang tìm kiếm hiệu suất thô, đây chắc chắn là giải pháp bạn lựa chọn. INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;5 là một câu lệnh dành riêng cho MySQL, được tối ưu hóa cao để chèn trực tiếp dữ liệu vào bảng từ tệp CSV/TSV Có hai cách để sử dụng INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;5. Bạn có thể sao chép tệp dữ liệu vào thư mục dữ liệu của máy chủ (thường là INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;7) và chạy LOAD DATA INFILE '/path/to/products.csv' INTO TABLE products;Điều này khá cồng kềnh vì nó yêu cầu bạn phải có quyền truy cập vào hệ thống tệp của máy chủ, đặt quyền thích hợp, v.v. Tin vui là bạn cũng có thể lưu trữ tệp dữ liệu ở phía máy khách và sử dụng từ khóa INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;8 LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;Trong trường hợp này, tệp được đọc từ hệ thống tệp của máy khách, được sao chép trong suốt vào thư mục tạm thời của máy chủ và được nhập từ đó. Nói chung, nó gần như nhanh như tải trực tiếp từ hệ thống tệp của máy chủ. Tuy nhiên, bạn cần đảm bảo rằng tùy chọn này được bật trên máy chủ của mình Có nhiều tùy chọn để INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;5, chủ yếu liên quan đến cách cấu trúc tệp dữ liệu của bạn (dấu phân cách trường, bao vây, v.v. ). Hãy xem tài liệu để xem tất cả Mặc dù INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;5 là tùy chọn hiệu suất tốt nhất của bạn, nhưng nó yêu cầu bạn phải chuẩn bị sẵn dữ liệu của mình dưới dạng tệp văn bản được phân tách bằng dấu phân cách. Nếu bạn không có các tệp như vậy, bạn sẽ cần sử dụng thêm tài nguyên để tạo chúng và có thể sẽ tăng thêm mức độ phức tạp cho ứng dụng của bạn. May mắn thay, có một giải pháp thay thếphần chèn mở rộng Một câu lệnh SQL INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;4 điển hình trông giống như INSERT INTO user (id, name) VALUES (1, 'Ben');Một INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;4 mở rộng nhóm một số bản ghi vào một truy vấn duy nhất________số 8 Chìa khóa ở đây là tìm số lần chèn tối ưu cho mỗi truy vấn để gửi. Không có con số chung nào phù hợp với tất cả, vì vậy bạn cần đánh giá chuẩn một mẫu dữ liệu của mình để tìm ra giá trị mang lại hiệu suất tối đa hoặc sự đánh đổi tốt nhất về hiệu suất/sử dụng bộ nhớ Để tận dụng tối đa các phần chèn mở rộng, bạn cũng nên
Tôi đang chèn 1. 2 triệu hàng, 6 cột thuộc nhiều loại khác nhau, trung bình ~26 byte mỗi hàng. Tôi đã thử nghiệm hai cấu hình phổ biến
Để làm cơ sở so sánh, tôi đã sao chép bảng bằng cách sử dụng LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;4, mang lại hiệu suất 313.000 lần chèn/giâyTẢI DỮ LIỆU VÀO TẬP TINTrước sự ngạc nhiên của tôi, INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;5 tỏ ra nhanh hơn một bản sao trên bàn
Sự khác biệt giữa hai con số dường như liên quan trực tiếp đến thời gian chuyển dữ liệu từ máy khách sang máy chủ. tệp dữ liệu có kích thước 53 MiB và chênh lệch thời gian giữa 2 điểm chuẩn là 543 ms, thể hiện tốc độ truyền 780 mbps, gần với tốc độ Gigabit Điều này có nghĩa là, rất có thể, máy chủ MySQL không bắt đầu xử lý tệp cho đến khi tệp được truyền hoàn toàn. do đó, tốc độ chèn của bạn liên quan trực tiếp đến băng thông giữa máy khách và máy chủ, điều quan trọng cần tính đến nếu chúng không nằm trên cùng một máy phần chèn mở rộngTôi đã đo tốc độ chèn bằng cách sử dụng LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;8, một phần lớp PHP của thư viện mã nguồn mở mà tôi đã viết, với tối đa 10.000 lần chèn cho mỗi truy vấnNhư chúng ta có thể thấy, tốc độ chèn tăng nhanh khi số lần chèn trên mỗi truy vấn tăng lên. Chúng tôi đã nhận được hiệu suất tăng gấp 6 lần trên máy chủ cục bộ và tăng 17 lần trên mạng, so với tốc độ INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;4 tuần tự
Phải mất khoảng 1.000 lần chèn cho mỗi truy vấn để đạt được thông lượng tối đa trong cả hai trường hợp, nhưng 40 lần chèn cho mỗi truy vấn là đủ để đạt được 90% thông lượng này trên máy chủ cục bộ, đây có thể là một sự cân bằng tốt ở đây. Cũng cần lưu ý rằng sau khi đạt mức cao nhất, hiệu suất thực sự giảm khi bạn đưa vào nhiều lần chèn hơn cho mỗi truy vấn Lợi ích của các phần chèn mở rộng sẽ cao hơn qua mạng vì tốc độ chèn tuần tự trở thành một chức năng của độ trễ của bạn INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;2 Độ trễ giữa máy khách và máy chủ càng cao, bạn càng được hưởng lợi nhiều hơn từ việc sử dụng các phần chèn mở rộng Phần kết luậnĐúng như dự đoán, INSERT INTO user (id, name) VALUES (1, 'Ben');0 là giải pháp ưu tiên khi tìm kiếm hiệu suất thô trên một kết nối. Nó yêu cầu bạn chuẩn bị một tệp được định dạng đúng, vì vậy nếu bạn phải tạo tệp này trước và/hoặc chuyển tệp đó tới máy chủ cơ sở dữ liệu, hãy đảm bảo tính đến điều đó khi đo tốc độ chènMặt khác, các phần chèn mở rộng không yêu cầu tệp văn bản tạm thời và có thể cung cấp cho bạn khoảng 65% thông lượng INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N;5, đây là tốc độ chèn rất hợp lý. Thật thú vị khi lưu ý rằng không quan trọng bạn đang sử dụng máy chủ cục bộ hay qua mạng, việc nhóm một số phần chèn trong một truy vấn luôn mang lại hiệu suất tốt hơn Nếu bạn quyết định sử dụng các phần chèn mở rộng, hãy đảm bảo kiểm tra môi trường của bạn bằng một mẫu dữ liệu thực tế và một vài cấu hình phần chèn cho mỗi truy vấn khác nhau trước khi quyết định giá trị nào phù hợp nhất với bạn Hãy cẩn thận khi tăng số lần chèn cho mỗi truy vấn, vì nó có thể yêu cầu bạn
Lưu ý cuối cùng, điều đáng nói là theo Percona, bạn có thể đạt được hiệu suất tốt hơn nữa bằng cách sử dụng kết nối đồng thời, phân vùng và nhiều vùng đệm. Xem bài đăng này trên blog của họ để biết thêm thông tin Các điểm chuẩn đã được chạy trên một máy chủ kim loại trần chạy Centos 7 và MySQL 5. 7, Xeon E3 @ 3. 8 GHz, RAM 32 GB và ổ SSD NVMe. Bảng điểm chuẩn MySQL sử dụng công cụ lưu trữ InnoDB Mã nguồn điểm chuẩn có thể được tìm thấy trong ý chính này. Biểu đồ kết quả điểm chuẩn có sẵn trên biểu đồ. ly Làm cách nào để chèn hàng loạt dữ liệu trong MySQL?Cú pháp chèn dữ liệu hàng loạt vào MySQL . Nhập mệnh đề INSERT INTO và tên bảng mà bạn muốn chèn dữ liệu vào Sử dụng mệnh đề GIÁ TRỊ và sau đó ghi dữ liệu của hàng đầu tiên vào trong ngoặc, đóng ngoặc và sau dấu phẩy Làm cách nào để chèn 10000 bản ghi trong MySQL?Điều này sẽ làm điều đó trong một câu lệnh SQL. $sql=" CHÈN VÀO wp_usermeta ('user_id', 'meta_key', 'meta_value') GIÁ TRỊ ";
Làm cách nào tôi có thể chèn hơn 1000 hàng vào MySQL?Bạn có thể dễ dàng thay đổi giới hạn này bằng cách truy cập MySQL Workbench >> Chỉnh sửa >> Tùy chọn >> tab Truy vấn SQL. Ở đây, bạn sẽ có tùy chọn Giới hạn hàng. Bạn có thể đặt giá trị này thành giá trị rất cao hoặc bỏ chọn tùy chọn này . Khi bạn bỏ chọn tùy chọn đó, nó sẽ truy xuất tất cả các hàng từ một truy vấn (tương đương với không giới hạn).
Làm cách nào để chèn nhanh vào MySQL?Bạn có thể sử dụng các phương pháp sau để tăng tốc độ chèn. Nếu bạn đang chèn đồng thời nhiều hàng từ cùng một máy khách, hãy sử dụng câu lệnh INSERT với nhiều danh sách GIÁ TRỊ để chèn nhiều hàng cùng lúc . Điều này nhanh hơn đáng kể (nhanh hơn nhiều lần trong một số trường hợp) so với việc sử dụng các câu lệnh INSERT một hàng riêng biệt. |
