Làm cách nào để cập nhật mysql nhanh hơn?
Khi cập nhật một số lượng lớn bản ghi trong cơ sở dữ liệu OLTP, chẳng hạn như MySQL, bạn phải lưu ý về việc khóa bản ghi. Nếu những bản ghi đó bị khóa, chúng sẽ không thể chỉnh sửa (cập nhật hoặc xóa) bởi các giao dịch khác trên cơ sở dữ liệu của bạn. Một phương pháp phổ biến được sử dụng để cập nhật một số lượng lớn bản ghi là chạy nhiều bản cập nhật nhỏ hơn theo đợt. Bằng cách này, chỉ những bản ghi đang được cập nhật tại bất kỳ thời điểm nào mới bị khóa Show Nếu bạn đang tự hỏi
Sau đó, bài viết này là dành cho bạn. Lưu ý rằng đây không phải là cách duy nhất. Có các cách tiếp cận khác như hoán đổi bảng, chạy bản cập nhật tiêu chuẩn tùy thuộc vào mức độ cô lập giao dịch của bạn, v.v. Việc sử dụng các phương pháp này phụ thuộc vào trường hợp sử dụng của bạn. Đối với trường hợp sử dụng của chúng tôi, giả sử chúng tôi đang cập nhật bảng người dùng, nếu bị khóa trong một khoảng thời gian đáng kể (giả sử > 10 giây), có thể ảnh hưởng đáng kể đến trải nghiệm người dùng của chúng tôi và không lý tưởng Cài đặtChúng tôi sẽ sử dụng docker để chạy thư viện 3 container và python 4 để tạo dữ liệu giả
Hãy thiết lập một thư mục dự án và tạo một số dữ liệu người dùng giả mạo
Tạo tập lệnh python tạo dữ liệu giả có tên 5
Tập lệnh 5 tạo dữ liệu giả có định dạng 7 mỗi hàng. Cấp quyền cho trình tạo dữ liệu giả và tạo 10 triệu hàng như hình bên dưới
Hãy bắt đầu một bộ chứa MySQL với dữ liệu chúng tôi đã tạo ở trên và đăng nhập vào đó ________số 8 Tạo một cơ sở dữ liệu có tên là 8 và một bảng 9 đơn giản. Chèn dữ liệu được tạo vào bảng như hình bên dưới 1Sự cố với một bản cập nhật lớnGiả sử chúng ta làm việc cho một trang web thương mại điện tử. Một lỗi đã được đưa vào sản xuất và hiện chúng tôi có trường 0(trạng thái) được đặt không chính xác cho người dùng có id từ 3 triệu(3000000) đến 8 triệu(8000000)Chúng tôi phải cập nhật 5 triệu bản ghi trong tổng số 10 triệu bản ghi để đặt giá trị 0 thành 2. Một hạn chế mà chúng tôi đang làm việc là giữ cho thời gian ngừng hoạt động của người dùng càng nhỏ càng tốt. Nếu bạn là người dùng và hồ sơ của bạn bị khóa, bạn sẽ không thể sửa đổi dữ liệu của mình. Hãy xem trải nghiệm người dùng có thể bị ảnh hưởng như thế nào bởi một bản cập nhật lớn đang khóa hồ sơ của họTrong thiết bị đầu cuối sql của bạn chạy lệnh cập nhật này 5Đồng thời, trong một thiết bị đầu cuối sql khác, hãy thử cập nhật bản ghi người dùng bị khóa bởi bản cập nhật ở trên. Chúng tôi sử dụng where 3 để chọn một hàng bị khóa bởi bản cập nhật ở trên 7 8Lỗi này xảy ra do bản cập nhật lớn của chúng tôi đã khóa bản ghi với 3 và điều này ngăn bất kỳ giao dịch nào khác sửa đổi dữ liệu bị khóaBản cập nhật thứ hai sẽ đợi theo mặc định 50 giây (được đặt là 5) trước khi hết thời gian chờ. Bạn có thể kiểm tra cài đặt này bằng truy vấn bên dưới 1Điều gì sẽ xảy ra nếu thay vì cập nhật 5 triệu bản ghi trong một câu lệnh cập nhật, chúng tôi chỉ cập nhật một số bản ghi người dùng tại một thời điểm? Cập nhật hàng loạtHãy chạy các bản cập nhật theo lô 50.000 bản ghi. Hãy xem cách chúng tôi có thể cập nhật bảng người dùng của mình theo khối Trong bảng người dùng của chúng tôi, chúng tôi có một cột chính là id tăng dần một cách đơn điệu. Chúng tôi có thể sử dụng điều này để phân chia các bản cập nhật của mình thành các lô 50.000 bản ghi người dùng. Giả sử lần này chúng ta muốn thiết lập kỷ lục 0 thành 7Để chạy các bản cập nhật trong 8, chúng ta cần tạo một thủ tục được lưu trữ để đóng gói logic này. Logic vòng lặp rất đơn giản. Chúng tôi tạo một cửa sổ trượt được xác định bằng id bắt đầu của 9 và id kết thúc của 0, được tăng lên bởi 1 cho mỗi lần chạyLưu ý rằng chúng tôi sử dụng 2 để đặt dấu phân cách câu lệnh thành 3. Điều này cho phép chúng tôi sử dụng dấu phân cách mặc định của MySQL là 4 trong định nghĩa thủ tục được lưu trữ. Bạn có thể thấy rằng sau lệnh 5, chúng tôi đặt dấu phân cách trở lại 4 3Câu lệnh cập nhật cuối cùng bên ngoài vòng lặp while được sử dụng để cập nhật các bản ghi bị bỏ sót trong vòng lặp. Chúng tôi cũng có một số báo cáo nhật ký để in ra các khối hiện đang được cập nhật. Vì MySQL 7 đã bao gồm nên chúng tôi đang cập nhật lại 0 của đoạn trước. Hãy chú ý đến điều này cho logic của bạnTrong khi 9 ở trên đang chạy, hãy chạy một bản cập nhật trong ứng dụng khách sql khác 7 1Bản cập nhật này thực hiện nhanh chóng. Mặc dù bản cập nhật lớn mất nhiều thời gian hơn do chúng tôi thực hiện theo đợt, nhưng chúng tôi có thể thấy cách tiếp cận này cho phép các giao dịch khác chỉnh sửa dữ liệu Phần kết luậnBạn có thể phá bỏ các thùng chứa docker của mình bằng các lệnh sau 2Tóm lại, chúng tôi đã thấy
Một điểm quan trọng cần lưu ý ở đây là thứ tự cập nhật. Khi chạy cập nhật hàng loạt và cập nhật đơn lẻ trên cùng một cột của bản ghi, bản cập nhật mới nhất sẽ được áp dụng. Bạn cũng có thể thực hiện cập nhật hàng loạt này song song, nhưng bạn phải cẩn thận để không gặp phải bất kỳ bế tắc nào Nếu logic cập nhật phức tạp hơn và bảng của bạn có chỉ mục, bạn có thể sử dụng 11 với các tính năng của 12 hoặc 13 như được hiển thị tại đâyLần tới khi bạn thực hiện một bản cập nhật lớn trên bảng, hãy cân nhắc chạy các bản cập nhật theo khối để giữ cho các giao dịch khác không bị ảnh hưởng bởi lỗi hết thời gian khóa Làm cách nào để tăng tốc độ cập nhật MySQL?Mẹo điều chỉnh và tối ưu hóa hiệu suất MySQL . Cân bằng bốn tài nguyên phần cứng chính Sử dụng InnoDB, không phải MyISAM Sử dụng Phiên bản mới nhất của MySQL. . Cân nhắc sử dụng Công cụ cải thiện hiệu suất tự động Tối ưu hóa truy vấn Sử dụng các chỉ mục khi thích hợp Chức năng trong Predicates Tránh % ký tự đại diện trong vị ngữ Tại sao MySQL rất chậm?Nếu cơ sở dữ liệu của bạn đang được sử dụng với số lượng lớn , điều này có thể làm chậm cơ sở dữ liệu. Khi có quá nhiều truy vấn cần xử lý cùng lúc, CPU sẽ bị nghẽn cổ chai dẫn đến cơ sở dữ liệu bị chậm.
Cái nào chèn hoặc cập nhật MySQL nhanh hơn?Để có hiệu suất truy vấn tốt nhất trong tương lai, bạn nên cập nhật để giữ nguyên phạm vi . Xóa và chèn sẽ không nhất thiết phải sử dụng cùng một phạm vi. Đối với một bảng có kích thước đó, sẽ khó có thể làm như vậy. |