Tôi có một cơ sở dữ liệu MySQL mà tôi muốn kiểm tra hiệu suất của. Tôi muốn chèn khoảng 5 triệu bản ghi vào một bảng có khóa chính được tăng tự động. Có cách nào dễ dàng để thực hiện việc này một cách nhanh chóng với SQL không? . sql, nhưng tất cả những gì tôi thực sự muốn làm là chèn cùng một dữ liệu giống hệt nhau [nhưng với một PK khác], 5 triệu lần
- stackoverflow. comghi bàn. 2
câu trả lời được chấp nhận
Bạn có thể chèn NULL làm giá trị của PK hoặc đơn giản là không chỉ định nó trong danh sách cột trong câu lệnh chèn của bạn. Bằng cách đó, nó sẽ tự động tăng lên
for [$i=0; $i[] something
}
ghi bàn. 1
Đây có thể không phải là một thử nghiệm hiệu suất tốt, vì các hàng giống hệt nhau sẽ không tạo chỉ mục với các giá trị được phân phối thường xuyên cho bất kỳ thứ gì ngoại trừ PK, vì vậy hầu hết các truy vấn sẽ không thể hiện hiệu suất như mong đợi
Sau nhiều năm làm việc chủ yếu trong MS SQL Server với tư cách là Nhà phát triển cơ sở dữ liệu, vài tháng trước, tôi đã bắt đầu một giai đoạn mới trong sự nghiệp chuyên nghiệp của mình. Điều này bao gồm làm việc trên nhiều công nghệ cơ sở dữ liệu hơn [Cơ sở dữ liệu quan hệ] như MySQL và NoSQL với tư cách là Cassandra và thậm chí có được kiến thức chuyển đổi trong OS Linux. Bài viết này là bài đầu tiên trong loạt ba bài dành riêng cho cơ sở dữ liệu quan hệ và những thách thức chung. Nhiều người trong số họ là một nguồn đau đầu phổ biến, đặc biệt là khi nhiều người trong chúng ta cũng cố gắng phát minh lại bánh xe. Nhóm bài viết này sẽ chỉ ra điều hoàn toàn ngược lại và có thể giúp bạn thoát khỏi tội lặp lại lời nguyền của thần thoại Sisyphus
Cách tiếp cận tiêu chuẩn để tải bảng
Bài viết này nhằm cung cấp cho bạn cách tiếp cận tiêu chuẩn để tải bảng -- ngay cả với hàng trăm triệu hàng có dữ liệu nguồn chẳng hạn như tệp văn bản thuần túy hoặc tệp CSV. Trước khi bắt đầu giải thích mã mà chúng ta có thể sử dụng, tôi muốn đề cập đến một số tình huống mà bạn có thể cần tải các bảng theo cách nhanh hơn, đáng tin cậy và dễ dàng hơn
- di cư. Trong một tỷ lệ lớn các dự án di chuyển trong đó cơ sở dữ liệu nguồn bao gồm các công nghệ khác nhau, phương pháp khả thi duy nhất bao gồm xuất dữ liệu thông qua các tệp văn bản thuần túy
- Làm sạch. Công ty sẽ bắt đầu sử dụng một bộ công cụ để làm sạch và xác thực dữ liệu mà sau đó phải nhập lại để nhắm mục tiêu DB
- Tải một phần. Điều này ít phổ biến hơn so với các kịch bản trước đây, nhưng đôi khi cần phải tích hợp dữ liệu mới đến từ các hệ thống phụ trợ và tích hợp vào một bảng hiện có. Trong trường hợp này, chúng tôi muốn tiếp tục sử dụng các bảng mục tiêu trong khi chúng tôi đang lưu trữ dữ liệu mới
Cách tải các bảng mục tiêu
Trong bài viết này, tôi sẽ chỉ cho bạn một cách tiếp cận để giải quyết các tình huống được mô tả ở trên. Các công nghệ được sử dụng cơ bản là Linux Bash Script và MySQL. Sơ đồ tiếp theo chỉ cho bạn các bước để tải các bảng mục tiêu
Tìm nguồn tệp CSV của bạn và chia nhỏ chúng
Bước đầu tiên là lấy các tệp CSV nguồn và chia nhỏ chúng. Bạn chỉ phải làm điều đó trong trường hợp số lượng hàng vượt quá một triệu. Hãy nhớ rằng bài viết này dành cho tải dữ liệu thực sự lớn tối thiểu mười triệu đến hàng trăm triệu hàng. Tại sao chúng tôi hướng đến việc chia nhỏ các tệp CSV?
Tập lệnh Bash, tập lệnh Shell và lệnh tách
Tách một tệp thành nhiều tệp cố định là một công việc tương đối đơn giản thông qua việc sử dụng tập lệnh Bash. Đây là một ngôn ngữ kịch bản mạnh mẽ thuộc về thế giới Linux. Trong phần này, chúng tôi muốn chỉ cho bạn cách sử dụng thực tế một số chức năng shell script thú vị hoạt động cùng với MySQL để giải quyết vấn đề liên quan đến tải lượng lớn dữ liệu vào Cơ sở dữ liệu
Sử dụng lệnh split là cách thực hiện nhanh hơn, đoạn mã tiếp theo cho bạn thấy cấu trúc của lệnh và một ví dụ
split -d -l [Number_of_Rows] [FileName] [NewName_Pattern] --additional-suffix=[File Extension]
Trong trường hợp này, chúng tôi sẽ tách một tệp có tên MyFile. csv vào một nhóm tệp có mẫu MyFile_ NumberOfFile. Nó sẽ tạo ra một danh sách các tệp dưới dạng MyFile_01. csv, Tệp của tôi_02. csv, v.v. Mã tiếp theo sẽ cho bạn thấy
tách -d -l 1000000 Tệp của tôi. csv MyFile_ --additional-suffix=. csv
Bây giờ chúng tôi có một nhóm tệp được chia thành các hàng nhỏ với độ dài một triệu mỗi tệp. Giai đoạn tiếp theo trong quy trình này là xây dựng một đoạn mã chung bên trong một loại tệp thực thi được gọi là tệp mẫu. Ở đó, bạn có thể cung cấp các biến trình bao và xây dựng logic động để sử dụng và xử lý các tệp nhỏ đầu vào. Thêm chi tiết về nó trong liên kết này
Mã mẫu cho đầu vào động
Đoạn mã tiếp theo là một ví dụ về mã mẫu giúp chúng ta lấy các tệp đầu vào và xử lý dữ liệu một cách linh hoạt. Bạn nên lưu mã bên trong tên tệp load_mysql. tpl
ĐẶT NGOÀI_KEY_CHECKS = 0;
ĐẶT UNIQUE_CHECKS = 0;
TẢI TẬP TIN DỮ LIỆU '/var/lib/mysql-files/csv/%FILENAME%' BỎ QUA
VÀO BẢNG my_target_table
CÁC LĨNH VỰC ĐƯỢC CHẤM DỨT BỞI ','
ĐƯỢC ĐẶT BỞI '"'
CÁC DÒNG ĐƯỢC KẾT THÚC BỞI '\n'
[cột1,cột2,cột3. ];
ĐẶT UNIQUE_CHECKS = 1;
ĐẶT NGOÀI_KEY_CHECKS = 1;
Bên trong tệp mẫu bash mới, chúng tôi đã tạo một mã năng động và linh hoạt, trong đó bạn có hai điểm chính. Đầu tiên là mã MySQL để thực hiện tải dữ liệu vào bảng đích. Vô hiệu hóa Khóa ngoại là một khuyến nghị tiêu chuẩn, điều này đặc biệt quan trọng để cải thiện hiệu suất trong quá trình tải. Việc sử dụng LOAD DATA INFILE đã trở thành một người bạn tốt của tôi trong những tháng qua. Đây có lẽ là cách nhanh nhất để tải dữ liệu từ các tệp văn bản thuần túy
Điểm thứ hai là sử dụng logic động để tiêu thụ tất cả các tệp CSV nhỏ hơn hoặc được chia nhỏ. Điều này là do bạn không muốn dành cả ngày cuối tuần để chờ tải tệp. Logic động cho phép bạn tự động hóa quy trình mà bạn có thể lên lịch để thực hiện thông qua một công việc định kỳ
Câu lệnh Bash tiếp theo cho phép chúng tôi lấy danh sách các tệp mà chúng tôi đã tạo trước đó. Danh sách các tệp này sẽ được sử dụng làm đầu vào để thực hiện một vòng lặp sẽ được gửi tới Máy chủ MySQL. Ở đây ví dụ được sử dụng là
cho n trong `ls MyFile_*. csv`; . tpl. mysql TargetDB;
Trong phần đầu tiên của câu lệnh bash trước đó, chúng tôi khởi tạo vòng lặp từ tải danh sách MyFiles_*. csv, phần tiếp theo [do echo $n] in tên tệp CSV đang xử lý. Sau đó, hướng dẫn sed -e thay thế trình giữ chỗ %FILENAME% bằng tên tệp csv cụ thể được tải trong mỗi lần lặp lại. Nó có nghĩa là MyFiles_01. csv, Tệp của tôi_02. csv, v.v. Bước cuối cùng bao gồm cung cấp devices_mysql. đầu vào tpl với thay đổi bên trong để chỉ ra tên tệp csv cụ thể. Nó được thực thi bên trong Cơ sở dữ liệu MySQL đích. Và bạn đã có nó -- mỗi lần lặp lại thực hiện tải vào bảng đích
Sự kết luận
Trong thời gian thực thi tập lệnh này, tôi quan sát thấy thời gian tải trung bình là một phút mười lăm giây trên một triệu hàng được chèn. Con số này có thể khác nhau tùy thuộc vào phần cứng máy chủ của bạn. Tôi đã không thể đạt được điều này cho đến khi áp dụng một chiến lược tiêu chuẩn kết hợp một số phương pháp hay nhất. Hãy thử xem -- nó tránh được sự cám dỗ của việc phát minh lại bánh xe và lặp lại huyền thoại Sisyphus